Capacity Planning for Data Pipelines: TB/day, Latency Budget, Cost Triangle

capacity planning data pipelines is the discipline every senior DE, DE manager, and platform SRE eventually owns — the throughput math that says "1 TB/day = 12 MB/sec average but 60 MB/sec peak on a 5× spike pattern", the latency budget decomposition that turns a 500 ms p99 SLO into per-component allowances of "ingest 50 ms + queue 100 ms + process 200 ms + write 100 ms + buffer 50 ms", and the cost / speed / correctness triangle that grounds every design decision in dollars. Every DE eventually sizes a pipeline; knowing the peak-vs-average ratio math, headroom rules, autoscale limits, and cost attribution patterns is what separates senior planning from mid-level guessing.

The tour walks the five pillars — (1) throughput math converting TB/day to MB/sec (average vs peak), batch-size trade-offs, per-record vs per-byte cost models, (2) latency budget decomposition (p50 / p99 targets, per-component budgets, SLO error budget with burn-rate math), (3) the cost triangle across compute / storage / speed, storage tiers (hot / warm / cool / cold), cloud vs on-prem, reserved vs on-demand, (4) headroom rules (50% baseline, autoscale limits, cooldown periods, burst allowances), and (5) budget alerts + per-team attribution + monthly capacity reviews. 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 indexing drills →.

---

On this page

• Why capacity planning matters in 2026
• Throughput math — TB/day
• Latency budget decomposition
• Cost triangle
• Headroom + scaling
• Cheat sheet — capacity planning recipe list
• Frequently asked questions
• Practice on PipeCode

---

1. Why capacity planning matters in 2026

The capacity planning data pipelines mental model — sizing before shipping

The one-sentence invariant: capacity planning is the discipline of computing three numbers before shipping a pipeline — the expected throughput (bytes/sec average + peak), the latency budget (SLO decomposed per component), and the cost envelope ($ / month at target scale) — and validating them against reality post-launch; skipping this step turns "the pipeline is slow" from a rare event into a monthly firefight. Every senior DE has been on both sides — being paged at 3 a.m. because someone shipped an under-provisioned pipeline, and doing the math up-front so they aren't.

Where capacity planning shows up.

• New pipeline design — how much compute? What tier of Snowflake? How many partitions?
• Yearly budget planning — projected data spend based on growth model.
• Post-incident retrospective — was it capacity? Was headroom insufficient?
• Scale-up decision — when to bump warehouse tier?
• Cost review — where's the money going?
• Migration planning — Kafka on 3 brokers to 5 brokers.
• Vendor negotiation — reserved capacity vs on-demand.

The three axes.

• Throughput (TB/day, events/sec).
• Latency (p50, p99).
• Cost ($ / day).

Trade-offs among these three drive design.

What senior interviewers actually probe.

• TB/day to bytes/sec math. 1 TB/day = 12 MB/sec.
• Peak vs average ratio. 5-10× for consumer.
• Batch size trade-offs. Larger = throughput; smaller = latency.
• Latency decomposition. How to break down p99.
• SLO vs SLA. Internal vs contract.
• Error budget math. 99.9% = 43 min/month.
• Cost triangle. Pick two.
• Storage tier decision. Access pattern.
• Headroom rule. 50% baseline.
• Autoscale limits. Min = baseline; max = 2× baseline.
• Reserved vs on-demand. Break-even at ~40% utilisation.
• Per-team tagging. Cost attribution.

The 5-step planning workflow.

• Step 1 — estimate throughput. TB/day, peak ratio, growth.
• Step 2 — decompose latency. SLO → per-component budgets.
• Step 3 — model cost. Rough $ per component per month.
• Step 4 — check triangle. Can you afford it? Meets SLA?
• Step 5 — plan headroom + autoscale. 50% baseline; cap max.

Worked example — the "how do I size this?" question

Prompt. "Design capacity for a new ETL: 500 GB/day of events, 15-min freshness, $10K/mo budget."

Analysis.

Q1: Throughput
   500 GB/day = 6 MB/sec avg. Peak 5× = 30 MB/sec.

Q2: Latency
   15-min freshness = 15 min budget.
   Ingest 2 min + queue 3 min + process 5 min + write 2 min + slack 3 min.

Q3: Storage
   500 GB/day * 30 days = 15 TB/month warm storage.
   Compress 5:1 = 3 TB.
   Warehouse: $23/TB/month * 3 = ~$70/month storage.

