Intermediate Data Engineering interview questions

150 questions with answers (DEN-151 to DEN-300). Try each one aloud before reading the answer.

SQL Fundamentals

DEN-151 · Write a query to find employees who earn more than their managers.

Use a self join on the manager ID.

SELECT e.name
FROM emp e
JOIN emp m ON e.manager_id = m.id
WHERE e.salary > m.salary;

DEN-152 · How do you find duplicate emails in a users table?

Group by the column and keep the groups with more than one row.

SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

DEN-153 · Find customers who have never placed an order.

Use an anti-join. NOT EXISTS is NULL-safe and usually well optimized.

SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

DEN-154 · Count orders per customer, including customers with zero orders.

Use a LEFT JOIN and count a column from the right table, so unmatched customers get 0.

SELECT c.id, COUNT(o.id) AS order_cnt
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

DEN-155 · What is the difference between IN and EXISTS?

IN compares a value to a list or subquery result. EXISTS checks whether a correlated subquery returns any row and stops at the first match. Modern optimizers often plan both as semi-joins, but NOT EXISTS is safer than NOT IN when the subquery can return NULLs.

DEN-156 · In a LEFT JOIN, what is the difference between putting a filter in ON vs in WHERE?

A condition in ON restricts which right-side rows match but keeps every left row. A condition on right-table columns in WHERE runs after the join and removes the NULL-extended rows, turning it into an inner join.

LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'PAID'

DEN-157 · Find the department with the highest total salary.

SELECT dept_id, SUM(salary) AS total
FROM emp
GROUP BY dept_id
ORDER BY total DESC
FETCH FIRST 1 ROW WITH TIES;   -- or LIMIT 1

WITH TIES returns every department that shares the top total.

DEN-158 · How do you delete duplicate rows but keep one copy?

Number the rows within each duplicate group and delete those numbered above 1.

WITH d AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM users)
DELETE FROM users WHERE id IN (SELECT id FROM d WHERE rn > 1);

DEN-159 · How do you pivot rows into columns without a PIVOT operator?

Use conditional aggregation.

SELECT product_id,
  SUM(CASE WHEN qtr = 'Q1' THEN sales ELSE 0 END) AS q1,
  SUM(CASE WHEN qtr = 'Q2' THEN sales ELSE 0 END) AS q2
FROM sales GROUP BY product_id;

DEN-160 · How do you compute each category's percentage of total sales?

Divide each group's sum by the grand total, using a window over the aggregate.

SELECT category,
       SUM(amount) * 100.0 / SUM(SUM(amount)) OVER () AS pct
FROM sales GROUP BY category;

DEN-161 · What do ROLLUP, CUBE and GROUPING SETS do?

They produce several grouping levels in one query. ROLLUP(a, b) gives (a, b), (a) and the grand total. CUBE(a, b) gives all combinations. GROUPING SETS lists exactly the groupings you want. GROUPING() identifies subtotal rows.

DEN-162 · Find the latest order for each customer.

SELECT * FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id
                                 ORDER BY order_ts DESC) AS rn
  FROM orders o) t
WHERE rn = 1;

Add a tiebreaker column to the ORDER BY if timestamps can repeat.

DEN-163 · What do ON DELETE CASCADE and ON DELETE SET NULL do?

They are referential actions on a foreign key. CASCADE deletes child rows automatically when the parent is deleted. SET NULL sets the child's foreign key to NULL. The default (NO ACTION/RESTRICT) blocks the delete.

Advanced SQL & Query Optimization

DEN-164 · Name some window functions and their uses.

Ranking: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(n). Offset: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(). Aggregates over windows: SUM() OVER (PARTITION BY … ORDER BY …) for running totals and moving averages.

DEN-165 · What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

For values 100, 90, 90, 80: ROW_NUMBER gives 1, 2, 3, 4 (unique). RANK gives 1, 2, 2, 4 (skips after ties). DENSE_RANK gives 1, 2, 2, 3 (no gaps).

DEN-166 · Write a running total of daily revenue.

SELECT order_date, revenue,
       SUM(revenue) OVER (ORDER BY order_date
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM daily_revenue;

Specifying ROWS avoids RANGE treating tied dates as one peer group.

DEN-167 · Compute month-over-month revenue growth.

SELECT month, revenue,
       (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0
         / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) AS mom_pct
FROM monthly_revenue;

NULLIF prevents division by zero.

DEN-168 · Get the top 3 highest-paid employees in each department.

SELECT * FROM (
  SELECT e.*, DENSE_RANK() OVER (PARTITION BY dept_id
                                 ORDER BY salary DESC) AS r
  FROM emp e) t
WHERE r <= 3;

Use ROW_NUMBER instead to return exactly three rows per department regardless of ties.

DEN-169 · How do you traverse an org hierarchy with a recursive CTE?

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS lvl FROM emp WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.lvl + 1
  FROM emp e JOIN org o ON e.manager_id = o.id)
