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

71 practiced

Describe with SQL DDL how to add CHECK constraints to a 'payments' table to ensure amount >= 0 and currency IN ('USD','EUR'). Explain how to add those constraints to a table that already has violating rows and how to deploy safely in production databases (PostgreSQL).

Sample Answer

You can enforce the rules with CHECK constraints. Example DDL to create the table with constraints:

CREATE TABLE payments ( id serial PRIMARY KEY, amount numeric(12,2) NOT NULL, currency text NOT NULL, CHECK (amount >= 0), CHECK (currency IN ('USD','EUR')));

Adding to an existing table (and handling violating rows)1. Find violations: SELECT * FROM payments WHERE amount < 0 OR currency NOT IN ('USD','EUR') LIMIT 100;2. Decide remediation: fix values, delete, or move to a quarantine table. Example fixes: UPDATE payments SET amount = 0 WHERE amount < 0; UPDATE payments SET currency = 'USD' WHERE currency IS NULL; -- only if business-valid OR CREATE TABLE payments_quarantine AS SELECT * FROM payments WHERE amount < 0 OR currency NOT IN ('USD','EUR');

Safe production deployment in PostgreSQL1. Add constraint without immediate validation to avoid a full table scan and long locks: ALTER TABLE payments ADD CONSTRAINT chk_amount_nonneg CHECK (amount >= 0) NOT VALID; ALTER TABLE payments ADD CONSTRAINT chk_currency_allowed CHECK (currency IN ('USD','EUR')) NOT VALID; (These are added quickly; they prevent new violating rows from being added only if you also add triggers or rely on application checks — NOT VALID does not enforce past rows.)2. Backfill/fix any remaining violations found in step 1.3. Validate constraints (this performs the scan and will fail if violations remain): ALTER TABLE payments VALIDATE CONSTRAINT chk_amount_nonneg; ALTER TABLE payments VALIDATE CONSTRAINT chk_currency_allowed; Run validation during a maintenance window or low-traffic time to reduce contention.

Notes / best practices- Use the NOT VALID + VALIDATE pattern to minimize downtime and lock duration.- Run the SELECT checks and remediate in a staged pipeline; for large tables, validate in batches or use parallelized scripts.- Consider adding a small trigger or using inserts/views to block new bad data until constraints are validated.- Test the whole flow in staging with a copy of production data before applying in production.

HardTechnical

67 practiced

Design SQL to compute a data quality scorecard per table and per column combining metrics: completeness (1 - null_pct), uniqueness (1 - dup_rate), accuracy (1 - error_rate), and timeliness (normalized freshness). Provide sample weightings and SQL that produces a composite score between 0 and 100.

Sample Answer

Approach: compute each metric per table+column as a 0..1 value, apply sample weights, produce composite score scaled 0..100. Timeliness (freshness) must be normalized (e.g., freshness_days mapped to 1 when <= target_days, to 0 when >= max_days, linear otherwise). Use baseline metadata views: table_row_counts (table_name, row_count), column_stats (table_name, column_name, null_count, distinct_count, sampled_error_count, last_updated_ts), and a parameters table for freshness targets.

Sample weights:- completeness: 30%- uniqueness: 25%- accuracy: 30%- timeliness: 15%

SQL (Postgres-ish):

sql

WITH params AS (
  SELECT 7 AS freshness_target_days, 30 AS freshness_max_days,
         0.30 AS w_completeness, 0.25 AS w_uniqueness, 0.30 AS w_accuracy, 0.15 AS w_timeliness
),
rows AS (
  SELECT table_name, row_count FROM table_row_counts
),
col AS (
  SELECT
    c.table_name,
    c.column_name,
    r.row_count,
    c.null_count,
    c.distinct_count,
    c.sampled_error_count,
    c.last_updated_ts
  FROM column_stats c
  JOIN rows r USING (table_name)
),
metrics AS (
  SELECT
    table_name,
    column_name,
    -- completeness: 1 - null_pct
    CASE WHEN row_count = 0 THEN 1.0
         ELSE GREATEST(0.0, 1.0 - (null_count::float / row_count)) END AS completeness,
    -- uniqueness: 1 - duplicate_rate = 1 - ((row_count - distinct)/row_count)
    CASE WHEN row_count = 0 THEN 1.0
         ELSE GREATEST(0.0, 1.0 - ((row_count - GREATEST(distinct_count,0))::float / row_count)) END AS uniqueness,
    -- accuracy: 1 - error_rate (sample-based)
    CASE WHEN row_count = 0 THEN 1.0
         ELSE GREATEST(0.0, 1.0 - (LEAST(sampled_error_count, row_count)::float / row_count)) END AS accuracy,
    -- timeliness: normalized freshness
    EXTRACT(EPOCH FROM (NOW() - last_updated_ts)) / 86400.0 AS freshness_days
  FROM col
),
timed AS (
  SELECT m.*,
    p.*,
    CASE
      WHEN freshness_days <= freshness_target_days THEN 1.0
      WHEN freshness_days >= freshness_max_days THEN 0.0
      ELSE 1.0 - ((freshness_days - freshness_target_days) / (freshness_max_days - freshness_target_days))
    END AS timeliness
  FROM metrics m CROSS JOIN params p
),
scored AS (
  SELECT
    table_name,
    column_name,
    completeness, uniqueness, accuracy, timeliness,
    (completeness * w_completeness
     + uniqueness * w_uniqueness
     + accuracy * w_accuracy
     + timeliness * w_timeliness) * 100.0 AS composite_score
  FROM timed
)
SELECT * FROM scored
ORDER BY table_name, column_name;

