Designing a Medallion Architecture (Bronze, Silver, Gold) Exclusively Using Google BigQuery and Dataform – Google Cloud Architecture, Data Engineering & Native FinOps

Executive Summary

Many cloud data platforms begin by loading raw data into Google BigQuery and building dozens of ad-hoc SQL views or scheduled queries directly on top of those raw tables. As data volume and business complexity grow, this flat structure breaks down: a single upstream schema change breaks executive reports, BI tools repeatedly scan terabytes of unpartitioned raw records on every page load, and engineers cannot tell which scheduled query runs before another.

The Medallion Architecture solves this problem by organizing data into three progressive quality layers—Bronze (Raw/Immutable), Silver (Cleaned/Conformed), and Gold (Aggregated/Business-Ready). Historically, organizations implemented this pattern using external compute frameworks such as Apache Spark or paid third-party transformation tools. Today, Google BigQuery paired with Dataform (Google Cloud’s native, zero-license-cost SQL transformation and DAG orchestration engine) enables teams to build a production-grade Medallion architecture entirely inside the BigQuery control plane.

Key Findings and Architectural Thesis:

Zero Software Licensing Overhead: Dataform is included in Google Cloud at 0.00 USD license cost. Compared to commercial SaaS transformation platforms (which cost between 1,200 USD and 4,500 USD per month for a mid-sized team) or cluster-based Spark lakehouses, a native BigQuery and Dataform stack reduces platform TCO by 62% to 78%.
Layer-Specific Storage Billing Optimization: BigQuery allows configuring Physical Storage Billing (0.04 USD per GiB active) or Logical Storage Billing (0.02 USD per GiB active) at the individual dataset level. Assigning append-only Bronze datasets to Physical Billing captures 4:1 to 6:1 columnar compression savings, while assigning high-churn Silver MERGE datasets to Logical Billing avoids paying for 7-day Time Travel and 7-day Fail-Safe physical snapshots.
Partition-Pruned Incremental Processing: Replacing full-table rebuilds with Dataform incremental models (updatePartitionFilter + clustering) reduces daily BigQuery compute bytes scanned by 85% to 94%.
Compile-Time DAG Safety and Quality Gates: Using Dataform assertions (uniqueKey, nonNull, rowConditions) with dependOnDependencyAssertions: true automatically blocks corrupted Silver tables from propagating into Gold executive marts.

1. Introduction and the Business Problem of Unlayered Data Warehouses

1.1 The Anatomy of an Unstructured Warehouse (“Spaghetti SQL”)

When engineering teams adopt a cloud data warehouse without a strict layering methodology, transformations typically evolve through three anti-patterns:

Direct BI Consumption of Raw Ingestion Tables: Dashboards in Looker Studio, Power BI, or Tableau query raw webhook, streaming, or Change Data Capture (CDC) tables directly. Every dashboard refresh executes complex JSON extraction, string parsing, and DISTINCT deduplication on billions of raw rows.
Deeply Nested View Chains: Analysts build SQL views on top of other SQL views five or six levels deep. Because standard BigQuery views do not materialize data, querying a top-level view triggers a cascading execution of every underlying view from scratch, often failing to push down partition filters.
Uncoordinated Scheduled Queries: Engineers schedule standalone SQL scripts using wall-clock estimates (for example, Job A runs at 02:00 UTC, Job B runs at 02:30 UTC assuming Job A finished). When Job A takes 35 minutes due to a traffic spike, Job B runs against yesterday’s incomplete data, producing silent financial inaccuracies.

1.2 Why Monolithic Transformations Fail Business SLAs

Without explicit separation of responsibilities across data layers, organizations encounter four structural failures:

Non-Idempotent Recovery: If a transformation script mutates a single table in place and fails halfway through, the raw source state is lost. Re-running the pipeline requires re-extracting data from external operational databases or third-party APIs, which may be rate-limited or no longer hold the historical state.
Runaway Compute Billing from Unpruned Scans: Raw streaming tables often contain semi-structured JSON payloads and duplicate network retries. When 50 business users filter dashboards over the last 30 days, BigQuery repeatedly parses the same raw bytes hundreds of times per day instead of reading pre-aggregated dimensional tables once.
Uncontrolled Schema Drift Cascades: When a backend software team renames a field in a microservice payload, every downstream query that touches the raw table breaks simultaneously.
Untested Data Quality: Without intermediate staging checkpoints, null primary keys, duplicate order IDs, or negative transaction amounts flow directly into executive KPIs before anyone notices.

1.3 Quantifying the Financial Impact: Flat Warehouse vs. BigQuery + Dataform Medallion

To evaluate the business case for refactoring an unstructured BigQuery environment into a Dataform-managed Medallion architecture, consider a mid-sized e-commerce and SaaS analytics platform with the following baseline parameters:

Raw data volume: 12 TiB of historical event and transactional data; 80 GiB of new data ingested daily.
Analytical workload: 40 scheduled transformation queries per day + 300 BI dashboard queries per day executed by 25 analysts and business stakeholders.
Engineering team: 4 data engineers at a fully loaded cost of 65.00 USD per hour.

Cost & Operational Dimension	Unlayered Warehouse (Views + Scheduled Queries)	Medallion Architecture (BigQuery + Dataform)	Mathematical Calculation and Underlying Mechanism
Daily Transformation Compute Scanned	48.00 TiB per day	3.20 TiB per day	Unlayered: 40 queries scanning an average of 1.2 TiB each. Medallion: Dataform incremental models scan only a 3-day partition window (approx. 80 GiB per model across 40 models).
Daily BI Dashboard Compute Scanned	30.00 TiB per day	0.60 TiB per day	Unlayered: 300 refreshes scanning 100 GiB of Silver/Bronze views. Medallion: 300 refreshes scanning 2 GiB Gold aggregated tables.
Total Monthly Compute Scanned (30 Days)	2,340.00 TiB per month	114.00 TiB per month	Unlayered: (48 + 30) multiplied by 30 days. Medallion: (3.2 + 0.6) multiplied by 30 days.
Monthly BigQuery On-Demand Compute Cost	14,618.75 USD	706.25 USD	Billed at 6.25 USD per TiB after the first 1 TiB free tier: (2,339 TiB * 6.25 USD) vs. (113 TiB * 6.25 USD).
Monthly Pipeline Breakages & Incident Recovery	10 incidents (40 engineer-hours = 2,600.00 USD)	1.5 incidents (3 engineer-hours = 195.00 USD)	Dataform DAG dependency ordering and pre-Gold blocking assertions eliminate race conditions and silent schema drift.
Total Monthly Platform & Operational Cost	17,218.75 USD per month	901.25 USD per month	Net monthly savings: 16,317.50 USD (195,810.00 USD annually — a 94.8% reduction in combined waste).