SELECT * FROM org;

An anchor member seeds the result, and the recursive member repeats until no new rows appear.

DEN-170 · Compute a 7-day moving average.

SELECT day, sales,
       AVG(sales) OVER (ORDER BY day
                        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily_sales;

This assumes one row per day. Fill missing days with a date spine first.

DEN-171 · How does a composite index's column order matter?

A B-tree index on (a, b, c) is sorted by a, then b, then c, so it serves predicates on a, a, b or a, b, c (the leftmost prefix). A filter on b alone usually cannot seek on it.

DEN-172 · What is a covering index?

An index that contains every column a query needs, either as key columns or included columns, so the database answers from the index alone without looking up the base table. This is called an index-only scan.

DEN-173 · What is a SARGable predicate?

A Search-ARGument-able condition that can use an index seek. WHERE order_date >= '2024-01-01' is SARGable. WHERE YEAR(order_date) = 2024 wraps the column in a function and usually forces a scan.

DEN-174 · Why avoid SELECT * in production queries?

It reads unnecessary columns, which is especially costly in columnar warehouses billed by bytes scanned. It also defeats covering indexes, increases network transfer and breaks downstream code when the schema changes.

DEN-175 · How do you find consecutive login streaks (gaps and islands)?

Subtracting a row number from the date gives a constant for each consecutive run.

SELECT user_id, MIN(d) AS start_d, COUNT(*) AS streak
FROM (SELECT user_id, d,
        d - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d) AS INT) AS grp
      FROM (SELECT DISTINCT user_id, login_date AS d FROM logins) x) t
GROUP BY user_id, grp;

Date-minus-integer syntax varies by database.

DEN-176 · How do you upsert with MERGE?

MERGE INTO dim_customer t
USING stg_customer s ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET t.email = s.email
WHEN NOT MATCHED THEN INSERT (customer_id, email)
                      VALUES (s.customer_id, s.email);

The source must have unique keys, or the MERGE fails or behaves nondeterministically.

Data Modeling

DEN-177 · What is a star schema vs a snowflake schema?

A star schema has a central fact table joined directly to denormalized dimension tables: fewer joins, simpler and faster queries. A snowflake schema normalizes dimensions into sub-tables (product → category): less redundancy but more joins.

DEN-178 · What is the difference between SCD Type 1 and Type 2?

Type 1 overwrites the attribute and keeps no history. Type 2 inserts a new row with a new surrogate key, plus valid_from/valid_to dates and an is_current flag, so it preserves full history.

DEN-179 · What are SCD Types 0, 3, 4 and 6?

Type 0 keeps the original value and never changes it. Type 3 adds a "previous value" column, giving limited history. Type 4 moves history to a separate history table. Type 6 combines 1 + 2 + 3: Type 2 rows plus a current-value column overwritten on all rows.

DEN-180 · What are the three main types of fact table?

Transaction facts hold one row per event. Periodic snapshot facts hold one row per entity per period, such as a daily account balance. Accumulating snapshot facts hold one row per process instance, updated as milestones occur, such as order → ship → deliver dates.

DEN-181 · What is a factless fact table?

A fact table with only dimension keys and no numeric measures. It records events or coverage, such as student attendance or products on promotion, and you analyze it with COUNT.

DEN-182 · What is a junk dimension?

A single dimension that combines several low-cardinality flags and indicators (payment type, gift-wrap flag, channel) to avoid cluttering the fact table with many tiny dimensions.

DEN-183 · What is a role-playing dimension?

One physical dimension used several times in a fact table with different meanings, such as a Date dimension used as order date, ship date and delivery date through views or aliases.

DEN-184 · How do you model a many-to-many relationship in a dimensional model?

Use a bridge table between the fact and the dimension, for example account ↔ customers, often with a weighting factor so measures are allocated correctly and are not double-counted.

DEN-185 · What is Kimball's four-step dimensional design process?

• Select the business process. 2. Declare the grain. 3. Identify the dimensions. 4. Identify the facts (measures).

DEN-186 · Give an example of a 2NF violation and how to fix it.

In order_items(order_id, product_id, product_name, qty) with key (order_id, product_id), product_name depends only on product_id, which is a partial dependency. Fix it by moving product_name into a products(product_id, product_name) table.

DEN-187 · What is BCNF?

Boyce-Codd Normal Form: for every non-trivial functional dependency X → Y, X must be a superkey. It is stricter than 3NF and removes anomalies that remain when overlapping candidate keys exist.

DEN-188 · What is the One Big Table (OBT) approach?

Pre-joining facts and dimension attributes into one wide, denormalized table. Columnar warehouses scan it efficiently, and BI users find it simple. The trade-offs are redundancy, costly rebuilds when dimensions change, and weaker reuse.

DEN-189 · How would you model clickstream event data?

