Sarang's Data Engineering Handbook¶

A comprehensive reference for data engineers — from first query to production pipelines. Each guide follows a Basic → Intermediate → Advanced progression with real, working code examples.

Offline? Download the whole handbook as a PDF (about 24 MB, bookmarked), or the PDF of any single guide from its page.

Browse by Topic¶

Foundations

SQL, Python, Linux & Bash, Git, Cloud Storage — the tools you use every day.

5 guides
Processing & Compute

DuckDB, Polars, PySpark, Trino, Docker, Databricks.

5 guides
Orchestration & Streaming

Airflow, Dagster, Prefect, Kafka, Flink, Beam, streaming SQL, CDC.

8 guides
Storage & Transformation

Snowflake, BigQuery, Redshift, Azure & Fabric, NoSQL, Delta Lake, Hudi, Iceberg, real-time OLAP, dbt, BI tools.

12 guides
Quality & Observability

Data quality, governance & lineage, catalogs, security & privacy, observability, DataOps.

6 guides
AI & Machine Learning

Prompting, RAG, agents, MCP and text-to-SQL, evals, fine-tuning, observability, local LLMs.

14 guides
Infrastructure

Kubernetes, Terraform, and testing and CI/CD for data pipelines.

3 guides
Conceptual & Reference

DE Concepts, Data Modeling, and the full Glossary.

3 guides
Architecture

System design, choosing a stack, and cost optimization.

3 guides
Hands-on Labs

Eight labs and two capstone projects on one shared dataset.

10 projects
Interview Prep

The interview roadmap, 15 SQL patterns, 5 system design case studies.

3 guides
Learning Paths

Three role-based paths and five topic paths, from complete beginner to AI engineering.

8 paths

Reference Guides¶

Foundations¶

Guide	What you'll learn
SQL Reference	SELECT, filtering, joins, aggregates, CTEs, window functions, indexes, transactions
Python for DE	Data types, OOP, generators, decorators, pandas, APIs, DE patterns
Linux & Bash	Filesystem, text processing, bash scripting, cron, SSH, DE workflows
Git for DE	Branching, merging, dbt CI/CD, git hooks, team workflows
Cloud Storage	S3, GCS, ADLS Gen2, medallion layout, partitioning, IAM, Python SDKs

Processing & Compute¶

Guide	What you'll learn
DuckDB & Polars	Single-node SQL and DataFrame analytics, larger-than-memory processing, object storage, interoperability
PySpark Reference	DataFrames, transformations, window functions, UDFs, streaming, optimization
Docker for DE	Images, Dockerfile, volumes, networking, Docker Compose, Airflow/Spark in Docker
Databricks	Delta Lake, Auto Loader, DLT, Unity Catalog, Workflows, Delta vs Iceberg vs Hudi
Trino & Query Federation	Coordinator and workers, catalogs and connectors, federated queries and pushdown, Iceberg tables, fault-tolerant execution

Orchestration & Streaming¶

Guide	What you'll learn
Apache Airflow	DAGs, operators, XComs, sensors, TaskFlow API, dynamic DAGs, CI/CD
Dagster	Software-defined assets, resources, asset checks, partitions, schedules and sensors, dbt integration
Prefect	Flows and tasks, retries, deployments, work pools, automations, event-driven runs
Apache Kafka	Topics, producers, consumers, Schema Registry, Kafka Connect, Kafka Streams, DLQ patterns
Apache Flink	Stateful stream processing, event time and watermarks, windows, stream joins, checkpoints, Flink SQL
Apache Beam & Dataflow	The Beam model, windows, triggers and late data, testing with TestStream, runners, Dataflow
Streaming SQL	Incremental view maintenance, RisingWave and Materialize, windows and watermarks, temporal filters, sinks
Data Ingestion & CDC	API, file, and database ingestion; incremental loads; CDC with Debezium; applying changes with MERGE; build vs buy

Storage & Transformation¶

