Expressions for End Date of Historical Row field

In this article

1 Azure Synapse
2 BigQuery
3 Databricks Lakehouse Platform (DLP)
4 Redshift
5 Snowflake

Using a fixed (date or timestamp) value for the End date of Historical Row may lead the Snap to unexpected behaviors like incorrect data loads or Snap errors. Hence, we recommend that you enable the Expression button to and specify one of the following expressions:

Before using the expressions below, make sure that you consider the following:

^# You can tailor the expression for Timestamp data type to match the sort column(s) and sort order mentioned in the ELT SCD2 Snap. Refer to the example expressions for the Date data type.

^ When start_date is not specified as the sort order column in the Snap configuration, the Snap automatically first sorts the rows in the ascending order of their start dates, followed by the other sort order columns specified. Hence, while you define the expression, you must include the start_date as the sort order column before mentioning the other sort order columns.

In case of timestamps, the above expressions consider adding 'n' hours. If your preference is different from this, you may tailor the respective expression to suit your needs.

Azure Synapse

Data Type of End date of Historical Row	Sort Column defined in Snap	Sort Order	Expression for End Date of Historical Row^#

Data Type of End date of Historical Row	Sort Column defined in Snap	Sort Order	Expression for End Date of Historical Row^#
Date	START_DATE	Ascending	`DATEADD(DAY, -1, CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY <natural_key> ORDER BY src.START_DATE ASC) = 1 AND operation_type = 'MERGEINTO UPDATE' THEN src.START_DATE` `WHEN LEAD(src.START_DATE) OVER (PARTITION BY <natural_key> ORDER BY src.START_DATE ASC) IS NULL THEN '9999-12-31'` `ELSE LEAD(src.START_DATE) OVER (PARTITION BY <natural_key> ORDER BY src.START_DATE ASC, src.ADDRESS DESC) END)`
Date	START_DATE	Descending	`DATEADD(DAY, -1, CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY <natural_key> ORDER BY src.START_DATE DESC) = 1 AND operation_type = 'MERGEINTO UPDATE' THEN src.START_DATE` `WHEN LEAD(src.START_DATE) OVER (PARTITION BY <natural_key> ORDER BY src.START_DATE DESC) IS NULL THEN '9999-12-31'` `ELSE LEAD(src.START_DATE) OVER (PARTITION BY <natural_key> ORDER BY src.START_DATE DESC) END)`
Date^	Any other <SORT_COL>	Any <SORT_ORDER>	`DATEADD(DAY, -1, CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY <natural_key> ORDER BY src.START_DATE ASC, src.<SORT_COL> <SORT_ORDER>) = 1 AND operation_type = 'MERGEINTO UPDATE' THEN src.START_DATE` `WHEN LEAD(src.START_DATE) OVER (PARTITION BY <natural_key> ORDER BY src.START_DATE ASC, src.<SORT_COL> <SORT_ORDER>) IS NULL THEN '9999-12-31'` `ELSE LEAD(src.START_DATE) OVER (PARTITION BY <natural_key> ORDER BY src.START_DATE ASC, src.<SORT_COL> <SORT_ORDER>) END)`
DateTime^#	START_DATE	Ascending	`DATEADD(HOUR, -1, CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY <natural_key> ORDER BY src.START_DATE ASC) = 1 AND operation_type = 'MERGEINTO UPDATE' THEN src.START_DATE` `WHEN LEAD(src.START_DATE)OVER (PARTITION BY <natural_key> ORDER BY src.START_DATE ASC) IS NULL THEN '2030-01-01 00:00:00.000000000'` `ELSE LEAD(src.START_DATE) OVER (PARTITION BY <natural_key> ORDER BY src.START_DATE ASC) END)`

BigQuery

Data Type of End date of Historical Row	Sort Column defined in Snap	Sort Order	Expression for End Date of Historical Row^#

