TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 26

Data Quality and Deduplication: practice questions

400 questions in four levels, all on RetailMart, the practice database of this course. Write every query yourself, get it wrong, read the error, fix it. That is how it sticks.

Open the SQL PlaygroundPut RetailMart on your laptop
Answers are coming soon. Post your query in the WhatsApp Community or Discord and we will check it together.

Core syntax, applied directly

CONCEPTUAL

  1. Q1What does it mean for a row to be a "duplicate" (logical key vs full row)?
  2. Q2Why is GROUP BY key HAVING COUNT(*) > 1 the canonical dupe-detector?
  3. Q3ROW_NUMBER OVER (PARTITION BY key ORDER BY recency) - what does it produce?
  4. Q4Why does ROW_NUMBER let you keep the "best" row when deduping?
  5. Q5Difference between DISTINCT and DISTINCT ON (Topic 22 tie-in).
  6. Q6What does COALESCE(a, b, c) return?
  7. Q7What does NULLIF(a, b) return?
  8. Q8What does IS DISTINCT FROM do that <> does not?
  9. Q9Three NULL strategies in cleansing: drop / replace / flag.
  10. Q10Why is LOWER + TRIM the minimum for free-text equality?
  11. Q11What does REGEXP_REPLACE(s, pat, repl) do?
  12. Q12~ vs ~* - case-sensitive vs case-insensitive regex match.
  13. Q13Why are phone numbers a classic standardization target (formats vary)?
  14. Q14Why is email a common duplicate key (with variants like trailing spaces)?
  15. Q15What is a "missing-value cluster" and why flag it?
  16. Q16What does it mean for a value to be "suspicious" (outside expected range)?
  17. Q17Why is a > 50% price change a likely data-entry error?
  18. Q18What does "canonicalization" mean for free-text keys?
  19. Q19How does Topic 22 DISTINCT ON pick the "right" row per dedup key?
  20. Q20Why dedup in a view/CTE (not by DELETE) when the table is read-only?
  21. Q21What's the difference between data cleansing and data validation?
  22. Q22Why is COUNT(*) different from COUNT(col) under NULLs?
  23. Q23What is "reconciliation" between two near-duplicate lists?
  24. Q24Why do anomaly flags need a BASELINE (mean / median / percentile)?
  25. Q25Name three RetailMart quality checks worth automating (per CONTEXT).

DUPLICATE DETECTION

  1. Q26Find customer emails that appear more than once (GROUP BY HAVING).
  2. Q27Find customers sharing the same (first_name, last_name) - likely homonyms.
  3. Q28Find products with duplicate names within the same brand.
  4. Q29Find suppliers with the same name (case-insensitive via LOWER).
  5. Q30Find stores with the same (city, address) - possible duplicates.
  6. Q31Find orders with same (cust_id, order_date, net_total) - likely double-submit.
  7. Q32Find reviews where (customer_id, product_id) appears twice.
  8. Q33Find tickets with the same (customer_id, subject) opened twice.
  9. Q34Find payments with same (order_id, amount) appearing twice.
  10. Q35Find page_views with same (customer_id, page_url, view_timestamp).
  11. Q36Find brand names that differ only by trailing whitespace.
  12. Q37Find customer emails that differ only by case (LOWER).
  13. Q38Per duplicate-email group: rank rows with ROW_NUMBER ORDER BY registration_date DESC.
  14. Q39Keep only the most-recently-registered row per duplicate email (filter rn=1).
  15. Q40Per duplicate (first_name, last_name): keep the row with the most orders.
  16. Q41Use DISTINCT ON (Topic 22) to keep the latest customer per email.
  17. Q42Count dupe groups for emails vs phones - which has more issues?
  18. Q43Find duplicate product (name, brand_id) across suppliers.
  19. Q44Find duplicate inventory snapshots (warehouse_id, prod_id, snapshot_date).
  20. Q45Find duplicate work_orders by (production_line_id, started_at).
  21. Q46Find addresses with same (customer_id, address_line1).
  22. Q47Find pay_slips with same (employee_id, salary_month, salary_year).
  23. Q48Detect duplicate review_text per product (verbatim copies).
  24. Q49Detect duplicate review_text per product after LOWER+TRIM canonicalization.
  25. Q50Use ROW_NUMBER to identify the "loser" rows that would be deleted in dedup.

