Designing, operating, and optimizing reliable data pipelines and ensuring data quality across ingestion, transformation, and consumption. Covers extract transform load and extract load transform patterns, efficient incremental and batch loading, idempotent processing, change data capture, orchestration and scheduling, and performance tuning to meet service level objectives. Includes data validation strategies such as schema enforcement, null and type checks, range and referential integrity checks, deduplication, handling late arriving and out of order data, reconciliation processes, and data profiling and remediation. Emphasizes observability, monitoring, alerting, and root cause analysis for data quality incidents, as well as data lineage tracking, metadata management, clear ownership and process discipline, testing and deployment practices, and governance to maintain data integrity for analytics and business operations. Also covers data integration concerns across customer relationship management systems, marketing automation systems, reporting systems, and other operational systems, including pipeline error handling, data contracts, and how test and validation checks can be integrated into pipelines to prevent regressions.
EasyTechnical
28 practiced
Explain Change Data Capture (CDC). As a data scientist, what CDC implementations would you consider for capturing updates from a relational OLTP database into your analytics platform? Discuss log-based CDC vs trigger-based CDC, latency, consistency guarantees, and how CDC interacts with schema changes.
Sample Answer
Change Data Capture (CDC) is the process of detecting and recording data changes (inserts, updates, deletes) in a source system so they can be propagated to downstream systems (analytics, data lake, feature store) efficiently and reliably.Practical CDC options for capturing OLTP changes into an analytics platform:- Managed connectors (Debezium + Kafka Connect, AWS DMS, Confluent CDC): integrate with streaming stacks.- Cloud-native services (Datastream, AWS DMS, Google Cloud Datastream).- ETL tools with CDC support (Fivetran, Airbyte).Log-based CDC vs trigger-based CDC- Log-based CDC: - How: Reads DB transaction logs (WAL/redo) and emits change events. - Latency: Low — near real-time (milliseconds to seconds) depending on pipeline. - Consistency: Stronger transactional consistency because it follows commit order; minimal impact on source. - Overhead: Low on DB CPU; safe for high-throughput OLTP. - Schema changes: Many tools capture DDL events; require schema evolution handling downstream (e.g., Avro schema registry, Debezium’s DDL events).- Trigger-based CDC: - How: Database triggers write changes to shadow tables or queues. - Latency: Can be real-time but often higher and more variable. - Consistency: Simpler semantics but risks: triggers run inside transactions — can increase latency/lock contention; harder to guarantee ordering across tables. - Overhead: Additional load and complexity on OLTP; risk of performance impact. - Schema changes: Triggers must be updated when schema changes; more maintenance.Considerations for a data scientist- Latency needs: For near-real-time features or monitoring use log-based CDC; for batched analytics, less-critical latency allows simpler ETL.- Consistency: For model training and backfills, ensure exactly-once or idempotent sinks and ordering (use transactional sinks or change metadata like lsn/offset, timestamps).- Schema evolution: Use a schema registry or message format that supports evolution (Avro/Protobuf/JSON with clear contracts). Plan for nullable/new fields and backfills for feature engineering.- Data quality & auditing: Capture metadata (operation type, commit timestamp, transaction id) to reconstruct state and handle late-arriving changes.- Operational concerns: Monitor lag, tombstones for deletes, and have replay/backfill strategies.Recommendation: Prefer log-based CDC (Debezium/Kafka or managed cloud CDC) for production OLTP -> analytics pipelines for low overhead, better consistency, and scalable real-time ingestion; combine with schema registry and idempotent downstream ingestion for robust analytics and model feature pipelines.
MediumTechnical
26 practiced
Observability (medium): Design a monitoring and alerting strategy for data quality incidents in a production pipeline. List key metrics to track (e.g., row counts, null rates, schema violations, latency, duplicate rates), how to choose thresholds and SLOs, what alerts to escalate to on-call vs ops dashboards, and what automated remediation you might trigger before human intervention.
Sample Answer
Designing monitoring and alerting for data-quality incidents requires measurable metrics, clear SLOs, tiered alerts, and safe automated remediation. Below is a practical strategy tailored for a production ML/data pipeline.Key metrics to track:- Row counts / delta vs expected (per table/partition)- Null / missing rates per critical column- Schema violations (type mismatches, new/missing columns)- Distribution drift (KS test, population percentiles) per feature- Duplicate rates (unique key cardinality)- Value-range/precision checks (outliers, impossible values)- Processing latency and throughput (ingest-to-ready time)- Failure/error rate (task/job exceptions)- Downstream model performance regressions (data-to-label leakage, accuracy drop)Choosing thresholds and SLOs:- Set SLOs on business-impacting metrics (e.g., <1% missing in key fields, 99% of daily partitions processed within SLA).- Use historical baselines and seasonal windows (p90/p99, rolling 30–90 days) to define normal ranges.- For distribution drift, use statistical thresholds (p-value < 0.01 or KL divergence beyond historical CI) plus absolute bounds for critical features.- Start conservative (fewer false positives), iterate after observing alert signal/noise.Alert routing and escalation:- Ops/dashboard (informational, lower urgency): small deviations in row counts, non-critical null increases, metric anomalies not yet affecting models.- On-call (pager, high urgency): schema violations, processing failures, large drops in throughput, large data volume loss, duplicates causing incorrect joins, model performance regressions beyond SLO.- Use multi-step escalation: quiet anomalies → channel alert (Slack) → persistent/validated incidents → pager to on-call.Automated remediation before human intervention:- Retry jobs with exponential backoff for transient failures.- Backfill small missing partitions automatically if source replays available.- Switch to degraded but safe dataset (fallback to last-known-good snapshot) and tag downstream models to use fallback.- Auto-drop or quarantine malformed rows into a dead-letter store and notify teams, preserving data for manual review.- Validate schema evolution rules: auto-accept additive, alert on destructive changes.- Throttle downstream model scoring or route traffic to cached predictions if data quality compromises safe inference.Runbooks and observability practices:- Record symptoms, validation checks, and step-by-step remediation in runbooks.- Correlate alerts with lineage (data catalog) to quickly identify upstream producers.- Add dashboards per pipeline showing SLO health, recent anomalies, and remediation status.- Periodically review thresholds, false positives, and post-incident RCA to refine rules.This approach balances automated mitigation to reduce downtime with human escalation for high-impact incidents, using statistical baselines and business-aligned SLOs to prioritize noisy signals.
EasyTechnical
34 practiced
You receive a dataset with many nulls and inconsistent types which will be used to train a model. Describe a practical strategy for handling null values and type inconsistencies during feature engineering for supervised learning. Include approaches for imputation, indicator features, type coercion rules, and how to log and monitor these transformations to avoid silent data drift.
Sample Answer
Situation: I get a raw tabular dataset with many nulls and inconsistent types to train a supervised model.Strategy (practical, repeatable):1. Quick audit- Compute per-column null%, unique counts, sample values and types.- Flag columns with mixed types (e.g., strings like "N/A", numeric strings, dates).2. Type coercion rules (apply before imputation)- Define explicit rules and use safe parsing: - Numerics: strip non-digit chars, convert via to_numeric(errors='coerce'). - Dates: try multiple formats with dateutil; fallback to NaT. - Categoricals: unify case/whitespace, map known synonyms, otherwise cast to string.- Keep raw-original copy and a schema file (expected type, allowed values).3. Imputation approaches (choose per-column by semantics)- Numeric: - If low missing and MCAR: median or KNN imputer. - If correlated with other features: iterative/Multivariate Imputer (e.g., sklearn IterativeImputer).- Categorical: - If high-cardinality: create “missing” category. - If predictive power: use target-guided encoding with separate train/val handling to avoid leakage.- Time-series: forward/backward fill respecting temporal order.- Use simple baselines first (mean/median/mode) then more complex only if they improve validation metrics.4. Indicator features- For any imputed feature, add a binary “was_missing” column to capture informative missingness.- For type-coerced values (e.g., parse failures), add flags (e.g., parse_failed).5. Pipeline & reproducibility- Implement all steps in a sklearn/compose pipeline or in an MLflow/Celery transform script.- Parameterize imputer choices and seeds.- Store fitted transformers (e.g., pickle, joblib) and schema artifacts in a model registry.6. Logging & monitoring to avoid silent drift- Log transformation metadata: null rates, imputer statistics, mapping tables, counts of parse failures per run.- Capture feature distributions (histograms, quantiles), cardinality, and missing% at each batch and baseline.- Automated drift detection: run statistical tests (KS for numerics, chi-square or PSI for categories) between training baseline and incoming batches; alert on thresholds.- Data contracts: enforce required columns, types, and acceptable null thresholds; reject or quarantine violating batches.- Version everything: schema versions, transformer versions, training data snapshot. Tie model version to transform metadata.Result / Rationale:- This ensures predictable, auditable preprocessing, preserves signal in missingness via indicators, avoids leakage, and gives automated detection of upstream data changes so models don’t silently degrade.
EasyTechnical
33 practiced
Explain idempotency in data pipelines. Provide two concrete examples where idempotent operations are necessary (one batch job, one streaming job) and describe patterns you would use to ensure idempotent behavior (e.g., upserts/merge, deduplication keys, idempotency tokens).
Sample Answer
Idempotency in data pipelines means that applying the same operation multiple times has the same effect as applying it once — no duplicate side effects or corrupted state. This is critical when jobs retry, run concurrently, or when sources re-send data.Example — batch job:Situation: Nightly ETL loads sales records from files that may be reprocessed after failures.Pattern: Use an upsert/merge into the fact table keyed by (order_id, event_timestamp). Implement a deterministic merge statement (INSERT ... ON CONFLICT / MERGE) so reprocessing the same file overwrites identical rows rather than inserting duplicates. Add a file_id + row_offset ingestion_id column so you can skip or dedupe previously-applied rows.Example — streaming job:Situation: Real-time clickstream processed by Kafka → stream processor with occasional retries and replays.Pattern: Use deduplication keys and exactly-once sinks. Assign a unique event_id (UUID or composite key) at source, keep a bounded state (or use an external idempotency store like RocksDB or Redis) to track seen event_ids within retention. Prefer transactional sinks (Kafka Transactions, Flink’s two-phase commit, or database upserts) so writes are atomic and not duplicated on retry.Additional patterns and considerations:- Idempotency tokens: clients include tokens to make API-side operations idempotent.- Watermarks and windowing: for streaming aggregation, use event-time windows and late-arrival policies to avoid double-counting.- Deterministic transforms: avoid non-deterministic operations (e.g., random seeds) unless controlled.- Monitoring: track processed offsets/file markers and expose metrics for duplicate rates.Combining deterministic keys, transactional writes, and an idempotency registry yields robust, retry-safe pipelines.
HardTechnical
33 practiced
Algorithmic/problem (hard): You need to reconcile unique-user counts between two large systems daily but cannot compare raw rows due to size. Propose a probabilistic reconciliation approach (e.g., HyperLogLog, Bloom filters) that detects discrepancies with low overhead. Describe how to set error bounds, and how you'd escalate to exact reconciliation when a discrepancy exceeds a threshold.
Sample Answer
Approach (high-level)- Use HyperLogLog (HLL) sketches daily in each system to publish per-day unique-user estimates and lightweight Bloom or Count-Min sketches for targeted membership/size checks. HLL gives tiny storage (~1–16KB) and fast union; Bloom filters help detect presence of suspicious subsets.Setting error bounds- HLL precision p (registers m = 2^p) yields relative standard error ≈ 1.04 / √m. Choose p so that σ_rel meets SLA. Example: p=14 → m=16384 → σ≈1.04/128 ≈ 0.8%. - For two independent HLLs A and B, variance of the difference ≈ var(A)+var(B); relative error on difference ≈ sqrt(σ_A^2 + σ_B^2). Define alert threshold T = α * sqrt(σ_A^2+σ_B^2) where α is z-score (e.g., 3 → ~99.7% confidence). If |est_A − est_B| > T * max(est_A, est_B), flag discrepancy.Practical reconciliation escalation1. Tier 1 — Probabilistic check: compute per-tenant/product HLLs and compare unions and diffs. If within threshold, accept.2. Tier 2 — Narrow with sketches: request Bloom filters or per-bucket HLLs (hash users into N buckets). Compare bucket-level estimates to localize buckets causing most discrepancy.3. Tier 3 — Exact reconcile: For offending buckets, run exact joins (e.g., hash-partitioned export of user IDs) or run distributed set-difference using Bitmaps or sorted merge on partitions. Only export partitions that failed Tier 2 to limit data transfer.Implementation notes and trade-offs- Choose bucket count so expected bucket size fits exact-reconcile budget (e.g., ~100k IDs). Bucketization reduces full-scan cost.- Bloom filters have false positives; use conservative thresholds. Use counting Bloom or invertible Bloom lookup tables (IBLTs) if you want to extract set differences directly with low bandwidth.- Monitoring: track daily variance, false-positive rate, and escalate policy; store HLLs in object store keyed by date and partition.Example numbers- For 100M daily users, p=14 gives ±0.8% error (~±800k). To detect >2% discrepancy with 99% confidence, set α ≈ 2.33 and T computed from combined σ.Why this works- HLLs minimize storage and network cost, bucketization and Bloom/IBLT narrow scope for exact checks, and statistically principled thresholds bound false alarms while keeping exact reconciliation rare and targeted.
Unlock Full Question Bank
Get access to hundreds of Data Pipeline and Data Quality interview questions and detailed answers.
