Data Exploration and Quality Assessment Questions

Investigate a dataset thoroughly before analysis or reporting by profiling its structure, contents, and reliability. Typical steps include examining row counts and data volume, inspecting column data types and sample values, validating date formats and ranges, and identifying missing values, duplicates, outliers, and impossible values. Understand schema and relationships between tables or files, check data freshness and latency, and characterize data completeness and coverage with simple metrics and queries. Document discovered issues, their likely causes and impacts on conclusions, and pragmatic workarounds or transformation strategies to mitigate risk. Use exploratory queries and summary statistics to quantify data quality, note limitations and assumptions, and allocate an appropriate portion of case study time to data assessment before proceeding to modeling or visualization.

HardTechnical

24 practiced

You have to deduplicate customer records that span multiple tables and systems with different identifiers (email, phone, external_id), some typos and missing fields. Explain a robust step-by-step de-duplication strategy suitable for production: include deterministic rules, fuzzy matching, scoring approach, threshold selection, and human-in-loop verification. Describe how you would measure precision and recall of your deduplication.

Sample Answer

Situation: We need production-ready de-duplication across systems where identifiers vary, contain typos, or are missing.

Step-by-step strategy:1. Ingest & canonicalize: standardize names (lowercase, strip punctuation), normalize phone (E.164), emails (lowercase, remove tags), and external_ids; capture provenance and timestamps.2. Deterministic rules (high-confidence merges): exact match on primary key combos — e.g., same external_id OR same normalized email AND same phone. These produce automatic merges (audit log + reversible).3. Rule-based normalization for common errors: handle transposed digits, domain typos (gamil→gmail), common nickname mapping (Bob→Robert).4. Fuzzy matching layer: compute pairwise similarity vectors — name (Jaro-Winkler), email local-part edit distance, phone numeric distance, address token similarity, and metadata (created_at proximity, same IP). Use blocking (by phone prefix, email domain, last name soundex) to limit pairs.5. Scoring model: weighted sum or logistic model producing confidence score between 0–1. Example: score = 0.4*email_sim + 0.3*phone_sim + 0.2*name_sim + 0.1*addr_sim. Train weights on labeled sample.6. Threshold selection: three zones — auto-merge (score ≥ 0.95), review-needed (0.6–0.95), keep separate (≤ 0.6). Calibrate thresholds via ROC/PR curves on holdout labeled data to meet business precision targets (e.g., >99% for auto-merges).7. Human-in-loop: provide reviewers with candidate pairs, side-by-side fields, provenance, and suggested merge action; track reviewer decisions to expand training labels and update model. Allow rollback and maintain merge lineage.8. Monitoring & pipelines: run nightly batch plus near-real-time incremental matching for new records. Record metrics and sample audits.

Measuring precision & recall:- Create a gold standard sample (stratified by systems, rare/common names). Compute: - Precision = TP / (TP + FP) where TP = correctly merged pairs, FP = incorrectly merged. - Recall = TP / (TP + FN) where FN = pairs that should have been merged but weren’t.- For entity-level (clusters), use pairwise F1 or cluster metrics (B-Cubed, pairwise precision/recall) and report precision/recall at each threshold zone. Monitor drift by periodic re-labeling and track reviewer override rates as proxy for false positives.

Key trade-offs: prioritize precision for auto-merges to avoid customer harm; use review band to improve recall. Continuously retrain, log decisions, and surface business-impacting merges for stakeholder sign-off.

EasyTechnical

25 practiced

You are given a PostgreSQL table `orders` with schema:

orders( order_id bigint PRIMARY KEY, user_id bigint, amount numeric(10,2), created_at timestamp, status varchar)

Write SQL queries (Postgres dialect) to perform a quick data profile for this table that returns: 1) total row count, 2) distinct count for each column, 3) null count for each column, and 4) five sample distinct values for the `status` column. Explain any performance considerations for running these queries on a 500M-row table.

Sample Answer

