Data Engineer interview questions and answers

Number the rows within each customer by recency and keep the first: WITH ranked AS (SELECT s.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn FROM staging_customers s) SELECT * FROM ranked WHERE rn = 1;

ROW_NUMBER gives exactly one row per customer even when two rows share the same updated_at, but which one wins is then arbitrary, so add a tie-breaker such as the load timestamp to the ORDER BY to make it repeatable. Snowflake and BigQuery support QUALIFY, which filters on the window function without a CTE. Mention that GROUP BY with MAX(updated_at) finds the latest time but not the rest of the row.

The logical order is: FROM and JOINs, then WHERE, then GROUP BY, then HAVING, then SELECT (where expressions, aliases and window functions are computed), then DISTINCT, then ORDER BY, and finally LIMIT or OFFSET. The optimiser may physically run things differently, but the result must match this order.

Since WHERE runs before SELECT, an alias defined in SELECT doesn't exist yet when WHERE is evaluated, so WHERE total_amount > 1000 fails if total_amount is an alias. Repeat the expression or wrap the query in a subquery or CTE. ORDER BY comes after SELECT, so aliases work there. It also explains why window functions can't appear in WHERE.

Join the two snapshots on the primary key with a FULL OUTER JOIN. Rows where yesterday's key is NULL are new; rows where today's key is NULL were deleted; rows present in both where any column differs were changed.

Compare columns carefully, because NULL = NULL isn't true: a value going from NULL to 'Pune' would be missed with a plain comparison. Use a NULL-safe comparison such as IS DISTINCT FROM, where your database supports it, or compare a hash of all the non-key columns, calculated with NULLs replaced by a fixed marker. For large tables, hashing is also faster. This comparison is the basis for change detection when the source can't provide change data capture.

Look at the execution plan with EXPLAIN, or EXPLAIN ANALYZE where available, which shows actual times and row counts. Look for full table scans on big tables, joins producing far more rows than expected, the join method chosen, and large sorts or data spilling to disk.

Common causes: a function on a filtered column, such as WHERE YEAR(order_date) = 2025, which stops the database using an index or partition pruning, so rewrite it as a date range; filtering after a join instead of before; SELECT * pulling unneeded columns, which is especially costly in columnar warehouses; out-of-date table statistics; and an accidental many-to-many join. Fix one thing at a time and compare the plan and timing.

CSV is plain text in rows: simple and readable everywhere, but it has no data types, so dates and numbers must be parsed, and commas or new lines inside values cause problems. JSON is text that supports nested structures, which suits API responses and events, but it's verbose and slow to scan at scale.

Parquet is a binary, columnar format that stores the schema and types, compresses well, and keeps statistics per block. Queries read only the columns they need and can skip blocks that can't match a filter. Use it for analytical data in a data lake or for large files read by Spark, pandas or a warehouse. It's not meant for being edited row by row or read by people.

Loop through the pages using the API's method, a page number, an offset or a next-page cursor, until no more results come back. Use a requests Session with a timeout on every call. Retry temporary failures, such as timeouts, 429 rate-limit responses and 5xx errors, with exponential backoff, and respect any Retry-After header. Don't retry 4xx errors like a bad token; fail and alert.

Keep credentials in environment variables or a secret manager. For a daily job, fetch only what changed if the API supports a filter like updated_since, and store the raw responses before transforming, so you can reprocess without calling the API again. Log page counts and record totals, and check them against any total the API reports.

Don't load it all at once. With pandas, read it in pieces with chunksize, process each chunk and append the results, and use usecols and explicit dtypes to cut memory. For many CSV tasks, the plain csv module reading one row at a time from a generator uses almost no memory at all.

Often the better answer is a tool built for out-of-memory data. DuckDB can run SQL directly on the CSV and stream through it, and Polars has a lazy, streaming mode; Spark makes sense if the data will keep growing or needs a cluster. Convert the file to Parquet once, so later jobs read only the columns they need. Mention checking where the data lands, so the output doesn't fill the disk.

At several levels. Unit tests with pytest for transformation logic: write transformations as functions that take and return data, then test them with small hand-made inputs covering edge cases like NULLs, duplicates, time zones and empty files. Integration tests run the pipeline end to end on a small sample dataset in a test environment and compare the output with expected results.

