wunder beta

📘 How does an ETL pipeline stay reliable?

Extraction is the first stage of a pipeline: reading raw data from one or more source systems so it can be moved

7
lessons
~30 min
to learn
Adults
level
Start the course →

What you’ll learn

  1. Extract: Pulling Data From SourcesExplain how pipelines extract data from databases, APIs, files, and logs, and choose between batch, incremental, and CDC strategies.Extraction reads raw data from source systems without transforming it, preserving an immutable copy for auditing and reprocessing. Full extracts re-read everything while incremental extracts read only changes since a watermark. Log-based change data capture streams inserts, updates, and deletes from the transaction log, capturing deletes and avoiding query load on the source. API and file sources add concerns like pagination, rate limits, and restartability. Capturing metadata such as load time and watermark underpins reliable, resumable pipelines.
  2. Transform: Cleaning and ReshapingDescribe the core transformations (cleaning, type casting, deduplication, joins, aggregation, business logic) that turn raw data into analysis-ready data.Transformation converts messy raw data into clean, consistent, trustworthy data. Cleaning and type casting standardize values and surface bad data early, while deduplication keeps one canonical row per business key using a latest-wins ordering column. Joins enrich records and aggregation summarizes them at a precisely defined grain to avoid double counting. Encoding business logic centrally ensures every report shares the same definitions. This stage holds most of a pipeline's complexity and data-quality value.
  3. Load: Writing to the DestinationCompare full-refresh, incremental, and upsert load strategies and match them to data mutability.Loading writes transformed data into the destination, and the strategy chosen determines correctness on reruns. A full refresh rebuilds the whole table, self-correcting but costly at scale. Incremental loads write only changes, and upserts (merges) update existing rows by key and insert new ones, which is needed when sources emit updates and deletes. Append-only loading suits immutable events but cannot represent updates. Matching the load pattern to the data's mutability is the central decision.
  4. ETL vs ELTContrast the ordering of ETL and ELT and explain why cloud warehouses shifted toward transforming in-warehouse.ETL transforms data on a separate engine before loading final results into the warehouse, while ELT loads raw data first and transforms it in place inside the warehouse. The ELT order keeps raw data available so transformations can be revised and rerun. Cloud warehouses separated storage from compute and made raw storage cheap, which made in-warehouse transformation economical and drove the shift to ELT. Tools like dbt embody ELT by running version-controlled SQL models in the warehouse. ETL still fits when data must be cleaned or masked before landing, or when heavy engines like Spark are warranted.
  5. Reliability: Idempotency and ReprocessingDefine idempotency and design loads, backfills, and late/duplicate-data handling so reruns are always safe.An idempotent operation yields the same final state whether run once or many times, which is what makes automatic retries safe. Idempotent loads overwrite a defined partition or upsert by key instead of blindly appending, since appends duplicate on retry. Partition-based design makes backfills safe by letting each period rebuild independently, and retaining raw data makes backfills possible. Late data is handled by reopening and rebuilding the affected partition, and duplicate delivery is neutralized by upserting or deduplicating on a business key. Designing for at-least-once delivery and then deduplicating is more robust than assuming exactly-once.
  6. Data Quality, Contracts, and ObservabilityManage schema evolution, data contracts, quality checks, partitioning, and observability to keep pipelines trustworthy.Source schemas evolve, so pipelines must handle additive changes safely and treat removals, renames, and type changes as breaking. Data contracts make schema and semantics an explicit, versioned agreement between producers and consumers. Quality checks validate nulls, uniqueness, ranges, and referential integrity, failing fast before bad data reaches dashboards. Partitioning by a column like date enables efficient incremental processing and partition-level reprocessing. Observability and lineage reveal freshness, failures, and how outputs derive from sources, enabling root-cause analysis.
  7. Build a Pipeline: SimulationAssemble and operate an end-to-end pipeline, making extract, transform, load, and reliability decisions scored for correctness.This capstone simulation has the learner configure a full pipeline and respond to realistic failures. Keeping a warehouse in sync with a mutable operational source rewards log-based CDC plus upsert loads so updates and deletes propagate. A failed nightly retry rewards idempotent, partition-overwriting design, and late data rewards reopening the affected partition. A breaking upstream schema change rewards a data-contract response over a silent patch. The final score combines stage correctness, reliability under retries and late data, and protection of downstream consumers.

Questions this course answers

Why is log-based change data capture (CDC) often preferred over polling an updated-at timestamp to extract changes from an operational database?

Log-based CDC reads the transaction log, so it captures inserts, updates, and deletes (including hard deletes that timestamp polling misses) without running queries against the source tables.

What primarily distinguishes an incremental extract from a full (batch) extract?

Incremental extraction reads only the slice of data changed since the last run, typically using a high-water-mark column such as an updated-at timestamp or increasing id, rather than re-reading everything.

Why should the extract stage avoid transforming data?

Keeping extraction free of transformation preserves an immutable raw copy, which enables auditing and full reprocessing if transformation logic later changes.

When deduplicating records that share a business key, what is the standard way to choose which row to keep?

Deduplication keeps one canonical row per business key, typically the most recent version chosen by an ordering column like updated-at, so real updates are preserved and stale rows are dropped.

Why is defining the aggregation grain precisely important?

The grain (the level of detail one row represents) defines what the aggregated table can answer; an imprecise grain leads to double counting or unanswerable questions.

What is a key benefit of centralizing business logic (like the definition of an active user) in the transformation layer?

Centralizing business definitions in the pipeline ensures consistent metrics across all reports; scattered or duplicated logic is a common cause of conflicting numbers.

Grounded in trusted sources

  • Kimball / Ross, The Data Warehouse Toolkit — ETL patterns
  • Airflow / Dagster / Prefect documentation — orchestration and retries
  • dbt Labs guides — transform-in-warehouse (ELT) practice
  • Google Dataflow / Data Engineering on Google Cloud primers
  • Great Expectations / data-quality contract literature

Every Wunder lesson is built from real, reputable sources — never invented.

Related courses

📘How should a data warehouse be modeled?📘How does streaming beat the nightly file?📘Keep stakeholder from meaning everyone📘Roadmaps: Product Foundations📘How do you write a brief before you build?📘Keep survey from meaning random poll📘How do you run a usability test?📘Keep North Star from meaning vanity hit

Wunder is a personalized learn-anything platform — tell it any topic and it builds a beautiful, fact-checked course in minutes, with narration, a knowledge check, and a college-style University track.

All topics · Home

© 2026 Wunder Learning LLC · Terms & Privacy