- 2-5 years experience. - Roles: Data Engineer, ETL Developer, Analytics Engineer. - 90+ checklist questions, 28 real interview Q&A, 10 live scenarios, 12 behavioral questions. - Question patterns common at Flipkart, Swiggy, Razorpay, Zerodha, PhonePe, Meesho, and similar Indian product and fintech companies.
You are 2-5 years into data engineering. You have built pipelines, written a lot of SQL, and probably broken production at least once. This round does not test whether you know what a JOIN is. It tests whether you can be handed a vague, half-broken system and reason your way to a fix without someone holding your hand. What changes from a junior interview to a mid-level one: junior interviews ask "what is a window function." Mid-level interviews hand you a table, a query, and a wrong number, and watch how you find out why. Interviewers are listening for whether you ask about data volume and grain before writing SQL, whether you can explain a trade-off instead of just naming a tool, and whether you have actually owned a pipeline in production, including the incident it caused. This module has three tiers. Every question across Tier 2 and Tier 3 is numbered continuously. **Tier 1 - Fundamentals Checklist (no answers)** 90+ questions across 10 topics: SQL, Python for pipelines, Data Modeling, Airflow, Spark, dbt, Cloud/Warehousing, Data Quality, Data Architecture and Streaming, and Observability. No answers given. If you cannot answer these from memory, this interview will be rough - go back to the relevant module first. **Tier 2 - Real Interview Questions (Q1 to Q24)** 24 questions with full answers - question patterns common at Flipkart, Swiggy, Razorpay, Zerodha, PhonePe, and similar product companies. Covers SQL under pressure, pipeline design, Airflow failure handling, Spark performance, dbt modeling decisions, warehouse cost and performance, data quality incidents, data architecture, and observability. **Tier 3 - Scenario Round (Q25 to Q34)** 10 production scenarios interviewers drop on you to watch how you think - a silently wrong dashboard, a pipeline that duplicates data, a warehouse bill that tripled, a schema change that broke downstream models, and more. No single correct answer. The interviewer is watching your diagnostic process, not your final SQL. **Behavioral Round (Q35 to Q46)** 12 behavioral questions with full answers covering owning a bad data incident, pushing back on unclear requirements, disagreeing with a data modeling decision, and what interviewers are actually evaluating when they ask them. ### What to Expect by Company Type This module teaches question patterns common across Indian product and fintech companies, not a leaked question bank for any specific employer - interview loops change, and no module can promise exact reproduction of what you'll be asked. What does hold reasonably steady is which category of problem gets emphasized where: | Environment | Likely emphasis | |:---|:---| | Marketplace / e-commerce | Scale, data freshness, orders and payments pipelines | | Fintech | Correctness, reconciliation, auditability | | High-scale consumer product | Spark performance, streaming, reliability under load | | Early-stage startup | End-to-end ownership and practical trade-off reasoning | Use this to calibrate emphasis, not as a guarantee of exact topics. ---
No answers given. These are the floor, not the ceiling. ### SQL * What is the difference between `RANK()`, `DENSE_RANK()`, and `ROW_NUMBER()`? * How do window functions differ from GROUP BY in terms of what they return? * What is the difference between a CTE and a subquery, and when does it actually matter? * What does `PARTITION BY` do inside a window function? * What is the difference between `UNION` and `UNION ALL`, and why does it matter for performance? * How do you find duplicate rows using SQL alone? * What is the difference between `COALESCE` and `ISNULL`? * What does `LAG()` and `LEAD()` let you compute that a self-join also could, and why prefer one over the other? * What is a correlated subquery and why can it be slow? * How do you read a query execution plan, and what should you look for first? * When does adding an index not help query performance? * Why can a JOIN silently drop or duplicate rows when a joined column contains NULLs? * What is the difference between `EXISTS` and `IN`, and when does the difference actually affect performance? * What is an anti-join and how do you write one without a `NOT IN` that breaks on NULLs? * What is a transaction isolation level, and why does it matter for a pipeline reading from a live OLTP source? ### Python for Data Pipelines * What is the difference between a list comprehension and a generator expression, and when does the difference matter for pipeline memory usage? * Why would you use `chunksize` when reading a CSV with pandas? * What does idempotent mean in the context of a pipeline script? * What is the difference between `df.merge()` and `pd.concat()`? * How do you handle a malformed row in a CSV without crashing the whole pipeline? * What is the difference between catching a broad `except Exception` and catching specific exception types, and why does it matter in a pipeline? * What is the difference between multiprocessing and multithreading in Python, and which one actually helps for a CPU-bound transformation step? * How would you find out which part of a pipeline script is consuming the most memory? * What does a retry with exponential backoff look like, and why not just retry immediately in a loop? * How do you handle a paginated API where the total record count is unknown upfront? ### Data Modeling * What is the grain of a fact table and why must you define it before building one? * What is the difference between a fact table and a dimension table? * Explain SCD Type 2 in one sentence a business stakeholder would understand. * What is a surrogate key and why not just use the source system's natural key? * What is the difference between a star schema and a snowflake schema? * What is the practical difference between SCD Type 1 and SCD Type 2, and what business question can you only answer with Type 2? * What is a degenerate dimension and why does it live on the fact table instead of its own dimension table? * What is a factless fact table, and what kind of business question is it built to answer? * What is a late-arriving dimension, and what problem does it cause if you don't handle it? * What is a conformed dimension, and why does it matter when two fact tables need to share it? ### Airflow and Orchestration * What is a DAG and why must it be acyclic? * What does `catchup=False` do and why is it usually set on new DAGs? * What is the difference between a Sensor and an Operator? * What is XCom and what should never be passed through it? * What does idempotency mean for an Airflow task specifically? * What is the difference between a DAG's logical date (data interval) and the actual wall-clock time it runs? * Why are retries and idempotency two separate concerns, and why does having one without the other still cause problems? * What is a pool in Airflow and what problem does it solve? * What is dynamic task mapping and what kind of problem does it replace a hand-written loop for? * What is a backfill, and why can running one carelessly cause the same problems as `catchup=True`? ### Spark * What is the difference between a transformation and an action in Spark? * What is a shuffle and why is it expensive? * What does a broadcast join do and when should you use one? * Why can calling `.collect()` on a large DataFrame crash your driver? * What is partition skew and why does it slow down a job? * What is the difference between `repartition()` and `coalesce()`, and why is one more expensive than the other? * What is the difference between a narrow transformation and a wide transformation? * What is the difference between `.cache()` and `.persist()`, and when does caching a DataFrame actually help versus just adding overhead? * What does the Catalyst optimizer do, in plain terms? * What is Adaptive Query Execution (AQE) and what class of problem does it fix at runtime that the optimizer couldn't know about ahead of time? ### dbt and Transformation * What is the difference between a dbt `view`, `table`, and `incremental` materialization? * What does `is_incremental()` do inside a dbt model? * Why does an incremental model need a `unique_key`? * What does `ref()` actually do beyond referencing a table name, and how does it build the DAG dependency graph? * What is a dbt snapshot and what problem does it solve that a regular model can't? * What is a dbt source, and what does source freshness checking actually verify? * What is a dbt exposure and why does it matter for understanding blast radius before changing a model? * What is a dbt seed, and when is it the right tool versus loading data through a normal pipeline? * What is the difference between a dbt schema test and a custom data test? ### Cloud and Warehousing * What is the difference between a data lake and a data warehouse? * Why is columnar storage faster for analytical queries than row storage? * What is query pruning and what does it depend on? * What is the difference between ETL and ELT? * What is the practical difference between partitioning and clustering a warehouse table? * What does separation of storage and compute actually let you do that a traditional on-prem warehouse can't? * What happens to query performance when many users query the same warehouse concurrently, and what's the usual fix? * What is result caching in a cloud warehouse, and what kind of query pattern benefits most from it? ### Data Quality * What are the four common dimensions of data quality? * What is the difference between a schema test and a business-rule test? * Why might a pipeline succeed but still produce wrong data? * What does freshness mean as a data quality dimension, and how would you actually monitor it? * What is the difference between completeness and accuracy as data quality dimensions? * What is a reconciliation check, and why is it usually the strongest test you can write for catching business-logic bugs? * What is anomaly detection in a data quality context, and how is it different from a fixed-threshold check? ### Data Architecture and Streaming * What is the medallion architecture (Bronze, Silver, Gold) and what does each layer guarantee that the one before it doesn't? * What is the difference between raw, staging, and mart layers in a warehouse, and why shouldn't business logic live in staging? * What is Change Data Capture (CDC) and why does it scale better than timestamp-based polling for high-write sources? * What is the difference between batch, micro-batch, and streaming processing? * Why does event ordering matter in a streaming pipeline, and what happens when events arrive out of order? * What is the difference between at-least-once and exactly-once delivery semantics, and what does achieving exactly-once actually cost you? * What is schema evolution, and what's the difference between a backward-compatible and a breaking schema change? ### Observability * What is the difference between monitoring a pipeline's technical health (did it run) and monitoring the data's quality (is it correct)? * What is an SLA and an SLO in a data pipeline context, and how are they different? * What is data lineage, and why does it matter when you're deciding whether a schema change is safe? * What causes alert fatigue, and what's the practical fix beyond "send fewer alerts"? * What is a runbook, and what should it contain that a Slack message during an incident can't replace? * How would you classify incident severity for a data pipeline, and why shouldn't every failure page someone at 2 AM? ---
### SQL Under Pressure ### 1. Write a query to find the second highest salary in each department without using `LIMIT` or `TOP`. This tests whether you reach for window functions instead of a fragile self-join or nested subquery. ```sql -- DENSE_RANK handles ties correctly - two people -- tied for highest still leaves a real "second highest" WITH ranked AS ( SELECT employee_id, department_id, salary, DENSE_RANK() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS salary_rank FROM employees ) SELECT department_id, employee_id, salary FROM ranked WHERE salary_rank = 2; ``` **Why `DENSE_RANK` and not `RANK` or `ROW_NUMBER`:** `ROW_NUMBER` gives every row a unique number even if salaries tie, so "second highest" could silently mean two employees with identical top salaries. `RANK` leaves a gap after a tie (1, 1, 3), which means "rank = 2" might return zero rows if two people tied for first. `DENSE_RANK` has no gaps (1, 1, 2), so "rank = 2" reliably means the second distinct salary value. **What a weak answer looks like:** reaching for `LIMIT 1 OFFSET 1` inside a per-department subquery. It works for one department but falls apart the moment the interviewer asks for "second highest per department" - correlated subqueries per group get messy fast, and most interviewers will ask exactly that follow-up. --- ### 2. You are given a `payments` table with a `payment_id` that should be unique but is not. Write a query to find and quantify the duplicates. ```sql -- Step 1 - find which payment_ids are duplicated and by how much SELECT payment_id, COUNT(*) AS occurrence_count FROM payments GROUP BY payment_id HAVING COUNT(*) > 1 ORDER BY occurrence_count DESC; -- Step 2 - see the actual duplicate rows, not just the count SELECT * FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY payment_id ORDER BY created_at ) AS row_num FROM payments ) t WHERE row_num > 1; ``` > 💡 **Green Flag:** The candidate asks "should I keep the first occurrence, the last occurrence, or is there a business rule for which one is correct?" before writing a deletion query. Deleting duplicates without knowing which copy is the source of truth is how real incidents happen. > 🔴 **Red Flag:** Immediately writing a `DELETE` statement without first running a `SELECT` to see what would be removed. In a real interview this is treated as a serious signal - production data deletion without a dry run first. **Follow-up interviewers ask:** "How would you prevent this at the pipeline level instead of cleaning it up after the fact?" The strong answer references idempotent loads and a `unique_key` in the merge/upsert logic, not just a periodic cleanup job. --- ### 3. Explain what happens, step by step, when this query runs, and why the WHERE clause placement matters. ```sql SELECT city, AVG(order_value) FROM orders WHERE order_date >= '2026-01-01' GROUP BY city HAVING AVG(order_value) > 500; ``` The logical execution order is not top to bottom as written. SQL evaluates in this order: `FROM` (identify the table), `WHERE` (filter individual rows before any grouping happens), `GROUP BY` (form groups), `HAVING` (filter the groups themselves, after aggregation), then `SELECT` (choose what to return). `WHERE order_date >= '2026-01-01'` filters raw rows before grouping - this happens first and is cheap, especially if `order_date` is indexed or the table is partitioned on it. `HAVING AVG(order_value) > 500` can only run after the average is computed per city, because you cannot filter on an aggregate that does not exist yet. **Why this distinction matters in practice:** a candidate who writes `WHERE AVG(order_value) > 500` will get a syntax error, because aggregates are not valid in `WHERE`. A candidate who understands *why* - not just that the syntax is different - can reason about performance: pushing a filter into `WHERE` instead of `HAVING` wherever possible means the engine discards irrelevant rows before doing the expensive aggregation work, not after. --- ### 4. You need to compute a 7-day rolling average of daily revenue per city. Write the query. ```sql SELECT city, order_date, daily_revenue, AVG(daily_revenue) OVER ( PARTITION BY city ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS rolling_7day_avg FROM daily_city_revenue; ``` > **Note:** `ROWS BETWEEN 6 PRECEDING AND CURRENT ROW` defines a window of the current row plus the 6 rows before it - 7 rows total. This only produces a correct 7-*day* average if there is exactly one row per city per day with no gaps. If a city has a day with zero orders and no row exists for it, the window silently includes a day from further back to fill the count, which quietly produces a wrong average. **What separates a strong answer:** explicitly flagging the missing-dates problem before the interviewer has to point it out, and proposing a fix - generating a complete date spine and left-joining revenue onto it so every city has exactly one row per calendar day, including zero-revenue days. --- ### 5. A query that used to return 10,000 rows now returns 4 million rows after someone added a JOIN. Nothing else changed. What almost certainly happened, and how do you confirm it? This is a classic join explosion - the join condition is matching more rows than intended, usually because the joined table is not unique on the join key the way the query author assumed. ```sql -- If orders has 10,000 rows and order_items has multiple -- rows per order, joining without aggregating order_items -- first multiplies every order row by its item count SELECT o.order_id, o.customer_id, oi.product_id FROM orders o JOIN order_items oi ON o.order_id = oi.order_id; -- 10,000 orders x avg 400 items each = 4 million rows, -- and this is completely correct behavior for this JOIN - -- the bug is that the query intended one row per order ``` **How to confirm it before assuming:** check whether the joined table is actually unique on the join key. ```sql SELECT order_id, COUNT(*) FROM order_items GROUP BY order_id HAVING COUNT(*) > 1; ``` If this returns rows, the join is fanning out as designed by the data, not as a bug in the JOIN syntax - the fix is either aggregating `order_items` down to one row per order before joining, or explicitly deciding the query should operate at the item grain instead of the order grain. > 🔴 **Red Flag:** A candidate who assumes the JOIN syntax itself is wrong and starts changing `JOIN` types (`INNER` to `LEFT`, etc.) without first checking whether the joined table's grain matches what the query expects. Changing join type does not fix a grain mismatch - it just changes whether unmatched rows are dropped or kept null. --- ### 6. How do you read a query execution plan, and what's the first thing you look for when a query is slower than expected? The plan shows the actual steps the database engine takes to answer the query - which tables it scans, in what order, using what access method, and roughly how much data each step touches. ```sql EXPLAIN ANALYZE SELECT city, SUM(amount) FROM orders WHERE order_date >= '2026-08-01' GROUP BY city; ``` **The first thing to check is whether the plan shows a sequential/full table scan where an index or partition scan was expected.** A `Seq Scan` on a large table when a filter column is indexed or the table is partitioned on that column usually means the index isn't being used - either because the filter is written in a way that prevents index use (applying a function to the column, for example), the statistics are stale, or the index simply doesn't exist on that column. **The second thing to check is estimated versus actual row counts.** Most plan formats show both. A large gap between them - the planner expected 100 rows and actually got 2 million - means the query planner's statistics are out of date, which can cause it to choose a bad execution strategy for the whole query, not just misreport a number. > **Note:** Reading a plan is a skill that develops with repetition. The habit to build is checking the plan *before* guessing at a fix - a candidate who proposes "add an index" or "rewrite the query" without having looked at the actual plan is guessing, not diagnosing. --- ### Pipeline and Ingestion Design ### 7. Design an ingestion pipeline for a source API that only lets you pull data once per hour, has no webhook, and occasionally returns records out of order. How do you build this to avoid data loss and duplicates? The two failure modes to design against explicitly are missed records (if your watermark logic has an off-by-one) and duplicate records (if you re-pull an overlapping window to be safe). **Watermark strategy with an overlap window:** ```python def get_extraction_window(last_successful_watermark): """ Pull a slightly wider window than strictly necessary to guard against late-arriving or out-of-order records, then rely on the load step to deduplicate by natural key. """ # Overlap by 15 minutes to catch records that arrived # late relative to their own timestamp overlap = timedelta(minutes=15) window_start = last_successful_watermark - overlap window_end = datetime.utcnow() return window_start, window_end ``` The overlap means you will sometimes re-pull records you already have. That is fine, because the load step handles it with an upsert keyed on the source system's natural ID, not a blind append. ```sql -- MERGE / UPSERT so re-pulled records overwrite rather than duplicate MERGE INTO orders_raw AS target USING staging_orders AS source ON target.order_id = source.order_id WHEN MATCHED THEN UPDATE SET target.* = source.* WHEN NOT MATCHED THEN INSERT (order_id, amount, order_date, ingested_at) VALUES (source.order_id, source.amount, source.order_date, CURRENT_TIMESTAMP); ``` > 💡 **Green Flag:** The candidate says "I would only advance the watermark after confirming the load succeeded" - not before. Advancing the watermark before confirming the write means a failed load silently skips that window forever on the next run. > 🔴 **Red Flag:** Designing the pipeline around "the API said it returns records in order" without a fallback. External systems change behavior without notice; a mid-level engineer should design for that reality, not assume it away. --- ### 8. Your daily batch pipeline processes 200GB and takes 6 hours. The business now wants the numbers refreshed every 30 minutes. Do you rebuild this as a streaming pipeline? The honest answer starts by pushing back on the premise, not jumping to Kafka. "Every 30 minutes" is not the same requirement as "real-time" - it is a faster batch cadence, and the fix might be entirely within batch tooling. Before proposing a streaming rewrite, I would ask: does the full 200GB need reprocessing every run, or is this actually an incremental problem that has been solved as a full reload out of convenience? If most of the 6 hours is spent reprocessing unchanged historical data, the fix is incremental processing - only process the new watermark window, not the full table - which alone could bring a 6-hour job down to minutes, satisfying a 30-minute SLA without touching the architecture. If the data genuinely needs to be incremental *and* the business truly needs sub-minute freshness (not just "faster than daily"), that is when a streaming rewrite with Kafka and a stream processor becomes justified - but that is a significant increase in operational complexity, and I would want the business requirement to actually demand it, not just prefer it. > 💡 **Engineering Decision:** Do not reach for streaming because "30 minutes sounds real-time." Streaming architectures cost significantly more to build, run, and debug than batch. The real question is whether the business decision made from this data changes meaningfully between a 30-minute-old number and a 2-hour-old number. If not, a faster, incremental batch job is the right answer and the cheaper one. --- ### Airflow and Orchestration ### 9. A DAG has 40 tasks. Task 22 fails intermittently about once a week, for no clear reason, and re-running it manually always fixes it. Your manager asks you to "just add more retries." What do you actually do? Blindly adding retries treats the symptom, not the cause, and can hide a real problem. Before touching the retry count, I want to know *why* task 22 fails intermittently - transient failures usually fall into a few buckets, and the fix is different for each. ```bash # Pull the actual error from the last several failures, # not just "it failed" - look for a pattern airflow tasks logs <dag_id> <task_id> <execution_date> ``` If the errors show connection timeouts to an external API or database, the fix is retries with exponential backoff plus a sensible timeout - this is a legitimate case for retries, because the failure is genuinely transient and external. ```python task_22 = PythonOperator( task_id='fetch_external_data', python_callable=fetch_data, retries=3, retry_delay=timedelta(minutes=2), retry_exponential_backoff=True, max_retry_delay=timedelta(minutes=15), ) ``` If the errors instead show a race condition - task 22 depends on data that task 21 sometimes has not fully committed yet - retries are masking a missing dependency, not fixing one. The real fix is an explicit sensor or a stronger `wait_for_downstream` dependency, not a retry loop that happens to work by accident most of the time. > 🔴 **Red Flag:** Setting `retries=10` without ever looking at why the task fails. This is the single most common "it works but nobody understands why" pattern in production Airflow DAGs, and it usually means a real bug is being silently absorbed until it eventually is not. --- ### 10. Explain the difference between `catchup=True` and `catchup=False`, and describe a real incident this setting can cause if set wrong. `catchup` controls whether Airflow automatically triggers all the historical DAG runs between a DAG's `start_date` and today when it is first turned on, or when it has been paused and is re-enabled. With `catchup=True` (the default), if you create a new DAG with `start_date=datetime(2024, 1, 1)` and today is August 2026, Airflow immediately schedules and attempts to run every daily interval from 2024 to today - potentially hundreds of runs firing at once. **The real incident this causes:** a new DAG that hits an external API on each run, deployed with `start_date` set far in the past and `catchup` left at its default `True`. The moment it is unpaused, Airflow fires hundreds of historical runs simultaneously, all hitting the same external API, and the API rate-limits or blocks the account entirely - taking down not just this new DAG but every other pipeline that depends on that same API key. ```python dag = DAG( 'daily_revenue_pipeline', start_date=datetime(2024, 1, 1), schedule_interval='@daily', catchup=False, # only run for "today" going forward, # do not backfill 2+ years of history ) ``` `catchup=False` should be the default on almost every new DAG unless backfilling historical data is genuinely the intent - and if it is, that backfill should usually be triggered deliberately and in a controlled way, not left to fire automatically the moment the DAG is turned on. --- ### 11. Your Airflow scheduler has been getting noticeably slower over the past month - new DAG runs are delayed by several minutes even though nothing looks broken. What do you investigate? A slowly degrading scheduler, with nothing outright failing, points toward the scheduler itself being overloaded rather than any single DAG being broken. The most common causes, in order of how often I have seen them: **Heavy computation inside DAG files themselves.** The scheduler parses every DAG file repeatedly to build the DAG structure. If a DAG file makes a database query or an API call at the top level - outside of any task - that code runs every single time the scheduler parses the file, not just when the DAG executes. This is one of the most common and least obvious Airflow performance mistakes. ```python # WRONG - this API call runs on every scheduler parse cycle, # potentially every few seconds, regardless of whether the DAG runs active_sources = requests.get("https://api.internal/active-sources").json() for source in active_sources: task = PythonOperator(task_id=f"extract_{source}", ...) # RIGHT - fetch this inside a task, which only runs when the DAG runs def get_active_sources(): return requests.get("https://api.internal/active-sources").json() ``` **Too many DAGs or too short a `schedule_interval` for the scheduler's resources** - the fix here is usually scaling scheduler resources or reducing DAG parsing frequency, not code changes. **A growing metadata database** - the Airflow metadata DB accumulates task instance history over time; without a retention/cleanup policy, queries against it slow down as it grows. > 💡 **Green Flag:** Mentioning DAG parse time specifically (`airflow dags list-import-errors` and the scheduler's own parse-duration metrics) as the first thing to check, rather than jumping straight to "add more workers." --- ### Spark and Distributed Processing ### 12. A Spark job joining a 500GB fact table with a 2MB lookup table is taking 40 minutes. What is almost certainly wrong, and how do you fix it? Before assuming anything is wrong, first inspect the physical execution plan rather than jumping straight to forcing a broadcast. A 2MB lookup table is usually an excellent broadcast candidate, and Spark's optimizer may already be choosing a broadcast hash join automatically, depending on its size statistics and the configured auto-broadcast threshold. ```python large_fact_df.join(small_lookup_df, on="lookup_key", how="left").explain() ``` If the plan shows a `SortMergeJoin` or a shuffle-based join instead of a `BroadcastHashJoin`, that tells you automatic broadcasting did not kick in - possibly because Spark's size estimate for the small table is stale (common after several transformations, since Spark's statistics can lag behind the DataFrame's actual current size) or because the configured broadcast threshold is lower than 2MB. That is the moment to force it explicitly: ```python from pyspark.sql.functions import broadcast # Force the broadcast once you've confirmed via .explain() that # the optimizer wasn't already choosing one on its own result = large_fact_df.join( broadcast(small_lookup_df), on="lookup_key", how="left" ) ``` **Why checking the plan first matters, not just fixing blindly:** if the plan already shows a broadcast join and the query is still slow, the bottleneck is somewhere else entirely - forcing `broadcast()` again would change nothing, and a candidate who jumps straight to "add broadcast()" without checking may waste time treating the wrong symptom. > **Note:** Most engines have an automatic broadcast threshold (Spark's default is commonly a low double-digit number of MB), but relying on autodetection is risky if the small table's size estimate is stale. Being explicit with `broadcast()` after confirming via the plan that it's actually needed removes the guesswork. **Follow-up interviewers ask:** "What if the small table were 500MB instead of 2MB - would you still broadcast it?" The strong answer is that it depends on available executor memory - broadcasting is not free, since every executor loads a full local copy, so a table that's small relative to the fact table but still large in absolute terms can cause its own memory pressure. This is a size judgment call, not a fixed rule. --- ### 13. Explain what a shuffle is in Spark, using an example, and why minimizing shuffles is often the single biggest performance lever available. A shuffle happens whenever Spark needs to move data between partitions across the network so that related records end up on the same executor - this happens for operations like `groupBy`, non-broadcast `join`, and `distinct`. Concretely: if you run `orders_df.groupBy("city").sum("amount")` and the data is spread across 50 partitions with no guarantee that all "Mumbai" rows are on the same partition, Spark must physically redistribute the data across the network so every "Mumbai" row lands on the same executor before it can sum them correctly. That network transfer, plus the disk writes involved in staging shuffle data, is the shuffle. ```python # Triggers a shuffle - Spark must regroup data by city across the network result = orders_df.groupBy("city").sum("amount") ``` **Why it dominates performance:** a shuffle involves disk I/O (writing intermediate shuffle files), network I/O (transferring them between executors), and serialization overhead - all of which are far slower than in-memory, in-partition computation. A job with several unnecessary shuffles chained together can spend the majority of its runtime moving data around rather than actually computing anything. **What reduces shuffles in practice:** broadcasting small tables in joins (covered above), filtering data as early as possible in the pipeline so less data needs to be shuffled later, and choosing partition keys that align with how the data will later be joined or grouped, so Spark does not have to reshuffle data that was already reasonably organized. --- ### 14. Your Spark job has 200 partitions, but the Spark UI shows one task taking 25 minutes while the other 199 finish in under a minute each. What is this and how do you fix it? This is partition skew - the data is not evenly distributed across partitions, so one executor ends up doing dramatically more work than the others while the rest sit idle waiting for it. **Diagnosing it:** the Spark UI's stage detail view shows task duration per partition. A single task taking 25x longer than its peers, on an otherwise identical operation, is the signature of skew - usually caused by a `groupBy` or `join` key with one wildly overrepresented value. **The diagnostic sequence, from least to most invasive - do not jump straight to the most complex fix:** First, confirm the skew is real and identify the specific dominant key value, rather than assuming. ```python orders_df.groupBy("merchant_id").count().orderBy(col("count").desc()).show(5) ``` Second, check whether Adaptive Query Execution (AQE) skew join handling is enabled - in recent Spark versions this can automatically split an oversized partition at runtime without any code change, and may already solve moderate skew on its own. ```python spark.conf.set("spark.sql.adaptive.enabled", "true") spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true") ``` Third, if one side of a join is small enough, broadcasting it sidesteps the skew problem entirely, since a broadcast join never shuffles the large side by key at all. Only if AQE and broadcasting are not sufficient - typically when both sides are large and skewed - is salting the right next step, and it should be treated as the more complex, last-resort fix rather than the default response to any skew: ```python from pyspark.sql.functions import concat, lit, floor, rand, col # Split the dominant key into several synthetic sub-keys so the # rows sharing it get spread across multiple partitions instead of one salted_df = orders_df.withColumn( "salted_merchant_id", concat(col("merchant_id"), lit("_"), (floor(rand() * 10)).cast("string")) ) ``` The aggregation then runs on the salted key first (spreading the load), and a second aggregation step combines the salted partial results back into the true per-merchant totals. > 🔴 **Red Flag:** A candidate who responds to any skew by immediately reaching for salting, without first checking AQE or whether a broadcast join would sidestep the problem entirely. Salting adds real complexity - a second aggregation step, more code to maintain - and a mid-level engineer should reach for the simplest fix that solves the actual problem, not the most sophisticated one they know. > 🔴 **Red Flag:** A candidate who responds to skew by simply increasing the number of partitions across the board. More partitions do not fix skew if the underlying key distribution is still lopsided. --- ### dbt and Transformation ### 15. You are choosing between `view`, `table`, and `incremental` materialization for a dbt model that computes daily order aggregates from a 2-billion-row raw orders table. Walk through your reasoning. Start from the actual constraint: 2 billion rows, and the model recomputes something derived from them. A `view` recomputes the full query every single time anything downstream selects from it - completely unworkable at this scale, since every dashboard query would trigger a full 2-billion-row scan. A `table` materialization runs the full query and stores the result physically, but re-runs the *entire* transformation from scratch on every `dbt run` - meaning every single run reprocesses all 2 billion rows even though only today's rows are new. That is correct but wasteful, and gets slower every day as the table grows. `incremental` is the right choice here specifically because the source data is append-heavy (new orders arrive, old orders rarely change) and the table is large enough that "reprocess everything every day" is genuinely expensive. ```sql {{ config( materialized='incremental', unique_key=['order_date', 'city'] ) }} SELECT order_date, city, SUM(amount) AS daily_revenue, COUNT(*) AS order_count FROM {{ ref('stg_orders') }} {% if is_incremental() %} -- Only process new data since the last run, with a short -- lookback window to catch any late-arriving orders WHERE order_date >= (SELECT MAX(order_date) - INTERVAL '3 days' FROM {{ this }}) {% endif %} GROUP BY order_date, city ``` > 💡 **Engineering Decision:** `unique_key` here must match the model's actual grain - one row per `order_date` per `city` - which is why it's a list of both columns, not just `order_date` alone. Setting `unique_key='order_date'` on a model that groups by both date and city would be a real bug: the MERGE would treat all cities on the same date as one record, silently overwriting one city's revenue with another's on every re-run instead of updating each city's row independently. The `unique_key` you configure is what makes this a MERGE/upsert rather than a blind append, and it only works correctly if it matches the grain the SELECT actually produces. --- ### 16. A colleague's dbt model passed all its tests in CI but produced wrong numbers in production. How is that possible, and what does it tell you about test coverage? Passing tests only prove what you actually tested for - `not_null`, `unique`, and `accepted_values` catch structural problems, but say nothing about whether the *business logic* inside the model is correct. A concrete way this happens: a model joins orders to a currency conversion table to compute revenue in INR. The join has `not_null` and `unique` tests on both sides, and both pass. But the join condition uses the *wrong date field* - joining on `order_date` instead of `payment_date` - which produces a row for every order with no NULLs and no duplicates, so every schema test passes, while every converted amount is calculated using the wrong day's exchange rate. Structurally perfect, business-logic wrong. **What this means for test coverage:** schema tests (`not_null`, `unique`, `relationships`) verify shape, not correctness. Catching this class of bug requires a **custom data test** that encodes an actual business expectation - for example, asserting that converted revenue for a sample of known orders falls within an expected range, or that the sum of a derived metric reconciles against an independently computed total. ```sql -- tests/assert_revenue_reconciles.sql -- Custom test: reconcile aggregated converted revenue per day -- against the independently summed raw amounts for that day, -- rather than checking any single row in isolation WITH raw_totals AS ( SELECT order_date, SUM(amount_usd) AS raw_usd_total FROM {{ ref('stg_orders') }} GROUP BY order_date ), converted_totals AS ( SELECT order_date, revenue_inr FROM {{ ref('fct_daily_revenue_inr') }} ) SELECT r.order_date, r.raw_usd_total, c.revenue_inr FROM raw_totals r JOIN converted_totals c USING (order_date) WHERE ABS(c.revenue_inr - r.raw_usd_total * 80) / NULLIF(c.revenue_inr, 0) > 0.05 -- flags any day where converted revenue is off by more than 5% -- from raw USD total times a representative FX rate - a reconciliation -- against an independent total, not a guess at a plausible range per row ``` > 🔴 **Red Flag:** A candidate who treats "all dbt tests passed" as equivalent to "the data is correct." Mid-level engineers should know that schema tests and business-logic correctness are two different guarantees, and premium pipelines need both. --- ### Cloud, Warehousing, and Cost ### 17. Your team's monthly warehouse bill tripled this month with no change in data volume. How do you investigate? The absence of a data volume change is the key clue - it rules out "we're just processing more data" and points toward either a workload change (new queries, more frequent runs) or a configuration/waste problem. The exact system views you query depend on the platform - Snowflake and Redshift expose this differently, and a premium answer names the right one rather than treating them as interchangeable. **On Snowflake:** ```sql SELECT query_text, warehouse_name, total_elapsed_time, bytes_scanned, credits_used_cloud_services FROM snowflake.account_usage.query_history WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP()) ORDER BY bytes_scanned DESC LIMIT 50; ``` **On Redshift:** ```sql SELECT query, querytxt, starttime, endtime, DATEDIFF(second, starttime, endtime) AS duration_seconds FROM stl_query WHERE starttime >= DATEADD(day, -30, GETDATE()) ORDER BY duration_seconds DESC LIMIT 50; ``` **Common causes of a cost spike with no volume change, roughly in order of likelihood, regardless of platform:** A new dashboard or scheduled report was added that queries a large unpartitioned table repeatedly - each refresh scans far more data than needed. A previously efficient query started scanning more data because a table lost its partitioning or clustering after a schema change or a full table rebuild. A warehouse's auto-suspend setting was accidentally increased or removed (Snowflake) or a cluster was resized and never scaled back down (Redshift), so compute keeps running idle between queries instead of shutting down. Someone is running ad-hoc `SELECT *` queries against a massive raw table directly instead of querying a smaller, pre-aggregated mart. > 💡 **Green Flag:** The candidate asks whether the cost increase is concentrated in a few queries or spread evenly, before proposing a fix - and names the correct platform-specific system view rather than a generic `query_history` that doesn't exist identically across warehouses. --- ### 18. Explain the difference between ETL and ELT, and give a real scenario where you would deliberately choose ETL over the now-more-common ELT pattern. ETL transforms data before loading it into the destination - the transformation logic runs in a separate processing layer, and only the already-cleaned result lands in the warehouse. ELT loads raw data into the warehouse first, then transforms it using the warehouse's own compute, typically with a tool like dbt. ELT has become the default in most modern stacks because cloud warehouse compute is elastic and comparatively cheap, and doing transformations in SQL inside the warehouse is easier to version, test, and debug than a separate transformation service. **A real case for choosing ETL instead:** a pipeline handling data with a legal requirement to mask or redact PII (personally identifiable information) before it is ever stored anywhere, for compliance reasons - healthcare or financial data under regulations that specify sensitive fields must be removed or transformed before entering the analytical destination. In that case, the transformation (masking) must happen *before* load, which is definitionally ETL, because ELT would mean raw PII lands in the warehouse first, even briefly, before being cleaned - which some regulatory or contractual requirements explicitly do not allow. > 💡 **Engineering Decision:** Default to ELT for its operational simplicity and the ability to keep raw data for reprocessing. Choose ETL specifically when policy, contractual requirements, data residency controls, or security architecture require sensitive fields to be removed or transformed before entering the analytical destination - the exact rule depends on the regulation and organization, so this is a case to confirm with legal or security, not assume. --- ### Data Quality ### 19. Design a data quality check for a `orders_fact` table that would have caught a real incident: a source system started sending amounts in paise instead of rupees, and the number went undetected in production for four days. This incident is a distribution/range problem, and it specifically was not caught because whatever checks existed - if any - only verified schema shape (not null, right data type), not the plausibility of the values themselves. **The check that would have caught this - a range/distribution test, not a schema test:** ```python # Using Great Expectations - assert daily average order value # falls within a plausible historical range, not just "is a number" validator.expect_column_mean_to_be_between( column="order_amount", min_value=150, # based on historical typical order value in INR max_value=5000, ) ``` A paise-instead-of-rupees bug multiplies every value by 100 - an average order value of Rs 450 suddenly reporting as 45000 would immediately fail a range check like this, on day one, instead of silently corrupting four days of downstream dashboards and reports before a human happened to notice. > 📌 **Remember:** `not_null` and `unique` tests, which most pipelines already have, would not have caught this at all - the paise values are real numbers, not null, and still unique. This is exactly why range and distribution checks matter as a distinct category from structural schema tests. **What a strong answer adds unprompted:** "I would also add this as a Great Expectations checkpoint that runs *inside* the Airflow DAG, before the data is loaded into the mart layer - not just as a nightly audit that runs after the bad data has already reached dashboards. Catching it in the pipeline, as a hard gate, is different from catching it in a report a data quality engineer reads a day later." --- ### 20. A `not_null` test on a critical column has been failing intermittently for weeks, and someone on the team quietly added it to an ignore list to stop the noise. What is wrong with this, and what should have happened instead? This is one of the most common ways real data quality problems become invisible. An intermittent test failure almost never means the test is wrong - it usually means the underlying data genuinely has nulls some of the time, and ignoring the test does not fix that, it just stops anyone from being told about it. **What should have happened instead of silencing it:** First, actually investigate why it is intermittent rather than constant - a `not_null` failure that fires 2 days out of 10 suggests a specific upstream condition, not universal breakage. Common causes: a particular source system or partner integration that sometimes omits the field, a race condition where the pipeline occasionally reads before an upstream write fully commits, or a recent schema change that only affects records created after a certain date. Second, if nulls in that column are occasionally legitimate - the business genuinely allows the field to sometimes be empty - the test itself was wrong from the start and should be relaxed to something more accurate, like `expect_column_values_to_not_be_null` with a tolerance threshold, or scoped to only apply to a subset of records where the field should always be present. Third, if nulls are never legitimate, the failing test is correctly catching a real, ongoing data quality problem, and silencing it just means the team is now knowingly shipping bad data downstream without anyone being alerted. > 🔴 **Red Flag:** Treating "the test is annoying" and "the test is wrong" as the same thing. They require completely different fixes, and only one of them is ever the right response to intermittent failures. --- ### Data Architecture ### 21. Explain the medallion architecture in one sentence per layer, and describe a real bug that only gets caught because Bronze exists. Bronze holds raw data exactly as it arrived from the source, unmodified. Silver holds cleaned, deduplicated, standardized data - still close to source structure but trustworthy. Gold holds business-ready, aggregated data shaped for a specific consumption need, like a dashboard or a report. **The bug Bronze catches that a two-layer (raw-to-gold) design would lose:** a transformation bug is introduced in the Silver layer's cleaning logic - say, a currency conversion step that silently uses the wrong exchange rate for three days before anyone notices the Gold-layer revenue numbers look off. Because Bronze preserved the untouched raw data from those three days, reprocessing is a matter of re-running Silver and Gold against the existing Bronze data with the corrected logic. Without Bronze - if the pipeline only ever kept the already-transformed result - those three days of original source data would be gone, and fixing the bug would mean the historical numbers for that period simply cannot be corrected, only estimated. > 💡 **Engineering Decision:** Bronze is what makes a pipeline replayable. The cost is extra storage for data that looks "already available" downstream; the payoff is that any bug discovered in transformation logic, even weeks later, can be fixed by reprocessing from Bronze rather than accepting permanently wrong historical numbers. --- ### 22. Your company's order database (OLTP) gets 50,000 writes per second. The analytics pipeline currently queries it directly every hour to check for new and updated orders. What's wrong with this, and what's the standard fix? Querying a live, high-write OLTP database directly for analytics competes for the same resources the application itself needs to serve real customers - a large analytical query can degrade checkout performance for actual paying users, which is a much worse outcome than a slightly stale dashboard. Beyond the resource contention, query-based polling for "what changed since last time" is also fragile at this write volume: it typically relies on an `updated_at` timestamp, which misses hard deletes entirely (a deleted row just disappears, there's no timestamp to poll for) and can miss rapid updates that happen and get overwritten between polling intervals. **The standard fix is Change Data Capture (CDC)** - reading the database's own transaction log (using a tool like Debezium) rather than querying the tables at all. CDC captures every insert, update, and delete as it happens, without adding query load to the OLTP database, and it captures deletes correctly since it reads the log entry for the delete operation itself, not the now-nonexistent row. > 💡 **Engineering Decision:** Query-based incremental polling is simpler to build and fine for lower-write-volume sources where a few seconds of query load is negligible. CDC is more operationally complex to set up and run but is the correct choice once write volume is high enough that polling either degrades the source system or structurally cannot capture deletes correctly - which is exactly the situation described here. --- ### Observability ### 23. Your dashboard shows a pipeline "succeeded" every day for the past week, but the data has actually been three days stale the entire time because an upstream source stopped sending new records. Why didn't anything catch this, and what should? This is the gap between monitoring a pipeline's technical health and monitoring the freshness of the data itself - two different things that are easy to conflate. "Succeeded" here means the DAG ran without throwing an error, not that it received new data. A pipeline that queries an empty or stale source, finds nothing new, and completes without error will report success every time, even though the actual business outcome - fresh data - has silently stopped happening. **The fix is a dedicated freshness check, separate from task success/failure monitoring:** ```sql -- A freshness check that fails explicitly if the newest -- record is older than the pipeline's expected cadence allows SELECT MAX(created_at) AS latest_record, CURRENT_TIMESTAMP - MAX(created_at) AS staleness FROM orders_raw HAVING CURRENT_TIMESTAMP - MAX(created_at) > INTERVAL '2 hours'; -- returning any row here means data is staler than expected -- for a pipeline that's supposed to run hourly ``` This should be wired as an explicit alert condition, not just a query someone runs manually when they suspect a problem - the entire point is that a human should not have had to notice the staleness themselves. > 📌 **Remember:** "The DAG succeeded" and "the data is fresh and correct" are two separate claims, and monitoring only the first one is one of the most common gaps in real production data platforms. A mid-level engineer should be explicitly designing checks for both. --- ### 24. Your team gets paged for every single pipeline failure, 24/7, regardless of which pipeline it is or what depends on it. What's wrong with this approach, and how would you fix it? Paging someone for every failure with no severity distinction guarantees alert fatigue - if a low-stakes internal reporting pipeline failing at 3 AM pages someone exactly as loudly as a payment reconciliation pipeline failing, the on-call engineer very quickly learns to treat every page as probably-not-urgent, which is exactly the wrong training to have when a truly critical failure happens. **The fix is defining incident severity ahead of time, tied to actual business impact, not just "did something fail":** A pipeline feeding a customer-facing feature or a financial reconciliation process failing is a genuine page-someone-at-3AM situation. A pipeline feeding an internal weekly report that nobody reads until Monday morning failing overnight can wait for business hours - a Slack notification is enough, no page needed. ```yaml # Conceptual severity tiering, not tool-specific syntax payment_reconciliation_pipeline: on_failure: page_oncall_immediately severity: P1 internal_weekly_report_pipeline: on_failure: slack_notification_only severity: P3 ``` **What a runbook adds on top of this:** when a P1 pipeline does page someone, a runbook - a short, specific document for that exact pipeline - tells them the first three things to check and who to escalate to if those don't resolve it, rather than the on-call engineer starting cold at 3 AM with zero context on a system they may not have touched before. A Slack message during the incident is not a substitute, because it only exists after someone has already diagnosed the problem once; a runbook exists before the incident, written calmly, for the next person who hits it. > 🔴 **Red Flag:** A candidate who treats "we get paged too much" as a tooling problem to solve by muting notifications. The actual fix is defining what genuinely deserves a page in the first place - muting or ignoring is treating the symptom of alert fatigue, not its cause. ---
These are live problems interviewers put in front of you and watch how you think. There is no single correct answer. ### Q25. Scenario - The Dashboard That's Quietly Wrong A VP's daily revenue dashboard has been showing numbers that "look a little low" for about two weeks, but nothing crashed, no pipeline failed, no alert fired. Someone finally compared it against the finance team's manual spreadsheet and found a 12% gap. Where do you start? The absence of any failure is the important detail - this is not a broken pipeline, it is a silently incorrect one, which is a different and often scarier class of problem because nothing was watching for it. First, establish the actual boundary of the discrepancy: is 12% consistent across all days in the window, or did it start on a specific date? A consistent 12% gap across the full two weeks suggests a logic bug that has always been there and only now got noticed. A gap that starts on a specific date points to a change - a deploy, a schema change, a source system update - around that date. ```sql -- Compare daily totals against a known-good historical baseline -- or an independent recomputation, day by day, not just in aggregate SELECT order_date, dashboard_revenue, recomputed_revenue, (dashboard_revenue - recomputed_revenue) / recomputed_revenue AS pct_diff FROM revenue_comparison ORDER BY order_date; ``` If the gap starts on a specific date, check what changed around that date - a dbt model deploy, an upstream schema change, a new filter that got added to a WHERE clause. If it is a slow, consistent gap with no clear start date, it is more likely a logic issue - a JOIN silently dropping rows (an INNER JOIN where a LEFT JOIN was intended, dropping orders whose customer record has not yet synced), or a filter meant to exclude test data that is also excluding some real records. **What you say to the VP while investigating:** be honest about the scope before you have the full answer. "We've confirmed the dashboard has been undercounting revenue by roughly 12% for at least two weeks. I'm narrowing down the exact cause now and will have a root cause and corrected historical numbers within a few hours." Do not guess at a cause before you have verified it. --- ### Q26. Scenario - The Pipeline That Duplicates Data Once a Month A pipeline runs fine 29 days out of 30, but roughly once a month a downstream table ends up with duplicate rows for a single day. Nobody can reproduce it on demand. How do you find the pattern? A failure that happens roughly monthly, not randomly, suggests it correlates with something else that happens on a similar cadence - a scheduled maintenance window, a monthly batch job that overlaps with this pipeline, a source system's own monthly reconciliation process, or simply "the day the pipeline happens to take longer than usual and overlaps with the next scheduled run." ```bash # First, get the exact dates duplication occurred historically - # not "monthly" as a vague description, but the actual dates ``` ```sql SELECT load_date, COUNT(*) AS row_count, COUNT(DISTINCT order_id) AS distinct_orders FROM orders_fact GROUP BY load_date HAVING COUNT(*) != COUNT(DISTINCT order_id) ORDER BY load_date; ``` With the exact dates in hand, check whether they cluster around anything: the first of the month, a specific day of the week, unusually long runtimes on the days before, or overlapping with another scheduled job. A frequent cause of "occasional" duplication is a pipeline that occasionally runs long enough to still be executing when its next scheduled run starts - if the DAG is not configured to prevent concurrent runs, two instances of the same load can both insert the same day's data. ```python # If concurrent runs are the cause, this is the fix - # Airflow config that prevents overlapping runs of the same DAG dag = DAG( 'daily_orders_load', max_active_runs=1, # only one run of this DAG at a time, # a slow run blocks the next one from starting ) ``` **What a strong candidate says:** "I would not accept 'it happens once a month' as the final description of the bug. I would pull the exact dates first, because 'monthly' as a human description often turns out to be 'whenever the previous run happens to take longer than usual' - which is a specific, fixable condition, not a mysterious calendar pattern." --- ### Q27. Scenario - The Schema Change That Broke Three Teams A source team changed a column name from `cust_id` to `customer_id` in their production database without telling anyone. Your ingestion pipeline did not fail - it just silently started loading NULLs into that column, and three downstream dbt models that depend on it have been producing garbage for two days before anyone noticed. The core problem is not the schema change itself - source teams change things, that is expected. The problem is that the failure was silent. A pipeline that hard-fails on an unexpected schema is easier to deal with than one that succeeds while quietly loading nulls, because the second one erodes trust in every dashboard for two days before anyone notices. **Immediate fix:** update the ingestion pipeline to reference the new column name, backfill the two days of NULL-corrupted data, and re-run the three downstream dbt models against the corrected data. **The structural fix, which matters more than the immediate one:** add a schema validation step at ingestion that fails loudly instead of degrading silently. ```python # A schema check at the start of ingestion - fail the DAG # explicitly rather than silently loading nulls for a missing column expected_columns = {"customer_id", "order_id", "amount", "order_date"} actual_columns = set(source_df.columns) missing = expected_columns - actual_columns if missing: raise ValueError( f"Schema drift detected - expected columns missing: {missing}. " f"Halting ingestion instead of loading incomplete data." ) ``` **What you raise with the source team, not just fix silently:** ask whether they can adopt a change-notification process - even something informal like posting in a shared Slack channel before a column rename - and independently, make sure your own pipeline fails fast on unexpected schema changes going forward, since you cannot fully control what upstream teams do, only how your pipeline reacts when they do it. --- ### Q28. Scenario - The Warehouse Migration Nobody Tested Under Load Your team migrated from Redshift to Snowflake last month. Everything passed testing. This week, during a high-traffic sale event, dozens of dashboard queries started timing out that never had problems before. What do you investigate? Passing tests before the migration and failing under real load points toward something that only shows up at scale or under concurrency - not a correctness bug, but a capacity or configuration one. First question: was the new warehouse's compute size actually sized to match or exceed the old system's capacity, or was it provisioned based on average load rather than peak load? A common migration mistake is sizing the new system to handle typical Tuesday traffic and never testing it against the actual peak the old system had been quietly absorbing for years. ```sql -- Check concurrent query counts and queueing during the incident window SELECT warehouse_name, COUNT(*) AS concurrent_queries, AVG(queued_provisioning_time) AS avg_queue_time FROM query_history WHERE start_time BETWEEN :incident_start AND :incident_end GROUP BY warehouse_name; ``` If queries are queuing rather than actually failing, the fix is likely a multi-cluster warehouse configuration that can scale out compute automatically under concurrent load - a capability many platforms offer but that has to be explicitly configured, and easy to miss during a migration that was validated functionally but not load-tested. **What you say afterward:** "The migration testing validated correctness but not load behavior under peak concurrency. Going forward, any infrastructure migration needs a load test that specifically replicates our highest historical traffic event, not just our average day, before we call it validated." --- ### Q29. Scenario - The Incremental Model That Silently Stopped Being Incremental A dbt incremental model has been running fine for six months. This week someone notices `dbt run` for this specific model started taking 45 minutes instead of its usual 3. What likely happened, and how do you confirm it? The most likely cause is that the model stopped actually running incrementally and started reprocessing the full table on every run - the symptoms (much longer runtime, no error) match this pattern closely. ```sql -- Check the compiled SQL that dbt is actually generating and -- executing - not the source model file, the compiled output ``` ```bash dbt compile --select my_incremental_model cat target/compiled/my_project/models/my_incremental_model.sql ``` Look specifically at whether the `is_incremental()` block's WHERE clause is present and evaluating to something sensible in the compiled output. Common causes of an incremental model silently reverting to full-refresh behavior: someone recently ran `dbt run --full-refresh` for an unrelated reason and it stuck as a habit in a CI script; the model's `unique_key` configuration was removed or is now referencing a column that no longer exists, causing dbt to fall back; or the underlying source table's schema changed in a way that broke the `is_incremental()` condition's logic without throwing an error. **What confirms it definitively:** comparing row counts processed on a normal run versus this slow run. If the slow run's logs show it processing close to the full historical row count instead of just the recent window, that confirms the model reverted to full processing, and the fix is finding and correcting whatever broke the incremental condition - not just accepting the slower runtime as the new normal. --- ### Q30. Scenario - Two Analysts, Two Different Numbers, Same Question Two analysts on different teams both compute "monthly active users" for the same month and get numbers that differ by 8%. Both queries "look correct." Your manager asks you to figure out which one is right. The most likely explanation, before assuming either query has a bug, is that "active user" was never given one agreed-upon definition, and both analysts independently and reasonably interpreted it differently. First step is not debugging SQL - it is comparing the two definitions in plain English. Does "active" mean logged in at all, or logged in and completed a specific action? Does the month boundary use UTC or local time? Does "user" include accounts created mid-month with only partial-month data, or only accounts that existed for the full month? ```sql -- Query A's actual definition, extracted from the WHERE clause WHERE event_type = 'login' AND event_date BETWEEN ... -- Query B's actual definition WHERE event_type IN ('login', 'app_open') AND event_date BETWEEN ... ``` Often the discrepancy traces to exactly this kind of difference - one query counts a broader set of "activity" events than the other, and both are internally consistent and defensible, they are just answering two subtly different questions that happen to share the same name. **The real fix is not picking a winner** - it is establishing one shared, documented definition of "monthly active user" that both teams' models reference, ideally through a single dbt model or metric definition that both dashboards pull from, so the question of "which number is right" cannot recur for this metric again. > 💡 **Green Flag:** The candidate frames this as a definitional/governance problem to solve once, not a bug to fix in one query. Interviewers are listening for whether you understand that "two correct queries, two different answers" is usually a semantic layer problem, not a SQL problem. --- ### Q31. Scenario - The Backfill That Took Down Production You need to backfill three months of historical data into a table that a live dashboard reads from. You run the backfill directly against the production table during business hours, and the dashboard becomes unusably slow for two hours while it runs. The mistake was not the backfill itself - it was running a large write operation directly against a table actively being read by a live dashboard, during business hours, without isolating the two. **What you say when this happens, honestly:** "I ran the backfill directly against the live table without isolating it, and it degraded dashboard performance for about two hours. I'm stopping the backfill now and will redo it properly." **How it should have been done instead:** write the backfilled data to a separate staging table first, fully validate it there with no risk to production, and only then swap it into place with a fast, atomic operation - not a long-running write against the table users are actively querying. ```sql -- Backfill into an isolated table, not the live one CREATE TABLE orders_fact_backfill AS SELECT * FROM orders_fact WHERE 1=0; -- same structure, empty -- ... run the full backfill into orders_fact_backfill ... -- ... validate row counts, spot-check values ... -- Then swap - this should be near-instant, not a multi-hour operation ALTER TABLE orders_fact RENAME TO orders_fact_old; ALTER TABLE orders_fact_backfill RENAME TO orders_fact; ``` **What you propose afterward:** "Going forward, any backfill or bulk write touching a table with live dashboard traffic goes through a staging-then-swap pattern, and runs outside business hours by default unless there's a specific reason it can't wait." --- ### Q32. Scenario - The Cost Alert Nobody Set Up A Spark job that used to cost about $40 a day in cluster compute has been quietly costing $600 a day for the past three weeks. Nobody noticed until finance flagged the AWS bill. How do you find the cause, and what do you put in place so this does not happen silently again? Fifteen times normal cost with no one noticing for three weeks is itself the real problem to solve - the cause matters, but the missing cost alerting is what let this run silently that long. ```bash # Check job history for changes in cluster size, runtime, # or instance type around when the cost started climbing ``` Common causes of this kind of silent cost spike: someone increased the cluster's instance count or instance type for a one-off test and never reverted it; a data volume increase upstream caused the job to auto-scale up and just stayed there; a change in the job's logic introduced an expensive shuffle or cross join that dramatically increased compute time per run without technically failing. Once the direct cause is fixed, the more important fix is putting a cost anomaly alert in place so a 15x cost increase gets flagged automatically within a day, not discovered three weeks later by finance reading a bill. ```python # Conceptual - a daily job cost check that alerts on deviation # from a rolling baseline, not a fixed absolute threshold if todays_cost > (rolling_7day_avg_cost * 3): send_alert( channel="#data-eng-alerts", message=f"Job cost {todays_cost} is 3x+ the recent average " f"({rolling_7day_avg_cost}). Investigate before next run." ) ``` **What you say to your manager:** "The immediate fix is reverting whatever change caused the spike, and I'll have that root cause today. The bigger gap is that we had no cost alerting at all - a 15x increase should never take three weeks and a finance review to notice. I want to put a cost anomaly alert in place this week so this class of problem gets caught in a day, not a month." --- ### Q33. Scenario - The New Hire Who Deleted a dbt Model A new data engineer on your team, two weeks into the job, accidentally deleted a dbt model that six downstream dashboards depend on, then force-pushed to main to "clean up" what they thought was a mistake in their own branch. The dashboards are now broken. This is a process failure more than a person failure, and how you respond to the new hire matters as much as fixing the dashboards. **Immediate technical fix:** the model still exists in Git history even after a force-push, since Git rarely truly deletes anything from the reflog immediately. ```bash # Find the commit before the deletion git log --all --oneline -- models/marts/fct_daily_revenue.sql # Restore the file from that commit git checkout <commit-hash> -- models/marts/fct_daily_revenue.sql git add models/marts/fct_daily_revenue.sql git commit -m "Restore accidentally deleted model" git push origin main ``` Re-run `dbt run` for the restored model and confirm the six downstream dashboards recover. **How you handle the new hire:** do not make them feel like they broke something unforgivable - a force-push to main should never have been *possible* for anyone without review, and that is the real gap. "This wasn't really your mistake - main should have branch protection requiring a PR and review before merge, so a force-push like this couldn't happen at all. I'm going to get that set up today. In the meantime, let's walk through what happened so it's clear for next time." **The structural fix:** add branch protection on main requiring at least one approving review and blocking direct force-pushes, for everyone, not just new hires - this is the actual fix, since without it the same mistake is available to any engineer on the team, at any tenure. --- ### Q34. Scenario - The Metric That Changed Definition Mid-Flight Your `gross_merchandise_value` (GMV) metric has been calculated one way for a year. A new requirement means it now needs to exclude cancelled orders, which it previously included. Leadership wants this changed "as soon as possible," but historical reports and quarter-over-quarter comparisons depend on the old definition. Changing the definition silently, in place, would make every historical trend line and quarter-over-quarter comparison compare two different things without anyone realizing the comparison is no longer apples-to-apples - which is a worse outcome than taking an extra day to do it correctly. **The right approach is not to overwrite the old metric - it is to version it.** Create the new definition as a distinct metric, clearly labeled, and only replace the old one after historical reports have either been recalculated under the new definition or explicitly flagged as using the old one. ```sql -- Do not silently redefine gmv - create a clearly versioned -- new metric so historical comparisons remain honest {{ config(materialized='table') }} SELECT order_date, SUM(order_amount) AS gmv_v1_including_cancelled, -- old definition, preserved SUM(CASE WHEN status != 'cancelled' THEN order_amount ELSE 0 END) AS gmv_v2_excluding_cancelled -- new definition FROM orders_fact GROUP BY order_date ``` **What you communicate to leadership, even under pressure to move fast:** "I can have the new GMV definition live this week. What I want to flag before I do: every existing dashboard and historical report currently uses the old definition, and changing it in place would silently break every quarter-over-quarter comparison anyone has ever screenshotted or referenced. I'd like a day to either recalculate historical numbers under the new definition or clearly label which reports use which version - that protects us from a much worse conversation in three months when someone notices a quarter looks inexplicably different." ---
12 behavioral questions with full answers covering ownership, pushback, and what interviewers are actually evaluating. ### How to Use the Behavioral Answers Do not memorize the examples below word-for-word. Interviewers who conduct many interviews can usually tell when a story is polished but generic, because it lacks the small, specific details that only come from something that actually happened - the exact metric name, the exact wrong assumption, the exact fix. Use each example as a structure, not a script: situation, technical context, the decision you made, the trade-off you weighed, the result, and what you changed afterward. Replace the specifics with an incident or project you actually experienced. If you genuinely have not experienced something close to a given question, say so honestly and answer with the closest real example you have, rather than fabricating a story that will not hold up under a follow-up question. ### Q35. Tell me about a data quality incident you caused or were involved in. What happened and what did you change afterward? Pick something real and specific - vague answers ("I learned to be more careful") are unconvincing. Interviewers want the actual mechanism of the mistake and the actual mechanism of the fix, not a moral. Good structure: what happened, what the downstream impact was, how it was caught, and specifically what changed in the pipeline or process afterward - not just "I was more careful next time." Example: "I built an incremental dbt model with a lookback window that was too short - two days instead of five - which meant orders that settled late were sometimes excluded from the daily revenue aggregate. It ran fine for weeks because late settlement is rare, until a payment provider outage caused a wave of orders to settle three days late, and revenue for that week was undercounted by about 4%. I caught it when the finance reconciliation didn't match. The fix was widening the lookback window and, more importantly, adding a reconciliation test that compares the model's output against an independent raw count on a rolling basis, so a gap like this gets caught by a test instead of a human noticing a mismatch three weeks later." ### Q36. A stakeholder asks for a report "by end of day" but the requirements are vague - you are not sure exactly what they need. What do you do? Building the wrong thing fast is worse than building the right thing slightly late, but that has to be communicated, not assumed. The right move is a short, specific clarifying conversation rather than either guessing silently or refusing to start until every detail is perfect. "Before I build this, can you confirm: when you say revenue, do you mean gross or net of refunds? And is 'by region' meant to be by shipping address or billing address?" Two specific questions, asked immediately, cost five minutes and prevent hours of rework. If the stakeholder is unavailable and the deadline is real, build the most defensible interpretation, state your assumptions explicitly in the deliverable itself, and flag that you will adjust quickly once you get confirmation - rather than silently picking an interpretation and hoping it is right. ### Q37. Tell me about a time you disagreed with a data modeling decision made by someone more senior than you. Interviewers are checking whether you can voice a technical disagreement productively, not whether you always win the argument or always defer silently. Good approach: ask about the reasoning first, since senior engineers often have context you do not. "I noticed we're using a snowflake schema for the customer dimension here - I would have expected a star schema given how this table gets queried. Is there a specific reason for the extra normalization?" Sometimes the answer reveals a constraint you were not aware of. Sometimes they reconsider once you raise the query pattern concern. If they still prefer their approach after the conversation and it is not a correctness or safety issue, implement it their way - a single disagreement on a modeling preference is not worth escalating repeatedly. Document your concern briefly in a PR comment or design doc so it is on record without becoming a recurring argument. ### Q38. Describe a time you had to say no to a request because it would compromise data quality or pipeline reliability. The key is showing you can push back with a specific technical reason, not just "I didn't feel comfortable." Example: "A product manager wanted a new feature's usage metric added to an existing dashboard by end of week, but the only available data source for it had known duplicate event firing that we had not yet fixed - roughly 15% of events fired twice. I said I could ship it in two days if we accepted a known 15% overcount clearly labeled as provisional, or five days if we fixed the deduplication first. I laid out both options with the actual numbers rather than either just saying no or silently shipping something I knew was wrong. They chose to wait for the clean version once they saw what 'fast' actually meant." ### Q39. Tell me about a time you had to learn a new tool or technology quickly for a project. Be specific about the learning process itself, not just "I read the docs." Example: "I had used Airflow but never dbt before joining a project already built entirely on it. I spent the first day reading the official dbt docs specifically on materializations and `ref()`, then rebuilt one small existing staging model from scratch in a sandbox to understand how the dependency graph actually resolved, rather than just reading about it. By the end of the first week I could confidently modify existing models and had a working mental model of `is_incremental()`, even though I would not have called myself an expert yet." ### Q40. How do you prioritize when you have a production incident, a stakeholder request, and your own planned work competing for the same day? Production incidents affecting live systems come first, always - a broken pipeline affecting a dashboard someone is actively relying on outranks new feature work. After that, anything blocking another person's work takes priority over solo planned work, since your delay becomes their delay too. Your own planned work comes last unless it has an external deadline that would slip. If there genuinely is not enough time for everything in a day, the honest move is telling your manager explicitly which item is being deprioritized and why, rather than silently dropping something and hoping nobody notices its absence. ### Q41. Tell me about a time a pipeline you built failed in production. Walk me through what happened. Every data engineer has shipped something that broke. The interviewer is checking your diagnostic process and what changed afterward, not judging you for the failure itself. Example: "A watermark-based incremental pipeline I built started silently skipping data after a source system migration changed their timestamp field from UTC to their local timezone without announcing it. My pipeline's watermark logic compared timestamps assuming UTC, so it was actually comparing against a shifted window and missing a few hours of data every single day for about a week before anyone noticed a gap. I fixed the immediate issue by correcting the timezone assumption, backfilled the missed week, and added a daily row-count sanity check comparing against the source system's own count, so a silent data-skipping bug like this gets caught within a day instead of a week." ### Q42. How do you handle being asked to cut corners on data validation to hit a deadline? Acknowledge the real tension - deadlines are real constraints, not something to dismiss - while being specific about what corner-cutting actually costs. "I would want to be specific about what 'cutting corners' means in this case rather than treating all validation as equally skippable. Some checks - like confirming row counts roughly match the source - take almost no extra time and catch the most common failure modes. Others, like a full reconciliation against an independent source, take longer and might genuinely be worth deferring if the downstream use is low-stakes. I would lay out which checks are cheap-and-essential versus expensive-and-deferrable, rather than just saying yes or no to 'skip validation' as one decision." ### Q43. Tell me about a time you had to explain a technical data problem to a non-technical stakeholder. The evaluation here is whether you can translate without either condescending or oversimplifying to the point of being misleading. Example: "I had to explain to a marketing director why campaign attribution numbers didn't match between our dashboard and their ad platform's own reporting. Instead of explaining our JOIN logic, I explained it as: 'Our system counts a purchase as attributed to your campaign if it happens within 24 hours of a click. The ad platform uses a 7-day window. Neither is wrong - they're answering slightly different questions, and the gap you're seeing is basically entirely explained by that difference.' That framing let her make an informed choice about which number to report, rather than treating one system as broken." ### Q44. Describe a situation where you had incomplete information but still had to make a decision or take action. Interviewers want to see reasonable judgment under uncertainty, not paralysis or overconfidence. Example: "During an active incident, I had to decide whether to roll back a deployment or let the on-call developer keep debugging forward, without knowing for certain the deploy was the cause. I made the call to roll back based on the timing correlation being strong enough, even without full certainty, because rolling back was reversible and low-risk if wrong, while continuing to debug forward while production stayed broken was the higher-cost option if I was right about the cause. I said explicitly in the incident channel that I wasn't 100% certain but the timing made it the safer bet, so the team understood the reasoning, not just the decision." ### Q45. Tell me about a time you gave feedback on a colleague's pipeline or model that they did not initially agree with. Show that you can raise a concern specifically and constructively, and that you handle disagreement about the feedback itself professionally. Example: "I reviewed a colleague's incremental model that used `dbt run --full-refresh` in a comment as the recommended way to 'fix' any issue with it, which struck me as masking rather than solving whatever was going wrong underneath. I raised it as a specific question in the PR - 'what happens if this needs a full refresh in production, given the table is 400GB now' - rather than a general comment that it felt wrong. They initially felt it was a minor note not worth blocking the PR over. I agreed to approve it as-is but asked to pair on investigating the underlying incremental logic the following week, and we found a real bug in the `unique_key` handling that the full-refresh workaround had been silently covering up." ### Q46. Where do you want to be in your data engineering career in two to three years? A strong answer for someone at 2-5 years describes owning a significant piece of data infrastructure end-to-end - not just building pipelines but being the person others come to for a specific domain - and growing into either deeper technical specialization (streaming, distributed systems performance) or broader architectural ownership, depending on genuine interest rather than a generic title upgrade. Vague answers about "growing my skills" without specifics read as unprepared for this question. ---
These are broad, market-oriented ranges, not guaranteed offers. Actual compensation varies by company stage, location, total years of experience, interview performance, area of specialization, and the mix of base salary, bonus, and equity - a range here reflects rough total compensation observed in the market, not a fixed base number. | Company Type | Range | |:---|:---| | Early-stage startup | Rs 12L - Rs 20L | | Mid-stage product startup | Rs 18L - Rs 30L | | Large product company (Flipkart, Swiggy, Razorpay tier) | Rs 25L - Rs 45L | | Fintech / high-scale (Zerodha, PhonePe tier) | Rs 30L - Rs 55L | These numbers also assume confident, example-backed answers across Tier 2. Candidates who answer only conceptually, without a real production incident or project to reference, tend to land at the lower end of each range.
You are 2-5 years into data engineering. You have built pipelines, written a lot of SQL, and probably broken production ...
No answers given. These are the floor, not the ceiling. SQL What is the difference between RANK(), DENSERANK(), and ROWN...
SQL Under Pressure 1. Write a query to find the second highest salary in each department without using LIMIT or TOP. Thi...
These are live problems interviewers put in front of you and watch how you think. There is no single correct answer. Q25...
12 behavioral questions with full answers covering ownership, pushback, and what interviewers are actually evaluating. H...
These are broad, market-oriented ranges, not guaranteed offers. Actual compensation varies by company stage, location, t...
Aligns directly with DevOps, Site Reliability (SRE), and Platform Engineering job descriptions.