Q4: Compute
   Snowflake Medium warehouse (~$8/hour) for 15 min every hour = $8 * 0.25 * 24 = $48/day = $1440/month.

Q5: Cost check
   Storage $70 + compute $1440 + BI + misc = ~$2K/month.
   Well within $10K budget. Add headroom for growth.

Rule of thumb. Estimate, then verify with actual measurements post-launch.

Worked example — the growth projection

Prompt. "Data doubles every 6 months. Plan capacity for 2 years."

Analysis.

Now: 100 GB/day.
6 months: 200 GB/day.
12 months: 400 GB/day.
18 months: 800 GB/day.
24 months: 1.6 TB/day (16× growth).

Storage: 1.6 * 30 * 12 = 576 TB annual retention. Consider archival tiering.
Compute: 16× throughput → need to scale warehouse tier or add clusters.
Budget: model at $16K/mo terminal state; provision reserved capacity accordingly.

Rule of thumb. Plan 2 years forward; reserve capacity for the terminal state.

Worked example — the cost surprise post-launch

Prompt. "Launched a new pipeline. First month bill was 4× what we estimated. Diagnose."

Analysis.

Estimated: 500 GB/day * $5/TB = $75/day = $2.3K/mo.
Actual: $9K/mo.

Debug:
- BigQuery bytes billed: 20 TB/day (40× more than expected).
- Root cause: SELECT * on wide table + no partitioning.
- Fix: partition + cluster + projection pruning.
- Result: back to $2K/mo.

Rule of thumb. Post-launch bills reveal mis-estimation; iterate.

Common beginner mistakes

• Estimating from average, not peak.
• Skipping cost projections.
• No SLO defined.
• Not planning growth.
• Missing headroom.
• No cost attribution.

capacity planning data pipelines interview question

A senior interviewer asks: "You're sizing a Kafka-based analytics pipeline for 1B events/day. Walk me through your capacity plan."

Solution Using the 5-step workflow

Step 1: Throughput
  1B events/day = 12K events/sec avg, 60K peak (5x).
  Event size ~500B → 6 MB/sec avg, 30 MB/sec peak.

Step 2: Latency
  Real-time SLO: p99 < 1 sec end-to-end.
  Ingest 100ms + queue 200ms + process 300ms + write 200ms + slack 200ms.

Step 3: Cost
  Kafka: 3× replication * 30d retention * 500 GB/day * $23/TB/mo = ~$1K/mo (data) + brokers $2K/mo.
  Flink: 24/7 cluster, 16 slots, ~$500/mo.
  Iceberg S3: $23/TB/mo * 15 TB/mo = $350/mo storage.
  Trino/BQ: $500/mo BI.
  Total: ~$4-5K/mo.

Step 4: Triangle check
  Cost: $5K/mo OK.
  Latency: p99 1 sec achievable with Flink.
  Correctness: exactly-once via 2PC.
  All three feasible.

Step 5: Headroom
  Kafka: 6 partitions × baseline; scale to 12 for headroom.
  Flink: 16 slots baseline; autoscale to 32 max.
  Storage: budget for 6-month growth.

Why this works — concept by concept:

• 5-step workflow — structured; no guessing.
• Peak sizing — 5× ratio catches spikes.
• Cost model — per-component; sums to total.
• Headroom — 50% base capacity extra.
• Post-launch verify — reality vs estimate.

SQL
Topic — SQL
SQL practice library

Practice →

SQL Topic — optimization SQL optimization drills

Practice →

---

2. Throughput math — TB/day

TB/day to MB/sec conversion, batch sizing, peak vs average

The mental model in one line: 1 TB/day = 12 MB/sec average = 60 MB/sec peak (5× typical peak/average for consumer apps), 2-3× for B2B SaaS, 1.2-1.5× for IoT with steady load; design for peak, run at average, and understand batch-size trade-offs (larger batches = higher throughput + higher latency; smaller batches = lower latency + more per-batch overhead) — the sweet spots are 100 KB - 10 MB for Kafka batches and 100 MB - 1 GB for warehouse file loads.

Slot 1 — TB/day to bytes/sec.

• 1 TB/day = 1,000 GB / 86,400 sec = ~12 MB/sec average.
• 100 GB/day = 1.2 MB/sec.
• 10 TB/day = 120 MB/sec.
• 100 TB/day = 1.2 GB/sec.