NULL STRATEGIES & TEXT STANDARDIZATION

  1. Q51Replace NULL review_text with '(no comment)' via COALESCE.
  2. Q52Replace NULL error_message in record_changes with '(none)'.
  3. Q53Use NULLIF to convert empty string '' to NULL for review_text.
  4. Q54Count rows where order_status IS DISTINCT FROM 'Delivered' (NULL-safe).
  5. Q55Standardize review_text: TRIM + LOWER.
  6. Q56Standardize brand name: TRIM both ends.
  7. Q57Use REGEXP_REPLACE to collapse multi-space runs in review_text to single space.
  8. Q58Strip leading/trailing punctuation from review_text.
  9. Q59Standardize email: LOWER + TRIM, then compare to original to spot dirty rows.
  10. Q60Use ~ to match products.name starting with 'iPhone'.
  11. Q61Use ~* (case-insensitive) to match brand names containing 'samsung'.
  12. Q62Find review_text containing only whitespace via ~ '^\s*$'.
  13. Q63Find emails NOT matching a simple email pattern with !~.
  14. Q64Replace digits in review_text with '#' via REGEXP_REPLACE.
  15. Q65Extract only digits from a phone with REGEXP_REPLACE(phone, '\D', '', 'g').
  16. Q66Standardize phone to E.164-ish: '+91' + last-10 digits (concept query).
  17. Q67Find customer first_names with leading/trailing spaces (TRIM <> orig).
  18. Q68Count NULL clusters: customers with NULL phone, by registration year (Topic 23).
  19. Q69Backfill NULL region (concept): COALESCE(region, 'Unknown').
  20. Q70Per category: COUNT(*) vs COUNT(price) - measure missing prices.
  21. Q71Show rows where two columns are "different" using IS DISTINCT FROM.
  22. Q72Standardize store address: TRIM + collapse spaces + LOWER.
  23. Q73Reviews: show original vs cleaned (LOWER+TRIM+single-spaced) side-by-side.
  24. Q74Find customers whose first_name is all uppercase (~ '^[A-Z]+$').
  25. Q75Find products whose name contains non-ASCII characters (regex).

ANOMALY FLAGS & RECONCILIATION

  1. Q76Flag orders where gross_total > 100000 (suspicious threshold).
  2. Q77Flag orders where discount_amount > gross_total (impossible).
  3. Q78Flag products where price < cost_price (negative margin).
  4. Q79From record_changes: find price-change events with > 50% jump (likely error).
  5. Q80From record_changes: find salary-change events with > 30% jump.
  6. Q81Flag pay_slips with net_salary < 1000 or > 200000 (outside expected range).
  7. Q82Flag reviews with rating outside 1..5 (data-integrity check).
  8. Q83Flag tickets where resolved_date < created_date (impossible).
  9. Q84Flag shipments where delivered_date < shipped_date (impossible).
  10. Q85Flag orders where order_date < customer registration_date (impossible per business rule).
  11. Q86Count rows failing each rule above as a single "data quality scorecard".
  12. Q87Reconcile two product-name lists: same after LOWER+TRIM but differ verbatim.
  13. Q88Reconcile brand names across suppliers: same after canonicalization.
  14. Q89Find customers with the same (LOWER(email), phone) - alternate dedup key.
  15. Q90Per region: % customers with a NULL phone (missing-value cluster check).
  16. Q91Per category: % products with NULL description (concept check).
  17. Q92Flag a price change > 50% in audit.record_changes joined to products.name.
  18. Q93Flag orders with status 'Delivered' but no payment row (consistency check).
  19. Q94Flag stores with zero orders (orphan rows).
  20. Q95Flag products with no inventory anywhere (catalog ghosts).
  21. Q96Flag employees with NULL department (assignment gap).
  22. Q97Reconcile order_items SUM(quantity*unit_price) vs orders.net_total per order.
  23. Q98Reconcile finance.payments total vs sales.payments total per order_id.
  24. Q99Find customer addresses whose city is misspelt vs the canonical list (LOWER+TRIM).
  25. Q100One-screen DQ scorecard: % failed per rule across the customer/orders tables.

Combined ideas, multi-step thinking