Use an append-only event fact at the grain of one event, with timestamp, user, session, page and device keys, and keep variable properties in a semi-structured column (JSON/VARIANT). Partition by event date, and derive sessions and funnels downstream.

Relational & NoSQL Databases

DEN-190 · What is the difference between B-tree and LSM-tree storage engines?

B-trees update pages in place and give fast reads, which suits read-heavy OLTP (PostgreSQL, InnoDB). LSM trees buffer writes in memory and flush sorted immutable files that are later compacted, which suits write-heavy workloads (Cassandra, RocksDB) at the cost of read and compaction overhead.

DEN-191 · What is MVCC?

Multi-Version Concurrency Control keeps several versions of a row so readers see a consistent snapshot without blocking writers, and writers do not block readers. PostgreSQL, Oracle and InnoDB use it, and old versions must be cleaned up later.

DEN-192 · What are dirty reads, non-repeatable reads and phantom reads?

A dirty read sees uncommitted changes. A non-repeatable read gets a different value when it re-reads the same row. A phantom read finds new or missing rows when it re-runs the same range query. Read Committed, Repeatable Read and Serializable prevent these progressively.

DEN-193 · What are partition keys and clustering keys in Cassandra?

The partition key is hashed to decide which node stores the data, and all rows with that key live together. Clustering keys sort rows within the partition, which enables efficient range queries inside it.

DEN-194 · When should you embed vs reference documents in MongoDB?

Embed when related data is read together, is bounded in size and belongs to the parent, such as order lines in an order. Reference when data is shared, large, unbounded or updated independently, such as customers referenced by many orders.

DEN-195 · What are partition keys, sort keys and GSIs in DynamoDB?

The partition key determines data distribution. The optional sort key orders items within a partition, enabling range queries. A Global Secondary Index provides a different partition/sort key for alternate access patterns and is eventually consistent.

DEN-196 · What is consistent hashing?

A scheme that places nodes and keys on a hash ring, with each key owned by the next node clockwise. Adding or removing a node moves only about 1/N of the keys, which is why Cassandra and DynamoDB use it, often with virtual nodes for balance.

DEN-197 · What does R + W > N mean in quorum replication?

With N replicas, a write waits for W acknowledgements and a read queries R replicas. If R + W > N, the read and write sets overlap in at least one replica, so a read sees the latest acknowledged write.

DEN-198 · What is optimistic vs pessimistic locking?

Pessimistic locking locks rows before updating them (SELECT … FOR UPDATE), which suits high contention. Optimistic locking checks a version column at update time and retries on conflict, which suits low contention.

UPDATE acct SET bal = 90, ver = ver + 1 WHERE id = 1 AND ver = 7;

DEN-199 · What is a deadlock, and how do databases handle it?

Two or more transactions each wait for locks held by the other, so none can proceed. Databases detect the cycle and abort one victim transaction. Prevent deadlocks by acquiring locks in a consistent order and keeping transactions short.

DEN-200 · What is a write-ahead log (WAL)?

An append-only log where changes are recorded durably before the data pages are modified. It enables crash recovery by replaying the log, and it also powers replication and log-based CDC.

DEN-201 · Why use connection pooling?

Opening database connections is expensive in CPU, memory and authentication. A pool (PgBouncer, HikariCP, SQLAlchemy pool) reuses a bounded set of connections, which cuts latency and protects the database from connection storms.

DEN-202 · What are common uses of Redis, and how does it persist data?

Redis is used for caching, sessions, rate limiting, leaderboards (sorted sets), queues and pub/sub. It persists data through RDB snapshots taken at intervals and/or an AOF (append-only file) log of writes, or it can run purely in memory.

Data Warehousing

DEN-203 · Why does columnar storage suit OLAP?

Queries read only the columns they reference. Similar values stored together compress well (dictionary, run-length encoding), and min/max metadata allows skipping blocks. Vectorized execution processes column batches efficiently.

DEN-204 · What are Redshift distribution styles and sort keys?

The distribution styles are KEY (rows with the same key go to the same slice, which co-locates joins), EVEN (round-robin), ALL (a full copy on every node, for small dimensions) and AUTO. Sort keys order data on disk so zone maps can skip blocks on range filters.

DEN-205 · How do partitioning and clustering work in BigQuery?

Partitioning by a date/timestamp, ingestion time or integer range splits the table so queries filtering on that column scan only the matching partitions. Clustering sorts data within partitions by up to four columns, which reduces bytes scanned for filters and aggregations on them.

DEN-206 · What are Snowflake micro-partitions and clustering keys?

Snowflake stores tables automatically as immutable micro-partitions of roughly 50–500 MB uncompressed, with min/max metadata per column for pruning. A clustering key keeps related rows co-located to improve pruning on large tables, with automatic reclustering that consumes credits.

DEN-207 · What is a late-arriving dimension (early-arriving fact)?