Guide	What you'll learn
Snowflake Reference	Architecture, virtual warehouses, semi-structured data, streams & tasks, RBAC
dbt Reference	Models, materializations, tests, macros, incremental models, snapshots, CI/CD
Semantic Layer & Metrics	Defining metrics once: entities, measures, MetricFlow, ratio and cumulative metrics, semantic layers for AI
BI Tools (Superset & Metabase)	Application database, models and datasets, where metrics live, performance, row-level security, embedding, operations
BigQuery	Serverless architecture, loading, partitioning and clustering, nested data, pricing and cost control, security
Amazon Redshift	Provisioned vs serverless, distribution and sort keys, COPY/UNLOAD, Spectrum, SUPER, workload management
Azure & Microsoft Fabric	OneLake, capacity, lakehouse vs warehouse, shortcuts and mirroring, Event Hubs, security, Fabric CI/CD
NoSQL & Operational Stores	DynamoDB, MongoDB, Valkey/Redis and Cassandra: access-pattern modelling, idempotent writes, CDC and exports, serving data back
Delta Lake	Transaction log, `MERGE`, time travel, schema enforcement, Change Data Feed, `OPTIMIZE`/`VACUUM`, delta-rs
Apache Hudi	Record-level upserts, Copy-on-Write vs Merge-on-Read, incremental queries, compaction, indexing
Apache Iceberg	Open table format, hidden partitioning, schema evolution, time travel, ACID, AWS Glue/Athena
Real-Time Analytics Databases	ClickHouse, Apache Druid and Apache Pinot: sort keys and segments, materialized views, rollup, star-tree index, choosing between them

Quality & Observability¶

Guide	What you'll learn
Data Quality	SQL checks, Great Expectations, dbt tests, anomaly detection, data contracts, alerting
Data Security & Privacy	Classification, least privilege, secrets, encryption, masking and pseudonymisation, erasure requests, LLM security
Pipeline Observability	SLIs and SLOs, freshness and volume monitoring, structured logging, alert design, incident runbook
Data Governance & Lineage	Catalogs, ownership, classification, access models, lineage and OpenLineage, contracts, retention and deletion
Data Catalogs in Practice	DataHub and OpenMetadata: architecture, ingestion recipes and workflows, metadata as code validated in CI, running a catalog
DataOps	Severity levels, on-call design, runbooks, incident roles and the data playbook, blameless postmortems, operating metrics

AI & Machine Learning¶

Guide	What you'll learn
Prompt Engineering	Zero-shot, few-shot, CoT, structured output, chaining, versioning
LLM APIs & SDKs	Anthropic Claude & OpenAI — streaming, tool use, vision, caching, batching
Embeddings	Generating embeddings, cosine similarity, chunking, semantic search, clustering
RAG	Build retrieval-augmented generation pipelines, hybrid search, re-ranking, evaluation
Vector Databases	pgvector, Pinecone, Chroma, Weaviate — indexing, filtering, multi-tenancy
AI Agents & Tool Use	Agentic loops, tool definitions, ReAct, multi-agent systems, human-in-the-loop
MCP & Text-to-SQL	A tested read-only SQL MCP server, SQL validation, execution-accuracy evaluation, security and governance
LangChain & LlamaIndex	RAG chains, agents, LCEL, custom retrievers, LangSmith tracing
Eval & Evals	Unit tests for LLMs, LLM-as-judge, RAGAS, regression testing, eval-driven development
MLflow	Experiment tracking, model registry, serving, custom models, DE integration
Claude Code	CLI setup, CLAUDE.md, MCP servers, hooks, skills, CI/headless mode, DE workflows
Fine-Tuning LLMs	LoRA/PEFT, full fine-tuning vs RAG decision, Hugging Face + OpenAI fine-tuning API
AI Observability	Cost/latency/quality monitoring, LangSmith, Langfuse, OpenTelemetry, RAG tracing
Local LLMs	Ollama, vLLM, Hugging Face, quantization (4-bit/fp16), local RAG, hardware guide

Infrastructure¶

Guide	What you'll learn
Kubernetes for Data Workloads	Jobs and CronJobs, requests and limits, node pools and spot capacity, Spark, Airflow and Flink on Kubernetes, debugging
Terraform for DE	IaC for S3, IAM, Snowflake, Databricks, MWAA Airflow — modules, remote state, CI patterns
Testing and CI/CD for Data Pipelines	Unit and property tests, idempotency and backfill tests, CI design, data diff, write-audit-publish, promotion

Conceptual & Reference¶

Guide	What you'll learn
DE Concepts	OLTP/OLAP, batch vs streaming, lakehouse, medallion architecture, file formats, ETL/ELT
Data Modeling	Star schema, SCDs, fact/dim design, snowflake schema, OBT, Data Vault, dbt layers
Glossary	Definitions for every term used across all guides — one place to look things up

Architecture¶

Guide	What you'll learn
Cost Optimization	Unit economics, attribution, spend monitoring per platform, compute/query/storage optimization, guardrails
Data Engineering System Design	Requirements, capacity estimation, architecture patterns, batch vs streaming, reliability, security, cost, worked designs
Choosing a Stack	Requirements first, four reference architectures, signals to grow, buy vs run vs build, stack review checks, ADRs, exit plans

