Incremental Loads and Slowly Changing Dimensions

These are standard data-warehousing patterns for keeping a target table in sync with a constantly-changing source, without reprocessing everything from scratch every time.

Incremental Loads — Only Process What Changed

Instead of reloading an entire source table daily, track a watermark (usually a timestamp or an incrementing ID) and only pull rows newer than the last successful load.

CREATE TABLE load_watermark (
  source_name TEXT PRIMARY KEY,
  last_loaded_at TIMESTAMPTZ
);

-- pull only what's new since the last run
SELECT * FROM orders
WHERE updated_at > (
  SELECT last_loaded_at FROM load_watermark WHERE source_name = 'orders'
);

-- after a successful load, advance the watermark
UPDATE load_watermark
SET last_loaded_at = now()
WHERE source_name = 'orders';

This is dramatically cheaper than a full reload once a source table reaches millions of rows.

Slowly Changing Dimension Type 1 — Overwrite

When history doesn't matter, just overwrite the old value — this is exactly what INSERT ... ON CONFLICT DO UPDATE (see the Upsert lesson) gives you for free.

INSERT INTO dim_customer (customer_id, name, city)
VALUES (101, 'Rahul Gupta', 'Pune')
ON CONFLICT (customer_id)
DO UPDATE SET name = EXCLUDED.name, city = EXCLUDED.city;
-- if Rahul moves to Mumbai, the old city "Pune" is simply gone

Slowly Changing Dimension Type 2 — Preserve History

When you do need to know what a value used to be (e.g. "which region was this order attributed to at the time it was placed?"), Type 2 keeps every version as its own row, with validity dates.

CREATE TABLE dim_customer (
  surrogate_key SERIAL PRIMARY KEY,
  customer_id INTEGER,
  name TEXT,
  city TEXT,
  valid_from TIMESTAMPTZ NOT NULL DEFAULT now(),
  valid_to TIMESTAMPTZ,
  is_current BOOLEAN DEFAULT true
);

-- Rahul moves from Pune to Mumbai: close out the old record, insert a new current one
BEGIN;

UPDATE dim_customer
SET valid_to = now(), is_current = false
WHERE customer_id = 101 AND is_current = true;

INSERT INTO dim_customer (customer_id, name, city)
VALUES (101, 'Rahul Gupta', 'Mumbai');

COMMIT;

-- "what city was Rahul in on a given historical date?"
SELECT city FROM dim_customer
WHERE customer_id = 101
  AND '2024-03-15'::TIMESTAMPTZ BETWEEN valid_from AND COALESCE(valid_to, 'infinity');

Type 1 vs Type 2

	SCD Type 1	SCD Type 2
History preserved	No — overwrites	Yes — every version kept as a row
Table size	Stays constant	Grows with every change
Use case	Fixing typos, values with no analytical meaning	Anything analyzed "as it was at the time"

Common Errors & Troubleshooting

• Incremental load missing rows updated exactly at the watermark boundary — using > instead of >= (or vice versa) at the boundary can silently skip or double-process a row updated in the same instant as the last load; be precise and consistent about the boundary condition.
• SCD Type 2 ending up with two "current" rows for the same entity — this means the UPDATE ... SET is_current = false step was missed or failed before the new row was inserted; always wrap both steps in a single transaction (as shown above) so they succeed or fail together.
• Watermark never advancing after a failed load — this is actually the correct, safe behavior — if the load fails, the watermark should stay put so the next run retries the same window rather than skipping data.

Key Takeaways

• Incremental loading tracks a watermark (last-loaded timestamp/ID) so daily jobs only process new/changed rows, not the whole source.
• SCD Type 1 overwrites and forgets history; SCD Type 2 keeps every version with valid_from/valid_to dates.
• Type 2 tables are exactly how a data warehouse answers "what was true at that point in time," not just "what's true right now."