Data Engineering  /  Data Modeling

🧱 Data Modeling 20 guides · updated 2026

From ER diagrams to data mesh — relational, dimensional, NoSQL, and governance-driven modeling for building data platforms people can trust.

Cloud Data Warehouse Modeling: Snowflake, BigQuery, and Redshift Patterns That Scale

A generation of dimensional-modeling wisdom was written for machines that no longer exist — warehouses where storage was precious, compute was fixed, and a bad schema meant buying hardware. Snowflake, BigQuery, and Redshift changed the physics: storage is nearly free, compute is elastic and metered by the second (or the byte scanned), and columnar engines laugh at table widths that would have crashed a 2005 Oracle box. Some classic rules survived the transition untouched; others quietly inverted.

This post sorts out which is which: the universal principles, the platform-specific levers each engine gives you, the modern layering pattern dbt standardized, and the discipline the cloud actually added — modeling for cost.

What the Cloud Changed (and Didn’t)

Changed:

Didn’t change:

The Star vs. Wide-Table Question, Settled Pragmatically

The cloud-era debate — “just build One Big Table (OBT), joins are for boomers” vs. “Kimball forever” — has resolved, in most experienced teams, into a layered both: model dimensionally, serve wide. Keep facts and dimensions as the governed core (reusable, one place to fix a definition, SCD handling in one spot), then materialize denormalized wide tables from the star for specific consumption: BI tools that struggle with joins, ML feature sets, latency-critical dashboards. The star is the source of meaning; OBTs are cached query results with a schema. When a wide table and the star disagree, you know which one is wrong — that’s what makes the pattern safe.

Two cloud-native adjustments to classic star design worth adopting:

Platform Levers: The Same Ideas, Three Dialects

None of the big three use traditional indexes; all are columnar with min/max metadata pruning. Your physical modeling job is arranging data so pruning works — one idea, three vocabularies:

Snowflake. Micro-partitions are automatic; your lever is the clustering key on large tables (CLUSTER BY (order_date, customer_id)) so range and equality filters skip micro-partitions. Costs credits to maintain — reserve it for multi-terabyte tables with consistent filter patterns; most tables under a TB need nothing. Zero-copy clones make dev/test environments a modeling workflow feature (test a remodel against full production data for pennies).

BigQuery. The lever pair is explicit: partition (almost always by date — also caps how much a query can scan, which under per-byte pricing is literally a spending limit) plus clustering (up to four columns, ordered most-filtered-first, handling the high-cardinality keys partitioning can’t). Require partition filters on big tables (require_partition_filter=true) and an unbounded SELECT * becomes an error instead of an invoice.

Redshift. The most hands-on of the three: DISTKEY decides which node holds each row (distribute big facts on the join key so fact-to-fact joins stay local; small dimensions DISTSTYLE ALL — copied to every node), SORTKEY plays the clustering role for range filters. Getting distribution wrong shows up as network shuffle on every join — the closest the cloud trio comes to old-school physical modeling consequences.

-- The same fact table, three dialects of the same thought
-- Snowflake
CREATE TABLE fct_orders (...) CLUSTER BY (order_date);
-- BigQuery
CREATE TABLE fct_orders (...)
PARTITION BY DATE(order_date) CLUSTER BY customer_id, store_id;
-- Redshift
CREATE TABLE fct_orders (...) DISTKEY(customer_id) SORTKEY(order_date);

The dbt Layering Pattern: How Cloud Warehouses Get Modeled in Practice

The de facto standard structure — worth adopting even if your tool isn’t dbt — is a pipeline of modeling intents, cousin to the lake’s medallion layers:

Raw / landed

(as-received,

never edited)

Staging

1:1 with sources —

rename, cast, clean

(views)

Intermediate

joins & business logic

(ephemeral/views)

Marts

facts & dimensions,

tested & documented

(tables/incremental)

Serving

wide tables, metrics,

aggregates

The load-bearing conventions: staging models touch one source each and do no joins (so source changes have one place to land); marts carry the tests, documentation, and grain declarations (this is where governance from earlier posts attaches); big facts build incrementally — processing only new/changed rows per run, which on metered compute is the single biggest recurring-cost saver available:

-- dbt incremental: pay to process a day, not five years, per run
{{ config(materialized='incremental', unique_key='order_id') }}
SELECT ... FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
  WHERE updated_at > (SELECT max(updated_at) FROM {{ this }})
{% endif %}

Modeling for the Bill: The Genuinely New Discipline

Habits that separate cheap warehouses from surprising invoices, all of them modeling decisions:

A Migration-Ready Checklist

  1. Grain declared and documented per mart table; conformed dimensions shared across stars.
  2. Date partitioning/clustering on every table past ~100 GB; platform-appropriate keys on the top ten query paths.
  3. Layered project: staging 1:1 with sources → marts tested and owned → serving tables derived, never hand-maintained.
  4. Incremental materialization on large facts; snapshot or Type 2 policy chosen per dimension, in writing.
  5. Cost review monthly: top ten most expensive queries traced back to the modeling decision that would fix them.

The cloud warehouse didn’t retire the dimensional modeler — it promoted them. Freed from rationing bytes, the job is now equal parts semantics (what does a row mean?), economics (what does a query cost?), and product (can an analyst find and trust this?). The platforms will keep leapfrogging each other on benchmarks; the models that survive the leapfrogging are the ones where somebody answered those three questions on purpose.