2. Mechanics of the Three Medallion Layers in BigQuery and Dataform

To design an optimal Medallion architecture on Google Cloud, each layer must be mapped to a specific GCP Dataset, BigQuery Storage Billing Model, Partitioning and Clustering Strategy, and Dataform Action Type. Moving from Bronze to Gold represents a progression from raw write-optimized capture to read-optimized business semantics.

2.1 The Bronze Layer: Raw Ingestion and Immutable History

Primary Objective: Capture an exact, immutable, append-only record of source systems (webhooks, Pub/Sub streams, CDC logs, batch CSV/Parquet extracts) with zero business logic applied. If a bug is discovered in Silver or Gold logic six months later, the entire warehouse can be deterministically replayed from Bronze without contacting external APIs.

BigQuery & Dataform Mechanics in Bronze:

Dataform Representation: Registered primarily as type: "declaration" sources inside Dataform, or lightweight append-only type: "incremental" tables that ingest from BigLake external tables (Cloud Storage Parquet/JSON) without filtering rows.
Schema Design: Preserves source payload structures using BigQuery’s native JSON data type for semi-structured payloads alongside metadata ingestion columns: _ingested_at (TIMESTAMP), _source_system (STRING), and _batch_id (STRING).
Partitioning & Clustering: Always partitioned by ingestion timestamp (DATE(_ingested_at)) with require_partition_filter = true. Clustered by _source_system and primary entity key if available.
Storage Billing Model: Physical Storage Billing (0.04 USD per GiB active / 0.02 USD per GiB long-term). Because Bronze tables are strictly append-only (zero UPDATE, DELETE, or MERGE statements), they generate zero bytes of Time-Travel or Fail-Safe overhead from mutated micro-partitions. Meanwhile, repeating JSON keys and raw text strings achieve 4:1 to 7:1 Zstandard columnar compression in BigQuery’s Capacitor storage format, cutting storage costs by 50% to 70% compared to Logical Billing.

2.2 The Silver Layer: Cleaned, Conformed, Deduplicated, and Entity-Centric

Primary Objective: Transform raw Bronze payloads into a single source of truth for atomic business entities (orders, customers, events, subscriptions, inventory_movements). Silver resolves data types, deduplicates at-least-once streaming records, handles late-arriving CDC updates, standardizes timestamps to UTC, and enforces strict data contracts.

BigQuery & Dataform Mechanics in Silver:

Dataform Representation: Implemented as type: "incremental" models with an explicit uniqueKey (triggering a BigQuery MERGE statement) or partition-overwrite strategies, paired with type: "assertion" quality tests.
Schema Design: Strongly typed relational columns (INT64, NUMERIC, TIMESTAMP, STRUCT, ARRAY). Semi-structured JSON fields from Bronze are extracted and cast using SAFE_CAST, LAX_INT64, or STRING.
Partitioning & Clustering: Partitioned by the business event date (for example, DATE(event_timestamp) or DATE(order_created_at)), NOT the ingestion timestamp. Clustered by high-cardinality join and filter keys (up to 4 columns, ordered from highest to lowest filter frequency, such as customer_id, order_status, country_code).
Storage Billing Model: Logical Storage Billing (0.02 USD per GiB active / 0.01 USD per GiB long-term). Because Silver tables undergo frequent incremental MERGE or partition replacement operations (often every 15 to 60 minutes), every modified storage block creates a historical physical snapshot retained for Time Travel (2 to 7 days) and Fail-Safe (7 days). Under Logical Billing, BigQuery charges 0.00 USD for Time-Travel and Fail-Safe bytes, preventing physical storage bloat.

2.3 The Gold Layer: Business Aggregates, Star Schemas, and Serving Marts

Primary Objective: Deliver fast, denormalized, business-domain datasets (mart_finance_daily_revenue, mart_marketing_attribution, dim_customer_360) tailored for BI dashboards, executive reporting, machine learning feature stores, and reverse-ETL activation.

BigQuery & Dataform Mechanics in Gold:

Dataform Representation: Implemented as type: "table" (for small-to-medium dimensional tables rebuilt atomically via CREATE OR REPLACE TABLE), type: "incremental" (for large daily grain rollups), or native BigQuery Materialized Views (materialized: true on type: "view").
Schema Design: Wide, pre-joined tables or Kimball Star Schemas (Fact and Dimension tables) with human-readable business metric names, pre-calculated ratios, and Fine-Grained Access Control (Policy Tags and Row-Level Access Policies).
Partitioning & Clustering: Partitioned by reporting grain (report_date or DATE_TRUNC(metric_date, MONTH)), clustered by primary dashboard filter dimensions (region, product_category, acquisition_channel).
Storage Billing Model: Logical Storage Billing (for tables rebuilt daily via CREATE OR REPLACE TABLE, which otherwise rewrite 100% of physical blocks every day and multiply physical Time-Travel storage by 14x over a 7-day window + 7-day fail-safe) or Physical Billing only for large, append-only historical snapshot marts with a reduced 48-hour time-travel window.

2.4 Comprehensive Architectural Mapping Matrix Across Medallion Layers

Architectural Dimension	Bronze Layer (Raw)	Silver Layer (Conformed & Cleaned)	Gold Layer (Business Marts)
GCP Dataset Naming Convention	`raw_bronze_<source>` (e.g., `raw_bronze_stripe`)	`core_silver_<domain>` (e.g., `core_silver_commerce`)	`mart_gold_<department>` (e.g., `mart_gold_finance`)
Primary Dataform Model Types	`declaration`, append-only `incremental`	`incremental` (`MERGE` / partition overwrite), `assertion`	`table`, `incremental`, `view` (`materialized: true`)
Write Pattern (DML / DDL)	`INSERT INTO` / Storage Write API / BigLake Load	`MERGE INTO` on `uniqueKey` or partition `DELETE + INSERT`	`CREATE OR REPLACE TABLE` or incremental grain `MERGE`
Data Mutability	Strictly Immutable (Append-only)	Mutable via deterministic upsert / CDC state resolution	Recomputable at any time from Silver
Partitioning Column	Ingestion Time (`DATE(_ingested_at)`)	Business Event Time (`DATE(event_timestamp)`)	Reporting Date (`report_date` / `metric_month`)
Clustering Columns (Max 4)	`_source_system, event_type`	`entity_id (PK), foreign_key_1, status`	`country, channel, product_tier, segment`
Recommended BigQuery Storage Billing	Physical Billing (0.04 USD/GiB active; zero DML churn + high compression)	Logical Billing (0.02 USD/GiB active; immune to `MERGE` Time-Travel bloat)	Logical Billing (0.02 USD/GiB active; immune to `CREATE OR REPLACE` rewrite bloat)
Recommended Time-Travel Window	168 hours (7 days)	48 to 168 hours (free on Logical Billing)	48 hours
Access Permissions (IAM)	Service accounts only (`sa-ingestion`, `sa-dataform`)	Data Engineers & Dataform Service Account	BI Service Accounts, Analysts, Domain Business Users

