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.

MediumTechnical

23 practiced

You have limited engineering bandwidth and multiple datasets reporting quality issues. As a BI lead, describe your prioritization framework to decide what to fix first. Include at least four prioritization criteria and how you quantify them for comparison.

Sample Answer

Situation: With limited engineering bandwidth and several datasets flagged for quality issues, I need a repeatable way to decide what to fix first so BI delivers maximal business value.

Framework (high-level): I score each data issue across four primary criteria, normalize to a 0–5 scale, apply weights, and prioritize by weighted score. I also reserve capacity for one “quick win” per sprint.

Prioritization criteria and quantification:1. Business impact (weight 35%): Estimate downstream financial or operational effect — e.g., lost revenue, cost overruns, or decision risk. Map ranges to 0–5 (0 = negligible, 5 = > $250k/quarter or strategic KPI materially affected).2. Consumer reach (weight 25%): Number of dashboards/reports and user roles impacted. 0 = single developer, 5 = executive metrics used company-wide (100s+ users).3. Remediation effort (inverse, weight 20%): Estimated engineering + BI hours to fix and validate. Score 5 for low effort (<8 hrs), 1 for very high effort (>80 hrs). (We invert so higher = better for prioritization.)4. Compliance / reputational risk (weight 20%): Legal/regulatory exposure or external reporting impact. 0 = none, 5 = direct regulatory breach or public misreporting risk.

How I use it:- Compute weighted score = sum(criteria_score * weight).- Rank issues by score; set thresholds (e.g., >4.0 urgent, 3.0–4.0 next sprint).- Tie-breakers: frequency of occurrence and time-since-detection.

Example:- Issue A: Business impact 5, Reach 4, Effort 3, Compliance 2 → score = 5*.35 + 4*.25 + 3*.20 + 2*.20 = 3.65- Issue B: Business impact 3, Reach 5, Effort 5, Compliance 1 → score = 3*.35+5*.25+5*.20+1*.20 = 3.45So A first.

Process & communication:- Validate estimates with stakeholders (finance, product) and engineering for effort.- Publish a short prioritization ledger so stakeholders see rationale and expected timelines.- Reserve a small buffer for hot fixes; track post-fix metrics and add automated tests/monitors to prevent regressions.

This approach balances business value, scope, and effort, is transparent to stakeholders, and creates measurable prioritization decisions the team can act on.

MediumTechnical

23 practiced

Write SQL to compute the completeness and coverage metric for a customers table by comparing the customers table (customer_id, created_at) to orders table (order_id, customer_id, order_date) to report the percentage of 'active' customers (at least one order in last 12 months) present in both tables. Explain assumptions and edge cases.

Sample Answer

Approach: define "active" customers as those with >=1 order in the last 12 months from a reference date (use current_date or a supplied report_date). Compute completeness = percent of active customers that exist in customers table and coverage = percent of customers table who are active (both directions) — the question asks "percentage of 'active' customers present in both tables" so main metric is active_in_both / active_total.

SQL (Postgres-compatible):

sql

-- set reference date; replace current_date with a report_date if needed
WITH recent_orders AS (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE order_date >= (current_date - INTERVAL '12 months')
),
active_in_customers AS (
  SELECT r.customer_id
  FROM recent_orders r
  JOIN customers c ON c.customer_id = r.customer_id
),
metrics AS (
  SELECT
    (SELECT COUNT(*) FROM recent_orders) AS active_total,
    (SELECT COUNT(*) FROM active_in_customers) AS active_in_both,
    (SELECT COUNT(*) FROM customers) AS customers_total
)
SELECT
  active_total,
  active_in_both,
  customers_total,
  CASE WHEN active_total = 0 THEN 0
       ELSE ROUND(100.0 * active_in_both / active_total, 2) END AS pct_active_present_in_customers,
  CASE WHEN customers_total = 0 THEN 0
       ELSE ROUND(100.0 * active_in_both / customers_total, 2) END AS pct_customers_active_and_present
FROM metrics;

Explanation & assumptions:- "Active" = order in last 12 months relative to current_date (can use a fixed report_date).- We count distinct customer_id in orders to identify active users.- Metric pct_active_present_in_customers = completeness of orders vs customers (what % of active order-holders are in customers table).- pct_customers_active_and_present = coverage of customers who are active.

Edge cases:- Null customer_id in orders — excluded by JOIN/distinct; decide if these should be flagged.- Timezone/partial day issues — use consistent date types.- Duplicate or multiple customer_id formats (cleaning/mapping needed).- No orders or no customers — handled (returns 0 and avoids divide-by-zero).

EasyTechnical

21 practiced

List common root causes (at least seven) of data quality issues in analytical systems and for each give one pragmatic mitigation you could implement quickly as a BI analyst to reduce risk or surface the problem to owners.

MediumTechnical

23 practiced

A dataset contains exact event timestamps but client retries caused duplicate events. As a BI analyst, describe how you would identify duplicate events at event level, propose a deduplication rule that balances false positives and false negatives, and explain how you would communicate any residual risk to product managers.

Sample Answer

