Data Cleaning and Quality Validation in SQL Questions

Handle NULL values, duplicates, and data type issues within queries. Implement data validation checks (row counts, value distributions, date ranges). Practice identifying and documenting data quality issues that impact analysis reliability.

EasyTechnical

81 practiced

You receive a table of user emails with inconsistent casing and leading/trailing whitespace. Given:

contacts(id INT, raw_email TEXT)

Write a SQL SELECT that returns cleaned_email (trimmed and lowercased) and flags rows where the cleaned_email does not match a standard email regex. Use PostgreSQL or standard SQL functions. Also describe whether storing cleaned_email in the warehouse is appropriate vs. cleaning at query-time.

Sample Answer

Approach: normalize emails by trimming whitespace and lowercasing, then validate with a standard regex. In PostgreSQL use trim() and lower(); flag invalids using NOT (cleaned_email ~ regex) and handle NULLs/empty strings.

sql

SELECT
  id,
  lower(trim(both FROM raw_email)) AS cleaned_email,
  CASE
    WHEN raw_email IS NULL OR trim(both FROM raw_email) = '' THEN true  -- treat blank as invalid
    WHEN lower(trim(both FROM raw_email)) ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN false
    ELSE true
  END AS is_invalid_email
FROM contacts;

Key points:- trim(both FROM raw_email) removes leading/trailing whitespace; lower(...) normalizes casing.- Regex checks a common email pattern (covers most valid addresses but not full RFC 5322 complexity).- We mark NULLs/empty strings as invalid.

Edge cases:- Internationalized emails (Unicode) or very rare valid RFC formats (quotes, comments) may be rejected by this simple regex.- Consider normalizing unicode (NFC) if input can contain composed characters.

Store vs. clean at query-time:- Store cleaned_email in the warehouse when you frequently query/join on email, to improve performance and ensure consistency; enforce cleaning via ETL with validation and provenance (original raw_email kept).- Clean at query-time when storage simplicity/preservation of raw input is required or validations evolve often. Best practice: persist cleaned_email plus a validation flag from ETL so downstream analysts get consistent, fast results while raw data is retained for audits.

HardSystem Design

79 practiced

Design an alerting and triage workflow for data quality incidents that is resilient and minimizes on-call noise. Provide a schema for a DQ_incidents table capturing (incident_id, check_name, severity, detected_at, status, assigned_to, sample_rows_link) and write a sample SQL that, given a failing check, selects a small representative sample of offending rows for immediate inspection (include columns to help debug: primary key, ingestion_batch_id, minimal context).

Sample Answer

I would design a resilient, low-noise alerting + triage workflow with these goals: surface only actionable incidents, provide quick context for on-call, enable automated remediation/roll-back, and fast escalation when needed.

High-level workflow- Detection: scheduled/stream DQ jobs emit events (check_name, tags, row_count, sample link).- Dedup & aggregation: events in short window (e.g., 15m) for same check+source are grouped; suppression if below severity thresholds.- Prioritization: assign severity (P0–P3) based on business impact rules; low severity batched into daily digest.- Routing & assignment: auto-assign to owning team via ownership table; if unowned, route to data platform on-call.- Alerting: P0–P1 to real-time on-call (PagerDuty/Slack), P2 batched + Slack, P3 in digest email.- Triage automation: enriched alert includes sample_rows_link, runbook link, recent metric deltas, query to fetch failure sample; quick “ack & snooze” and “create incident” actions.- Escalation & remediation: automated retries, rewind ingestion, or rollback based on runbook; escalate if no acknowledgement in SLA.- Observability & feedback: incident metrics, false-positive tracking, periodic tuning of thresholds.

Schema for DQ_incidents table- incident_id UUID PK- check_name VARCHAR- data_source VARCHAR- severity ENUM('P0','P1','P2','P3')- detected_at TIMESTAMP- status ENUM('open','acknowledged','triaged','resolved','suppressed')- assigned_to VARCHAR (team or user)- sample_rows_link TEXT- aggregated_count INT- last_notified_at TIMESTAMP- runbook_link TEXT- created_by VARCHAR- updated_at TIMESTAMP

