Learn

Architecture & pattern guides

Read the idea, build it on a Grid, prove it in a challenge. Short, practical guides — illustrated with RodGrid's own nodes — on system design, design patterns, flow modeling, and how to get the most out of the app.

All articles

ETL/ELT & data orchestration

How data gets from operational systems into the warehouse and stays trustworthy: ETL vs ELT and why the order matters, full vs incremental loads from a high-water mark or CDC, orchestration as a DAG of tasks with dependencies, retries and schedules, quality checks before a table is published, and freshness checks that catch the run that never happened.

Three systems hold one number

A concert-ticketing company wants one figure on a dashboard: net revenue per show, per day. Orders live in the app's PostgreSQL database, 180 million rows of them. Refunds live at the payment provider and come out of its API. Which promoter runs each venue is kept in a CRM. The join between the three exists nowhere, and analytical queries run against the orders database take capacity from the checkout that sells the tickets; Analytics data: OLTP vs OLAP & the warehouse explains why those two workloads do not mix.

So the data moves. A data pipeline extracts rows from each source, transforms them (cleans, joins, computes net revenue) and loads them into the warehouse the dashboard reads. Most teams assemble it from tools with one job each: Fivetran extracts and loads, dbt transforms inside the warehouse, Airflow schedules and orchestrates the steps. It runs on a schedule, every hour or every night, or continuously from a stream of changes.

Transform first, or load first

In ETL, the pipeline transforms on its own compute before loading, so the warehouse only ever receives finished tables: show_revenue, one row per show per day. In ELT, it loads raw copies of orders, refunds and venues, and the transform runs afterwards, as SQL inside the warehouse.

The order decides what can still be done later. Six months in, finance changes the definition: net revenue should now leave out the booking fee. With ELT, the raw orders are still in the warehouse, so the team edits one SQL model and rebuilds the table for every past day. With ETL, the warehouse only ever saw the old numbers, and unless the pipeline kept a raw copy of its extracts, recomputing them means extracting six months again from sources that have changed since, or no longer keep that history. ELT keeps the pipeline simple and lets analysts reshape data with SQL, which is why it is the usual choice when the warehouse is powerful.

ETL still fits when something must not land raw: personal fields the warehouse may not hold are masked or dropped in flight, before any copy exists. Deciding who may read the raw copies ELT keeps falls to Data governance: catalogs, lineage & classification.

Copying only what changed

At first, the orders extract is a full load: every run reads the whole table and replaces the warehouse copy. At launch that is cheap. A year later, each hourly run reads 180 million rows to pick up the 40,000 that changed.

An incremental load reads only the change. The pipeline stores a high-water mark, the largest updated_at it has loaded so far; the next run asks for WHERE updated_at > :mark, merges the rows into the warehouse table by primary key (inserting new orders, updating ones already there), and only after that write succeeds moves the mark to the largest value it loaded. A run that fails before then reads the same rows again, and an order that changed twice arrives as its newest version.

The mark only sees what updated_at says: a deleted order leaves no row to find, and a row stamped before a slow transaction committed can turn up after the mark has already passed it. When deletes and every committed change matter, read the database's log instead: Change data capture turns each insert, update and delete into an event as it commits, and the pipeline loads that stream (computing over the stream as it flows is a job for Stream processing: windows, watermarks & state).

Two ways into the warehouse

A scheduled extract from a high-water mark, or CDC's stream of every committed change. A team usually picks one per source; either way the warehouse merges by primary key.

Tasks that wait for each other

Net revenue needs every load finished before the transform starts. Written as cron entries with a safety gap between them (the clock itself, with its missed and doubled firings, is Scheduled jobs & cron), the transform starts when its slot comes. On the day the refunds extract runs long, it computes revenue over half a day of refunds, and nobody is told.

An orchestrator such as Airflow, Dagster or Prefect replaces the gaps with dependencies. The pipeline becomes a DAG, a directed acyclic graph of tasks: each task starts when the tasks it depends on have succeeded, and with no cycles there is always an order to run them in. The ticketing DAG has six:

  • extract_orders, extract_refunds and extract_venues (venues and promoters, from the CRM) depend on nothing and run in parallel, each loading raw rows.
  • build_show_revenue depends on all three, and runs the dbt model that joins them.
  • check_show_revenue depends on the build, and runs the quality checks on the new rows.
  • publish depends on the checks, and makes the new day visible to the dashboard.

A schedule creates one run per interval (after an outage, the missed ones only when catch-up is on, or through a backfill), and each run carries the interval it covers (Airflow's data interval); a task that selects rows by those bounds reads the same window even if an outage delays Tuesday's run to Thursday, but getting back every change made in that window needs a source that keeps them, such as a change log or a versioned raw copy. Retries are per task: a timeout from the payment API retries extract_refunds after a delay, without rerunning the orders extract that already succeeded. A rerun has to replace what the failed attempt wrote, or merge it by a stable key, not append to it; Batch processing: partitions, shuffles & backfills shows how, and how past days are recomputed after a model changes.

A data DAG is not an application workflow: an order's payment and delivery steps need Durable workflows: orchestration that survives restarts, not a scheduler that rebuilds tables.

A table that loads is not a table that is right

A run can succeed and still publish wrong numbers. Someone adds a venue to the CRM without a promoter; build_show_revenue joins venues to promoters with an inner join, so that venue's shows drop out, every task goes green, and the total is lower than the money that came in.

Checks on load turn assumptions into tests that can fail the run:

  • Volume. Today's row count falls within a range of the same weekday last week; zero rows from a source that normally sends thousands is a failure until someone shows otherwise.
  • Keys. order_id is unique and not null in the loaded orders, after the merge. A duplicate often means a retry appended instead of merging.
  • Relationships. Every refund matches an order, and every show in the loaded orders has a venue and a promoter. Checked against the orders, not the joined result that has already lost those shows, this is the check that catches the new venue.

Where checks run matters as much as what they check: build the new day into a staging table, check it there (dbt ships tests for the key and relationship checks), and only then merge or swap it into the table the dashboard reads, so a failed check stops the run before anyone sees the bad day. A column renamed at the source can arrive as a column of nulls while the load succeeds, so schema changes belong in the same checks; Schema evolution: event formats & compatibility explains how producers change a format without breaking consumers.

The run that never happened

The worst failure produces no error at all. Someone pauses the DAG during a database migration and forgets to unpause it. No run starts, so no task fails, so nothing alerts, and the dashboard keeps showing Thursday's revenue, which was correct on Thursday. Or the runs happen and load nothing: a server with its clock a day ahead wrote one order, the mark jumped to tomorrow, and every run since has asked for rows newer than tomorrow, found none, and succeeded. The rows written before that future time are skipped for good, unless someone resets the mark.

Watching the job only sees runs that exist, so watch the data. A freshness check, run by something other than the pipeline it watches, reads, for each source, the newest source timestamp that made it into the published show_revenue, and alerts when any of them is older than the table's promise, three hours for an hourly table, say, or lies in the future. Use the source's time, not the load's: a table rebuilt on schedule from a source that stopped changing looks fresh by its own load time. Allow for quiet sources, orders at night and venues that can sit unchanged for days, or the alert fires nightly and people stop reading it.

Draw the error paths

ETL Pipeline Flow asks for the run itself as a flowchart: extract, transform, parallel loads that merge, a decision that validates the load, and an error path. Make that decision hold this article's checks (row count, unique keys, every refund matched to an order), and let a failed load and a failed check each reach the error path, so the summary document reports a stopped run. That challenge is ETL by design; in ELT the transform step would move after the load, into the warehouse.

Practice this