Week 4 · Load to the warehouse › Day 24

Day 24. Loading patterns that survive reruns

1. Why this matters

The load step is where "ran twice" turns into "revenue doubled". Today you learn the four patterns production teams use, and when each is right.

2. Pattern 1: staging + MERGE (upsert by key)

Use when rows have a stable business key and can change (ad stats in the look-back window, bookings).

-- 1) Python bulk-loads into a session staging table: stg.ads_daily_load (truncated first)
-- 2) one transaction merges it
begin tran;
merge mart.fct_ads_daily with (holdlock) as t
using (select * from stg.ads_daily_load) as s
   on t.ad_date = s.ad_date and t.campaign_id = s.campaign_id
when matched and (t.spend <> s.spend or t.clicks <> s.clicks or t.conversions <> s.conversions) then
  update set spend = s.spend, clicks = s.clicks, impressions = s.impressions,
             conversions = s.conversions, conversion_value = s.conversion_value, updated_at = sysutcdatetime()
when not matched by target then
  insert (ad_date, campaign_id, spend, clicks, impressions, conversions, conversion_value, updated_at)
  values (s.ad_date, s.campaign_id, s.spend, s.clicks, s.impressions, s.conversions, s.conversion_value, sysutcdatetime());
commit;

holdlock avoids a known race where two concurrent merges insert the same key. Many SQL Server engineers prefer explicit update then insert ... where not exists inside one transaction: easier to read and debug, same result. Postgres uses insert ... on conflict (keys) do update.

3. Pattern 2: delete-insert a partition

Use when the source gives you the complete set for a slice (a whole business day of POS lines, a full day of ad stats).

begin tran;
delete from mart.fct_qsr_lines where business_date = @d;
insert into mart.fct_qsr_lines (...) select ... from stg.qsr_lines_load where business_date = @d;
commit;

Simple, idempotent, and handles deletions in the source (rows that vanish from the re-sent day vanish from the target). For very large tables, SQL Server partition switching does the same as a metadata operation.

4. Pattern 3: append-only with dedup on read

Raw tables: append every load with load_id and loaded_at, never update. Staging views pick the latest per key (Day 12). Cheapest to write, full history, but reads must dedup.

5. Pattern 4: full rebuild + swap

Small dimensions (stores, campaigns, menu): rebuild into mart.dim_campaign__new, validate, then swap names in one transaction so readers see either old or new.

begin tran;
exec sp_rename 'mart.dim_campaign', 'dim_campaign__old';
exec sp_rename 'mart.dim_campaign__new', 'dim_campaign';
commit;
drop table mart.dim_campaign__old;

(In Postgres: alter table ... rename in a transaction, or create or replace view over versioned tables.)

6. Watermarks and the audit table

create table ops.watermark (source sysname primary key, high_water datetime2 not null, updated_at datetime2 not null);
create table ops.load_audit (load_id uniqueidentifier primary key default newid(), asset sysname, partition_key varchar(32),
  rows_staged int, rows_inserted int, rows_updated int, rows_deleted int,
  started_at datetime2, finished_at datetime2, status varchar(16), error nvarchar(4000));

Update the watermark in the same transaction as the data. If the data commit fails, the watermark doesn't move, and the next run retries the same window.