Issue: SCD_TYPE_2_BY_TIME on BigQuery incorrectly tracks changes of NULL values
SQLMesh version: 0.216.0
Gateway: BigQuery
Description:
When using an SCD_TYPE_2_BY_TIME model kind on a BigQuery backend, NULL values in a source record are being incorrectly filled with non-NULL values from a subsequent update for the same unique_key. This behavior appears to be caused by the use of COALESCE in the generated merge statement, which does not preserve the intended NULL values from the source data.
Steps to reproduce:
- Define the following SCD_TYPE_2_BY_TIME model:
MODEL (
name project.target_model,
start '2024-01-01',
columns (
identifier STRING,
a_nullable_value STRING,
another_nullable_value STRING,
updated_at TIMESTAMP
),
kind SCD_TYPE_2_BY_TIME (
unique_key identifier,
updated_at_name updated_at,
time_data_type TIMESTAMP,
batch_size 1,
forward_only false
),
partitioned_by TIMESTAMP_TRUNC(valid_from, DAY),
cron '@daily'
);
SELECT
identifier,
a_nullable_value,
another_nullable_value,
updated_at
FROM source-data.source_dataset.source_table
WHERE
_PARTITIONTIME BETWEEN @start_ds AND @end_ds
- Provide the following source data in
source-data.source_dataset.source_table:
identifier a_nullable_value another_nullable_value updated_at _PARTITIONTIME
'aaa' (null) 'C' 2024-02-03 17:25:04.000000 UTC 2024-02-03 00:00:00.000000 UTC
'aaa' 'A' 'C' 2024-08-07 05:45:58.000000 UTC 2024-08-07 00:00:00.000000 UTC
'aaa' 'B' 'D' 2025-02-06 14:41:54.000000 UTC 2025-02-06 00:00:00.000000 UTC
- Run a
sqlmesh plan to create and populate the target model.
Expected behavior:
The initial record from 2024-02-03 should retain its NULL value for the a_nullable_value column.
The resulting project.target_model table should contain the following entries:
identifier a_nullable_value another_nullable_value updated_at valid_from valid_to
'aaa' (null) 'C' 2024-02-03 17:25:04.000000 UTC 2024-02-03 17:25:04.000000 UTC 2024-08-07 05:45:58.000000 UTC
'aaa' 'A' 'C' 2024-08-07 05:45:58.000000 UTC 2024-08-07 05:45:58.000000 UTC 2025-02-06 14:41:54.000000 UTC
'aaa' 'B' 'D' 2025-02-06 14:41:54.000000 UTC 2025-02-06 14:41:54.000000 UTC (null)
Actual Behavior:
The NULL value in the a_nullable_value column for the first record is replaced by the value 'A' from the subsequent record.
The table is populated with the following incorrect data:
identifier a_nullable_value another_nullable_value updated_at valid_from valid_to
'aaa' 'A' 'C' 2024-02-03 17:25:04.000000 UTC 2024-02-03 17:25:04.000000 UTC 2024-08-07 05:45:58.000000 UTC
'aaa' 'A' 'C' 2024-08-07 05:45:58.000000 UTC 2024-08-07 05:45:58.000000 UTC 2025-02-06 14:41:54.000000 UTC
'aaa' 'B' 'D' 2025-02-06 14:41:54.000000 UTC 2025-02-06 14:41:54.000000 UTC (null)
Possible Cause:
The issue likely stems from the generated CREATE OR REPLACE TABLE statement, which uses COALESCE on all columns.
This logic incorrectly backfills NULLs with values from later records during the join operation.
Relevant Query Snippet:
SELECT
COALESCE(`joined`.`t_identifier`, `joined`.`identifier`) AS `identifier`,
COALESCE(`joined`.`t_a_nullable_value`, `joined`.`a_nullable_value`) AS `a_nullable_value`,
COALESCE(`joined`.`t_another_nullable_value`, `joined`.`another_nullable_value`) AS `another_nullable_value`,
COALESCE(`joined`.`t_updated_at`, `joined`.`updated_at`) AS `updated_at`,
CASE
WHEN `t_valid_from` IS NULL AND NOT `latest_deleted`.`_exists` IS NULL
THEN
CASE
WHEN `latest_deleted`.`valid_to` > `updated_at`
THEN `latest_deleted`.`valid_to`
ELSE `updated_at`
END
WHEN `t_valid_from` IS NULL
THEN `updated_at`
ELSE `t_valid_from`
END AS `valid_from`,
CASE
WHEN `joined`.`updated_at` > `joined`.`t_updated_at`
THEN `joined`.`updated_at`
ELSE `t_valid_to`
END AS `valid_to`
FROM `joined`
LEFT JOIN `latest_deleted` ON `joined`.`identifier` = `latest_deleted`.`_key0`
Issue:
SCD_TYPE_2_BY_TIMEon BigQuery incorrectly tracks changes of NULL valuesSQLMesh version: 0.216.0
Gateway: BigQuery
Description:
When using an
SCD_TYPE_2_BY_TIMEmodel kind on a BigQuery backend, NULL values in a source record are being incorrectly filled with non-NULL values from a subsequent update for the same unique_key. This behavior appears to be caused by the use ofCOALESCEin the generated merge statement, which does not preserve the intended NULL values from the source data.Steps to reproduce:
source-data.source_dataset.source_table:sqlmesh planto create and populate the target model.Expected behavior:
The initial record from
2024-02-03should retain its NULL value for thea_nullable_valuecolumn.The resulting
project.target_modeltable should contain the following entries:Actual Behavior:
The NULL value in the
a_nullable_valuecolumn for the first record is replaced by the value'A'from the subsequent record.The table is populated with the following incorrect data:
Possible Cause:
The issue likely stems from the generated
CREATE OR REPLACE TABLEstatement, which usesCOALESCEon all columns.This logic incorrectly backfills NULLs with values from later records during the join operation.
Relevant Query Snippet: