Forum Discussion
SQL Timestamp Incremental Load Issue
An incremental load is implemented based on timestamp, using a view to update a fact table.
• The fact table contains multiple KPIs coming from different source tables.
• Two main KPI tables are included directly in the view and drive the incremental logic (their timestamps change).
• Additional KPIs come from other source tables, which are LEFT JOINed to the view.
During an incremental run:
• Main source tables are updated → timestamp changes
• Other source tables are NOT updated → timestamp unchanged
• Because of the LEFT JOIN, missing values from these tables are returned as NULL
• These NULLs overwrite existing values in the fact table
Example
Fact table (before load)
id app_number score
1 333 5
Source tables
• source1, source2 → timestamps updated (drive incremental load)
• source3 → timestamp unchanged
Stored procedure logic
MERGE fact_table tgt
USING (
SELECT app_number,
ISNULL(score, 0) AS score
FROM vw_main_kpis v
LEFT JOIN source3 s3 ON v.app_number = s3.app_number
) src
ON tgt.app_number = src.app_number
WHEN MATCHED THEN
UPDATE SET tgt.score = src.score;
Result (incorrect)
id app_number score
1 333 0
Existing data is lost.
1 Reply
Your incremental load fires because one table changed, but the other tables you LEFT JOIN to didn’t. Since they didn’t change, they return NULL, and your MERGE/update blindly writes those NULLs over perfectly good existing values. Nothing’s wrong with timestamps or SQL here it’s the load logic. You need to either not update a column when the source value is NULL, or stop driving the whole load off a single timestamp when multiple sources are involved. Been there, it’s an easy trap to fall into.