A fact arrives before its dimension record exists. Insert an "inferred member" placeholder dimension row with the business key so the fact can be loaded, then update that row when the real dimension data arrives.

DEN-208 · Why add an "Unknown" member row to dimensions?

A default row, such as key -1 labeled "Unknown", lets facts with missing or invalid foreign keys still load and join with inner joins, instead of being dropped or carrying NULL keys. Counts then remain correct.

DEN-209 · What are aggregate tables, and when do you use them?

Pre-summarized tables at a coarser grain, such as daily sales per store, built from detailed facts. They speed up frequent dashboard queries at the cost of storage and refresh logic.

DEN-210 · Why are hash keys used instead of sequences in distributed warehouses?

Sequences need central coordination and make parallel or multi-source loads order-dependent. A hash of the business key, such as MD5(source || id), is deterministic, needs no lookups and can be computed independently. Data Vault uses this approach.

DEN-211 · How do you load an SCD Type 2 dimension in SQL?

Close the current rows whose attributes changed, then insert new versions.

UPDATE dim_cust d SET valid_to = CURRENT_DATE, is_current = FALSE
FROM stg s WHERE d.cust_id = s.cust_id AND d.is_current
  AND d.city <> s.city;
INSERT INTO dim_cust (cust_id, city, valid_from, valid_to, is_current)
SELECT s.cust_id, s.city, CURRENT_DATE, '9999-12-31', TRUE
FROM stg s LEFT JOIN dim_cust d ON d.cust_id = s.cust_id AND d.is_current
WHERE d.cust_id IS NULL;

DEN-212 · What are the typical layers of a data warehouse?

Staging/raw (source copies), integration/core (cleaned, conformed, historized data, often 3NF or Data Vault) and presentation/marts (dimensional models and aggregates for BI). In dbt these are often called staging, intermediate and marts.

DEN-213 · How does result caching work in cloud warehouses?

Snowflake and BigQuery return a cached result for an identical query if the underlying data has not changed, without using compute. Snowflake retains results for 24 hours. Non-deterministic functions or data changes invalidate the cache.

DEN-214 · How do warehouses handle high query concurrency?

Through workload management queues and priorities (Redshift WLM), concurrency scaling clusters, Snowflake multi-cluster warehouses that add clusters under load, and BigQuery slot reservations. You can also isolate workloads such as ETL and BI on separate compute.

DEN-215 · What are the trade-offs of materialized views in a warehouse?

They speed up repeated aggregations and joins, and some engines refresh them automatically or rewrite queries to use them. The costs are refresh compute and storage, restrictions on supported SQL, and possible staleness.

ETL/ELT & Orchestration

DEN-216 · What are the main components of Airflow?

The scheduler (triggers DAG runs and queues tasks), the executor and workers (run tasks), the metadata database (state), the webserver/API server (UI) and the DAG files. Airflow 2.x adds the triggerer for deferrable operators.

DEN-217 · What Airflow executors exist, and when do you use each?

SequentialExecutor runs one task at a time, for testing. LocalExecutor uses parallel processes on one machine. CeleryExecutor distributes tasks to a worker pool through a broker. KubernetesExecutor starts one pod per task for isolation and elastic scaling.

DEN-218 · What do start_date, catchup and the logical date mean in Airflow?

A DAG run covers a data interval and is triggered at the end of that interval. Its logical date (formerly execution_date) is the start of the interval. With catchup=True, Airflow creates runs for every missed interval since start_date.

DEN-219 · How do you define task dependencies in Airflow?

extract >> transform >> [load_dw, load_lake]
# or: transform.set_upstream(extract)

Downstream tasks run only when upstream tasks satisfy their trigger_rule, which defaults to all_success.

DEN-220 · How do you configure retries and alerts for Airflow tasks?

Set retries, retry_delay and retry_exponential_backoff in default_args, add on_failure_callback for notifications, and use execution_timeout to kill hung tasks.

default_args = {"retries": 3, "retry_delay": timedelta(minutes=5)}

DEN-221 · What are dbt's materializations?

view (the default, always fresh, computed at query time), table (fully rebuilt on each run), incremental (only new or changed rows are inserted or merged) and ephemeral (inlined as a CTE into dependent models, never built). Materialized views are also supported on some adapters.

DEN-222 · Write a dbt incremental model.

{{ config(materialized='incremental', unique_key='order_id') }}
SELECT * FROM {{ source('shop', 'orders') }}
{% if is_incremental() %}
WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

unique_key makes dbt merge updated rows instead of only appending.

DEN-223 · What are dbt's built-in generic tests?

unique, not_null, accepted_values and relationships (referential integrity). They are declared in YAML and compiled to queries that pass when they return zero rows. Packages such as dbt-utils and dbt-expectations add more.

DEN-224 · What are dbt snapshots?

A dbt feature that captures SCD Type 2 history of a mutable source table. It uses the timestamp or check strategy and adds dbt_valid_from and dbt_valid_to columns on each run.