Sample SQL: given a failing check, select a concise representative sample of offending rows (include primary key, ingestion_batch_id, small context columns). This uses reservoir sampling for fairness and limits to 20 rows.

sql

WITH failing_rows AS (
  SELECT
    pk,                         -- primary key
    ingestion_batch_id,
    created_at,
    status,
    important_col_1,
    important_col_2,
    ROW_NUMBER() OVER (PARTITION BY ingestion_batch_id ORDER BY created_at DESC) AS rn
  FROM analytics.events
  WHERE <dq_check_predicate>        -- e.g., WHERE email IS NULL OR amount < 0
    AND detected_at >= now() - interval '1 hour'
),
sample_per_batch AS (
  -- take up to 5 recent per batch to preserve batch context
  SELECT * FROM failing_rows WHERE rn <= 5
),
final_sample AS (
  -- randomize and cap to 20 rows for immediate inspection
  SELECT pk, ingestion_batch_id, created_at, important_col_1, important_col_2
  FROM sample_per_batch
  ORDER BY random()
  LIMIT 20
)
SELECT * FROM final_sample;

Why this helps- Keeps alerts focused (aggregation, severity rules) and reduces pager noise (batching low-severity).- Incident table stores ownership, lifecycle and links for quick action.- SQL returns small, contextual, representative rows (PK + batch + minimal context) enabling rapid diagnosis during on-call triage.

MediumTechnical

92 practiced

Explain how to implement row-level data lineage using only SQL constructs: capture source_file, source_row_id, ingestion_batch_id in staging, propagate these fields through transformations, and provide a sample SQL pattern that joins a transformed analytics row back to its originating source rows for root cause analysis. Use example table names: staging.raw_events and analytics.daily_events.

Sample Answer

Approach (high level)- Capture provenance at ingestion: every raw row in staging.raw_events includes source_file, source_row_id, ingestion_batch_id.- Propagate lineage through transformations by either (A) carrying an aggregated lineage column (ARRAY/JSON of source identifiers) in the analytics table, or (B) writing a separate many-to-many bridge table that maps analytics row keys to source rows. Option (B) is more query-friendly and scales better for root-cause joins.

Example schema (staging)staging.raw_events(source_file TEXT, source_row_id TEXT, ingestion_batch_id TEXT, event_time TIMESTAMP, user_id INT, amount NUMERIC)

Pattern A — single-table with JSON lineage (compact, simple)

sql

INSERT INTO analytics.daily_events (day, user_id, total_amount, lineage_json)
SELECT
  DATE(event_time) AS day,
  user_id,
  SUM(amount) AS total_amount,
  TO_JSON(ARRAY_AGG(JSON_BUILD_OBJECT(
     'source_file', source_file,
     'source_row_id', source_row_id,
     'ingestion_batch_id', ingestion_batch_id
  ))) AS lineage_json
FROM staging.raw_events
WHERE event_time BETWEEN @start AND @end
GROUP BY DATE(event_time), user_id;

- Pros: self-contained. Cons: lineage can be large and harder to index/query.

Pattern B — bridge table (recommended)Step 1: create analytics row and get its id (assume surrogate id daily_event_id)

sql

-- Create analytics rows
INSERT INTO analytics.daily_events (day, user_id, total_amount)
SELECT DATE(event_time) AS day, user_id, SUM(amount)
FROM staging.raw_events
WHERE event_time BETWEEN @start AND @end
GROUP BY DATE(event_time), user_id
RETURNING daily_event_id, day, user_id;

Step 2: populate bridge table analytics.daily_event_lineage(daily_event_id, source_file, source_row_id, ingestion_batch_id)

sql

