BigQuery Stored Procedures → ELT & SCD

Build ELT in pure SQL on BigQuery – SCD Type 1 & 2 with MERGE, partitions, clusters, and scheduled runs.

Difficulty: Intermediate
Tech stack: BigQuery (Stored Procedures, MERGE), BQ Scheduler/Composer
Estimated time: 1-2 hrs

Overview

You’ll load/raw stage data in BigQuery, then use SQL Stored Procedures to standardize schemas and implement SCD patterns. `MERGE` handles upserts: SCD1 overwrites attributes; SCD2 closes current rows and opens new versions with effective and end dates. Facts are partitioned/clustered for cost/perf. A scheduler (BQ Scheduler/Composer) runs the procs on a cadence for fully warehouse-native ELT.

Outcome

Warehouse-native ELT using Stored Procedures (no external engine).
History tracking via SCD1 (overwrite) and SCD2 (effective_from/to).
Faster, cheaper queries with partitioned/clustered facts.

What you’ll build

Raw → staged → modeled tables in BigQuery.
Stored Procedures that orchestrate SCD1/SCD2 with `MERGE`.
Helper views/UDFs for change detection and audit columns.
Partitioning & clustering strategy for facts/dimensions.
(Optional) Triggers via BQ Scheduler or Composer.