DEN-225 · What is the difference between log-based and query-based CDC?

Log-based CDC (Debezium, AWS DMS) reads the database transaction log. It captures every change, including deletes, with low source load. Query-based CDC polls with WHERE updated_at > last_value. It is simpler, but misses hard deletes and intermediate updates and needs a reliable timestamp column.

DEN-226 · How does a high-water-mark incremental extract work?

Store the maximum updated_at or ID processed in the last successful run, then query rows greater than it. Update the mark only after the load commits. Use a small overlap window plus dedup to catch late-committed rows.

DEN-227 · What is the difference between poke and reschedule mode for Airflow sensors?

In poke mode, the sensor holds a worker slot for the whole wait. In reschedule mode, it releases the slot between checks. Deferrable operators go further and hand the wait to the async triggerer.

DEN-228 · How do you make Airflow tasks process the correct date partition?

Use Jinja templated macros such as {{ ds }} or {{ data_interval_start }} rather than datetime.now(), so reruns and backfills process their own interval deterministically.

BashOperator(task_id="load",
             bash_command="load.sh --date {{ ds }}")

Apache Spark & Big Data

DEN-229 · What happens during a Spark shuffle, and why is it expensive?

Wide transformations (groupBy, join, repartition) redistribute data across partitions by key: map tasks write shuffle files to local disk and reduce tasks fetch them over the network. Disk I/O, serialisation and network transfer make shuffles the most expensive part of most Spark jobs.

DEN-230 · How does a broadcast join work, and when should you use it?

Spark sends a full copy of the small table to every executor so the large table can be joined locally without shuffling it. It suits joins where one side fits comfortably in executor memory (Spark auto-broadcasts below spark.sql.autoBroadcastJoinThreshold, 10 MB by default).

DEN-231 · What is data skew in Spark, and how do you detect it?

Skew occurs when a few keys hold most of the data, so a few tasks run far longer than others. In the Spark UI, a stage with most tasks finishing quickly and a few very long tasks with large shuffle read sizes is the typical sign.

DEN-232 · How do repartition() and coalesce() differ?

repartition(n) performs a full shuffle and can increase or decrease partitions, producing even sizes; coalesce(n) only reduces partitions by merging existing ones without a full shuffle, which is cheaper but can leave uneven partitions.

DEN-233 · What does Adaptive Query Execution (AQE) do in Spark 3?

AQE re-optimises plans at runtime using shuffle statistics: it coalesces small shuffle partitions, switches sort-merge joins to broadcast joins when a side turns out small, and splits skewed partitions in joins.

DEN-234 · What is the Catalyst optimizer?

Spark SQL's query optimiser that turns DataFrame and SQL queries into a logical plan, applies rule-based optimisations (predicate pushdown, constant folding, column pruning) and cost-based choices, then generates a physical plan with whole-stage code generation.

DEN-235 · Why are Python UDFs slow in PySpark, and what are the alternatives?

Row-by-row Python UDFs require serialising data between the JVM and Python workers and block Catalyst optimisations. Prefer built-in functions; if custom logic is needed, use vectorised pandas UDFs (Arrow-based) or write the logic in SQL expressions.

DEN-236 · How do you choose the number of shuffle partitions?

Aim for partition sizes of roughly 100–200 MB after shuffle, enough partitions to keep all cores busy (2–3× total cores), and let AQE coalesce small partitions. The default of 200 is often wrong for very small or very large jobs.

DEN-237 · What is predicate pushdown and partition pruning?

Predicate pushdown applies filters at the data source (e.g. Parquet row-group statistics) so less data is read; partition pruning skips whole directories whose partition values don't match the filter. Both cut I/O dramatically.

DEN-238 · What causes "out of memory" errors on Spark executors, and how do you fix them?

Large partitions, skewed keys, collecting large datasets, too many cached objects, or big broadcast tables. Increase partitions, fix skew, avoid collect(), tune executor memory and overhead, and reduce cached data.

DEN-239 · What is the difference between Spark's client and cluster deploy modes?

In client mode the driver runs on the machine that submitted the job (useful for interactive work); in cluster mode the driver runs inside the cluster, which is more robust for production jobs.

DEN-240 · How does Spark achieve fault tolerance?

RDDs and DataFrames track lineage, so lost partitions are recomputed from source data; shuffle files and checkpoints reduce recomputation for long lineages, and failed tasks are retried on other executors.

Streaming (Kafka, Flink, Spark Streaming)

DEN-241 · How does Kafka guarantee message ordering?

Ordering is guaranteed only within a partition. Messages with the same key go to the same partition, so per-key ordering holds; there is no global ordering across partitions of a topic.

DEN-242 · What do Kafka's acks settings control?

acks=0 doesn't wait for confirmation, acks=1 waits for the leader to write, and acks=all waits for all in-sync replicas. With acks=all and min.insync.replicas ≥ 2, acknowledged messages survive a broker failure.

