Data Engineer interview questions and answers

All three functions number the rows inside a window. They differ only in how they treat ties.

• ROW_NUMBER() gives a unique sequence (1,2,3,4). It breaks ties in any order.
• RANK() gives tied rows the same number, then skips numbers (1,2,2,4).
• DENSE_RANK() gives tied rows the same number with no gaps (1,2,2,3).

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;

Use ROW_NUMBER to remove duplicates. Use DENSE_RANK for "Nth highest" questions. Use RANK when the gaps after ties matter.

What they are checking: Do you know how each function treats ties, and can you pick the right one?

The safest way is DENSE_RANK. It handles ties and works for any Nth value.

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r
  FROM employees
) t
WHERE r = 2;

Other ways to do it:

-- Offset approach
SELECT DISTINCT salary FROM employees ORDER BY salary DESC OFFSET 1 LIMIT 1;

-- Subquery approach
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

Note one difference. The window version returns no row (not NULL) when there is no second value. The MAX version returns NULL. Say which result the caller wants.

What they are checking: Can you handle ties, and do you know more than one way?

GROUP BY collapses rows into one row per group. A window function calculates a value across related rows but keeps every input row.

-- One row per department
SELECT dept, AVG(salary) FROM employees GROUP BY dept;

-- Every employee row, plus the department average beside it
SELECT name, dept, salary,
       AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

• PARTITION BY sets the group. ORDER BY sets the order inside the group. A frame (ROWS BETWEEN ...) sets which rows are used.
• Window functions run after WHERE, GROUP BY and HAVING. So you cannot filter on them in WHERE. Put the query in a CTE or subquery instead.

What they are checking: Do you know that a window keeps rows and GROUP BY collapses them, and do you know the run order?

Number the duplicates with ROW_NUMBER over the columns that make a row a duplicate. Then delete every row after the first.

DELETE FROM customers
WHERE ctid IN (
  SELECT ctid FROM (
    SELECT ctid,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM customers
  ) t
  WHERE rn > 1
);

ctid is the physical row id in PostgreSQL. It works even when the table has no primary key. In other databases, use the primary key or another unique id.

For a very large table, copying is often faster. Build a clean copy with SELECT DISTINCT ON (email) ... or the ROW_NUMBER filter, then swap it in. After that, add a unique constraint so the problem cannot come back.

What they are checking: Can you remove duplicates safely and stop them coming back?

Group by the columns that define a duplicate. Keep only the groups with more than one row.

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

To see the full duplicate rows, join back or use a window count:

SELECT *
FROM (
  SELECT c.*, COUNT(*) OVER (PARTITION BY email) AS n
  FROM customers c
) t
WHERE n > 1;

Be clear about what a duplicate means. It could be the same email, or the same email and name. Also think about NULLs. For a unique constraint, NULLs are not equal to each other. But GROUP BY puts all NULLs in one group.

What they are checking: Can you find duplicates and say exactly what a duplicate is?

LAG(col, n, default) returns the value from n rows before the current row, in the window order. LEAD returns the value from n rows after. If there is no default, a missing row gives NULL.

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

Notes:

• Use PARTITION BY to compare inside a group, for example per product.
• NULLIF(..., 0) avoids divide-by-zero errors.
• LAG replaces a self join for "previous row" logic. It is usually faster and easier to read.

What they are checking: Can you compare neighbouring rows without a self join?

Rank the rows inside each group with a window function. Then filter in an outer query.

SELECT *
FROM (
  SELECT category, product, sales,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
  FROM product_sales
) t
WHERE rn <= 3;

Pick the ranking function with care. ROW_NUMBER returns exactly 3 rows per group. RANK or DENSE_RANK may return more when there are ties. Say which result the business wants.

PostgreSQL also supports CROSS JOIN LATERAL with ORDER BY ... LIMIT 3. This can be faster when there is an index on (category, sales) and there are few categories.

What they are checking: Can you solve the classic top-N-per-group problem and talk about ties?