Interview Prep¶

Guide	What you'll learn
Interview Roadmap	The rounds, a topic map to these guides, a four-week plan, how to answer, behavioural stories
SQL Interview Patterns	Fifteen tested query patterns: top-N, gaps and islands, sessionisation, cohorts, and more
System Design Case Studies	Five worked designs: real-time dashboards, fintech PII, warehouse migration, fraud detection, RAG

Hands-on Labs¶

Practise with eight labs and two capstone projects that run locally. The labs and the first capstone use one realistic e-commerce dataset:

Lab	Practise
01 — SQL Analytics	Deduplication, window functions, funnels, sessionization (DuckDB)
02 — dbt Transformations	Layered models, data and unit tests, incremental models, snapshots
03 — Spark Lakehouse	Medallion pipeline on Delta Lake: `MERGE`, time travel, schema evolution
04 — Kafka Streaming	Consumer groups, dead-letter topics, event-time windows, watermarks
05 — Airflow Orchestration	Backfills, idempotent loads, quality gates, pools, asset scheduling
06 — Capstone: Dagster pipeline	An end-to-end pipeline with quality gates, quarantine tables and a dashboard (see Projects)
07 — Capstone: Docs RAG	Chunking, BM25 retrieval and a retrieval eval over these guides
08 — Data Quality Gates	Great Expectations suites, thresholds and severity, and a gate that blocks bad, stale and schema-changed data
09 — Iceberg Lakehouse	The Lab 03 pipeline on Apache Iceberg: snapshots, `MERGE`, schema and partition evolution, maintenance, branches
10 — CDC with Debezium	Postgres to Kafka with Debezium, applying changes idempotently, deletes, connector restarts, schema changes

Learning Paths¶

By role: the analytics engineer, data platform engineer and AI data engineer paths pair each stage with a lab and a checkpoint. The five topic paths below start from a subject instead.

Path 1: Complete beginner → job-ready¶

DE Concepts — understand the landscape
Glossary — reference when you hit an unfamiliar term
SQL Reference — the universal language of data
Python for DE — scripting and automation
Data Modeling — design data structures that scale
Linux & Bash — work in production environments
Git for DE — collaborate and ship safely
Cloud Storage — store and retrieve data at scale
Docker for DE — package and run anything
Terraform for DE — provision infra as code
Data Ingestion & CDC — get data in reliably
Data Engineering System Design — put it all together
DataOps — run it reliably, with on-call and incident response
Choosing a Stack — pick tools from requirements

Path 2: Warehouse & transformation focus¶

SQL Reference
A cloud warehouse: Snowflake, BigQuery, or Amazon Redshift
dbt Reference
Data Quality
Data Governance & Lineage, then Data Catalogs in Practice
Git for DE — CI/CD section
Testing and CI/CD for Data Pipelines — prove changes are safe before they ship
BI Tools — serve the models to the business
Cost Optimization

Path 3: Spark & big data focus¶

DE Concepts
DuckDB & Polars — single-node first
PySpark Reference
Databricks
Cloud Storage
Apache Kafka
Trino & Query Federation — interactive SQL over the lake
Kubernetes for Data Workloads — run Spark and batch jobs on shared infrastructure

Path 4: Streaming & real-time¶

DE Concepts — streaming section
Apache Kafka
Apache Flink
Data Ingestion & CDC — CDC with Debezium
PySpark Reference — Structured Streaming section
Databricks — Auto Loader and DLT sections
Data Quality — DQ in streaming pipelines
Real-Time Analytics Databases — serve fresh data with sub-second queries
Apache Beam & Dataflow — one model for batch and streaming
Streaming SQL — always-fresh views without writing a streaming job

Path 5: AI & LLM engineering¶

Prompt Engineering — foundation for everything
LLM APIs & SDKs — hands-on from day 1
Embeddings — prerequisite for RAG
RAG — most in-demand AI skill right now
Vector Databases — implement RAG at scale
AI Agents & Tool Use — where the field is heading
LangChain & LlamaIndex — practical orchestration
Eval & Evals — measure and improve quality
MLflow — bring it back to the data pipeline
Claude Code — use AI to build AI things
Fine-Tuning LLMs — when RAG isn't enough
AI Observability — monitor production LLM apps
Local LLMs — run models without the API bill
MCP & Text-to-SQL — give assistants safe, measured access to your data

Quick Reference¶

When should I use what?¶

