# Broken Promises Between Tables > Every foreign key is a pinky-swear. Count the ones that got broken. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem The data-quality pipeline runs a battery of validation rules over our warehouse tables, logging each run in dq_checks. A downstream consumer just choked on dangling references, so the on-call DE wants to know how bad the referential-integrity situation is. Count how many data quality checks whose rule is referential integrity actually failed (passed = 0). Note that rule names are not stored consistently, so match the rule case-insensitively. Return a single column fail_count. ## Worked solution and explanation ### Why this problem exists in real interviews The query is one line. The probe is whether you treat `LOWER(rule) LIKE '%referential%'` and `passed = 0` as obvious or as ambiguous. They want to see if you pause to ask what counts as a referential-integrity rule, why case-insensitive matching is needed when rule names aren't stored consistently, whether `passed` is a boolean or tri-state, and whether the downstream consumer cares about a window. A staff DE forces those questions before writing. --- ### Break down the requirements #### Step 1: Pin the rule taxonomy Ask out loud: is `rule` a free-text label or a controlled vocabulary? The prompt says rule names are not stored consistently, so you match `%referential%` case-insensitively with `LOWER(rule)`. Confirm there's no `rule_type` enum you should be using instead, and that no other rule family happens to contain the substring 'referential'. #### Step 2: Pin the failure flag `passed` is on `dq_checks`. Treat 0 as fail and 1 as pass, but ask if NULL means skipped or errored. `passed = 0` filters NULLs out by three-valued logic; that's correct here, but say so. #### Step 3: Pin the window The consumer choked on dangling references now, not all-time. Ask whether they want a `run_at` window (last 24h, current run batch). Write the unbounded version first, then add `AND run_at >= ?` once they answer. #### Step 4: Count, don't list Single scalar with `COUNT(*)` and an alias. No GROUP BY, no DISTINCT on `check_id` unless they tell you the same referential check can fail twice in scope. --- ### The solution **REFERENTIAL INTEGRITY FAILURE COUNT** ```sql SELECT COUNT(*) AS fail_count FROM dq_checks WHERE LOWER(rule) LIKE '%referential%' AND passed = 0 ``` > **Cost Analysis** > > 500k rows with a leading-wildcard `LIKE` over `LOWER(rule)` defeats any btree on `rule`. Expect a full scan, made worse by the per-row `LOWER()` call. If this runs hourly, push for a normalized `rule_type` column or an expression index on `LOWER(rule)` with trigram support. For a one-off audit, scan cost is fine. > **Interviewers Watch For** > > Before typing, ask: 'Rule names aren't stored consistently, so do I need case-insensitive matching?' and 'Is the consumer asking about a specific run window, or lifetime?' and 'Does `passed` have a NULL state for skipped checks?' Skipping these and writing the one-liner immediately reads as junior, even when the SQL is right. > **Common Pitfall** > > Matching `rule LIKE '%referential%'` without `LOWER()` and missing rows stored as 'Referential' or 'REFERENTIAL'. The prompt warns rule names are inconsistent, so fold case on both sides. Equally, writing `passed != 1` instead of `passed = 0`: under three-valued logic NULL `passed` rows get dropped either way, but `!=` invites a debate about whether NULL means failure. Use the positive predicate so the semantics are explicit. --- ### COMMON FOLLOW-UP QUESTIONS ## Common follow-up questions - How would you break this out by `tbl_name` to show which tables fail referential-integrity checks most often? _(Probes whether you reach for `GROUP BY tbl_name` and add `ORDER BY fail_count DESC LIMIT N`.)_ - What if the same `check_id` can produce multiple rows per day and you only want one referential failure per check per day? _(Probes deduplication via `COUNT(DISTINCT check_id)` or a window-based pick on `run_at`.)_ - How would you compute a failure rate instead of a raw count? _(Probes conditional aggregation: `SUM(CASE WHEN passed = 0 THEN 1 ELSE 0 END) * 1.0 / COUNT(*)` scoped to referential rules via `LOWER(rule) LIKE '%referential%'`.)_ - If `severity` exists, would you weight failures by it? _(Probes whether you push back and ask the consumer what 'how bad' means when not all dangling-reference failures are equal.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/broken_promises_between_tables) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.