Gowtham Potureddi

Posted on Jul 16

System Design Interviews for Data Engineers: A 10-Question Framework

#python #sql #interview #dataengineering

system design data engineering is the interview category every senior DE candidate faces — and it's fundamentally different from software system design because the trade-offs live in data volume (TB/day), freshness SLA (minutes vs hours), cost per TB scanned, and correctness semantics (exactly-once vs at-least-once) rather than QPS and latency budgets. Every senior DE candidate eventually gets a "design the analytics pipeline for a Twitter-scale product" prompt; knowing the 10-question framework, having three canonical design patterns ready to draw on a whiteboard, and being able to walk trade-offs out loud is what separates the offer from the polite pass.

The tour walks the five pillars — (1) how DE system design differs from SWE system design (data volume + freshness + cost + correctness, not QPS + latency + availability), (2) the 10-question framework covering V/V/V, SLA, storage, compute, latency, correctness, replay, evolution, ops, cost, (3) design pattern for real-time event pipeline with Kafka + Flink + Iceberg + exactly-once semantics, (4) design pattern for batch + BI stack with CDC + Snowflake + dbt + Metabase, and (5) design pattern for streaming ML feature store with online + offline stores + point-in-time correctness. Every section ships a Solution-Tail interview answer — code, trace, output, why-this-works with __concept__ underlines.

Practice on SQL library →, SQL optimization drills →, and SQL join drills →.

On this page

Why DE system design differs in 2026
The 10-question framework
Design pattern 1 — event pipeline
Design pattern 2 — batch + BI
Pattern 3 — streaming ML feature store
Cheat sheet — DE system design recipe list
Frequently asked questions
Practice on PipeCode

1. Why DE system design differs in 2026

The `system design data engineering` mental model — data axes vs SWE axes

The one-sentence invariant: software system design centres on QPS + latency + availability + consistency; DE system design centres on data volume (TB/day) + freshness (SLA in minutes) + cost per TB + correctness semantics — the primitives are different (Kafka topics vs REST endpoints, Iceberg tables vs Redis keys, Flink jobs vs microservices), the failure modes are different (schema drift + late-arriving data + cost blowup, not just service outage), and the whiteboard diagram looks different (partitioned append-only logs + columnar stores + orchestrated DAGs, not load balancers + service mesh).

Where DE system design shows up.

Senior DE loop — 45-60 min "design the pipeline for X" prompt.
FAANG DE / analytics engineering — designing data platforms at scale.
Data infra / platform — designing internal DE tooling.
Staff / principal DE — architecting cross-team data flows.

What SWE system design asks about.

QPS + latency + availability + consistency (CAP).
CDN + load balancer + service mesh.
Cache tier + DB read replicas.
Horizontal scale + sharding.

What DE system design asks about.

TB/day + freshness SLA + backfill window.
Storage tier (hot / warm / cold).
Compute engine choice (Spark / Snowflake / BigQuery / Flink).
Exactly-once / at-least-once semantics.
Schema evolution + backward compatibility.
Cost per TB scanned + cost per query.

What senior interviewers actually probe.

Volume math. 1 TB/day = 12 MB/sec average, 60 MB/sec peak (5× ratio).
SLA + freshness. Real-time (< 1 sec), near-real-time (< 1 min), hourly, daily.
Storage tier. Redis for < 10 ms, warehouse for analytics, S3 for archive.
Compute engine. Spark for TB batch, Flink for streaming, dbt for transforms.
Latency budget. Ingest + queue + compute + write + serve.
Correctness. Exactly-once (transactional sink), at-least-once + dedup.
Replay. Kafka retention, warehouse time-travel, Iceberg snapshot.
Schema evolution. Backward + forward compat; schema registry.
Ops. On-call rotation, monitoring, DLQ.
Cost triangle. Compute vs storage vs speed — pick two.

The 5-property design.

Volume-appropriate storage. Hot / warm / cold tiers matched to access frequency.
Latency-appropriate compute. Streaming for real-time, batch for daily reports.
Exactly-once at critical boundaries. Kafka + transactional sink or MERGE.
Replay-capable. Retention windows longer than typical incident duration.
Cost-observable. Per-team tags; alerts on burn rate.

Worked example — the "design analytics for a Twitter-scale product" prompt

Question. Twitter has ~500M daily active users emitting ~1B events/day. Design the analytics pipeline.

Structured approach.