-- Link each analytics row to its contributing source rows
INSERT INTO analytics.daily_event_lineage (daily_event_id, source_file, source_row_id, ingestion_batch_id)
SELECT de.daily_event_id, r.source_file, r.source_row_id, r.ingestion_batch_id
FROM staging.raw_events r
JOIN analytics.daily_events de
  ON de.day = DATE(r.event_time)
  AND de.user_id = r.user_id
WHERE r.event_time BETWEEN @start AND @end;

Root-cause join pattern (find source rows for a given daily_events row)

sql

SELECT r.*
FROM analytics.daily_events de
JOIN analytics.daily_event_lineage l ON l.daily_event_id = de.daily_event_id
JOIN staging.raw_events r
  ON r.source_file = l.source_file
  AND r.source_row_id = l.source_row_id
  AND r.ingestion_batch_id = l.ingestion_batch_id
WHERE de.daily_event_id = 1234;

Key reasoning and best practices- Always capture source_file, source_row_id, ingestion_batch_id in staging at ingestion time.- Use a bridge table for efficient joins, indexing (index on daily_event_id and (source_file, source_row_id, ingestion_batch_id)).- If transformations aggregate many rows, avoid duplicating large JSON blobs; use normalized bridge.- Keep ingestion_batch_id to support time-travel or reprocessing scenarios; combine with source_row_id to uniquely identify a raw row.- Consider deduplication in staging before lineage capture to avoid noisy links.

MediumTechnical

81 practiced

You discovered that numeric amounts are stored as text and include formatting like '$1,234.56' and '(1,234.56)' for negatives. Write PostgreSQL SQL to clean and cast amount_text into a numeric column 'amount', correctly handling commas, currency symbols, and negative values in parentheses. Show how you'd surface rows that still fail casting after cleaning.

Sample Answer

Approach: remove currency symbols/commas/spaces, convert parentheses to a leading minus, then validate the cleaned string with a regex before casting. Use regexp_replace/translate for cleaning; surface rows that still don’t match numeric pattern so you can review them.

Example SELECT (safe, non-destructive):

sql

SELECT
  id,
  amount_text,
  -- convert "(1,234.56)" -> "-1234.56", remove $ and commas/spaces
  regexp_replace(
    translate(
      regexp_replace(amount_text, '^\s*\((.*)\)\s*$', '\1'),  -- strip outer parentheses content
      '$, ', '')                                              -- remove $, comma, space
    , '^([0-9]+(\.[0-9]+)?)$', '\1')                           -- placeholder for clarity
    AS cleaned_candidate,
  CASE
    WHEN regexp_replace(amount_text, '^\s*\((.*)\)\s*$', '-\1') ~ '^[+-]?[0-9]+(\.[0-9]+)?$'
      THEN (regexp_replace(amount_text, '^\s*\((.*)\)\s*$', '-\1')::numeric)
    ELSE NULL
  END AS amount_cast
FROM my_table;

More concise and robust cleaning + casting:

sql

SELECT
  id,
  amount_text,
  -- 1) move parentheses to leading '-'  2) remove $, commas, spaces  3) collapse multiple +/-
  cleaned,
  CASE WHEN cleaned ~ '^[+-]?[0-9]+(\.[0-9]+)?$' THEN cleaned::numeric ELSE NULL END AS amount
FROM (
  SELECT
    id,
    amount_text,
    trim(
      translate(
        regexp_replace(amount_text, '^\s*\((.*)\)\s*$', '-\1'),  -- (x) -> -x
        '$, ', '')                                              -- remove $, commas, spaces
    ) AS cleaned
  FROM my_table
) t;

To update a new numeric column safely and log failures:

sql

-- add column
ALTER TABLE my_table ADD COLUMN amount numeric;

-- populate only valid rows
UPDATE my_table
SET amount = cleaned::numeric
FROM (
  SELECT id, trim(translate(regexp_replace(amount_text, '^\s*\((.*)\)\s*$', '-\1'), '$, ', '')) AS cleaned
  FROM my_table
) c
WHERE my_table.id = c.id
  AND c.cleaned ~ '^[+-]?[0-9]+(\.[0-9]+)?$';

