Part 6 — Data at work › Chapter 40

ETL, ELT, warehouses and dbt

SSIS in the Italian market, idempotent loads, watermarks, and why ELT won.

From the advert

“Esperienza nello sviluppo ETL con SSIS” — SSIS is still everywhere in Italian SQL Server shops, and the problems it solves are the same ones dbt and Airflow solve elsewhere.

ETL, ELT, and why the order changed

🧒 Simple

Old way: wash and chop the vegetables on the doorstep, then carry them in. New way: carry everything in first, then wash and chop in the kitchen — because the kitchen turned out to be much better equipped than the doorstep, and because if you chopped something wrong you still have the original.

🎓 Professional

ETL transforms in a separate engine — an SSIS data-flow, a Python script — before loading. ELT loads raw data first and transforms with SQL inside the warehouse. ELT won for three reasons: warehouse compute became cheap and elastic, SQL is reviewable and testable in a way a drag-and-drop data-flow is not, and keeping the raw data means a transformation bug is re-runnable rather than a request to the source system for last month's extract again. That third reason is the one that actually changes your life.

The three layers everybody converges on

Layer	Contains	Rule
Raw / landing	Exactly what arrived, plus when it arrived and from where	Never modified. Append-only. It is your evidence
Staging / cleansed	Typed, deduplicated, normalised keys, obvious rubbish rejected	Rebuildable from raw at any time
Marts	Star schemas the business queries	Rebuildable from staging

✔ The property that makes this worth the extra tables

Every layer is reproducible from the one before it. So a bug found in September in a transformation written in March is fixed by changing the SQL and re-running — not by asking a source system that no longer has the data. Being able to say that sentence is the difference between having built a pipeline and having maintained one.

Incremental loading

Incremental loading: the watermark pattern, with the two details that decide whether it is correct.

-- The watermark pattern, with the two details that matter.
BEGIN;                                                   -- load and marker, together
  INSERT INTO staging.orders (…)                         -- the target…
  SELECT … FROM raw.orders                               -- …from the raw landing table…
  WHERE ingested_at >= (SELECT watermark                 -- …restricted to what is new
                        FROM etl_state WHERE job = 'orders')  -- one marker row per job
  --                 ^ INCLUSIVE, and safe only BECAUSE the load is idempotent
  ON CONFLICT (order_id) DO UPDATE SET …;                -- ← the idempotency itself

  UPDATE etl_state SET watermark = :new_high,            -- move the marker forward…
                       run_at    = now()                 -- …and record when
  WHERE job = 'orders';                                  -- one row per job
  --  ^ SAME transaction: a crash between the two would lose or duplicate rows
COMMIT;                                                  -- both, or neither

The two details, spelled out

>= rather than > deliberately reloads the boundary rows. That is safe because the load is an upsert — reprocessing a row rewrites it with the same values. With a plain INSERT it would be a duplicate-key error every run.
The marker update lives in the same transaction as the load. Split them and a crash between the two either loses rows forever or reprocesses them silently, and neither failure raises an alert.
The pattern depends on the source setting ingested_at monotonically. Clock skew between two application servers is enough to break that, which is the argument for change data capture when correctness really matters.
Extract into a raw landing table first and transform from there. Re-running a transform is then free; re-running an extract may not be possible at all.

⚠ Trap — late-arriving data

Source rows do not always arrive in timestamp order: a system was offline, a transaction committed after you read the maximum, clocks differ. A strictly-greater-than watermark silently loses those rows forever, and nobody notices until an accountant does. The standard defence is an overlapping window — reprocess the last few hours or days every run — which is only affordable because the load is idempotent. Idempotency and late data are the same conversation.

Change data capture

A watermark needs a reliable updated_at, and many source systems do not have one — and none of them record deletes. CDC reads the source's transaction log instead:

Approach	Catches deletes?	Load on source
Timestamp watermark	No	A query per run
Full reload	Yes, implicitly	Very high; only viable when small
Trigger-based capture	Yes	Every write on the source gets slower
Log-based CDC (Debezium, SQL Server CDC)	Yes	Minimal — it reads the log, not the tables

“How do you detect deletes in the source?” is a very good interview question and a surprisingly common gap. A timestamp-based incremental load simply cannot; either you reconcile periodically with a full key comparison, or you use CDC.

Data quality: test the pipeline, not just the code

Test the pipeline, not just the code: the four checks that catch most incidents.

