MediumTechnical

32 practiced

Discuss trade-offs between storing flexible menu data as normalized relational tables versus JSONB blobs in PostgreSQL for DoorDash. Consider update patterns, schema flexibility (restaurant-specific fields), query complexity (filtering by attributes), indexing, and ETL for analytics.

Sample Answer

Situation (context): DoorDash menus vary a lot across restaurants — different item attributes, options, and frequent updates. As a data engineer you must decide between normalized relational tables and JSONB blobs in PostgreSQL.

Trade-offs — Normalized relational model:- Strengths: Strong schema, referential integrity, compact join-friendly representations for items, options, prices. Easy to enforce constraints, run joins for analytics, and index individual columns (B-tree).- Weaknesses: Rigid when restaurants add custom fields (requires migrations); many small tables and joins increase complexity and cost for high-cardinality attributes and frequent schema churn.- Update patterns: Efficient for atomic updates (update a row/column). But schema changes are costly if new per-restaurant attributes appear frequently.- Query complexity: Filtering on standard attributes is straightforward and fast; complex option-attribute queries need multiple joins.

Trade-offs — JSONB blobs:- Strengths: Flexible schema per-restaurant; store entire menu/state in one column; easy to ingest variable records without migration. Good for write-heavy, schema-fluid ingestion.- Weaknesses: Harder to enforce integrity, harder to query and JOIN at scale. Filtering by attributes requires GIN indexes (jsonb_path_ops or jsonb_ops) or expression indexes on extracted keys. Complex predicates can be slower and less predictable.- Update patterns: Rewriting large blobs for small changes can be expensive; partial updates possible with jsonb_set but can get messy and cause write amplification.- Query complexity: Ad-hoc filtering (e.g., "vegetarian items under $10 with size 'large'") becomes complex and may need indexes on specific paths or extracted columns.

ETL & analytics considerations:- Normalized tables map well to star schemas and incremental CDC; easier for downstream aggregation and joins with order data. JSONB requires extraction/parsing before analytics — add transform steps in ETL (Spark/DBT) to normalize important fields.- Recommendation: Hybrid approach — keep canonical core fields (item_id, restaurant_id, price, active) normalized for fast joins and analytics; store extensions/custom attributes in JSONB. Create expression indexes on hot JSON paths and maintain regular ETL jobs that flatten JSONB into analytics tables (or materialized views) for reporting. Use CDC (logical replication / Debezium) to feed both raw events and normalized analytics tables. This balances flexibility, query performance, and analytical tractability.

EasyTechnical

23 practiced

Design a normalized OLTP schema to support DoorDash order processing with high transactional throughput. Provide table-level descriptions for orders, order_items, customers, and payments including primary keys, foreign keys, necessary constraints, and recommended indexes for ~10k writes/sec. Explain choices for PKs (UUID vs sequence), FK constraints, and index design to preserve ACID while keeping writes fast.

Sample Answer

Requirements clarified: OLTP for DoorDash-like orders, normalized, ACID, ~10k writes/sec in Postgres.

Schema (table, PK, FKs, constraints, indexes):

1) customers- PK: customer_id BIGSERIAL PRIMARY KEY- cols: name, email UNIQUE NOT NULL, phone, created_at TIMESTAMPTZ DEFAULT now()- Indexes: PK B-tree on customer_id, unique index on email- Rationale: small lookup-by-id, sequential PK to minimize b-tree page splits on insert.

2) orders- PK: order_id BIGSERIAL PRIMARY KEY- cols: customer_id BIGINT NOT NULL, restaurant_id BIGINT, status VARCHAR NOT NULL, total_amount NUMERIC(10,2), created_at TIMESTAMPTZ DEFAULT now()- FKs: FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT- Indexes: PK, index on (created_at) PARTITION or BRIN for time queries, index on (customer_id, created_at) for customer history- Rationale: sequential PK for fast inserts; time-based partitioning (daily) to scale writes and vacuum.