Approach: run a small set of aggregate queries (one to get totals/distincts/nulls in a single pass) and a small separate query to sample distinct status values. On 500M rows consider sampling or approximate methods rather than repeated full-table scans.

SQL (Postgres):

sql

-- 1) total rows, 2) distinct per column, 3) null counts per column (single pass)
SELECT
  COUNT(*) AS total_rows,
  COUNT(DISTINCT user_id) AS distinct_user_id,
  COUNT(DISTINCT amount) AS distinct_amount,
  COUNT(DISTINCT created_at) AS distinct_created_at,
  COUNT(DISTINCT status) AS distinct_status,
  SUM((user_id IS NULL)::int) AS null_user_id,
  SUM((amount IS NULL)::int) AS null_amount,
  SUM((created_at IS NULL)::int) AS null_created_at,
  SUM((status IS NULL)::int) AS null_status
FROM orders;

sql

-- 4) five sample distinct status values (most frequent first)
SELECT status
FROM orders
WHERE status IS NOT NULL
GROUP BY status
ORDER BY COUNT(*) DESC
LIMIT 5;

Performance considerations for 500M rows:- COUNT(*) and SUM(...) require a full table scan; COUNT(DISTINCT) is expensive and may require large memory and sorting/hashing. Expect long runtime and high I/O.- Options to reduce cost: - Use sampling for approximations: TABLESAMPLE SYSTEM (1) to get quick estimates. e.g. SELECT COUNT(*) FROM orders TABLESAMPLE SYSTEM (1); - Use extensions/algorithms for approximate distinct counts (e.g., postgresql-hll/hyperloglog or tdigests) to get much faster distinct estimates. - Run during off-peak and ensure ANALYZE has been run so planner/stats are current. - Increase work_mem if COUNT(DISTINCT) spills to disk; enable parallel query (Postgres will parallelize aggregates if available). - Maintain partial indexes/materialized views for high-value columns (e.g., status) if you profile often.- For exact one-off profiles, allow time for the full scan; for frequent profiling prefer approximate methods and incremental statistics.

MediumSystem Design

36 practiced

Propose an approach to compute and track data lineage and ownership that aids data quality debugging. Include what metadata to collect, how to store lineage (graph vs table), and how an analyst would use this information during root-cause analysis of a broken KPI.

Sample Answer

Requirements & goals:- Track end-to-end lineage (source → transformations → targets), owners, SLAs and quality metrics so analysts can quickly root-cause broken KPIs.- Low-latency queries for impact analysis and human-readable traces for debugging.

Metadata to collect:- Dataset/Table: name, platform, environment (prod/dev), schema snapshot (cols, types), row count, partitioning- Column-level: column name, semantic tag, data type, example values- Job/Transformation: job id, type (SQL/ETL/stream), code or SQL, parameters, schedule, version, run history (start/end, status)- Lineage edges: from (dataset.column), to (dataset.column), transformation id, timestamp- Ownership & governance: primary/secondary owner (user/team), contact, SLAs, sensitivity/classification- Quality metrics: freshness, null %, distinct %, anomaly flags, test results- Provenance: source system ids, extraction queries, commit hashes

Storage & model:- Use a hybrid: graph database (e.g., Neo4j, AWS Neptune) for lineage traversal (impact analysis, upstream/downstream), plus a relational store or metadata service for reporting, ACLs, and batch queries. Graph nodes: Dataset, Column, Job, Owner; edges: PRODUCES, CONSUMES, RUN_BY, OWNS.- Store time-versioned lineage (append-only) to debug historical KPI calculations; keep latest materialized views for fast lookups.

Ingestion & instrumentation:- Instrument ETL frameworks to emit lineage events (OpenLineage or custom): capture SQL AST to map source columns → target columns.- Capture job run metadata and quality metrics into a metadata pipeline (streaming into the graph + metrics DB).

