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.
MediumSystem Design
63 practiced
Propose a data catalog and lineage solution for an organization with 500 datasets and 50 downstream dashboards. Describe required metadata fields (owners, SLAs, tags), automated lineage capture approaches, integration with CI/CD and access control, how to assign dataset ownership, and how analysts will use the catalog to discover datasets and downstream impacts.
Sample Answer
Requirements:- Discoverability, impact analysis, SLA tracking, access control, automated lineage for 500 datasets and 50 dashboards, CI/CD integration, low friction for analysts.High-level architecture:- Metadata store + UI (e.g., Amundsen/DataHub) + Lineage ingest (OpenLineage/Marquez) + Cataloging agents in ETL/analytics tools + Authz layer (LDAP/OKTA + RBAC) + CI/CD hooks.Required metadata fields (recommended):- Technical: dataset_id, name, description, schema, columns (type, nullable), sample rows, last_updated, row_count, partitioning- Operational: owner (primary owner email, team), steward, SLA (RTO/RPO, freshness window, SLA owner), sensitivity/classification (PII level), tags/categories, business glossary term, retention policy- Lineage: upstream_ids, downstream_ids, transformation summary, tool/source (db/table, pipeline id), versioning- Observability: quality checks, SLA status, recent incidents, SLIs/metrics- Access: allowed_roles, sensitivity-based access notes, data access request linkAutomated lineage capture:- Instrument ETL (Airflow, dbt, Spark) to emit OpenLineage events collected by Marquez/DataHub.- Parse SQL from BI tools (Tableau/Power BI) and query logs to map dashboards -> datasets.- Capture table-level and column-level lineage via query AST parsing (sqlglot) and job metadata.- Backfill using metadata connectors (RDBMS, S3, Kafka) and sampling-based profiling for schema inference.CI/CD integration:- Pipeline step emits metadata (schema, version, tests) on deploy; run contract tests (great_expectations), schema checks, backward-compatibility validation.- Prevent migration/deploys that break downstream SLA/tests; require owner approval for breaking changes via pull requests.- Store dataset versions and allow rollout/rollback.Access control & governance:- Integrate with org identity provider; enforce RBAC + attribute-based policies for sensitive tags.- Catalog UI shows redaction/obfuscation status and links to request access workflows.- Audit logs for catalog reads/requests and data access.Assigning dataset ownership:- Automated suggestion: infer primary owner from last commit/ETL job owner or table creator; require owner confirmation via onboarding email.- Define roles: Owner (operational), Steward (business), Consumer Representatives.- Governance policy: owners must maintain description, SLA, tests; periodic ownership confirmation (quarterly).How analysts use the catalog:- Search by business term, tag, column, or sample values; filter by freshness, SLA, owner.- View dataset profile: schema, sample rows, column definitions, quality metrics, SLA status, and lineage graph showing upstream sources and downstream dashboards.- Impact analysis: click “downstream impacts” to list 50 dashboards and filters to see which KPIs and users are affected.- Subscribe to dataset or SLA alerts, open access requests, and link to example SQL / dbt models and notebook snippets.- Before using a dataset, analysts check lineage and SLA, run quick profiling, and request owner clarifications if needed.Trade-offs:- Full column-level lineage is more accurate but costlier; start table-level + critical columns for key KPIs.- Prioritize instrumentation for most-used datasets/dashboards first.Metrics for success:- % datasets with owner/SLA documented, % dashboards linked to lineage, mean time to resolve access requests, reduction in incidents from schema changes.
HardTechnical
67 practiced
Batch ETL receives late-arriving events up to 48 hours after occurrence. Describe strategies to handle late data so daily reports are timely but eventually consistent: incremental backfills, idempotent upserts, partition-level re-computation, change logs, streaming ingestion with compaction, and trade-offs in complexity and cost. Provide a recommended approach for a small analytics team.
Sample Answer
Situation: Our daily batch ETL can receive events up to 48 hours late, but stakeholders need timely daily reports that converge to correct numbers once late events arrive.Approach: explain options, trade-offs, then recommend a practical solution for a small analytics team.Strategies & trade-offs:- Incremental backfills: schedule targeted reprocessing windows (e.g., re-run last 48 hours) to catch late rows. Low complexity, moderate compute cost; risk: duplicate processing if not idempotent.- Idempotent upserts: write ETL to upsert on a unique key (event_id + source). Ensures correctness after retries/backfills; slightly more complex SQL and requires primary-key hygiene.- Partition-level re-computation: recompute whole day partitions when late data arrives. Simple to reason about, higher compute cost for wide partitions, but good correctness guarantees.- Change logs (CDC): capture source changes and replay to analytics store. Accurate and eventually consistent, but operationally heavy for a small team.- Streaming ingestion with compaction: ingest events into append store, compact by key periodically (e.g., daily compaction). Low query latency and eventual correctness; higher engineering cost.- Trade-offs summary: complexity and operational burden increase from simple backfills → CDC/streaming; cost increases with full recompute and streaming infra; correctness improves with idempotent writes and compaction.Recommended approach for a small analytics team:1. Implement idempotent upserts into a date-partitioned fact table keyed by event_id.2. Run daily jobs that load same-day data for timeliness (T+0) and a scheduled incremental reprocess for the previous 48 hours (T+2) that upserts. Use partition-level recompute only for partitions with large materialized aggregates.3. Maintain an audit/change log (simple staging table with ingestion timestamps) to track late arrivals and trigger reprocesses automatically.4. Monitor: metrics for late-event rate, reprocess duration, and differences post-backfill; alert if divergence > threshold.This balances timeliness (daily reports), correctness (idempotent upserts + T+2 reprocess), and low operational overhead suitable for a small team, while leaving room to adopt CDC/streaming later if scale demands it.
MediumTechnical
57 practiced
You maintain an events dataset that ingests ~100 billion rows per year. Propose a partitioning and clustering/indexing strategy for storing Parquet files on S3 queried by Presto/Trino or for managed warehouses like BigQuery/Snowflake. Explain partition key selection, granularity (daily/hourly), file sizing, compaction, and clustering columns to support common patterns like date-range scans and user-level queries while avoiding small-file problems.
Sample Answer
Situation/Goal: You have ~100B event rows/year and need a storage/layout strategy (Parquet on S3 for Presto/Trino or managed warehouses like BigQuery/Snowflake) that supports fast date-range scans and user-level queries while avoiding small-file issues and high cost.Partition key selection- Primary partition by event_date (UTC normalized). Date-range scans are the most common for analytics so date-first partitioning gives pruning for both Presto and warehouses.- Secondary partitioning only if necessary: e.g., event_type or dataset/environment for very skewed categories. Avoid high-cardinality fields (user_id) as partition keys.Granularity (daily/hourly)- Default to daily partitions. At 100B rows/year (~274M rows/day average), daily keeps partition count reasonable and allows pruning for most analyses.- Use hourly only for extremely high-ingest hours or if realtime hourly queries are common. Hourly increases partition count and small-file risk.File sizing and layout- Target Parquet file sizes 128–512 MB (compressed). This balances read throughput and metadata overhead for Presto/Trino and cloud engines.- Within each daily partition, write files grouped by ingestion time and use a writer that appends to larger files (e.g., multi-part upload pattern or batching).Compaction- Run periodic compaction jobs (daily or hourly depending on latency constraints) to merge small files into target sizes. Use Spark/Glue/EMR or cloud-native jobs.- For append-only event data: compact older partitions (e.g., > 1 day) less frequently; retain fast writes for recent partitions then compact after stabilization.- Maintain an automated vacuum: remove tombstones/obsolete files and update manifest/catalog.Clustering / indexing (for queries like user-level lookups)- Use clustering columns (Snowflake/BigQuery) or Z-order / sort columns (Presto with Hive/iceberg/parquet) on user_id, event_date, and event_type. - Recommended sort order: (event_date, user_id) or Z-order on (user_id, event_timestamp) depending on tooling.- For BigQuery: partition by DATE(event_timestamp) + clustering on user_id, event_type.- For Snowflake: use automatic clustering keys on (user_id, event_date) if frequent user-level scans; monitor micro-partitioning and re-cluster as needed.- For Iceberg/Delta Lake: maintain sort order and use data skipping/partition evolutions.Other considerations- Use a table format (Iceberg/Delta/Hive/Glue Catalog) to manage manifests and enable atomic commits and efficient pruning.- Monitor partition sizes and query patterns; if a partition grows too large, consider sub-partitioning by week-month or bucketing user_id.- Optimize schema: put frequently filtered columns in parquet row-groups and enable predicate pushdown and columnar compression.- Instrument cost/latency metrics and tune compaction cadence accordingly.Result: Daily partitions + compacted 128–512 MB Parquet files + clustering on user_id (with event_date first) provide efficient date-range pruning and performant user-level queries while minimizing small-file overhead and cost.
HardTechnical
58 practiced
Compare BigQuery, Snowflake, and Amazon Redshift for a mid-sized e-commerce analytics workload. Evaluate cost model, concurrency, performance for large scans, separation of storage and compute, analyst usability, and ecosystem integrations. Recommend one platform for a team of five analysts with limited engineering support and justify your choice.
Sample Answer
Summary recommendation: Snowflake. For a mid-sized e‑commerce analytics workload run by five analysts with limited engineering support, Snowflake offers the best balance of predictable cost controls, analyst usability, strong concurrency, and cross‑cloud ecosystem support — while still delivering excellent performance on large scans. Below is a criterion-by-criterion comparison and rationale.Cost model- BigQuery: Flexible on‑demand (cost per TB scanned) or flat‑rate. Good for sporadic queries but can be unpredictable if analysts run unoptimized large scans.- Snowflake: Credit‑based per‑warehouse (time used). Predictable if you auto‑suspend and size warehouses conservatively; easier to budget.- Redshift: Node‑based (on‑demand or reserved). Lower cost at scale but requires right‑sizing and capacity planning.Concurrency- Snowflake: Strong — multi‑cluster warehouses handle concurrent analyst queries with minimal ops.- BigQuery: Serverless, high concurrency by design; throttling rare but possible if quotas hit.- Redshift: Concurrency scaling exists but typically requires more tuning; WLM queues can complicate analyst experience.Performance for large scans- BigQuery: Excellent for very large scans due to Dremel execution; very fast for aggregations across large datasets.- Snowflake: Very good; scales compute independently, columnar storage and result caching help.- Redshift: High performance with tuned cluster and sort/zone maps, but performance depends on cluster maintenance and vacuuming.Separation of storage and compute- Snowflake: Native separation with independent scaling — strong advantage for predictable workloads.- BigQuery: Effectively separated (serverless storage/compute), users don’t manage compute.- Redshift: Historically coupled; newer RA3 nodes improve separation but still more operational.Analyst usability- Snowflake: Very analyst‑friendly SQL, clear UI, role/warehouse isolation, time‑travel, zero‑admin day‑to‑day — minimal engineering needed.- BigQuery: SQL standard, integrates well with Looker/Tableau/Sheets; slightly more need to teach cost‑aware querying.- Redshift: Familiar SQL (Postgres-like) but more ops and tuning; less friendly for non‑engineers.Ecosystem integrations- BigQuery: Best for GCP ecosystem, Looker, Dataflow, Dataproc.- Snowflake: Strong cross‑cloud connectors (AWS/GCP/Azure), native data sharing and marketplace, broad BI/ETL support.- Redshift: Best if entire stack is AWS; deep integrations but limited cross‑cloud.Final justificationWith five analysts and limited engineering support you need low ops, predictable cost, good concurrency, easy BI integration, and the ability to control runaway costs. Snowflake’s multi‑cluster warehouses, auto‑suspend, easy role/warehouse separation, and mature BI integrations minimize operational overhead while giving analysts flexibility and performance. If your org is heavily invested in GCP and prefers a purely serverless model, BigQuery is a close second — but be disciplined about query cost controls. Redshift is suitable only if you already have AWS infra and dedicated engineering time for tuning.
MediumTechnical
79 practiced
Design a testing and validation strategy for ETL/ELT pipelines covering unit tests, integration tests, and regression tests for metrics. Include concrete test cases (schema checks, row counts, null thresholds, referential integrity), where tests should run (CI vs staging vs production monitoring), and how to fail pipelines safely with actionable diagnostics.
Sample Answer
Overview: Build a layered testing strategy — fast unit tests for logic, integration tests for end-to-end correctness, and regression/monitoring in staging + production to catch metric drift. Automate in CI and surface actionable diagnostics so pipelines fail fast and safely.Unit tests (CI, pre-merge)- Purpose: validate transformations, SQL logic, UDFs on small synthetic datasets.- Examples: - Schema check: SELECT columns and types match expected. - Null threshold: feed rows with known nulls; assert null% <= threshold. - Value transform: assert mapping functions produce expected outputs for edge inputs.- Run: every PR, quick (<1–5 min).Integration tests (staging)- Purpose: validate connectors, joins, bulk transformations, and referential integrity on realistic data samples.- Examples: - Row counts: source vs sink expected delta (e.g., +/− allowed change). - Referential integrity: child keys exist in parent table; report missing keys. - Duplicate detection: primary-key uniqueness checks.- Run: nightly or on deploy to staging; use subset of production-like data.Regression tests for metrics (staging + production monitoring)- Purpose: catch silent changes in KPIs.- Examples: - Metric thresholds: global MAU/CTR within historical tolerance bands (e.g., 5% daily, 15% weekly). - Anomaly detection: control charts, EWMA or seasonal decomposition alerts. - Backfill validation: compare new run to previous baseline over same window; percent change alerts.- Run: scheduled post-deploy in staging; continuous monitoring in production with alerting.Where to run- CI: unit tests, linting, static analysis.- Staging: full integration and regression runs against masked or sampled prod-like data.- Production: lightweight health checks, row counts, schema validation, and metric monitors/alerts. Use streaming or scheduled checks; avoid heavy scans during peak hours.Failing pipelines safely & diagnostics- Fail-fast in CI with explicit error messages (test name, expected vs actual, sample failing rows).- In staging, on failure: stop deployment, write detailed failure report to artifact storage with sample rows, SQL explain plans, and a run-id.- In production, prefer alert + safe stop for downstream consumers: mark affected datasets with a “stale/invalid” flag in metadata and pause dependent reports. Include: - Diagnostic payload: failing query, sample rows (anonymized), counts, timestamps, and hashes of inputs. - Recovery plan: automatic rollback to last good snapshot or re-run with corrected inputs; manual runbook linked in alert.- Observability: central dashboard (e.g., Data Quality UI) showing recent test history, flaky-test tracking, SLA status, and breadcrumbs to logs and run artifacts.Operational practices- Maintain test definitions as code (YAML/SQL templates) versioned with pipeline code.- Use data diff tools (Great Expectations, dbt tests, Soda, Deequ) to standardize checks.- Set sane thresholds to avoid alert fatigue; implement escalation policies.- Periodically review failed tests and update baselines; track flaky tests and quarantine until fixed.This approach provides fast feedback for developers, realistic validation before production, continuous metric safety, and actionable diagnostics so analysts can trust downstream reports and respond quickly when things break.
Unlock Full Question Bank
Get access to hundreds of Data and Analytics Infrastructure interview questions and detailed answers.