3) order_items- PK: order_item_id BIGSERIAL PRIMARY KEY- cols: order_id BIGINT NOT NULL, menu_item_id BIGINT, qty INT, price NUMERIC(10,2)- FKs: FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE- Indexes: index on order_id (BTREE) for joins; consider clustered physical ordering by order_id (fillfactor tuned)- Rationale: many items per order, FK with cascade to keep referential integrity.

4) payments- PK: payment_id UUID PRIMARY KEY DEFAULT gen_random_uuid()- cols: order_id BIGINT UNIQUE NOT NULL, amount NUMERIC, method VARCHAR, status, processed_at TIMESTAMPTZ- FKs: FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE RESTRICT- Indexes: PK on payment_id, unique index on order_id- Rationale: UUID for payments because external systems may generate IDs and for idempotency across services.

Design choices and tips:- Use BIGSERIAL for high-throughput local inserts to avoid random I/O; sequences are light-weight and cacheable.- Use UUIDs selectively where external uniqueness or idempotency is required; otherwise use sequential for write performance.- Enforce FK constraints to preserve ACID; keep them but ensure referenced PKs are indexed (they are).- Tune fillfactor (e.g., 70-90%) on hot tables and avoid wide transactions; keep transactions short to reduce lock contention.- Partition orders by date to spread write IO and make vacuuming cheaper.- Use connection pooling and batch writes where possible; monitor bloat and autovacuum.- Consider async denormalized replicas (CDC) for analytics to avoid OLTP contention.

MediumTechnical

27 practiced

How would you model driver availability and assignments in the database? Should availability be a column in the drivers table, a separate status table, or managed in an external in-memory store? Discuss trade-offs for consistency, read/write performance, and real-time requirements for matching.

Sample Answer

Situation: We need a model to represent driver availability for two consumers: (1) the real-time matching engine (low-latency reads/updates), and (2) analytics / historical reporting (durable, queryable).

Recommendation (hybrid):- Keep availability state in an external in-memory store (e.g., Redis) for real-time matching and fast updates. Use small keys like driver:{id}:status with TTL for implicit stale detection and pub/sub or streams for notifications. Redis gives sub-ms reads/writes and atomic ops (SET, HSET, Lua scripts) to handle concurrent assignments.- Persist every availability/change event to the primary datastore (drivers table or separate status table) via an append-only event log (Kafka) or CDC to a transactional DB. The durable table should be event-sourced or have a status history table for auditing/analytics, not used for hot matching.

Trade-offs:- Consistency: In-memory is eventually consistent with DB. Mitigate with strong ordering guarantees: write to Redis first (fast), emit event to Kafka, consume and persist; or write to DB then update cache using write-through cache pattern. Use idempotent updates and version/timestamp to resolve races.- Read/write performance: Redis for high QPS and low latency. DB writes are slower but batched/async for throughput. Avoid hitting DB for every match decision.- Real-time requirements: In-memory store meets real-time SLA. Use pub/sub to push assignment events to matching service and drivers.- Complexity: Hybrid adds operational complexity (monitoring, failover). Plan for cache warm-up and reconciliation (periodic full sync or snapshot validation).- Durability & analytics: Event log + persisted status/history enables downstream ETL into data warehouse for reporting.

Example flow:1. Driver toggles available -> update Redis key (atomic), publish event to Kafka.2. Kafka consumer persists to status_history table; CDC pipelines populate data warehouse.3. Matching engine reads Redis; on assignment it atomically sets key to busy using Lua script to avoid double-booking, publishes assignment event.

Best practices:- Use TTL and heartbeat to detect offline drivers.- Include version/timestamp on updates.- Implement reconcilers to fix drift.- Monitor metrics (cache hit ratio, lag between Redis/Kafka/DB).

EasyTechnical

23 practiced

