Data Engineering Foundations: Batch and Streaming Pipelines

Build reliable data pipelines in Python and DuckDB: file formats, idempotent and incremental loads, partitioned Parquet, quality checks and a first stream.

Beginner · Data Engineering · about 15 hours · 5 modules · certificate on completion

Taught by Ismaìlè Rahimovì, Idrissà Ouédraogò, AI Learning Companions.

About this course

Data engineers build the pipelines that carry data from where it's created to where it's used: from apps, sensors and files into tables that analysts, dashboards and machine learning models can trust. This course teaches the foundations by building them. Working with readings from a network of rain gauges, you'll compare ETL with ELT, batch with streaming, and CSV and JSON with Parquet; write loads that can run twice without doubling the data, that pick up only what's new and that replace a single day's partition; and build a small batch pipeline in Python and DuckDB that rejects bad rows with a reason, checks each day before publishing it, and is covered by tests.

The last module looks beyond a single script: scheduling with cron and what an orchestrator adds, a stream processed in micro-batches with a checkpoint, de-duplication and event-time windows, and how to choose tools as data grows. Everything runs on a laptop with no cloud account, and the data is synthetic, made for the course. Afterwards, Orchestrating Data Pipelines with Apache Airflow, Analytics Engineering with dbt and DuckDB, Big Data Processing with Apache Spark, Data Quality, Testing and Data Contracts and Real-Time Data Streaming with Apache Kafka each take one part further.

What you'll learn

  • Explain what data engineers build, and choose between batch and streaming, and between ETL and ELT, for a given need
  • Choose a file format, and explain how Parquet's columns, row groups and statistics make analytical queries cheaper
  • Write loads that are idempotent and incremental, and partition data so that a single day can be replaced
  • Build a tested batch pipeline in Python and DuckDB that rejects bad rows with reasons and checks its data before publishing
  • Process an event stream in micro-batches with a checkpoint, de-duplication and event-time windows
  • Schedule a pipeline with cron, and explain what an orchestrator adds

Skills you'll gain

Data pipelines, ETL and ELT, Batch processing, Stream processing basics, Parquet and columnar storage, Idempotent loads, Incremental loading, Partitioning, Data quality checks, SQL with DuckDB

Tools

Python 3, DuckDB, pandas, pytest, cron

Before you start

Python Programming with PyCharm, or the same Python: functions, files and installing packages with pip. SQL basics (SELECT, WHERE, GROUP BY and joins), as in SQL for Data Analysis with DataGrip. Install Python 3.10 or later and, in a virtual environment, DuckDB, pandas and pytest: python -m pip install duckdb pandas pytest. The labs were tested with DuckDB 1.5, and every dataset comes with them.

Modules

  1. What data engineers build

    What a data pipeline is and who depends on it, how batch and streaming differ, and why most teams now load raw data first and transform it where it's stored.

    • Pipelines and the people who use them (10 min)
    • Batch and streaming (12 min)
    • ETL and ELT (12 min)
    • Hands-on lab: Process the same file with ETL and ELT

  2. Files and formats

    Text formats and columnar Parquet: what each keeps, how readers guess types, and how columns, row groups and statistics make analytical queries cheaper.

    • CSV and JSON: text that every system reads (12 min)
    • Parquet and columnar storage (12 min)
    • Schemas, compression and table formats (10 min)
    • Hands-on lab: Compare CSV, JSON Lines and Parquet

  3. Loading data reliably

    Loads that can be retried and rerun without corrupting data, that pick up only what's new, and partitions that let one day be replaced on its own.

    • Idempotent loads (12 min)
    • Full and incremental loads (12 min)
    • Partitioning (12 min)
    • Hands-on lab: Make loads idempotent and incremental

  4. A batch pipeline in Python and DuckDB

    Structure a pipeline as small, testable steps, transform in SQL with DuckDB, check each day's data before publishing it, and test the whole thing.

    • Designing a batch pipeline (12 min)
    • Transforming data with SQL in DuckDB (12 min)
    • Checking data before publishing it (12 min)
    • Hands-on lab: Build the rain-gauge pipeline

  5. Scheduling, streaming and scaling up

    Run pipelines on a schedule and see what an orchestrator adds, process an event stream in micro-batches, and choose tools as data grows.

    • Scheduling and orchestration (12 min)
    • Streams: events, offsets and windows (14 min)
    • Choosing tools as data grows (10 min)
    • Hands-on lab: Process a stream in micro-batches

Project: Build a week-long rain-gauge pipeline

A network of eight rain gauges sends one CSV file a day, and a team planning reservoir releases needs daily figures per station and per climate zone that it can trust. Generate a week of data with make_capstone_data.py, then build a batch pipeline in Python and DuckDB (your module 4 lab is a good start) that loads every file, keeps the raw rows, rejects bad rows with a reason, checks each day before publishing it, and publishes a station_daily mart, a zone_daily mart and a partitioned Parquet lake. The data has the problems real feeds have: duplicates; missing, negative and malformed values; a station that isn't on the list; a day when one station stops reporting; a file whose columns arrive in a different order; and a correction for 2024-06-02 that arrives a week later. Loads must be idempotent and incremental (driven by a watermark), every run must be logged, and a short design note explains your choices, including whether any part needs streaming.

Part of

The products this course names are trademarks of their owners, who haven't made or endorsed it.

Start the course free