Why this matters
As an ETL Developer, every design choice has a price tag: how much data you scan, how many files you touch, how often you compute, and where your data lives. Cost-aware design lets you meet SLAs while paying only for what you truly need.
- Your daily report suddenly costs 10x more after adding a column — understand why and fix it.
- Ingesting billions of events? Avoid per-file overhead by compacting and partitioning smartly.
- Batch vs streaming: choose the cheapest option that still satisfies freshness targets.
Concept explained simply
Cost-aware pipeline design means choosing formats, schedules, and compute that deliver the needed results at the lowest sustainable cost. You minimize bytes processed, reduce file overhead, reuse work, and right-size compute.
Mental model
- Think levers: scan less, store smarter, compute once, right-size compute.
- Think trade-offs: freshness vs cost, latency vs cost, flexibility vs cost.
- Think unit economics: cost per GB processed, cost per successful run, cost per row delivered.
Cost drivers and cost levers
Compute cost
- Drivers: cluster size, run time, parallelism, shuffles/joins.
- Levers: broadcast small tables, cache hot dims, reduce shuffles, autoscale/spot with retries.
Storage cost
- Drivers: data volume, retention, file format, small files.
- Levers: columnar formats (Parquet/ORC), compression, compaction to target sizes (e.g., 128–512 MB), lifecycle policies (hot → cold → archive).
I/O and bytes scanned
- Drivers: SELECT *, unfiltered scans, poor partitioning, non-columnar formats.
- Levers: column pruning, predicate pushdown, partitioning by common filters (e.g., date), clustering/sorting.
Data transfer/egress
- Drivers: cross-region reads/writes, copying between systems.
- Levers: co-locate compute and storage, minimize cross-region movement, cache near compute.
Orchestration and API calls
- Drivers: excessive task fan-out, polling, per-file triggers.
- Levers: batch file events, exponential backoff, consolidate tasks.
Worked examples
Example 1: Partition pruning to cut scan costs
Scenario: You run a daily revenue report. Unpartitioned dataset: 5 TB. Charge model: $5 per TB scanned. You only need the last 7 days.
- Before: 5 TB × $5 = $25 per run.
- After partition by event_date and filter last 7 days: 0.7 TB × $5 = $3.50 per run.
- Daily savings: $21.50. Monthly (30 runs): $645.
Example 2: Fix the small files problem
Scenario: Streaming job writes 50,000 files/day, average 2 MB each. Your engine has per-file listing and open overhead.
- Symptom: long planning time, inflated job runtime, higher compute cost.
- Fix: Compact to ~256 MB target files every hour → ~390 files/day instead of 50,000.
- Effect: Less metadata overhead, better throughput, lower compute cost.
Example 3: Cheaper joins
Scenario: Join 1 TB fact with a 200 MB dimension.
- Broadcast the 200 MB table to avoid a full shuffle of 1 TB.
- Result: fewer network transfers, less executor time, faster and cheaper job.
Design steps you can follow
- Clarify SLA: latency, freshness, and data volume. Choose batch, micro-batch, or streaming accordingly.
- Pick storage format: columnar + compression by default for analytics; row-oriented for OLTP-style lookups.
- Partition by the most selective, frequently-filtered field (often date). Avoid over-partitioning.
- Plan file management: target file sizes (128–512 MB), periodic compaction, avoid too many tiny files.
- Optimize queries: select only needed columns, push filters early, pre-aggregate if re-used.
- Right-size compute: autoscale, use spot/preemptible for fault-tolerant steps with retries/checkpoints.
- Set lifecycle policies: raw → compressed → aggregated; move cold data to cheaper tiers; define TTLs.
- Track unit metrics: cost per GB processed, per run, per row delivered; alert on spikes.
Exercises
Complete these and compare with the solutions below each exercise. The quick test is at the end.
Exercise 1: Estimate daily cost and storage savings
Assume your query engine charges $5 per TB scanned. You currently scan 5 TB per daily run. After partitioning by event_date and filtering to the last 7 days, you scan 0.7 TB per run. Storage is 5 TB raw. Compression improves size by 3x. Storage costs $0.023 per GB-month.
- Q1: What is the daily query cost before and after?
- Q2: What is the monthly storage cost before and after compression?
- Q3: What are the daily and monthly savings for queries (30 runs/month) and monthly storage savings?
Show solution
Q1: Before: 5 TB × $5 = $25/day. After: 0.7 TB × $5 = $3.50/day.
Q2: 5 TB = 5,000 GB → 5,000 × $0.023 = $115/month. After 3x compression: ~1,667 GB → 1,667 × $0.023 ≈ $38.34/month.
Q3: Query savings/day: $21.50. Query savings/month (30 runs): $645. Storage savings/month: ~$76.66.
Exercise 2: Propose a low-cost partitioning and file strategy
Dataset: clickstream events with fields: event_time (UTC), user_id, session_id, country, device_type, attributes (JSON). Typical queries filter by date range and country; some joins to a small users dimension. Propose:
- Partition key(s) and granularity
- File format and compression
- Compaction target file size
- Optional clustering/bucketing field
- Retention/lifecycle policy
- Two sentences on why this keeps costs low
Show solution
Partition by event_date (daily). Store columnar (Parquet) with snappy/zstd. Compact to ~256 MB target files hourly. Cluster (or bucket) by country or user_id depending on query mix; if country is selective, prefer clustering by country. Retain raw 90 days hot, 365 days aggregated; move older raw to cold storage. This reduces bytes scanned with date filters, improves predicate pushdown and column pruning, and cuts small-file overhead via compaction.
Self-check checklist
- I can estimate cost from bytes scanned and storage rates.
- I can pick a partition key aligned with common filters.
- I can choose columnar format and compression defaults for analytics.
- I can describe a compaction plan with target file sizes.
- I can justify when to use spot/preemptible instances safely.
Common mistakes and how to self-check
- Over-partitioning (e.g., partition by user_id): creates millions of tiny partitions and high metadata cost.
- SELECT * everywhere: scans unnecessary columns and inflates cost.
- Skipping compaction: too many small files slow jobs and increase compute time.
- Unbounded streaming writes: tiny files and high orchestration/API costs.
- Cross-region reads: hidden egress and latency costs.
- Recomputing full history daily: missing incremental loads and idempotency.
Run this self-check
- Open the last 7-day job run. Compare bytes scanned vs input size. If similar, add partition filters and column projection.
- List files in a typical partition. If most are under 64 MB, schedule compaction.
- Inspect joins. If a small table is shuffled, switch to broadcast join with safety limits.
- Check storage class and region. Co-locate compute and storage; move cold data to cheaper tier via lifecycle rules.
- Audit reruns. If a retry doubles cost, make the step idempotent and incremental.
Practical projects
- Cost dashboard: Track cost per run, bytes scanned, and file counts per partition. Add alerts on 2× spikes.
- Compaction service: A scheduled job that merges small files in hot partitions to ~256 MB targets.
- Incremental loader: From raw events to daily aggregates with idempotent upserts and partition pruning.
Who this is for
ETL Developers, Analytics Engineers, and Data Engineers who maintain pipelines that must scale without runaway costs.
Prerequisites
- Basic SQL (filters, joins, aggregates)
- Familiarity with data lakes/warehouses and file formats
- Comfort with batch processing concepts
Learning path
- Understand cost drivers and unit economics.
- Master partitioning, column pruning, and predicate pushdown.
- Implement compaction and lifecycle policies.
- Optimize joins and shuffles; add caching where justified.
- Set up cost monitoring and budgets with alerts.
Next steps
- Apply partition filters to one expensive query today.
- Schedule a compaction job for hot partitions.
- Add a unit metric: cost per GB processed for your top pipeline.
Mini challenge
Pick one pipeline. Reduce its cost by 30% without missing SLA. Document exactly which levers you pulled and the before/after metrics.
Quick Test
Open to everyone. Only logged-in users get saved progress.