Add Custom Columns to Target/Data Warehouse Tables

Owned by Cortlandt _
Last updated: May 05, 2024
2 min read

How to add a custom column / field to the target table loaded into the data warehouse.

Preparation

  • Decide on the name and data type of the custom column/field you want to add to the target table.

  • Design the formula/calculation for the value of the custom column. Code the formula as a T-SQL expression.

Add the Custom Column with the AS Keyword

  1. Add the custom column to the SELECT clause in the Extract SQL Statement panel.
    Use the AS syntax. For example 1 as [my_count]

  2. Open the Design image-20240505-010911.png page .

  3. On the Design page select the row for the custom column.

    1. Modify the Dw Data Type as required

    2. Enter the T-SQL expression into T-SQL Expression.

    3. Confirm.

  4. Make note of the load option selected in the Load panel.

  5. Select Load All in the Load panel.
    Select Load Now image-20240505-010947.png.

  6. Restore the load setting as required – see step #4.

 

NOTES

The SQL used in the Extract SQL Statement must be compatible with the Source Object.
For more on this see https://dataself.atlassian.net/wiki/spaces/DS/pages/1928527873. https://dataself.atlassian.net/wiki/spaces/DS/pages/1928527873/ETL+Extract+SQL+Statement+Panel#SourceDrivers has links for the right SQL Syntax to Use with Various Source Drivers and Source Systems.

Using the AS keyword in the SELECT statement

The ASkeyword adds a column to the result-set of the SELECT query. The syntax does not allow you to assign a specific data type to the column.

The data type of the new column is determined by SQL based on the the value or expression in the AS expression.

 

SQL Expressions by ETL+ Source Driver / Source System

 

SQL Expressions by ETL+ Source Driver / Source System

Source Data Type

MS SQL Server /T-SQL

Text (.CSV)

ANSI Standard SQL

datetime

 

NOW() AS <column-name>

  • CAST(<existing-column> AS DATETIME) AS <new-column-name>

decimal(28,6)

 

1.1 as <column-name>

 

AS SYNTAX

  • <constant> AS <new-column-name>

    • <constant> == a number (e.g.; 22, 99.1) or string ("brown fox")

  • <expression> AS <new-column-name>

    • <expression> == date, datetime, string or numeric SQL function

    • <expression> == A constant, function, any combination of column names, constants, and functions connected by an operator or operators, or a subquery.

 

Miscellaneous

Tricks and Workarounds

  • CAST(<existing-column> AS DATETIME) AS new_hire_date

  • – T-SQL’s CONVERT function vs. ANSI standard SQL CAST function. is platform agnostic.