Q1: V/V/V?
  Volume: 1B events/day ~= 100TB/month uncompressed.
  Velocity: 12K events/sec average, 60K peak (5x).
  Variety: heterogeneous events (tweet, like, reply, follow, ad-view).

Q2: SLA?
  Real-time for feed ranking (<1 sec).
  Near-real-time for user-facing metrics (<1 min).
  Hourly for internal dashboards.
  Daily for financial reporting.

Q3: Storage tier?
  Hot: Redis for feed cache (< 10 ms lookups).
  Warm: BigQuery or Snowflake for analytics (30 days).
  Cold: S3 + Iceberg for historical.

Q4: Compute?
  Streaming: Flink for real-time aggregation.
  Batch: Spark on K8s for nightly reprocessing.
  Warehouse: BigQuery for SQL analytics.

Q5: Latency budget?
  Ingest 100ms + Queue 200ms + Process 300ms + Write 200ms + Serve 200ms = 1s p99.

Q6: Correctness?
  Exactly-once for financial events (transactional sink).
  At-least-once + dedup for analytics events.

Q7: Replay?
  Kafka retention 7 days.
  Iceberg time-travel 90 days.

Q8: Schema evolution?
  Protobuf + Confluent Schema Registry.
  Backward + forward compat enforced in CI.

Q9: Ops?
  Prometheus + Grafana + PagerDuty.
  DLQ topic for malformed.

Q10: Cost?
  Storage tiers: Redis expensive, S3 cheap.
  Compute: Flink 24/7 costs; BigQuery per-query.
  Tag by team.

Walk each question with the interviewer.

Rule of thumb. Structure > improvisation. The 10-question walk signals senior thinking.

Worked example — the "design system for compliance-driven customer analytics" prompt

Question. SaaS product with strict compliance (GDPR, HIPAA); need customer analytics dashboards + ML features.

Structured approach.

Q1: V/V/V — 50GB/day per tenant, 100 tenants, 5TB/day total.

Q2: SLA — hourly refresh for dashboards; daily for ML.

Q3: Storage tier —
  Encrypted-at-rest S3 for raw.
  Snowflake for warehouse (RBAC + row-level security).
  Redis for online ML features (per-tenant partition).

Q4: Compute — dbt on Snowflake; Airflow K8s.

Q5: Latency — 15 min hourly refresh; 24h ML.

Q6: Correctness — dedup on hash; MERGE.

Q7: Replay — Snowflake Time Travel; dbt state.

Q8: Schema evolution — dbt tests + versioned models.

Q9: Ops — audit logs, GDPR delete pipeline.

Q10: Cost — per-tenant tag; Snowflake credits by warehouse.

Rule of thumb. Different prompt, same 10 questions; different specifics.

Worked example — the "design real-time ad bidder analytics" prompt

Structured approach.

Q1: V/V/V — 100K bids/sec, 100 KB per bid, ~1PB/month.
Q2: SLA — 100 ms p99 for bidder feedback loop.
Q3: Storage — Redis (hot bidding state) + Kafka (log) + S3 Parquet (archive).
Q4: Compute — Flink for windowed aggregates; Spark for backfills.
Q5: Latency — p99 100ms hard limit.
Q6: Correctness — exactly-once for financial reconciliation.
Q7: Replay — Kafka 7d + S3.
Q8: Schema — Avro + registry.
Q9: Ops — sub-second monitoring.
Q10: Cost — reserved BigQuery slots; auto-scale Flink.

Rule of thumb. High-throughput / low-latency systems stress streaming + hot storage.

Common beginner mistakes

Jumping to solution before understanding scale.
Not asking about SLA — designing for wrong latency.
Ignoring cost triangle.
Not planning for schema evolution.
Missing correctness semantics.
No replay strategy.

`system design data engineering` interview question on structuring the response

A senior interviewer opens with: "Design the analytics pipeline for a product with 100M DAU. Talk me through your process."

Solution Using the 10-question framework applied top-down

Walk the 10 questions out loud, draw diagram, explain trade-offs. Rule — never just start drawing; ask questions first.

SQL
Topic — SQL
SQL practice library

Practice →

SQL Topic — optimization SQL optimization drills

Practice →

2. The 10-question framework

Q1-Q10 — the senior DE checklist for any system design prompt

The mental model in one line: every DE system-design prompt maps onto 10 questions that must be answered before a design converges — Q1 volume/velocity/variety establishes scale, Q2 SLA + freshness anchors latency requirements, Q3 storage tier chooses hot/warm/cold, Q4 compute engine picks Spark/Flink/Snowflake, Q5 latency budget decomposes across components, Q6 correctness sets exactly-once vs at-least-once, Q7 replay + backfill defines retention, Q8 schema evolution ensures compat, Q9 ops covers monitoring + on-call, Q10 cost triangle grounds the design in dollars.