Situation: Our event table contains exact timestamps but client-side retries created duplicate rows (same user action recorded multiple times). This biased KPIs like DAU and conversion rates.

Identify duplicates:- Explore patterns: group by user_id, event_type, event_timestamp, event_properties hash.- Visual checks: histogram of inter-event intervals per user; spikes near zero indicate retries.- SQL to surface candidates:

sql

SELECT user_id, event_type, event_time,
       COUNT(*) AS cnt,
       MIN(id) AS first_id,
       ARRAY_AGG(id ORDER BY id) AS ids
FROM events
GROUP BY user_id, event_type, event_time_bucket -- see dedupe rule
HAVING COUNT(*) > 1;

Deduplication rule (balance FP vs FN):- Use a conservative rule that removes obvious retries but keeps legitimate quick repeated actions.- Example rule: consider events duplicates if they have same user_id, event_type, and identical key properties (hash of payload) and occur within a short window (e.g., <= 2 seconds). Keep the earliest event_id.- Implement as idempotent pipeline step. SQL example:

sql

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, event_type, properties_hash, DATE_TRUNC('second', event_time)
      ORDER BY event_time ASC, event_id ASC
    ) AS rn,
    event_time - LAG(event_time) OVER (PARTITION BY user_id, event_type ORDER BY event_time) AS delta
  FROM events
)
DELETE FROM events WHERE rn > 1 AND delta <= INTERVAL '2 seconds';

- Rationale: 2s window minimizes false positives (legitimate repeated clicks rarely identical payloads within 2s) while removing most retry duplicates. Use properties_hash to avoid collapsing distinct actions.

Validation & metrics:- Before/after compare totals, unique users, conversion funnel by cohort and time-of-day.- Estimate residual duplicates by sampling events with identical hashes and delta between 2–30s.- Track rollback risk: compute percent of removed events vs total and spot-check a stratified sample.

Communicating residual risk to PMs:- Provide clear summary: what was removed, why, and quantitative impact (e.g., "We removed 1.8% of events; conversion rate changed from 4.2%→4.1%").- Share uncertainty: estimate residual duplicates (e.g., 0.2–0.5%) and potential effect on top KPIs.- Recommend mitigation: add client-side idempotency keys, server-side dedupe using event_id, add ingestion-level validation.- Offer dashboards showing both raw and deduped metrics for 30 days so PMs can understand differences and decide which to trust for decisions.

This approach is conservative, measurable, and provides product teams with the data and context to accept the deduped metrics while planning longer-term fixes.

MediumTechnical

24 practiced

In Tableau or Power BI, describe how you would implement a dashboard widget that lists datasets with freshness and completeness metrics, supports sorting by severity, and allows analysts to drill into a sampled failing-rows query. Include the data model and example SQL used to return a sample of failing rows.

Sample Answer

Approach summary: build a dataset-quality widget that shows each dataset row with freshness (last_ingest_time), completeness (% non-null required fields), and severity (derived score). Enable sorting by severity and a drill action that opens a detailed view showing a random sample of failing rows and the failing rule(s). Implement in Tableau or Power BI by modeling a quality table, using calculated fields for metrics, and a parameter/URL action or direct drillthrough to run the sample-query.

Data model (table: dataset_quality_metrics):- dataset_id (pk)- dataset_name- last_ingest_time (timestamp)- completeness_score (float 0-1)- freshness_days (int) -- days since last_ingest_time- severity_score (int) -- e.g., 0=OK,1=Warning,2=Critical- failing_rules (json/text) -- list of rules currently failing- sample_query_template (text) -- parameterized SQL to retrieve failing rows (safe, pre-approved)

Widget implementation:- Visual: table/list with columns: dataset_name, freshness_days, completeness_score (as %), severity (color-coded).- Sorting: bind sorting to severity_score then completeness_score.- Drill: configure drill-through (Power BI) or URL action (Tableau) passing dataset_id and rule_id to a detail page that executes the sample SQL (or retrieves cached sample) and shows a paginated table + rule explanation.

Example parameterized sample SQL (Postgres) to return up to 10 failing rows for a selected dataset and rule_id:

sql

-- params: :dataset_id, :rule_id, :limit
WITH failing_rows AS (
  SELECT *
  FROM raw_schema.data_table dt
  JOIN metadata.datasets d ON d.id = :dataset_id AND dt.dataset_key = d.dataset_key
  WHERE
    CASE :rule_id
      WHEN 'not_null_customer_id' THEN dt.customer_id IS NULL
      WHEN 'negative_amount' THEN dt.amount < 0
      WHEN 'invalid_date' THEN dt.order_date IS NULL OR dt.order_date > now()
      ELSE FALSE
    END
)
SELECT *
FROM failing_rows
ORDER BY random()
LIMIT COALESCE(:limit, 10);

Notes and best practices:- Pre-validate and whitelist sample_query_template to avoid arbitrary SQL execution.- Cache sample results for high-traffic datasets and refresh on ingest.- Use incremental refresh/aggregates for completeness/freshness calculations to keep dashboard snappy.- In Tableau, use Dashboard Actions to pass dataset_id to a second sheet; in Power BI use Drillthrough filters or a paginated report for large samples.

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.