Slot 2 — peak vs average ratios.

• Consumer app — 5-10× peak/avg (evening spike).
• B2B SaaS — 2-3× (business hours).
• IoT / sensor — 1.2-1.5× (steady).
• Ad-tech / bidding — 3-5× (auction spikes).

Design for peak; run at average.

Slot 3 — batch size trade-offs.

Larger batches:

• Higher throughput (amortise overhead).
• Higher latency (wait for batch to fill).

Smaller batches:

• Lower latency.
• More overhead per batch.
• More small files (bad on warehouses).

Slot 4 — sweet spots.

• Kafka batch: 100 KB - 10 MB per batch.
• Warehouse file load: 100 MB - 1 GB per file.
• Flink checkpoint: 30s - 2 min.
• dbt incremental batch: 1 hour - 1 day.

Slot 5 — per-record vs per-byte cost.

• Kafka producer — request rate bound (~30K rps typical per broker).
• Warehouse load — bytes bound.
• API scraping — request-rate bound (rate limits).

Design bottleneck-appropriate.

Slot 6 — worked example — 100 TB/day.

100 TB/day → 1.2 GB/sec avg → 6 GB/sec peak (5× ratio).

Kafka:
- 6 GB/sec write throughput needed at peak.
- Partition throughput ~100 MB/sec each.
- Partitions needed: 6000 / 100 = 60 → round to 64.
- Replication 3× → 192 physical broker-partition instances.
- Broker count: 6 brokers minimum; 12 for headroom.

Storage:
- 100 TB/day * 30d retention = 3 PB.
- Replication 3× = 9 PB Kafka storage.
- Cost: $23/TB/mo * 9000 = $200K/mo just Kafka storage.
- ← reduce retention or use tiered storage.

Slot 7 — buffer sizing.

• Producer buffer: seconds of throughput.
• Consumer buffer: > processing time × concurrent.
• Kafka page cache: fits recent hours in RAM (bounds by broker RAM).

Slot 8 — network throughput.

• 1 Gbps NIC = 125 MB/sec.
• 10 Gbps = 1.25 GB/sec.
• 25 Gbps = 3 GB/sec.
• 6 GB/sec peak → 25 Gbps or bond multiple.

Slot 9 — disk throughput.

• Standard SSD: ~500 MB/sec sequential.
• NVMe: 3-7 GB/sec.
• Kafka disk IO can dominate; NVMe for hot brokers.

Slot 10 — bottleneck identification.

CPU-bound: perf top / py-spy shows Python.
IO-bound: iostat shows disk 100%.
Network-bound: ifstat shows NIC saturated.
Fix per bottleneck.

Common beginner mistakes

• Designing for average — spike takes it down.
• Missing peak ratio (industry-typical multiplier).
• Batch too small — overhead dominates.
• Batch too large — latency violates SLA.
• Ignoring network / disk bottleneck.
• Missing replication factor in storage math.

Worked example — sizing Kafka for 500 GB/day

500 GB/day = 6 MB/sec avg, 30 MB/sec peak (5× ratio).

Partitions needed:
  Per-partition write ~100 MB/sec.
  Peak 30 MB/sec / 100 = 0.3 → 1 partition sufficient (throughput-wise).
  But: consumer parallelism = partition count. For 5 consumers, 5+ partitions.
  Compromise: 10 partitions.

Storage:
  500 GB/day * 3 replicas * 7d retention = 10.5 TB Kafka storage.
  Cost: $23/TB * 10.5 = ~$250/mo storage.
  Brokers: 3-broker cluster fine at this scale.

Consumer:
  30 MB/sec / consumer throughput ~50 MB/sec → 1 consumer OK.
  Reality: run 2 consumers for HA.

Post-launch verify:
  Prometheus: kafka_topic_partition_current_offset delta / 5 min.
  Should show ~30 MB/sec peak.

Rule of thumb. Partitions = max(consumer parallelism, throughput/partition throughput).

Worked example — batch-size decision for Snowflake

Ingest scenario: files land in S3 every 1 min, 100 MB each = 6 GB/hour.