CONCEPTUAL

  1. Q1Composite dedup keys vs single column - when each is right.
  2. Q2Canonicalization-before-dedup: LOWER+TRIM+collapse-spaces, then group.
  3. Q3ROW_NUMBER vs DISTINCT ON for "keep most recent" - performance and clarity.
  4. Q4Tie-breaking in dedup (always include a deterministic secondary key).
  5. Q5NULL-safe equality: IS DISTINCT FROM and its inverse.
  6. Q6Coalesced-key join: COALESCE(a.x, '') = COALESCE(b.x, '') tradeoffs.
  7. Q7Soft-delete tag vs hard delete in cleansing (DELETE -> practice).
  8. Q8Quarantine pattern: invalid rows go to a SELECT result for review.
  9. Q9Baseline-driven anomaly: median, P95, IQR fences (Topic 22).
  10. Q10Why a fixed threshold (e.g. > 100k) ages badly; benchmark-relative is robust.
  11. Q11Regex anchors ^ / $ and why they pin patterns to whole strings.
  12. Q12Character classes [], quantifiers *, +, ?, {m,n}.
  13. Q13Greedy vs lazy quantifiers (concept).
  14. Q14Capture groups + backreferences in REGEXP_REPLACE.
  15. Q15Unicode pitfalls in TRIM (some "spaces" aren't ASCII).
  16. Q16Email normalization: lowercase, trim, strip plus-tags (concept).
  17. Q17Phone normalization: strip non-digits, keep last 10, prefix country.
  18. Q18Reconciliation across two lists: full-outer join on canonical key (Topic 10).
  19. Q19DQ scorecards: % rules passed per dimension (Topic 21 pivot).
  20. Q20Anomaly per group vs global - why per-group is more honest (Topic 22).
  21. Q21Missing-value clusters: detecting concentrations by time/region.
  22. Q22Inconsistent labels (e.g. "Maharastra" vs "Maharashtra").
  23. Q23Why fixing data at ETL beats fixing at every query.
  24. Q24When to materialize a "cleaned" view (Topic 25 tie-in).
  25. Q25Topic 27 preview: how clean cohorts depend on clean customers/orders.

MULTI-KEY DEDUP

  1. Q26Dedup customers by LOWER(TRIM(email)); keep latest registration.
  2. Q27Dedup customers by (LOWER(email), phone); keep most-orders row.
  3. Q28Dedup products by (brand_id, LOWER(name)); keep highest-revenue.
  4. Q29Dedup suppliers by LOWER(TRIM(name)); keep most-recently-active.
  5. Q30Dedup reviews by (customer_id, product_id); keep latest review_date.
  6. Q31Dedup tickets by (customer_id, subject, created_date::date); keep one per day.
  7. Q32Dedup orders by (cust_id, order_date, net_total); keep first by order_id.
  8. Q33Dedup page_views by (customer_id, session_id, page_url) within 1-second window.
  9. Q34Dedup addresses by (customer_id, LOWER(TRIM(address_line1))); keep latest.
  10. Q35Dedup inventory_snapshots by composite PK (warehouse, prod, date).
  11. Q36Dedup brand names by canonical LOWER+TRIM+collapse-spaces.
  12. Q37Use DISTINCT ON (Topic 22) to keep one row per canonical email.
  13. Q38Rank duplicates by quality score (orders, recency) and keep best.
  14. Q39List the "loser" rows in a dedup (rn > 1) for review.
  15. Q40Reconcile dedup result counts vs raw count per group.
  16. Q41Detect customers with same (first_name, last_name, date_of_first_order).
  17. Q42Detect products with same (brand_id, name, supplier_id) - supplier-level dupes.
  18. Q43Detect employee records with same (LOWER(email)) across stores.
  19. Q44Detect stores with same (city, LOWER(TRIM(name))) - sister stores.
  20. Q45Cohort-safe dedup: keep first signup per email (Topic 27 prep).
  21. Q46Per-month dedup: only allow one ticket per (customer, subject) per month.
  22. Q47Dedup keeping highest aggregate (e.g. customer with most spend).
  23. Q48Cross-table dedup: align customers with addresses on canonical pin/city.
  24. Q49Two-pass dedup: canonicalize -> dedup -> verify counts equal expected.
  25. Q50Build "v_customers_dedup" candidate (SELECT-only; Topic 25 view in your practice).

NULL-SAFE JOINS & STANDARDIZATION

  1. Q51NULL-safe match: a.x IS NOT DISTINCT FROM b.x in a JOIN ON.
  2. Q52Coalesced join key: COALESCE(a.email,'') = COALESCE(b.email,'').
  3. Q53Anti-join with IS DISTINCT FROM for unmatched rows.
  4. Q54Replace NULL store_id in HQ employees with -1 for grouping (concept).
  5. Q55Group missing-value clusters: % NULL phone per region per registration year.
  6. Q56COALESCE chain to backfill a derived city across customers/addresses.
  7. Q57NULLIF to neutralize sentinel '' before joining.
  8. Q58Standardize and re-join: LOWER+TRIM email both sides, then anti-join.
  9. Q59Detect mismatches that disappear after canonicalization (rule report).
  10. Q60Use REGEXP_REPLACE to canonicalize brand names, then group.
  11. Q61Normalize phone to last-10 digits, then dedup.
  12. Q62Identify free-text categorical drift (e.g. "Critical " vs "Critical").
  13. Q63Compute "after vs before" cleansing row counts (audit log).
  14. Q64Find pairs of rows that match only after canonicalization (reconciliation).
  15. Q65Build a cleaned customer set: COALESCE+TRIM+LOWER on email, phone, name.
  16. Q66Build a cleaned product set: TRIM+collapse-spaces on name.
  17. Q67Build a cleaned address set: standardized city/pincode (LOWER+regex).
  18. Q68Per region: missing-value heatmap by column (Topic 21 pivot of NULL %).
  19. Q69Detect "near-empty" strings: TRIM length 0 (treat as NULL).
  20. Q70Detect "all caps" or "all lower" rows (likely raw imports).
  21. Q71Detect mixed digit/letter junk in name fields.
  22. Q72Detect emails with disallowed characters via regex.
  23. Q73Detect addresses missing pincode digits via regex (~ '^\d{6}$').
  24. Q74Compose a "cleaned customer" derived row in a SELECT (Topic 25 view prep).
  25. Q75Quarantine view (SELECT-only): rows failing any standardization rule.

ANOMALY THRESHOLDS WITH BASELINES

  1. Q76Flag orders with net_total > regional P95 (Topic 22 baseline).
  2. Q77Flag products with margin % below category median.
  3. Q78Flag price-change events with ratio |new-old|/old > 0.5 (record_changes).
  4. Q79Flag salary changes > 30% in record_changes.
  5. Q80Flag tickets with resolution time > priority P95 (Topic 22).
  6. Q81Flag stores whose monthly revenue dropped > 50% vs last month (Topic 23).
  7. Q82Flag page_views per customer above NTILE-99 of session lengths (Topic 16).
  8. Q83Flag reviews whose rating is > 2 std devs from product mean (concept).
  9. Q84Flag pay_slip net outside [department-P5, department-P95].
  10. Q85Flag inventory snapshots with qty > 5x the warehouse median.
  11. Q86Flag ad spend per platform > P95 (Topic 22 baseline).
  12. Q87Detect orders without payments (consistency anti-join).
  13. Q88Detect deliveries with shipped_date AFTER delivered_date.
  14. Q89Detect tickets whose customer was registered AFTER ticket creation.
  15. Q90Detect orders whose customer doesn't exist (orphan FK) - should be empty.
  16. Q91Reconcile finance.payments vs sales.payments per order.
  17. Q92Reconcile order_items SUM vs orders.net_total per order.
  18. Q93Reconcile inventory snapshots vs sales activity (concept).
  19. Q94DQ scorecard: per rule total + failure count (Topic 21 pivot).
  20. Q95DQ scorecard per region (Topic 21 multi-dim pivot).
  21. Q96DQ trend over time (Topic 23 monthly): % rules failed per month.
  22. Q97Cleansed-derived MV (Topic 25 preview): a cleaned customers view.
  23. Q98Cleansed-derived MV: a cleaned products view (TRIM name, canonical brand).
  24. Q99Per region anomaly count per rule (Topic 21 pivot + Topic 22 baselines).
  25. Q100End-to-end audit: scorecard + quarantine sample for one BIG table (orders).

Interview grade, edge cases

CONCEPTUAL

  1. Q1Canonical-key strategy: deterministic transforms (case, trim, collapse, strip).
  2. Q2Soft / fuzzy match: same-canonical-key but different verbatim (reconcile).
  3. Q3Keep-rule design: most-recent vs most-active vs highest-quality-score.
  4. Q4Tie-breakers as part of the dedup contract (always deterministic).
  5. Q5Quarantine pattern: invalid rows go to a side-channel SELECT for review.
  6. Q6Percentile-baseline anomalies vs static thresholds (Topic 22).
  7. Q7Period-over-period drift detection (Topic 23 + LAG, Topic 18).
  8. Q8Reconciliation contracts between facts (orders<->payments<->shipments).
  9. Q9DQ scorecards: per rule, per dimension (region/month) pivot (Topic 21).
  10. Q10Materialized cleansed layer (Topic 25) - refresh cadence and SLA.
  11. Q11Composite-key dedup at scale: index strategy (Topic 20).
  12. Q12EXPLAIN ANALYZE (Topic 19) dedup queries - sort vs hash plan.
  13. Q13JSON quarantine payload (Topic 24) for failed rows.
  14. Q14Free-text classification: regex pre-screen + manual review.
  15. Q15Anomaly batching: per-region thresholds prevent false positives.
  16. Q16Multi-rule DQ engine: rule registry + per-rule pass/fail counts.
  17. Q17Data-contract enforcement: upstream vs downstream responsibilities.
  18. Q18NULL strategies at scale: don't COALESCE silently in DQ checks.
  19. Q19Reproducible cleansing: deterministic, idempotent transforms.
  20. Q20Auditing the auditor: tests for the DQ engine itself.
  21. Q21False-positive control: outliers vs anomalies (Topic 22 IQR vs P99).
  22. Q22Reconciliation drift over time (Topic 23) - when contracts break.
  23. Q23Cleansed MV (Topic 25) consumers + deprecation of old-name views.
  24. Q24DQ for cohort analytics (Topic 27 preview): why clean facts matter.
  25. Q25Sunset criteria: when is a rule no longer needed.

DEDUP ENGINES

  1. Q26Customer canonical-key dedup engine: LOWER(TRIM(email)) primary, phone tiebreak.
  2. Q27Keep-best customer: most orders -> highest revenue -> most-recent registration.
  3. Q28Product dedup engine: (brand_id, LOWER(TRIM(name))), keep top-revenue.
  4. Q29Supplier dedup engine: canonical name; keep most-recent activity.
  5. Q30Review dedup engine: (customer_id, product_id), keep latest by review_date.
  6. Q31Order dedup engine: (cust_id, order_date, net_total), keep min(order_id).
  7. Q32Session dedup engine: (customer_id, session_id, page_url), keep within 2s window.
  8. Q33Address dedup engine: (customer_id, LOWER(TRIM(line1, city, pincode))), keep latest.
  9. Q34Ticket dedup engine: same (customer, subject) within 24h, keep first.
  10. Q35Pay-slip dedup engine: (employee_id, salary_month, salary_year), keep one.
  11. Q36Inventory snapshot dedup engine: composite PK, keep most-recent updated_at (concept).
  12. Q37Brand canonical name engine: LOWER+TRIM+collapse-spaces; dedup.
  13. Q38DISTINCT ON (Topic 22) variant of the customer engine; compare results.
  14. Q39Loser-rows engine: rn > 1 with reason and proposed action.
  15. Q40Reconcile dedup outputs across two engine variants (ROW_NUMBER vs DISTINCT ON).
  16. Q41Multi-canonical engine: try multiple canonical keys, score similarity.
  17. Q42Sister-row detection: (city, LOWER(TRIM(name))) for stores.
  18. Q43Employee email dedup across stores; preserve primary store.
  19. Q44Dedup with weighted keep rule (e.g. score = orders*2 + reviews).
  20. Q45Cohort-safe dedup (Topic 27 prep): one signup per email forever.
  21. Q46Round-trip safety: assert dedup_count + losers = total.
  22. Q47Idempotent dedup query (rerun gives same result).
  23. Q48Plan inspection (Topic 19) on the customer dedup; index design (Topic 20).
  24. Q49Cleansed view (Topic 25) for the customer engine - SELECT-only here.
  25. Q50JSON quarantine (Topic 24) payload of loser rows with dup-group id.

ANOMALY ENGINES

  1. Q51Region-baselined order-value anomaly engine (Topic 22 P95/P99).
  2. Q52IQR-fence outlier engine per region (Topic 22).
  3. Q53Period-over-period drift engine: revenue change > 50% MoM per region (Topic 23).
  4. Q54Price-change engine: > 50% jump in record_changes (joined to products).
  5. Q55Salary-change engine: > 30% jump in record_changes.
  6. Q56SLA-breach engine: resolution > priority P95 (Topic 22).
  7. Q57Delivery-anomaly engine: per region delivery_days > P95 (Topic 22+23).
  8. Q58Inventory-spike engine: snapshot qty > 5x warehouse median.
  9. Q59Ad-spend anomaly engine: daily spend > platform P95.
  10. Q60Review-rating anomaly: rating > 2 stddev from product mean (concept).
  11. Q61Session-length anomaly per device (Topic 22 NTILE 99).
  12. Q62Order-without-payment anti-join (Topic 9) over a period.
  13. Q63Shipment timeline anomaly (shipped after delivered).
  14. Q64Ticket-before-registration anomaly (impossible per business rule).
  15. Q65Multi-rule anomaly engine: union all rule results with rule_id + severity.
  16. Q66Anomaly trend (Topic 23): % rows failing each rule per month.
  17. Q67Per-region anomaly pivot (Topic 21) of rule-fail counts.
  18. Q68Top-K anomalies per region by severity.
  19. Q69JSON anomaly payload (Topic 24) for downstream alerting.
  20. Q70Suppress repeat anomalies: dedup alerts per entity per day.
  21. Q71Anomaly burn rate: rolling-7-day rate (Topic 23 + window).
  22. Q72Tier-aware anomaly: Gold/Platinum exempt from low-spend flags.
  23. Q73Cleansed-base anomaly: run engine over cleansed view (Topic 25).
  24. Q74Plan inspection (Topic 19) on the heaviest anomaly query; index hints (Topic 20).
  25. Q75SLA: every rule produces a known-good empty-result on clean data (audit-pass).

RECONCILIATION & DQ DELIVERY

  1. Q76Reconcile order_items SUM vs orders.net_total per order; mismatch report.
  2. Q77Reconcile finance.payments vs sales.payments per order.
  3. Q78Reconcile shipments vs orders (every Delivered has a shipment).
  4. Q79Reconcile returns vs orders + refunds: refund_amount <= net_total.
  5. Q80Reconcile inventory delta vs sales movement per warehouse (concept).
  6. Q81Reconcile attendance vs pay_slip months (Topic 23 join).
  7. Q82Reconcile customers vs addresses (every customer has >=1 address).
  8. Q83Reconcile customers vs orders consistency (registration_date <= first order).
  9. Q84Reconcile reviews vs orders consistency (review_date >= order_date).
  10. Q85Reconcile tickets vs customers consistency (ticket_date >= registration).
  11. Q86Per region reconciliation scorecard (Topic 21 pivot).
  12. Q87Per month reconciliation drift (Topic 23).
  13. Q88Cleansed customer MV (Topic 25) + dedup contract.
  14. Q89Cleansed product MV (Topic 25) + canonical-brand contract.
  15. Q90Quarantine MV: row + rule_id + first_seen for review (Topic 25).
  16. Q91JSON DQ-scorecard export (Topic 24) for the BI layer.
  17. Q92Cohort-readiness check (Topic 27 prep): customers usable for cohort analysis.
  18. Q93RFM-readiness check (Topic 27 prep): clean recency/frequency/monetary inputs.
  19. Q94Funnel-readiness check (Topic 27 prep): web_events sessions intact.
  20. Q95Anomaly digest export to JSON (Topic 24) for alerting.
  21. Q96Multi-rule scorecard with weights and overall DQ score per table.
  22. Q97Cleansed analytics layer (Topic 25): orders/customers/products as cleaned views.
  23. Q98Repeatable cleansing script (deterministic, idempotent SELECTs).
  24. Q99Plan-checked DQ engine (Topic 19) over 150k orders - index check (Topic 20).
  25. Q100Capstone: a multi-rule DQ scorecard (per table, per region, per month) + quarantine SELECTs + JSON export, all running off the cleansed view layer.

Production scenarios, optimisation

CONCEPTUAL

  1. Q1Architect a DQ platform: rule registry -> engines -> scorecards -> quarantine -> exports.
  2. Q2Multi-canonical-key strategy: try keys in priority; merge candidate groups.
  3. Q3Weighted keep-rules: composing scores (orders, recency, completeness).
  4. Q4Idempotent / deterministic dedup contract under reruns.
  5. Q5Anomaly severity scoring: distance from baseline x business weight.
  6. Q6Reconciliation as contracts between fact tables (orders<->payments<->...).
  7. Q7DQ SLAs: % rows passing per rule, freshness, time-to-detect, time-to-fix.
  8. Q8Quarantine lifecycle: detect -> triage -> fix -> reconcile -> close.
  9. Q9Cleansed semantic layer (Topic 25) as the contract surface for downstream.
  10. Q10JSON export contract (Topic 24) for alerting and BI consumers.
  11. Q11False-positive control via per-group baselines (Topic 22) and seasonality (Topic 23).
  12. Q12Drift detection: rule pass-rate trend (Topic 23 + window 16-18).
  13. Q13Multi-tenant DQ: per-region baselines and isolation.
  14. Q14Dedup performance: indexes (Topic 20), sort vs hash plans (Topic 19).
  15. Q15Audit trail: capture before/after for every cleansing transform.
  16. Q16Replay-safe pipelines: cleanse -> reconcile -> publish; rerunnable.
  17. Q17Documentation as data: rule owner, severity, runbook in MV comments.
  18. Q18Versioning rules and cleansing transforms; deprecation window.
  19. Q19Data lineage: which downstream view depends on which cleansed source.
  20. Q20Topic 27 readiness: cohort/RFM/funnel demand pristine inputs - define checks.
  21. Q21JSON quarantine envelope (Topic 24): {rule_id, row, reason, severity, ts}.
  22. Q22Anti-pattern: implicit COALESCE that hides DQ issues.
  23. Q23Anti-pattern: dedup without a tie-breaker (non-determinism).
  24. Q24Anti-pattern: thresholds without baselines (false positives).
  25. Q25Sunset criteria for a rule (zero hits over N periods).

DEDUP PLATFORMS

  1. Q26Customer dedup platform: canonical(email,phone) primary + name fallback + score-based keep.
  2. Q27Product dedup platform: canonical(brand,name) + supplier scoring + revenue tiebreak.
  3. Q28Supplier dedup platform: canonical(name) + last-active fallback + multi-canonical merge.
  4. Q29Reviews dedup platform: per (customer, product) keep latest + audit losers.
  5. Q30Order dedup platform: per (cust, day, amount) detect double-submit + correlate payments.
  6. Q31Session dedup platform: collapse near-duplicate page_views in 2s window.
  7. Q32Address dedup platform: canonical(line1, city, pincode) + most-recent activity.
  8. Q33Ticket dedup platform: per (customer, subject, day) keep first + audit reopens.
  9. Q34Pay-slip dedup platform: per (employee, period) keep one + reconcile to attendance.
  10. Q35Inventory snapshot dedup platform: composite PK + most-recent updated_at concept.
  11. Q36Brand canonical-name platform: LOWER+TRIM+collapse + cross-supplier reconcile.
  12. Q37Loser-row audit log: rule_id + reason + suggested action.
  13. Q38Round-trip safety: kept + losers = total per group; assertion query.
  14. Q39Multi-canonical merge: agglomerate candidate groups across two keys.
  15. Q40Score-based keep with explainability column (why this row won).
  16. Q41Plan inspection (Topic 19) on platform queries; covering indexes (Topic 20).
  17. Q42CONCURRENTLY-refresh cleansed MV (Topic 25) backed by the dedup platform.
  18. Q43Versioned cleansed MV (vN) for safe rollout.
  19. Q44JSON quarantine payload export (Topic 24): {row, dup_group_id, kept_row_id, reasons[]}.
  20. Q45Soft-merge contract: keep id, but tag duplicates for downstream join steering.
  21. Q46Cross-table propagation: if a customer is deduped, orders carry the kept_id.
  22. Q47Cohort-safe dedup (Topic 27 prep): cohort uses canonical first signup.
  23. Q48Idempotent platform run: rerun gives identical row set; test it.
  24. Q49Per-region dedup KPIs: % rows kept, % merged, top reasons.
  25. Q50End-to-end dedup pipeline: detect -> audit log -> cleansed MV -> JSON export.

ANOMALY + RECONCILIATION ENGINES

  1. Q51Anomaly engine: per region per metric P50/P95/P99 baselines (Topic 22) + severity.
  2. Q52PoP drift engine: revenue MoM/YoY > threshold (Topic 23 + window).
  3. Q53Price-change engine: > 50% jumps + actor + table + before/after.
  4. Q54Salary-change engine: > 30% jumps + dept context + before/after.
  5. Q55SLA-breach engine: resolution > priority P95 + breach count + trend.
  6. Q56Delivery-anomaly engine: per region delivery_days > P95, week-over-week.
  7. Q57Inventory-spike engine: > 5x warehouse median + rolling check.
  8. Q58Ad-spend anomaly engine: daily > platform P95 with rolling baseline.
  9. Q59Review-rating outlier engine: rating > 2sigma from product mean.
  10. Q60Session anomaly engine: per device NTILE-99 (Topic 16).
  11. Q61Multi-rule anomaly engine: rule registry table (SELECT-only) + union per-rule outputs.
  12. Q62Severity scoring engine: distance from baseline x business weight.
  13. Q63Anomaly burn-rate engine: rolling-7d rate (Topic 23 + window).
  14. Q64Reconciliation engine: order_items SUM = orders.net_total per order.
  15. Q65Reconciliation engine: finance.payments = sales.payments per order.
  16. Q66Reconciliation engine: shipments coverage for Delivered orders.
  17. Q67Reconciliation engine: returns refund_amount <= net_total per order.
  18. Q68Reconciliation engine: attendance <-> pay_slip month parity.
  19. Q69Reconciliation engine: addresses <-> customers parity.
  20. Q70Per-region scorecard (Topic 21 pivot) of anomaly + reconciliation counts.
  21. Q71Trend dashboard (Topic 23): pass-rate per rule per month with MoM% (window).
  22. Q72JSON anomaly digest (Topic 24) export for alerting.
  23. Q73JSON reconciliation report (Topic 24) export.
  24. Q74Suppress repeats: dedup alerts per entity per day (Topic 22 DISTINCT ON).
  25. Q75Plan-checked anomaly engine (Topic 19) + index design (Topic 20).

PRODUCTION DQ DELIVERY

  1. Q76Semantic cleansed layer (Topic 25): v_core_customers, v_core_products, v_core_orders.
  2. Q77mv_core_customers + UNIQUE INDEX + refresh cadence comment (Topic 25).
  3. Q78mv_core_products + UNIQUE INDEX + canonical-brand contract.
  4. Q79mv_core_orders + secondary indexes for downstream metrics (Topic 20).
  5. Q80mv_dq_scorecard per table/rule/month + UNIQUE INDEX (table, rule_id, month).
  6. Q81mv_quarantine per rule + UNIQUE INDEX (rule_id, row_id) (Topic 25).
  7. Q82JSON DQ scorecard export (Topic 24) consumed by BI.
  8. Q83JSON quarantine export (Topic 24) consumed by ops.
  9. Q84Anomaly-digest MV (Topic 25): per region top-K anomalies with severity.
  10. Q85Reconciliation-status MV: latest pass/fail per contract.
  11. Q86Cohort-readiness MV (Topic 27 prep): customers usable for cohort analysis.
  12. Q87RFM-readiness MV (Topic 27 prep): clean recency/frequency/monetary inputs.
  13. Q88Funnel-readiness MV (Topic 27 prep): web_events sessions intact + customer linkage.
  14. Q89DQ trend MV (Topic 23): pass-rate per rule per month.
  15. Q90Versioned cleansed MV rollout v2; deprecation script.
  16. Q91Drift alerting MV: rules whose pass-rate fell > X% WoW (Topic 23).
  17. Q92Per region DQ scorecard MV (Topic 21 pivot).
  18. Q93Plan-check (Topic 19) the heaviest DQ query; index strategy (Topic 20).
  19. Q94Refresh-DAG for the DQ layer: dedup -> cleansed -> anomaly -> scorecard -> JSON.
  20. Q95Reconciliation alerts JSON (Topic 24) with severity + runbook link.
  21. Q96Multi-tenant per-region cleansed views (concept).
  22. Q97Documentation: per MV - owner, rules, refresh, dependents (in COMMENT).
  23. Q98Topic 27 hand-off: list the cleansed inputs cohort/RFM/funnel will consume.
  24. Q99End-to-end platform: dedup -> cleansed -> anomalies -> reconciliation -> scorecard -> exports.
  25. Q100Capstone: production DQ platform - rule registry, dedup engine, anomaly engine, reconciliation engine, cleansed MV layer, scorecard MV, JSON exports - with refresh DAG documented.