Q1 — Volume, velocity, variety.

Volume: How many rows / TB / day? Peak vs average?
Velocity: Streaming or batch? Peak rate?
Variety: Sources — relational, event, files, APIs? Schemas?
Signal: Volume 100 TB/day + real-time → distributed streaming architecture.

Q2 — SLA + freshness.

Real-time (< 1 sec).
Near-real-time (< 1 min).
Hourly.
Daily.
Longer.
Signal: freshness drives streaming vs batch.

Q3 — Storage tier.

Hot (< 10 ms) — Redis, ClickHouse.
Warm — Snowflake, BigQuery, Databricks.
Cool — S3 Standard.
Cold — Glacier / Archive.
Signal: access pattern drives tier.

Q4 — Compute engine.

Spark — TB-scale batch.
Flink / Kafka Streams — streaming.
Snowflake / BigQuery / Databricks — warehouse SQL.
dbt — transformations on warehouse.
Trino / Presto — interactive SQL.
Signal: compute pattern drives engine.

Q5 — Latency budget.

Break end-to-end latency into components.
Ingest + queue + compute + write + serve = SLA target.
Each component owns its budget.
Signal: budget shows where to optimise.

Q6 — Correctness.

Exactly-once — transactional sink or MERGE.
At-least-once + dedup — cheaper, application-side idempotency.
At-most-once — rarely acceptable.
Late-arriving events — watermark strategy.
Signal: business rule determines.

Q7 — Replay + backfill.

Kafka retention window (7-90 days).
Warehouse time-travel (Snowflake 90 days).
Iceberg snapshots.
Backfill 90-day window without downtime.
Signal: how far back to replay.

Q8 — Schema evolution.

Backward compat — old consumers read new data.
Forward compat — new consumers read old data.
Full compat — both.
Schema registry (Confluent, Apicurio).
Signal: contract between producer + consumer.

Q9 — Ops.

On-call rotation.
Monitoring: pipeline health, data quality, cost.
Runbooks per pipeline.
SLO tracking.
Signal: operability.

Q10 — Cost triangle.

Storage vs compute vs latency.
Warehouse credits / bytes billed.
Egress fees.
What's the budget?
Signal: cost bounds design.

Worked example — the Q1-Q10 walk on a real prompt

Prompt. "Design a data pipeline for a marketplace: 10M sellers, 100M buyers, 5B events/day."

Walk.

Q1: 5B events/day = 60K events/sec avg, 300K peak. 100 TB/day.
Q2: Real-time for fraud detection; hourly for seller dashboards; daily for BI.
Q3: Redis (hot fraud rules) + Snowflake (warm analytics) + S3 (cold archive).
Q4: Flink (streaming fraud) + dbt on Snowflake (warehouse transforms).
Q5: Fraud detection p99 < 100ms; dashboard 15 min freshness; BI 24h.
Q6: Exactly-once for transactions; at-least-once + dedup for analytics.
Q7: Kafka 30d retention; Snowflake 90d time-travel.
Q8: Protobuf + schema registry; backward compat enforced.
Q9: PagerDuty on fraud model latency + null rate spikes.
Q10: $50K/mo budget; per-team tags on Snowflake warehouses.

Rule of thumb. Walk 10 out loud → signal seniority.

Worked example — SLA drives every choice

Prompt. "Real-time fraud detection at 100K events/sec."

Analysis.

SLA: p99 < 50ms fraud decision.
Implication: NOT warehouse (10s query); NOT batch.
Choice: Flink streaming + Redis feature lookup.
Trade-off: more complex ops but hits latency.

Rule of thumb. Latency SLA is the single biggest constraint.

Worked example — cost as constraint

Prompt. "Analyze 500TB user behavior data monthly. Budget $10K/month."

Analysis.

Storage: S3 standard $23/TB × 500TB = $11.5K → too much. Use Glacier for cold.
Compute: BigQuery on-demand $5/TB × 500TB scanned = $2.5K.
    Reduce: partition + cluster; query only recent 30 days = 100TB = $500/month.
Storage tiers: hot 30d in Snowflake, warm 90d in S3, cold in Glacier.
Total: ~$3K/month.

Rule of thumb. Cost triangle bounds — reject designs blowing budget.