-- surface rows that failed (need manual review)
SELECT id, amount_text, trimmed AS cleaned
FROM (
  SELECT id, amount_text,
         trim(translate(regexp_replace(amount_text, '^\s*\((.*)\)\s*$', '-\1'), '$, ', '')) AS trimmed
  FROM my_table
) x
WHERE NOT (trimmed ~ '^[+-]?[0-9]+(\.[0-9]+)?$') OR trimmed = '';

Key points:- regexp_replace handles parentheses -> negative.- translate removes $, commas, spaces efficiently.- Validate with regex before casting to avoid exceptions.- Surface failures for manual inspection (currency codes, text like "N/A", ranges, multiple signs).

MediumTechnical

76 practiced

Create a single SQL query that runs multiple data quality checks for an orders and products dataset and emits a standardized results table with columns: check_name, expected, observed, status. Checks should include: uniqueness of order_id in orders, product_id in products exists for all order items (referential integrity), and null rate for order_date. Use these schemas:

orders(order_id STRING, order_date DATE)
order_items(order_item_id STRING, order_id STRING, product_id STRING)
products(product_id STRING)

Return a single result set with the three checks.

Sample Answer

Approach: compute the three metrics in CTEs (duplicates in orders, missing product_ids in order_items, null rate of order_date) and return a standardized result set with check_name, expected, observed, and status (PASS/FAIL).

sql

WITH
-- count duplicate order_ids (0 expected)
order_dup AS (
  SELECT
    COUNT(*) - COUNT(DISTINCT order_id) AS duplicates
  FROM orders
),
-- count order_items whose product_id doesn't exist in products (0 expected)
missing_products AS (
  SELECT
    SUM(CASE WHEN p.product_id IS NULL THEN 1 ELSE 0 END) AS missing_count
  FROM order_items oi
  LEFT JOIN products p ON oi.product_id = p.product_id
),
-- null rate for order_date as percentage (expected 0.0)
order_date_nulls AS (
  SELECT
    100.0 * SUM(CASE WHEN order_date IS NULL THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0) AS null_rate_pct
  FROM orders
)
SELECT
  'orders.order_id uniqueness' AS check_name,
  '0 duplicates' AS expected,
  CONCAT(CAST(od.duplicates AS VARCHAR), ' duplicates') AS observed,
  CASE WHEN od.duplicates = 0 THEN 'PASS' ELSE 'FAIL' END AS status
FROM order_dup od

UNION ALL

SELECT
  'referential: order_items.product_id -> products.product_id' AS check_name,
  '0 missing references' AS expected,
  CONCAT(CAST(mp.missing_count AS VARCHAR), ' missing') AS observed,
  CASE WHEN mp.missing_count = 0 THEN 'PASS' ELSE 'FAIL' END AS status
FROM missing_products mp

UNION ALL

SELECT
  'orders.order_date null rate' AS check_name,
  '0.0%' AS expected,
  CONCAT(ROUND(orate.null_rate_pct,3), '%') AS observed,
  CASE WHEN COALESCE(orate.null_rate_pct,0.0) = 0.0 THEN 'PASS' ELSE 'FAIL' END AS status
FROM order_date_nulls orate;

Key points:- Uses CTEs for clarity and reuse.- Referential check uses LEFT JOIN to identify missing products.- Null rate uses NULLIF to avoid division-by-zero.Edge cases:- Empty orders table handled by NULLIF (null_rate_pct becomes NULL → treated as 0 for status).- Large tables: ensure appropriate indexes on join keys for performance.Alternative: return numeric observed fields instead of formatted strings to enable programmatic checks.

Unlock Full Question Bank

Get access to hundreds of Data Cleaning and Quality Validation in SQL interview questions and detailed answers.

Join thousands of developers preparing for their dream job.