Scenario based Data Engineer interview questions

Work from evidence, not guesses.

1. Capture the exact query and its parameters. Run EXPLAIN (ANALYZE, BUFFERS). Compare it with an old plan if you saved one.
2. Compare estimates with actual numbers. If they are far apart, run ANALYZE on the tables. Data growth or a bulk load may have made the statistics old.
3. Look for a changed plan. A seq scan may have replaced an index scan. A bad join order may have replaced a good one.
4. Check table and index bloat (wasted dead space). Check that autovacuum keeps up.
5. Check for locks and contention in pg_stat_activity. Look for other heavy jobs running at the same time.
6. Check data growth. The table may have grown to a size where the plan changes. A new uneven value may have appeared.
7. Check recent changes: a dropped index, a schema migration, a new parameter value, or a version upgrade.

Fix the root cause. It may be statistics, an index, a query rewrite, or partitioning. Then add monitoring with pg_stat_statements so you see the problem early next time.

What they are checking: Do you have a clear, step by step way to debug slow queries?

Islands are runs of consecutive values. Gaps are the breaks between them. The standard trick is to subtract a ROW_NUMBER from the value. Inside one island, the difference stays the same.

Do two steps in one transaction. First close the changed current rows. Then insert the new versions.

Mark the start of a new session when the gap from the previous event is more than 30 minutes. Then take a running sum of the marks. That sum is the session id.

PostgreSQL has a short option called DISTINCT ON. It keeps the first row of each group, based on the ORDER BY.

Avoid one giant transaction. It holds locks, makes the WAL (write-ahead log) very large, and can run for hours.

Options:

• Do the work in batches of 10,000 to 100,000 rows. Commit after each batch and sleep briefly between batches:

Give each user a cohort (their signup month). Work out the months since signup for each activity. Then count distinct active users for each cohort and month offset.

Generate the calendar with generate_series. Then LEFT JOIN the data to it.

Start with EXPLAIN ANALYZE. Then reduce the work.

First make things stable, then fix the cause, then prevent it from happening again.

1. Assess: read the logs. Find the failed task, the error, and what was already written. Check that the target is in a consistent state for users.
2. Communicate: tell downstream owners that the data is partial, and until when.
3. Fix the cause: bad data, schema change, timeout, credentials, or capacity.
4. Rerun safely: loads should be idempotent, so rerun the failed task and its downstream tasks for the same logical date. Do not rerun the finished ones. In Airflow, clear the failed task instance.
5. Reconcile: compare row counts and key totals between source and target.
6. Prevent: write to staging and swap or commit at once. Add retries with backoff, data quality gates, and alerts on failure and freshness.

If the pipeline is not idempotent, fix that first. Make the affected loads partition-overwrite or MERGE, so a rerun cannot create duplicates.

What they are checking: Whether you can recover calmly using idempotency and reconciliation

Measure the problem, find the source, fix it, then prevent it.

Find the duplicates:

SELECT order_id, COUNT(*) FROM fact_orders GROUP BY order_id HAVING COUNT(*) > 1;

Check the load timestamps or batch ids on the duplicate rows. If the copies came from different batches, a rerun appended instead of overwriting. If they came from the same batch, the source or a join made them. A one-to-many join can multiply rows. The source may also have sent the record twice.

Fix: keep one row per key with ROW_NUMBER on a rule such as the latest loaded_at. Then rewrite the table or partition.

Prevent:

• Make loads idempotent (MERGE on the business key or partition overwrite).
• Add a unique constraint or a dbt unique test on the key.
• Remove duplicates in staging.
• Reconcile counts with the source after each load.

Do not just delete rows without knowing the cause. The duplicates will come back.

What they are checking: Whether you look for the root cause, not only a cleanup query

Treat it as a controlled reprocessing job.

Monitor both the jobs and the data.

Job health: failures, retries, run time compared with normal, queue time, resource use, missed schedules.

Data health: freshness (the latest timestamp is recent), volume (row count in an expected range), schema changes, null rate, duplicates, and reconciliation with the source.

Start from the needs: freshness, volume, history, and users.

Design for limits, retries and restarts.

Do not depend on column position.

Measure first, then cut the biggest items.

Incremental loads based on updated_at never see a hard delete. So you need an explicit approach.

Treat it as a performance regression and find where the time went.

I compare run-time limits, data volume, cost model and effort to operate.

Start with the business process (placing an order). Then declare the grain: one row per order line item.

Fact: fact_order_line