Scenario	Tool
Ad-hoc data exploration	SQL
Scheduled batch pipeline	Airflow + PySpark or dbt
Real-time event processing	Kafka + PySpark Structured Streaming
Cloud data warehouse	Snowflake + dbt
Delta Lake / Lakehouse	Databricks
Containerized pipeline	Docker Compose
CI/CD for transformations	dbt + GitHub Actions
Data quality enforcement	dbt tests + Great Expectations
Build an LLM app on private data	RAG + vector DB
LLM with external actions	AI agents + tool use
Measure LLM app quality	Evals + LLM-as-judge
Track ML experiments	MLflow
Customize a model for your domain	Fine-tuning (LoRA/PEFT)
Monitor LLM app in production	AI Observability (LangSmith/Langfuse)
Run models privately / offline	Local LLMs (Ollama/vLLM)
Open table format for big data	Apache Iceberg
Interactive SQL over a lake and several databases	Trino
Sub-second dashboards over fresh event data	ClickHouse, Druid or Pinot
Shared, elastic infrastructure for batch and streaming jobs	Kubernetes
Microsoft-centred analytics platform	Azure and Microsoft Fabric
Prove a pipeline change is safe before shipping	Unit tests, data diff, write-audit-publish
One model for batch and streaming, on Google Cloud	Apache Beam on Dataflow
Always-fresh SQL views over streams, without a streaming job	A streaming database (RisingWave, Materialize)
Low-latency lookups and key-based serving for applications	DynamoDB, MongoDB or Valkey/Redis
Dashboards for the business	Superset or Metabase over curated marts
Find, own and trace data assets	A catalog: DataHub or OpenMetadata, with metadata in Git
Let an AI assistant query data safely	An MCP server with a validated, read-only SQL tool
Respond to data incidents reliably	Severity levels, on-call, runbooks, blameless postmortems
Decide which tools to adopt	Requirements first, the simplest stack, an ADR, and stack review checks

File format cheat sheet¶

Format	Use when
Parquet	Columnar analytics, Spark, large-scale reads
Avro	Kafka messages, schema evolution, row-based streaming
Delta	Lakehouse tables with ACID, time travel, MERGE (Databricks-native)
Iceberg	Open lakehouse tables — multi-engine (Spark, Flink, Trino, Athena), hidden partitioning
JSON	Raw landing zone, semi-structured, API payloads
CSV	External hand-offs, small seeds, human-readable exports
ORC	Hive/Hadoop ecosystems (prefer Parquet elsewhere)

Materializations comparison¶

Type	When to use
`view`	Lightweight, always fresh, no storage cost
`table`	Expensive query that many models read
`incremental`	Large tables where only new/changed rows matter
`ephemeral`	Staging logic used once, not queried directly

Delivery guarantees¶

Guarantee	Meaning
At-most-once	May lose messages, never duplicate
At-least-once	May duplicate, never lose
Exactly-once	No duplicates, no loss (hardest to achieve)

Concepts at a Glance¶

Medallion Architecture¶

Bronze (raw)      → Silver (cleaned, conformed)      → Gold (aggregated, business-ready)
Exact copy          Deduped, typed, validated           Fact/dim tables, aggregates, KPIs
of source data      Joined where needed                 Consumed by BI / ML / APIs

Data Warehouse vs Data Lake vs Lakehouse¶

	Data Warehouse	Data Lake	Lakehouse
Storage	Proprietary (Snowflake, BigQuery)	Object storage (S3, GCS, ADLS)	Object storage
Format	Vendor-specific	Any (Parquet, CSV, JSON…)	Open (Delta, Iceberg, Hudi)
Schema	Schema-on-write	Schema-on-read	Both
ACID	Yes	No	Yes (with Delta/Iceberg)
Cost	Higher compute	Lower storage	Balanced
Examples	Snowflake, Redshift	S3 + Glue	Databricks, Delta Lake

ETL vs ELT¶

ETL (traditional):  Extract → Transform → Load      (transform before loading)
ELT (modern):       Extract → Load → Transform      (load raw, transform in warehouse)

ELT is dominant today because cloud warehouses are cheap and powerful enough to handle transforms at scale.

Resources¶

Data Engineering - dbt Documentation - Apache Airflow Documentation - Apache Kafka Documentation - Databricks Documentation - Snowflake Documentation - PySpark API Reference - Great Expectations Documentation

AI & LLMs - Anthropic API Documentation - OpenAI API Documentation - LangChain Documentation - LlamaIndex Documentation - MLflow Documentation - RAGAS Documentation - Voyage AI (Embeddings)