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.