Model scan, capacity, credits, storage, and egress separately from one workload trace.
Cost Models: Scan / Compute / Credits / Slots / Storage / Egress and How Physical Design Changes Spend
Build a representative workload profile before changing indexes, SQL, materializations, or concurrency policy.
Learning outcomes
Distinguish scan-based, capacity/slot-based, credit-based, storage, and egress metering dimensions.
Build transparent monthly cost formulas from one fixed workload trace and explicit rate assumptions.
Quantify how pruning/physical design can change scan-metered spend without changing logical results.
Explain why idle policy can dominate credit-metered spend and why egress can dominate either compute model.
Reject list-price comparisons that omit region, edition/contract, free tiers, cache, concurrency, or workload shape.
Chapter 27 begins from the accepted AtlasMart state through Chapter 26: 10 current paid lines, 8 orders, 12 units, 820 USD gross revenue, 495 USD cost, and 325 USD gross profit, with source progress committed through sequence 208. The governed metric contracts, security policies, lineage, tests, SLOs, incident practices, and representative performance corpus remain authoritative. The cloud calculations in this chapter alter only execution and cost assumptions; they never redefine grain, history, keys, or business metrics.
Execution: Python standard library only; no cloud account, paid service, or vendor SDK. Scale reference: Chapter 26’s deterministic 360,000-row performance fixture is used only to motivate workload shape. Cost workload: 30-day synthetic trace with BI, ad-hoc, ELT, and reconciliation classes. Storage: 500 GiB teaching assumption. Egress: 50 GiB/day teaching assumption. Price inputs: 5 USD/TiB scanned, 0.020 USD/GiB-month storage, 0.090 USD/GiB egress, 3 USD/credit, and 0.040 USD/slot-hour are hypothetical teaching inputs, not vendor price quotes. Time zone: UTC. Security: synthetic data only. Cloud limitation: no provisioning, cold-start, cache, network, or remote-shuffle behavior is claimed as measured; those are modeled or described from current official documentation.
1. Realistic problem: three teams each claim their warehouse is cheaper
Finance compares dollars per TiB scanned, the platform team compares dollars per credit, and an analyst compares dollars per slot-hour. Each number can be valid inside its own billing model and still be useless for migration. Cost engineering starts with the same workload and service objective, then calculates each native cost dimension separately.
2. Cost dimensions and formulas
monthly_total = query_or_compute_cost + durable_storage_cost + data_transfer_or_egress_cost + feature/service costs not included abovescan_style_compute = processed_TiB * USD_per_TiBcapacity_style_compute = slot_hours * USD_per_slot_hourcredit_style_compute = credits_consumed * USD_per_credit# Do not equate processed_TiB, slot_hours, and credits.3. Scan-metered style: physical design changes the billable work assumption
| Case | Monthly scan | Compute @ hypothetical 5 USD/TiB | Storage | Egress | Modeled total |
|---|---|---|---|---|---|
| Unoptimized | 61.52 TiB | $307.62 | $10.00 | $135.00 | $452.62 |
| Optimized | 17.58 TiB | $87.89 | $10.00 | $135.00 | $232.89 |
The teaching workload’s pruning/projection assumptions cut scanned volume by 71.43%, reducing the modeled scan charge by about $219.73. Notice that the fixed egress assumption is $135, so scan optimization alone does not eliminate total cost. These dollar rates are intentionally hypothetical.
4. Capacity/slot style: fixed allocated capacity can be independent of bytes
| Input | Value |
|---|---|
| Reserved/autoscaled teaching capacity | 200 slots |
| Usage window | 2 hours/day × 30 days |
| Slot-hours | 12000 |
| Hypothetical rate | $0.040 / slot-hour |
| Compute | $480.00 |
| Storage + egress | $145.00 |
| Total | $625.00 |
Under a capacity model, scanning fewer bytes may still improve latency and free slots for more work, but the monthly currency effect depends on whether capacity actually scales down, commitments change, or workloads fill the freed capacity. Cost and performance are connected but not identical.
5. Credit-metered virtual warehouse style
| Idle policy | Modeled credits | Compute @ hypothetical 3 USD/credit | Why it differs |
|---|---|---|---|
| 60 s suspend | 10.00 | $30.00 | Short burst windows stop consuming compute quickly |
| 600 s suspend | 46.00 | $138.00 | Idle gaps remain billed longer in this trace |
Current Snowflake documentation makes warehouse size, cluster count, and running time central to credit use, with a 60-second minimum each time compute is provisioned. The cost of a credit varies by account/edition/contract/region, which is exactly why this lesson uses a declared teaching rate instead of freezing a price table.
6. Egress and region can invert the decision
The fixture exports 50 GiB/day across a billed boundary, totaling 1,500 GiB/month and $135 at the teaching rate. If BI, object storage, and warehouse sit in different regions or clouds, transfer can become material. If they are co-located or the provider waives a path, the number changes. Therefore every estimate must name source region, destination region/service, direction, and whether the listed path is billable.
7. Controlled failure: compare list prices with different workload semantics
A vendor A estimate applies a seven-day date filter and column projection, while vendor B’s estimate scans the whole fact. Vendor A “wins” even though the workload differs. Another spreadsheet excludes storage and egress for one side. The repair is a cost test harness: identical logical query corpus, explicit physical layout per engine, same result checksum, native metering units, region/edition/contract assumptions, and sensitivity analysis for scan, concurrency, idle gaps, and data transfer.
8. Sensitivity beats a single-number forecast
| Variable | Low case | Base case | High case | Why it matters |
|---|---|---|---|---|
| Monthly processed data | 9 TiB | 17.58 TiB | 60 TiB | On-demand scan charge can move proportionally |
| Warehouse idle gap | 60 s | 10 min | never suspend | Credit consumption can dominate bursty workloads |
| Capacity use | 50 slots | 200 slots | 800 slots | Slot-hours and concurrency service level change |
| Cross-boundary egress | 0 GiB | 1,500 GiB | 10,000 GiB | Data placement can dominate compute optimization |
Use actual observed query history when available. Before migration, rerun the model with provider calculators or billing exports for the target region and contract.
Why can a 70% reduction in scanned bytes fail to reduce a capacity reservation bill by 70%?
Show answer
Because the billing unit is reserved/consumed capacity over time, not bytes. The optimization can improve throughput and free capacity, but currency savings occur only if the capacity commitment/autoscaler usage decreases or freed capacity avoids additional purchases.
9. Production judgment and bridge
Choose a cloud execution model only after the logical warehouse, metric contracts, security boundaries, and reliability SLOs are fixed. Then compare workload-specific latency, concurrency, cold-start exposure, scan/compute/storage/egress units, region placement, ownership burden, and rollback options. A cloud service can automate provisioning without automating semantic correctness, cost governance, incident response, or workload prioritization. Keep vendor-specific physical settings in a decision record so a migration can preserve the logical model while replacing the execution strategy.
Next: Translate the Vendor-Neutral Model into a Cloud Warehouse and Document Which Optimizations Are Engine-Specific.
Knowledge check
Check your understanding
- What is the central mechanism in “Cost Models: Scan/Compute/Credits/Slots/Storage/Egress and How Physical Design Changes Spend”, and which AtlasMart grain or metric contract must remain unchanged?
- Which observable evidence in this lesson distinguishes the correct design from the controlled failure?
- Which assumptions are local or engine-specific, and what must be re-checked before production use?
Review the answers
1. Preserve the lesson’s declared business grain, history semantics, governed metric definitions, and reconciliation controls while changing only the mechanism under study.
2. Use the lesson’s counts, sums, checksums, plans, traces, timing/cost calculations, or failure-state evidence—not a green task status or naming convention alone.
3. Re-check runtime/version, data scale and distribution, cache/concurrency, storage layout, security context, pricing/region where relevant, and the exact product guarantees before production adoption.
Authoritative references
- Google Cloud — BigQuery overviewCurrent documentation for BigQuery's serverless model and separation of storage and compute.
- Google Cloud — BigQuery storage overviewCurrent documentation for managed columnar storage, independent storage/compute scaling, and analytical scan behavior.
- Google Cloud — BigQuery workload managementCurrent documentation distinguishing on-demand bytes processed from capacity-based slot-hour models.
- Google Cloud — BigQuery pricingCurrent pricing-model documentation; this chapter deliberately uses hypothetical local rates instead of freezing region-dependent price numbers.
- Snowflake — Key concepts and architectureCurrent documentation for Snowflake storage, compute, and cloud-services layers and independent virtual warehouses.
- Snowflake — Warehouses overviewCurrent documentation for virtual warehouses, auto-suspend, and auto-resume.
- Snowflake — Warehouse considerationsCurrent documentation for warehouse credit metering, per-second billing after a 60-second minimum, and suspension tradeoffs.
- Snowflake — Understanding overall costCurrent documentation separating compute, storage, and data-transfer costs.
10. Lab cleanup/reset
The mandatory lab is local and synthetic. Delete
ch27_lab/ and rerun the embedded Python calculation
to reproduce the workload/cost evidence. No cloud warehouse,
object-store bucket, reservation, virtual warehouse, billing
account, or credential is created. If you optionally reproduce
examples in a real cloud, use a separate bounded sandbox and
follow that provider’s current cleanup and billing guidance.