Data Type of End date of Historical Row	Sort Column defined in Snap	Sort Order	Expression for End Date of Historical Row^#
Date	start_date, address	Ascending	`DATE_ADD((CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY <natural_key> ORDER BY src.start_date ASC NULLS FIRST, src.address ASC NULLS LAST) = 1 AND operation_type = 'MERGEINTO UPDATE' THEN src.start_date` `WHEN LEAD(src.start_date) OVER (partition by <natural_key> ORDER BY src.start_date ASC NULLS FIRST, src.address ASC NULLS LAST) IS NULL THEN '9999-12-31'` `ELSE LEAD(src.start_date) OVER (partition by <natural_key> ORDER BY src.start_date ASC NULLS FIRST, src.address ASC NULLS LAST) END), INTERVAL -1 DAY)`
Date	start_date, address	Descending	`DATE_ADD((CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY <natural_key> ORDER BY src.start_date DESC NULLS FIRST, src.address ASC NULLS LAST) = 1 AND operation_type = 'MERGEINTO UPDATE' THEN src.start_date` `WHEN LEAD(src.start_date) OVER (partition by <natural_key> ORDER BY src.start_date DESC NULLS FIRST, src.address ASC NULLS LAST) IS NULL THEN '9999-12-31'` `ELSE LEAD(src.start_date) OVER (partition by <natural_key> ORDER BY src.start_date DESC NULLS FIRST, src.address ASC NULLS LAST) END), INTERVAL -1 DAY)`
Date^	Any other <SORT_COL1>, <SORT_COL2>	Any <SORT_ORDER>	`DATE_ADD((CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY <natural_key> ORDER BY src.<SORT_COL1> <SORT_ORDER> <NULLS_ORDER>, src.<SORT_COL2> <SORT_ORDER> <NULLS_ORDER>) = 1 AND operation_type = 'MERGEINTO UPDATE' THEN src.start_date` `WHEN LEAD(src.<SORT_COL1>) OVER (partition by <natural_key> ORDER BY src.<SORT_COL1> <SORT_ORDER> <NULLS_ORDER>, src.<SORT_COL2> <SORT_ORDER> <NULLS_ORDER>) IS NULL THEN '9999-12-31'` `ELSE LEAD(src.start_date) OVER (partition by <natural_key> ORDER BY src.<SORT_COL1> <SORT_ORDER> <NULLS_ORDER>, src.<SORT_COL2> <SORT_ORDER> <NULLS_ORDER>) END), INTERVAL -1 DAY)`
Timestamp^#	START_DATE	Ascending	`TIMESTAMP_ADD((CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY src.customer_id ORDER BY src.start_date ASC) = 1 AND operation_type = 'MERGEINTO UPDATE' THEN src.start_date` `WHEN LEAD(src.start_date) OVER (partition by src.customer_id ORDER BY src.start_date ASC) IS NULL THEN '9999-01-01 00:00:00.000000'` `ELSE LEAD(src.start_date) OVER (partition by src.customer_id ORDER BY src.start_date ASC) END), INTERVAL -1 HOUR)`

Databricks Lakehouse Platform (DLP)

Data Type of End date of Historical Row	Sort Column defined in Snap	Sort Order	Expression for End Date of Historical Row^#

