Data Engineering: ETL, Warehousing, dbt and Airflow
Design and run a modern data pipeline: choose ETL or ELT, model a star schema, build and test models in dbt, schedule them in Airflow, and keep quality, cost and governance under control.
What you will learn
- Describe the data engineering lifecycle and choose between batch and streaming, and between ETL and ELT.
- Design reliable ingestion: incremental loads, change data capture, idempotent reruns and sensible file formats.
- Model a business process as a star schema: declare the grain, build facts and dimensions, and handle slowly changing dimensions.
- Compare cloud warehouses, data lakes and lakehouses, and organise data in layers with partitioning and open table formats.
- Build, test and document transformations in dbt using sources, ref, materializations, tests and snapshots.
- Orchestrate pipelines with Airflow: write a Dag, set schedules and retries, use assets and run backfills safely.
- Apply data quality checks, observability, governance and cost controls to a production pipeline.
Modules
- The data engineering lifecycle
- Reliable ingestion
- Dimensional modelling
- Warehouses, lakes and lakehouses
- Transformations with dbt
- Orchestration with Airflow
- Quality, governance and cost
Course facts
- Estimated study time
- about 60 minutes
- Final assessment
- 42 questions · Pass mark 75%
- Available in
- English, Français, العربية
- Price
- Free. Optional verifiable certificate: $19
- Last updated
- Published by
- AMAADOR ACADEMY
Who this course is for
Aspiring and junior data engineers, analytics engineers, BI developers, data analysts moving into engineering, and IT staff who must build or review data pipelines.
Prerequisites
Working knowledge of SQL (joins, group by) and basic Python. No prior experience with dbt or Airflow is needed.
Course content
1. The data engineering lifecycle
A data engineer builds and runs the systems that move data from where it is created to where people and models use it. Whatever the tools, the work follows five stages.
The five stages. Security, data management, orchestration and cost run underneath all of them.
Sources are applications, databases, files, APIs, sensors and event streams. You rarely own them, so you must treat them as unreliable: schemas change without warning and fields arrive late or empty.
Batch versus streaming is the first design choice. Batch processes a bounded set of data on a schedule. Streaming processes events continuously as they arrive.
| Question | Batch | Streaming |
|---|---|---|
| Typical delay | Minutes to a day | Seconds or less |
| Complexity | Lower | Higher (ordering, late events, state) |
| Cost to run | Pay per run | Always on |
| Good fit | Daily finance reports, monthly KPIs | Fraud alerts, live stock levels |
Remember: ask the business what delay is acceptable before choosing. Most reports are fine with batch, and streaming that nobody needs only adds cost and failure modes.
The letters describe the order of three steps: Extract from the source, Transform, and Load into the target.
2. Reliable ingestion
Ingestion copies data from a source into your platform. How you copy decides cost, delay and whether deleted rows are noticed.
| Method | How it works | Watch out for |
|---|---|---|
| Full load | Copy the whole table each run | Slow and costly as tables grow; fine for small reference data |
| Incremental by timestamp | Copy rows where updated_at is later than the last run | Misses hard deletes; needs a reliable updated_at column |
| Change data capture (CDC) | Read the database transaction log and emit every insert, update and delete | Needs database permissions and care with schema changes |
CDC captures deletes and the exact order of changes, with little load on the source because it reads the log rather than querying tables. Open-source tools such as Debezium and managed connectors from ingestion vendors use this approach for databases like PostgreSQL and MySQL.
- Pick a key — find the primary key of each source table.
- Pick a cursor — choose the column that moves forward, such as updated_at or a log position.
- Store the high-water mark — save the last value loaded, only after the load succeeds.
- Overlap on purpose — reload a small window, for example the last two hours, and deduplicate by key.
- Land it raw — write exactly what you received, with a load timestamp, before any cleaning.
3. Dimensional modelling
Analysts ask questions such as “sales by month, region and product”. A star schema answers them fast and clearly: one central fact table of measurable events, surrounded by dimension tables that describe them. The approach comes from Ralph Kimball’s dimensional modelling.
The fact table holds keys and numbers; the dimensions hold the descriptions people filter and group by.
- Choose the business process — for example order fulfilment, not “the sales department”.
- Declare the grain — say in one sentence what one row means: “one row per order line”.
- Choose the dimensions — who, what, where, when that describe each row.
- Choose the facts — the numbers at that grain, such as quantity and net amount.
The grain is the most important decision. Every column in the fact table must be true at that grain. Mixing grains, for example a monthly target next to an order line amount, produces double counting.
| Measure type | Can you add it? | Example |
|---|---|---|
| Additive | Across every dimension | Quantity, net amount |
| Semi-additive | Not across time | Account balance, stock level |
| Non-additive | Never; recompute from parts | Unit price, ratios |
4. Warehouses, lakes and lakehouses
Three storage patterns dominate modern platforms. They overlap more every year, so judge by what you need to do rather than by the label.
| Data warehouse | Data lake | Lakehouse | |
|---|---|---|---|
| Data kept | Structured, modelled tables | Any files: tables, logs, images, JSON | Files in cheap storage with a table layer on top |
| Query with | SQL | Spark, Python, SQL engines | SQL and Spark on the same tables |
| Transactions | Yes, built in | No, only files | Yes, from the table format |
| Examples | Snowflake, BigQuery, Amazon Redshift | Files on Amazon S3, Google Cloud Storage or Azure Data Lake Storage | Databricks, Microsoft Fabric, Iceberg tables on object storage |
Cloud warehouses are columnar: each column is stored and compressed separately, so a query that needs three columns out of fifty reads only those three. They also separate storage from compute, so you can add compute for a heavy job and stop paying when it ends.
Two features control query cost and speed:
- Partitioning splits a table by a column, usually a date, so a filter on that column skips whole partitions.
- Clustering or sorting orders data inside partitions by columns you often filter on, so even fewer blocks are read.
5. Transformations with dbt
dbt turns SQL SELECT statements into tables and views inside your warehouse, with version control, tests and documentation. It does the T of ELT; it does not extract or load data. dbt is released in two lines: the Python-based dbt v1 (v1.12 in the 2026 docs) and the newer dbt v2. The concepts below are the same in both.
- Declare sources — list the raw tables in a YAML file so models refer to them with source(), and you can check their freshness.
- Write staging models — one model per source table: rename, cast types, nothing else. Example: stg_orders selects from source('shop', 'orders').
- Write intermediate and mart models — join and aggregate, referring to other models with ref('stg_orders').
- Run dbt build — dbt works out the order from the ref() calls, then runs models, tests, seeds and snapshots in that dependency order.
The ref() function is the heart of dbt. It builds the dependency graph (the DAG), resolves the right schema for each environment and lets you test changes in a development schema without touching production.
6. Orchestration with Airflow
An orchestrator decides what runs, when, in what order and what happens on failure. Apache Airflow is one of the most widely used open-source ones; the latest release in the official announcements is 3.4.0 (6 November 2026). You describe a workflow as a Dag (directed acyclic graph) in Python: tasks are the nodes and dependencies are the arrows, with no loops.
| Concept | Meaning |
|---|---|
| Dag | The workflow: tasks, dependencies, schedule |
| Task | One unit of work, written with an operator or the TaskFlow @task decorator |
| Dag run | One execution of the Dag for one logical date |
| Retries | How many times, and after what delay, a failed task is tried again |
| Sensor | A task that waits for a condition, such as a file arriving |
In Airflow 3 you import the building blocks from airflow.sdk (dag, task, Asset). Several older features were removed: execution_date is replaced by logical_date, SubDAGs are replaced by task groups and assets, and catchup now defaults to False, so a new Dag does not silently start hundreds of past runs.
7. Quality, governance and cost
A pipeline that runs green but delivers wrong numbers is worse than one that fails, because nobody notices. Quality is checked in dimensions you can measure.
| Dimension | Question | Typical check |
|---|---|---|
| Completeness | Is anything missing? | not_null, row count against the source |
| Uniqueness | Is anything duplicated? | unique on the key |
| Validity | Is the value allowed? | accepted_values, ranges, formats |
| Timeliness | Is it fresh enough? | Source freshness, Dag run deadlines |
| Consistency | Do tables agree? | relationships, totals that reconcile |
Put checks where they are cheapest to act on: on arrival (schema, row count, freshness), during transformation (dbt tests) and before serving (reconciliation with the finance total). When a critical check fails, stop the flow and alert a named owner, rather than publishing bad data with a warning nobody reads.
A data contract is a written agreement between the team that produces data and the teams that consume it: the schema, meaning of fields, freshness and who to call. It turns “the source changed a column” from a surprise into a managed change.
Observability adds visibility: run history, row counts over time, freshness, and lineage that shows which dashboards depend on which tables, so you know who is affected before you change something.
The full lesson continues in the interactive course on this page.
Sources and references
- Apache Airflow documentation
- dbt documentation: build, tests and snapshots
- Dimensional modeling techniques
- Apache Iceberg documentation
- Apache Parquet file format
- Delta Lake documentation
- General Data Protection Regulation (EU) 2016/679
- FinOps Framework
Vendor-neutral educational course. Tool versions and features were checked on the official documentation in November 2026 (Apache Airflow 3.4, dbt v1.12 and v2); they change quickly, so check the current docs before you build. Legal points (GDPR, Moroccan law 09-08) are an introduction, not legal advice: involve your data protection officer. Product names are used only to explain concepts.
All courses · AI & Data - Free courses, certificate from $7 | AMAADOR ACADEMY