Common beginner mistakes

Skipping Q1 (V/V/V) — designing wrong scale.
Assuming freshness — always ask.
No cost analysis.
Missing schema evolution.
Ignoring ops — design doesn't run itself.

`system design data engineering` interview question on structuring answer

A senior interviewer says: "Design the data platform for a fintech with 10M users making 100M transactions/day."

Solution Using structured 10-Q walk

Walk each question; draw architecture; walk trade-offs; check off Q1-Q10.

Sample response outline:

Q1: ~100M txn/day = 1200 txn/sec avg, 6K peak.
Q2: Real-time fraud (100ms); hourly for user dashboards; daily for BI + reg reporting.
Q3: Redis (hot) + Snowflake + S3.
Q4: Flink + Snowflake + dbt.
Q5: fraud 100ms budget; dashboard 15min.
Q6: exactly-once critical (financial); DLT if not.
Q7: Kafka 30d + Snowflake time-travel 90d.
Q8: Avro + registry + CI compat check.
Q9: SOC2 audit trail; PagerDuty; DLQ.
Q10: $50K/mo budget; reserved capacity.

SQL
Topic — SQL
SQL practice library

Practice →

SQL Topic — optimization SQL optimization drills

Practice →

3. Design pattern 1 — event pipeline

Kafka → Flink → Iceberg — real-time event processing with exactly-once + replay

The mental model in one line: the canonical real-time event pipeline is producer → Kafka (partitioned append-only log, 7-30 day retention) → Flink (stateful streaming with checkpointing every 30 sec, exactly-once via 2-phase commit sink) → Iceberg on S3 (columnar table format with time-travel + partition prune + schema evolution) → Trino / Redis for serving; the pipeline supports replay (rewind Kafka offsets + Flink savepoint), backfill (Iceberg snapshot for time-travel), and exactly-once semantics end-to-end.

Architecture.

Sources → Kafka (7-30d retention) → Flink (stateful, checkpointed) → Iceberg (S3)
                                        ↓
                                Redis (real-time serving)

Slot 1 — Kafka.

Partitioned by event key.
3× replication factor.
7-30 day retention.
Schema registry integration (Avro / Protobuf).
Consumer groups for parallel processing.

Slot 2 — Flink.

Stateful streaming — RocksDB state store per operator.
Checkpoints every 30 sec to S3.
Exactly-once via 2PC sink.
Watermarks for late-arriving events.
Savepoints for manual restart.

Slot 3 — Iceberg.

Columnar Parquet on S3.
Schema evolution (add/rename column safely).
Time-travel — SELECT * FROM t VERSION AS OF '2026-07-12'.
Partition pruning — automatic filter push-down.
Compaction jobs for small-file cleanup.

Slot 4 — serving layer.

Trino for ad-hoc SQL over Iceberg.
Redis for pre-materialised aggregates.
BigQuery / Snowflake for BI dashboards.

Slot 5 — exactly-once semantics.

Kafka producer → Flink source → transform → Flink sink → Iceberg
   ^                                              ↓
   Kafka transactional producer   Iceberg transactional commit
   Two-phase commit protocol coordinates.

Kafka: idempotent producer.
Flink: checkpoint-based.
Sink: transactional 2PC.

Slot 6 — replay.

1. Rewind Kafka consumer group to earliest offset.
2. Delete Iceberg table's affected snapshots (or read replay-safe snapshot).
3. Restart Flink from a savepoint.
4. Flink reprocesses; state rebuilds; sink commits idempotently.

Slot 7 — schema evolution.

Avro schema registry.
CI check: new schema is backward + forward compatible.
Iceberg accepts column adds; renames via alias.

Slot 8 — cost model.

Kafka storage: 3× replication + retention window = ~9× raw data.
Flink compute: 24/7 slots; scale to load.
Iceberg storage: S3 tiering.
Total for 1 TB/day: ~$3-5K/month.

Slot 9 — ops.

Prometheus for Flink metrics (lag, throughput, state size).
Grafana dashboards.
PagerDuty on lag > 1 min.
DLQ Kafka topic for malformed events.

Slot 10 — when to pick this pattern.

Real-time / near-real-time SLA.
Event-driven (not batch).
Need replay from source.
Exactly-once required.
Team has Flink expertise (or willing to invest).

Worked example — end-to-end 1B events/day pipeline

Architecture.

Apps (mobile, web) → Kafka (100 partitions, 30d retention)
                        ↓