Data Type of End date of Historical Row	Sort Column defined in Snap	Sort Order	Expression for End Date of Historical Row^#
Date	START_DATE	Ascending	`DATEADD (DAY, -1, CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY <natural_key> ORDER BY START_DATE ASC NULLS FIRST) = 1 THEN src.START_DATE` `WHEN LEAD (src.START_DATE) OVER (partition by <natural_key> ORDER BY START_DATE ASC NULLS FIRST) IS NULL THEN '9999-12-31'` `ELSE LEAD (src.START_DATE) OVER (partition by <natural_key> ORDER BY START_DATE ASC NULLS FIRST) END)`
Date	START_DATE	Descending	`DATEADD (DAY, -1, CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY <natural_key> ORDER BY START_DATE DESC NULLS FIRST) = 1 THEN src.START_DATE` `WHEN LEAD (src.START_DATE) OVER (partition by <natural_key> ORDER BY START_DATE DESC NULLS FIRST) IS NULL THEN '9999-12-31'` `ELSE LEAD (src.START_DATE) OVER (partition by <natural_key> ORDER BY START_DATE DESC NULLS FIRST) END)`
Date^	Any other <SORT_COL>	Any <SORT_ORDER>	`DATEADD (DAY, -1, CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY <natural_key> ORDER BY <SORT_COL> <SORT_ORDER> <NULLS_ORDER>) = 1 THEN src.START_DATE` `WHEN LEAD (src.START_DATE) OVER (partition by <natural_key> ORDER BY <SORT_COL> <SORT_ORDER> <NULLS_ORDER>) IS NULL THEN '9999-12-31'` `ELSE LEAD (src.START_DATE) OVER (partition by <natural_key> ORDER BY <SORT_COL> <SORT_ORDER> <NULLS_ORDER>) END)`
Timestamp^#	START_DATE	Ascending	`TIMESTAMPADD(HOUR, -1, CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY src.CUSTOMER_ID ORDER BY src.START_DATE ASC NULLS FIRST) = 1 THEN src.START_DATE` `WHEN LEAD(src.START_DATE) OVER (partition by src.CUSTOMER_ID ORDER BY src.START_DATE ASC NULLS FIRST) IS NULL THEN '2030-01-01 00:00:00.000000000'` `ELSE LEAD(src.START_DATE) OVER (partition by src.CUSTOMER_ID ORDER BY src.START_DATE ASC NULLS FIRST) END)`

Redshift

Data Type of End date of Historical Row	Sort Column defined in Snap	Sort Order	Expression for End Date of Historical Row^#

Data Type of End date of Historical Row	Sort Column defined in Snap	Sort Order	Expression for End Date of Historical Row^#
Date	START_DATE	Ascending	`DATEADD(DAY, -1, CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY <natural_key> ORDER BY src.START_DATE ASC NULLS FIRST, src.address DESC NULLS LAST) = 1 AND operation_type = 'MERGEINTO UPDATE' THEN src.START_DATE` `WHEN LEAD(src.START_DATE) OVER (PARTITION by <natural_key> ORDER BY src.START_DATE ASC NULLS FIRST, src.address DESC NULLS LAST) IS NULL THEN '9999-12-31'` `ELSE LEAD(src.START_DATE) OVER (PARTITION by <natural_key> ORDER BY src.START_DATE ASC NULLS FIRST, src.address DESC NULLS LAST) END)`
Date	START_DATE	Descending	`DATEADD(DAY, -1, CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY <natural_key> ORDER BY src.START_DATE DESC NULLS FIRST, src.address DESC NULLS LAST) = 1 AND operation_type = 'MERGEINTO UPDATE' THEN src.START_DATE` `WHEN LEAD(src.START_DATE) OVER (PARTITION by <natural_key> ORDER BY src.START_DATE DESC NULLS FIRST, src.address DESC NULLS LAST) IS NULL THEN '9999-12-31'` `ELSE LEAD(src.START_DATE) OVER (PARTITION by <natural_key> ORDER BY src.START_DATE DESC NULLS FIRST, src.address DESC NULLS LAST) END)`
Date^	Any other <SORT_COL>	Any <SORT_ORDER>	`DATEADD(DAY, -1, CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY <natural_key> ORDER BY src.<SORT_COL1> <SORT_ORDER> <NULLS_ORDER>, src.<SORT_COL2> <SORT_ORDER> <NULLS_ORDER>) = 1 AND operation_type = 'MERGEINTO UPDATE' THEN src.START_DATE` `WHEN LEAD(src.START_DATE) OVER (PARTITION by <natural_key> ORDER BY src.<SORT_COL1> <SORT_ORDER> <NULLS_ORDER>, src.<SORT_COL2> <SORT_ORDER> <NULLS_ORDER>) IS NULL THEN '9999-12-31'` `ELSE LEAD(src.START_DATE) OVER (PARTITION by <natural_key> ORDER BY src.<SORT_COL1> <SORT_ORDER> <NULLS_ORDER>, src.<SORT_COL2> <SORT_ORDER> <NULLS_ORDER>) END)`
DateTime^#	START_DATE	Ascending	`DATEADD(HOUR, -1, CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY src.CUSTOMER_ID ORDER BY src.START_DATE ASC NULLS FIRST, src.address DESC NULLS LAST) = 1 AND operation_type = 'MERGEINTO UPDATE' THEN src.START_DATE` `WHEN LEAD(src.START_DATE) OVER (PARTITION BY src.CUSTOMER_ID ORDER BY src.START_DATE ASC NULLS FIRST, src.address DESC NULLS LAST) IS NULL THEN '9999-01-01 00:00:00.000000'` `ELSE LEAD(src.START_DATE) OVER (PARTITION BY src.CUSTOMER_ID ORDER BY src.START_DATE ASC NULLS FIRST, src.address DESC NULLS LAST) END)`