• Keys: date_key, customer_key, product_key, store_or_channel_key, promotion_key, payment_key.
• Degenerate dimension: order_number.
• Measures: quantity, unit_price, discount_amount, tax_amount, net_amount (all additive).

Dimensions:

• dim_date (day, week, month, quarter, holiday flag).
• dim_customer (SCD Type 2 for segment and city; keep the natural key).
• dim_product (name, brand, category, hierarchy).
• dim_channel, dim_promotion, and a junk dimension for flags like is_gift.

Extras: a separate fact_returns that shares the conformed dimensions. A periodic snapshot for inventory. An accumulating snapshot for order fulfilment (order, ship and delivery dates).

Use surrogate keys. Use an Unknown member (-1) for missing references. Add tests for uniqueness at the grain and for foreign key integrity.

What they are checking: Can you model from start to finish using the Kimball steps?

First check what the query does and how much data it touches.

First decide if it is a correction or a real change.

Late-arriving fact: the fact shows up after its date. Load it normally. But look up the dimension version that was valid on the event date, not the current one. Then reprocess the aggregates and snapshot tables for that date.

Business process: completed trips. Grain: one row per trip.

Find the cost drivers first: query history, warehouse usage and storage.

Plan in phases and keep the risk low.

Never drop the fact silently, because the totals would be wrong. Choose a documented strategy:

Treat driver out-of-memory (OOM) and executor OOM as two separate problems.

Driver: collect() or toPandas() on a large result, a huge broadcast, or metadata for too many partitions. Fix: write to storage, or use take or limit. Raise spark.driver.memory only as a last step.

Use the Spark UI before you change anything.

dropDuplicates removes exact or key-based duplicates. It does not let you choose which row stays. For 'latest per key', use a window function.

Small files overload the file system and slow down later reads. Fix it when writing, and with compaction.

Start from the workload and the node size of the cluster. Then tune using the Spark UI.

First find out why executors die. Then reduce the pressure.

Process only new or changed data, and make the job idempotent (running it twice gives the same result).

The difference is usually the environment, the data size, or serialization.

Read it in chunks. Aggregate or write out each chunk. Never hold all the data at once.

import pandas as pd
totals = {}
for chunk in pd.read_csv("big.csv", chunksize=500_000, usecols=["region", "amount"],
                         dtype={"region": "category"}):
    part = chunk.groupby("region")["amount"].sum()
    for k, v in part.items():
        totals[k] = totals.get(k, 0) + v
print(totals)

Also do these things. Load only the columns you need (usecols). Choose smaller dtypes. Parse dates explicitly. Convert the file to Parquet once. If the data keeps growing, move to DuckDB, Polars (lazy mode), Dask or Spark.

What they are checking: Practical large-file handling

Validate at the edges: right after reading and right before writing. Check the schema, nulls, uniqueness, ranges and links between tables (referential integrity). Fail the job, or set bad rows aside.

import pandas as pd
df = pd.DataFrame({"id": [1, 2, 2], "age": [25, -3, 40]})
errors = []
if df["id"].duplicated().any():
    errors.append("duplicate ids")
bad = df[~df["age"].between(0, 120)]
if not bad.empty:
    errors.append(f"{len(bad)} rows with invalid age")
print(errors)

Useful libraries: Pandera, Great Expectations, Pydantic (row by row) and dbt tests. Good practice: write rejected rows to a quarantine table with a reason. Track row counts in and out. Alert when the failure rate crosses a limit. Do not drop data silently.

What they are checking: Reliability mindset

Idempotent means that running a job twice leaves the same final state as running it once.

Profile first (%timeit, cProfile). Then fix the biggest cost.

Fix the data first. Then change the design so a rerun does no harm.

Load the batch into a staging table. Then merge it into the target table with one SQL statement, inside a transaction.

Loop until there is no next page. Reuse one session. Respect rate limits. Retry temporary errors with backoff (wait longer after each failure).

Make both sides smaller first. Then merge. Watch out for duplicate keys.

Save the highest value of a column that only grows (such as updated_at or an id) from the last successful run. Then query only the rows beyond it. This saved value is called a watermark.

Work from the file toward the scheduler.

Check these in order:

Queued means the scheduler gave the task to the executor, but no worker has started it.

Fix the cause. Then clear only the failed task, and its downstream tasks if you want, in the UI or CLI. Airflow reruns just those task instances.

Use four idempotent tasks, keyed on the logical date. Airflow orchestrates them, and other systems do the work.

Use a backfill, or clear the past runs, with limits, on idempotent tasks.

The task process may have died without telling Airflow. Or it may be waiting on something forever.

Parse time is the time spent running your DAG files. So cut the work done at module level.