Data Engineer interview prep

Short answer: ETL transforms data before loading (on a separate engine), while ELT first loads raw data into the warehouse and runs transformations inside it in SQL. ELT became the default because cloud MPP warehouses (Snowflake, BigQuery, Redshift) scale compute cheaply and separate storage from compute.

In depth:

1. ETL — transform on an intermediate engine (historically pricey ETL servers). Only clean, ready data lands in the warehouse. Downside: logic is locked in the tool and the raw data is lost.
2. ELT — load the raw layer as-is, transform via dbt/SQL. Upside: raw is preserved, transformations are version-controlled in Git, and the warehouse handles scaling.
3. Why ELT won — separating storage/compute made in-warehouse compute cheap and elastic; analysts prefer keeping logic in SQL.

Criterion	ETL	ELT
Where transform runs	On a separate engine	Inside the warehouse
Raw layer	Usually lost	Preserved
Scaling	Bound by the engine	Elastic (MPP)
Typical stack	Informatica, SSIS	Fivetran + dbt + Snowflake

When ETL still fits: heavy preprocessing before the warehouse, masking PII/compliance (raw cannot be loaded), or a source easier to coerce to schema on the fly.

⚠️ Common mistake: thinking ELT means "no transformations." The transformations didn't disappear — they just moved into the warehouse and run after loading.

Short answer: The driver builds the plan and coordinates execution, the cluster manager (YARN, Kubernetes, Standalone) allocates resources, and executors on the workers run tasks and hold data in memory. Every action launches a job, which the scheduler cuts into stages at shuffle boundaries, and each stage into tasks by partition count.

In depth:

1. Driver — the process running your code and the SparkSession. It builds the logical and physical plan, the DAG of stages, schedules tasks, and collects results. If the driver dies, the whole application dies.
2. Cluster manager — negotiates resources: how many executors, how many cores and how much memory each. It does no computation itself.
3. Executors — JVM processes on the workers. They run tasks, cache partitions, and serve data during shuffles. They live for the application's lifetime.
4. Work hierarchy — Job (one per action) → Stage (bounded by shuffles) → Task (one per partition, the smallest unit of parallelism).

            ┌──────────────┐
            │    Driver    │  plan + DAG + scheduler
            │ SparkSession │
            └──────┬───────┘
                   │ request resources
            ┌──────▼───────┐
            │Cluster Manager│  YARN / K8s / Standalone
            └──────┬───────┘
        ┌──────────┼──────────┐
   ┌────▼────┐ ┌───▼────┐ ┌───▼────┐
   │ Executor│ │Executor│ │Executor│  tasks + cache
   └─────────┘ └────────┘ └────────┘

⚠️ Common mistake: conflating executor cores with tasks — parallelism is capped at num executors × cores, and if there are fewer partitions than slots, some cores sit idle.

Short answer: The partition is the unit of parallelism: within a consumer group each partition is read by exactly one consumer. So effective parallelism is capped by the partition count — consumers beyond that sit idle.

In depth:

1. Consumer group — a set of consumers sharing a group.id, across which partitions are distributed (rebalance). Each partition is assigned to one consumer in the group.
2. Ordering is guaranteed only within a partition. More partitions → more parallelism, but no global ordering across the topic.
3. Relationship: consumers ≤ partitions for full utilization. With more consumers than partitions, the surplus stays idle.
4. Scaling: partitions set the ceiling. Their count is easy to increase but not decrease, and it changes how keys map to partitions.

Partitions	Consumers	Result
4	2	2 partitions per consumer
4	4	1 partition each — max parallelism
4	6	4 work, 2 sit idle

⚠️ Common mistake: adding consumers to go faster while forgetting the ceiling is the partition count. Beyond it, consumers sit with no partitions assigned.

Short answer: In a row format the values of a single record sit together; in a columnar format all values of one column sit together. Analytical queries (aggregates over a few of dozens of columns) read only the needed columns, compress far better and support predicate pushdown — so less I/O and lower cost.

In depth:

1. Column projection (projection pushdown) — a query needs 3 of 50 columns, and a columnar engine reads only those three off disk. A row format must read the whole row.
2. Compression — a column holds homogeneous values (one type, similar data), so dictionary, run-length and delta encoding reach compression ratios several times higher than on a heterogeneous row.
3. Predicate pushdown — using stored per-block min/max statistics, a filter like WHERE date = ... skips whole blocks without reading them.
4. Row format wins for point operations: read/write a whole record (OLTP, one-record-at-a-time streaming).

Row layout:   [id=1,name=A,ts=..][id=2,name=B,ts=..][id=3,...]
               └── record 1 ──┘└── record 2 ──┘

Columnar layout:
  id:   [1, 2, 3, ...]        ← read only the columns
  name: [A, B, C, ...]          you need, each one
  ts:   [.., .., .., ...]       compressed separately

⚠️ Common mistake: treating columnar as universally better. For row-at-a-time writes and whole-record reads (streaming, transactional updates) a row format (Avro) is more efficient.

Short answer: You read a plan bottom-up and inside-out: the leaves are table access (Seq Scan / Index Scan), joins and aggregations sit above. Hunt for the most expensive node, full scans of large tables under a filter, and the chosen join method.

In depth:

1. Reading direction — from the innermost nodes (data access) up to the root (final result). In EXPLAIN ANALYZE compare the estimate (rows=) with reality (actual rows): a large gap exposes stale optimizer statistics.
2. Access method — Seq Scan (full scan) vs Index / Index Only Scan. A full scan of a big table under a selective filter is the first candidate for tuning.
3. Join method — Nested Loop, Hash Join, Merge Join (see table).
4. The expensive node — the one with the largest cost/time; that's where you focus.

Method	When chosen	Cost
Nested Loop	small outer input + index on inner	cheap at small volumes, else O(N·M)
Hash Join	large unsorted sets, equi-join	builds a hash table in memory
Merge Join	both inputs sorted on the key	pays off when sorting already exists

EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= DATE '2026-01-01';

-- Hash Join  (cost=...  rows=...)        <- join method
--   ->  Seq Scan on orders o            <- full scan: filter not on an index
--         Filter: (created_at >= ...)
--   ->  Hash
--         ->  Seq Scan on customers c

⚠️ Common mistake: looking only at the final cost and ignoring the rows vs actual rows gap — that gap is exactly what reveals stale statistics and a bad plan choice.