Data engineering and ETL quiz

Question 1

What is the fundamental difference between ETL and ELT, and why has ELT become dominant in cloud data warehousing?

Accepted Answer

ELT loads raw then transforms in-warehouse, leveraging elastic cloud compute; ETL transforms first

Answer

ELT transforms before loading; ETL after — ELT is faster on-premise

Answer

ETL and ELT are identical; the name only depends on the vendor

Answer

ELT only works for streaming while ETL only works for batch

Question 2

In Apache Spark, what is a shuffle and why is it a primary performance bottleneck?

Accepted Answer

Redistribution of data across partitions for wide transformations, costly due to disk/network I/O

Answer

An in-memory cache eviction event

Answer

The process of reading Parquet footers

Answer

A garbage collection pause on the driver

Question 3

What does idempotency mean for a data pipeline, and why is it critical for reliable ETL?

Accepted Answer

Re-running with the same input yields the same final state, enabling safe retries/backfills

Answer

The pipeline runs only once and refuses retries

Answer

The pipeline processes records in strict insertion order

Answer

Each run uses a unique random partition key

Question 4

Explain Slowly Changing Dimension (SCD) Type 2 and how it differs from Type 1.

Accepted Answer

Type 1 overwrites (no history); Type 2 adds new versioned rows with effective dates/flags

Answer

Type 2 overwrites values; Type 1 keeps full history

Answer

Both keep history but Type 2 uses partitions

Answer

Type 2 deletes old rows while Type 1 archives them

Question 5

What is Change Data Capture (CDC), and what advantage does log-based CDC have over query-based CDC?

Accepted Answer

Log-based reads the transaction log: low-latency, captures deletes, minimal source load vs polling

Answer

Log-based CDC requires full table scans each run

Answer

Query-based CDC is always lower latency than log-based

Answer

They are functionally identical; only naming differs

Question 6

In stream processing, what is a watermark and what problem does it solve?

Accepted Answer

A heuristic event-time bound used to finalize windows despite late/out-of-order events

Answer

A checksum verifying record integrity

Answer

A throttling token controlling ingestion rate

Answer

A column marking deleted records in CDC

Question 7

Why are columnar file formats like Parquet preferred over row formats (CSV/Avro) for analytical workloads?

Accepted Answer

Columnar layout enables column pruning, better compression, and predicate pushdown for analytics

Answer

They store rows together for faster transactional updates

Answer

They avoid the need for any schema

Answer

They are always smaller than any compressed row format regardless of access pattern

Question 8

What is the medallion (bronze/silver/gold) architecture in a lakehouse?

Accepted Answer

Raw (bronze) → cleaned/conformed (silver) → business aggregates (gold) layered quality model

Answer

Three replicas of the same table for high availability

Answer

A tiered storage cost optimization (hot/warm/cold)

Answer

A security model with three access roles

Question 9

What problem do open table formats like Apache Iceberg, Delta Lake, and Hudi solve over plain Parquet on object storage?

Accepted Answer

They add an ACID metadata layer enabling transactions, time travel, and schema evolution

Answer

They compress Parquet files more aggressively

Answer

They replace object storage with a relational database

Answer

They only enable faster reads but no write guarantees

Question 10

In Apache Kafka, what does a consumer group enable, and how does it relate to partitions?

Accepted Answer

Each partition is assigned to one consumer in the group, so parallelism is bounded by partition count

Answer

It replicates messages across brokers for durability

Answer

It guarantees global ordering across all partitions

Answer

It compresses messages before delivery

Question 11

What is partition pruning, and how does it differ from predicate pushdown?

Accepted Answer

Pruning skips whole partitions by partition-key filters; pushdown filters within files via statistics

Answer

They are the same operation under different names

Answer

Pruning compresses data; pushdown encrypts it

Answer

Pruning applies only to streaming; pushdown only to batch

Question 12

What is data skew in distributed processing, and name two mitigation strategies.

Accepted Answer

Uneven key distribution causing straggler tasks; mitigate via salting or broadcast joins

Answer

Skew is schema mismatch; fix by casting types

Answer

Skew is clock drift between nodes; fix with NTP

Answer

Skew only affects storage, not compute

Question 13

What are the three semantic delivery guarantees in streaming, and which is hardest to achieve?

Accepted Answer

Exactly-once is hardest, needing atomic offset+output coordination (transactions/idempotency)

Answer

At-least-once is hardest because it requires deduplication

Answer

At-most-once is hardest because retries are mandatory

Answer

All three are equally trivial with checkpointing

Question 14

In dimensional modeling, what is the difference between a star schema and a snowflake schema?

Accepted Answer

Star uses denormalized dimensions (fewer joins); snowflake normalizes dimensions into sub-tables

Answer

Star normalizes dimensions; snowflake denormalizes them

Answer

They differ only in the database vendor used

Answer

Snowflake schema has no fact table

Question 15

What is a data contract, and what problem does it solve in modern data platforms?

Accepted Answer

A versioned producer-consumer agreement on schema/SLAs/quality, enforced to prevent silent breakage