Analyst workflow for RCA on a broken KPI:1. Alert shows KPI drop; open lineage UI from dashboard to the KPI dataset/table.2. Use graph view to expand upstream sources and transformations within N hops; filter by recent job failures or quality anomalies.3. Inspect recent job runs and quality metrics (e.g., sudden null% spike on source column X) and open transformation code for suspicious changes (recent commit).4. Identify owner(s) from node metadata and contact; meanwhile create a short-lived data snapshot to reproduce the KPI calculation.5. If transformation bug suspected, use column-level mappings to recompute KPI locally or sandbox a fix; track fix through job re-run and observe metric recovery.6. Document root cause in metadata (annotate nodes/edges) to prevent regressions.

Example queries analysts need:- Upstream sources for dataset D within 3 hops.- Jobs that wrote to column C in last 7 days with failed runs.- Latest quality metrics for all upstream columns feeding KPI.

Trade-offs:- Graph DB optimizes traversal but adds operational complexity; relational store simplifies compliance queries. Hybrid balances both.- Full column-level lineage requires parsing SQL/DSLs and may be approximate for opaque transforms (ML models, UDFs); annotate those as black-box with input/output fingerprints.

This approach gives analysts fast, actionable lineage + ownership + quality context to shorten MTTD/MTTR for KPI issues.

HardTechnical

23 practiced

You suspect an A/B test instrumentation bug that affected 10% of traffic for 3 days and biased conversion upwards. Describe how you would quantify the bias introduced into reported experiment results, propose methods to correct the analysis (reweighting, exclusion, model adjustment), and explain how you would communicate the uncertainty of corrected estimates to stakeholders.

Sample Answer

Clarifying assumptions: 10% of traffic for 3 days was mis-instrumented and biased conversion upward. I’d treat this as a contamination problem and proceed in three phases: quantify bias, correct estimates, and communicate uncertainty.

1) Quantify the bias- Reconstruct affected population: join instrumentation logs to experiment assignment by user_id, timestamp, and device to label each visit as affected/unaffected.- Estimate raw bias: compute conversion rate difference within each arm between affected vs unaffected periods: bias = CR_affected − CR_unaffected. Do this stratified by key covariates (country, device, new vs returning) to detect heterogeneity.- Use bootstrapping or Bayesian posterior sampling on these strata to get uncertainty on bias estimates.- If instrumentation randomly hit 10% overall, check randomness: run balance tests (pre-treatment metrics) between affected and unaffected groups to confirm randomness; if not random, adjust for confounding.

2) Correct the analysis- Exclusion: safest if affected subset is small and non-randomness suspected. Remove affected user-traffic from primary analysis and rerun A/B test. Report loss of power and run a sensitivity analysis.- Reweighting (IPW): if affected selection is predictable from covariates, model probability of being affected p(x) via logistic regression; compute weights w = 1 / (1 − p(x)) for unaffected observations or inverse-probability weights to reweight results to represent full population. Apply stabilized weights to avoid variance inflation.- Model adjustment: fit a regression (GLM or logistic mixed model) for conversion: outcome ~ treatment + affected_flag + treatment*affected_flag + covariates. Use the coefficient on treatment (or marginal effects averaging over affected_flag) to estimate corrected treatment effect. If using Bayesian hierarchical model, incorporate prior uncertainty on affected effect.- Combine approaches: if exclusion reduces bias but hurts power, report both excluded and adjusted (reweighted/regression) estimates.

3) Communicate uncertainty and recommendations- Present: (a) original published estimate, (b) estimate after exclusion, (c) estimate after adjustment, each with 95% CI and explanation of assumptions for each method.- Show sensitivity analysis: worst/best-case bounds (e.g., assuming maximal plausible bias) and probability the treatment effect remains positive.- Be explicit about limitations: whether affected assignment was random, loss of power, model assumptions.- Recommend operational fixes: roll back decisions until corrected analysis completed, patch instrumentation, add monitoring/alerts, and rerun experiment if necessary.- Provide clear actionable summary: most defensible corrected estimate, confidence level, and next steps (e.g., rerun, additional data collection).

