Covers the principles, frameworks, practices, and tooling used to ensure data is accurate, complete, timely, and trustworthy across systems and pipelines. Key areas include data quality checks and monitoring: nullness and type checks, freshness and timeliness validation, referential integrity, deduplication, outlier detection, reconciliation, and automated alerting. Includes designing service level agreements for data freshness and accuracy, data lineage and impact analysis, metadata and catalog management, data classification, access controls, and compliance policies. Encompasses operational reliability of data systems: failure handling, recovery time objectives, backup and disaster recovery strategies, data observability, and incident response for data anomalies. Candidates may be evaluated on designing end to end data quality programs, selecting metrics and tooling, defining roles and stewardship (data owner, steward, custodian), building golden-record and master-data-management strategies for record linkage and deduplication across source systems (illustrative domains include CRM and sales data, IoT telemetry, financial transactions, and event or log data, among others), and implementing automated pipelines and governance controls.
EasyTechnical
42 practiced
Explain what a data catalog provides in a modern analytics stack. For a small startup with 10 analysts, list the essential catalog capabilities you would prioritize in the first 6 months and justify each choice with expected benefits.
Sample Answer
A data catalog is a centralized inventory of an organization’s data assets plus their metadata: schema, lineage, ownership, quality metrics, usage, and business context. In a modern analytics stack it enables discovery, governance, trust, and faster reuse so analysts and data scientists can find the right data, understand provenance and limitations, and onboard features/models more quickly.For a small startup with ~10 analysts, prioritize these core capabilities in the first 6 months:- Searchable discovery with rich metadata - Benefit: Reduces time-to-insight by letting analysts find tables/columns quickly; avoids duplicate work and redundant ad-hoc ETLs.- Data lineage (upstream/downstream) - Benefit: Enables impact analysis and debugging when pipelines change; critical for model reproducibility and incident triage.- Ownership and contact information - Benefit: Speeds clarification (who to ask), assigns responsibility for fixes, and supports accountability for data quality.- Basic data quality profiling (row counts, null rates, basic anomalies) - Benefit: Surface obvious problems early so analysts don’t build models on bad inputs; improves trust in outputs.- Business glossary / semantic tagging - Benefit: Aligns technical fields with business terms (e.g., “MAU”); lowers misinterpretation risk across teams and eases stakeholder communication.- Access controls / integration with existing auth - Benefit: Ensures compliant, least-privilege access without slowing analysts; supports future auditing.These priorities maximize immediate productivity and trust with minimal engineering overhead. Once adopted, expand to automated monitoring, richer quality rules, and catalog-driven feature stores.
MediumTechnical
46 practiced
You own a revenue forecasting model and forecasts have underperformed in the last month. Walk through a data-focused root cause analysis: which data checks, feature distribution comparisons, and experiments would you run to determine if the issue is due to data quality (e.g., missing transactions, changed business rules) versus model drift?
Sample Answer
Situation: Last month's forecasts underperformed against actual revenue — I own the model and must determine whether this is data quality or model drift.Approach: Systematic data-first root cause analysis in three phases — validate inputs, compare feature/target distributions, run experiments to isolate cause.1) Data checks (quick wins)- End-to-end pipeline health: check ingestion logs, schema changes, failed jobs, latency, and row counts by source and partition (daily/hourly).- Sanity counts: compare total transactions, customers, and revenue last month vs. expected seasonal baseline and same period last year; alert if drop/gap > X%.- Completeness & duplicates: identify missing dates, null rates per feature, duplicate transaction IDs.- Business-rule changes: query product catalog/version history, pricing updates, promo flags; talk to ops to confirm rule changes.- Example queries: daily transaction counts, lead-lag between event timestamp and ingestion timestamp.2) Feature & label distribution comparisons- Univariate shifts: KS test or Wasserstein distance for continuous features; chi-square for categorical. Flag features with p < 0.01 or distance > threshold.- Multivariate drift: monitor population stability index (PSI) for top features, covariance changes, and concept shift in target conditional distributions P(y|x) vs. historical.- Visuals: histograms, cumulative density plots, rolling time series of feature means/std, and calibration plots for predicted vs actual.- Check upstream aggregations (e.g., cohort sizes used to compute features) for bias.3) Experiments to isolate cause- Backfill test: re-run feature pipeline on raw events for recent period to confirm no late-arriving or dropped records; compare model inputs to production inputs.- Shadow scoring: score historical models on current inputs and current model on historical inputs. If current inputs produce worse results for all models → data issue. If only current model degrades on unchanged inputs → model drift/bug.- Retrain vs. serve-old: retrain model including recent data; if retrained model restores accuracy, likely real concept change (drift). If retrain doesn't help, suspect data quality or label noise.- Feature ablation: remove suspected shifted features and evaluate performance change; large improvement suggests corrupted features.- A/B test with corrected data (if available) or feature-imputed variants.Decision rules & next steps- If ingestion gaps, mismatched counts, or backfill fixes predictions → data quality; fix pipeline, reprocess, and add alerts.- If inputs stable but P(y|x) changed and retrain recovers accuracy → concept drift; update model, add monitoring, and consider feature engineering or nonstationary models.- Implement automated dashboards: row counts, null rates, PSI, KS/Wasserstein, calibration, residuals by segment, and a shadow-testing framework to quickly distinguish data vs model issues.This process balances fast checks with experiments that isolate root cause and produce actionable remediation.
EasyTechnical
40 practiced
Explain the difference between data lineage and metadata/catalog information. Give two concrete scenarios where lineage is essential for troubleshooting and two scenarios where metadata/catalog features are more useful for analysts. Describe how lineage and metadata complement each other in a governance program.
Sample Answer
Data lineage vs. metadata/catalog:- Data lineage describes the end-to-end provenance and transformations of data: where it originated, how it was transformed (jobs, SQL, code), intermediate datasets, and where it flows. It’s operational and temporal—useful to trace a value back through pipelines.- Metadata/catalog information describes descriptive attributes about datasets: schema, owner, tags, refresh cadence, business glossary terms, sample rows, sensitivity classification. It’s discovery- and governance-oriented.Two scenarios where lineage is essential for troubleshooting:1) Unexpected model input shift: A feature used by a production model suddenly changes distribution. Lineage lets you trace that feature back to the upstream ETL job and specific SQL transformation that introduced the shift (e.g., a join change), so you can fix the transformation and roll back if needed.2) Downstream report shows wrong totals: Lineage identifies which upstream table or incremental job produced the erroneous rows and the exact job run/time, enabling rapid rollback or patching and replay of downstream aggregates.Two scenarios where metadata/catalog is more useful for analysts:1) Data discovery: An analyst searching for sales data can use catalog tags, descriptions, sample rows, and schema to find the right table quickly without inspecting pipelines.2) Access and compliance checks: Before using a dataset for PII-sensitive analysis, analysts consult sensitivity classification, owner contact, and retention policies in the catalog to request access or apply masking.How they complement each other in governance:- Catalog metadata gives context and policy (who owns data, sensitivity, SLA), while lineage provides the technical proof and traceability to implement and audit those policies. Together they enable impact analysis (catalog shows consumers/owners; lineage shows exactly which jobs produce consumers’ inputs), faster root-cause resolution, audit trails for regulators, and confidence for analysts to trust data: metadata answers “what is this and who to ask”; lineage answers “how was this produced and can I reproduce it.”
HardSystem Design
71 practiced
Design a proof-of-concept architecture for a self-healing ETL pipeline that detects corrupt partitions (malformed files or schema mismatches), attempts automated repair (re-parse with alternate schema, re-ingest from previous snapshot), reprocesses data, and notifies stakeholders with root-cause and audit trail. Include orchestration, checkpoints, idempotency, retry logic, and cost considerations.
Sample Answer
Requirements:- Detect corrupt partitions (malformed files, schema mismatch)- Auto-repair attempts (alternate schema, re-ingest snapshot)- Reprocess and ensure idempotency- Orchestration, checkpoints, retries, audit trail, stakeholder notifications- Cost-aware (pay-for-what-used, avoid full reprocesses)High-level architecture:Source → Landing (object store, e.g., S3) → Orchestrator (Airflow or Prefect) → Validator/Detector → Repair Engine → Staging (parquet/Delta Lake) → Consumer (feature store / model training) Monitoring/Alerting & Audit DB (Postgres/Dynamo) alongside Notification service (Slack/Email)Core components & responsibilities:1. Landing: raw immutable files, partitioned by date/source. Enable versioning and lifecycle rules.2. Orchestrator: DAG per partition. Steps: download, validate, transform, commit. Maintains checkpoints and run metadata.3. Validator/Detector: schema registry + JSON/CSV/parquet validators. Rules: type checks, nullability, row-level constraints, hash-sums. Emits detailed failure code and example rows.4. Repair Engine: - Strategy 1: Re-parse with alternate schema/version (from schema registry) using tolerant parsers - Strategy 2: Re-ingest from previous snapshot or upstream source (object store versioning) - Strategy 3: Row-level quarantine: extract bad rows to quarantine bucket and process remainder Repair attempts increment a counter, record actions in Audit DB.5. Staging: Append-only table format (Delta Lake/Apache Iceberg) supporting time travel/ACID so reprocessing is incremental and replayable.6. Idempotency & Checkpoints: - Each partition processed with deterministic job id and input checksum. Writers use upserts by partition+file_checksum. - Use transaction log (Delta) to commit atomically. Orchestrator writes checkpoint on successful commit.7. Retry logic: - Exponential backoff with jitter for transient failures. - Repair attempts capped (e.g., 3 automated tries). After threshold, escalate to human. - For schema drift: if automated schema evolution allowed, apply with approval flag; otherwise, create PR in schema registry workflow.8. Audit & Root-cause: - Audit DB stores: run_id, partition, checksums, validator errors, repair actions, durations, artifacts (sample bad rows). - Root-cause reporter composes human-readable summary + machine artifacts and posts to stakeholders with links to quarantine data, diffs, and remediation steps.9. Cost considerations: - Use serverless compute (AWS Lambda/EKS Fargate) for lightweight validation; batch cluster (Spot EMR/Dataproc) for heavy reprocessing. - Avoid full reprocess: use partition-level or row-level repair and upserts. - Use lifecycle policies to delete old raw files, compress to columnar formats to reduce storage and processing costs. - Prioritize repairs: high-value partitions (model training windows) first.Data flow example (DAG):1. validate_partition(task): checksum, schema check → OK -> transform. If fail -> goto repair.2. repair_partition(task): try alt schema parse (idempotent). If success -> transform. Else try re-ingest snapshot. If success -> transform. Else quarantine and alert human.3. transform_and_commit(task): write to Delta with upsert by partition+file_checksum and commit checkpoint.4. notify(task): success/failure with root-cause + audit link.Why this works for a Data Scientist:- Preserves data lineage and reproducibility (time travel + audit)- Minimizes wasted compute by repairing only affected partitions- Provides clear root-cause artifacts for model impact analysis (sample bad rows, schema diffs)- Enables automated recovery for common corruptions while surfacing novel issues to humansEdge cases & trade-offs:- Aggressive auto-schema-evolution risks silent data drift — require gated approvals for production models.- Spot instances save cost but increase retry complexity; use mixed strategy.- Quarantine growth: implement retention and sampling.Metrics to track:- Mean time to detect (MTTD) and mean time to recover (MTTR)- Repair success rate, number of human escalations- Cost per TB processed and cost per recovery- Impact on model performance (pre/post reprocess validation)Implementation notes:- Use Delta Lake (or Iceberg) for ACID upserts and time travel.- Schema registry (Confluent/Apicurio) to version parsing logic.- Airflow/Prefect for orchestrator with XCom or metadata DB to store checkpoints.- Audit DB (Postgres) with S3 links to artifacts. Notification via PagerDuty + Slack.
EasyTechnical
61 practiced
What is referential integrity and why does it matter in analytics systems that denormalize data for performance? Describe three practical strategies to detect and enforce referential integrity across ETL/ELT pipelines when source-of-truth sits in operational DBs.
Sample Answer
Referential integrity means that references between records are valid: a child row’s foreign key points to an existing parent row in the source-of-truth. In analytics systems we often denormalize (duplicate keys and attributes into wide fact tables) for performance. If references break, analyses, joins and model features become incorrect or biased — e.g., user events mapped to deleted customers, or features built from stale dimension attributes.Three practical strategies to detect and enforce referential integrity across ETL/ELT when the operational DB is the source-of-truth:1) Automated validation tests during ETL/ELT (detect)- Implement lightweight, fast checks after each load: left-join from denormalized records to the source dimension and count NULLs or mismatches (or use anti-join). Fail the job or raise alerts if missing rate > threshold.- Example: in dbt write a test: select id from analytics.fact f left join ops.dim d on f.dim_id=d.id where d.id is null.2) Use CDC + upserts and tombstones (enforce near-real-time)- Stream changes from the operational DB (Debezium/Kafka CDC) to ensure deletes/updates propagate. On downstream tables, apply upserts and tombstone (soft-delete) markers so denormalized records reflect current truth rather than accumulating orphan references.- This keeps analytics features consistent for training and serving.3) Periodic reconciliation and automated repair (detect + enforce)- Run daily/weekly reconciliation jobs: compare counts, checksums, and sample keys between ops and analytics. For discrepancies, auto-backfill or re-run the affected partitions; maintain an SLA and audit log.- Add contract checks: schema/key existence assertions before downstream consumers use data; maintain a golden-dimension lookup (materialized) refreshed on a cadence and used as authoritative join table.Why these work: tests catch problems early, CDC minimizes drift, and reconciliation + repair ensures long-lived consistency. Together they balance performance (denormalized reads) with correctness for analysis and modeling.
Unlock Full Question Bank
Get access to hundreds of Data Quality and Governance interview questions and detailed answers.
