In this article
...
You can use this Snap to implement the Slowly Changing Dimension Type 2 (SCD2) function in your Snowflake, Redshift, Azure Synapse, Databricks Lakehouse Platform, or BigQuery table. Configure the database type and the access to corresponding tables in the Configuring ELT Database Accounts for the Snap.
Warning | ||
---|---|---|
| ||
Starting from November 2021, 4.27 release, the ELT SCD2 Snap performs a MERGE INTO-based SCD2 operation instead of the previous Insert-and-then-Update approach. It is important that you take notice of this change in behavior. However, if you prefer to use the Insert-and-then-Update approach for your SCD2 operation, define the Pipeline parameter SCD2_INSERTUPDATE_FALLBACK_MODE with a value greater than zero (0), before running your Pipeline. |
...
Multiexcerpt include macro | ||||
---|---|---|---|---|
|
Known Issues
In the case of Azure Synapse, if you are configuring the Start_Date column as a Target Table Natural Key, the Snap fails in each of the following scenarios:
The source table/file contains one or more null values.
The target table is empty.
The End date of current row or End date of historical row has a static value, for example: '2021-01-01'.
- When your Databricks Lakehouse Platform instance uses Databricks Runtime Version 8.4 or lower, ELT operations involving large amounts of data might fail due to the smaller memory capacity of 536870912 bytes (512MB) allocated by default. This issue does not occur if you are using Databricks Runtime Version 9.0.
...
Info | ||
---|---|---|
| ||
You can use the SQL Expressions and Functions supported for ELT to define your Snap or Account settings with the Expression symbol = enabled, where available. This list is common to all target CDWs supported. You can also use other expressions/functions that your target CDW supports. |
...
Parameter Name | Data Type | Description | Default Value | Example | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Label | String |
| N/A | Orders_SCD2_Data | ||||||||||
Get preview data | Checkbox |
| Not selected | Selected | ||||||||||
Database Name | String/Expression | Specify the name of the database in which the target table exists. Leave this field blank if you want to use the database name specified in the Default Database Name field in the account settings. | N/A | TESTDB | ||||||||||
Schema Name (Not applicable to Databricks Lakehouse Platform) | String/Expression/Suggestion | Required. Specify the name of the database schema. Alternatively, click to fetch a list of schemas associated with the database, and select the schema.
| N/A | SALES | ||||||||||
Target Table Name | String/Expression/Suggestion | Required. Specify the name of the SCD2 table in which you want to load the data. Alternatively, click to fetch a list of table names associated with the schema, and select the table name.
| N/A | SALES_ORDERS | ||||||||||
Input Source Alias | String/Expression | Specify an alias name for your source data table. | N/A | src | ||||||||||
Target Table Action | Drop-down list | Select the SCD2 action to perform on the target table. Available options are:
| Merge data into target table | Alter table | ||||||||||
Table Columns | This field set enables you to configure the schema of the target table. You can use this field set to add or drop columns in the target table if you select the Drop and Create table or Alter table in the Target Table Action field. Click to add a new row in the field set. This field set consists of the following fields:
| |||||||||||||
Column | String/Suggestion | Specify the name of the column that you want to load in the target table. You can also specify the columns to drop if you select the Alter table option in the Target Table Action field. | N/A | ID FNAME | ||||||||||
Data Type | String | Activates when you select the Drop and Create table or Alter table options in the Target Table Action field. Specify the data type for the values in the specified column. | N/A | INT VARCHAR | ||||||||||
Modifier | Drop-down list | Activates when you select the Alter table option in the Target Table Action field. Select whether you want to add/drop the specified column. Available options are:
| Add | Drop | ||||||||||
Null Value Behavior | Drop-down list | Select an option to specify how you want the Snap to treat rows with null values in Natural Key or Cause-Historization fields of the source table. The available options are:
Refer to the ELT SCD2 scenarios to learn more about the impact of the selection you make in this field. | Honor nulls as distinct values | Ignore nulls | ||||||||||
Invalid Row Handling | Drop-down list | Select an option to specify how you want the Snap to treat invalid rows, if any, in the source data. A row is considered invalid when it does not result in a new, valid, and coherent SCD2 entry/row in the target table. The available options are:
Refer to the ELT SCD2 scenarios to learn more about the impact of the selection you make in this field. | Ignore invalid rows | Insert duplicate invalid rows | ||||||||||
Target Table Natural Key(s) | Required. This field set enables you to configure the names of fields that identify a unique row in the target table. The identity key cannot be used as the Natural key, since a current row and its historical rows cannot have the same identity value. Click to add a new row in the field set. Specify the value in the following field for each new row added in this field set.
| |||||||||||||
Target Table Natural Key(s) | String/Expression/Suggestion | Specify the name of the Natural Key column from the target table. | N/A | CUSTOMER_ID | ||||||||||
Target Table Cause-Historization Field(s) | Required. This field set enables you to configure the names of fields where any change in value causes the historization of an existing row and the insertion of a new 'current' row. Click to add a new row in the field set. Specify the value in the following field for each new row added in this field set.
| |||||||||||||
Target Table Cause-Historization Field(s) | String/Expression/Suggestion | Required. Specify the name of the historization-causing column from the target table. | N/A | LAST_ORDER_AMT | ||||||||||
Target Table Temporal Field(s) | Required. This field set enables you to configure the historical and updated information for the Cause-historization field. Click to add SCD fields. Specify the values in the following fields for each SCD field row in the field set.
| |||||||||||||
Meaning | Drop-down list | Required. Select one from the following six permitted values for this field in each row. At the minimum, ensure that you configure appropriate values for the first three meanings, only one of the end dates, and the flag in this list.
| Five rows with one list item in each row . | Start date of current row | ||||||||||
Field | String/Expression/Suggestion | Required. Configure the field name from the target table that would contain the historical information. Click to select an item from the suggested list of items.
See the Troubleshooting section in this document and ELT SCD2 Scenarios to ensure that you have configured these fields in your Snap's settings properly.
| N/A | Start_Date End_Date Current_Row Historical_Row | ||||||||||
Value or Expression | String/Expression | Required. Configure the value for each of the fields selected in the Field field.
| N/A | For date fields: For row fields: | ||||||||||
Target Table Sort Field(s) | Required. This field set enables you to sort the records in the target table, by one or more historization fields (for example, by natural key). This mapping is for columns that are not part of the Target Table Temporal Fields. Click to add a sort order. Specify the values in the following fields for each sort order.
| |||||||||||||
Sort Field | String/Expression | Specify the column by which to sort the target table data set. | N/A | start_date GENDER | ||||||||||
Sort Order | Drop-down list | Choose either of the sort orders—ASC (ascending) or DESC (descending)—for the target table data set. | ASC | DESC | ||||||||||
Null Value Sort Preference | Drop-down list | Choose the position for the null values, if any, in the selected column while sorting—NULLS FIRST (at the beginning) or NULLS LAST (at the end).
| NULLS LAST | NULLS FIRST | ||||||||||
Target-Source Column Mapping | This field set enables you to define a one-to-one mapping between the columns in the source and target tables for inserting new rows into the target table. Click to add a new mapping row.
| |||||||||||||
Target Table Column Name | String/Expression/Suggestion | Choose a column from the target table's schema to map with a column from the source data set (schema).
| N/A | ORD_AMT | ||||||||||
Source Column Mapping Expression | String/Expression | Choose a column from the source data set (schema) that corresponds to the respective column in the target table's schema. You can alternatively specify an expression for the source column name.
| N/A | src.ORD_AMT |
...
Error | Reason | Resolution |
---|---|---|
Ambiguous SCD column mapping | You cannot map a field that is configured in Target Table SCD Fields again in the Target-Source Column Mapping field set. | Ensure that the target table column name you specify for mapping with a source column is not already configured as a target table temporal field. |
Invalid target column - source expression mapping | You cannot map a target table column to null or a blank source column value/expression. | If you have specified the target table column as a target table natural key or a target table cause-historization field already in this Snap, ensure that you specify the corresponding source column mapping expression (not null nor left blank). All other columns not mentioned in the Snap can be mapped to null. |
Database cannot be blank. (When seeking the suggested list for Schema Name field) | Suggestions in the Schema Name and Target Table Name fields do not work when you have not specified a valid value for the Database Name field in this Snap. | Specify the target Database Name in this Snap to view and choose from a suggested list in the Schema Name and Target Table Name fields respectively. |
Invalid row flag not configured for target table | Invalid historical row flag is not defined in Target Table Temporal Field(s) field set. | Add an entry in Target Table Temporal Field(s) field set for the Meaning Invalid historical row flag with the corresponding field name in the target table and the boolean value (true or false) or an expression that resolves to a boolean true or false. Make sure that the target table contains the specified field. |
Target table sort column %s not mapped to a source column or an expression | Target table sort fields should be mapped to a source column or an expression in one of the two places: Target Table Temporal Field(s) field set or Target-Source Column Mapping field set. | Map this column either in Target Table Temporal Field(s) field set or Target-Source Column Mapping field set before you can use it for sorting. |
Invalid end date configuration for SCD2 | You have provided both End date of current row and End date of historical row while one of them is adequate. | Remove one of End date of current row and End date of historical row from the Target Table Temporal Field(s) field set and try again. |
Ambiguous source data (Only when you configure the Snap to check for nulls and duplicates.) | Source data contains nulls in one or more of columns you specified in the Target Table Natural Key(s) field set, Target Table Cause-Historization Field(s) field set, or the Start date of current row in Target Table Temporal Field(s) field set. | Peruse your source data to ensure that it is valid and contains valid values required to perform the SCD2 operation and try again. |
Source data has ambiguous records (Only when you configure the Snap to check for nulls and duplicates.) | Source data contains duplicate keys in one or more of columns you specified in the Target Table Natural Key(s) field set, Target Table Cause-Historization Field(s) field set, or the Start date of current row in Target Table Temporal Field(s) field set. | Peruse your source data to ensure that it is valid and contains only unique entries required for the SCD2 operation in these columns and try again. |
Incorrect start date in source data (Only when you configure the Snap to check for nulls and duplicates.) | The column you specified for the Start date of current row in Target Table Temporal Field(s) field set has one or more nulls. | Ensure that your source data or the start date SQL expression resolves to a valid start date and try again. |
Incorrect start date(s) in source data (Only when you configure the Snap to check for nulls and duplicates.) | Multiple rows for one or more Target Table Natural Keys that you defined contain the same start date. | Ensure that your source data contains only unique start dates across the rows for the same natural key. Perform this check for all natural keys you specified, before trying again. |
Scenario: Sort order by a date column in the target table is incorrect after the SCD2 operation is complete. | Sorting the date values in a column with date data type may yield different results when compared to sorting the same date values in a column with string data type. | Ensure that the values loaded in the target table column are of the same data type as defined for the respective column/field. |
SQL exception from Snowflake: Syntax error in one or more positions in the SQL query | Column names in Snowflake tables are case-sensitive. It stores all columns in uppercase unless they are surrounded by quotes during the time of creation in which case, the exact case is preserved. See, Identifier Requirements — Snowflake Documentation. | Ensure that you follow the same casing for the column table names across the Pipeline. |
[Simba][SparkJDBCDriver](500051) ERROR processing query/statement. Error Code: 0 Cannot create table (' (Target CDW: Databricks Lakehouse Platform) | The specified location contains one of the following:
So, the Snap/Pipeline cannot overwrite this table with the target table as needed. | Ensure that you take appropriate action (mentioned below) on the existing table before running your Pipeline again (to create another Delta table at this location). Move or drop the existing table from the schema manually using one of the following commands: Access the DBFS through a terminal and run:
OR Use a Python notebook and run:
|
...