Build Reliable Data Pipelines with pandas, DuckDB, SQL, dbt, and Airflow

Read before you buy: the PDF here is a free sample — the first 20 pages of the 529-page book (payments, sales and reviews on this demo site are sample data). Get the full e-book at thenexbooks on Gumroad.

Turn messy files into data people can trust — every morning, without babysitting.

Most data problems are not analysis problems. The hard part is everything that has to be true before the numbers can be trusted: files that arrive late or twice, columns that change type, a rerun that doubles yesterday's sales, a bad row that silently disappears, a job that fails at 3 a.m. with nobody noticing. Data engineering is the discipline of moving, cleaning, modeling, and serving data reliably — and if you already write Python and SQL, you are closer than you think.

This book teaches the engineering habits that turn a working notebook into a dependable pipeline. You build one real pipeline from raw files to a tested, orchestrated, monitored warehouse and reporting mart, using tools that run on a laptop — Python, pandas 3, DuckDB, SQL, and PostgreSQL — and connect it to the orchestration and transformation tools used across the industry, Apache Airflow and dbt.

What's inside

• What data engineers do, ETL vs ELT, and what "reliable" really means: correct, complete, timely, idempotent, observable
• Batch vs micro-batch vs streaming: event time, windows, watermarks, delivery guarantees, and when you actually need streaming
• File formats that matter: CSV pitfalls, nested JSON, and columnar Parquet with partitioned layouts
• Ingesting files with pandas 3 using explicit schemas, and cleaning with quarantine instead of silent drops
• pandas 3 behaviors you must know: Copy-on-Write, chained assignment, the new default string dtype, and datetime resolution
• DuckDB for analytics SQL directly on CSV, JSON, and Parquet: window functions, QUALIFY, PIVOT, nested data, and pandas interop
• Dimensional modeling: grain, facts and dimensions, surrogate keys, the unknown member, and slowly changing dimensions (type 2) with point-in-time joins
• Data quality checks, reconciliation reports, unit tests with tiny fixtures, and data contracts
• Idempotent loads (delete-insert, upserts, MERGE), incremental loads with watermarks, late data, corrected re-deliveries, and backfills
• Loading PostgreSQL efficiently with COPY and upserts, and serving a reporting mart with least-privilege roles
• Orchestration concepts and a full Apache Airflow DAG; Prefect and Dagster compared neutrally
• dbt models, sources, tests, incremental models, snapshots, and documentation
• Logging, monitoring, alerting, performance profiling, security and PII handling, and CI for data pipelines

One running example, start to finish. You build the daily sales pipeline for Brambleway Grocers, a fictional grocery chain with four branches: three send CSV files from an older point-of-sale system and one sends nested JSON from a newer one, complete with a few deliberately dirty rows. Step by step it becomes a DuckDB star-schema warehouse with history-tracking dimensions, quality checks, safe reruns, a PostgreSQL reporting mart ready by 07:00, an Airflow DAG, a dbt project, and a CI pipeline. The sample data is small enough to check by hand, and every pandas, DuckDB, and SQL result in the book was produced by running the code on it.

Current and honest. Examples were verified with Python 3.12, pandas 3, DuckDB 1.5, and PostgreSQL 18. Airflow (3.x, with notes for 2.x) and dbt examples are clearly labeled where behavior depends on your version. The book contains no invented benchmarks or statistics — it shows you how to measure on your own data instead. Every chapter includes runnable code with real output, Tips, Notes, Warnings, exercises, and Key Takeaways, and the appendices give you a cheat sheet, copy-ready templates, a production checklist, and a glossary.

Format: PDF, 529 pages (A4), linked table of contents and bookmarks. English. First Edition, 2026. Published by thenexbooks. This book is independent and is not affiliated with, sponsored by, or endorsed by the Python Software Foundation, NumFOCUS, the DuckDB Foundation, the Apache Software Foundation, dbt Labs, or any other company or project mentioned.

Table of Contents

Part I — Foundations

• Ch 1 · What Data Engineers Do: Pipelines, Roles, and the Brambleway Project
• Ch 2 · Setting Up Your Data Engineering Workspace: Python, pandas, DuckDB, and the Sample Data
• Ch 3 · Batch and Streaming: Processing Models, Latency, and Architecture Choices
• Ch 4 · File Formats That Matter: CSV, JSON, and Parquet
• Ch 5 · Python for Pipelines: Project Layout, Functions, Types, and Configuration

Part II — Ingest and Transform

• Ch 6 · Ingesting Files with pandas 3: Explicit Schemas for CSV and JSON
• Ch 7 · Cleaning Data with pandas: Types, Missing Values, Duplicates, and Quarantine
• Ch 8 · Combining and Reshaping: Joins, Group By, Pivots, and Window Operations
• Ch 9 · pandas 3 Behaviors You Must Know: Copy-on-Write, String Dtypes, and Datetimes
• Ch 10 · DuckDB Fundamentals: Analytics SQL Directly on Files

Part III — Modeling and Warehousing

• Ch 11 · Advanced Analytics SQL in DuckDB: CTEs, Window Functions, QUALIFY, and Nested Data
• Ch 12 · Dimensional Modeling: Star Schemas, Grain, Facts, and Dimensions
• Ch 13 · Slowly Changing Dimensions: Type 1, Type 2, and Point-in-Time Joins
• Ch 14 · Building the Brambleway Warehouse in DuckDB
• Ch 15 · Warehouses and Lakehouses: Cloud Platforms and Open Table Formats

Part IV — Reliable Pipelines

• Ch 16 · Data Quality Checks: Validation Rules, Quarantine, and Reconciliation
• Ch 17 · Testing Data Pipelines: Unit Tests, Fixtures, and Data Contracts
• Ch 18 · Idempotent Loads: Safe Reruns, Delete-Insert, Upserts, and MERGE
• Ch 19 · Incremental Loads: Watermarks, Late Data, and Backfills
• Ch 20 · Loading PostgreSQL: COPY, Upserts, and a Reporting Mart

Part V — Orchestration and Transformation

• Ch 21 · Orchestration Concepts: From Scripts to a CLI, Scheduling, Retries, and Dependencies
• Ch 22 · Apache Airflow: DAGs, Tasks, and the Brambleway Daily Pipeline
• Ch 23 · dbt: SQL Models, Sources, Tests, Snapshots, and Documentation
• Ch 24 · Logging, Monitoring, and Alerting for Data Pipelines

Part VI — Production

• Ch 25 · Performance: Profiling, Memory, Chunking, and Parquet Layouts
• Ch 26 · Security, Privacy, and PII in Data Pipelines
• Ch 27 · Continuous Integration for Data Pipelines
• Ch 28 · Capstone: The Complete Brambleway Pipeline, End to End

Appendices
A · Data Engineering Cheat Sheet
B · Templates and Reference
C · Production Checklist
D · Glossary

Book details