• INNER JOIN: only rows that match in both tables. For example, orders with their customers.
• LEFT JOIN: every row from the left table, with NULLs where the right table has no match. For example, all customers and their orders, including customers with no orders.
• RIGHT JOIN: the mirror of LEFT. Most people swap the tables and use a LEFT JOIN instead.
• FULL OUTER JOIN: all rows from both sides, with NULLs where there is no match. Use it to compare two systems and find records missing from either.

SELECT c.id, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

A common bug: filtering the right table in WHERE (WHERE o.status = 'paid') turns a LEFT JOIN into an inner join. Put that condition in the ON clause instead.

What they are checking: Do you know how each join works, and the WHERE versus ON trap?

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?

ETL changes the data before it loads it into the target. ELT loads the raw data into the target first and changes it there.

ELT became common because warehouses such as Snowflake and BigQuery scale compute on its own. They can run SQL transforms cheaply. Keeping the raw data lets you reprocess it when business rules change.

ETL still fits in two cases. One is when sensitive fields must be masked before they land. The other is when the target cannot do the transformation.

What they are checking: Whether you can explain the shift and the trade-offs

Batch processes a fixed set of data on a schedule, such as hourly or daily. Streaming processes a never-ending flow of events as they arrive.

• Latency: batch takes minutes to hours. Streaming takes seconds or less.
• Complexity: streaming must handle event order, late events, state, and exactly-once results. It is harder to build and run.
• Cost: batch can use cheap spot compute for short windows. Streaming runs all the time.
• Examples: nightly sales reports use batch. Fraud detection and live dashboards use streaming.
• Tools: Airflow with Spark or SQL for batch. Kafka with Flink, Spark Structured Streaming or Kinesis for streaming.

Pick the slowest speed the business can accept. Many systems use micro-batches as a middle path, for example loads every 1 minute.

What they are checking: Whether you choose based on the speed needed, not fashion

A data pipeline is an automatic flow that moves data from sources to a destination. It also prepares the data for use.

Typical stages:

1. Ingest: pull data from databases, APIs, files, or event streams.
2. Land raw: store the data unchanged (in object storage or a raw schema).
3. Clean and transform: fix types, remove duplicates, join tables, apply business rules.
4. Model: build analytics tables such as facts and dimensions.
5. Serve: give the data to BI tools, ML features, or applications.
6. Orchestrate, monitor and test: scheduling, retries, alerts, data quality checks.

A good pipeline is repeatable and idempotent (running it twice gives the same result). It is also easy to watch and well documented.

Give one example. Orders from PostgreSQL land in S3 each day. dbt transforms them, and they load into a warehouse for dashboards.

What they are checking: Whether you see the pipeline from start to end, including operations

A full load reloads the whole dataset each run. An incremental load moves only the new or changed records since the last run.

• Full load: simple and fixes itself, but slow and costly as data grows. Fine for small reference tables.
• Incremental: fast and cheap, but you need a reliable way to detect changes and handle deletes.

Common ways to detect changes:

• An updated_at timestamp.
• An id that always increases.
• A log-based CDC feed (reads the database change log).
• File arrival partitions.

SELECT * FROM orders
WHERE updated_at > :last_watermark
  AND updated_at <= :run_end;

Save the watermark (the last point you loaded) only after the load succeeds. Use a small overlap window, for example read the last 10 minutes again. Use an upsert (update or insert). Then late commits are not missed and duplicates do no harm.

What they are checking: Whether you know watermarks, deletes and overlap

An idempotent pipeline gives the same result however many times you run it for the same input. This makes retries and backfills safe.

Techniques:

• Overwrite by partition: delete and reload one date partition (or use INSERT OVERWRITE) instead of appending.
• Use upsert or MERGE on a business key, not a plain INSERT.
• Make outputs predictable: build file names and ids from the input (a hash or batch date), not from now() or random UUIDs.
• Write to a temp location and swap it in at once on success.
• Keep a run log keyed by batch id, so a finished batch is skipped.
• Avoid side effects that cannot be repeated, such as sending emails. Or guard them.