Batch size options:
  Option A: Load every 1 min (100 MB each).
    Pros: fresh.
    Cons: 60 Snowpipe events/hour = notification cost + micro-partition churn.

  Option B: Load every 5 min (500 MB batch).
    Pros: fewer events; better partitioning.
    Cons: 5-min lag.

  Option C: Load every 1 hour (6 GB batch).
    Pros: minimal overhead.
    Cons: 1-hour lag.

Pick: Option B — good balance.

Rule of thumb. Warehouse file loads: aim 100 MB - 1 GB per file; every 1-5 min.

Worked example — network capacity

Peak 6 GB/sec across Kafka cluster.

3 brokers × 25 Gbps = 3 × 3.125 GB/sec = 9.375 GB/sec.
Utilization: 6 / 9.375 = 64%. OK.

Alternative: 6 brokers × 10 Gbps = 7.5 GB/sec. Utilization 80%. Cutting close.

Recommendation: 25 Gbps NICs for high-throughput Kafka.

Rule of thumb. Network can be surprise bottleneck; provision headroom.

capacity planning data pipelines interview question on Kafka sizing

A senior interviewer asks: "Size a Kafka cluster for 100M events/day."

Solution Using throughput math

100M events/day = 1200 events/sec avg, 6000 peak.
Event size 500 B → 600 KB/sec avg, 3 MB/sec peak.

Partitions: 12 for parallelism (though throughput fine with 4).
Retention: 7 days.
Storage: 100M/day * 500B * 7d * 3 replicas = 1 TB storage.
Brokers: 3-broker cluster (HA baseline).
Cost: ~$500/mo brokers + $30/mo storage.

Post-launch: Prometheus monitors offset delta; alert if lag > 1 min.

Why this works — concept by concept:

• Math from first principles — no guessing.
• Peak sizing — 5× headroom.
• HA baseline — 3 brokers minimum.
• Retention & replication — drives storage.
• Verify post-launch — reality check.

SQL
Topic — SQL
SQL practice library

Practice →

SQL Topic — optimization SQL optimization drills

Practice →

---

3. Latency budget decomposition

p50 and p99 targets, per-component budgets, SLO burn rate

The mental model in one line: a pipeline latency SLO is a per-request p99 target (e.g., "99% of events flow end-to-end in under 500 ms"), decomposed into a budget per component (ingest 50 ms + queue 100 ms + process 200 ms + write 100 ms + slack 50 ms = 500 ms); each component owns its budget; the error budget (1 - SLO / 1 = 0.1% for 99.9% SLO = 43 min/month) is spent by burn-rate math — 1× is normal, 2-5× is warning, > 10× is fire.

Slot 1 — SLO vs SLA.

• SLO — internal target. "99.9% of requests < 500 ms".
• SLA — external contract. Usually looser than SLO with financial penalty for breach.
• Error budget — 1 - SLO. 99.9% = 0.1% error budget = 43 min/month.

Slot 2 — p50 vs p99.

• p50 (median) — typical experience.
• p99 — the tail; 1 in 100 events see this.
• p99.9 — 1 in 1000.
• Watch p99, p99.9 in SLO tracking.

Slot 3 — budget decomposition.

For a 500 ms p99 pipeline SLO:

Ingress (Kafka producer + topic write): 50 ms
Queue wait (Kafka consumer lag): 100 ms
Processing (Flink transform): 200 ms
Sink (Iceberg / warehouse write): 100 ms
Headroom (buffer): 50 ms
Sum: 500 ms

Each component owns its budget.

Slot 4 — burn rate math.

• 30-day error budget for 99.9% SLO = 43 min/month = 86 sec/day (avg).
• 1× burn — normal; using 43 min in 30 days.
• 2× burn — warning; will exhaust in 15 days.
• 5× burn — critical; will exhaust in 6 days.
• 10× burn — fire; exhaust in 3 days; page.

Slot 5 — measurement.

• Track p50, p95, p99, p99.9 per component.
• Prometheus histograms — quantile computation server-side.
• Grafana dashboards.
• Alert on burn rate.

Slot 6 — component ownership.

• Kafka team owns ingest latency.
• Flink team owns processing latency.
• Warehouse team owns write latency.
• Cross-team SLOs need coordination.

Slot 7 — end-to-end vs per-component.

• End-to-end SLO — user-visible.
• Per-component SLO — team-owned.
• Sum of component p99s > end-to-end p99 (due to correlation).
• Use for planning; measure both.

Slot 8 — reducing tail latency.

