Subjects

SQL Mental Model & Query Execution Order

The foundational SQL subject every data engineering interview loop assumes you already have: the logical order the database actually evaluates a query in (FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT) versus the order you type it in, why that gap explains half of the 'why doesn't this query work' confusions candidates hit, JOIN types reasoned about by row count rather than memorized as Venn diagrams, UNION/INTERSECT/EXCEPT set semantics, the three-valued logic that makes NULL comparisons and the NOT IN trap so dangerous, GROUP BY/HAVING as a mental model rather than a syntax rule, and a fully worked step-by-step trace of a non-trivial query.

ETL vs ELT & Pipeline Design

A practitioner's tour of batch pipeline design as a data engineering interview topic: the real history behind ETL vs ELT and why elastic cloud-warehouse compute made load-then-transform the default, idempotency as the non-negotiable property of any job that will ever be re-run, watermark-based incremental loading and merge/upsert patterns, log-based CDC (Debezium) vs query-based timestamp CDC and their tradeoffs, why backfilling a year of history is a structurally different problem than the daily job, and how at-least-once delivery plus idempotent writes gets you effectively-once processing without a distributed transaction.

dbt & Analytics Engineering

A practitioner's tour of dbt and the analytics engineering discipline it created: what analytics engineering is and why it sits between data engineering and analysts, how ref() and source() build a dependency DAG out of plain SQL, the four materializations and when each one earns its cost, incremental model strategies (merge vs insert_overwrite, unique_key, is_incremental()) with a worked example, the testing and documentation layers that make a warehouse trustworthy, and the staging/intermediate/marts project structure nearly every serious dbt project converges on.

Airflow & Workflow Orchestration

A practitioner's tour of Airflow as a data engineering interview topic: how DAGs, tasks, and operators fit together; why the scheduling model's catchup default silently reprocesses history; why classic sensors starve the worker pool and what deferrable operators fix; how retries, SLAs, and alerting are actually wired up; dynamic task mapping for runtime-sized fan-out; why large payloads must never flow through XComs; idempotent task design as the property that makes retries and backfills safe; and a short comparison of Airflow's task-centric model to Dagster and Prefect's asset-centric one.

Data Warehouses & Lakehouses

A practitioner's tour of the storage and compute architectures that sit underneath every analytics stack: why OLTP systems and analytical workloads want fundamentally different engines, what columnar storage buys you and why it's the single biggest lever behind fast aggregate queries, the MPP conceptual model that Snowflake, BigQuery, and Redshift all implement in different ways, and the lakehouse's core bet — that open table formats (Iceberg, Delta Lake, Hudi) can bring warehouse-grade ACID transactions, time travel, and schema evolution to plain object storage. Closes with a decision framework for warehouse vs lakehouse vs plain data lake, and a comparison of how Snowflake, BigQuery, Redshift, and Databricks actually position against each other.

Data Modeling: Dimensional & Normalized

A practitioner's tour of data modeling as a data engineering interview topic: why OLTP systems normalize to 3NF and what breaks when you don't, how Kimball dimensional modeling turns a normalized source into a queryable star schema built around grain, the four ways to handle a dimension that changes over time (SCD 0-3) worked through concrete before/after rows, the three fact table types (transaction, periodic snapshot, accumulating snapshot) and which questions each one answers, the modern argument for denormalizing into One Big Table on a columnar warehouse and where that argument breaks down, and a brief look at Data Vault as the alternative enterprises reach for when Kimball's assumptions stop holding.

Data Engineering in Production

The practitioner-level layer that separates a data engineer who has built pipelines in a notebook from one who has run them in production: testing dbt models and DAGs before merge with slim CI, dev/staging/prod environment strategy and promotion, secrets management for pipeline credentials, cost optimization in cloud warehouses (clustering, auto-suspend, per-query attribution, the runaway-warehouse story), on-call and runbooks for pipeline failures, how sources, orchestration, storage, transformation, and BI actually compose end to end with real ownership boundaries, and the documentation practices that keep a pipeline alive after its author leaves.

Case Study: Design a Streaming Event Pipeline

Model interview answer for designing a clickstream/event-analytics pipeline that ingests billions of events per day through Kafka, computes real-time aggregates with a stream processor, and lands the same events in a lakehouse for historical analytics: the arithmetic behind peak throughput and partition counts, why partition key choice is the single decision that determines correctness downstream, the windowed-aggregation design and engine choice, why kappa beats lambda here and what it doesn't solve, exactly-once semantics traced through every hop from producer to sink, watermarking and late-data handling, a schema-evolution strategy that survives years of producer changes, cost and storage math at scale, and the monitoring signals that catch pipeline failure before the business notices.

Case Study: The SQL Interview Gauntlet

A worked answer key for the SQL problems that show up over and over in data engineering interviews, structured as seven realistic prompts with sample data and full solutions: funnel conversion analysis with drop-off rates, N-day retention by signup cohort, sessionizing a raw event log with a gap threshold, deduplication strategies compared (ROW_NUMBER, DELETE, DISTINCT ON), efficient running/cumulative metrics without a self-join, the classic Nth-highest-value problem generalized to Nth-highest-per-group with DENSE_RANK, and a this-year-vs-last-year cohort comparison in a single query. Each problem is reasoned from the execution model rather than pattern-matched from memory.