DEN-243 · What is a watermark in stream processing?

A watermark is the system's estimate of event-time progress: events older than the watermark are considered late. It lets windows close and results be emitted while bounding the state kept for late data.

DEN-244 · How does Kafka achieve exactly-once semantics?

Idempotent producers deduplicate retries per partition using sequence numbers, and transactions let a producer write to several partitions and commit consumer offsets atomically. Consumers reading with isolation.level=read_committed see only committed data, giving exactly-once within Kafka read-process-write pipelines.

DEN-245 · What is consumer lag, and why does it matter?

Lag is the difference between the latest offset in a partition and the consumer group's committed offset. Growing lag means consumers can't keep up, so data freshness falls; it is a key alert for streaming pipelines.

DEN-246 · How does a consumer group rebalance work?

When consumers join, leave or fail, partitions are reassigned among group members. During classic eager rebalances, consumption pauses; cooperative incremental rebalancing reduces this disruption.

DEN-247 · What is stateful stream processing?

Operations such as windowed aggregations, joins and deduplication keep state across events. Engines like Flink and Spark store state in local state backends (e.g. RocksDB) and checkpoint it for recovery.

DEN-248 · How do Flink checkpoints provide fault tolerance?

Flink periodically injects barriers into streams; operators snapshot their state when barriers pass, creating a consistent global checkpoint. On failure, the job restores state and source offsets from the last checkpoint.

DEN-249 · What is a compacted Kafka topic?

Log compaction keeps at least the latest value for each key and removes older values, so the topic represents the current state of each key, useful for changelogs and lookup tables.

DEN-250 · How do you handle late-arriving events in windowed aggregations?

Set watermarks with an allowed lateness, update results when late events arrive within that bound, route very late events to a side output or dead-letter store, and reconcile with batch processing if needed.

DEN-251 · How do you join a stream with a slowly changing reference table?

Use a broadcast or lookup join against a cached table refreshed periodically, a stream-table join with the table as a compacted changelog, or temporal joins that use the reference version valid at event time.

DEN-252 · What is a schema registry, and why use it with Kafka?

It stores versioned schemas (Avro, Protobuf, JSON Schema) and enforces compatibility rules, so producers and consumers can evolve independently without breaking each other.

Data Lakes & Lakehouse (Delta, Iceberg, Hudi)

DEN-253 · How do table formats like Delta Lake provide ACID transactions on object storage?

They keep a transaction log (Delta log, Iceberg metadata/manifests) listing which data files belong to each table version. Writers commit by atomically adding a new log entry; readers use a consistent snapshot, giving atomicity and isolation without a database server.

DEN-254 · What is the small files problem, and how is it fixed?

Many tiny files (from streaming or frequent appends) slow queries because of file open and metadata overhead. Fix with compaction (OPTIMIZE, rewrite_data_files), auto-compaction, and writing larger batches.

DEN-255 · How do MERGE and upserts work in a lakehouse table?

MERGE matches source rows to target rows on a key, then rewrites affected files with updates, inserts and deletes (copy-on-write) or writes delete files (merge-on-read). Partitioning and clustering limit how many files must be rewritten.

DEN-256 · What is the difference between copy-on-write and merge-on-read?

Copy-on-write rewrites data files on update, giving fast reads but slower writes; merge-on-read writes change logs or delete files and merges at read time, giving fast writes but slower reads until compaction.

DEN-257 · How does Iceberg's hidden partitioning help?

Iceberg derives partitions from column transforms (e.g. day(ts)) and tracks them in metadata, so queries filter on the original column and still prune partitions; partition layouts can evolve without rewriting data.

DEN-258 · What are Z-ordering and liquid clustering?

They co-locate related values of multiple columns in the same files so data-skipping statistics can prune more files for filters on those columns. Liquid clustering lets clustering keys change without full rewrites.

DEN-259 · How does a lakehouse differ from a classic data lake plus warehouse setup?

A lakehouse adds table formats with ACID transactions, schema enforcement, time travel and indexing directly on low-cost object storage, so one copy of data serves BI, data science and streaming. The classic setup keeps a lake for raw data and copies curated data into a separate warehouse.

DEN-260 · How do you choose a partitioning column for a large lake table?

Pick a column that queries filter on frequently (often a date) with moderate cardinality, so partitions are large (hundreds of MB to GBs) and few. Avoid high-cardinality columns that create millions of tiny partitions.

DEN-261 · What does VACUUM do in Delta Lake, and what is the risk?

It deletes data files no longer referenced by table versions older than the retention period, freeing storage. Vacuuming with too short a retention breaks time travel and can corrupt readers still using old snapshots.

DEN-262 · How does schema evolution work in table formats?

They support adding, renaming, reordering and widening columns through metadata changes (by column ID in Iceberg), with compatibility checks. Writers may need explicit options (e.g. mergeSchema) to allow new columns.