Key points:- All sub-scores clipped to [0,1]; empty tables treated as perfect (adjust if desired).- Timeliness normalization uses linear decay between target and max; can replace with exponential.- If distinct_count is unavailable, consider using approximate distinct (HyperLogLog) and adjust dedup logic.Edge cases:- sampled_error_count should be scaled if sampling fraction known.- For partitioned tables compute last_updated per partition or per column source.This produces a 0–100 composite score per table+column for data quality monitoring.

MediumTechnical

94 practiced

You must estimate the duplicate rate on a 100B-row partitioned 'clicks' table for keys (user_id, session_id) without scanning everything. Describe a statistically sound sampling strategy, write the SQL to obtain the sample, compute duplicate rate and 95% confidence interval, and explain how you'd stratify by partition/day.

Sample Answer

Approach (brief)- Use reproducible hash-based sampling (avoids full scan, evenly samples across partitions).- Estimate duplicate rate = (total_rows_in_sample - distinct_key_count_in_sample) / total_rows_in_sample.- Compute 95% CI for a proportion (duplicates treated as "success"): p̂ ± z*sqrt(p̂(1-p̂)/n). For stratified by day, sample per-day and combine with weighted average (post-stratification).

Step 1 — pilot sample to get p̂- Take small pilot (e.g., ~100k rows) to estimate p̂, then compute required n for desired margin m (e.g., 0.5%): n = z^2 * p*(1-p) / m^2 (use z=1.96)- If p unknown, use conservative p=0.5.

SQL — reproducible uniform sample (single global sample)

sql

-- Example: 0.01% sample via hash (adjust modulus to get desired n)
SELECT
  COUNT(*)           AS sample_rows,
  COUNT(DISTINCT user_id, session_id) AS sample_unique_keys
FROM clicks
WHERE mod(abs(farm_fingerprint(concat(user_id, "|", session_id))), 1000000) = 0;

Compute duplicate rate and 95% CI- sample_rows = n, duplicates = n - unique_keys, p̂ = duplicates / n- SE = sqrt(p̂ * (1 - p̂) / n)- 95% CI = p̂ ± 1.96 * SE

Example (numbers)- Suppose n = 100000, unique = 99,600 → duplicates = 400 → p̂ = 0.004- SE = sqrt(0.004*0.996/100000) ≈ 0.000199- 95% CI ≈ 0.004 ± 1.96*0.000199 → [0.00361, 0.00439] (0.361%–0.439%)

Stratified by partition/day (preferred)- Sample within each day partition proportionally (or with Neyman allocation if you know per-day variance).- SQL to get per-day sample (e.g., 0.1% per day):

sql

SELECT
  partition_date,
  COUNT(*) AS sample_rows,
  COUNT(DISTINCT user_id, session_id) AS sample_unique_keys
FROM clicks
WHERE mod(abs(farm_fingerprint(concat(user_id, "|", session_id))), 1000) = 0
GROUP BY partition_date;

- For each day i compute p̂i = (ri - ui)/ri, weight wi = Ni / Ntotal (Ni = total rows in day i; you can get Ni from table metadata or a fast per-partition row count).- Combined estimate p̂ = sum(wi * p̂i)- Variance ≈ sum(wi^2 * var(p̂i)) where var(p̂i) = p̂i(1-p̂i)/ni (ni = sampled rows in day i). 95% CI: p̂ ± 1.96*sqrt(variance).

Notes / best practices- Use hash-based sampling so queries hit partitions uniformly and are reproducible.- Ensure sample size per stratum >= ~30 for CLT validity; increase for low-variance strata.- If duplicate rate is very small, oversample (or use unequal allocation) to get enough duplicate observations.- Validate results with occasional larger samples or targeted scans on suspicious partitions.

MediumTechnical

76 practiced

The 'user_profiles' table has a 'gender' column with inconsistent values: 'M', 'Male', 'male', 'FEMALE', 'f', etc. Write SQL to produce a mapping of observed values to canonical values and a SELECT that normalizes gender using that mapping. Explain where you would store the mapping for maintainability.