Also test the data itself on every run: schema checks, row counts, uniqueness of keys and valid ranges, using dbt tests or a library such as Great Expectations. Test that rerunning a load gives the same result, so it's idempotent. Run the tests in CI on every change, and keep the test data small enough to run in minutes.

In ETL (extract, transform, load), data is transformed on a separate processing server or tool before being loaded into the warehouse, so only cleaned data arrives. In ELT (extract, load, transform), raw data is loaded into the warehouse first and transformed there with SQL.

ELT has grown because cloud warehouses like Snowflake, BigQuery and Redshift have cheap storage and scalable compute, so transforming inside them is fast and simple. Keeping the raw data also means you can rebuild tables when logic changes, and tools like dbt manage the SQL transformations. ETL still makes sense when data must be masked or reduced before it reaches the warehouse, for example personal data, or when the source volume is huge.

A DAG, a directed acyclic graph, is a pipeline defined in Python: a set of tasks with dependencies between them and no loops, plus a schedule. Each task is an instance of an operator, a template for one kind of work, such as BashOperator, PythonOperator or a provider operator for running a query in a warehouse. Dependencies set the order, written like extract >> transform >> load, so a task runs only after the tasks it depends on succeed.

The scheduler creates DAG runs on schedule and queues tasks, and executors and workers run them. Each run covers a data interval, identified by its logical date. Keep heavy processing out of Airflow itself; it should orchestrate work in other systems.

A full load reloads the whole source table every run. It's simple and always correct, but slow and expensive for large tables. An incremental load moves only the rows that are new or changed since the last run.

The common method is a watermark: store the highest updated_at value processed, then fetch rows where updated_at is greater than it, and upsert them into the target with MERGE on the primary key. Use a small overlap window to catch late commits, which the upsert makes safe. Two weaknesses to mention: it needs a reliable updated_at column, and it can't see hard deletes. For those, use soft-delete flags, periodic full comparisons or change data capture from the database log.

Make tasks idempotent first: each run writes its own date's partition with overwrite or MERGE, so a retry or rerun never duplicates data. Then set retries with a retry_delay, ideally with exponential backoff, for temporary problems such as network errors. Use timeouts so a hung task fails instead of blocking everything, and run sensors in reschedule mode so they don't hold a worker slot while waiting.

Send alerts on final failure through a failure callback to email, Slack or an on-call tool, with a link to the logs. Make downstream tasks wait for upstream success, so bad data doesn't spread. After a fix, clear the failed task to rerun from that point, and backfill any missed intervals.

dbt handles the transform step inside the warehouse. Each model is a SELECT statement in a SQL file; dbt wraps it to create a view or table, and references between models with ref() let it build the dependency graph and run models in the right order. Materialisations decide how each model is built: view, table, incremental (process only new rows), or ephemeral.

It brings software practices to SQL: version control, code review, environments for development and production, and reusable macros. Built-in tests such as unique, not_null, accepted_values and relationships check the data on every run, and snapshots record slowly changing dimensions. It also generates documentation and lineage. dbt doesn't extract or load data; it's usually scheduled by an orchestrator like Airflow.

A data warehouse stores structured, cleaned data in tables with a defined schema, optimised for SQL analytics and reporting: Snowflake, BigQuery and Redshift are examples. A data lake stores raw data of any kind, such as CSV, JSON, Parquet, logs or images, cheaply in object storage like Amazon S3 or Azure Data Lake Storage, with the schema applied when the data is read. It's flexible but can become a messy "data swamp" without governance.

A lakehouse adds warehouse features on top of lake storage through open table formats such as Delta Lake, Apache Iceberg or Apache Hudi: ACID transactions, schema enforcement, time travel and fast SQL. Databricks is the best-known example, and the warehouses now read these formats too.

Analytical queries usually read a few columns across many rows, such as summing amount by region over millions of orders. Columnar storage keeps each column's values together, so a query reads only the columns it uses, and similar values next to each other compress very well. That's why SELECT * is costly in a warehouse and selecting only needed columns is cheaper.