3. System Comparison: Native BigQuery + Dataform vs. Competing Stacks

Before committing to a pure BigQuery and Dataform architecture, technical decision-makers must understand how this native stack compares against alternative transformation and lakehouse platforms in terms of internal architecture, scaling mechanics, and total cost of ownership.

3.1 Evaluated Transformation and Lakehouse Platforms

BigQuery + Dataform (Native GCP Stack): Serverless SQL compilation and DAG execution built directly into the Google Cloud console and APIs, executing transformations on BigQuery’s Dremel engine.
BigQuery + dbt Cloud (SaaS Control Plane): Commercial hosted transformation platform by dbt Labs using Jinja/Python macros to compile SQL and submit jobs to BigQuery.
BigQuery + dbt Core on Cloud Run / Cloud Composer (Self-Hosted OSS): Open-source dbt-bigquery CLI packaged inside Docker containers and orchestrated by Managed Apache Airflow (Cloud Composer) or Cloud Workflows.
Databricks Lakehouse (Delta Live Tables + Unity Catalog): Spark-based lakehouse architecture storing Delta Lake / Parquet files in object storage, using declarative Delta Live Tables (DLT) on Photon compute clusters for Bronze, Silver, and Gold pipelines.
Snowflake (Dynamic Tables + Tasks / Snowpark): Cloud data warehouse using automated declarative Dynamic Tables and Virtual Warehouses (T-shirt sized compute clusters) to refresh Medallion layers.

3.2 Deep Dive: Internal Mechanisms (Storage, Compute, Compilation, Scaling, and Pricing)

1. Native BigQuery + Dataform

Storage Mechanism: BigQuery’s proprietary Capacitor columnar format stored on Colossus (Google’s distributed file system), plus native support for BigLake managed Apache Iceberg tables on Cloud Storage. Storage and compute are completely decoupled across a petabit-per-second Jupiter network fabric.
Compute & Execution Mechanism: Dataform runs a serverless Node.js compilation sandbox inside Google Cloud that resolves JavaScript and SQLX files into a deterministic Directed Acyclic Graph (DAG) of standard GoogleSQL statements. Dataform’s workflow invoker submits each node asynchronously to BigQuery via the Jobs API, respecting parent-child dependencies and concurrency limits. All data processing happens inside BigQuery’s distributed Dremel shuffle-in-memory execution tree—zero data ever leaves BigQuery to pass through the Dataform container.
Scaling: Auto-scales per query up to 2,000 slots on-demand or scales in 50-slot increments within milliseconds using BigQuery Editions autoscaling reservations.
Pricing Model:
- Dataform Service: 0.00 USD (completely free for unlimited users, repositories, workspaces, and executions).
- BigQuery Compute: 6.25 USD per TiB on-demand (first 1 TiB free) or starting at 0.04 USD per slot-hour (Standard Edition) / 0.06 USD per slot-hour (Enterprise Edition).
- BigQuery Storage: 0.02 USD per GiB logical active (0.01 USD long-term) or 0.04 USD per GiB physical active (0.02 USD long-term).

2. BigQuery + dbt Cloud

Storage & Compute Mechanism: Identical warehouse execution (BigQuery Capacitor + Dremel), because dbt also pushes SQL down to BigQuery.
Compilation & Orchestration Mechanism: Uses Python and Jinja2 templating on dbt Labs’ multi-tenant SaaS infrastructure. While Jinja is widely adopted and has a rich package ecosystem (dbt_utils, dbt_expectations), parsing large Jinja macros across 1,000+ models in Python is significantly slower (often 20 to 60 seconds per compile) than Dataform’s native V8 JavaScript engine (typically 1 to 3 seconds).
Pricing Model:
- Team Plan: 100.00 USD per developer seat per month (up to 8 seats), includes 15,000 free successful models built per month; additional model runs cost 0.01 USD per successful model build.
- Enterprise Plan: Custom annual contract (typically 3,000 USD to 5,000+ USD per month) plus per-model-execution consumption fees on modern usage tiers.

3. BigQuery + dbt Core on Cloud Composer (Managed Airflow)

Compilation & Orchestration Mechanism: Engineers maintain a CI/CD pipeline that builds a Docker image containing dbt-core and dbt-bigquery, pushed to Artifact Registry. Cloud Composer (Apache Airflow running on a dedicated GKE cluster) triggers KubernetesPodOperators or Cloud Run Jobs to run dbt build.
Scaling & Weaknesses: Running a persistent Cloud Composer 2 or 3 environment just to trigger SQL queries introduces an always-on infrastructure baseline cost (300 USD to 750 USD per month) plus operational overhead for upgrading Airflow versions, managing Python dependency conflicts, and parsing dbt manifest.json into Airflow tasks via cosmos.
Pricing Model: dbt Core is free open-source software, but Cloud Composer environments cost roughly 320.00 USD to 800.00 USD per month for compute, database, and GKE management fees.

4. Databricks Lakehouse (Delta Live Tables)

Storage Mechanism: Open-format Delta Lake (Parquet files + transaction logs) stored in customer-managed Cloud Storage (GCS/S3).
Compute Mechanism: Declarative pipelines run on dedicated or serverless Apache Spark / Photon clusters. Unlike BigQuery’s shared multi-tenant serverless slot pool where a query finishes in 4 seconds and bills only for those 4 seconds, Spark clusters often incur cluster spin-up latency (30 to 180 seconds on classic compute) or higher per-DBU serverless rates.
Pricing Model: Billed in Databricks Units (DBUs) (ranging from 0.20 USD to 0.55+ USD per DBU depending on Delta Live Tables Core/Pro/Advanced tier and Serverless vs. Classic) plus underlying cloud VM instances, block storage, and GCS API operation fees.

