Technical Project Stories Questions
Prepare two to four hands on technical project narratives that demonstrate engineering depth, architectural thinking, and measurable outcomes. For each project describe the business problem, system architecture or design choices, trade offs evaluated, scaling and reliability challenges, instrumentation or observability decisions, implementation details and technologies used, your specific responsibilities, and the measurable results achieved. Be prepared to dive deep on technical decisions, show diagrams or component flows if asked, describe how technical debt and operational run book items were managed, and explain how the work influenced broader engineering practices. Include examples across front end, back end, infrastructure, data, and security as relevant to the role.
MediumTechnical
39 practiced
Explain how you managed schema and metadata for analytics datasets to support ad-hoc queries at scale. Discuss partitioning strategies, data formats (Parquet/ORC/Avro), file sizes, and how you balanced read performance vs write cost.
Sample Answer
Situation: At my last role I owned the analytics data lake that served hundreds of analysts running ad-hoc SQL on multi-TB event data. We needed fast reads for exploratory queries while keeping ingestion cost and pipeline latency low.Approach/Task: I designed schema + metadata and operational patterns to optimize for predicate pruning, columnar reads, and manageable file counts.Actions (what I did and why):- Partitioning: I partitioned by date (day) as the primary partition to satisfy most time-based queries. For secondary pruning I used a small set of low-cardinality columns (e.g., region, product_category) as directory-level partitions only when cardinality < 100. For high-cardinality dimensions (user_id, session_id) I avoided partitioning and relied on clustering (Iceberg/Delta) or Z-ordering to colocate related rows.- File formats: Chose Parquet for analytics (columnar, wide tool support, predicate pushdown, dictionary encoding). For heavy write streams where schema evolution was frequent, we ingested in Avro then converted to Parquet during a compaction job. Considered ORC where columnar compression/row-indexing benefits were needed on Hive-heavy stacks.- File sizes: Targeted 128–512 MB compressed files for Parquet to maximize scan throughput and reduce metadata overhead. For streaming micro-batches we wrote smaller files (~50–100MB) then used periodic compaction (Spark/EMR jobs) to merge into target sizes.- Metadata/catalog: Registered datasets in Glue/Hive metastore and later moved hot tables to Iceberg/Delta to get ACID, partition evolution, and fast metadata scans. Used table-level statistics, min/max per column, and bloom filters for selective queries.- Read vs write cost balance: - To minimize write latency and cost, ingestion wrote small files and asynchronous compaction consolidated them. This kept streaming pipelines responsive while preserving read performance. - For tables with heavy ad-hoc reads, I ran scheduled compactions and materialized common aggregates. I also used Parquet with Snappy compression for fast decompression (good tradeoff between CPU and size). - Where interactive latency mattered, I leveraged caching (Presto/Trino result cache or Spark cache) and maintained summary tables to avoid full scans.- Monitoring and maintenance: Implemented metrics for file count, average file size, partition growth, and query scan bytes. Alerts triggered compaction or re-partition jobs when small-file thresholds exceeded.Result/Learning: This approach reduced average bytes scanned per ad-hoc query by 5–10x (via partition pruning + column pruning), kept query latency in interactive range, and kept ingestion cost predictable. Key trade-offs: aggressive partitioning can bloat metadata and slow writes; too few partitions cause large scans. Using columnar formats plus clustering and periodic compaction gave the best balance for our workload.
HardTechnical
79 practiced
Describe how you instrumented and debugged a production issue related to message ordering or duplicate processing in a distributed pipeline. Include the root cause analysis steps, logs/traces used, fixes applied, and any follow-up changes to prevent regression.
Sample Answer
Situation: A critical analytics pipeline started showing duplicate user events and out-of-order session actions in downstream aggregates, causing inflated MAU and session-metrics within customer dashboards.Task: As the data engineer on-call I needed to identify why ordering/duplicates occurred, fix production impact, and prevent recurrence.Action:- Triage & instrumentation - Pulled consumer group state with kafka-consumer-groups and broker logs to see partition offsets and rebalances. - Enabled request/response correlation IDs at the producer side and pushed them into message headers; added similar correlation logging on consumers. - Turned on OpenTelemetry traces for the producer -> Kafka -> consumer path to measure retries, latencies and which nodes produced rebalances.- Root-cause analysis - Found pattern: a partition leader flapped during a burst; producers retried and some messages were re-sent before leader acknowledged; consumers used at-least-once processing and committed offsets asynchronously, causing duplicates and apparent reordering across a short window. - Also discovered our producer did not use idempotent/transactional semantics and consumer commit logic could commit before downstream write finished.- Fixes applied - Short-term hotfix: added a lightweight deduplication layer in the consumer using an in-memory Redis set keyed by event_id with a TTL to stop immediate duplicates and rolled back any partial aggregates to correct counts. - Production fix: enabled Kafka idempotent producers and moved critical writes into Kafka transactions so produce+offset-commit became atomic (enable.idempotence=true, transactional.id, commitTxn/abortTxn flow). Changed consumer to commit offsets only after successful transaction/DB write. - For ordering sensitivity, we ensured partition key was correctly computed (session_id) so related events land in same partition; fixed a bug where one producer was hashing user_id inconsistently.- Validation - Replayed a synthetic load with controlled leader failovers to confirm no duplicates and maintain ordering; monitored end-to-end traces to confirm single hop per event.- Follow-up & prevention - Added dashboards/alerts: duplicate-rate, consumer-rebalance frequency, producer-retry rate. - Added schema requirement: every event must include sequence_number and event_id; schema-registry validation enforced it. - Added integration tests and chaos runs (broker restarts, network partitions) in CI to ensure transactional path holds. - Updated runbook: steps to triage duplicates, how to enable temporary dedupe, and rollback instructions. - Documented idempotency guarantees and ownership for producers so future changes keep partitioning and transactional settings correct.Result: Duplicate events dropped to near zero, ordering restored for session-scoped metrics, and incident recurrence risk reduced via instrumentation, transactional guarantees, and operational runbooks.
HardSystem Design
39 practiced
Discuss a project where you had to design SLIs/SLOs for a data product (e.g., freshness, completeness, query latency). Define the SLI, the SLO target, error budget, and how the team used the error budget to prioritize work and incidents.
Sample Answer
Requirements & constraints:- Data product: daily customer-events table powering analytics and ML features.- Non-functional focus: freshness (how up-to-date), completeness (data volume/coverage), and query latency for BI/feature-store reads.- Consumers: analysts (ad hoc queries), ML pipelines (near real-time features). Must balance availability vs. development velocity.High-level approach:- Define concrete SLIs per consumer need, set SLO targets tied to business impact, compute an error budget, instrument observability, and enforce budget-driven prioritization.SLIs, SLOs, error budgets1) Freshness SLI- Definition: Fraction of published table partitions whose latest event timestamp is within 1 hour of wall-clock at evaluation time.- SLO: 99% of partitions fresh within 1-hour window, evaluated hourly over a 30-day rolling window.- Error budget: 1% of partitions * 30 days ≈ allowable freshness violations per month.2) Completeness SLI- Definition: Ratio of received events to expected events per source per day, where expected = historical baseline ± tolerance or schema-enforced counts for finite sources.- SLO: 99.9% of expected daily volume per source over 30 days.- Error budget: 0.1% of total expected daily volume * 30 days.3) Query latency SLI- Definition: 95th-percentile query latency for a set of representative BI/feature-store queries.- SLO: p95 < 2 seconds for BI dashboards; p95 < 200ms for feature-store RPCs, measured daily.- Error budget: allowable p95 breaches = 1% of samples per month.Instrumentation & implementation- Metrics emitted by ingestion (Kafka offsets, Spark job commit timestamps, row counts) and serving layer (BigQuery / Redshift / Snowflake query durations).- Tools: Prometheus for real-time metrics, Grafana dashboards, DataDog for SLO burn alerts, Great Expectations for per-batch data quality checks, and a lightweight service that computes daily SLO compliance and error budget remaining.- Alerts: multi-level—warning at 25% budget consumed (burn rate > 2), critical at 50% and immediate suspension of feature work at 100% burn.Operationalizing error budget- Define burn-rate windows (1h / 24h / 7d) to detect fast burns. Use automated alerting to on-call.- Runbook actions by severity: - Warning (low burn): Notify owners, increase monitoring granularity, backfill checks. - Critical (high burn, >50% monthly budget): All non-essential releases/features frozen; roll back recent deployments; prioritize reliability tickets. - Exhausted (100%): Halt new features, focus 100% on remediation; declare incident if consumer impact confirmed; update stakeholders.- Prioritization example: A model feature rollout was scheduled when completeness SLO burn hit 60% due to an upstream source regression. We paused the rollout, diverted two engineers to fix Kafka producer batching and implemented backpressure, and delayed a performance optimization sprint. This preserved consumer SLAs and prevented larger outages.Post-incident & governance- Postmortem with SLO impact analysis, quantify budget spent, root cause, corrective actions (producer-side retries, schema validation, partition-level backfills).- Quarterly SLO review with stakeholders: adjust thresholds if business needs change, tighten/loosen SLOs based on cost-benefit.- Tie SLOs to contractual SLAs for external teams; internal error-budget policy enforces developer incentives (feature freezes, prioritization).Trade-offs & rationale- Chose p95 for latency to balance tail sensitivity vs noise; used ratio-based completeness to handle variable-volume sources.- Higher SLOs cost more (cost vs freshness/throughput). Error budget enables data-driven decisions: spend budget for planned changes or prioritize reliability when budget burns unexpectedly.Outcome (example metrics)- After implementing these SLOs and burn-driven process: freshness compliance rose from 95% to 99.3% within 3 months, incidents causing downstream model retraining dropped by 70%, and developer rollbacks due to data issues fell by 60%.
HardTechnical
48 practiced
For a project that used message queues (Kafka, Pulsar, SQS), explain the partitioning key design, consumer group strategy, and how you handled hotspots or rebalancing events. Include how you validated partitioning choices under real traffic patterns.
Sample Answer
Situation: On a high-throughput ingestion pipeline using Kafka (millions of events/day) I needed a partitioning and consumer strategy that preserved ordering where required, avoided hotspots, and survived rebalances with minimal disruption.Partitioning key design:- Classify messages by ordering requirement. For events requiring strict per-entity order (user actions, order updates) use entity_id as the logical key. For high-cardinality hot entities, apply key-bucketing: hash(entity_id) % N_buckets, where N_buckets << partitions, plus a secondary shard field (region/date) to spread load.- For analytics-only events where order doesn’t matter, use round-robin to maximize parallelism.- Example custom partitioner (python kafka-python):Consumer group strategy:- Separate consumer groups by workload: one group for order-sensitive stream processors (single-threaded per partition), another autoscaling Spark Structured Streaming job for bulk analytics reading the same topic.- Use fixed mapping: reserve a set of partitions for critical consumers by co-locating them and tuning max.poll.records to control processing time.- Use static membership (group.instance.id) and Kafka’s cooperative-sticky rebalance to reduce churn on deploys.Handling hotspots and rebalancing:- Detect hotspots via per-partition metrics (bytes/sec, records/sec, consumer CPU, partition-lag). When a partition becomes hot: - Increase partitions (when possible) + re-partition topic using a controlled migration (mirror with MirrorMaker2 or create a new topic and backfill). - Use key bucketing/hot-key routing in producer to distribute heavy keys across buckets. - Apply backpressure: producers rate-limit or sample non-critical events.- For rebalances: - Use cooperative rebalancer to avoid mass partition movement. - Tune session.timeout.ms and max.poll.interval.ms to match processing latencies; implement checkpointing and pause/resume so long processing doesn't trigger revokes. - Implement graceful shutdown handlers to complete inflight work before leaving group.Validation under real traffic:- Shadow traffic: replicate production traffic to a staging cluster (topic clone) and run target consumer topology while measuring per-partition throughput/lag and latencies.- Synthetic stress tests: produce skewed hot-key distributions to verify bucketing and consumer scaling behavior.- Monitoring: dashboards for per-partition throughput, consumer lags, GC/CPU, and end-to-end latency. Alerts on partition skew (stddev of records/sec across partitions).- Post-rollout validation: run A/B for new partitioning (new topic) for a subset of traffic, compare tail latencies and lag; if regressions, rollback.Result/learning: Using key-bucketing plus cooperative rebalances and static membership minimized ordering violations and reduced hot-partition incidents by ~70% in production. Validating with shadow traffic caught edge-case skews before customer impact.
python
def partitioner(key_bytes, all_partitions, available):
# simple bucketing to avoid single-hotkey overload
key = key_bytes.decode()
if is_hot(key):
return hash(key) % HOT_BUCKETS + HOT_OFFSET
return hash(key) % len(all_partitions)MediumTechnical
53 practiced
For a project, describe how you did capacity planning and forecasting: expected growth, provisioning strategy, benchmarks used, headroom targets, and how often you revisited estimates to avoid over/under-provisioning.
Sample Answer
Situation: At my previous company I owned the ETL platform feeding analytics and ML models. We were onboarding new product telemetry and expected 3x data volume growth over 12 months.Task: Create a capacity plan and forecasting cadence so we could provision compute/storage without wasted spend or pipeline failures.Action:- Forecasting: I built a baseline using historical ingest rates (events/sec, GB/day) and growth drivers (new features, user growth, retention curves). I used exponential smoothing on the last 6 months and created three scenarios: conservative (1.5x), expected (3x), and aggressive (5x).- Benchmarks: I ran load tests with representative datasets through our Spark jobs (input sizes, partition counts) to measure CPU, memory, shuffle IO, and job latency. For storage I measured compaction and read latencies on Parquet files and S3 throughput limits.- Provisioning strategy: For compute (EMR/Kubernetes), I used a hybrid approach—right-sized reserved instances for steady baseline + autoscaling spot/ondemand for peak loads. For storage used lifecycle policies and columnar formats to control costs.- Headroom targets: Set headroom at 40% CPU and 30% memory above expected scenario to absorb bursts and reduce OOMs; targeted 99th percentile job latency SLAs.- Cadence & monitoring: Revisited forecasts monthly and re-run benchmarks quarterly or after major feature rollouts. Implemented dashboards (Prometheus/Grafana + CloudWatch) for ingest rate, job failure rate, queue depth, and cost per GB; alerts triggered if usage trended >15% above forecast.Result: We scaled to 3x volume without incidents, reduced emergency scaling costs by 25% versus ad hoc provisioning, and improved pipeline success rate to 99.6%. Key learning: combine data-driven forecasts with automated monitoring and periodic re-benchmarking to balance cost and reliability.
Unlock Full Question Bank
Get access to hundreds of Technical Project Stories interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.