Flink cluster (128 slots, checkpoint every 30s to S3)
   ├─→ Iceberg raw table (S3)         ← time-travel, replay
   ├─→ Iceberg aggregates (S3)         ← hourly rollups
   └─→ Redis real-time cache           ← < 10ms lookup
                        ↓
     Trino for SQL analytics
     BigQuery for BI (via CDC from Iceberg)

Numbers.

1B events/day = 12K events/sec avg, 60K peak.
Event size ~500 bytes → 500 GB/day → 15 TB/month.
Kafka: 100 partitions, 3× replication, 30d = 15 TB × 3 × 1 = 45 TB Kafka storage.
Flink: 128 slots × 4 GB = 512 GB RAM.
Iceberg: compressed Parquet ~5:1 = 3 TB/month.
Redis: hot aggregates ~50 GB.

Rule of thumb. This is the "reference implementation" for real-time event pipelines.

Worked example — replay from Kafka after data bug

Scenario. Discovered a bug in Flink transform 3 days ago. Need to replay.

Steps.

1. Deploy fixed Flink job version.
2. Rewind consumer group to 3 days ago:
   $ kafka-consumer-groups.sh --reset-offsets --to-datetime 2026-07-09T00:00:00Z --execute
3. Iceberg — delete affected snapshots (or write to new branch).
4. Flink restarts; reprocesses 3 days.
5. Verify output matches expected.

Rule of thumb. Kafka retention window > incident detection time.

Worked example — schema evolution

Scenario. Add user_agent field to events.

Steps.

1. Update Avro schema in registry with new field (nullable, has default).
2. Producer starts emitting with new field.
3. Flink continues (backward compat).
4. Consumer eventually reads new field.
5. Iceberg auto-adds column.

Rule of thumb. Additive schema changes are safe; deletes / renames need care.

`event pipeline` interview question on exactly-once

A senior interviewer asks: "How do you achieve exactly-once from Kafka to Iceberg?"

Solution Using Kafka transactional producer + Flink checkpoint + Iceberg 2PC sink

// Flink app
StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
env.enableCheckpointing(30000);   // 30 sec
env.getCheckpointConfig().setCheckpointingMode(CheckpointingMode.EXACTLY_ONCE);

DataStream<Event> stream = env
    .fromSource(KafkaSource.<Event>builder()
        .setBootstrapServers("kafka:9092")
        .setTopics("events")
        .setGroupId("flink-etl")
        .setDeserializer(...)
        .build(), WatermarkStrategy.forBoundedOutOfOrderness(Duration.ofSeconds(30)), "kafka");

stream
    .map(new EnrichmentFunction())
    .sinkTo(IcebergSink.builder()
        .table(...)
        .toBranch("main")
        .deltaCommit(true)   // transactional
        .build());

env.execute("event-etl");

Why this works — concept by concept:

Checkpoint mode EXACTLY_ONCE — Flink guarantees state consistency across restart.
Kafka source with consumer group — offsets committed on checkpoint.
Iceberg sink deltaCommit — 2PC with Flink checkpoint.
Watermark 30s — late-arriving events handled.
End-to-end — no duplicates, no losses.

SQL
Topic — SQL
SQL practice library

Practice →

SQL Topic — optimization SQL optimization drills

Practice →

4. Design pattern 2 — batch + BI

CDC → Snowflake → dbt → Metabase — the warehouse BI stack

The mental model in one line: the canonical batch + BI stack is source Postgres/MySQL → Debezium CDC → Kafka → Snowpipe (auto-ingest) → Snowflake staging → dbt models (staging/intermediate/marts) → Metabase / Looker / Superset for BI; SLA is 15 min - 24 hours; scale limits are Snowflake credits + warehouse tier + query concurrency; not for real-time but the default for 90% of DE work in 2026.

Architecture.

Postgres → Debezium → Kafka → Snowpipe → Snowflake staging
                                                ↓
                                          dbt models
                                          (staging → intermediate → marts)
                                                ↓
                                     Metabase / Looker / Superset

Slot 1 — CDC via Debezium.

Reads Postgres WAL.
Publishes change events to Kafka.
One Kafka topic per source table.
Handles inserts, updates, deletes.

Slot 2 — Snowpipe auto-ingest.

Detects new files in S3.
Loads to staging tables.
Serverless; scales automatically.

Slot 3 — dbt layers.

models/
├── staging/     # 1:1 with source; cast, rename
├── intermediate/ # dedup, session, filter
└── marts/       # business-facing tables