5. Snowflake (Dynamic Tables + Virtual Warehouses)

Storage Mechanism: Proprietary micro-partitioned columnar files stored on cloud object storage.
Compute Mechanism: Uses T-shirt sized Virtual Warehouses (X-Small = 1 credit/hour, Small = 2 credits/hour, Medium = 4 credits/hour, doubling each step). A virtual warehouse bills for a 60-second minimum upon resumption and bills per second for the entire cluster capacity even if a running query only utilizes 20% of the cluster’s CPU/memory.
Pricing Model: Credits cost between 2.00 USD and 4.00 USD per credit (Standard vs. Enterprise vs. Business Critical), plus 23.00 USD to 40.00 USD per TB per month for compressed storage.

3.3 Comprehensive Feature and Mechanism Matrix

Evaluation Dimension	BigQuery + Dataform (Native)	BigQuery + dbt Cloud	BigQuery + dbt Core + Composer	Databricks (Delta Live Tables)	Snowflake (Dynamic Tables)
Transformation Language	GoogleSQL + JavaScript (ES6+)	GoogleSQL + Jinja2 (Python)	GoogleSQL + Jinja2 (Python)	Spark SQL / PySpark	Snowflake SQL / Snowpark
Compilation Speed (500 Models)	1 to 3 seconds (V8 JS engine)	15 to 45 seconds (Python Jinja)	15 to 45 seconds (Container cold start + Jinja)	10 to 30 seconds (Spark Catalyst planner)	Native DDL evaluation
Data Residency & Security	100% inside GCP IAM boundary	Credentials & metadata shared with external SaaS	100% inside GCP IAM boundary	Compute & GCS inside GCP/AWS/Azure	Hosted inside Snowflake VPC
Built-in Data Quality Gates	Native `assertions` (`dependOnDependencyAssertions`)	`dbt test` (blocking in `dbt build`)	`dbt test` (blocking in `dbt build`)	DLT `@dlt.expect_or_fail`	Snowflake Data Metric Functions (DMFs)
Compute Billing Granularity	Per-byte scanned OR per-millisecond slot usage	Same (BigQuery compute) + SaaS per-model fee	Same (BigQuery compute) + 24/7 Airflow cluster	Per-DBU + VM uptime seconds	Per-second per Virtual Warehouse (60s min)
Programmatic Code Generation	Native JS loops (`forEach`, functions, modules)	Jinja macros (verbose for complex data structures)	Jinja macros	Python metaprogramming	SQL scripting / Snowpark Python
External SaaS / Infra License Fee	0.00 USD	100 USD/seat + 0.01 USD/model run	320 USD to 800 USD/mo (Composer)	Included in DBU markup	Included in Credit markup

3.4 Financial TCO Comparison: Explicit Calculations for a 350-Model Medallion Pipeline

Let us calculate the exact monthly TCO for a realistic production Medallion platform with the following parameters:

Team Size: 10 analytics and data engineers developing and reviewing transformations.
Pipeline Scale: 350 total models and assertions across Bronze, Silver, and Gold (50 Bronze declarations/loads, 180 Silver incremental models + 60 assertions, 60 Gold marts).
Execution Frequency: Runs 6 times per day (every 4 hours) = 300 executable actions * 6 runs/day * 30 days = 54,000 action executions per month.
Data Volume: 25 TiB Active Logical volume across all layers (compressed to 5 TiB Physical volume).
Compute Scanned per Full DAG Run: Thanks to incremental partition pruning, each 4-hour DAG run scans 450 GiB across all 300 executable actions (450 GiB * 6 runs/day * 30 days = 81,000 GiB = 79.10 TiB scanned per month).

Platform Option	Component Cost Breakdown (Explicit Math)	Estimated Monthly Total (USD)	Estimated Annual Total (USD)
1. Native BigQuery + Dataform + Cloud Workflows	1. Dataform License: 10 users, 54,000 model runs = 0.00 USD. 2. Cloud Workflows + Cloud Scheduler: 180 runs/month (first 5,000 steps free) = 0.10 USD. 3. BigQuery Storage (Hybrid Billing): 15 TiB Bronze Logical compressed to 2.5 TiB Physical (2,560 GiB * 0.04 USD = 102.40 USD) + 10 TiB Silver/Gold Logical (10,240 GiB * 0.02 USD = 204.80 USD) = 307.20 USD. 4. BigQuery Compute (On-Demand): (79.10 TiB minus 1.0 TiB free tier) * 6.25 USD/TiB = 488.13 USD.	795.43 USD	9,545.16 USD
2. BigQuery + dbt Cloud (Enterprise / Team Scale)	1. BigQuery Storage & Compute: Identical = 795.33 USD. 2. dbt Cloud Base Seats: 10 developer seats (requires Enterprise or multi-seat tier, approx. 250 USD/seat) = 2,500.00 USD. 3. dbt Cloud Model Consumption: (54,000 successful models minus 15,000 included) * 0.01 USD = 390.00 USD.	3,685.33 USD	44,223.96 USD
3. BigQuery + dbt Core on Cloud Composer 3	1. BigQuery Storage & Compute: Identical = 795.33 USD. 2. Cloud Composer Small Environment: 24/7 Airflow scheduler, web server, database, and worker pod executions = 385.00 USD. 3. Artifact Registry & Cloud Build CI:15.00 USD.	1,195.33 USD	14,343.96 USD
4. Databricks Lakehouse (DLT Pro + GCS)	1. GCS Storage (5 TiB compressed + transaction log overhead): 5,500 GiB * 0.02 USD + Class A/B ops = 135.00 USD. 2. DLT Compute + Interactive SQL Warehouses: 6 daily runs + BI serving (approx. 3,800 DBUs at blended 0.45 USD/DBU + underlying GCE VMs) = 2,480.00 USD.	2,615.00 USD	31,380.00 USD
5. Snowflake (Dynamic Tables + Enterprise Credits)	1. Snowflake Compressed Storage (5 TiB + Time Travel): 5.5 TB * 23.00 USD = 126.50 USD. 2. Compute Credits (Transformation + BI Warehouse): Approx. 1,100 credits/month * 3.00 USD/credit (Enterprise Edition) = 3,300.00 USD.	3,426.50 USD	41,118.00 USD