Separating storage and compute means data sits in cheap, durable storage, while compute clusters start, stop and scale independently. Different teams can run separate compute on the same data without slowing each other down, and you pay for compute only when queries run. Traditional databases tie the two together, so scaling one means scaling both.

In BigQuery you partition a table by a date or timestamp column, ingestion time or an integer range, and queries filtering on that column scan only the matching partitions. Clustering then sorts the data within partitions by up to four columns, so filters on them skip blocks. In Snowflake, data is split automatically into micro-partitions with statistics for pruning; on very large tables you can define a clustering key to keep related rows together, at the cost of background reclustering.

Choose the columns your queries filter on most often: usually the event or order date for partitioning, then frequently filtered columns such as customer or region for clustering. Avoid partitions so small that there are thousands of tiny ones.

First find where the money goes from the warehouse's own usage views: the costliest queries, users, scheduled jobs and, in Snowflake, warehouses. Usually a few jobs or dashboards cause most of the cost.

Then fix by billing model. BigQuery on-demand charges for bytes scanned, so avoid SELECT *, require partition filters on big tables, and remember LIMIT doesn't reduce the bytes scanned. Snowflake charges for warehouse running time, so set short auto-suspend, right-size warehouses, separate workloads, and stop dashboards refreshing every few minutes for no reason. Everywhere: make repeated heavy queries incremental or pre-aggregated, and drop unused tables. Finally, add budgets, alerts or resource monitors so it's caught early next time.

Both have a central fact table surrounded by dimensions. In a star schema each dimension is a single, denormalised table: the Product dimension holds the product's name, brand, category and department together. In a snowflake schema dimensions are normalised into several related tables, such as Product linked to Category linked to Department.

Snowflaking saves a little storage and avoids repeating attribute values, but needs more joins, makes queries harder to write and is often slower for analysis. In a modern warehouse storage is cheap and compression handles repetition well, so the star schema is the usual choice for the layer that analysts and BI tools use. Snowflaking can still make sense for a very large dimension with a shared hierarchy.

It's a dimension whose attributes change occasionally, like a customer moving from Chennai to Bengaluru. Type 1 simply overwrites the old value, keeping no history; all past sales then appear under Bengaluru. Use it for corrections or attributes where history doesn't matter, like fixing a misspelt name.

Type 2 keeps history by adding a new row for each change, with valid-from and valid-to dates and a current flag. Each row has its own surrogate key, and facts store the surrogate key that was current when they happened, so old sales stay with Chennai and new ones go to Bengaluru. Use it when reports must reflect history, like sales by the customer's city at the time. dbt snapshots implement Type 2.

Data vault is a modelling method for the integration layer of a warehouse, built from three kinds of table. Hubs hold the unique business keys, such as customer numbers. Links record relationships between hubs, such as which customer placed which order. Satellites hold the descriptive attributes and their history, each row stamped with a load date and its source.

Its tables are insert-only, so it keeps full history and an audit trail, and new sources can be added without redesigning existing tables. That suits large enterprises with many changing source systems and strict audit needs, such as banks. It's harder to query, so you still build star schemas on top for reporting. For a small team with a few sources, it's usually more than needed.

Batch processes data in groups on a schedule, hourly or daily. It's simpler to build, test and rerun, and cheaper, and it suits most reporting: daily sales dashboards, finance reconciliations, monthly payroll data and model training. Streaming processes each event within seconds of arrival. It suits cases where a late answer is useless: fraud checks on payments, live delivery tracking, alerts from sensors, or stock updates during a flash sale.

Streaming adds complexity: out-of-order events, state, exactly-once concerns and always-on infrastructure. So ask what freshness the business really needs; many "real-time" requests are satisfied by micro-batches every few minutes. Many platforms use streaming for ingestion and batch for heavy transformations.

An idempotent pipeline gives the same result whether it runs once or several times for the same input. That matters because jobs fail and get retried, and people rerun days after fixing bugs; a pipeline that simply appends will then duplicate data.

Design each run to process a defined slice, usually the run's date or data interval, and replace that slice completely: overwrite the partition, or delete that date's rows and insert them in one transaction, or MERGE on a unique key so reruns update rather than add. Base the slice on the run's logical date, not the current time, so a rerun of last Tuesday processes last Tuesday. Avoid side effects like sending emails inside the load step.