BEGIN;
DELETE FROM fact_sales WHERE sale_date = :run_date;
INSERT INTO fact_sales SELECT * FROM stg_sales WHERE sale_date = :run_date;
COMMIT;

What they are checking: Whether you design for safe reruns, the core DE skill

CDC (Change Data Capture) finds inserts, updates and deletes in a source database. It sends them to other systems as they happen.

Approaches:

• Log-based: read the database transaction log (PostgreSQL WAL, MySQL binlog). It puts little load on the source, captures deletes, and keeps order. Tools: Debezium, AWS DMS.
• Trigger-based: triggers write changes to an audit table. This adds load to the source.
• Timestamp or version column: query updated_at > last_run. It is simple, but it misses hard deletes. It can also miss rows if the timestamps are not reliable.
• Snapshot diff: compare full snapshots. This is heavy.

Log-based CDC is the usual answer for production. Plan for an initial snapshot plus an ongoing stream. Think about order and schema changes. Also decide how deletes appear (a tombstone or an op field). The target applies changes with MERGE on the primary key.

What they are checking: Whether you know log-based CDC and its trade-offs

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

Check at the boundaries: after ingestion and before publishing.

• Completeness: row counts match the source, required columns are not null.
• Uniqueness: primary or business keys have no duplicates.
• Validity: types, formats, allowed values, ranges (age between 0 and 120).
• Referential integrity: every fact foreign key exists in its dimension.
• Freshness: the latest timestamp is inside the expected window.
• Reconciliation: totals match the source system, and sums agree across layers.
• Anomaly checks: sudden drops or spikes in volume compared with recent days.

Tools: dbt tests, Great Expectations, Soda, Deequ.

Decide how serious each failure is. Block the pipeline for critical failures. Only warn for small ones. Put bad rows in quarantine instead of dropping them silently. Send alerts to the owner and keep the results to see trends.

What they are checking: Whether you build quality gates, not just move data

CSV is a plain text format that stores data row by row. It has no schema or types. Parquet is a binary format that stores data column by column. It has a built-in schema, compression and statistics.

Analytics queries touch a few columns of many rows. Columnar reads save I/O, and compression works well on similar values. So Parquet is preferred for analytics. CSV is still common for sharing with vendors and for small files.

What they are checking: Whether you know why columnar formats win for analytics

OLTP runs the day-to-day transactions of an app. OLAP supports analysis over large amounts of old data.

Keep them separate. Heavy analysis queries on the OLTP database slow the app down. Data moves from OLTP to the warehouse through ETL or ELT.

What they are checking: Basic data platform words.

Both are dimensional models. Each has a central fact table with dimension tables around it.

• Star schema: each dimension is one flat table, joined directly to the fact. It has fewer joins, simpler queries and is faster for BI. Some data is repeated.
• Snowflake schema: dimensions are split into sub-tables (product to category to department). It repeats less data and makes hierarchies easier to maintain. But it needs more joins and the queries are harder.

-- Star: one join to get category
SELECT d.category, SUM(f.amount)
FROM fact_sales f JOIN dim_product d ON d.product_key = f.product_key
GROUP BY d.category;

Columnar warehouses make storage cheap, so a star schema is the better choice in most cases. Use snowflaking only when many dimensions share a hierarchy, or when the hierarchy changes often.

What they are checking: Can you compare the two and explain why star is the default?

A fact table stores measurable events at a chosen grain (the level of detail of one row). A dimension table stores the descriptive details used to filter and group those events.

• Fact: numbers (quantity, amount, duration) plus foreign keys to dimensions. It is very large and narrow, for example one row per order line.
• Dimension: attributes such as customer name, city, product category and date. It is smaller, wide and mostly text.

Example: fact_sales(date_key, product_key, customer_key, quantity, amount) with dim_date, dim_product and dim_customer.

A business question maps neatly: "sales amount (fact) by product category and month (dimension attributes)". Always state the grain of the fact table first. The grain decides which dimensions and measures make sense.

What they are checking: Core words of dimensional modeling.

SCD means slowly changing dimension. It is a way to handle dimension attributes that change over time.