DEN-263 · Why is Parquet efficient for analytics?

It stores data by column with encoding (dictionary, run-length) and compression, keeps min/max statistics per row group for skipping, and lets engines read only the needed columns.

DEN-264 · How do you handle a GDPR erasure request in a data lake built on immutable files?

Locate the person's records through a catalog or ID index, delete them with table-format DELETE or MERGE (which rewrites affected files or writes delete files), then expire old snapshots and vacuum so the data is physically removed, including from time-travel history and raw zones. Some teams pseudonymise data and delete the key instead (crypto-shredding).

Cloud Data Platforms (AWS, Azure, GCP, Snowflake, Databricks)

DEN-265 · How does Snowflake separate storage and compute, and why does it matter?

Data lives in cloud object storage in micro-partitions, while independent virtual warehouses provide compute. Teams can scale compute up or out per workload without copying data or competing for resources, and pay for compute only when warehouses run.

DEN-266 · How does BigQuery charge for queries, and how do you reduce cost?

On-demand pricing charges by bytes scanned, so select only needed columns, filter on partitioned and clustered columns, avoid SELECT *, materialise frequently used results, or use capacity (slot) reservations for predictable workloads.

DEN-267 · What is Snowflake's Time Travel and Fail-safe?

Time Travel lets you query, clone or restore data as it was at a past point within the retention period; Fail-safe is an additional period during which Snowflake can recover data for disaster recovery, not directly accessible to users.

DEN-268 · What is zero-copy cloning in Snowflake?

A clone creates a new table, schema or database that shares the original's micro-partitions instead of copying data; only changed data afterwards consumes extra storage. It is ideal for dev and test environments.

DEN-269 · How does Databricks Unity Catalog help governance?

It provides a central catalog with fine-grained access control (tables, rows, columns), lineage tracking, auditing and data sharing across workspaces, replacing per-workspace metastores.

DEN-270 · How would you load files from S3 into Redshift efficiently?

Use the COPY command with many compressed, similarly sized files (multiples of the number of slices), columnar formats where possible, and appropriate distribution and sort keys.

DEN-271 · What is AWS Lake Formation used for?

It centralises permissions and governance for data lakes on S3 and the Glue Data Catalog, enabling table, column and row-level access control across services like Athena and Redshift Spectrum.

DEN-272 · How do you secure data in cloud storage buckets?

Block public access, use least-privilege IAM policies, encryption at rest with managed or customer keys, encryption in transit, VPC endpoints, access logging and regular audits.

DEN-273 · What are Snowflake streams and tasks?

Streams track row-level changes (CDC) on a table since the last consumption; tasks schedule SQL statements or procedures. Together they build incremental pipelines inside Snowflake.

DEN-274 · How do Azure Data Factory, Synapse and Fabric relate?

Data Factory orchestrates data movement and transformation pipelines; Synapse Analytics combines SQL pools, Spark and pipelines for analytics; Microsoft Fabric unifies these capabilities on OneLake storage as a SaaS platform. They overlap, and Fabric is Microsoft's newer integrated direction.

DEN-275 · How would you choose between a managed service and self-managed open-source tools?

Managed services cut operational work, give scaling and security out of the box and speed delivery, but cost more per unit and risk lock-in; self-managed tools offer control and portability at the cost of engineering effort. Team size, skills, scale and compliance needs decide.

DEN-276 · What is Delta Sharing or secure data sharing in Snowflake?

Both let providers share live, governed data with other organisations without copying files: Snowflake shares grant access to objects across accounts, and Delta Sharing is an open protocol for sharing Delta tables with any client.

Data Quality, Governance & Security

DEN-277 · How would you implement data quality checks in a pipeline?

Define expectations per dataset (schema, not-null, uniqueness, ranges, referential integrity, freshness, volume) using tools like Great Expectations, dbt tests or Soda; run them at ingestion and transformation stages, and block or quarantine bad data with alerts.

DEN-278 · What is a data contract?

An agreed specification between data producers and consumers of a dataset's schema, semantics, quality expectations and SLAs, enforced in CI and at runtime so upstream changes don't silently break downstream pipelines.

DEN-279 · How does column-level lineage help data teams?

It shows exactly which upstream columns feed each downstream column, so teams can assess the impact of schema changes, trace errors to their source and prove where sensitive data flows.

DEN-280 · How do you implement row-level security in a warehouse?

Use row access policies or secure views that filter rows based on the querying user's role or attributes (e.g. region), often driven by a mapping table, so one table serves users with different permissions.

DEN-281 · What is dynamic data masking?

Policies show masked values (e.g. ****1234) to users without permission while authorised roles see real data, applied at query time without changing stored data.

DEN-282 · What is tokenisation versus encryption for sensitive data?

Encryption transforms data with a key and can be reversed by key holders; tokenisation replaces values with tokens mapped in a secure vault, preserving format and keeping real values out of analytics systems.