Sample Answer

Approach: first enumerate distinct observed values and map them to canonical values (e.g., 'M'/'Male'/'male' → 'Male'; 'F'/'FEMALE'/'f' → 'Female'; unknown → 'Other' or NULL). Implement mapping as a stable lookup table and use a join to normalize values in SELECTs or in ETL.

SQL to produce observed values (quick audit):

sql

SELECT gender AS raw_value,
       COUNT(*) AS occurrences
FROM user_profiles
GROUP BY gender
ORDER BY occurrences DESC;

Create a maintained mapping table (recommended):

sql

CREATE TABLE gender_mapping (
  raw_value VARCHAR PRIMARY KEY,     -- values as observed, normalized (lowercased) recommended
  canonical_value VARCHAR NOT NULL,  -- 'Male','Female','Other',NULL etc.
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Example inserts:
INSERT INTO gender_mapping (raw_value, canonical_value) VALUES
('m','Male'), ('male','Male'), ('M','Male'),
('f','Female'), ('female','Female'), ('FEMALE','Female'),
('','Other'), (NULL,'Other');

SELECT that normalizes using the mapping (robust to case):

sql

SELECT up.*,
       COALESCE(gm.canonical_value, 'Other') AS gender_normalized
FROM user_profiles up
LEFT JOIN gender_mapping gm
  ON LOWER(COALESCE(up.gender, '')) = gm.raw_value;

(If mapping stores lowercased raw_value, compare to LOWER(up.gender).)

Maintainability recommendations:- Store mapping in a small canonical table in the data warehouse (single source of truth) so analysts/pipelines reuse it.- Track who/when changed (audit columns) and enforce FK or validation checks.- Expose the mapping via a managed config service or git-backed SQL migrations for review.- In pipelines (Spark/DBT), centralize normalization as a transformation step (UDF or dbt model) so changes propagate.- Monitor unknown raw_values by periodically querying LEFT JOIN WHERE gm.raw_value IS NULL to update mapping.

MediumTechnical

65 practiced

A frequently run analytic query repeatedly casts a varchar column 'user_id_str' to INT many times, causing CPU overhead. Propose a SQL rewrite and a long-term schema change to avoid repeated casting. Provide before-and-after SQL snippets and discuss trade-offs.

Sample Answer

Situation: A hot analytic query repeatedly does CAST(user_id_str AS INT) in joins/filters, causing CPU churn and preventing index usage. We can optimize immediately with a rewritten query and plan a schema change to avoid repeated casts.

Quick rewrite (short-term): cast once in a CTE and reuse it

sql

WITH users_cast AS (
  SELECT id, CAST(user_id_str AS INT) AS user_id_int, other_cols
  FROM users
)
SELECT a.*, b.*
FROM events e
JOIN users_cast u ON CAST(e.user_id_str AS INT) = u.user_id_int  -- avoid repeating on users side
JOIN users_cast u2 ON u2.id = e.some_ref;

Better: cast event side once too

sql

WITH events_cast AS (
  SELECT *, CAST(user_id_str AS INT) AS user_id_int FROM events
)
SELECT e.*, u.*
FROM events_cast e
JOIN users u ON e.user_id_int = CAST(u.user_id_str AS INT);

Optimal short-term: cast both sides once and join on ints:

sql

WITH users_cast AS (SELECT id, CAST(user_id_str AS INT) AS user_id_int FROM users),
     events_cast AS (SELECT *, CAST(user_id_str AS INT) AS user_id_int FROM events)
SELECT e.*, u.*
FROM events_cast e
JOIN users_cast u ON e.user_id_int = u.user_id_int;

Long-term schema changes:- Add a persisted/computed integer column user_id_int populated from user_id_str (or during ETL) and backfill existing rows.- Create indexes on user_id_int and use integer joins/filters.- Alternatively, create a materialized view normalizing user_id_int for hot queries.

Example ALTER (Postgres):

sql

ALTER TABLE users ADD COLUMN user_id_int bigint;
UPDATE users SET user_id_int = user_id_str::bigint WHERE user_id_str ~ '^\d+$';
ALTER TABLE users ALTER COLUMN user_id_int SET NOT NULL;
CREATE INDEX idx_users_user_id_int ON users(user_id_int);

Trade-offs:- Benefits: huge CPU savings, sargable predicates, smaller index sizes, faster joins and aggregations.- Cons: storage overhead for extra column and index; need ETL/ingest change to maintain user_id_int (risk if strings contain non-numeric values); migration/backfill cost and coordination.- Alternatives: computed/persisted column (automation) vs materialized view (lighter if only a few queries use it). If many tables use the string id, prefer changing ingest to store numeric id once.

Recommendation: implement the persisted integer column in upstream ETL to eliminate casts, backfill existing data, add indexes, and convert hot queries to use the integer columns.

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.