Implementing Your Logical Data Model in BigQuery: A Practical Guide

A clean Logical Data Model (LDM) captures your enterprise’s business entities, attributes, grain, and relationships independent of any database engine. Yet one of the costliest mistakes teams make when moving to Google BigQuery is translating a logical 3NF or Kimball ER diagram 1-for-1 into flat relational tables with heavy runtime joins—ignoring how BigQuery’s Capacitor columnar storage and Dremel shuffle engine actually execute queries.

Here is a practical blueprint for turning your logical model into a fast, cost-efficient physical BigQuery schema without sacrificing governance or BI tool compatibility.

1. Choose Between Star Schema vs. Nested & Repeated Fields (ARRAY<STRUCT>)

In traditional row-oriented databases, normalizing parent–child entities (for example, orders and order_items) into separate tables avoided repeating header columns across every line item. Because BigQuery stores data column-by-column using Dremel repetition and definition levels, you can co-locate 1:N child records inside a single physical table using ARRAY<STRUCT<...>>—storing header fields once per order with zero distributed shuffle join at query time!

2. Physical DDL: Partitioning, Clustering & Nested Structs

Every large fact table in BigQuery should translate access patterns from your logical model into 1 Partition column (coarse block pruning, typically by event or business date) plus up to 4 Clustering columns (fine-grained sort order for high-cardinality filter and join keys):

CREATE TABLE IF NOT EXISTS `commerce_dw.dim_customer` (
  customer_sk INT64 NOT NULL OPTIONS(description="Deterministic surrogate key"),
  customer_id STRING NOT NULL,
  loyalty_tier STRING,
  country_code STRING,
  effective_from TIMESTAMP NOT NULL,
  is_current BOOL NOT NULL,
  PRIMARY KEY (customer_sk) NOT ENFORCED
)
CLUSTER BY country_code, loyalty_tier;

CREATE TABLE IF NOT EXISTS `commerce_dw.fct_orders` (
  order_sk INT64 NOT NULL,
  order_id STRING NOT NULL,
  order_date DATE NOT NULL,
  customer_sk INT64 NOT NULL,
  currency_code STRING,
  order_total NUMERIC(18, 2),
  shipping_address STRUCT<
    city STRING,
    state STRING,
    postal_code STRING,
    country_code STRING
  >,
  line_items ARRAY<STRUCT<
    line_number INT64,
    product_id STRING,
    quantity INT64,
    unit_price NUMERIC(18, 2),
    extended_amount NUMERIC(18, 2)
  >>,
  PRIMARY KEY (order_sk) NOT ENFORCED,
  FOREIGN KEY (customer_sk) REFERENCES `commerce_dw.dim_customer`(customer_sk) NOT ENFORCED
)
PARTITION BY order_date
CLUSTER BY customer_sk
OPTIONS (
  require_partition_filter = TRUE,
  description = "Order grain fact table with nested line_items array"
);

3. Declare NOT ENFORCED Primary & Foreign Keys for Join Elimination

Notice the PRIMARY KEY (...) NOT ENFORCED and FOREIGN KEY (...) NOT ENFORCED clauses above. BigQuery does not incur OLTP lock overhead enforcing uniqueness on write (your dbt/Dataform data quality assertions must still guarantee key integrity!), but BigQuery’s cost-based query optimizer actively uses these declarations for:

• Inner/Left Join Elimination: When a BI tool like Looker or Tableau generates a wide SQL view joining fct_orders to 10 dimensions, if a user’s chart selects metrics only from fct_orders, BigQuery prunes the unnecessary dimension scans completely!
• Join Reordering & Broadcast Sizing: Helps the optimizer choose optimal hash-join build sides without over-allocating shuffle slots.
• Self-Documenting ER Metadata: BI semantic layers and AI SQL agents can introspect INFORMATION_SCHEMA.TABLE_CONSTRAINTS directly.

4. Querying Nested Line Items Without Fan-Out Traps

When querying nested ARRAY<STRUCT> columns, aggregate inside a correlated scalar subquery (or UNNEST carefully) so you never accidentally multiply header-level measures like order_total by line count:

SELECT
  o.order_date,
  COUNT(o.order_sk) AS total_orders,
  SUM(o.order_total) AS gross_order_revenue,
  SUM((SELECT SUM(li.quantity) FROM UNNEST(o.line_items) AS li)) AS total_units_sold
FROM `commerce_dw.fct_orders` AS o
WHERE o.order_date BETWEEN '2025-04-01' AND '2025-04-15'
GROUP BY o.order_date
ORDER BY o.order_date;

Pair this physical modeling foundation with deterministic surrogate keys from A Detailed Guide to Key Generation in BigQuery to keep both storage and compute bills lean.

Originally published on Medium / Dev Genius — Implementing Your Logical Data Model in BigQuery: A Practical Guide.