• Warm caches.
• Reduce timeouts.
• Retry with hedging (send to 2 replicas; take faster).
• Reduce variance (batch smaller).

Slot 9 — batch pipelines have different metrics.

• Batch: freshness SLO — "table refreshed within 15 min of hour boundary".
• Not p99 latency per record.
• Different math.

Slot 10 — error budget policy.

• Consume error budget → freeze new deploys.
• Refill error budget → resume.
• Aligns dev pace with reliability.

Common beginner mistakes

• Watching p50 only.
• No per-component SLO.
• No error budget tracking.
• Ignoring burn rate.
• Latency budget doesn't sum.

Worked example — decomposing SLO

Product: real-time analytics API.
Target: p99 < 200 ms end-to-end.

Components:
  API load balancer: 10 ms
  Redis feature lookup: 20 ms
  ML model inference: 50 ms
  API response serialization: 20 ms
  Network round-trip: 30 ms
  Total: 130 ms (well within 200 ms with 70 ms buffer)

Deploy budget:
  Team: p99 < 100 ms for the API server (compute + Redis).
  Measure via Prometheus histogram.
  Alert on p99 > 150 ms for 5 min.

Rule of thumb. Decompose; each team owns; sum ≤ target with margin.

Worked example — error budget spent

SLO: 99.9% of pipeline events processed within 5 minutes.
Error budget: 43 min/month.

Last week: incident consumed 20 min of budget.
This week: burn rate 1.5× normal.

Projected: exhaust budget by day 25.

Action:
- Freeze non-critical deploys.
- Focus on reliability.
- Refill budget via improvements.
- Resume deploys when budget replenishes.

Rule of thumb. Error budget policy = coordination mechanism.

Worked example — measuring p99

# Prometheus histogram
from prometheus_client import Histogram

pipeline_latency = Histogram(
    'pipeline_latency_seconds',
    'End-to-end pipeline latency',
    buckets=[0.05, 0.1, 0.2, 0.5, 1.0, 2.0, 5.0],
)

# Instrument
with pipeline_latency.time():
    process(event)

# Query
# histogram_quantile(0.99, rate(pipeline_latency_seconds_bucket[5m]))

Rule of thumb. Instrument first; measure; alert.

latency budget interview question on SLO decomposition

A senior interviewer asks: "Your end-to-end SLO is p99 < 1 sec for events. Decompose into per-component budgets."

Solution Using per-component allocation

Total: 1000 ms

Component budgets:
  Producer send: 50 ms
  Kafka broker persist + replicate: 100 ms
  Consumer lag (queue): 200 ms
  Processing (transform + enrich): 400 ms
  Write to sink: 150 ms
  Slack + buffer: 100 ms
  Total: 1000 ms ✓

Per-team ownership:
  App team: producer 50 ms.
  Kafka team: broker 100 ms.
  Streaming team: consumer + processing 600 ms.
  Storage team: sink 150 ms.

Alert: p99 > 1500 ms sustained 5 min.
Burn: >2× normal → page on-call.

Why this works — concept by concept:

• Sum ≤ target — margin for measurement variance.
• Team ownership — clear accountability.
• Per-component alerting — pinpoint issue.
• Burn rate policy — coordinated response.

SQL
Topic — SQL
SQL practice library

Practice →

SQL Topic — indexing SQL indexing drills

Practice →

---

4. Cost triangle

Compute vs storage vs speed — pick two

The mental model in one line: every DE pipeline sits somewhere in the triangle of compute vs storage vs speed — you can optimise for two but pay for the third (e.g., materialised views for dashboards trade storage for query speed; batch overnight ETL trades speed for compute; Redis-backed serving trades cost for speed); understanding your workload's position on the triangle drives storage tier choices (hot vs warm vs cold), compute engine choices (streaming vs batch vs warehouse), and reserved-vs-on-demand pricing decisions.

Slot 1 — the three vertices.

• Compute — CPU / memory / GPU / warehouse credits per hour.
• Storage — TB per month, cached hot or archived cold.
• Speed — query latency or pipeline freshness.

You can optimise two; the third suffers.

Slot 2 — common patterns.

Pick	Sacrifice	Example
Speed + compute	Storage	Materialised views for dashboards
Speed + storage	Compute	Redis-backed API serving
Storage + compute	Speed	Batch overnight ETL to warehouse

Slot 3 — storage tiers.