First fix and test the logic, and agree with stakeholders on what the corrected numbers should look like for a sample period. Make sure the pipeline is parameterised by date and idempotent, so each day or month can be rebuilt independently.

Then run the backfill in chunks, such as a month at a time, with limited parallelism so it doesn't starve the daily jobs or blow the warehouse budget; estimate the cost from one chunk first. For safety, write into a new table, compare totals with the old one and the finance figures, then swap them. Rebuild downstream tables and dashboards in dependency order, and tell users in advance that historical numbers will change, and why.

Cover a few kinds. Completeness: today's row count compared with the usual range for that weekday, and no NULLs in required columns such as order_id and amount. Uniqueness: order_id is unique in the fact table. Validity: amounts are positive and within sensible limits, dates aren't in the future, and status values come from the allowed list. Consistency: every product_id exists in the product dimension, and totals match the source system. Freshness: the latest order timestamp is recent.

Decide which checks stop the pipeline and which only warn. dbt tests or Great Expectations can run these automatically, and failures should alert the team before dashboards refresh.

A data contract is an agreement between the team that produces data, often an application team owning a database or event stream, and the teams that consume it. It defines the schema with column names and types, what each field means, quality rules such as which fields can be NULL, how fresh the data will be, and how changes will be announced.

Without one, an app developer renames a column or changes a status value, and pipelines and dashboards break the next morning, with the data team finding out last. With a contract, the schema is checked automatically, for example in the producer's CI or by a schema registry for events, so breaking changes are caught before release and need a new version and notice.

Follow the data backwards using lineage: check the dashboard's own refresh, then the table it reads, then each upstream table and job, until you find where yesterday's data stops. The cause is usually a failed or skipped job, a source that didn't deliver a file or changed its format, a job that "succeeded" with zero rows, or a time zone mistake that put yesterday's data under another date.

Fix it, rerun the affected loads, and tell users when the numbers are complete. To catch it earlier, add freshness checks on key tables, such as an alert if the latest order is more than a few hours old, and row-count checks that fail on unusually low volumes.

Transformations, such as select, filter, withColumn, join and groupBy, describe a new DataFrame from an existing one, but don't run anything immediately. Actions, such as count, show, collect and write, ask for a result, and only then does Spark execute the work.

This is lazy evaluation: Spark records the transformations as a plan, and when an action is called, its optimiser looks at the whole plan, for example pushing filters closer to the data source and reading only the needed columns, then splits it into stages and tasks. A common mistake is calling several actions on the same expensive DataFrame, which recomputes it each time; cache it if it's reused, and avoid collect on large data.

An RDD (resilient distributed dataset) is Spark's original low-level abstraction: a distributed collection of objects processed with functions like map and reduce. Spark can't see inside those functions, so it can't optimise them much. A DataFrame is a distributed table with named columns and a schema, used through the DataFrame API or Spark SQL.

Because Spark knows the structure, the Catalyst optimiser can rewrite DataFrame queries, and the Tungsten engine stores data in an efficient binary format. So DataFrames are usually much faster, especially from Python, where RDD functions run in Python processes. Use DataFrames by default, and RDDs only for rare low-level control. Python UDFs have a similar cost, so prefer built-in functions.

In a narrow transformation, such as filter or select, each output partition depends on one input partition, so it runs without moving data between machines. In a wide transformation, such as groupBy, join or distinct, rows with the same key must end up together, so Spark redistributes data across the cluster: a shuffle. Shuffles write data to disk and send it over the network, so they're usually the most expensive part of a job and mark stage boundaries.

repartition(n) does a full shuffle to produce n evenly sized partitions, and can increase or decrease the count. coalesce(n) only reduces partitions by merging existing ones without a full shuffle, which is cheaper but can leave them uneven. Use coalesce before writing to avoid many tiny files.

That's data skew: a few keys have far more rows than others, so the partitions holding them get much more work, often in a join or groupBy on a key such as a popular product or a NULL or default value. Confirm it in the Spark UI by comparing task input sizes in that stage, and by counting rows per key.