Incremental models for large tables.
Tests: not_null, unique, custom.
Automatic docs via dbt docs.

Slot 4 — Airflow / dbt Cloud for scheduling.

Hourly or daily runs.
Slim CI: dbt build --select state:modified+.

Slot 5 — BI tool.

Metabase — self-hosted, simple, good for internal.
Looker — expensive, powerful, LookML.
Superset — Apache; self-hosted; flexible.
Tableau — enterprise, expensive.

Slot 6 — SLA.

15-minute freshness for hot dashboards.
24 hours for cost-sensitive.
Alerting on stale data.

Slot 7 — cost model.

Snowflake: warehouse tier × wall clock time.
Auto-suspend + auto-resume.
Reserved credits (20% cheaper than on-demand).
BI Engine / result cache for dashboards.

Slot 8 — schema evolution.

dbt models pass tests on schema change.
New column in source → new column in staging → downstream models unchanged.
Removed column → update dbt models before source removal.

Slot 9 — ops.

dbt tests fail = alert.
Airflow task fail = page.
Snowflake query timeout monitoring.
Cost alerts per warehouse.

Slot 10 — when to pick this pattern.

Business intelligence / analytics.
SLA ≥ 15 min.
Analytics team writes SQL (dbt).
Business questions vs real-time decisions.

Worked example — end-to-end BI stack

Architecture.

Postgres (production)
  → Debezium (K8s pod, reads WAL)
  → Kafka (topics: users, orders, events)
  → Kafka Connect (S3 sink)
  → S3 (Parquet files)
  → Snowpipe (auto-ingest)
  → Snowflake raw schema
  → dbt (staging → intermediate → marts)
  → Metabase (dashboards)

Orchestration: Airflow hourly dbt run.
Testing: dbt tests + CI on PR.
Monitoring: Datadog + PagerDuty.

Numbers.

1 TB/day source Postgres.
Kafka: 3d retention, 3× replication.
Snowflake: XS warehouse for hourly refresh; M for ad-hoc.
Cost: $2-4K/month at moderate scale.

Rule of thumb. Batch + BI is 90% of DE — master this pattern.

Worked example — dbt incremental model

Code.

-- models/marts/fact_orders.sql
{{ config(
    materialized='incremental',
    unique_key='order_id',
    on_schema_change='append_new_columns'
) }}

SELECT
    o.order_id,
    o.customer_id,
    o.total,
    o.created_at,
    c.email,
    p.name AS product_name
FROM {{ source('raw', 'orders') }} o
JOIN {{ source('raw', 'customers') }} c ON c.id = o.customer_id
JOIN {{ source('raw', 'products') }} p ON p.id = o.product_id

{% if is_incremental() %}
    WHERE o.updated_at > (SELECT MAX(created_at) FROM {{ this }})
{% endif %}

Rule of thumb. Incremental for large tables; full refresh for small dims.

Worked example — Snowpipe auto-ingest

Code.

-- Create pipe
CREATE PIPE raw.orders_pipe
AUTO_INGEST = TRUE
AS
COPY INTO raw.orders
FROM @staged/orders/
FILE_FORMAT = (TYPE = PARQUET);

-- SNS notification on S3 → Snowpipe → auto-load

Rule of thumb. Snowpipe = serverless streaming ingest.

`batch + BI` interview question on cost tuning

A senior interviewer asks: "Snowflake cost is $50K/mo. Where do you look first?"

Solution Using cost attribution + warehouse right-sizing + BI Engine

Analysis + fixes:

Warehouse right-sizing (avoid over-provisioning).
Auto-suspend after 60 sec idle.
Reserved credits for baseline.
BI Engine / result cache for repeat queries.
Prune queries via cluster keys + partitioning.

Output: Typical 30-50% savings.

SQL
Topic — SQL
SQL practice library

Practice →

SQL Topic — join SQL join drills

Practice →

5. Pattern 3 — streaming ML feature store

Online + offline stores with point-in-time correctness

The mental model in one line: an ML feature store solves the "training-serving skew" problem — training joins historical features as-of the label's timestamp (point-in-time correctness) so the model doesn't learn from data unavailable at inference; the two stores are the offline store (Parquet on S3 or warehouse table with as-of timestamps for training) and the online store (Redis / DynamoDB for < 10 ms serving); sync process reconciles.

Architecture.

Events (Kafka) → Flink feature computation
                       ↓
        ┌──────────────┴──────────────┐
        ↓                              ↓
   Online Store             Offline Store
   (Redis / DynamoDB)       (S3 Parquet)
        ↓                              ↓
   API serving (<10ms)     Training pipeline (batch)