• Type 1: overwrite the old value. There is no history. Use it for corrections, or when history does not matter.
• Type 2: add a new row for each change, with valid_from, valid_to and is_current. It keeps full history. Facts link to the version that was valid at that time through the surrogate key. This is the most common type when you need history.
• Type 3: keep limited history in extra columns (current_city, previous_city). It is simple, but it stores only one earlier value.

Also good to know: Type 0 (never changes), Type 4 (history in a separate table) and Type 6 (a mix of 1, 2 and 3).

Example of Type 2:

What they are checking: Can you describe the SCD types and compare them?

A surrogate key is an ID made by the system. It has no business meaning and is usually an integer or a hash. It is used as the primary key of a dimension.

Reasons to use it:

• It supports SCD Type 2. One natural key (customer C1) has several rows, and each row needs its own key.
• It protects the warehouse from source changes. Natural keys can be reused, change format, or differ between systems.
• It works with many sources. You can combine systems that identify customers in different ways.
• Joins are smaller and faster than with long composite or string keys.
• It allows special members, such as -1 for Unknown.

Fact tables store the surrogate key. During load, look up the key by natural key and business date. Keep the natural key as an attribute so you can trace the data.

What they are checking: Do you know why SCD2 needs surrogate keys?

• Data warehouse: structured, modelled data in a store that checks the schema when data is written (schema-on-write). It is built for SQL analysis and BI. Quality and governance are high. But it is usually limited to structured data, and storage costs more.
• Data lake: raw data of any type (files, logs, images, JSON) in cheap object storage. The schema is applied when data is read (schema-on-read). It is flexible and cheap. Without governance it can turn into a swamp. Its transaction support is weak.
• Lakehouse: lake storage plus open table formats (Delta, Iceberg, Hudi). These add ACID transactions, schema checks and fast SQL. One platform serves BI, ML and streaming.

Many companies use a lake to land raw data. They then use a warehouse or lakehouse layer for clean, ready-to-use data.

What they are checking: Can you compare the three designs and say when to use each?

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?

Apache Spark is a distributed engine that processes data in memory. It works for batch jobs, SQL, streaming and machine learning.

Why it is usually faster than MapReduce:

• It keeps temporary data in memory. MapReduce writes every stage to disk (HDFS).
• It builds a DAG (a graph of steps) for the whole job and optimizes it. MapReduce has fixed map then reduce steps.
• Tasks run as threads inside long-lived executors, so no new JVM starts for each task.
• Catalyst and Tungsten optimize SQL plans and memory layout.

Spark still spills to disk when data does not fit. It can read and write HDFS, S3 and other stores. It is only a compute engine, not a storage system.

What they are checking: Whether you know the real technical reasons, not just the slogan 'in memory'

A Spark application has one driver, many executors, and a cluster manager that gives out resources.

• Driver: runs main() and creates the SparkSession. It turns your code into a logical plan and a DAG, splits it into stages and tasks, and schedules them.
• Executors: JVM processes on worker nodes. They run tasks, hold cached data, and write shuffle files.
• Cluster manager: gives out resources. Examples are Standalone, YARN, Kubernetes (and Mesos in older setups).

Flow: the driver asks the cluster manager for executors. Then it sends tasks to them and collects the results. If the driver dies, the application dies. Losing an executor is fine, because lost tasks are recomputed from lineage.

What they are checking: Whether you can say who plans the work and who runs it

A transformation describes a new dataset made from an existing one. It is lazy, so it does not run yet. An action starts the work and returns a result or writes output.

• Transformations: select, filter, map, withColumn, join, groupBy, union. They return a new DataFrame or RDD and run nothing.
• Actions: count, collect, show, take, first, write, foreach. Each one starts a job.

df2 = df.filter(df.age > 30)   # nothing runs
print(df2.count())             # job runs here

Transformations are also split into narrow and wide (shuffle) types. Each action runs the whole chain again unless the data is cached.

What they are checking: The basic words of lazy evaluation

Spark saves transformations as a plan. It runs them only when you call an action.

Why it matters:

