Data Engineer interview prep

Short answer: SELECT retrieves rows from tables. The logical execution order does NOT match the written order: first FROM, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT.

In depth:

Written (syntactic) order:

SELECT   DISTINCT col1, agg(col2)
FROM     t
WHERE    cond
GROUP BY col1
HAVING   agg_cond
ORDER BY col1
LIMIT    10 OFFSET 20;

Logical execution order:

1. FROM / JOIN      -- which tables, how to join
2. WHERE            -- row filter BEFORE grouping
3. GROUP BY         -- grouping
4. HAVING           -- group filter AFTER aggregation
5. SELECT           -- evaluate expressions, aliases
6. DISTINCT         -- remove duplicates
7. ORDER BY         -- sorting
8. LIMIT / OFFSET   -- slice

⚠️ Gotcha: an alias from SELECT cannot be used in WHERE (since WHERE runs before SELECT), but it can be used in ORDER BY and often in GROUP BY (depends on the DBMS). Example of the error:

SELECT salary * 12 AS annual FROM emp WHERE annual > 100000; -- ERROR
SELECT salary * 12 AS annual FROM emp ORDER BY annual;        -- OK

Short answer: OLTP serves short transactions — inserting and updating individual rows with low latency, while OLAP answers analytical queries that scan millions of rows. Row storage is common for OLTP and columnar storage for OLAP; these are workload categories, not mandatory storage layouts. You don't run heavy analytics on the production OLTP database because it competes for resources with transactions and tanks production latency.

In depth:

1. Workload profile — OLTP: many small operations (INSERT/UPDATE by primary key); OLAP: few heavy queries with GROUP BY and aggregations.
2. Storage model — row storage reads the whole row (good for transactions); columnar reads only the needed columns and compresses better (good for analytics).
3. Normalization — OLTP often uses normalization for write integrity; OLAP often uses dimensional or denormalized models for analysis. Hybrid designs exist.
4. Resource isolation — an analytical scan floods the buffer cache and disk, so transactions start to wait.

Criterion	OLTP	OLAP
Purpose	transactions	analytics
Operations	row reads/writes	column aggregations
Typical storage	row-wise	columnar
Common model	normalized	dimensional/star
Metric	latency, TPS	scan throughput

⚠️ Common mistake: running reports directly against the production database. Extract data into a separate warehouse (ETL/ELT) so analytics doesn't disturb transactions.

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: 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: 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.