Data Engineer interview questions

Try each question out loud before opening the answer. Shared questions (tagged by skill) also appear on other roles that use the same skill.

Concepts

1. Beginner · SQLWhat is the difference between WHERE and HAVING?

   WHERE filters rows before grouping; HAVING filters groups after aggregation. So WHERE fare_amount > 0 removes refunds before you count, while HAVING count(*) > 100000 keeps only busy zones after counting. Aggregates such as count(*) cannot appear in WHERE. Filtering in WHERE when you can is also faster, because fewer rows reach the grouping step.

2. Beginner · SQLWhen can a LEFT JOIN return more rows than the left table has?

   When the right table has more than one match for a key. Each left row is repeated once per match. Check the key is unique on the right side (count(*) versus count(distinct key)) and compare row counts before and after the join. Duplicating a single zone in a lookup table, for example, inflated a 3.48 million row taxi join by about 170,000 rows.

3. Beginner · SQLYou wrote a LEFT JOIN but the result looks like an INNER JOIN. What is the likely cause?

   A condition on the right table in the WHERE clause, such as WHERE c.country = 'India'. Unmatched rows have NULL there, so the filter removes them. Move the condition into the ON clause to keep unmatched left rows, or keep it in WHERE deliberately if you really want only matches.

4. Beginner · Data modelling, Power BIWhy does Power BI work best with a star schema?

   Filters flow from dimension tables to fact tables along one-to-many relationships, and DAX, time intelligence and the storage engine are optimised for that shape. A star keeps facts narrow (keys and numbers) and puts descriptions in small dimensions, which compresses well and avoids ambiguous filter paths. A single flat table works for a demo but breaks with a second fact table or date comparisons.

5. Beginner · Data cleaning and qualityHow do you ensure the data in your reports is accurate?

   Check completeness, validity, consistency, uniqueness and timeliness, and decide for each problem whether to fix, exclude, flag or escalate, with counts documented. Reconcile totals with a trusted source. Then automate: data tests on every refresh with thresholds, and alerts that reach a person. Give a real example, such as taxi totals that did not equal the sum of their parts, which you escalated instead of patching.

6. Beginner · Data warehousing and dbt, ETL and data pipelinesWhat is the difference between ETL and ELT, and why has ELT become common?

   ETL transforms data before loading it into the warehouse; ELT loads raw data first and transforms it inside the warehouse with SQL (often with dbt). ELT became common because cloud warehouses are cheap and powerful enough to transform at scale, and keeping raw data lets you rebuild when logic changes. ETL still makes sense when data must be filtered or masked before it lands, for privacy or cost.

7. Beginner · Data modellingWhat is the grain of a fact table, and why declare it first?

   The grain is what one row represents, for example one order line, or one order per day. Declaring it first decides which dimensions and facts are valid, prevents mixing levels (order totals and line amounts in one table) and makes double counting visible. If nobody can state the grain in one sentence, the table will produce wrong totals.

8. Beginner · Data warehousing and dbtWhy are Parquet and other columnar formats preferred for analytics?

   Queries read only the columns they need, similar values compress well, and files store min and max statistics per block so engines skip irrelevant data. On a month of NYC taxi trips, Parquet was about six times smaller than CSV and a one-column aggregate ran about fifty times faster. CSV remains fine for small exchanges, but convert at the first step of a pipeline.

9. Intermediate · Cloud platforms (AWS, Azure, Google Cloud)How would you give a pipeline access to cloud storage securely?

   Run it with its own role or service identity, not personal keys. Grant only the actions and prefixes it needs (read raw, write clean), with short-lived credentials, encryption on the bucket, public access blocked, and access logged. Keep write paths separate from trigger paths to avoid loops.

10. Intermediate · ETL and data pipelinesWhat does it mean for a pipeline to be idempotent, and how do you achieve it?

    Running it twice for the same input period gives the same result as running it once, with no duplicates. Achieve it by overwriting partitions for the period (delete and insert, or partition overwrite), or by merging on a reliable unique key, and by deriving the period from the run's logical date rather than "now". Test it by rerunning and comparing outputs.