DEN-283 · How do you detect anomalies in data quality automatically?

Track metrics such as row counts, null rates, distinct counts and distributions over time and alert on statistical deviations (data observability tools like Monte Carlo or Elementary).

DEN-284 · What is data observability, and how does it differ from data quality testing?

Data quality tests check known rules you wrote in advance; observability continuously monitors freshness, volume, schema changes, distributions and lineage across all tables to catch unknown problems, and links incidents to upstream causes and downstream impact.

DEN-285 · How do you apply least privilege to a data platform?

Grant access through roles mapped to groups rather than individuals, give each role only the schemas, tables and actions it needs, separate read from write and production from development, use time-limited elevated access for admins, and review and audit grants regularly.

DEN-286 · How do you find and classify sensitive data across hundreds of tables?

Run automated scanners that detect PII patterns and column names (emails, phone numbers, national IDs), confirm results with data owners, store classification tags in the catalog, and attach masking and access policies to tags so new tables inherit protection automatically.

DEN-287 · How do you enforce data retention policies in practice?

Define retention per data category and legal requirement, implement automated deletion or archival jobs (including lifecycle rules on object storage), make sure backups, replicas, logs and time-travel history follow the same rules, and record evidence of deletion for audits.

DEN-288 · How do you make datasets discoverable and trusted by analysts?

Publish catalog entries with business descriptions, owners, update frequency, freshness status, quality test results, lineage and example queries, certify gold datasets, and deprecate duplicates. Trust grows when people can see who owns data and whether it passed checks today.

Python, Pipeline Design & Cost Optimization

DEN-289 · What makes a data pipeline idempotent, and why does it matter?

Running it twice for the same input produces the same result, with no duplicates or double counting, for example by overwriting a date partition or using MERGE on keys instead of blind appends. It makes retries and backfills safe.

DEN-290 · How do you process a file too large to fit in memory with Python?

Stream it in chunks (pandas.read_csv(path, chunksize=100_000), generators or pyarrow record batches), process and write each chunk incrementally, and keep only running aggregates in memory. For heavier work, use out-of-core engines such as Polars lazy mode or DuckDB, or a distributed engine like Spark.

DEN-291 · Why can pandas be slow, and how do you speed it up?

Row-wise loops and apply run in Python, and object dtypes are slow. Use vectorised operations, categorical and numeric dtypes, Arrow-backed types, and switch to Polars or DuckDB for large datasets.

DEN-292 · How do you design retries for a pipeline that calls an external API?

Retry only transient errors (timeouts, 429, 5xx) with exponential backoff and jitter, cap attempts, respect rate limits, make requests idempotent, and send persistent failures to a dead-letter store with alerts.

DEN-293 · How do you test data pipelines?

Unit test transformation functions with small fixtures, run integration tests on sample data in a staging environment, add data quality tests on outputs, and run CI checks (linting, type checks, dbt tests) on every change.

DEN-294 · What is the difference between batch, micro-batch and streaming pipelines in cost and latency?

Batch runs periodically with low cost and high latency; micro-batch (e.g. every minute) reduces latency at moderate cost; true streaming gives seconds-level latency but needs always-on resources and more complex state handling.

DEN-295 · How would you reduce a cloud warehouse bill that doubled in three months?

Find the top-cost queries, users and warehouses from usage views; fix full-table scans with partitioning or clustering, right-size and auto-suspend warehouses, schedule heavy jobs efficiently, remove unused tables and duplicate pipelines, and set budgets and alerts.

DEN-296 · How do you choose storage formats and compression for a data lake?

Use columnar formats (Parquet, ORC) for analytics with Snappy or ZSTD compression, row formats (Avro, JSON) for raw ingestion and streaming, and table formats on top for ACID and schema evolution.

DEN-297 · How do you design a pipeline to backfill two years of history safely?

Process in partitioned chunks (e.g. per day), make tasks idempotent, limit concurrency to protect sources and costs, validate each chunk, and run the backfill separately from daily production jobs.

DEN-298 · How do you manage configuration and secrets in pipelines?

Keep configuration in version-controlled files or environment variables per environment, and store secrets in a secret manager (AWS Secrets Manager, Vault) rather than code or logs.

DEN-299 · What is the difference between ELT with dbt and transformation in Spark?

dbt runs SQL transformations inside the warehouse with testing and documentation, ideal for structured analytics; Spark handles large-scale, complex or unstructured processing and ML features outside the warehouse.

DEN-300 · How do you estimate the cost of a new data pipeline before building it?

Estimate input volume and growth, run frequency and compute time per run (from a prototype on sample data), then apply cloud prices for compute, storage, network egress and managed services, plus monitoring and maintenance effort. Compare design options (batch vs streaming, warehouse vs Spark) on monthly cost and latency.

Other levels: Basic · Advanced · Tricky · All Data Engineering questions