Snowflake

Data Type of End date of Historical Row	Sort Column defined in Snap	Sort Order	Expression for End Date of Historical Row^#

Data Type of End date of Historical Row	Sort Column defined in Snap	Sort Order	Expression for End Date of Historical Row^#
Date	START_DATE	Ascending	`DATEADD(DAY, -1, CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY <natural_key> ORDER BY src.START_DATE ASC NULLS FIRST) = 1 AND operation_type = 'MERGEINTO UPDATE' THEN src.START_DATE` `WHEN LEAD(src.START_DATE) OVER (partition by <natural_key> ORDER BY src.START_DATE ASC NULLS FIRST) IS NULL THEN '9999-12-31'` `ELSE LEAD(src.START_DATE) OVER (partition by <natural_key> ORDER BY src.START_DATE ASC NULLS FIRST) END)`
Date	START_DATE	Descending	`DATEADD(DAY, -1, CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY <natural_key> ORDER BY src.START_DATE DESC NULLS FIRST) = 1 AND operation_type = 'MERGEINTO UPDATE' THEN src.START_DATE` `WHEN LEAD(src.START_DATE) OVER (partition by <natural_key> ORDER BY src.START_DATE DESC NULLS FIRST) IS NULL THEN '9999-12-31'` `ELSE LEAD(src.START_DATE) OVER (partition by <natural_key> ORDER BY src.START_DATE DESC NULLS FIRST) END)`
Date^	Any other <SORT_COL>	Any <SORT_ORDER>	`DATEADD(DAY, -1, CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY <natural_key> ORDER BY src.<SORT_COL> <SORT_ORDER> <NULLS_ORDER>) = 1 AND operation_type = 'MERGEINTO UPDATE' THEN src.START_DATE` `WHEN LEAD(src.<SORT_COL>) OVER (partition by <natural_key> ORDER BY src.<SORT_COL> <SORT_ORDER> <NULLS_ORDER>) IS NULL THEN '9999-12-31'` `ELSE LEAD(src.START_DATE) OVER (partition by <natural_key> ORDER BY src.<SORT_COL> <SORT_ORDER> <NULLS_ORDER>) END)`
Timestamp^#	START_DATE	Ascending	`TIMESTAMPADD(hour, -1, CASE` `WHEN ROW_NUMBER() OVER (PARTITION BY src.CUSTOMER_ID ORDER BY src.START_DATE ASC NULLS FIRST) = 1 AND OPERATION_TYPE = 'MERGEINTO UPDATE' THEN src.START_DATE` `WHEN LEAD(src.START_DATE) OVER (partition by src.CUSTOMER_ID ORDER BY src.START_DATE ASC NULLS FIRST) IS NULL THEN '9999-01-01 00:00:00.000000000'` `ELSE LEAD(src.START_DATE) OVER (partition by src.CUSTOMER_ID ORDER BY src.START_DATE ASC NULLS FIRST) END)`