Slot 1 — online store.

Redis or DynamoDB.
Key: entity_id (user_id, item_id).
Value: latest feature values.
TTL for stale features.
< 10 ms lookup.

Slot 2 — offline store.

Parquet on S3.
Partitioned by date.
Feature values with as-of timestamp.
Time-versioned.

Slot 3 — point-in-time correctness.

Training joins: "feature value AS OF training_ts".
Prevents time leakage.
Critical for model correctness.

Slot 4 — feature transform.

Batch: dbt or Spark computing features.
Streaming: Flink for real-time features.
Both write to online + offline.

Slot 5 — Feast / Tecton.

Open-source: Feast handles PIT correctness.
Commercial: Tecton, Chronon.

Slot 6 — freshness budgets.

Online: seconds to minutes.
Offline: hours to days.
Sync process reconciles.

Slot 7 — feature registry.

Central metadata: name, owner, freshness SLA.
Version tracked.

Slot 8 — cost triangle.

Storage (offline Parquet): cheap.
Compute (feature transforms): moderate.
Serving (Redis): expensive per GB RAM.

Slot 9 — schema evolution.

Add feature: safe.
Remove feature: deprecation cycle.
Rename: alias + notification.

Slot 10 — when to pick.

ML/AI use cases.
Real-time inference.
Training-serving skew a concern.
Multiple models sharing features.

Worked example — Feast setup

Code.

from feast import Entity, FeatureView, FeatureStore
from feast.types import Float64

user = Entity(name="user_id", join_keys=["user_id"])

user_features = FeatureView(
    name="user_activity",
    entities=[user],
    source=BigQuerySource(
        query="""
            SELECT user_id, avg_session_length, num_events_last_7d, event_timestamp
            FROM `myco.features.user_activity`
        """,
        timestamp_field="event_timestamp",
    ),
    online=True,
)

fs = FeatureStore(repo_path=".")
fs.apply([user, user_features])

# Historical retrieval for training
training_df = fs.get_historical_features(
    entity_df=labels_df,   # includes event_timestamp
    features=["user_activity:avg_session_length"],
).to_df()

# Online retrieval for serving
online = fs.get_online_features(
    features=["user_activity:avg_session_length"],
    entity_rows=[{"user_id": 42}],
).to_dict()

Rule of thumb. Feast unifies online + offline with PIT correctness.

Worked example — point-in-time join

Question. Why does joining features on NOW() break training?

Answer.

Training data: (user_id, event_ts, label)
                  42        2026-01-01     churned
                  43        2026-02-15     not churned

Feature: avg_session_length as of 2026-07-12 (current)

If we join on NOW():
  User 42 gets feature computed from data through 2026-07-12
  Includes their churn behaviour (data leakage!)

Correct: join AS OF event_ts
  User 42 gets feature computed from data through 2026-01-01
  Feature reflects pre-churn behaviour

Rule of thumb. PIT correctness is non-negotiable for ML.

Worked example — cost trade-off

Analysis.

Online store size = 100M users × 100 features × 8 bytes = 80 GB
Redis: $200/GB/month = $16K/month → expensive
Alt: DynamoDB: cheaper for infrequent access

Optimise:
- Only cache "top" features per model.
- TTL on stale features.
- Warm cache from offline.

Rule of thumb. Online store is the cost hotspot.

`feature store` interview question

A senior interviewer asks: "How do you prevent training-serving skew?"

Solution Using single feature computation code path + PIT joins

Same code computes feature for online + offline.
PIT joins in training.
Freshness budgets tracked.
Feature registry as source of truth.

Why this works — concept by concept:

Single code path — no divergence.
PIT joins — no leakage.
Freshness — training sees stale reflect prod.
Registry — governance.

SQL
Topic — SQL
SQL practice library

Practice →

SQL
Topic — join
SQL join drills

Practice →

Cheat sheet — DE system design recipe list

10-question framework: V/V/V, SLA, storage, compute, latency, correctness, replay, evolution, ops, cost.
Kafka + Flink + Iceberg = real-time event pipeline.
CDC + Snowflake + dbt = batch + BI.
Feature store = online + offline + PIT.
Retention windows drive replay ability.
Exactly-once needs idempotent sink or 2PC.
Cost triangle: pick two.
Ask about SLA first.
Draw architecture on whiteboard.
Walk trade-offs out loud.
Schema registry for wire contracts.
Point-in-time correctness for ML.
Monitoring per pipeline.
Cost tags per team.
Test in staging.

