? Architecture Layers

1️⃣ Raw Layer (Bronze)

• Exact source copies

• Partitioned

• No transformations

• JSON preserved if needed

Dataset:

2️⃣ Staging Layer (Silver)

• Clean types

• Standardized timestamps

• Deduplicated

• Basic transformations

Dataset:

3️⃣ Core Warehouse (Gold – Dimensional)

Star schemas:

Partitioned + clustered.

4️⃣ Aggregation Layer

Precomputed aggregates:

Used by dashboards.

? Governance Practices

• Access control at dataset level

• Separate service accounts for pipelines

• Row-level security if needed

• dbt tests for:

  • Null keys

  • Duplicate grain

  • SCD validity

⚡ Performance Optimization in Production

• Always filter on partition column

• Avoid SELECT *

• Pre-aggregate dashboard tables

• Use materialized views selectively

• Monitor query plan for shuffle stages

? What This Looks Like at Scale

Example:

• 5B row fact table

• Partitioned by date

• Clustered by customer_key

• Avg dashboard query scans < 2GB

• Monthly storage cost low due to BigQuery pricing model

Final Recommendation

For most BigQuery implementations:

Raw → Staging → Kimball Star → Aggregates

Keep it simple.
Use SCD2 for important dimensions.
Partition aggressively.
Design for BI users first.