Advanced SQL: Window Functions & CTEs

A practitioner's tour of the SQL constructs that separate 'knows SELECT' from 'can solve the hard interview problem': window function anatomy (OVER, PARTITION BY, ORDER BY, and the ROWS-vs-RANGE frame clause), ranking functions and when ROW_NUMBER, RANK, or DENSE_RANK is the correct choice, running totals and moving averages, LAG/LEAD for row-to-row comparisons, the top-N-per-group pattern, recursive CTEs for hierarchy traversal and date-spine generation with a full worked example, the gaps-and-islands problem solved without a loop, and a clear-eyed comparison of CTEs, subqueries, and temp tables for readability and optimizer behavior.

Streaming Fundamentals & Kafka

A practitioner's tour of streaming as a data engineering interview topic: when streaming is genuinely justified versus when it's over-engineering a batch problem, Kafka's core model (topics, partitions, offsets, consumer groups, replication, and in-sync replicas) with a concrete partition-assignment walkthrough, the mechanics of at-most-once, at-least-once, and exactly-once delivery (idempotent producers and transactions, not magic), event time versus processing time, watermarking and late-arriving data, tumbling/sliding/session windowing, and a survey of Kafka Streams, Flink, and Spark Structured Streaming and how each sits on top of Kafka.

Spark Architecture & Execution Model

A practitioner's tour of Spark as a data-engineering interview topic: how the driver, executors, and cluster manager divide work across a cluster; why DataFrames replaced RDDs as the default API; what lazy evaluation actually buys you and the exact moment a chain of transformations becomes real work; how the Catalyst optimizer turns a DataFrame plan into an executed physical plan and how Adaptive Query Execution re-plans that physical plan mid-flight using runtime statistics; how partition count and executor cores determine real parallelism; and the decision framework for recognizing when a single-node warehouse query or an engine like DuckDB would answer the question faster and cheaper than spinning up a cluster.

Case Study: Design a Batch Analytics Platform

Model interview answer for designing the batch analytics platform behind a mid-size company's BI and reporting layer, from OLTP sources through CDC/ELT ingestion, lakehouse/warehouse landing, dbt transformation layers, and Airflow orchestration to dashboards: concrete row-count and byte-volume math that drives every downstream decision, the CDC-vs-batch-extract call made per source rather than uniformly, warehouse-vs-lakehouse and file-format/partitioning choices, staging/intermediate/marts layering in dbt, an Airflow DAG shaped around data-aware dependencies rather than fixed clock time, an SLA built with deliberate slack instead of run at the theoretical minimum, an idempotent partition-scoped backfill strategy, cost control through partition pruning and materialization rather than bigger warehouses, data-quality gates that quarantine rather than silently drop or block, and a team/ownership model that treats the staging layer as a reviewed public interface.

SQL Query Optimization & Indexing

A practitioner's tour of SQL performance as a data engineering interview topic: how to read an EXPLAIN / EXPLAIN ANALYZE plan and tell a sequential scan from an index scan from an index-only scan, how B-tree, hash, GIN, and GiST indexes differ and when each applies, why composite index column order determines whether an index is even usable, why wrapping a column in a function or relying on an implicit type cast silently disables an index, how the planner's cardinality estimates drive its join and scan choices — and go wrong when statistics are stale — the anti-patterns (SELECT *, OR instead of UNION, N+1 queries) that quietly cost the most in production, and the disciplined answer to 'should I just add an index here?'

File Formats, Partitioning & Storage Layout

A practitioner's tour of how data is physically laid out on disk and why that layout is often the single biggest lever on query cost: row-oriented vs columnar storage and why analytics workloads favor the latter, Parquet's row-group and column-chunk structure and how embedded statistics enable predicate pushdown, Avro's role in streaming and schema evolution, a brief look at ORC, the speed-vs-ratio trade-off across snappy, gzip, and zstd, partitioning strategy and the cardinality pitfalls of over-partitioning, clustering and sort order within files, and the small-files problem with concrete compaction strategies.

Data Quality, Testing & Observability

A practitioner's tour of data quality as a data engineering interview topic: the five dimensions of data quality (accuracy, completeness, timeliness, consistency, uniqueness) illustrated with concrete failures, the tradeoffs of enforcing checks at the source, in-pipeline, or at the warehouse, schema contracts between producers and consumers and how to catch breaking changes before they ship, freshness/volume/distribution anomaly monitoring and why it catches what a schema check never will, dbt tests versus dedicated data-observability tooling and when each is the right layer, and a full incident-response walkthrough — triage, containment, root cause, prevention — for a pipeline that silently broke a downstream dashboard.

Spark Performance Tuning

A practitioner's guide to Spark performance tuning as a data engineering interview topic: what a shuffle actually costs and what triggers one, diagnosing and fixing data skew with salting and adaptive query execution, when a broadcast join is a free win and when the threshold needs tuning, sizing partitions against the target-file-size heuristic, choosing a persistence storage level (and knowing when caching makes things worse), the execution-vs-storage memory split and what spill to disk looks like, reading the Spark UI's stage and task views to find the actual bottleneck, and a fully worked diagnose-and-fix walkthrough that ties all of it together.