Frequently asked questions

How is DE system design different from SWE system design?

DE emphasises data volume (TB/day), freshness SLA, storage tier choice, cost per TB, and correctness semantics. SWE emphasises QPS, latency, availability, and consistency. Both share fundamental concepts (scale, sharding, replication) but the axes differ. DE questions probe understanding of columnar stores, streaming semantics, schema evolution, and cost triangles — not just service-mesh patterns.

Which 3 patterns should I have memorised?

Event pipeline (Kafka+Flink+Iceberg), batch+BI (CDC+warehouse+dbt), and feature store (online+offline+PIT). These cover 80% of DE prompts. Add streaming pattern (Kafka Streams / ksqlDB) if you know it.

How much time on each question?

45-min interview: 10 min on Q1-Q3 (understanding requirements), 20 min on architecture, 15 min on trade-offs / deep dive. Adjust to the interviewer's cues.

What if I don't know a specific technology?

Say so and offer the abstraction. "I haven't used Flink but I know stateful stream processing — I'd use it here for the checkpointed state store". Interviewers appreciate honesty + abstract thinking.

Should I draw diagrams?

Yes — always. Boxes for services, arrows for data flow, sinks and sources labelled. Interviewers grade clarity. Erase and iterate.

How do I handle "at what scale does your design break?"

Identify the bottleneck. "The current design handles 100 TB/day; past that, we'd need to shard by tenant" or "past 10K QPS on the API, we'd need to horizontally scale Redis." Show scale thinking.

Practice on PipeCode

Drill the SQL practice library → — 450+ DE-focused questions.
Sharpen SQL optimization drills →.
Layer SQL join drills →.
For the broader SQL interview surface, take the SQL for Data Engineering course →.

Pipecode.ai is Leetcode for Data Engineering — every `system design data engineering` pattern above ships with hands-on practice rooms where you walk the 10-question framework on a marketplace analytics prompt, draw the Kafka+Flink+Iceberg reference implementation, size a batch+BI stack for $50K/mo budget, build a feature store with PIT correctness, and finally trade off cost/storage/speed out loud — the exact system-design fluency that senior DE interviews probe.

Practice SQL now →
Optimization drills →

1. Why DE system design differs in 2026

The system design data engineering mental model — data axes vs SWE axes

Worked example — the "design analytics for a Twitter-scale product" prompt

Worked example — the "design system for compliance-driven customer analytics" prompt

Worked example — the "design real-time ad bidder analytics" prompt

system design data engineering interview question on structuring the response

Solution Using the 10-question framework applied top-down

2. The 10-question framework

Q1-Q10 — the senior DE checklist for any system design prompt

Worked example — the Q1-Q10 walk on a real prompt

Worked example — SLA drives every choice

Worked example — cost as constraint

system design data engineering interview question on structuring answer

Solution Using structured 10-Q walk

3. Design pattern 1 — event pipeline

Kafka → Flink → Iceberg — real-time event processing with exactly-once + replay

Worked example — end-to-end 1B events/day pipeline

Worked example — replay from Kafka after data bug

Worked example — schema evolution

event pipeline interview question on exactly-once

Solution Using Kafka transactional producer + Flink checkpoint + Iceberg 2PC sink

4. Design pattern 2 — batch + BI

CDC → Snowflake → dbt → Metabase — the warehouse BI stack

Worked example — end-to-end BI stack

Worked example — dbt incremental model

Worked example — Snowpipe auto-ingest

batch + BI interview question on cost tuning

Solution Using cost attribution + warehouse right-sizing + BI Engine

5. Pattern 3 — streaming ML feature store

Online + offline stores with point-in-time correctness

Worked example — Feast setup

Worked example — point-in-time join

Worked example — cost trade-off

feature store interview question

Solution Using single feature computation code path + PIT joins

Cheat sheet — DE system design recipe list

Frequently asked questions

How is DE system design different from SWE system design?

Which 3 patterns should I have memorised?

How much time on each question?

What if I don't know a specific technology?

Should I draw diagrams?

How do I handle "at what scale does your design break?"

Practice on PipeCode

The `system design data engineering` mental model — data axes vs SWE axes

`system design data engineering` interview question on structuring the response

`system design data engineering` interview question on structuring answer

`event pipeline` interview question on exactly-once

`batch + BI` interview question on cost tuning

`feature store` interview question