Customize this training

Get a certificate — $34.29$12

Data Engineering: ETL, Warehousing, dbt and Airflow

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

  1. The data engineering lifecycle
  2. Reliable ingestion
  3. Dimensional modelling
  4. Warehouses, lakes and lakehouses
  5. Transformations with dbt
  6. Orchestration with Airflow
  7. 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.

QuestionBatchStreaming
Typical delayMinutes to a daySeconds or less
ComplexityLowerHigher (ordering, late events, state)
Cost to runPay per runAlways on
Good fitDaily finance reports, monthly KPIsFraud 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.

MethodHow it worksWatch out for
Full loadCopy the whole table each runSlow and costly as tables grow; fine for small reference data
Incremental by timestampCopy rows where updated_at is later than the last runMisses hard deletes; needs a reliable updated_at column
Change data capture (CDC)Read the database transaction log and emit every insert, update and deleteNeeds 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.

  1. Pick a key — find the primary key of each source table.
  2. Pick a cursor — choose the column that moves forward, such as updated_at or a log position.
  3. Store the high-water mark — save the last value loaded, only after the load succeeds.
  4. Overlap on purpose — reload a small window, for example the last two hours, and deduplicate by key.
  5. 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.

  1. Choose the business process — for example order fulfilment, not “the sales department”.
  2. Declare the grain — say in one sentence what one row means: “one row per order line”.
  3. Choose the dimensions — who, what, where, when that describe each row.
  4. 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 typeCan you add it?Example
AdditiveAcross every dimensionQuantity, net amount
Semi-additiveNot across timeAccount balance, stock level
Non-additiveNever; recompute from partsUnit 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 warehouseData lakeLakehouse
Data keptStructured, modelled tablesAny files: tables, logs, images, JSONFiles in cheap storage with a table layer on top
Query withSQLSpark, Python, SQL enginesSQL and Spark on the same tables
TransactionsYes, built inNo, only filesYes, from the table format
ExamplesSnowflake, BigQuery, Amazon RedshiftFiles on Amazon S3, Google Cloud Storage or Azure Data Lake StorageDatabricks, 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.

  1. Declare sources — list the raw tables in a YAML file so models refer to them with source(), and you can check their freshness.
  2. Write staging models — one model per source table: rename, cast types, nothing else. Example: stg_orders selects from source('shop', 'orders').
  3. Write intermediate and mart models — join and aggregate, referring to other models with ref('stg_orders').
  4. 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.

ConceptMeaning
DagThe workflow: tasks, dependencies, schedule
TaskOne unit of work, written with an operator or the TaskFlow @task decorator
Dag runOne execution of the Dag for one logical date
RetriesHow many times, and after what delay, a failed task is tried again
SensorA 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.

DimensionQuestionTypical check
CompletenessIs anything missing?not_null, row count against the source
UniquenessIs anything duplicated?unique on the key
ValidityIs the value allowed?accepted_values, ranges, formats
TimelinessIs it fresh enough?Source freshness, Dag run deadlines
ConsistencyDo 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

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