Subjects

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.

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.

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.

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.