Fixes: if one side of the join is small, broadcast it so the big table isn't shuffled. Turn on adaptive query execution, enabled by default in recent Spark versions, whose skew-join handling splits oversized partitions. Filter or handle NULL keys separately. For a few huge keys, salt them: add a random suffix to spread them across partitions, then aggregate again.

A topic is a named stream of messages, such as orders. It's split into partitions, each an ordered, append-only log stored across brokers, which lets the topic scale and be read in parallel. Each message in a partition has an offset, its position, and messages stay for a configured retention period whether or not they're read. A message's key decides its partition, so all events for one order go to the same partition and stay in order; there's no ordering across partitions.

A consumer group is a set of consumers sharing the work: each partition is read by exactly one consumer in the group, and the group commits offsets to track progress. Consumers beyond the number of partitions sit idle.

At-most-once means a message may be lost but is never processed twice, for example if offsets are committed before processing. At-least-once means nothing is lost but duplicates are possible, if processing succeeds and the consumer crashes before committing. Exactly-once means each message affects the result once.

Within Kafka, exactly-once relies on the idempotent producer, which stops retries from writing duplicates, and on transactions, which write output messages and the consumer's offsets atomically, with downstream consumers reading only committed data (isolation.level=read_committed). Kafka Streams wraps this in one setting. When the output goes to an external database, you also need an idempotent write, such as an upsert by a unique event id, or offsets stored in the same transaction as the data.

First see where the lag is: across all partitions, or only one or two. Lag on a single partition usually means skewed keys, so one partition receives far more traffic and only one consumer can read it. Lag everywhere means the consumers can't keep up overall.

Then check the consumers: is processing slow because each message makes a database or API call that could be batched? Are consumers being rebalanced repeatedly, often because processing a batch exceeds the maximum poll interval and the consumer is removed from the group? Are some instances down? You can add consumers up to the number of partitions; beyond that, increase partitions, which changes key-to-partition mapping for new messages. Also check whether input traffic simply jumped.

Keep it to a minute and point everything at data engineering. For example: "I'm a [degree] graduate from [college]. I'm strongest in SQL and Python, and I've learnt [tools, e.g. Airflow, Spark and BigQuery]. In my project I built a pipeline that [what it did, e.g. pulled daily data from a public API, stored it as Parquet and loaded it into a warehouse with dbt models and tests]. I [internship or certification, with one detail]. I enjoy making data reliable for others to use, and I'm looking to start as a data engineer."

Be ready to draw your project's pipeline on a whiteboard; interviewers often ask.

Show that you know what the job is and that it suits you. For example: "While working on analysis projects, I found I enjoyed building the pipeline that got the data clean and on time more than making the charts. I like engineering problems: making things reliable, fast and automatic. Every analyst and data scientist depends on good data, so data engineering lets me make a whole team more effective."

Avoid suggesting it's a fallback after not getting a data science role, even if that's partly true; focus on what attracts you to the work itself.

Answer honestly and show you understand why it matters: business teams expect fresh numbers when they start work. For example: "Yes. I understand the morning loads need to be ready before the business day, and I'm comfortable being part of an on-call rotation. I'd also want to learn from each incident, so we fix causes and add checks, which should mean fewer calls over time."

If you have real constraints, mention them politely and ask how the rotation works, how often each person is on call, and whether there's time off after a night call. That shows maturity, not reluctance.

Use STAR and show calm, structured handling. For example: "One Monday the finance dashboard showed half the usual weekend sales (situation). I was on call (task). I told finance the numbers were incomplete before they used them, then traced it to a source system that had changed its file format, so our job loaded only the rows it could parse and still reported success. I fixed the parser, reloaded the weekend and checked totals against the source (action). Numbers were correct by noon, and I added a row-count check that fails on low volumes (result)."

Interviewers value early communication with users as much as the technical fix.

Take it seriously and investigate together rather than defending the pipeline. Ask for a specific example: which report, which number and what they expected. Then trace that one number from the source through each step. Often both sides are partly right: the pipeline is doing what it was designed to do, but the definition differs, such as order date versus delivery date, or cancelled orders included.

For example: "I'd share what I find openly, fix any real bug immediately, and if it's a definition issue, agree the definition with the data owner and document it in the data catalogue so it doesn't come up again." Showing you value trust in the data over being right is the key.