GCP Data Architect Series - Part III
Walk through a concrete example (e.g., e-commerce in SQL)
AdminFollow
5 min•Feb 28, 2026
Views - 12
Business Process: Orders
Grain:
One row per order line item.
? Fact Table: fact_order_lines
CREATE TABLE analytics.fact_order_lines
PARTITION BY order_date
CLUSTER BY customer_key, product_key
AS
SELECT
o.order_line_id,
DATE(o.order_timestamp) AS order_date,
dc.customer_key,
dp.product_key,
o.quantity,
o.unit_price,
o.quantity * o.unit_price AS gross_revenue
FROM staging.order_lines o
JOIN analytics.dim_customer dc
ON o.customer_id = dc.customer_id
AND dc.is_current = TRUE
JOIN analytics.dim_product dp
ON o.product_id = dp.product_id;
PARTITION BY order_date
CLUSTER BY customer_key, product_key
AS
SELECT
o.order_line_id,
DATE(o.order_timestamp) AS order_date,
dc.customer_key,
dp.product_key,
o.quantity,
o.unit_price,
o.quantity * o.unit_price AS gross_revenue
FROM staging.order_lines o
JOIN analytics.dim_customer dc
ON o.customer_id = dc.customer_id
AND dc.is_current = TRUE
JOIN analytics.dim_product dp
ON o.product_id = dp.product_id;
Why this works well in BigQuery:
Partitioning reduces scan cost
Clustering improves join/filter performance
Narrow fact table → efficient scans
? Dimension: dim_customer
Grain:
One row per customer version (SCD2)
Columns:
customer_key (surrogate)
customer_id (business key)
name
email
city
effective_start_date
effective_end_date
is_current
? Dimension: dim_date
Typically pre-built with:
CREATE TABLE analytics.dim_date AS
SELECT
d AS date_key,
EXTRACT(YEAR FROM d) AS year,
EXTRACT(MONTH FROM d) AS month,
EXTRACT(DAY FROM d) AS day,
FORMAT_DATE('%A', d) AS weekday_name
FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01','2030-12-31')) d;
SELECT
d AS date_key,
EXTRACT(YEAR FROM d) AS year,
EXTRACT(MONTH FROM d) AS month,
EXTRACT(DAY FROM d) AS day,
FORMAT_DATE('%A', d) AS weekday_name
FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01','2030-12-31')) d;
Comments (0)
No comments yet.