-- Run these AFTER every load. Each one has caused a real incident by being absent.
-- 1. Nothing arrived — the job "succeeded" against an empty extract.
SELECT CASE WHEN COUNT(*) = 0                            -- no rows at all…
            THEN RAISE_FAIL('no rows loaded') END        -- …is a failure, not a quiet success
FROM staging.orders …;                                   -- the table just loaded
-- 2. Volume anomaly: today is 3x or 0.3x the trailing average. Alert, do not fail.
-- 3. Referential: every fact key must exist in its dimension.
SELECT COUNT(*) FROM fact_sales f                        -- count the orphans…
LEFT JOIN dim_customer d ON d.customer_key = f.customer_key   -- …by looking for non-matches…
WHERE d.customer_key IS NULL;                            -- …which must be 0
-- 4. Reconciliation: the mart total equals the source total for the period.

Why these four, and not unit tests

The code can be correct and the data still wrong: an upstream schema change, a truncated extract, a timezone shift. Unit tests do not see any of that, because the input they test with is the input you imagined.
Check 1 is the most valuable line in this chapter. A job that processes zero rows and exits 0 is the most common silent pipeline failure there is.
Check 3 is the LEFT JOIN … IS NULL anti-join of chapter 08: rows on the left with no partner on the right. Its answer must be exactly 0, which makes it a build assertion rather than a report.
RAISE_FAIL stands in for your engine's own error verb — THROW in T-SQL, RAISE EXCEPTION in PL/pgSQL, a non-zero exit in the orchestrator. The point is that a failed check must fail something.
Alert on 2, fail on 1, 3 and 4. A volume anomaly is often a real Monday; an orphan key never is.

dbt made this normal, and that is its real contribution

dbt is SQL SELECT statements plus a dependency graph, materialisation strategies (view / table / incremental), and tests declared beside the model (unique, not_null, relationships, accepted_values). Whether or not you use dbt, the idea to take from it is that a transformation without a test is an assertion nobody checked — and the tests are cheap enough that there is no excuse.

SSIS, honestly

🎓 What to say when the advert names it

It is genuinely good at heterogeneous sources — a flat file, an Excel sheet, an FTP drop, a SOAP service — and at the bulk-load fast path into SQL Server. That is a real problem and it solves it.
It is painful at version control (a .dtsx is XML with layout coordinates, so diffs are unreadable and merges are hopeless), testing, and code reuse.
The pragmatic pattern, and the one worth proposing: use SSIS as a mover — extract and land raw data with minimal transformation — and do the transformation in SQL in stored procedures or dbt, where it can be diffed, reviewed and tested. That is ELT wearing an SSIS coat, and it is a change you can make incrementally inside an existing estate rather than proposing a rewrite.

⚠ Do not walk into an interview disparaging their stack

If the advert says SSIS, somebody there built it and probably still maintains it. “I would use it for extraction and landing, and keep the transformations in SQL so they can be reviewed and tested” is a proposal. “SSIS is legacy rubbish” is a personality.

ELT won because warehouse compute is cheap, SQL is reviewable, and keeping raw data makes a transformation bug re-runnable.
Raw is append-only evidence; staging and marts are rebuildable from it.
Every layer must be reproducible from the one before it. That is the whole design.
Update the watermark inside the load transaction, and use an inclusive comparison.
Late-arriving data silently disappears with a strict watermark. Overlap the window; idempotency makes that affordable.
A timestamp-based load cannot detect deletes. Use CDC, or reconcile keys periodically.
Log-based CDC costs the source almost nothing; trigger-based capture makes every source write slower.
Check every load: non-zero rows, volume anomaly, referential integrity, reconciliation against the source.
A transformation without a test is an assertion nobody checked.
Use SSIS to move data, SQL to transform it. Propose that, do not disparage the stack.

Interview questions this chapter answers

ETL or ELT?

ELT where the warehouse can do the work: transformations become SQL that can be reviewed, tested and re-run, and keeping the raw landing data means a bug found months later is fixed by re-running rather than by asking the source system for data it no longer has. ETL still makes sense when the source is awkward or the volume must be reduced before it moves.

How do you make a nightly load safe to re-run?

Idempotent writes — upsert on a business key, or delete-and-reload the affected slice — and a watermark advanced in the same transaction as the load. Then I can use an overlapping window rather than a strict cutoff, which is what protects against late-arriving rows.

How do you detect that a row was deleted in the source system?

A timestamp watermark cannot — deletes leave no updated row. Either log-based CDC, which reads the source's transaction log and sees deletes as events, or a periodic full reconciliation comparing the key sets. Which one depends on how much load the source can take.