Derived Columns
A stage model is capable of defining Derived Columns. As the name indicates, this allows users to apply Hard Rules for transformation.
The metadata structure within a stage model looks like this:
derived_columns:
<col_alias_1>:
value: <expression_1>
datatype: <datatype_1>
src_cols_required: <src_col_1>
<col_alias_2>:
value: <expression_2>
datatype: <datatype_2>
src_cols_required:
- <src_col_2>
- <src_col_3>
Depending on how ‘col_alias’ and ‘src_col’ are called, two different behaviors can be achieved:
Overwriting existing columns
When ‘col_alias’ equals ‘src_col’, the original input column will be overwritten with the transformation configured in ‘expression’. Use with caution!
Adding new columns
When ‘col_alias’ deviates from the’src_col’, the transformation will be added as a new column.
Expressions
An expression is defined under the key ‘value’ and can basically do three different things:
- Inserting a static string that will be the same across all rows. Needs to begin with ‘!’ followed by the string.
- Renaming a column. Expression would just be the name of another column.
- Applying SQL. The Expression needs to hold valid SQL, typically based on one or multiple columns. Note: All used columns should be listed under ‘src_cols_required’.
Datatypes
Defining a datatype of the expressions is mandatory to properly generate Ghost Records. Please note, that setting a datatype does not automatically cast the expression to this datatype. You manually have to ensure that your expression matches the datatype defined.
It must only be set for SQL expressions. For static strings, it will be set to STRING (and the database correspondents) automatically. For column renaming, datatype will be set to the datatype of the input column.
Required Source Columns
The parameter ‘src_cols_required’ is only required when the Stage model is configured to not include the source columns by setting the parameter ‘include_source_columns’ to false.
If this is the case, you have to list all columns used within the SQL expressions under the parameter ‘src_cols_required’. This information is required to properly generate the model SQL.