Skip to content
Tech Interview Prep
Technical interview guide

Data Quality & Validation

Catching bad data before it reaches downstream consumers: schema checks, freshness, and anomaly detection.

Read
29 min
Practice MCQs
25
Interview Q&A
25
Edition
v4
Editorial status
Reviewed

Scope: Great Expectations, dbt, Deequ, OpenLineage 1.53, JSON Schema 2020-12, and OpenAPI current 2026-08-31.

Curated: · Written: · Reviewed:

25 questions

MCQ-2A nightly pipeline runs one data-quality test on the orders table: `customer_id IS NOT NULL`, scoped to yesterday's partition. It passes on all 4.2M rows. The referential-integrity and row-count checks the team wants are not built yet, so the release report can cite only this result. What has the passing test established?
MCQ-25In Great Expectations, an engineer needs an expectation to evaluate only a subset of records matching a specific filter condition without changing the entire Batch definition or global dataset scope. Which parameter accomplishes this evaluation-level subset filtering?
MCQ-11A nightly job runs three assertions on `orders` before publishing to the warehouse: null rate on `customer_id` (threshold 0.5%, severity warn), freshness of `created_at` (within 6 h, severity block), and row count (1.0M ±5%, severity warn). This run observes 1.2% nulls, a 9 h freshness lag, and 1.02M rows. What does the run record, and what happens to the publish step?
MCQ-12A dbt Core 1.x job builds `fct_orders` (12,000,000 rows), runs a `not_null(customer_id)` test configured `severity: warn`, then builds `fct_revenue` from `fct_orders`. The team chose warn so the pipeline keeps running, but they expect the 4.1% null rows to be held out of the published table. Last night the test failed at 4.1% nulls and the job exited 0. What was the state of `fct_orders` and `fct_revenue` after the run?
MCQ-23A nightly data-quality check counts payments with negative amounts in the last day: ```sql SELECT COUNT(*) FROM payments WHERE amount < 0 AND created_at >= CURRENT_DATE - INTERVAL '1 day' ``` The pipeline fails when the count is greater than 0. The check has returned 0 every night for 30 days. On March 3 a backfill inserts 2,000 rows with `amount = -50` and `created_at = '2024-03-01'`. The March 3 run still returns 0. Which test, written before deployment, would have failed on this defect?
MCQ-6A nightly job loads a day of payments into a warehouse and validates only the day's grand total against the source. For 2024-06-03 the source has 12,480 rows summing to $3,120,000. The load drops payment 4471 ($40,000, West) and loads payment 8820 ($40,000, East) twice. The reconciliation reports 12,480 rows and $3,120,000 and marks the load clean. What is the correct assessment?
MCQ-5A nightly pipeline loads 1,000,000 source rows into a target table. Grand totals match on both sides: 1,000,000 rows and $50,000,000 in amount. Segmented by region, the target shows EU at $12M against the source's $10M, and APAC at $13M against the source's $15M. What does this pattern indicate?
MCQ-19A nightly job loads the 200 files listed in a manifest (10,000,000 rows total) into an append-only warehouse table, tagging every row with its source file. It reports SUCCESS and the table holds 10,000,000 rows. The table has no rows tagged file 147 (3,500 rows in the manifest), and file 148's 3,500 rows appear twice. What does this establish?
MCQ-4A revenue dashboard joins `fact_orders` to `dim_customer` on `customer_sk` with an inner join and reports 412 fewer orders than the source system sent. The 412 rows are present in `fact_orders`, and the source system sometimes sends an order before the customer's dimension row is created. Which validation stage, run before publish, would have caught the discrepancy?
MCQ-3A `dim_customer` table is loaded as a Type-2 slowly changing dimension with a declared grain of one row per (customer_id, valid_from); it currently holds 2.4M rows for 310k customers. A uniqueness test on `customer_id` alone fails with 8,900 duplicate values, and every duplicate pair differs in `valid_from`. What should the engineer do?
MCQ-14A validation rule flags records whose `amount` is negative. Over one week the pipeline processes 500,000 records. An audit later confirms that 400 records held genuinely invalid amounts (negative or non-numeric). The rule fired on 350 records; 300 of those were truly invalid, and 50 were legitimate refunds that are negative by design. What is the rule's false-negative rate for the week?
MCQ-13An order pipeline validates each event against an Avro schema before writing to the warehouse. One malformed event throws during validation, the offset is never committed, and the consumer restarts from the same offset; the job has been stuck on that record for 6 hours and consumer lag is 12M and rising. The upstream team will correct the schema tomorrow, and the affected orders must be reprocessed. What should the pipeline do with the malformed record?
MCQ-7At 10:00:00, an orders stream has events up to 09:57:30. The serving table's newest order_ts is 09:52:10. A backfill rewrote 100 old rows at 09:59:50, so the table's last_modified is 09:59:50. The dashboard computes freshness as now - last_modified and shows 10 seconds. What freshness should on-call page on?
MCQ-1A daily `orders` load runs Monday–Friday and writes 1.2M–1.4M rows. The only rule on the table, `freshness < 24h`, pages on-call every Saturday and Sunday, so the team has muted it. Last Tuesday the load job reported success but wrote 380k rows, and revenue dashboards read 68% low for three days. Which single rule change would have paged on that Tuesday and stayed quiet over the weekend?
MCQ-16A nightly job loads orders into a warehouse and emits one row-count metric per run. The only alert is a static threshold: fire when a run loads fewer than 1,000,000 rows. Weekday runs normally load 1.95M–2.10M rows; weekend runs normally load 1.05M–1.15M. Last night, a Tuesday, the job loaded 1,150,000 rows, no alert fired, and the revenue dashboard is down 42% versus the previous Tuesday. Which change would have caught this run without also firing on normal runs?
MCQ-9A team alerts when a table's daily row count deviates more than 3σ from a single global baseline of 1,200,000 ± 40,000 rows. Every Saturday the table legitimately loads ~300,000 rows, so on-call is paged every weekend. They add a day-of-week baseline (Saturday: mean 300,000, sd 30,000) and the weekend pages stop. A month later a Saturday load lands at 1,150,000 rows. What is the correct reading of this event?
MCQ-10A metrics platform pages when a tenant's hourly p99 exceeds 3× a single global baseline of 120 ms. Tenant `batch-etl` runs 380–420 ms p99 every hour and pages constantly; tenant `checkout-api` normally runs 55–70 ms and sat at 300 ms for six hours last Tuesday without a page. Tenants are onboarded automatically at ~30/week and each has a strong weekly cycle. Which change fixes both symptoms?
MCQ-8An hourly loader writes into a daily-partitioned `events` table. At 09:00 the freshness monitor is green: `max(loaded_at) = 08:52`, inside the 30-minute SLA. Today's partition holds 1.1M rows; at 09:00 on each of the previous seven days, that day's partition held 4.6M–5.2M rows. Revenue on the dashboard for the last 24 hours is 38% of the trailing 7-day average. The loader exits 0 and no job has failed. What is the correct next step?
MCQ-24An orders pipeline lands 200 batches a day. A new quality check will page the on-call engineer whenever the null rate of orders.customer_id in a landed batch exceeds a threshold. On good loads the null rate exceeds 0.5% on 10% of batches and exceeds 1.0% on 2% of batches; the highest null rate ever recorded on a good load is 1.4%. A partial upstream join failure leaves 1.5%-2.5% of customer_id null, and a full join failure leaves 10%-40%. Every bad batch that is not paged is served to the customer dashboard until someone notices it, and on-call will absorb at most 5 pages a day from this check. Which threshold should the check use?
MCQ-15An orders pipeline holds an orders table whose total_amount can be null. One data-quality rule, R-17, runs at 02:00 daily: the null rate of orders.total_amount must stay at or under a 5% limit. Each run is stored as one row keyed by (rule_id, run_date) that holds only the measured null rate, and the dashboard computes pass or fail at read time by comparing that rate with the limit on the rule row. February passed 27 of its 28 runs: the null rate sat between 2.8% and 3.4% on those 27 days, and on February 17 a truncated upstream file pushed it to 7.4%. On March 1 at 01:50 the rule owner edits R-17 in place, changing the limit from 5% to 2%. The March 1, 2 and 3 runs measure 3.1%, 3.0% and 3.2% nulls. The dashboard now reports 0% pass for February and 0% for March, and on-call is paged for a quality regression. How many of the 31 stored runs change their pass/fail status because of the edit?
MCQ-20A nightly pipeline runs `raw_orders` → `stg_orders` → `fct_orders` → `revenue_daily`, and quality assertions are attached to dataset versions in the lineage graph. For June 3 the graph shows: `raw_orders` landed 01:05 with 1,204,331 rows (assertion: within 1% of 1,200,000), passed; `stg_orders` written 02:10 (freshness SLA 03:00), passed; `fct_orders` v1 written 02:47 with 1,200,412 rows. Passed; `revenue_daily` v1 written 03:05. At 14:00 a backfill wrote `fct_orders` v2 with 1,051,882 rows. Row-count assertion failed. At 15:00 `revenue_daily` v2 was written. The June 3 dashboard now shows revenue 12% below the prior week. What does the assertion-on-lineage let the engineer conclude about the cause?
MCQ-17A nightly validation job runs 30 checks on a 900 GB Parquet table: 26 unfiltered aggregate checks (row count, null fraction, distinct count, mean, stddev) and 4 checks whose `where` clauses are `region = 'EU'`, `region = 'US'`, `region = 'APAC'`, and `tier = 'gold'`. The engine shares one scan only among analyzers carrying an identical filter. How many full scans does the job perform, and what is the cheapest change that lowers it?
MCQ-18A nightly job appends one immutable partition per day of clickstream events and reports daily row counts, distinct user IDs, and p99 page-load time. The team wants to replace the 6-hour full rescan with a 20-minute job that folds each new partition into the running totals. Which condition makes that fold correct?
MCQ-21A nightly quality check must finish in 10 minutes. The `sessions` table has 120M rows; a full scan takes 30 minutes. The check looks for duplicate `session_id` values, which historically occur at 1 per 100,000 rows and can corrupt session analytics. The team can scan 1M rows in 10 minutes. What should they do?
MCQ-22A multi-tenant validator writes per-tenant column profiles (min, max, null_count, distinct_count) to a shared observability cluster that every tenant's support engineer can query, and writes each rejected row's full column values to a `failed_rows` table in the tenant's own schema. Tenant X has 3 rows in `patients`; its `diagnosis_code` profile reads distinct_count = 1, min = max = 'E11.9', and the last run rejected 2 rows whose `email` values are stored verbatim. Which statement about the exposure is correct?