Course · Data platforms
ETL and ELT for Data Engineers
ETL transforms data before loading it; ELT loads first and transforms inside the warehouse or lakehouse. Learn when each fits.
- Lessons
- 10
- Interview questions
- 1
- Projects & case studies
- 7
- Reading time
- ~2 h
Your progress
Saved in this browser onlyCourse structure
- 1 lessonStart hereThe complete overview of the course in one read.
- 1 lessonBeginnerCore concepts you will use every day.
- 7 lessonsIntermediatePatterns used in production pipelines.
- 1 lessonAdvancedPerformance, internals and edge cases.
Practise
Lessons
Work through the lessons in order. Completed lessons show a tick; lessons you have opened are outlined.
Start here
The complete overview of the course in one read.
Beginner
Core concepts you will use every day.
Intermediate
Patterns used in production pipelines.
- Batch vs Streaming Data PipelinesBatch vs streaming: choose by latency need and cost, understand event time vs processing time, watermarks and late data, and the micro-batch middle ground.
- Idempotency in Data PipelinesIdempotent data pipelines: deterministic partitions, overwrite-partition, MERGE and ON CONFLICT upserts, natural keys, run ids and guarded side effects.
- Incremental Loading, Watermarks, Late Data and BackfillsIncremental loading with watermarks and MERGE: handle commit lag, deletes and late-arriving events, and run backfills without corrupting current data.
- Data Quality: Checks, Contracts and Failure HandlingData quality checks and contracts: which checks to run where, quarantining bad rows, blocking bad publishes, with Python, SQL, dbt, Great Expectations, Soda.
- Data Pipeline Observability FundamentalsData pipeline observability: measure freshness, volume, schema and lag, define SLIs, SLOs, SLAs, detect drift, use lineage and design alerts people act on.
- Data Pipeline Reliability and Retry DesignPipeline reliability and retries: classify failures, use capped exponential backoff with jitter, set timeouts, add dead-letter queues and plan backfills.
- Testing Data Pipelines and CI/CD for DataTesting data pipelines: unit tests for transforms, integration tests on a real engine, data tests and regression diffs, then safe CI/CD.
Advanced
Performance, internals and edge cases.
Projects and case studies
Apply what you learned and prepare material to discuss in interviews.
System design case studies
- AdvancedDesign a Backfill and Late-Data Handling SystemDesign the platform capabilities that let a data team handle late-arriving data automatically in daily and hourly pipelines, and run large backfills (after bug fixes, new columns or new pipelines) across months of history and dozens of dependent tables, safely, cheaply and without disturbing production loads or consumers.
- AdvancedDesign a Data Observability SystemA data platform runs 3,000 tables across a warehouse and a lakehouse, fed by Airflow, dbt, Spark and streaming jobs. Problems are usually found by business users hours later. Design a data observability system that monitors pipelines and data automatically, detects freshness, volume, schema and distribution anomalies, finds the likely root cause through lineage, and drives incidents to resolution against defined SLAs.
- AdvancedDesign a Data Quality FrameworkBad data keeps reaching dashboards and models: duplicated orders after a retry, a source that silently sent half its rows, negative prices after an upstream change. Design a company-wide data quality framework that lets teams declare expectations on their datasets, enforces them at the right points in batch and streaming pipelines, stops bad data from being published, and routes problems to the people who can fix them.
- AdvancedDesign a Data SLA and Freshness Monitoring SystemDesign a system that tells a company, for each of its important datasets, whether the data is fresh, complete and correct enough to use right now; alerts the right owner before consumers notice a problem; shows which downstream dashboards and models are affected; and reports SLA attainment over time.
- AdvancedDesign a Financial Reconciliation PipelineDesign a daily batch pipeline that reconciles the company's internal payment ledger with settlement files from payment service providers (PSPs) and statements from banks, so finance can prove every transaction was received, settled and paid out, and can investigate every difference.
- AdvancedDesign a Near-Zero Downtime Data Platform MigrationDesign the migration of a live on-premises data warehouse, the ETL jobs that load it and the dashboards that read it to a cloud warehouse or lakehouse, so that consumers see no more than a few minutes of disruption and every number can be proved to match before the old system is switched off.
- AdvancedDesign a Schema Registry and Data Contract SystemDesign a schema registry and data contract system for a company where 60 product teams publish events to Kafka and expose database tables to the data platform, so that producers can evolve their data without breaking hundreds of downstream pipelines, dashboards and ML models, and breaking changes are caught before deployment rather than in production.
Resources
Cheat sheets
Related courses
- Data modelingData modeling and warehousing: star schemas and grain, fact and dimension design, SCDs, Data Vault, dbt, semantic layers and incremental models.
- SQLSQL is the core language of data work: querying, transforming and modelling data in warehouses, lakehouses and Spark. Start here before any other tool.
- AirflowAirflow schedules and orchestrates pipelines as DAGs. Learn scheduling, task dependencies, retries and idempotent task design.