• Hot (Redis, ClickHouse) — $$$$/TB.
• Warm (Snowflake, BigQuery) — $$$/TB.
• Cool (S3 Standard) — $$/TB.
• Cold (Glacier) — $/TB.

Tier by access frequency.

Slot 4 — cloud vs on-prem.

• Cloud — pay per use, elastic, capex-light.
• On-prem — fixed cost, high utilisation efficient.
• Break-even — around 40% steady utilisation.

Slot 5 — reserved vs on-demand.

• Snowflake reserved credits — 20-30% cheaper than on-demand.
• BigQuery flat-rate slots — vs on-demand.
• AWS EC2 reserved — 30-60% off.
• Commit to baseline; on-demand for burst.

Slot 6 — cost per operation.

• Read query — bytes scanned.
• Write — bytes written.
• Compute (warehouse) — wall-clock seconds × warehouse size.
• Storage — bytes × time.
• Egress — bytes leaving cloud.

Model each; sum.

Slot 7 — the "no free lunch" rule.

Adding hot storage → costs more $/TB but faster.
Adding compute → shorter wall clock but higher cost.
Adding correctness (exactly-once) → higher overhead than at-least-once.

Slot 8 — cost optimisation targets.

• Query cost > $1 per run → optimise.
• Warehouse cost > 10% of team budget → attribute + review.
• Storage growth > revenue growth → tier + archive.

Slot 9 — auto-suspend + auto-resume.

Snowflake auto-suspend 60 sec, auto-resume on query. Only pay when actively querying.

Slot 10 — result cache.

Snowflake / BigQuery caching = zero-cost re-run of same query. Encourages BI patterns that re-query.

Common beginner mistakes

• All-hot storage — bill blowout.
• No cache — repeated queries pay full cost.
• No reserved capacity — miss 30% discount.
• Ignoring egress fees.
• No cost tags per team.

Worked example — the "cost optimisation" audit

Current: $50K/mo Snowflake.

Top spenders (via QUERY_HISTORY):
  Team A analytics: $20K/mo — mostly hourly refresh dashboards.
  Team B ML: $15K/mo — large training pulls.
  Team C ad-hoc: $10K/mo — exploration.
  Other: $5K/mo.

Optimisations:
  Team A: enable result cache + BI Engine → $10K/mo.
  Team B: use M warehouse instead of XL for training → $8K/mo.
  Team C: educate on cluster keys + LIMIT → $5K/mo.
  Other: unchanged.

Post: $28K/mo. 44% saving.

Rule of thumb. Cost audit reveals 20-50% savings typical.

Worked example — hot vs cold tier decision

Access pattern:
  Data accessed within 30 days: 90% of queries.
  Data accessed 30-90 days: 8% of queries.
  Data accessed 90d+: 2% of queries.

Storage tier decision:
  0-30d: Snowflake (warm) — $23/TB/month.
  30-90d: S3 Standard (cool) — $23/TB/month.
  90d+: S3 Glacier (cold) — $4/TB/month.

Cost for 100 TB:
  All hot: 100 × $23 = $2300/month.
  Tiered: 30 × $23 (Snowflake) + 60 × $23 (S3) + 10 × $4 (Glacier) = $690 + $1380 + $40 = $2110.
  Marginal saving small. But egress from cold is expensive; access carefully.

Better: 100 TB in Snowflake vs S3 depending on query needs.

Rule of thumb. Tier only when access pattern justifies extra complexity.

Worked example — reserved credit sizing

Baseline usage: 50% steady utilisation of Medium warehouse.
On-demand cost: $8/hr * 24 * 30 = $5760/month × 0.5 = $2880 effective.

Reserved credits: commit 100K credits/month at $2.50/credit (20% off) = $2500/month.
Saves $380/month.

But: risk of over-commit (unused credits). Model demand carefully.
Rule: reserve baseline; on-demand for burst.

Rule of thumb. Reserved for known steady-state.

cost triangle interview question

A senior interviewer asks: "Your dashboard query pattern is repeat-heavy (same 10 queries run every 5 minutes). Optimize."

Solution Using result cache + BI Engine + materialised views

• Snowflake result cache: 24h TTL; free re-run.
• BI Engine on BigQuery: in-memory cache for aggregates.
• Materialised views: pre-computed aggregates; write once, read many.
• Cache warmup on daily refresh; queries hit cache.

