design·lab

Data & storage

ETL Pipelines

Raw data from different systems is messy and disconnected — ETL/ELT pipelines extract it, clean and reshape it, then load it somewhere queryable on a repeatable schedule.

✗ The problem

Raw source data doesn't line up

Every source has its own schema, its own quirks, and no shared keys. You can't just query across them — the data isn't ready to analyze.

// CRM export
{ id: "C-1", name: "alice j", joined: "01/06/2024" }

// Web signup log — different schema!
{ user_id: 1, full_name: "Bob K", signup_date: "2024-01-05" }
{ user_id: 1, full_name: "Bob K", signup_date: "2024-01-05" } // duplicate
{ user_id: 2, full_name: "eve s",  signup_date: null }
Mismatched field names, mixed date formats, a duplicate row, a missing value — and no way to join CRM rows to web rows. Not analytics-ready.
✓ How it works

Extract → Transform → Load

Extract pulls rows from every source. Transform cleans, dedupes, joins, and conforms types. Load writes the result into the warehouse.

// ETL — transform BEFORE loading
extract(sources)
  .then(rows  => transform(rows))   // clean · dedupe · join
  .then(clean => load(warehouse, clean));
Sources
CRM · web · CSV
→
Extract
→
Transform
clean·dedupe·join
→
Load
→
Warehouse
Modern cloud warehouses are cheap to scan, so many teams flip the order to ELT: load the raw rows first, then run the transform inside the warehouse with SQL.
✓ See it live

Run the pipeline — watch rows get cleaned

Click through Extract → Transform → Load. Watch the raw rows shrink and clean up one stage at a time.

Source
→
Extract
→
Transform
→
Load
→
Warehouse

idnamesignup_dateemail
Click "Run pipeline ▶" to extract raw rows…
Idle — nothing extracted yet
✓ Takeaway

Repeatable, reliable data movement

  • ETL vs ELT: transform-before-load (ETL) suits limited-compute targets; load-then-transform (ELT) suits cheap-to-scan cloud warehouses.
  • Idempotent + incremental: re-running a load shouldn't duplicate rows; pull only what changed since the last run.
  • Data quality checks: validate row counts, null rates, and schema before trusting a load.
  • Scheduled, not manual: pipelines run on a cron/orchestrator so the warehouse stays fresh.
  • Relates to the warehouse it feeds, a data-driven approach to decisions, and queues for streaming ingestion.
Back to all topics →