Translate the governed model, then re-derive engine-specific layout and workload policy from evidence.
Translate the Vendor-Neutral Model into a Cloud Warehouse and Document Which Optimizations Are Engine-Specific
Build a representative workload profile before changing indexes, SQL, materializations, or concurrency policy.
Learning outcomes
Translate one vendor-neutral AtlasMart dimensional model into two cloud-warehouse execution styles without changing grain, history, or metrics.
Classify every design choice as portable contract, engine-specific physical layout, workload policy, or pricing assumption.
Create a migration decision record with correctness, security, lineage, SLO, cost, and rollback acceptance criteria.
Detect mechanical ports of partition/clustering/distribution settings that do not exist or behave differently on the target.
Validate a cloud migration through golden results and observed native metering instead of vendor folklore.
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: move AtlasMart without rewriting AtlasMart
The company wants to evaluate a serverless scan/capacity style and a virtual-warehouse credit style. The migration succeeds only if consumers still receive the same governed facts, SCD history, metric definitions, security filtering, freshness SLOs, and lineage. Cloud-native physical choices are allowed to differ because the target engines expose different storage and compute mechanisms.
2. Freeze the portable contract first
| Portable contract | AtlasMart requirement | Migration test |
|---|---|---|
| Fact grain | one accepted paid order line per current fact row | 10 current lines in canonical fixture; no fanout |
| Revenue | governed paid-line USD metric | 820 USD canonical control |
| History | event-time SCD resolution and CDC restatements | golden history cases and sequence 208 replay |
| Dimensions | conformed customer/product/date semantics | same durable/natural/surrogate identity rules |
| Security | least privilege + governed row/column policy | allow/deny and bypass tests |
| Reliability | freshness/completeness + incident/runbook contracts | same SLO and reconciliation gates |
| Lineage | source → model → metric → mart → consumer | impact analysis reaches all downstream assets |
3. Style A: serverless/disaggregated scan or capacity model
Portable model: fact/dim tables and governed metrics are unchanged. Engine-specific decisions: date partition/clustering syntax, metadata statistics, bytes-processed behavior, reservation/slot assignment, cache/result behavior, and any automatic layout features. Cost record: keep bytes processed and slot-hours separate; choose the billing model based on observed workload and governance requirements, not a generic recommendation.
-- The exact syntax is provider-specific and must be re-checked.CREATE TABLE fact_sales (...)PARTITION BY <order_date expression>CLUSTER BY <workload-evidenced columns>;-- Verify with native EXPLAIN/profile and bytes/capacity telemetry.-- Do not copy Chapter 17 physical syntax mechanically.4. Style B: virtual-warehouse credit model
Portable model: the same tables/metrics/security contracts. Engine-specific decisions: warehouse size, separate BI vs ELT compute, auto-suspend/resume, multi-cluster/concurrency policy where available, clustering/automatic optimization features, cache behavior, and credit/resource monitoring. The storage and compute layers are separated, but running warehouse time becomes a major cost dimension.
interactive_compute: isolation: certified_bi auto_suspend: workload_evidence_based auto_resume: truebatch_compute: isolation: elt_and_backfill concurrency_cap: explicit budget_monitor: required# Exact property names and availability are vendor-specific.5. Engine-specific choice register
| Decision | Portable principle | Style A implementation | Style B implementation |
|---|---|---|---|
| Date filtering | Avoid irrelevant scan while preserving event-date semantics | partition/pruning + clustering settings | micro-partition pruning / clustering behavior as exposed by target |
| Concurrency | Protect interactive SLOs from noisy neighbors | reservations/slot assignments or quotas | separate warehouses / multi-cluster policy |
| Idle capacity | Do not pay for unused capacity unless it buys required latency | serverless/on-demand or autoscaled capacity behavior | auto-suspend/resume policy |
| Precomputation | Freshness + lineage + reconciliation first | materialized/aggregate feature as supported | materialized/aggregate feature as supported |
| Cost guardrail | Native metering + budget owner | bytes/slot-hours/storage/egress | credits/storage/transfer + monitors |
6. Controlled failure: mechanically port partition/distribution advice
The source system used a distribution key and monthly partitions, so the migration script recreates the same concepts even though the target has no equivalent distribution control and the workload’s filters are now daily. The syntax may be accepted or emulated, but the mechanism no longer matches the workload. The repair is workload-first design: re-run the Chapter 26 corpus, inspect target-native plans/telemetry, preserve result checksums, then choose only the physical controls that the target engine actually supports.
7. Migration acceptance record
| Gate | Evidence required | Rollback trigger |
|---|---|---|
| Correctness | canonical 10/8/12/820/495/325 controls + golden metric/history cases | any semantic mismatch |
| Freshness | source-ready → certified SLO meets contract | consumer freshness breach beyond agreed budget |
| Security | row/column policy + bypass tests + service identities | broader access than source contract |
| Lineage/governance | owners, certification, impact graph intact | orphaned consumer or ownerless certified asset |
| Performance | representative BI/ad-hoc/ELT p50/p95/p99 and queue evidence | tail regression beyond approved threshold |
| Cost | native usage units + storage + egress under stated region/contract | forecast/actual exceeds governed budget |
| Recovery | restart/replay/backfill tested | cannot safely reproduce or roll back partitions |
8. Lab decision record and verification
The local decision artifact classifies declared fact grain, conformed dimensions, metric contracts, lineage, tests, least privilege, and freshness SLOs as portable. It classifies partition/clustering syntax, bytes-scanned billing, slot reservations, warehouse sizes, auto-suspend, multi-cluster scaling, cache behavior, and remote-shuffle implementation as engine-specific examples. The rollback contract is to remove target-specific layout/routing policy while retaining the logical and semantic contracts.
What is the strongest evidence that a cloud migration preserved the warehouse?
Show answer
Not matching table names or DDL. The strongest evidence is that canonical and edge-case business results, history behavior, security rules, lineage, SLOs, replay/reconciliation tests, and consumer contracts all pass on the target while performance/cost are measured in the target’s native telemetry.
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: Warehouse vs Lakehouse: Governance, Transactions, Performance, Open Formats, and Ecosystem Tradeoffs.
Knowledge check
Check your understanding
- What is the central mechanism in “Translate the Vendor-Neutral Model into a Cloud Warehouse and Document Which Optimizations Are Engine-Specific”, 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.