Cost impact: $10K → $1K typical for cache-friendly dashboards.

SQL Topic — optimization SQL optimization drills

Practice →

SQL Topic — SQL SQL practice library

Practice →

---

5. Headroom + scaling

50% headroom rule, autoscale bounds, budget alerts, per-team attribution

The mental model in one line: baseline capacity should be 2× current average (50% headroom) to handle peaks + surprise growth; autoscale bounds are min = baseline / max = 2× baseline (prevents runaway); cooldown 5-10 min avoids scaling flap; budget alerts fire at 80% consumption for monthly forecasts; per-team cost attribution requires tagging every resource at creation time.

Slot 1 — 50% headroom.

• Baseline = 2× current average.
• Handles peak + surprise growth.
• Reserves for unusual spike (5-10× peak/avg).
• Never sustained > 80% capacity.

Slot 2 — autoscale bounds.

• Min = baseline (never below).
• Max = 2× baseline (prevents runaway cost).
• Cool-down: 5-10 min to avoid flap.
• Scale-up faster than scale-down.

Slot 3 — budget alerts.

• Daily spend > 80% budget: warn.
• Weekly forecast > budget: alarm.
• Monthly attribution per team.
• Break-glass approval for exceed.

Slot 4 — per-team tagging.

Every resource tagged at creation:

{
  "team": "analytics",
  "env": "prod",
  "cost_center": "cc-42",
  "created_by": "[e-mail]"
}

Rollup for attribution.

Slot 5 — dialect matrix.

Concern	Warehouse (Snowflake)	Streaming (Flink)	Object storage (S3)
Peak sizing	WH tier	Parallelism	Request rate
Autoscale	Multi-cluster	Dynamic allocation	Elastic
Baseline	Reserved credits	Fixed cluster	Fixed
Alert	Credits/day	Slot_ms	Put/get count

Slot 6 — headroom monitoring.

Prometheus + Grafana dashboard:

• Current CPU vs baseline.
• Current memory vs baseline.
• Query queue depth.
• Autoscale current vs max.

Alert on approach to max.

Slot 7 — pre-emptive scale up.

Predictable spikes (marketing campaign, end-of-quarter):

• Manual scale-up before event.
• Scale down after.

Slot 8 — chaos testing capacity.

Quarterly:

• Force spike traffic → validate autoscale.
• Kill nodes → validate resilience.
• Test SLA under stress.

Slot 9 — monthly capacity review.

• Current spend vs plan.
• Growth trend.
• Bottlenecks emerging.
• Optimisations landed.

Slot 10 — cost per unit tracking.

• $ per event ingested.
• $ per row served.
• $ per user-hour.

Track over time; identify inflation.

Common beginner mistakes

• No headroom.
• Autoscale unbounded.
• No budget alerts.
• Untagged resources.
• No quarterly review.

Worked example — headroom planning

Current baseline: Snowflake Medium warehouse.
Peak load: 60% of Medium capacity.

Headroom plan:
  Peak/avg ratio: 5×.
  Design capacity = 2× current baseline = 2× Medium = Large or 2× Medium multi-cluster.

Autoscale:
  Multi-cluster warehouse.
  min_cluster_count = 1 (baseline).
  max_cluster_count = 3 (max 3× capacity).
  scaling_policy = "STANDARD".

Rule of thumb. 50% headroom absorbs typical volatility.

Worked example — cost attribution

-- Snowflake — attribute cost by warehouse tag
SELECT
    warehouse_name,
    TAG_VALUE('team') AS team,
    SUM(credits_used) AS credits,
    SUM(credits_used) * 2.50 AS cost_usd
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_DATE)
GROUP BY 1, 2
ORDER BY cost_usd DESC;

Rule of thumb. Cost per team = tagging discipline.

Worked example — budget alert config

# CloudWatch cost alert
Type: AWS::CloudWatch::Alarm
Properties:
  AlarmName: monthly-budget-80pct
  MetricName: EstimatedCharges
  Namespace: AWS/Billing
  Statistic: Maximum
  Period: 3600
  EvaluationPeriods: 1
  Threshold: 8000   # 80% of $10K budget
  ComparisonOperator: GreaterThanOrEqualToThreshold
  AlarmActions:
    - !Ref SnsToOnCall

Rule of thumb. Alert BEFORE budget exceeded, not after.

headroom + scaling interview question

A senior interviewer asks: "You have $50K/mo Snowflake budget. Team asks for 3× more warehouses. How do you decide?"

Solution Using cost model + growth projection + attribution

Analyze:
  Current: $50K/mo with 5 warehouses.
  Requested: 3× more warehouses.
  Projected: $150K/mo? Unlikely — proportional but usage varies.

Model:
  Existing WH utilisation: 60%.
  Adding: same pattern → 3 × $10K/mo per team = $30K new.
  Total: $80K/mo, but budget is $50K.

Options:
  1. Right-size existing (reduce over-provisioned).
  2. Reserved credits (save 20%).
  3. Tighten dashboards (less compute).
  4. Explicit budget increase request.

Decision: audit first, then reserve, then request.

Why this works — concept by concept:

• Cost model — quantifies.
• Growth projection — realistic.
• Optimisation before ask — cheaper to reduce than expand.
• Reserved credits — 20% off baseline.
• Explicit request — informed decision.

SQL
Topic — SQL
SQL practice library

Practice →

SQL
Topic — optimization
SQL optimization drills

Practice →

---

Cheat sheet — capacity planning recipe list

• 1 TB/day = 12 MB/sec avg.
• Peak/avg 5-10× consumer, 2-3× B2B, 1.5× IoT.
• Batch size: 100 KB - 10 MB streams, 100 MB - 1 GB warehouse files.
• SLO 99.9% = 43 min/month error budget.
• p99 > p50 by 10-100×.
• Decompose latency budget per component.
• Cost triangle: compute, storage, speed — pick two.
• Storage tiers: hot / warm / cool / cold.
• 50% headroom baseline.
• Autoscale: min = baseline, max = 2× baseline.
• Cool-down 5-10 min.
• Reserved credits 20-30% cheaper.
• Alert at 80% budget consumption.
• Tag every resource for attribution.
• Monthly capacity review.
• Chaos test quarterly.
• Break-glass approval for budget exceed.

Frequently asked questions

How do I compute peak throughput?

Measure baseline (average) traffic; multiply by industry-typical peak ratio (5-10× for consumer, 2-3× for B2B, 1.2-1.5× for IoT). If you have historical data, use actual p95/p99 traffic during the busiest hour. Design to peak, run at average, alert on divergence. Rule — under-estimating peak = incidents; over-estimating = wasted cost.

What's a reasonable error budget?

99.9% SLO = 43 min/month error budget = ~1 hour a month. 99.99% = 4 min a month. 99.999% = 26 sec a month. Choose based on business need — dashboards can tolerate 30 min; payment pipelines need 4 min or less. Rule — start with 99.9%; tighten only where business demands.

How much headroom is enough?

Baseline capacity = 2× current average (50% headroom). Enough for 2-5× traffic bursts and 6 months of growth. Refresh quarterly. Under-provisioned = incidents; over-provisioned = wasted cost. Rule — 50% baseline works for most workloads; tune per real behaviour.

When should I bump warehouse tier?

Watch remote spill on Snowflake, wait time on BigQuery, worker queue on Airflow. Any of these trending up = capacity issue. Also watch cost trending — if bill grows 20%/month, capacity needs review. Rule — bump when SLO violated OR cost of bump < cost of incident.

On-prem or cloud for a new pipeline?

Cloud unless you have very steady utilisation > 40% AND compliance requirements AND ops capacity to manage. Cloud wins for elasticity, capex-light, and simpler ops. On-prem wins for cost when fully utilised. Rule — cloud for new; consider on-prem after steady-state utilisation known.

How do I attribute costs to teams?

Tag every resource with team name at creation. Monthly rollup by tag. Enforce in IaC — untagged resources fail linting. Snowflake supports tags per warehouse and per query. BigQuery labels per job. AWS cost allocation tags. Rule — tag at creation; no untagged resources allowed.

Practice on PipeCode

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

Pipecode.ai is Leetcode for Data Engineering — every `capacity planning data pipelines` pattern above ships with hands-on practice rooms where you convert 100 TB/day to bytes/sec and size the Kafka broker count, decompose a 500 ms p99 SLO into per-component budgets with burn-rate policy, size Snowflake warehouse credits with reserved vs on-demand, plan 50% headroom + autoscale bounds, and set per-team cost attribution alerts — the exact capacity fluency that senior DE and platform interviews probe.

Practice SQL now →
Optimization drills →