Prepare a deep, end-to-end walkthrough of a project you personally built or substantially contributed to, in whatever domain you work in (software, data, ML, infrastructure, design, research, product, or otherwise). Describe the problem or need you were solving, the constraints you faced, the success metrics you defined, and how you scoped and planned the work. Explain your overall approach or design: the major components or workstreams, how they fit together, and the specific decisions you made along the way. Be explicit about your exact role and which parts you owned versus work done by others. Discuss the tools, methods, or technologies you chose and why, how you verified your work was correct or effective (testing, validation, review, QA, or the equivalent practice in your field), and how you tracked progress. Cover trade-offs you evaluated, problems or failures you hit, how you diagnosed and resolved them, and any improvements you made to quality, performance, or reliability. Describe the end-to-end delivery process: iteration cycles, review practices, rollout or launch steps, and follow-up after completion. Where possible, quantify impact with metrics, highlight lessons learned, and explain what you would do differently with more time or experience. Interviewers are listening for depth of understanding, ownership, problem-solving, and clarity of explanation.
MediumTechnical
58 practiced
Explain a concrete cost optimization you implemented on a data platform (storage or compute). Provide before/after numbers, what changes you made (spot instances, partition pruning, file formats, compaction), and any trade-offs in availability or latency.
Sample Answer
Situation: Our data lake on AWS S3 supported daily batch ETL jobs in Spark that populated analytics tables. Monthly S3 + EMR costs had crept to ~$38k/month, and Spark job runtimes were increasing (avg job 4.5 hours), blocking SLAs.Task: Reduce monthly cost while keeping data freshness within 2 hours and acceptable query latency for analysts.Action:- Storage changes: Converted raw/parquet landing from small JSON/CSV to partitioned Parquet with Snappy compression and predicate-friendly schema. Rewrote historical ~50 TB into partitioned Parquet (by ingestion_date and region) and compacted small files into larger 256 MB files using a one-time Spark compaction job.- Compute changes: Migrated recurring batch Spark jobs from on-demand EMR to EMR on EC2 Spot Instances with a mixed-instance fleet and checkpointing; moved interactive/BI queries to Athena over the optimized Parquet to avoid spinning clusters.- Query/workflow changes: Added partition pruning (pushdown) in Spark and enforced partition filters in job contracts; scheduled compaction weekly for small incremental writes.- Benchmarking: Ran A/B on representative jobs to validate.Result:- Monthly cost fell from ~$38k to ~$18k (≈53% reduction): S3 lifecycle + storage compaction saved ~15% ($5.7k), EMR spot + smaller cluster sizes saved ~32% ($12.3k), Athena cut interactive cluster time saving ~6% ($2k).- Job runtimes dropped from 4.5h to 1.2h for the main pipeline (70% faster) due to fewer files, columnar format, and partition pruning.- Query latency for analysts improved (median 2s → 1s for common queries).Trade-offs and mitigations:- Spot interruptions: introduced checkpointing, shorter task allocations, and a fallback to on-demand for critical runs. This added complexity but kept availability high (failed job rate <1%).- One-time rewrite and compaction had an upfront cost (~$2k compute) and required maintenance windows.- More rigid partitioning required producers to set correct keys; enforced via CI checks.Learning: File format and layout gave the biggest ongoing ROI; compute savings multiplied after storage optimization.
MediumTechnical
76 practiced
Explain how you handled schema evolution for a table consumed by multiple downstream systems. Include your versioning strategy (schema registry, table versioning), migration plan, how you ensured backward/forward compatibility, and how you tested consumer compatibility before rollout.
Sample Answer
Situation: We had a central event table (Avro over Kafka + Parquet in the data lake) consumed by analytics jobs, a streaming enrichment service, and downstream ML pipelines. We needed to add fields and rename one column without breaking consumers.Versioning & registry:- We used Confluent Schema Registry with Avro schemas and enforced SUBJECT-level compatibility = BACKWARD (for adding fields) during minor changes, and FULL for more conservative changes.- For the Parquet lake, each physical table write included a table_version metadata column; logical stable views (table_current) exposed the canonical schema to consumers.Migration plan:1. Design change: propose schema diff in PR with compatibility rationale.2. Create new Avro schema with default values for added fields (ensures backward-compatibility).3. Deploy producer changes to write new schema but continue populating old fields for renamed columns (dual-write where necessary).4. Publish schema to registry in a timed release window.5. Update ETL consumer code behind feature flags to accept new fields (parse with schema registry client).6. Gradually switch consumers: unit tests → staging integration → canary on a subset of topics/jobs → full rollout.7. For incompatible changes (rename/type change), introduce a new subject (topic.v2) and create view/transform jobs to populate it; deprecate v1 after consumers migrate.Ensuring backward/forward compatibility:- Added fields had defaults so old consumers could ignore them (backward-compatible).- For forward compatibility (older producers to newer consumers), we avoided removing fields and used tolerant parsers; where necessary consumers used schema resolution via registry to handle missing fields.- For renames, we added the new field and kept old until all consumers migrated, then removed in a major-versioned change.Testing consumer compatibility before rollout:- Contract tests: each consumer had automated schema contract tests that fetched both v1 and v2 schemas from the registry and validated deserialization using consumer code.- Integration tests: end-to-end pipelines ran in staging with synthetic records for both schemas.- Canary runs: ran downstream analytics and model scoring on a 5% traffic shadow; compared outputs (diff metrics) and alerting thresholds.- Monitoring: lineage and data-quality checks (null rates, distribution shifts) and alerts for schema-related parse errors.Outcome/learning: This approach let us add fields and make non-breaking renames with zero customer-facing incidents. Major incompatible changes require explicit versioning, views/transforms, and a coordinated consumer migration plan.
HardSystem Design
92 practiced
Walk through a migration you led from on-prem Hadoop to a cloud platform (choose AWS/GCP/Azure). Cover discovery, choosing transfer vs reprocessing, data transfer tooling, refactoring jobs, validating data parity, cutover plan, rollback strategy, and observed cost/performance changes post-migration.
Sample Answer
Requirements & constraints:- Migrate 500 TB HDFS + nightly Spark ETL, low-latency analytic queries, strict parity for historical datasets, 2-month migration window, minimal disruption.High-level plan:- Target: AWS S3 (data lake) + EMR/Glue for processing, Redshift for analytics.- Goals: decide between bulk transfer vs reprocessing, preserve lineage, improve cost/ops.Discovery:- Inventory data types, formats, schemas, ownership, job DAGs, run-times, dependencies, and ACLs.- Measured HDFS block layout, compression, small-file ratio, and peak network egress limits.- Identified ~70% of datasets were immutable historical snapshots suitable for bulk transfer; 30% were regenerated or cheap to reprocess.Transfer vs Reprocess decision:- Bulk-transfer for cold immutable datasets to avoid re-running expensive historical pipelines.- Reprocess for datasets derived from live sources or where upstream logic had changed (ensures correctness).Data transfer tooling:- For 500 TB initial bulk: used AWS Snowball for physical import to S3 (speed, reliability).- For ongoing delta and replication: used DistCp (Hadoop) to S3 via s3a connector for large bulk copies, and AWS DataSync for frequent small-file syncs.- Cataloging: used AWS Glue crawlers to populate Glue Data Catalog and migrate Hive metastore (export/import).- Security: preserved IAM mappings, encrypted S3 with KMS, retained audit logs in CloudTrail.Refactoring jobs:- Rewrote Hadoop MapReduce jobs as Spark where necessary, optimized for EMR with dynamic allocation and instance fleets.- Converted HiveQL to Glue/EMR Spark SQL; standardized reads/writes to Parquet with partitioning strategies (date, region) and compaction jobs to mitigate small-file issues.- Modularized ETL DAGs in Airflow, added schema enforcement (avro/protobuf) and unit tests for transformations.Validating data parity:- Row-count and checksum comparisons for bulk datasets (Parquet file-level checksums).- Record-level sampling with deterministic hashing on primary keys to compare aggregates.- End-to-end reconciliation on critical metrics (DAU, revenue) using dual-run approach for a 2-week parallel window.- Automated validation suite: schema, row counts, key aggregation diffs with alert thresholds.Cutover plan:- Phased cutover per dataset group: cold historical → analytical datasets → near-real-time.- For each phase: freeze writes to source HDFS, final DistCp sweep, run validations, switch consumers to S3 endpoints and update catalog.- DNS/endpoint cutover and update Airflow connections; run smoke tests and monitor SLA KPIs for 48 hours.Rollback strategy:- Maintain source HDFS and metadata until post-validation sign-off.- For consumer failures, revert Airflow DAGs to original HDFS connector and roll back downstream schema changes.- Keep incremental replication window to replay data into HDFS if roll-forward needed.Observed cost & performance changes:- Cost: storage moved from HDFS op-ex to S3 with tiering — total storage cost down ~25% using S3 Intelligent-Tiering + infrequent access for cold data; EMR compute cost initially rose due to conservative instance sizing but optimized with spot fleets and right-sizing, net neutral after 3 months.- Performance: query latencies on partitioned Parquet improved 2–4x for analytic workloads; job run-times reduced 30% after Spark optimizations and using S3 Select for some workflows.- Operational: improved reliability, faster provisioning, easier DR, and simpler metadata management via Glue; trade-off—needed tighter governance to control S3 egress and prevent cost leakage.Key learnings:- Early inventory and classification drives transfer strategy.- Invest in file format, partitioning, and compaction to avoid small-file pain on object storage.- Automate validations and keep a fall-back path for rapid rollback.
HardSystem Design
66 practiced
Architect a multi-region, near-real-time data pipeline with at-least-once delivery and cross-region failover for analytics relied on by global customers. Describe components (ingest, transport, processing, storage), replication strategy, ordering and consistency concerns, how you would test failover, and cost trade-offs.
Sample Answer
Requirements:- Ingest from global clients with <5s end-to-end latency, near-real-time analytics, at-least-once delivery, cross-region failover, eventual consistency acceptable but some streams require ordering (per-customer/session).High-level architecture:- Regional edge ingest → regional durable queue → regional stream processing → regional serving/storage + cross-region replication → global analytics layer.Components:1. Ingest: API Gateway / CDN + regional collectors (Kinesis Data Streams / Pub/Sub / Kafka Ingress) to terminate client connections close to users to minimize latency.2. Transport: Partitioned append-only log (managed Kafka/Kinesis Pub/Sub) per region. Use partitioning key = customer_id or session_id to preserve ordering where needed.3. Processing: Stateful stream processors (Flink/Spark Structured Streaming) running in each region, consuming the regional topic, performing enrichment, idempotent transformations, emitting to both local storage and outbound replication topics.4. Storage: Hot store = regional OLAP sink (clickhouse, Druid, BigQuery streaming tables) for low-latency queries; Cold store = object storage (S3/GCS) with partitioned parquet for batch analytics.5. Global analytics: Materialized aggregate stores built by a global job consuming replicated streams; or query federation across regions.Replication strategy:- Asynchronous incremental replication of validated events between regions using the append-only logs. For each regional partition, mirror to a global topic (MirrorMaker / Dataflow Replication) with durable offsets.- Use active-active writes: clients write to nearest region; each region replicates to others. Resolve conflicts via last-writer-wins with vector clocks or include logical timestamps + source-region precedence for deterministic merge when needed.Ordering & consistency:- Ordering guaranteed within a partition key per region. Cross-region ordering is not strictly guaranteed—if strong global ordering required, route that traffic through a single canonical region or use a global sequencer (adds latency).- At-least-once delivery: processors must be idempotent (dedupe via event_id + deterministic upserts). Maintain a small dedupe window in state store.- Exactly-once semantics achievable within single-stream-processing framework (Flink TWO-PHASE COMMIT) to sinks, but cross-region replication usually yields at-least-once.Failover testing:- Automated chaos tests: region outage simulation by disabling regional ingress and network egress; verify that clients still write to next-nearest region (DNS/CDN failover).- Consumer failover: kill processing cluster in region and ensure replicated streams in other regions are consumed and materialized correctly.- Data integrity checks: end-to-end reconciliation jobs comparing event counts, checksums, and high-water marks between regions after failover; SLA tests for recovery time and data completeness.- Runbook and drills: exercise manual cutover, validate dashboards and rollback.Operational concerns & cost trade-offs:- Multi-region active-active with full replication doubles storage/egress costs and increases operational complexity; cheaper alternative is active-passive with cross-region backup (lower cost, higher RPO/RTO).- Strong global ordering requires a sequencer → increased latency and single point of failure; use only for streams that need it.- Use managed services to reduce ops cost (Cloud Pub/Sub / Kinesis / BigQuery) but accept vendor egress costs and less control.- Tune retention and compacting to balance replayability vs storage.- Monitor costs by tiering: hot short-retention streaming sinks for realtime, cheaper object storage for long-term.Why this design:- Edge ingest minimizes client latency. Partitioned logs give predictable ordering and easy replication. Idempotent processing + reconciliation supports at-least-once delivery and correctness. Asynchronous replication and active-active support low-latency global analytics while offering clear trade-offs between cost and consistency.
MediumSystem Design
60 practiced
Design an end-to-end architecture for a pipeline that ingests 1 TB/day and serves near-real-time analytics. Include ingestion methods, message queueing, processing (batch and streaming components), storage choices (lake vs warehouse), serving layer, and assumptions about throughput and latency. Call out partitioning and compaction strategies.
Sample Answer
Requirements & assumptions- Ingest 1 TB/day (~12 MB/s avg). Assume peak bursts up to 5x (~60 MB/s). Near-real-time analytics target: <5s for event availability in streaming metrics, <1 minute for rolling aggregates. Retention: raw + curated for months-years. Strong eventual consistency; ACID for curated tables.High-level architecture1. Ingestion- Clients → lightweight SDKs / File drops → API gateway (autoscaled) or multipart upload to object store for bulk.- For events: produce to Kafka (or cloud Pub/Sub/Kinesis) with partitioning by event timestamp + tenant/id to allow parallelism.2. Message queueing- Kafka cluster (or managed): replication factor 3, topic partition count sized for throughput (e.g., 100 partitions to support parallel consumers). Schema Registry (Avro/Protobuf) for evolution.3. Processing- Streaming layer: Apache Flink or Spark Structured Streaming consuming Kafka, doing stateless enrichment, windowed aggregations, dedup (using event-id + event-time watermark), and writing: - Low-latency serving store (ClickHouse / Druid / Materialized Kafka topics) for sub-second reads. - Append to raw object store in micro-batches (Parquet/ORC) via Iceberg/Delta for transactional writes.- Batch layer: Daily Spark jobs on EMR / Dataproc to run heavier transforms, backfills, ML feature generation, compaction and re-partitioning; write curated tables into a data warehouse and table format.4. Storage choices- Data lake (S3/GCS/Azure Blob) for raw & processed parquet using Iceberg/Delta to support ACID, time-travel, and efficient compaction.- Data warehouse (BigQuery / Snowflake / Redshift) for high-concurrency analytical SQL and BI dashboards (curated, columnar, optimized for ad-hoc queries).- OLAP store (Trino/Presto) can query lakehouse directly for cost-effective access.5. Serving layer- For near-real-time dashboards: Druid / ClickHouse for fast aggregates with ingestion from streaming layer.- For ad-hoc and historical analytics: Warehouse (Snowflake/BigQuery).- For user-facing low-latency lookups: Redis or Cassandra for point queries.Partitioning & compaction strategies- Partition by ingestion_date (yyyy/mm/dd) + service/tenant (multi-level) for both lake and warehouse; include partition pruning keys (date + region).- Within partitions use bucket/cluster by user_id or order_id to improve predicate performance.- Prevent small files: streaming writes collect into time/size-based micro-batches (e.g., 1–5 min or ~128–512 MB files) then run background compaction via Spark jobs using Iceberg/Delta rewrite to merge small files.- Auto-compaction policies: size threshold, small-file detector, and scheduled compaction during off-peak.- Retention & lifecycle: tier raw data to cheaper storage after 30/90 days; vacuum/expire snapshots.Operational concerns- Monitoring: lag, throughput, consumer offsets, compaction metrics, SLAs.- Exactly-once where needed: use Kafka transactions + Flink checkpoints + Iceberg/Delta sinks.- Backpressure & scaling: autoscale consumers based on lag, increase partitions for throughput.- Security & governance: encryption at rest/in transit, IAM, Glue/Atlas catalog, data quality checks, lineage.Trade-offs- Lakehouse (Iceberg/Delta) + warehouse combo gives cost-effective long-term storage + performant BI. Using Druid/ClickHouse adds cost but meets sub-second rollups. Choosing managed cloud services reduces ops burden.
Unlock Full Question Bank
Get access to hundreds of Project Walkthrough and Contributions interview questions and detailed answers.
