Designing, building, and operating end-to-end data and analytics platforms that collect, transform, store, and serve event, product, and revenue data for reporting, analysis, and decision making. Core areas include event instrumentation and tag management to capture user journeys, marketing attribution, and experimental events; data ingestion strategies and connectors; extract-transform-load (ETL/ELT) pipelines and streaming processing; orchestration and workflow management; and the trade-offs between batch and real-time architectures. Candidates must be able to design storage and serving layers, including data warehouses, data lakes, lakehouse patterns, and managed analytical databases, and to choose storage formats, partitioning, and indexing strategies driven by volume, velocity, variety, and access patterns. Data modeling for analytics covers raw event layers, curated semantic layers, dimensional modeling, and metric definitions that support business intelligence and product analytics. Governance and reliability topics include data quality validation, freshness monitoring, lineage, metadata and cataloging, schema evolution, master data considerations, and role-based access control. Operational concerns include scaling storage, processing, and query concurrency; fault tolerance and resiliency; monitoring, observability, and alerting; and cost, performance, and capacity planning trade-offs. Finally, candidates should be able to evaluate and select tools and frameworks for orchestration, stream processing, and business intelligence; integrate analytics platforms with downstream consumers; and explain how architecture and operational choices support marketing, product, and business decisions while balancing tooling investment and team skills.
HardTechnical
69 practiced
Design a scalable data quality framework for a platform that ingests both batch and streaming data. Describe types of checks (schema, nulls, distributional drift, referential integrity), where to run checks (ingest, transform, post-load), alerting thresholds, automated remediation (quarantine, backfill), and how to track SLOs for data quality over time.
Sample Answer
Requirements & goals: ensure reliable inputs for models/analytics, detect issues early for both batch and streaming, automate low-risk remediation, and track SLOs so data consumers (including models) have quantified trust.Types of checks (what and why)- Schema: column presence, type, nullable, enum values. Fail-fast for breaking changes.- Nulls / completeness: per-column null% and row-level completeness. Important for feature availability.- Referential integrity: foreign-key existence, dedup keys, cardinality changes.- Distributional drift: feature-wise distribution comparison (KS/AD for continuous, chi-square for categorical), population stability index (PSI), label drift for supervised pipelines.- Business-rule / statistical checks: ranges, outliers, monotonicity, seasonal expectations.Where to run checks (pipeline stages)- Ingest (edge): lightweight schema + basic nulls, size/throughput, basic sanity to block gross violations.- Transform (stream micro-batch or batch ETL): full schema, referential, business rules, and sample-based drift tests. Use windowed checks for streaming (sliding/hopping).- Post-load (warehouse/feature store): longitudinal drift and integrity checks across joins, full-table stats, downstream model input validation.Alerting thresholds & policies- Severity tiers: Critical (schema break, FK violation > threshold) = immediate page; Warning (null% > soft threshold, PSI > 0.1) = Slack/email; Degradation (PSI 0.1–0.25) = monitor.- Example numeric thresholds: null% > 5% (warning) / >20% (critical); PSI >0.1 (investigate) / >0.25 (action); KS p-value < 0.01 for large samples triggers investigate.- Use adaptive thresholds (baseline ± n sigma) and configurable per-stream/feature.Automated remediation- Quarantine: route offending messages/partitions to a quarantined topic/table with provenance metadata.- Reject-at-ingest for critical schema mismatches; for soft failures, tag and continue.- Auto-backfill: schedule backfill jobs for identified missing partitions or replayable stream segments; prioritize by consumer importance.- Auto-repair: simple fixes (cast types, fill with default or model-imputed values) under strict guarded rules with audit logs.- Human-in-loop escalation for ambiguous drift or repeated failures.Instrumentation & implementation- Central metadata store + data contract registry (schemas, owners, SLAs).- Observability: metrics (counts, null rates, PSI, KS p-values), logs, sample snapshots, lineage. Emit to Prometheus/Grafana and send anomalies to Alertmanager/Slack.- Use stream processors (Flink/Beam) for windowed checks; Airflow/DBT for batch checks; feature store (Feast) with validation hooks.Tracking SLOs for data quality- Define SLOs per dataset/feature: e.g., "99.5% of daily partitions pass critical checks" or "feature X null% < 2% over a 7-day window".- Track error budget (allowable failures). Visualize burn rate and historical trends on dashboards.- Maintain per-owner SLA reports, automated weekly rollups, and monthly postmortems when error budget is exhausted.Example workflow- Ingest microservice validates schema; malformed messages -> quarantine topic.- Transform job runs per-window drift checks; if PSI>0.2, pause downstream model refresh, notify owner, trigger backfill candidate creation.- Post-load daily job computes SLO metrics, updates dashboard, and auto-opens ticket if SLOs breached.Why this works (trade-offs)- Early lightweight checks prevent noisy downstream issues; heavier statistical checks run later to avoid latency.- Automation reduces toil but keeps human oversight for non-deterministic drift.- Configurable thresholds and per-dataset SLOs align detection sensitivity with business impact.
EasyBehavioral
56 practiced
Tell me about a time you discovered a data quality issue that impacted an important metric such as revenue or retention. Describe the situation using STAR: Situation, Task, Action, Result. Explain how you diagnosed the issue, remediated it, and what long-term controls you implemented.
Sample Answer
Situation: Last year I noticed a sudden 6% drop in 30-day retention for a key subscription cohort while revenue reports showed a small increase — the signals conflicted and leadership was concerned.Task: As the data scientist owning retention metrics, I needed to determine whether this was a real user-behavior change or a data-quality problem, quantify the impact, fix historical metrics if needed, and prevent recurrence.Action:- Diagnosed: I ran anomaly detection on related metrics, then traced the retention calculation back through the ETL. Using SQL and join-count diagnostics I found a recent change in the auth service created duplicate user_ids (same email, different uuid) — our pipeline joined on uuid only, so activity split across duplicates, undercounting retained users while revenue attribution aggregated by email.- Remediated: I wrote a deduplication script to map duplicate uuids to canonical user_ids (using email+created_at heuristics), reprocessed affected partitions of the events warehouse, and recalculated retention and revenue reports. I coordinated the fix with engineering, QA, and finance and communicated expected changes to stakeholders.- Long-term controls: added unit checks in the ETL (uniqueness and referential integrity tests), introduced nightly validation jobs that compare cohort sizes and retention delta thresholds (alerting on >2% drift), added schema constraints upstream, and documented a runbook for similar incidents.Result: Reprocessed metrics showed retention was stable; the apparent 6% drop was a data artifact. Financial reports were corrected (avoided an estimated $250k misinformed marketing spend), stakeholders regained confidence, and the validation tests caught a later duplication bug before it affected KPIs. I learned the value of tracing metrics to raw events and building automated guardrails.
EasyTechnical
78 practiced
As a data scientist owning key metrics, list the SLAs and SLOs you would define for data pipelines and metric serving (freshness, accuracy, completeness, and query latency). For each SLA include the metric, a suggested threshold, and the alerting action or escalation policy.
Sample Answer
Situation: As the data scientist owner of key metrics, I’d define SLAs (contractual guarantees) and SLOs (operational targets) across freshness, accuracy, completeness, and query latency to ensure trusted metrics.1) Freshness- Metric: Time since last successful pipeline run / data timestamp skew.- SLO: 95% of metric updates ≤ 30 minutes; SLA: 99.9% ≤ 2 hours.- Alerting/escalation: WARNING at >30m (pager duty to on-call data engineer), CRITICAL at >2h (page engineering manager + notify product owner, open incident ticket).2) Accuracy- Metric: % of records passing validation rules / anomaly rate vs historical baseline.- SLO: Data validation pass rate ≥ 99% per run; SLA: ≥ 99.9%.- Alerting: Automated daily report; immediate alert if pass rate <99% (email + Slack to data QA), critical if <95% (page lead DS + rollback/stop downstream consumers).3) Completeness- Metric: % of expected rows/partitions present vs expected window.- SLO: ≥ 98% coverage per ingestion window; SLA: ≥ 99.5%.- Alerting: Warning if <98% (ticket to ETL owner), critical if <95% (page infra + block metric serving until manual review).4) Query latency (metric serving)- Metric: 95th-percentile API/query response time.- SLO: p95 ≤ 300 ms for dashboards; SLA: p95 ≤ 1s.- Alerting: Degradation alert if p95 >300ms for 5+ minutes (ops Slack channel), escalation to SRE if >1s for 10+ minutes (page, incident).Cross-cutting actions:- Attach runbooks (triage steps, rollback, data replay), include owners, and automatic consumer notifications.- Integrate with monitoring (Prometheus/Datadog), maintain dashboards, and review SLOs monthly.
MediumSystem Design
58 practiced
Design a lakehouse architecture that supports interactive BI and ML feature engineering for ~10PB of data. Describe storage formats, metadata/catalog, ACID semantics, compute separation, support for time travel, and incremental updates. Explain how you would integrate the lakehouse with a warehouse for serving BI queries.
Sample Answer
Requirements:- Store ~10PB raw + curated data; support interactive BI (low-latency SQL), and ML feature engineering (fast reads, versioning, reproducibility).- ACID for correctness, time-travel for experiments, incremental updates for streaming/batch sources, compute isolation for workloads.High-level architecture:- Object store (S3/GCS/ADLS) with columnar Parquet files as base format.- Table format: Delta Lake or Apache Iceberg (both support ACID, metadata, snapshots); choose Delta if existing Spark ecosystem, Iceberg for multi-engine parity.- Metadata/catalog: Hive Metastore or a managed catalog (AWS Glue, Unity Catalog) storing table schemas, partitions, and snapshot history.- Compute separation: decouple storage from compute. Use: - Interactive SQL engine (Presto/Trino or Spark SQL / Databricks SQL) for BI with cached query acceleration and a BI-serving cluster (autoscaled). - Dedicated ML feature compute (Spark or Dataproc/EMR, or feature store services) for joins, aggregations, and offline feature pipelines.- ACID semantics & time travel: table format provides atomic commits, snapshot isolation, and time-travel APIs to read historical table versions (for reproducible experiments).- Incremental updates: use CDC and streaming writes into Delta/Iceberg (merge/upsert semantics). Maintain partitioning and Z-order/compaction for read performance. Implement incremental ETL using watermarking and change tables.- Feature store integration: materialize features as versioned feature tables (backfills + online store). Use same lakehouse tables for offline features; export point-in-time-correct feature snapshots for training.- BI warehouse integration: create a read-optimized replica or export aggregated marts into a columnar warehouse (Snowflake/BigQuery/Redshift) via scheduled CDC or materialized views. Alternatively, use a query engine that federates to the lakehouse and maintains a cache/materialized query results layer for low-latency BI.Trade-offs:- Choose Delta/Iceberg to balance multi-engine access vs ecosystem. Compaction and partition/planning critical for interactive SLAs.
MediumTechnical
64 practiced
You must implement metric governance across BI and product teams to reduce metric drift and multiple answers to the same question. Propose an operational plan that includes ownership, a metric registry, review cadence, technical controls (semantic layer, access control), and onboarding for new metrics.
Sample Answer
Situation: We were seeing multiple teams publish different values for the same KPIs (e.g., MAU, conversion rate), causing conflicting decisions.Plan (operational):1. Ownership- Assign a single Metric Steward for each core metric (product, growth, finance). Metric steward = accountable for definition, lineage, and QA.- Define RACI: Stewards (A), Data Engineering (R for pipelines), BI/Product (C), Analytics Platform (I).2. Metric Registry- Build a central registry (Git-backed + UI) storing: canonical name, business definition, SQL/semantic expression, source tables, owner, tags, last validated timestamp, SLA, and sample queries.- Require registry entry before any dashboard/experiment uses the metric.3. Review cadence- Weekly triage for new metric requests; monthly governance review for drift, deprecated metrics, and gating changes; quarterly audit for lineage and accuracy.- Use PR process: changes to metric definitions require steward approval + automated tests.4. Technical controls- Implement a semantic layer (dbt + metric layer like Cube or dbt metrics) that exposes canonical metrics to BI tools and analytics notebooks.- Enforce access control: only read canonical metrics in Tableau/Power BI; raw tables access limited to data engineering + approved analysts.- CI tests: unit tests for metric SQL, data-quality checks, anomaly detection alerts on sudden shifts.5. Onboarding new metrics- Template-driven request (business hypothesis, owner, definition, downstream consumers, expected SLAs).- Trial period: staging metric in registry, validation against known signals, stakeholder sign-off, then promote to canonical.- Training: short playbook + quarterly workshops for product/BI teams.Result: Single source of truth, faster investigations, fewer ad-hoc reconciliations, and measurable reduction in metric-related incidents.
Unlock Full Question Bank
Get access to hundreds of Data and Analytics Infrastructure interview questions and detailed answers.