Answer

A legal license for purchasing third-party data

Answer

An encryption key exchange protocol

Answer

A billing agreement between cloud and customer

Question 16

Why is a broadcast join used in Spark, and what is its main risk?

Accepted Answer

It broadcasts a small table to avoid shuffle; risk is OOM if the table is too large

Answer

It sorts both tables on disk; risk is slow I/O

Answer

It replicates the large table; risk is data loss

Answer

It always beats sort-merge join with no downsides

Question 17

What is a surrogate key in a data warehouse and why use it instead of the natural/business key?

Accepted Answer

A system-generated stable integer key, decoupling joins from mutable business keys

Answer

A backup copy of the natural key

Answer

An encrypted version of the primary key

Answer

A foreign key to an external system

Question 18

In Airflow, what is the difference between depends_on_past and wait_for_downstream, and what is a backfill?

Accepted Answer

depends_on_past waits for the same task's prior run; backfill reruns missed historical logical dates

Answer

Both are identical retry settings

Answer

depends_on_past disables retries; backfill deletes old runs

Answer

wait_for_downstream pauses the scheduler globally

Question 19

What is schema evolution, and how do compatible vs breaking changes differ in a streaming/Avro context?

Accepted Answer

Adding/removing fields with defaults stays compatible; renames or incompatible type changes break compatibility

Answer

Any schema change is always safe with Avro

Answer

Schema evolution means rewriting all historical data on every change

Answer

Only column order matters for compatibility

Question 20

What is the small files problem in data lakes, and how is it remediated?

Accepted Answer

Too many tiny files inflating metadata/open costs; fix via compaction/OPTIMIZE and fewer partitions

Answer

Files too large to fit in memory; split them

Answer

Files with wrong permissions; fix ACLs

Answer

Duplicate file names; rename them

Question 21

What is Z-ordering (multi-dimensional clustering) and when does it help?

Accepted Answer

Multi-column clustering via a space-filling curve to enable data skipping on several filter columns

Answer

A sorting of partitions alphabetically

Answer

A compression codec for nested data

Answer

A replication strategy across availability zones

Question 22

Why might you choose a windowed/incremental load over a full reload in ETL, and what is the key correctness risk?

Accepted Answer

Incremental cuts cost/runtime but risks missing late/out-of-order updates near the watermark boundary

Answer

Incremental is always less accurate so never use it

Answer

Full reloads are always cheaper at scale

Answer

Incremental loads cannot handle deletes under any circumstance

Question 23

In dbt, what is the difference between a view, table, and incremental materialization?

Accepted Answer

View = SQL-on-read; table = full rebuild persisted; incremental = merge only new/changed rows

Answer

All three rebuild the full dataset every run

Answer

Incremental never persists data

Answer

Table materialization is computed only at query time

Question 24

What is the difference between OLTP and OLAP systems, and why don't you run analytics directly on OLTP?

Accepted Answer

OLTP = many small transactions (row store); OLAP = large scans/aggregations (columnar); analytics offloaded to avoid contention

Answer

OLTP is for analytics; OLAP is for transactions

Answer

They are interchangeable for any workload

Answer

OLAP cannot store historical data

Question 25

What is a dead-letter queue (DLQ) in a data pipeline and why is it important?

Accepted Answer

A sink for failed/unprocessable records to avoid data loss and pipeline blockage

Answer

A queue for high-priority records

Answer

A cache of the most recent successful records

Answer

A backup of the entire topic

Question 26

What is the difference between a fact table's additive, semi-additive, and non-additive measures?

Accepted Answer

Additive sums across all dims; semi-additive not across time; non-additive (ratios) sum across none

Answer

All measures are additive in a star schema

Answer

Semi-additive means it can only be averaged

Answer

Non-additive measures cannot be stored in fact tables

Question 27

How does exactly-once processing work in a Kafka-to-Kafka pipeline using transactions?

Accepted Answer

Atomic write of outputs + offset commits via transactions, with read_committed consumers

Answer

By disabling all retries on the producer

Answer

By deduplicating with a downstream batch job only

Answer

Exactly-once is impossible in Kafka

Question 28

What is data lineage and why is it essential for governance and debugging?

Accepted Answer

The traceable map of data origins, flows, and transformations for impact analysis and debugging

Answer

The order in which queries execute

Answer

The physical disk layout of tables

Answer

A list of users who accessed the data

Question 29

What is the difference between tumbling, hopping (sliding), and session windows in stream processing?

Accepted Answer

Tumbling = fixed non-overlapping; hopping = overlapping fixed; session = dynamic gap-based

Answer

All three are fixed, non-overlapping windows

Answer

Session windows are always larger than tumbling windows

Answer

Hopping windows never overlap

Question 30

What is the purpose of a staging (landing) area in an ETL/ELT pipeline?

Accepted Answer

An intermediate zone holding raw extracts, decoupling extract from transform and enabling replay

Answer

A live mirror of the production OLTP database

Answer

The final consumption layer for BI dashboards

Answer

A cache for query results

Test yourself