Data Engineering Fundamentals – ETL Pipelines, Data Warehouses and the Modern Data Stack

Data engineering is the discipline of building and maintaining the infrastructure that makes data available, reliable, and useful for analytics and machine learning. While data scientists work with datasets, data engineers build the systems that produce those datasets — ingesting raw data from dozens of sources, transforming and cleaning it, loading it into storage systems optimised for analytical queries, and ensuring the entire process runs reliably at scale. This guide covers the core concepts, tools, and architectural patterns of modern data engineering.

Data engineering is examined in our Data Engineering Interview Q&A which covers 40+ technical questions. The ETL pipelines described here use the tools covered in our ETL Pipelines with Airflow and dbt guide. The data produced by these pipelines feeds the machine learning workflows in our MLOps Interview Q&A and the SQL analytics patterns in our Advanced SQL for Data Scientists guide.

ETL vs ELT — A Paradigm Shift

The traditional pipeline pattern is ETL: Extract from source systems, Transform the data, then Load into the destination. ETL was designed for on-premises data warehouses where storage was expensive and computation happened in specialised ETL tools before loading. The transformation logic lived in ETL tools (Informatica, DataStage) and was hard to test, version-control, or reuse.

Modern cloud data warehouses (Snowflake, BigQuery, Redshift) have inverted this: storage is cheap, and the warehouse itself is massively parallel and fast at transformation. This enables ELT: Extract raw data and Load it into the warehouse first, then Transform it using SQL inside the warehouse. The transformation logic lives in version-controlled SQL (managed by dbt) and can be tested, documented, and run incrementally.

Apache Airflow — Pipeline Orchestration

Airflow is the de facto standard for orchestrating data pipelines. A pipeline is represented as a DAG (Directed Acyclic Graph) — a collection of tasks with explicit dependencies. Airflow schedules DAGs using cron expressions, tracks execution history, handles retries and failure alerts, and provides a web UI for monitoring.

from airflow import DAG
from airflow.operators.python import PythonOperator
from airflow.operators.bash import BashOperator
from datetime import datetime, timedelta

default_args = {
    'owner':            'data_team',
    'depends_on_past':  False,
    'start_date':       datetime(2026, 9, 1),
    'retries':          2,
    'retry_delay':      timedelta(minutes=5),
    'email_on_failure': True,
    'email':            ['data-alerts@company.com'],
}

with DAG(
    dag_id='daily_sales_pipeline',
    default_args=default_args,
    schedule_interval='0 6 * * *',   # daily at 06:00 UTC
    catchup=False,
    tags=['sales', 'daily'],
) as dag:

    def extract_from_api(**context):
        import requests, json
        ds   = context['ds']   # execution date YYYY-MM-DD
        resp = requests.get('https://api.orders.com/sales',
                            params={'date': ds},
                            headers={'Authorization': 'Bearer TOKEN'})
        with open('/tmp/sales_' + ds + '.json', 'w') as fp:
            json.dump(resp.json(), fp)
        return '/tmp/sales_' + ds + '.json'

    extract = PythonOperator(
        task_id='extract_sales',
        python_callable=extract_from_api,
    )

    load_to_s3 = BashOperator(
        task_id='load_to_s3',
        bash_command='aws s3 cp /tmp/sales_{{ ds }}.json '
                     's3://data-lake/raw/sales/{{ ds }}/data.json',
    )

    run_dbt = BashOperator(
        task_id='run_dbt_models',
        bash_command='cd /opt/dbt && dbt run --select +fct_daily_sales',
    )

    dbt_test = BashOperator(
        task_id='dbt_test',
        bash_command='cd /opt/dbt && dbt test --select +fct_daily_sales',
    )

    extract >> load_to_s3 >> run_dbt >> dbt_test

dbt — Transform Inside the Warehouse

dbt (data build tool) brings software engineering best practices to SQL analytics. A dbt model is a single SQL SELECT statement — dbt wraps it in CREATE TABLE AS or CREATE VIEW AS and handles materialisation, dependency resolution, and incremental loading. dbt provides automated documentation, testing (not_null, unique, accepted_values), version control, and modular refactoring via the ref() function.

-- models/staging/stg_orders.sql
-- config(materialized='view')
SELECT
    order_id::VARCHAR                    AS order_id,
    customer_id::VARCHAR                 AS customer_id,
    CAST(created_at AS TIMESTAMP)        AS created_at,
    UPPER(TRIM(status))                  AS status,
    ROUND(total_amount::NUMERIC, 2)      AS total_amount_usd,
    COALESCE(country_code, 'UNKNOWN')    AS country_code
FROM raw.orders
WHERE created_at >= '2024-01-01';

-- models/marts/fct_daily_sales.sql
-- config(materialized='incremental', unique_key='sale_date')
WITH daily AS (
    SELECT
        DATE(created_at)   AS sale_date,
        country_code,
        COUNT(*)           AS n_orders,
        SUM(total_amount_usd) AS revenue_usd,
        COUNT(DISTINCT customer_id) AS unique_customers
    FROM stg_orders           -- ref('stg_orders') in real dbt
    WHERE status = 'COMPLETED'
    GROUP BY 1, 2
)
SELECT * FROM daily;

The Modern Data Stack

The data lakehouse is the latest architectural pattern — merging the flexibility of data lakes with the performance and reliability of data warehouses. Apache Iceberg and Delta Lake add ACID transactions, schema evolution, time travel, and efficient metadata management on top of object storage. Databricks (Delta Lake) and Apache Iceberg on S3 are the two dominant implementations.

For the SQL skills needed to work with data in these warehouses, our Advanced SQL for Data Scientists guide covers window functions, CTEs, and query optimisation. For the Python patterns used in data transformation, our Pandas and NumPy Mastery guide covers the core skills. For building ML feature pipelines on top of this infrastructure, our Feature Engineering guide covers feature stores, online/offline serving, and the data engineering considerations for ML. The time series data stored in these warehouses powers the forecasting models in our Time Series Forecasting guide.