11. Intermediate · ETL and data pipelinesWhy keep a raw, unmodified copy of source data?

    So you can rebuild everything downstream when a transformation bug is found or logic changes, audit what the source actually sent, and debug disagreements with the source owner. Raw data is written once and never edited; retention and access rules still apply, especially for personal data.

12. Intermediate · Data modellingExplain slowly changing dimension type 2 and how facts join to it.

    Type 2 keeps history by adding a new dimension row whenever tracked attributes change, with a surrogate key, valid-from and valid-to dates and a current flag. Facts join on the business key and the date range (the fact's date between valid-from and valid-to), or store the surrogate key captured at load time. Test that ranges never overlap and each key has exactly one current row.

13. Intermediate · Data warehousing and dbtHow do you choose partition and clustering keys for a large table?

    From the filters queries actually use. Partition by a low to medium cardinality column that nearly every query filters on, usually event date, at a granularity that keeps partitions reasonably large. Cluster or sort by the next most common filter (customer or region) so blocks can be skipped by min and max statistics. Avoid partitioning by high-cardinality ids, and check pruning in query plans.

14. Intermediate · Apache Airflow and orchestrationIn Airflow, what are DAGs, tasks, logical dates and XComs?

    A DAG is the pipeline definition: tasks and their dependencies. A task is one retryable unit of work. Each run has a logical date that identifies the period it covers; tasks should derive their inputs from it so reruns and backfills are deterministic. XComs pass small values (paths, counts) between tasks, never large data.

15. Intermediate · ETL and data pipelines, Apache SparkA source adds new columns every few months. How do you handle schema evolution?

    Land raw data with its schema preserved, detect changes at ingestion and alert, and make downstream staging select explicit columns so new ones do not break anything. Add new columns deliberately with defaults for history. Formats and table formats such as Parquet with Delta Lake or Iceberg support adding columns safely; renames and type changes need a migration plan.

16. Intermediate · Apache Airflow and orchestrationWhy use an orchestrator like Airflow instead of cron?

    Cron starts commands on a schedule and nothing more. An orchestrator adds dependencies between tasks, retries with backoff, a record of every run and its logs, backfills for past periods, alerting, and a UI showing what failed and where. Cron is fine for one small independent job; once jobs depend on each other or must be rerun for history, use an orchestrator.

17. Advanced · Apache SparkWhat is lazy evaluation in Spark?

    Transformations (select, filter, join, groupBy) only build a logical plan; nothing runs until an action (count, show, collect, write). The optimiser then rewrites the whole plan, pushing filters and column selection into the file scan. Each action reruns the plan from the source unless you cache an intermediate DataFrame.

18. Advanced · SQLA dashboard query takes two minutes. How do you speed it up?

    Read the plan with EXPLAIN ANALYZE. Look for full scans of large tables, non-sargable filters (functions on indexed columns), joins on unindexed keys and row estimates far from reality. Fixes: filter early, select only needed columns, add or fix indexes, rewrite date filters as ranges, pre-aggregate into a summary table, and on warehouses use partitioning and clustering to cut bytes scanned. Measure before and after.

19. Advanced · Apache SparkWhen should you use a broadcast join in Spark?

    When one side is small enough to fit in executor memory, such as a lookup table. Spark copies it to every executor and joins the large side in place, avoiding a shuffle of the large table. It happens automatically below spark.sql.autoBroadcastJoinThreshold if Spark knows the size; otherwise use F.broadcast(). On 3.5 million taxi trips joined to 265 zones, a broadcast join ran about five times faster than a sort-merge join locally.

20. Advanced · Kafka and event streamingHow do partitions and consumer groups work in Kafka?

    A topic is split into partitions; order is guaranteed only within a partition, and events with the same key always go to the same partition. Consumers in a group share partitions, each partition read by one member at a time, so parallelism is capped by the partition count. Each group commits offsets per partition to remember its position.

21. Advanced · Kafka and event streamingHow do you avoid duplicates when a stream consumer crashes and restarts?

    Assume at-least-once delivery: process, then commit offsets. Make the sink idempotent with a unique event id and an upsert, or record processed ids in the same transaction as the result. Enable producer idempotence to avoid duplicates at the source, route poison messages to a dead-letter topic, and remember that side effects (emails, dead letters) also repeat on replay.

22. Advanced · Data warehousing and dbt, Apache SparkWhat is the small files problem and how do you fix it?

    Writing many tiny files (from too many partitions or frequent micro-batches) makes reads slow, because each file has open and metadata overhead, and strains storage metadata services. Fix by coalescing or repartitioning before writes, compacting files periodically (OPTIMIZE in Delta Lake, similar commands elsewhere) and choosing sensible partition granularity.

23. Advanced · CI/CD, Data warehousing and dbtWhat would you run in CI for a data project?

    Linting and formatting for SQL and Python, unit tests for transformation code, dbt build (or equivalent) on a small sample or only the changed models and their children, schema and data tests, and a plan for infrastructure changes. Fail fast, keep it under ten minutes, and block merges on failure.

Coding

1. Intermediate · SQLReturn the second highest salary in each department. How do you handle ties?

   select department, salary
   from (
     select department, salary,
            dense_rank() over (partition by department order by salary desc) as r
     from employees
   ) ranked
   where r = 2;

   dense_rank treats equal salaries as one rank, so "second highest" means the second distinct value. row_number would pick an arbitrary row among ties, and rank would skip rank 2 if two people tie for first. Say which interpretation you chose.

2. Intermediate · SQLWrite a query for daily revenue and a running total per month.

   with daily as (
     select cast(ordered_at as date) as day, sum(amount) as revenue
     from orders group by 1
   )
   select day, revenue,
          sum(revenue) over (partition by date_trunc('month', day) order by day
                             rows between unbounded preceding and current row) as mtd
   from daily order by day;

   Mention missing days: generate a calendar and left join if the report needs every date.

Case studies

1. Beginner · pandasYou receive a new CSV for a take-home task. What do you check before any analysis?

   Shape, column types (dtypes), a sample of rows, missing values per column, duplicates, value ranges (negative quantities, impossible dates) and key uniqueness. Read ids as strings to keep prefixes and leading zeros. Write down every exclusion with row counts. Then check the extremes: the largest values often reveal errors, like a cancelled 80,995-unit order in the UCI Online Retail data.

2. Intermediate · Data cleaning and quality, Stakeholder managementFinance says your revenue number is 4% higher than theirs. What do you do?

   Treat it as a definitions problem until proven otherwise. Compare definitions (gross or net, cancellations, returns, taxes, currency, date basis such as order date versus invoice date) and time zones. Reconcile on a small slice, one day or one region, line by line. Document the differences, agree a single definition with finance, and label the other clearly if both are needed.

3. Intermediate · Data warehousing and dbtHow would you structure a dbt project and its tests?

   Sources declared in YAML; staging models (one per source table: rename, type, basic cleaning); intermediate models for reusable joins; marts per business area with a stated grain. Test every primary key with unique and not_null, foreign keys with relationships, enums with accepted_values, plus custom tests for business rules. Run dbt build in CI on pull requests and use incremental models for large facts.

4. Advanced · Data warehousing and dbt, ETL and data pipelinesHow do you handle late-arriving data in a daily incremental load?

   Load by a watermark on arrival or update time, but reprocess a lookback window behind it (for example three days) and merge by a unique key, so late or corrected rows land without duplicates. Choose the window from observed lateness, monitor rows arriving outside it, and provide a backfill path for anything older. Verify by comparing the incremental table with a full rebuild.

5. Advanced · Apache Airflow and orchestration, ETL and data pipelinesYou fixed a bug in a transformation. How do you correct the last six months?

   Confirm every task is idempotent and period-scoped, then backfill the range with limited parallelism (for example airflow backfill create with --max-active-runs). Verify counts and key metrics per period against the source, compare with the previous output to confirm only the intended change, and inform downstream owners before and after.

6. Advanced · Apache SparkOne Spark task takes 40 minutes while the others take 2. What is happening and what do you do?

   Data skew: one key (often null, "unknown" or a huge customer) holds a large share of rows after a shuffle. Confirm in the Spark UI task metrics. Fixes: broadcast the smaller side of a join, enable adaptive skew join handling, filter or handle null keys separately, or salt the hot key into sub-keys and aggregate twice.

7. Advanced · ETL and data pipelines, Stakeholder managementAn upstream team keeps renaming columns and breaking your pipeline. What do you do?

   Agree a data contract: schema, types, meaning, freshness and a change process with notice. Enforce it with schema checks at ingestion that fail fast and alert both teams, and version breaking changes. Meanwhile, isolate the impact by validating in the raw-to-staging step so bad changes never reach marts.

8. Advanced · Security compliance and data protection, ETL and data pipelinesAnalysts need customer-level data. How do you share it responsibly?

   Classify columns, drop what is not needed, reduce precision (month instead of timestamp), replace direct identifiers with keyed pseudonyms whose key stays in a secrets manager, restrict access, set retention and log access. Remember pseudonymised data is still personal data under DPDP and GDPR; truly anonymous outputs need aggregation with minimum group sizes.

9. Advanced · ETL and data pipelines, Monitoring and observabilityHow do you know your pipelines are healthy?

   Monitor freshness (latest data timestamp against expectation), volume (rows per run against a band), failures and duration per task, data test results, and for streams, consumer lag and dead-letter counts. Alert with owners and runbooks, and publish freshness to users so they know whether to trust today's numbers.

10. Advanced · ETL and data pipelines, Kafka and event streamingWhen would you choose streaming over batch processing?

    When the business needs results within seconds or minutes and acts on them: fraud checks, live operations, alerts. Batch is simpler, cheaper and easier to backfill when hourly or daily freshness is enough. Many systems stream events into storage and still compute most analytics in batch. Choose from the latency requirement, not fashion.

11. Advanced · Cloud platforms (AWS, Azure, Google Cloud), Data warehousing and dbtThe warehouse bill doubled this month. How do you investigate?

    Break cost down by user, query and table from the warehouse's usage views. Look for new scheduled queries, full scans from select * or missing partition filters, failed retries, and dashboards refreshing too often. Fix with partition pruning, clustering, materialised summaries, query limits and budgets, and add cost monitoring per team.

Behavioural

1. Beginner · Communication, Data cleaning and qualityTell me about a mistake you made with data and what you did.

   Pick a real, modest mistake. Explain how you found it, how quickly you told the people affected, how you fixed it, and what check you added so it cannot happen again. Interviewers look for ownership and prevention, not perfection.

2. Beginner · Communication, ETL and data pipelinesWalk me through a pipeline you built.

   Cover the source and volume, the layers (raw, clean, modelled), key transformations and data quality checks, orchestration and schedule, how reruns are safe, how you monitored it, and one problem you solved. Draw it. Numbers make it credible: rows per day, runtime, how you proved correctness.

3. Intermediate · Communication, ETL and data pipelinesTell me about a time a pipeline you owned produced wrong data.

   Explain how it was detected (ideally by your own checks), how you communicated the impact quickly, how you contained it (paused downstream jobs, flagged dashboards), the root cause, the fix and backfill, and the prevention you added (a test, a contract, an alert). Interviewers look for ownership and prevention.

4. Advanced · Stakeholder managementThree teams need analysis from you this week. How do you prioritise?

   Ask each team what decision depends on the work and by when. Rank by impact and deadline, share the proposed order with the requesters and your manager, and make trade-offs explicit ("I can do A and B this week, C next Tuesday"). Offer smaller versions where a rough answer is enough. Agree it in writing so nobody is surprised.