Analytical Conclusion: Using native BigQuery and Dataform with Cloud Workflows delivers the entire Medallion architecture for 795.43 USD per month, saving 2,889.90 USD per month (34,678.80 USD per year) compared to dbt Cloud on the exact same warehouse, and 1,819.57 USD to 2,631.07 USD per month compared to Databricks or Snowflake.

4. End-to-End Implementation: Terraform, Dataform Core, and F#

To build a resilient Medallion pipeline, we divide implementation into three clean layers of code:

Infrastructure as Code (Terraform): Provisioning isolated Bronze, Silver, and Gold datasets with tailored storage billing models (PHYSICAL vs. LOGICAL), creating the Dataform repository, and configuring least-privilege IAM.
Transformation & Quality Code (Dataform JavaScript & SQLX): Defining source declarations, incremental partition-pruned Silver transformations, blocking quality assertions, and Gold analytical marts. (Note: All examples below use Dataform’s official JavaScript Core API ctx.ref() and string concatenation so zero currency symbols appear anywhere in the codebase.)
Programmatic Contract & Dry-Run Validator (F#): A strongly typed F# validator and orchestrator that executes a BigQuery dry-run cost check before triggering a Dataform workflow invocation.

4.1 Stage 1: Provisioning Medallion Datasets and Dataform via Terraform

Notice how the Terraform code below explicitly sets storage_billing_model = "PHYSICAL" on the append-only Bronze dataset and storage_billing_model = "LOGICAL" on the high-churn Silver and Gold datasets.

Terraform

terraform {
  required_version = ">= 1.6.0"
  required_providers {
    google = {
      source  = "hashicorp/google"
      version = ">= 5.30.0"
    }
    google-beta = {
      source  = "hashicorp/google-beta"
      version = ">= 5.30.0"
    }
  }
}

variable "project_id" {
  type        = string
  description = "GCP Project ID hosting the Medallion Data Warehouse"
}

variable "region" {
  type        = string
  default     = "US"
  description = "BigQuery multi-region or regional location"
}

variable "git_remote_url" {
  type        = string
  description = "HTTPS URL of the Git repository containing Dataform code"
}

# 1. Dedicated Service Account for Dataform Execution
resource "google_service_account" "dataform_runner" {
  project      = var.project_id
  account_id   = "sa-dataform-medallion"
  display_name = "Dataform Medallion Pipeline Executor"
}

# 2. BRONZE Dataset: Append-only -> Physical Storage Billing for 4:1+ compression savings
resource "google_bigquery_dataset" "bronze_raw" {
  project                    = var.project_id
  dataset_id                 = "raw_bronze_commerce"
  location                   = var.region
  description                = "Bronze Layer: Immutable raw append-only ingestion tables"
  storage_billing_model      = "PHYSICAL"
  max_time_travel_hours      = "168" # 7 days (zero mutation churn on append-only tables)
  delete_contents_on_destroy = false

  labels = {
    medallion_layer = "bronze"
    billing_strategy = "physical_compressed"
  }
}

# 3. SILVER Dataset: Frequent MERGE upserts -> Logical Storage Billing to avoid Time-Travel bloat
resource "google_bigquery_dataset" "silver_core" {
  project                    = var.project_id
  dataset_id                 = "core_silver_commerce"
  location                   = var.region
  description                = "Silver Layer: Deduplicated, cleaned, and conformed entity tables"
  storage_billing_model      = "LOGICAL"
  max_time_travel_hours      = "168" # Free under Logical Billing
  delete_contents_on_destroy = false

  labels = {
    medallion_layer = "silver"
    billing_strategy = "logical_merge_safe"
  }
}

# 4. GOLD Dataset: Rebuilt marts & aggregates -> Logical Storage Billing
resource "google_bigquery_dataset" "gold_mart" {
  project                    = var.project_id
  dataset_id                 = "mart_gold_finance"
  location                   = var.region
  description                = "Gold Layer: Executive financial and attribution data marts"
  storage_billing_model      = "LOGICAL"
  max_time_travel_hours      = "48"
  delete_contents_on_destroy = false

  labels = {
    medallion_layer = "gold"
    billing_strategy = "logical_mart"
  }
}

# 5. Dataform Assertions Dataset: Stores failed assertion rows for debugging
resource "google_bigquery_dataset" "dataform_assertions" {
  project                    = var.project_id
  dataset_id                 = "audit_dataform_assertions"
  location                   = var.region
  description                = "Holds views and results of Dataform quality assertions"
  storage_billing_model      = "LOGICAL"
  max_time_travel_hours      = "48"
}

# 6. Provision the Native Dataform Repository
resource "google_dataform_repository" "medallion_repo" {
  provider        = google-beta
  project         = var.project_id
  region          = "us-central1"
  name            = "commerce-medallion-pipeline"
  service_account = google_service_account.dataform_runner.email

  git_remote_settings {
    url                                 = var.git_remote_url
    default_branch                      = "main"
    authentication_token_secret_version = format("projects/%s/secrets/dataform-git-token/versions/latest", var.project_id)
  }

  workspace_compilation_overrides {
    default_database = var.project_id
  }
}

4.2 Stage 2: Implementing Bronze, Silver, and Gold in Dataform (JavaScript Core API)

Dataform provides two equivalent ways to define models: .sqlx template files and .js programmatic definitions using the global declare(), publish(), and assert() functions. Using the JavaScript Core API (definitions/medallion_pipeline.js) provides two distinct engineering advantages:

It avoids template literal interpolation symbols completely, ensuring clean rendering across documentation and CMS platforms.
It allows programmatic helper functions, dynamic loops, and strict compile-time validation of partition filters.

Below is a complete, production-grade Dataform definition file (definitions/commerce_medallion.js) implementing:

Bronze Declaration: raw_orders_stream (partitioned by _ingested_at).
Silver Incremental Model: stg_orders_conformed with a 3-day sliding partition pruning filter (updatePartitionFilter), deterministic deduplication via QUALIFY ROW_NUMBER(), and automated blocking assertions (uniqueKey, nonNull, rowConditions).
Gold Analytical Mart: fct_daily_revenue_mart configured with dependOnDependencyAssertions: true so it never executes if Silver quality assertions fail.

JavaScript

// File: definitions/commerce_medallion.js
// Native Dataform Core API implementation of Bronze -> Silver -> Gold

// 1. BRONZE LAYER: Declare the immutable raw streaming ingestion table
declare({
  database: dataform.projectConfig.defaultDatabase,
  schema: "raw_bronze_commerce",
  name: "raw_orders_stream"
});

// 2. SILVER LAYER: Incremental MERGE model with strict partition pruning and quality gates
publish("stg_orders_conformed", {
  type: "incremental",
  schema: "core_silver_commerce",
  description: "Cleaned, deduplicated, and strongly typed orders at 1 row per order_id",
  tags: ["silver", "commerce", "hourly"],

  // BigQuery physical layout and partition pruning configuration
  bigquery: {
    partitionBy: "DATE(order_created_at)",
    clusterBy: ["customer_id", "order_status", "country_code"],
    updatePartitionFilter:
      "order_created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)",
    labels: {
      medallion_layer: "silver",
      domain: "commerce"
    }
  },

  // Unique key triggers an atomic BigQuery MERGE statement
  uniqueKey: ["order_id"],

  // Built-in Dataform Quality Assertions (automatically create blocking assertion nodes)
  assertions: {
    uniqueKey: ["order_id"],
    nonNull: ["order_id", "customer_id", "order_created_at", "net_amount_usd"],
    rowConditions: [
      "net_amount_usd >= 0",
      "tax_amount_usd >= 0",
      "LENGTH(country_code) = 2"
    ]
  },

  // Ensure any upstream assertions pass before running this model
  dependOnDependencyAssertions: true
}).query(function(ctx) {
  // When running incrementally, scan only the last 3 days of Bronze ingestion partitions
  var incrementalWhereClause = ctx.when(
    ctx.incremental(),
    "WHERE DATE(_ingested_at) >= DATE_SUB(CURRENT_DATE('UTC'), INTERVAL 3 DAY)",
    "WHERE DATE(_ingested_at) >= DATE('2024-01-01')"
  );

  return (
    "SELECT " +
    "  STRING(json_payload.order_id) AS order_id, " +
    "  STRING(json_payload.customer_id) AS customer_id, " +
    "  UPPER(TRIM(STRING(json_payload.country_code))) AS country_code, " +
    "  LOWER(TRIM(STRING(json_payload.status))) AS order_status, " +
    "  TIMESTAMP(STRING(json_payload.created_at)) AS order_created_at, " +
    "  TIMESTAMP(STRING(json_payload.updated_at)) AS order_updated_at, " +
    "  ROUND(SAFE_CAST(JSON_VALUE(json_payload, '$.net_amount') AS NUMERIC), 2) AS net_amount_usd, " +
    "  ROUND(SAFE_CAST(JSON_VALUE(json_payload, '$.tax_amount') AS NUMERIC), 2) AS tax_amount_usd, " +
    "  _ingested_at AS bronze_ingested_at " +
    "FROM " + ctx.ref("raw_bronze_commerce", "raw_orders_stream") + " " +
    incrementalWhereClause + " " +
    // Deterministic deduplication: keep only the latest state per order_id
    "QUALIFY ROW_NUMBER() OVER ( " +
    "  PARTITION BY STRING(json_payload.order_id) " +
    "  ORDER BY TIMESTAMP(STRING(json_payload.updated_at)) DESC, _ingested_at DESC " +
    ") = 1"
  );
});

// 3. CUSTOM CROSS-COLUMN SILVER ASSERTION: Detect anomalous refund ratios
assert("assert_silver_orders_valid_timestamps", {
  schema: "audit_dataform_assertions",
  description: "Fails if any order has an updated_at timestamp earlier than created_at",
  tags: ["silver", "data_quality"]
}).query(function(ctx) {
  return (
    "SELECT " +
    "  order_id, " +
    "  order_created_at, " +
    "  order_updated_at " +
    "FROM " + ctx.ref("core_silver_commerce", "stg_orders_conformed") + " " +
    "WHERE DATE(order_created_at) >= DATE_SUB(CURRENT_DATE('UTC'), INTERVAL 7 DAY) " +
    "  AND order_updated_at < order_created_at"
  );
});

// 4. GOLD LAYER: Executive Daily Revenue Mart (Blocked if Silver assertions fail)
publish("fct_daily_revenue_mart", {
  type: "incremental",
  schema: "mart_gold_finance",
  description: "Daily aggregated revenue, tax, and order counts by country and status",
  tags: ["gold", "finance", "executive_sla"],

  // CRITICAL: Blocks Gold execution if stg_orders_conformed assertions return > 0 rows
  dependOnDependencyAssertions: true,

  // Explicitly depend on our custom timestamp assertion as well
  dependencies: ["assert_silver_orders_valid_timestamps"],

  uniqueKey: ["report_date", "country_code", "order_status"],

  bigquery: {
    partitionBy: "report_date",
    clusterBy: ["country_code", "order_status"],
    updatePartitionFilter:
      "report_date >= DATE_SUB(CURRENT_DATE('UTC'), INTERVAL 7 DAY)",
    labels: {
      medallion_layer: "gold",
      department: "finance"
    }
  }
}).query(function(ctx) {
  var silverFilter = ctx.when(
    ctx.incremental(),
    "WHERE DATE(order_created_at) >= DATE_SUB(CURRENT_DATE('UTC'), INTERVAL 7 DAY)",
    "WHERE DATE(order_created_at) >= DATE('2024-01-01')"
  );

  return (
    "SELECT " +
    "  DATE(order_created_at) AS report_date, " +
    "  country_code, " +
    "  order_status, " +
    "  COUNT(DISTINCT order_id) AS total_orders, " +
    "  COUNT(DISTINCT customer_id) AS unique_customers, " +
    "  SUM(net_amount_usd) AS total_net_revenue_usd, " +
    "  SUM(tax_amount_usd) AS total_tax_usd, " +
    "  SUM(net_amount_usd + tax_amount_usd) AS total_gross_revenue_usd, " +
    "  CURRENT_TIMESTAMP() AS gold_refreshed_at " +
    "FROM " + ctx.ref("core_silver_commerce", "stg_orders_conformed") + " " +
    silverFilter + " " +
    "GROUP BY 1, 2, 3"
  );
});

4.3 Stage 3: Pre-Flight Cost Estimation and Medallion Contract Validator in F#

When running a Medallion pipeline in CI/CD or before triggering a production backfill, engineers need a reliable way to verify that a compiled Dataform query will not accidentally trigger a multi-terabyte full-table scan.

Below is a self-contained F# module (MedallionCostGuard.fs) that takes compiled SQL statements from a Dataform compilation result, runs a zero-cost BigQuery Dry Run (QueryOptions.DryRun <- Nullable(true)), calculates the exact estimated bytes scanned and on-demand USD cost, and blocks execution if any Silver or Gold step exceeds its layer-specific byte budget.

namespace TechMacro.Medallion

open System
open Google.Cloud.BigQuery.V2

/// Represents the Medallion layer and its maximum allowed scan budget per execution
type MedallionLayer =
    | Bronze of maxGiBAllowed: float
    | Silver of maxGiBAllowed: float
    | Gold   of maxGiBAllowed: float

/// Result of a pre-flight BigQuery Dry-Run evaluation
type DryRunEvaluation =
    | Approved of layer: string * estimatedGiB: float * estimatedCostUsd: float
    | Rejected of layer: string * estimatedGiB: float * limitGiB: float * reason: string

module MedallionCostGuard =

    let private bytesPerGiB = 1073741824.0
    let private bytesPerTiB = 1099511627776.0
    let private onDemandPricePerTiBUsd = 6.25

    /// Extracts the layer name and GiB ceiling from the discriminated union
    let private getLayerPolicy (layer: MedallionLayer) : (string * float) =
        match layer with
        | Bronze maxGiB -> ("BRONZE", maxGiB)
        | Silver maxGiB -> ("SILVER", maxGiB)
        | Gold   maxGiB -> ("GOLD", maxGiB)

    /// Executes a zero-cost BigQuery Dry Run to validate SQL syntax and byte scan volume
    /// before Dataform submits the actual job to the warehouse.
    let evaluateCompiledActionSql
        (client: BigQueryClient)
        (actionName: string)
        (layer: MedallionLayer)
        (compiledSql: string) : DryRunEvaluation =

        let (layerName, maxGiBAllowed) = getLayerPolicy layer
        let options = QueryOptions()
        options.DryRun <- Nullable<bool>(true)
        options.UseQueryCache <- Nullable<bool>(false)

        try
            let dryRunJob = client.CreateQueryJob(compiledSql, null, options)
            let totalBytes =
                if dryRunJob.Statistics.TotalBytesProcessed.HasValue then
                    float dryRunJob.Statistics.TotalBytesProcessed.Value
                else
                    0.0

            let estimatedGiB = Math.Round(totalBytes / bytesPerGiB, 3)
            let estimatedCostUsd = Math.Round((totalBytes / bytesPerTiB) * onDemandPricePerTiBUsd, 4)

            if estimatedGiB > maxGiBAllowed then
                let msg =
                    sprintf "Action '%s' in %s layer scans %.3f GiB (%.4f USD), exceeding cap of %.3f GiB. Check partition pruning!"
                        actionName layerName estimatedGiB estimatedCostUsd maxGiBAllowed
                Rejected (layerName, estimatedGiB, maxGiBAllowed, msg)
            else
                Approved (layerName, estimatedGiB, estimatedCostUsd)

        with
        | :? Google.GoogleApiException as ex ->
            let errMsg = sprintf "SQL Compilation or Schema Error in '%s': %s" actionName ex.Message
            Rejected (layerName, 0.0, maxGiBAllowed, errMsg)

5. Eight Practical Production Cases: Architectural Deep Dives

Let us analyze eight concrete production scenarios demonstrating how BigQuery and Dataform solve complex engineering challenges across Bronze, Silver, and Gold layers, supported by explicit storage and compute calculations.

Case 1: High-Volume GA4 and Server-Side GTM Event Stream into Bronze, Sessionization in Silver, and Attribution Mart in Gold

Business Context: An enterprise retailer collects 120 million web and app events per day (roughly 180 GiB/day raw) via Google Analytics 4 (GA4) daily exports and a Server-Side Google Tag Manager (sGTM) pipeline streaming into BigQuery.
Architectural Design Across Layers:
1. Bronze (raw_bronze_analytics.sgtm_events): Append-only table partitioned by DATE(_ingested_at) and clustered by event_name, client_id. Uses Physical Storage Billing (180 GiB/day logical compresses 5:1 down to 36 GiB/day physical).
2. Silver (core_silver_analytics.fct_sessions): A Dataform incremental model running every 2 hours. It reads only the last 2 days of Bronze partitions, unnests the event_params array once, assigns deterministic session_id boundaries using a 30-minute inactivity window (LAG(event_timestamp)), and writes a flat, strongly typed session table (reducing 120 million raw event rows down to 8 million session rows per day—a 93.3% row reduction).
3. Gold (mart_gold_marketing.channel_attribution): Joins the 8 million daily Silver sessions with core_silver_commerce.stg_orders_conformed to calculate First-Click, Last-Click, and Linear attribution revenue per campaign.
Quantified Impact: Previously, 15 marketing Looker Studio dashboards queried the raw unnested event table directly (15 dashboards * 20 refreshes/day * 180 GiB/day over 30 days = 1,582 TiB scanned/month = 9,887.50 USD/month). Querying the Gold attribution table (15 MB per day * 30 days = 450 MB per dashboard scan) drops monthly BI scan volume to 0.13 TiB (covered 100% by BigQuery’s 1 TiB monthly free tier = 0.00 USD/month).

Case 2: Handling Late-Arriving Events and Out-of-Order CDC Updates with Sliding-Window Incremental MERGE

Business Context: Mobile app events and offline point-of-sale (POS) transactions frequently arrive in Bronze 24 to 72 hours after the physical purchase occurred (order_created_at is 3 days older than _ingested_at). Meanwhile, payment settlement webhooks update the order_status from pending to captured up to 5 days later.
Architectural Design:
- In Silver (stg_orders_conformed), partitioning is set on the business timestamp (DATE(order_created_at)), while the Bronze source filter reads WHERE DATE(_ingested_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY).
- However, if the MERGE target table is not constrained, BigQuery will scan the entire 5-year Silver table (for example, 4 TiB) just to match ON target.order_id = source.order_id.
- By setting updatePartitionFilter: "order_created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)" in Dataform, BigQuery injects AND target.order_created_at >= ... directly into the MERGE ON predicate.
Quantified Impact: Instead of scanning 4,096 GiB (4 TiB) on every hourly MERGE run (4 TiB * 24 runs/day * 30 days = 2,880 TiB/month = 18,000.00 USD/month), the 7-day updatePartitionFilter restricts the target scan to 18 GiB per run (18 GiB * 24 * 30 = 12.65 TiB/month = 79.06 USD/month), reducing MERGE compute cost by 99.56%.

Case 3: Optimizing Storage Costs Across Layers: Physical Billing on Bronze vs. Logical Billing on Silver

Business Context: A platform architect must decide whether to enable Physical Storage Billing across the entire project or split billing models by Medallion layer.
Explicit Mathematical Comparison:
- Bronze Dataset (raw_bronze_events): 20,000 GiB (20 TiB) of active logical JSON/string data. Compression ratio is 5:1 (4,000 GiB physical base bytes). Because Bronze is strictly append-only, time_travel_physical_bytes = 0 GiB and fail_safe_physical_bytes = 0 GiB.
  - Logical Billing Cost: 20,000 GiB * 0.02 USD = 400.00 USD/month.
  - Physical Billing Cost: 4,000 GiB * 0.04 USD = 160.00 USD/month (Saves 240.00 USD/month).
- Silver Dataset (core_silver_entities): 2,000 GiB (2 TiB) of active logical structured data. Compression ratio is 3.33:1 (600 GiB physical base bytes). However, Dataform runs an incremental MERGE every 30 minutes (48 times/day), rewriting 15% of the table’s physical micro-partitions (90 GiB rewritten per run = 4,320 GiB/day of mutated physical blocks). Over a 7-day Time-Travel window plus 7-day Fail-Safe window (14 days total retention of mutated blocks), accumulated time_travel_physical_bytes + fail_safe_physical_bytes equals 60,480 GiB.
  - Physical Billing Cost: (600 GiB base + 60,480 GiB time-travel/fail-safe) * 0.04 USD = 2,443.20 USD/month.
  - Logical Billing Cost: 2,000 GiB * 0.02 USD = 40.00 USD/month (Saves 2,403.20 USD/month).

Medallion Layer	Logical Size (GiB)	Base Physical Size (GiB)	14-Day Time-Travel + Fail-Safe Physical (GiB)	Cost Under Logical Billing (0.02 USD/GiB)	Cost Under Physical Billing (0.04 USD/GiB)	Optimal Dataset Configuration
Bronze (Append-Only)	20,000 GiB	4,000 GiB	0 GiB	400.00 USD	160.00 USD	`PHYSICAL`
Silver (Frequent MERGE)	2,000 GiB	600 GiB	60,480 GiB	40.00 USD	2,443.20 USD	`LOGICAL`
Combined Total	22,000 GiB	4,600 GiB	60,480 GiB	440.00 USD (All Logical)	2,603.20 USD (All Physical)	200.00 USD (Hybrid by Layer)

Case 4: Enforcing Zero-Defect Data Quality Gates with Dataform Blocking Assertions Before Gold Refresh

Business Context: An upstream currency conversion API experiences an outage and returns 0.00 for EUR-to-USD exchange rates. If the Gold financial mart refreshes with 0.00 exchange rates, automated executive emails and C-level dashboards will report a 40% drop in European revenue.
Architectural Design:
- In Silver, core_silver_finance.dim_fx_rates includes a Dataform assertion:rowConditions: ["fx_rate_to_usd > 0.0001", "fx_rate_to_usd < 10000.0"].
- In Gold, mart_gold_finance.fct_global_revenue sets dependOnDependencyAssertions: true.
- When Dataform executes the DAG:
  1. dim_fx_rates (Silver) updates.
  2. core_silver_finance_dim_fx_rates_assertions_rowConditions runs immediately after Silver.
  3. Because fx_rate_to_usd = 0.00 violates fx_rate_to_usd > 0.0001, the assertion view returns offending rows and Dataform raises a fatal error on the assertion step.
  4. Dataform immediately halts the downstream branch. mart_gold_finance.fct_global_revenue is not executed; it continues serving the previous valid snapshot to BI users while Cloud Monitoring alerts the engineering team.

Case 5: Schema Evolution Without Downtime: Extracting Semi-Structured JSON in Bronze to Strongly Typed Silver

Business Context: Product engineering deploys new checkout attributes (promo_campaign_code, installment_months) inside webhook payloads twice a week without coordinating with the data engineering team.
Architectural Design:
- Instead of defining 80 brittle columns on the Bronze ingestion table (which causes BigQuery LOAD or Streaming jobs to fail whenever an upstream microservice adds an unknown JSON property), the Bronze table uses a single json_payload JSON column alongside ingestion metadata.
- BigQuery’s native JSON data type automatically sub-columnarizes frequent sub-fields internally inside Capacitor storage, meaning querying STRING(json_payload.order_id) scans only the virtual sub-column for order_id rather than reading the entire JSON blob!
- When analytics requires the new installment_months field two weeks later, a data engineer adds LAX_INT64(json_payload.installment_months) AS installment_months to the Silver Dataform model and runs a single backfill over the needed partitions. Zero ingestion downtime occurs during the deployment.

Case 6: Sub-Second BI Serving in Gold Using BigQuery Materialized Views and BI Engine

Business Context: A customer-facing embedded analytics portal queries the Gold layer 15,000 times per day. Average query latency must stay below 400 milliseconds, and the pipeline cannot wait for hourly batch schedules.
Architectural Design:
- Instead of a batch table in Gold, define the Gold model in Dataform as a native BigQuery Materialized View over the Silver table (type: "view", materialized: true with enable_refresh = true and refresh_interval_minutes = 15).
- BigQuery Materialized Views support Smart Tuning and Delta Read: if a user queries the Materialized View (or even queries the underlying Silver base table directly with a matching GROUP BY), BigQuery automatically combines the pre-computed Materialized View blocks with only the newly appended rows in the Silver table written since the last refresh!
- Pairing the Gold dataset with a 4 GiB BigQuery BI Engine reservation caches the hot blocks in memory, delivering 180-millisecond P95 latency across all 15,000 daily queries.

Case 7: Multi-Brand / Multi-Region Consolidation Using Dataform JavaScript Loops (`forEach`)

Business Context: An international holding group operates 12 regional e-commerce brands (each landing raw data in its own Bronze dataset: raw_bronze_brand_uk, raw_bronze_brand_de, raw_bronze_brand_pl, etc., with 5 core tables per brand = 60 Bronze tables). Maintaining 60 copy-pasted .sqlx files creates severe maintenance debt.
Architectural Design:
- A single 45-line JavaScript file in Dataform (definitions/multi_brand_factory.js) iterates over an array of brand configurations (const brands = ["uk", "de", "pl", ...]), registers the 60 Bronze declare() sources, generates 60 standardized Silver incremental models, and builds a single UNION ALL Gold global consolidation table dynamically at compile time: