Data pipelines and integration
Data rarely stays where it was created. Pipelines move, transform, and validate data between systems, reliably and repeatably.
ETL and ELT
Extract, transform, load (ETL) pulls data from sources, transforms it on a separate system, and loads it into the warehouse. With powerful cloud warehouses, extract, load, transform (ELT) has become common: load raw data first, then transform it inside the warehouse using SQL. ELT keeps raw data available for reprocessing when business logic changes.
Batch and streaming
Batch pipelines process data in scheduled chunks, such as nightly loads, which is simple and efficient but introduces delay. Streaming pipelines process events continuously as they arrive, within seconds, using platforms such as Apache Kafka for durable event logs and processors such as Apache Flink or Spark Structured Streaming. Many organisations use both.
Change data capture
Change data capture (CDC) streams every insert, update, and delete from an operational database, usually by reading its transaction log, as tools like Debezium do. This keeps warehouses, caches, and search indexes in sync in near real time without heavy queries on the source, and avoids the inconsistency of applications writing to several systems separately.
Orchestration
Pipelines consist of many dependent steps: extract orders, then join with customers, then refresh dashboards. Orchestrators such as Apache Airflow, Dagster, and Prefect schedule these as directed acyclic graphs of tasks, handle retries and failures, and record what ran when. Transformation tools such as dbt let analysts define SQL models with dependencies, tests, and documentation under version control.
Data quality
Pipelines must check data, not just move it. Tests assert expectations: IDs are unique and not null, foreign keys match, values fall in valid ranges, row counts are within normal bounds, and data is fresh. Failing checks should stop or flag the pipeline before bad data reaches dashboards. Data contracts formalise what producers promise consumers about schemas and semantics.
Idempotency and backfills
A pipeline step is idempotent if running it twice gives the same result as once, for example by overwriting a day's partition rather than appending to it. Idempotency makes retries safe after failures. Backfills rerun pipelines for past periods when logic changes or bugs are fixed, which is straightforward when steps are idempotent and raw data has been kept.
Lineage and governance
Data lineage tracks where each dataset came from and which downstream reports depend on it, so teams can assess the impact of changes and trace errors to their source. Governance covers catalogues of available data, ownership, access controls, retention rules, and compliance with privacy laws such as the GDPR, including the right to have personal data erased.
Worked example
An online shop's pipeline: Debezium captures order changes from PostgreSQL into Kafka; a streaming job updates a real-time sales dashboard within seconds; every night, Airflow triggers dbt models that build fact_sales and dimension tables in the warehouse, running tests for unique order IDs and non-negative amounts. One night, a test detects negative amounts from a currency bug; the run stops, the team fixes the transform, and an idempotent backfill rebuilds the affected days.
Key takeaways
ETL transforms before loading; ELT loads raw data and transforms in the warehouse, keeping raw data for reprocessing. Batch pipelines run on schedules; streaming pipelines with Kafka and Flink process events continuously. Change data capture streams database changes from transaction logs, as Debezium does. Orchestrators such as Airflow, Dagster, and Prefect run task DAGs with retries; dbt manages tested SQL models. Data-quality tests and contracts stop bad data. Idempotent steps make retries and backfills safe. Lineage and governance track origins, dependencies, access, and privacy compliance.
Flashcard
Extract, transform, load
Check your understanding
Why should a daily pipeline step overwrite that day's partition instead of appending rows?