HardTechnical

24 practiced

You are asked to detect and quantify late-arriving data that affects daily metrics. Given an events table with `arrived_at` (ETL ingest time) and `occurred_at` (event time), design SQL queries that compute the fraction of events for each occurred_date that arrived late (>24 hours) and how late-arrival patterns have changed over the last 90 days.

Sample Answer

Approach: define occurred_date = DATE(occurred_at), compute delay = arrived_at - occurred_at in hours. For each occurred_date, compute fraction of events that arrived >24h. Then compute 90-day trend including daily fraction and a 7-day rolling average; optionally compute distribution of delays (e.g., >24h, 24–48h, 48–72h, >72h).

Per-day late fraction:

sql

WITH events_by_occurred AS (
  SELECT
    DATE(occurred_at) AS occurred_date,
    COUNT(*) AS total_events,
    SUM(CASE WHEN TIMESTAMP_DIFF(arrived_at, occurred_at, HOUR) > 24 THEN 1 ELSE 0 END) AS late_events
  FROM events
  GROUP BY 1
)
SELECT
  occurred_date,
  total_events,
  late_events,
  SAFE_DIVIDE(late_events, total_events) AS late_fraction
FROM events_by_occurred
ORDER BY occurred_date;

90-day trend with daily fraction and 7-day rolling avg:

sql

WITH daily AS (
  SELECT
    DATE(occurred_at) AS occurred_date,
    COUNT(*) AS total_events,
    SUM(CASE WHEN TIMESTAMP_DIFF(arrived_at, occurred_at, HOUR) > 24 THEN 1 ELSE 0 END) AS late_events
  FROM events
  WHERE DATE(occurred_at) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 89 DAY) AND CURRENT_DATE()
  GROUP BY 1
)
SELECT
  occurred_date,
  total_events,
  late_events,
  SAFE_DIVIDE(late_events, total_events) AS late_fraction,
  -- 7-day rolling average of late_fraction
  AVG(SAFE_DIVIDE(late_events, NULLIF(total_events,0))) OVER (
    ORDER BY occurred_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS late_fraction_7d_avg
FROM daily
ORDER BY occurred_date;

Delay-bucket distribution (helpful to monitor changes):

sql

SELECT
  DATE(occurred_at) AS occurred_date,
  SUM(CASE WHEN diff_h <= 24 THEN 1 ELSE 0 END) AS ontime,
  SUM(CASE WHEN diff_h BETWEEN 25 AND 48 THEN 1 ELSE 0 END) AS late_24_48,
  SUM(CASE WHEN diff_h BETWEEN 49 AND 72 THEN 1 ELSE 0 END) AS late_48_72,
  SUM(CASE WHEN diff_h > 72 THEN 1 ELSE 0 END) AS late_over_72,
  COUNT(*) AS total,
  SAFE_DIVIDE(SUM(CASE WHEN diff_h > 24 THEN 1 ELSE 0 END), COUNT(*)) AS late_fraction
FROM (
  SELECT *, TIMESTAMP_DIFF(arrived_at, occurred_at, HOUR) AS diff_h
  FROM events
  WHERE DATE(occurred_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 89 DAY)
) t
GROUP BY 1
ORDER BY occurred_date;

Why this works:- Uses occurred_date as the reporting date and arrived_at to classify lateness.- SAFE_DIVIDE/NULLIF avoids divide-by-zero.- Rolling average smooths noise and reveals trend shifts.Edge cases:- Null timestamps, negative delays (future-arrival) — filter or flag them.- Timezone consistency: ensure arrived_at/occurred_at in same timezone or normalized to UTC.Alternatives:- Use percentiles of delay per day to detect tail shifts.- Compute cumulative late arrivals by ingestion date to see backfill velocity.

Unlock Full Question Bank

Get access to hundreds of Data Exploration and Quality Assessment interview questions and detailed answers.

Join thousands of developers preparing for their dream job.