ETL & Data Pipelines

ETL stands for Extract, Transform, Load — the process of pulling data from source systems, reshaping it into the right structure, and loading it into a target (usually a data warehouse). Without reliable ETL pipelines, a warehouse is just an empty shell, and dashboards show stale or wrong data.

This topic teaches you how ETL pipelines work in practice: the difference between ETL and ELT, the major tools used in the industry, how to schedule and monitor pipelines, handle errors gracefully, enforce data quality gates, and choose between incremental and full loads. These skills are essential whether you are a BI engineer building pipelines or a BI analyst who needs to understand where the data in your dashboards comes from.

Continue from Data Warehousing Concepts to understand the warehouse target. SQL basics from SQL for Business Intelligence will help but are not required for the conceptual parts of this topic.

ETL is a three-stage pipeline:

1. Extract — connect to source systems (databases, APIs, files, streams) and pull data. Extraction must handle different formats (SQL tables, JSON APIs, CSV exports), connectivity (JDBC, REST, SFTP) and timing (real-time vs batch).
2. Transform — clean, standardize, enrich and restructure the data. Common transformations: type casting, null handling, deduplication, lookup enrichment, aggregation, business rule application, format standardization.
3. Load — write the transformed data into the target warehouse or mart. Loading strategies include full replace, append, merge (upsert) and slowly changing dimension handling.

Each stage has its own challenges. A robust pipeline handles failures at any stage without corrupting the target — ideally through transactional loads (all-or-nothing) and staging tables (transform into a temporary area, then swap into production).

The difference is where the transformation happens:

Modern trend: cloud warehouses (Snowflake, BigQuery, Synapse) are powerful enough that ELT is now the dominant pattern — load raw data first, then transform it with SQL (often using dbt). ETL still dominates in on-premises environments and when sources require pre-processing before loading.

For this path, you will learn dbt (for SQL-based ELT) and Azure Data Factory concepts (for orchestration). These represent the modern, cloud-native approach most employers expect.

Pipelines do not run once — they run on a schedule (hourly, daily, weekly) and depend on each other in a directed acyclic graph (DAG):

• Schedule — define when each pipeline runs. Daily at 2 AM is common for batch; real-time streaming for near-instant needs.
• Dependencies — the dimension pipeline must finish before the fact pipeline starts (facts reference dimension keys).
• Retries and alerting — if a pipeline fails, retry 2–3 times with backoff, then alert the team (email, Slack, PagerDuty).
• Idempotency — running a pipeline twice should produce the same result as running it once. Use merge/upsert, not blind append.
• Logging and lineage — record what ran, how long it took, how many rows moved, and any errors. Tools like dbt and Airflow provide this natively.

Production pipelines fail. The question is not if but how gracefully. A robust pipeline implements:

1. Pre-load validation — check source data before transforming: null counts, row count thresholds, schema validation, referential integrity. If validation fails, block the load and alert.
2. Quality gates — automated checks between stages. Example: "if more than 5% of rows have null revenue, stop the pipeline."
3. Error logging — capture failed rows, error messages and timestamps into an error table for investigation.
4. Quarantine pattern — instead of failing the whole load, route bad rows to a quarantine table. Load the good rows; investigate and fix the bad ones separately.
5. Post-load reconciliation — compare row counts and checksums between source and target after loading. If they do not match, alert.

Full load replaces the entire target table with all source data every run. Simple, but slow and expensive on large tables.

Incremental load only processes new or changed rows since the last run. Faster, cheaper, but requires a way to identify changes (timestamps, CDC, or hash comparisons).

You can practice ETL with dbt Core (free, runs locally with SQLite or DuckDB) or with any ETL tool you have access to.

1. Download a public CSV dataset with 5,000+ rows (e.g. from Kaggle). Write a simple ETL pipeline that: extracts from CSV, transforms (rename columns, cast types, handle nulls, add a calculated column), and loads into a SQLite database.
2. Convert your pipeline to dbt: set up a dbt project, define a staging model and a mart model, add tests.
3. Implement a full load strategy first, then convert to incremental: add a updated_at timestamp and filter for only new/changed rows.
4. Add three data quality checks: not-null on key columns, a value range check, and a uniqueness check.
5. Simulate a failure: corrupt 5 rows in the source (nulls in required fields). Verify your quality gate catches them.
6. Document your pipeline: what it does, what sources it reads, what it produces, and how to run it.

In production BI, ETL pipelines are the unsung heroes. A retail company runs nightly ETL that pulls POS data from 500 stores, cleans it, loads it into a warehouse, and by 6 AM the dashboards are ready. A bank runs real-time CDC pipelines that feed fraud detection systems. When pipelines break — and they do — the BI engineer is the one who notices the alert, diagnoses the root cause (source API changed, disk full, bad data from a new store), and fixes it before the business sees wrong numbers. This topic gives you the mental model to build and troubleshoot those systems.

Business problem: A SaaS company tracks user signups, feature usage and subscription payments across three systems. They need a nightly pipeline that combines this data into a warehouse for their BI dashboards.

Dataset: create three mock CSVs: users.csv (1,000 rows: user_id, name, email, signup_date, plan), usage.csv (10,000 rows: user_id, feature_name, usage_date, session_duration), payments.csv (2,000 rows: user_id, payment_date, amount, status).

Objective: build a dbt project that stages, transforms and tests this data.