Explain star schema versus snowflake schema for DoorDash analytics. For a reporting use case (average delivery time by restaurant over months), argue which schema you would choose and why. Cover dimension granularity, join complexity, ETL cost, storage, and query performance trade-offs.

HardSystem Design

28 practiced

Design a Hybrid Transactional/Analytical Processing (HTAP) approach for DoorDash so that near-real-time analytics (latency of a few seconds to a minute) can run without impacting OLTP throughput. Recommend technologies, schema strategies (single schema vs read-optimized replicas), and how to ensure transactional guarantees aren't compromised.

Sample Answer

Requirements & constraints:- Functional: OLTP for orders/payments must remain low-latency; analytics queries need near-real-time (seconds→1 minute).- Non‑functional: Strong transactional semantics for business-critical ops, high ingest (orders, courier events), multi-region availability, cost control.

High-level architecture:1. OLTP DB (primary): YugabyteDB (Postgres-compatible, distributed, MVCC) hosting live transactional schema for orders, payments, couriers.2. Change Data Capture (CDC) stream: Yugabyte changefeeds (or Debezium if using managed Postgres) publish WAL changes to Kafka/Kinesis.3. Stream processing layer: Apache Flink / Spark Structured Streaming / Kafka Streams consumes CDC, performs lightweight enrichment, dedup, and writes to: - Read-optimized analytical store (clickhouse / Snowflake / Redshift / BigQuery) for heavy aggregation queries. - Near-real-time materialized views (Presto/Trino or Dremio) served from OLAP store or from a dedicated HTAP engine.4. Low-latency serving layer: For sub-minute dashboards/alerts, maintain read replicas or materialized tables in a fast analytical DB (ClickHouse or Yugabyte read-replicas tuned for analytical reads).5. API/Query layer: Trino for federated queries across OLTP and OLAP if needed.

Schema & replication strategy:- Keep normalized OLTP schema optimized for transactions.- Do NOT run heavy analytical queries against primary schema.- Use CDC to project and transform into read-optimized schemas: wide denormalized tables, pre-aggregations, time-series partitions, and materialized views in the analytical store.- Maintain read replicas only for low-cost transactional read scaling (strictly for OLTP reads); analytical workloads go to the read-optimized stores.

Ensuring transactional guarantees:- Source-of-truth remains Yugabyte primary; CDC reads WAL with publish order to preserve commit order and transaction boundaries.- Use exactly-once or idempotent writes in stream processors: Flink with checkpointing and Kafka transactional writes, or write dedup keys (txn_id + op_seq) to analytical sink.- Preserve causal consistency for derived aggregates by watermarking and windowing in stream processing to avoid partial updates.- For analytics requiring point-in-time consistent snapshots, rely on consistent snapshot export (Yugabyte snapshot/consistent CDC offset) and label downstream datasets with CDC offsets.

Latency vs throughput trade-offs:- Keep CDC->stream processing lightweight (enrichments + small joins) to hit seconds; heavier batch aggregations run on minute windows or micro-batches.- Tune retention/compaction in Kafka and batch sizing to trade latency for throughput.

Operational concerns:- Monitoring: end-to-end lag (CDC lag, stream processing latency, sink lag), duplicate rates, and checkpoint health.- Backpressure & throttling: autoscale stream processors; if sink is slow, route to cold storage and mark downstream as eventual-consistent.- Testing: chaos test CDC offsets, simulate high write throughput and ensure exactly-once semantics hold.- Security & governance: enforce PII masking in stream layer, schema evolution management via registry.

Why this design:- Decouples OLTP from analytics via CDC so OLTP throughput unaffected.- Yugabyte provides distributed transactions and MVCC for strong guarantees; stream processing + idempotent sinks preserves correctness.- Read-optimized replicas and denormalized analytical schemas provide low-latency queries for dashboards while heavy analytics run on scalable OLAP systems.

Data Modeling for DoorDash Domain Questions

Sample Answer

Sample Answer

Sample Answer

Sample Answer

Sample Answer

Unlock Full Question Bank