• The optimizer sees the whole plan. It can push filters down, skip unused columns, and combine steps (Catalyst).
• Narrow transformations run together in one pass over the data.
• Nothing is computed for results you never use.

Things to remember: an error in a transformation may only show up at the action. Two actions on the same DataFrame compute it twice unless you use cache().

What they are checking: Do you know why Spark code runs late, and what that means for debugging

All three are distributed collections. They differ in how much structure and optimization they have.

In PySpark you almost always use DataFrames. They run on the optimized engine and do not serialize each row through Python. Use RDDs only when you need low-level control or have unstructured data. A DataFrame is a Dataset of Row.

What they are checking: Whether you know why DataFrames are the default

A narrow transformation needs data from only one input partition for each output partition. A wide transformation needs data from many partitions, so it causes a shuffle.

• Narrow: map, filter, select, withColumn, union, coalesce. No data moves, and the steps run together.
• Wide: groupBy, join (unless broadcast or co-partitioned), distinct, repartition, orderBy, reduceByKey.

Each wide transformation creates a stage boundary. Shuffles write to local disk and move data over the network. They are the main cost in most jobs, so optimize them first.

What they are checking: Do you see the link between transformation type, shuffle and stage boundary

A shuffle moves data between partitions so that rows with the same key end up together. Wide transformations cause it.

Why it costs a lot:

• The map side writes shuffle files to local disk.
• The reduce side fetches blocks over the network.
• Data must be serialized and compressed, and it may spill to disk.
• Skew (uneven data) can make a few tasks do most of the work.

How to reduce it: filter early and select only the columns you need. Use broadcast joins and pre-aggregate. Avoid extra repartition calls. Tune spark.sql.shuffle.partitions (default 200).

What they are checking: Do you know the main performance bottleneck and how to attack it

They are three levels of the execution plan.

• Job: created by one action (for example count() or write).
• Stage: a group of tasks that can run without a shuffle. A new stage starts at each shuffle.
• Task: the smallest unit. There is one per partition per stage, and it runs on one executor core.

Example: read -> filter -> groupBy -> count is one job with two stages. Stage 1 reads, filters and does a partial aggregation. Stage 2 reads the shuffled data and finishes the aggregation. The Spark UI shows all three, so start debugging there.

What they are checking: Whether you can match code to what you see in the Spark UI

Both change the number of partitions. repartition does a full shuffle and can increase or decrease the number. coalesce merges existing partitions without a full shuffle and can only decrease the number.

• repartition(n) gives partitions of roughly equal size. It can also take columns, like repartition(200, "user_id").
• coalesce(n) is cheaper, but partitions may be uneven.

df.repartition(100)      # shuffle, even sizes
df.coalesce(10)          # no shuffle, fewer files

Typical use: coalesce before writing, to reduce small files. repartition is for more parallelism, or to put the same keys together before a join or partitioned write.

What they are checking: A classic tuning question with a trade-off

A broadcast join sends a small table to every executor. The large table is then joined locally, with no shuffle.

from pyspark.sql.functions import broadcast
result = big_df.join(broadcast(small_df), "country_code")

Use it when one side is small enough to fit in executor memory. Spark does it automatically below spark.sql.autoBroadcastJoinThreshold (default 10 MB). The other choice is a sort-merge join, which shuffles both sides. Do not broadcast a large table. It can cause out-of-memory errors on the driver or executors.

What they are checking: The most common join optimization

cache() is persist() with the default storage level. persist() lets you choose the level.

• DataFrame cache() uses MEMORY_AND_DISK.
• Other options include persist(StorageLevel.DISK_ONLY), MEMORY_ONLY and MEMORY_AND_DISK_SER.

AQE changes the plan while the job runs, using real statistics collected after each shuffle stage. It is on by default since Spark 3.2 (spark.sql.adaptive.enabled).

Skew means some keys have far more rows than others. A few tasks then run much longer and may run out of memory, while the rest sit idle.

How to spot it: on the stage page in the Spark UI, the max task time and shuffle read are far above the median.

Use spark.read and df.write with a format.