One Big Table (OBT) vs Star Schema: Denormalization Trade-Offs in the Modern Warehouse

A one big table — a single wide, fully denormalized table where every dimension attribute is pre-joined and inlined next to the fact measures — is the data model that quietly took over analytics engineering the moment cloud warehouses made storage almost free and joins almost cheap, and it is the design decision that senior interviewers now use to separate people who model from dogma versus people who model from cost. For thirty years the answer to "how do I model a warehouse?" was reflexive: build a star schema with a central fact table and normalized dimensions, because a row-store engine paid dearly for every redundant byte and every join was a hash-build against a disk-bound table. On a columnar warehouse that reflexive answer is no longer automatically correct — and the reflexive opposite ("just denormalize everything into a wide table") is no longer automatically wrong.

This guide is the walkthrough you wished existed the first time an architect asked "would you model this as a star or as one big table, and defend the trade-off with numbers." It works through both models on their merits — the star schema with its facts, grain, surrogate keys, and conformed dimensions; the wide-table pattern with its pre-join materialization and its update anomalies; the denormalization economics that columnar compression and scan-bytes billing actually produce; and the dbt-driven hybrid where you model a conformed core once and publish wide table marts per use case. The four axes that matter — query performance, storage, maintainability, and flexibility — are the frame for every decision, and dimensional modeling fluency is what lets you move along all four axes instead of picking a camp. Each section pairs a teaching block with a Solution-Tail interview answer: real SQL, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the dimensional-modeling practice library →, rehearse the analytical query patterns on the SQL practice library →, and sharpen the join-elimination intuition on the joins practice library →.

---

On this page

• Why the OBT-vs-star debate exists in the columnar era
• The star schema: facts, dimensions & conformed dimensions
• One Big Table: denormalize everything into one wide table
• Performance, storage & cost: measuring the trade-off
• Hybrid patterns & the modern-stack verdict
• Cheat sheet — OBT vs star recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why the OBT-vs-star debate exists in the columnar era

Two models, one warehouse — the choice that used to be automatic and now needs a defense

The one-sentence invariant: the choice between a normalized star schema and a fully denormalized one big table is a trade-off across four axes — query performance, storage cost, maintainability, and flexibility — and the reason the debate reopened is that columnar cloud warehouses (Snowflake, BigQuery, Databricks) inverted the two assumptions the star schema was built to satisfy: storage is no longer expensive, and joins are no longer the dominant cost of a query. In a 1998 row-store, a redundant customer name repeated across ten million order rows cost real money in disk and real time in every scan, so you normalized it into a dimension and joined at query time. In a 2026 columnar warehouse, that same repeated string compresses to almost nothing (dictionary + run-length encoding collapses low-cardinality repeats), and the "join" you were avoiding is often cheaper than the extra I/O of reading a second table — so the historical justification for the star schema partially evaporated, and the wide table became a legitimate first-class option instead of a rookie mistake.

The four axes every OBT-vs-star discussion converges on.

• Query performance. A one big table answers most analytical queries with a single scan and zero joins — the BI tool points at one object and reads the columns it needs. A star schema scans the fact table and joins one row per needed dimension. On columnar engines both are fast, but the OBT removes join planning, shuffle, and the small-dimension broadcast entirely — predictable single-scan latency is its headline advantage.
• Storage. The star schema stores each dimension attribute once (in the dimension table) and references it by a surrogate key. The OBT stores every dimension attribute inlined on every fact row, so a category name that appears once in dim_product now appears on every order line. Uncompressed this is a huge duplication; compressed on a columnar engine it is far smaller than intuition suggests — but never zero.
• Maintainability. A star schema localizes change: fix a mislabeled product category in one dimension row and every fact query sees the correction immediately. An OBT freezes dimension attributes into the fact grain at build time, so the same correction requires rebuilding (or incrementally patching) the wide table. Star wins maintainability; OBT trades it for query simplicity.
• Flexibility. A star schema can answer questions its designers did not anticipate — add a fact, join an existing conformed dimension, and a new analysis appears with no rebuild. An OBT is optimized for the queries it was shaped around; a genuinely new question can require re-materializing the table with different joins. Star wins flexibility; OBT wins for a known, stable set of dashboard queries.

What columnar storage actually changed — the math, not the dogma.

• Storage got cheap. Object storage (S3/GCS/ADLS) under Snowflake/BigQuery/Databricks is billed at cents per GB-month. The star schema's core value proposition — "don't duplicate data, it's expensive" — lost most of its force. Duplication still costs something, but it is rarely the deciding factor.
• Compression got aggressive. Columnar engines store each column contiguously and encode it (dictionary, run-length, delta, bit-packing). A denormalized category column with 50 distinct values across a billion rows is a 50-entry dictionary plus a billion tiny dictionary references — often compressing 20–100×. The "OBT duplicates strings everywhere" objection is real but heavily blunted by encoding.
• Scans got columnar. A query that reads 3 of 40 columns reads only those 3 columns' bytes, not whole rows. This makes a 40-column OBT cheap to query narrowly — you pay for the columns you touch, not the width of the table. It is the single most important fact that makes wide tables viable.
• Joins stopped being the villain. Vectorized hash joins against a broadcast-small dimension are fast, but they still cost planning, a build phase, and a probe. Eliminating them (OBT) removes a real, if smaller-than-1998, cost — and removes the risk of a bad join plan on a large-dimension join.

What interviewers actually probe.

• Do you name all four axes — performance, storage, maintainability, flexibility — rather than arguing "OBT is faster" as if speed were the only dimension? — senior signal.
• Do you explain why columnar changed the calculus (cheap storage + compression + columnar scans) instead of reciting Kimball from memory? — required answer.
• Do you say "it depends on the number of facts and the volatility of the dimensions" rather than picking a camp? — senior signal.
• Do you know that an OBT is still built from a star — you model conformed dimensions, then denormalize into the wide table — rather than treating them as mutually exclusive from the source? — senior signal.
• Do you reach for "measure the scan bytes and the rebuild cost" rather than asserting a winner? — required answer.

Worked example — the OBT-vs-star four-axis comparison table

Detailed explanation. The single most useful artifact for an OBT-vs-star interview is a memorised 4×2 comparison table. Every senior modeling discussion converges on this table within the first ten minutes; having it in your head is what separates a fluent answer from a stumbling one. Build the table for a hypothetical retail sales analysis that needs to reach a Snowflake dashboard layer.

• Query-performance axis. How many objects does a typical dashboard query touch, and how many joins does the planner run?
• Storage axis. How many times is each dimension attribute physically stored, before and after columnar compression?
• Maintainability axis. When a dimension attribute changes, what has to happen for downstream queries to see the new value?
• Flexibility axis. Can a brand-new, unplanned question be answered without re-materializing anything?

Question. Build the four-axis comparison for a retail sales model and state which model each axis favors.

Input.

Axis	Star schema	One big table (OBT)
Query performance	scan fact + N dimension joins	single scan, zero joins
Storage	each attribute stored once	attributes inlined per row (compresses well)
Maintainability	fix once in the dimension	rebuild / patch the wide table
Flexibility	join any conformed dim on demand	optimized for the queries it was shaped for

Code.

-- Star: dashboard query joins the fact to two dimensions
SELECT d.category,
       dt.year_month,
       SUM(f.amount_cents) AS revenue_cents
FROM   fact_sales        f
JOIN   dim_product       d  ON d.product_key = f.product_key
JOIN   dim_date          dt ON dt.date_key    = f.date_key
GROUP  BY d.category, dt.year_month;

-- OBT: the same dashboard query, no joins at all
SELECT category,
       year_month,
       SUM(amount_cents) AS revenue_cents
FROM   obt_sales
GROUP  BY category, year_month;

Step-by-step explanation.

1. The star query touches three objects (fact_sales, dim_product, dim_date) and asks the optimizer to build two hash joins. The dimensions are small, so they broadcast — fast, but not free: planning, build, probe, and the risk of a bad plan if a dimension is unexpectedly large.
2. The OBT query touches one object (obt_sales) and runs zero joins. category and year_month are already physically present on every fact row because the wide table was built by pre-joining the star. The planner has nothing to shuffle.
3. On the storage axis, the star stores category once per product in dim_product — a few thousand rows. The OBT stores category on every one of the billions of obt_sales rows. Uncompressed that is a catastrophe; dictionary-encoded on a columnar engine it is a small integer per row plus a tiny dictionary — the duplication is real but far cheaper than the row-store intuition suggests.
4. On the maintainability axis, if marketing renames a category, the star fixes one dim_product row and every historical and future query reflects it. The OBT has the old category name frozen onto billions of rows and must be rebuilt (or the affected rows patched) to reflect the rename.
5. The flexibility axis is where the star quietly wins: an unplanned question — "revenue by customer loyalty tier" — is a new join to dim_customer in the star, but in the OBT it only works if loyalty_tier was inlined at build time. If it was not, you rebuild the OBT with an extra join. The model you pick encodes the questions you expect.

Output.

Axis	Winner	Why
Query performance	OBT	single scan, no join planning or shuffle
Storage	Star	attributes stored once (OBT close after compression)
Maintainability	Star	change localized to one dimension row
Flexibility	Star	new conformed-dim joins need no rebuild

Rule of thumb. Never argue OBT-vs-star as if query speed were the only axis. Write the four-axis table on the whiteboard first; the OBT wins performance and query simplicity, the star wins storage, maintainability, and flexibility — and the right answer is whichever axis your workload actually optimizes for.

Worked example — the decision rubric a senior architect runs in their head

Detailed explanation. Given a new analytics request, the senior analytics engineer runs a short decision rubric rather than reaching for a favorite. Codifying the rubric makes the interview answer reproducible: any interviewer can hand you a scenario and you can walk it out loud. Walk through the rubric with three canonical scenarios — a single-grain marketing dashboard, a multi-fact finance warehouse, and a self-service exploration layer.

• Q1 — how many fact processes? One business process (one grain) → OBT is viable. Many processes that share dimensions → a conformed star pays off.
• Q2 — how volatile are the dimensions? Stable dimensions (rarely re-labeled) → OBT's rebuild cost is low. Frequently corrected dimensions → the star's localized fix wins.
• Q3 — are the queries known or open-ended? Fixed dashboard set → OBT shaped to them. Ad-hoc / self-service exploration → the star's flexibility wins.
• Q4 — who queries it? BI tools and analysts who dislike joins → OBT ergonomics. Data engineers writing SQL → either.

Question. Walk the rubric for the three scenarios and record the model each ends up with.

Input.

Scenario	Q1 (facts?)	Q2 (dim volatility?)	Q3 (queries?)	Q4 (who?)
Marketing campaign dashboard	one grain	low	fixed	analysts
Finance multi-fact warehouse	many facts	medium	mixed	data engineers
Self-service exploration layer	many	medium	open-ended	mixed

Code.

# Decision-rubric helper (illustrative)
def pick_model(num_fact_processes: int,
               dim_volatility: str,          # "low" | "medium" | "high"
               queries: str,                 # "fixed" | "mixed" | "open"
               ) -> str:
    """Return the recommended warehouse model for an analytics request."""
    if num_fact_processes == 1 and queries == "fixed" and dim_volatility in ("low", "medium"):
        return "OBT (one big table)"
    if num_fact_processes > 1 and queries in ("mixed", "open"):
        return "conformed star schema"
    if queries == "open":
        return "star schema (flexibility)"
    # default: model a star core, publish OBT marts
    return "hybrid: star core + OBT marts"


print(pick_model(1, "low",    "fixed"))   # → OBT (one big table)
print(pick_model(3, "medium", "mixed"))   # → conformed star schema
print(pick_model(4, "medium", "open"))    # → conformed star schema

Step-by-step explanation.

1. Scenario 1 — a marketing campaign dashboard at one grain (one row per campaign-day), stable dimensions, a fixed set of dashboard queries, consumed by analysts who dislike writing joins. The rubric short-circuits to OBT: one grain + fixed queries + low volatility is the textbook wide-table case.
2. Scenario 2 — a finance warehouse with many fact processes (billings, payments, refunds, ledger entries) that all share dim_account, dim_date, and dim_entity. Many facts sharing dimensions is exactly what conformed dimensions were invented for; a star schema lets each fact reuse the same dimensions and lets cross-process questions ("payments vs billings by account") join cleanly.
3. Scenario 3 — a self-service exploration layer where analysts ask open-ended questions no one pre-planned. Flexibility dominates; the star schema answers unanticipated questions by joining existing dimensions, whereas an OBT would need rebuilding whenever a new attribute is needed.
4. The rubric's default when the axes conflict is the hybrid: model a conformed star core (dimensions + facts) once as the governed source of truth, then publish one-big-table marts on top for the specific dashboards that want single-scan simplicity. This is the modern-stack answer and the subject of section 5.
5. Notice the rubric never says "OBT is better" or "star is better." It maps workload characteristics to a model. That mapping — not a preference — is what interviewers score.

Output.

Scenario	Model	Deciding axis
Marketing dashboard	OBT	fixed queries + one grain
Finance multi-fact	conformed star	many facts share dimensions
Self-service layer	star (or hybrid)	flexibility / open-ended queries

Rule of thumb. The four-question rubric is whiteboard-friendly. Practice walking it end-to-end so an interviewer can hand you any scenario and get a model name — with a defense — in under sixty seconds. The default when axes conflict is always the hybrid, not a coin flip.

Worked example — how columnar storage changes the storage math

Detailed explanation. The strongest objection to a one big table — "you are duplicating every dimension attribute onto billions of rows" — is quantitatively true and qualitatively overstated, because columnar dictionary encoding turns a repeated low-cardinality string into a tiny integer reference. Walk through the actual byte math for a single denormalized category column so you can defend the OBT storage cost with a number instead of a shrug.

• The scenario. One billion obt_sales rows. A category attribute with 50 distinct values, average string length 16 bytes.
• Naive (uncompressed) cost. 1e9 rows × 16 bytes = 16 GB just for the category column, repeated on every row.
• Dictionary-encoded cost. A 50-entry dictionary (~800 bytes) plus one small reference per row. 50 values fit in 6 bits; run-length encoding of sorted/clustered data shrinks it further.
• The comparison. In the star, category lives once in dim_product (a few thousand rows) — kilobytes. In the OBT it is dictionary-encoded across a billion rows — tens of megabytes, not the naive 16 GB.

Question. Estimate the compressed storage cost of the inlined category column in the OBT versus the star, and state the real overhead.

Input.

Quantity	Value
Rows in obt_sales	1,000,000,000
Distinct category values	50
Avg string length	16 bytes
Uncompressed inline cost	16 GB
Dictionary size	~50 entries (~800 B)

Code.

# Byte math: inlined low-cardinality column, dictionary-encoded
ROWS            = 1_000_000_000
DISTINCT        = 50
AVG_STR_BYTES   = 16

# Naive: store the string on every row
naive_gb = ROWS * AVG_STR_BYTES / (1024**3)

# Dictionary encoding: bits-per-row = ceil(log2(distinct))
import math
bits_per_row  = math.ceil(math.log2(DISTINCT))          # 6 bits for 50 values
dict_bytes    = DISTINCT * AVG_STR_BYTES                 # the dictionary itself
refs_bytes    = ROWS * bits_per_row / 8                  # one small ref per row
# Run-length encoding of clustered data typically cuts refs 2-5x further
rle_factor    = 4
encoded_gb    = (dict_bytes + refs_bytes / rle_factor) / (1024**3)

print(f"naive inline:      {naive_gb:6.2f} GB")
print(f"dict+RLE encoded:  {encoded_gb:6.3f} GB")
print(f"compression ratio: {naive_gb / encoded_gb:6.1f}x")
# → naive inline:       14.90 GB
# → dict+RLE encoded:    0.17 GB
# → compression ratio:   85.3x

Step-by-step explanation.

1. The naive intuition — "the OBT stores a 16-byte string a billion times" — yields ~15 GB for a single column, and this is the number that makes people reject wide tables out of hand. It is the uncompressed cost, which no columnar engine actually pays.
2. Dictionary encoding replaces the string with a reference into a per-column dictionary. With only 50 distinct values, each reference needs ceil(log2(50)) = 6 bits, and the dictionary itself is a rounding error (~800 bytes). The billion 6-bit references are ~715 MB before further encoding.
3. Run-length and bit-packing encodings then exploit the fact that clustered data repeats — if the OBT is clustered by category or date, long runs of identical references collapse to (value, count) pairs. A conservative 4× further reduction brings the column to ~170 MB.
4. The honest comparison: the star stores category in ~a few KB (once per product). The OBT stores it in ~170 MB. So the OBT overhead for this column is real — roughly 170 MB versus KB — but it is 170 MB, not 15 GB, and it buys join elimination on every query. Multiply across a dozen inlined attributes and the OBT is perhaps a few GB heavier — pennies of storage.
5. The lesson interviewers want: quantify the duplication, then note that columnar encoding turns "catastrophic 15 GB" into "a few hundred MB." The OBT storage objection is real but small; the deciding factors are usually maintainability and flexibility, not raw storage bytes.

Output.

Encoding	category column size	Overhead vs star
Naive uncompressed inline	~15 GB	enormous (misleading)
Dictionary-encoded	~0.7 GB	large but manageable
Dictionary + RLE (clustered)	~0.17 GB	small — pennies/month
Star (dim_product)	~few KB	baseline

Rule of thumb. When someone rejects an OBT because "it duplicates every attribute," ask for the compressed number, not the uncompressed one. Low-cardinality attributes dictionary-encode 20–100×; the real OBT storage premium is usually a few GB, not the terabytes the naive math implies. Storage is the axis columnar warehouses made least important.

Senior interview question on OBT vs star selection

A senior interviewer often opens with: "You inherit a Snowflake warehouse with a normalized star schema feeding twelve BI dashboards. The BI team complains the joins are slow and the tool keeps generating bad SQL. The head of data wants to know whether to flatten everything into one big table. Walk me through how you'd decide, what you'd measure before committing, and the model you'd actually ship."

Solution Using a measured decision — profile the workload, then model a star core with OBT marts

-- Step 1 — profile the actual dashboard workload before deciding.
-- Snowflake: which tables/joins dominate, and what do queries scan?
SELECT
    query_type,
    COUNT(*)                                   AS n_queries,
    AVG(bytes_scanned)   / POWER(1024, 3)      AS avg_gb_scanned,
    AVG(total_elapsed_time) / 1000.0           AS avg_seconds,
    SUM(partitions_scanned)                    AS total_partitions_scanned,
    SUM(partitions_total)                      AS total_partitions_available
FROM   snowflake.account_usage.query_history
WHERE  start_time > DATEADD('day', -30, CURRENT_TIMESTAMP())
  AND  query_text ILIKE '%fact_sales%'
GROUP  BY query_type
ORDER  BY n_queries DESC;

-- Step 2 — estimate the OBT build cost by materializing one mart from the star.
-- This is the "big JOIN" that pre-joins the star into a wide table.
CREATE OR REPLACE TABLE obt_sales
CLUSTER BY (date_key, category) AS
SELECT
    f.sale_id,
    f.date_key,
    dt.calendar_date,
    dt.year_month,
    d.product_key,
    d.product_name,
    d.category,
    c.customer_key,
    c.customer_segment,
    c.loyalty_tier,
    s.store_key,
    s.store_region,
    f.quantity,
    f.amount_cents
FROM   fact_sales   f
JOIN   dim_date     dt ON dt.date_key    = f.date_key
JOIN   dim_product  d  ON d.product_key  = f.product_key
JOIN   dim_customer c  ON c.customer_key = f.customer_key
JOIN   dim_store    s  ON s.store_key    = f.store_key;

-- Step 3 — compare the two plans on the SAME dashboard query.
-- Star (joins) vs OBT (single scan); read bytes_scanned from QUERY_HISTORY.
-- Star:
SELECT d.category, dt.year_month, SUM(f.amount_cents) AS rev
FROM   fact_sales f
JOIN   dim_product d ON d.product_key = f.product_key
JOIN   dim_date   dt ON dt.date_key   = f.date_key
GROUP  BY 1, 2;

-- OBT:
SELECT category, year_month, SUM(amount_cents) AS rev
FROM   obt_sales
GROUP  BY 1, 2;

Step-by-step trace.

Step	Before (pure star)	After (star core + OBT marts)
Dashboard query shape	fact + 4 joins	single scan of obt_sales
BI-tool SQL quality	tool generates fragile joins	tool reads flat columns
Avg dashboard latency	~4.2 s (join build + probe)	~1.3 s (single clustered scan)
Storage	dims stored once	+a few GB inlined (compressed)
Dimension correction	fix one dim row	rebuild/patch OBT mart
Source of truth	the star	still the star; OBT is derived
Ad-hoc new question	join a conformed dim	rebuild OBT or fall back to star

After the change, the twelve dashboards point at obt_sales and answer in a single clustered scan with no BI-generated joins, while the conformed star remains the governed source of truth for ad-hoc analysis and for building future marts. Dimension corrections happen in the star and propagate to the OBT on the next scheduled rebuild.

Output:

Metric	Before (star)	After (star core + OBT)
Dashboard p50 latency	~4.2 s	~1.3 s
Joins per dashboard query	4	0
Extra storage	baseline	+~4 GB (compressed)
Dimension fix propagation	instant	next rebuild
Ad-hoc flexibility	full	full (via star)

Why this works — concept by concept:

• Profile before you flatten — the query_history scan tells you whether joins are actually the bottleneck or whether the problem is missing clustering. Measuring bytes_scanned and partitions_scanned per query turns "the joins feel slow" into a number you can defend a decision with.
• Star core as source of truth — you never throw the star away. The conformed dimensions and facts remain the governed model; the OBT is a derived materialization, so corrections stay localized and flexibility is preserved.
• OBT mart for the fixed dashboards — the twelve known dashboards are exactly the "fixed queries, one grain" case where the wide table wins: single scan, no BI-generated join fragility, predictable latency.
• Cluster by (date_key, category) — clustering the OBT on the columns dashboards filter and group by lets the engine prune partitions and collapse dictionary runs, which is what makes the single scan both fast and cheap.
• Cost — one extra table (~a few GB compressed) and a scheduled rebuild job, in exchange for zero-join dashboards. The rebuild is O(fact rows) per refresh; the query saving is O(joins eliminated) on every one of thousands of daily dashboard queries. You trade a bounded build cost for an unbounded query saving.

Dimensional Modeling
Topic — dimensional-modeling
Dimensional modeling — star vs OBT design problems

Practice →

Design Topic — design Design problems on warehouse data models

Practice →

---

2. The star schema: facts, dimensions & conformed dimensions

star schema is the model where measures live in fact tables, context lives in dimension tables, and conformed dimensions let many facts share one source of truth

The mental model in one line: a star schema splits the world into fact tables (the numeric measurements of a business process, at a declared grain, referencing dimensions by surrogate key) and dimension tables (the descriptive context — who, what, where, when), and its superpower is the conformed dimension: a single dim_customer or dim_date that every fact table joins to, so that "revenue by customer segment" and "returns by customer segment" mean the same thing across the entire warehouse. Every serious warehouse has been modeled this way since Kimball formalized it in the 1990s, and it remains the correct backbone whenever multiple business processes share the same descriptive entities.

The anatomy of a star schema.

• Fact table. Holds the measurements (additive numbers like amount_cents, quantity) plus foreign keys to dimensions. It is the big table — one row per event at the declared grain — and it is narrow: mostly keys and numbers.
• Dimension tables. Hold the descriptive attributes — product_name, category, customer_segment, store_region. They are small (thousands to millions of rows) and wide (many text columns). They give the fact its context.
• Grain. The single most important design decision: what does one fact row represent? "One row per order line" is a different grain from "one row per order" or "one row per daily product-store total." Grain governs everything downstream.
• Surrogate keys. Dimensions use a warehouse-generated integer key (product_key) rather than the source system's natural key (sku), so the warehouse controls identity, handles slowly-changing history, and joins on cheap integers.

Conformed dimensions — the star's decisive advantage.

• What they are. A dimension used, identically, by more than one fact table. dim_date conforms across fact_sales, fact_returns, and fact_shipments; dim_customer conforms across sales and support tickets.
• Why they matter. They make cross-process analysis coherent. Because both fact_sales and fact_returns join the same dim_product, "return rate by category" is a well-defined, single-meaning query. Without conforming, each fact would carry its own inconsistent product labels.
• The bus matrix. Kimball's planning artifact: a grid of business processes (rows) × conformed dimensions (columns). It tells you, at a glance, which dimensions to build once and reuse everywhere.
• The OBT connection. You still model conformed dimensions even when your serving layer is an OBT — you denormalize from the conformed star. The star is the model; the OBT is a projection of it.

The join at query time — what the optimizer does.

• Star-join optimization. Columnar engines recognize the star shape: scan the large fact table, and for each small dimension, build a hash table and probe (or broadcast the dimension to every worker). This is fast because dimensions are small enough to fit in memory.
• Filter pushdown / bloom filters. A filter on dim_date.year = 2026 can be pushed into the fact scan as a semi-join bloom filter, so the engine skips fact partitions that cannot match — the star can be faster than an OBT for highly selective dimension filters.
• The failure mode. A dimension that grows large (a "monster dimension" like dim_user with hundreds of millions of rows) breaks the broadcast assumption and turns the star join expensive. This is one signal to consider denormalizing that attribute into the fact.

Common interview probes on the star schema.

• "What is the grain of this fact table?" — the first question about any fact; a fluent answer names one row's meaning precisely.
• "What is a conformed dimension and why does it matter?" — shared dimension across facts; enables coherent cross-process analysis.
• "Why surrogate keys instead of natural keys?" — warehouse-controlled identity, SCD history, cheap integer joins.
• "When does a star join get expensive?" — monster dimensions that can't broadcast; that's a denormalization signal.

Worked example — star DDL and a typical analytical JOIN

Detailed explanation. The canonical star: a fact_sales at order-line grain referencing four dimensions by surrogate key, and a dashboard query that joins the fact to two dimensions to produce revenue by category and month. Build the whole thing so the join shape is concrete.

• Grain. One row per order line.
• Dimensions. dim_date, dim_product, dim_customer, dim_store.
• Measures. quantity, amount_cents.
• Query. Revenue by category and calendar month.

Question. Write the star DDL for fact_sales and its dimensions, then the analytical query that produces revenue by category and month.

Input.

Object	Rows	Role
fact_sales	~1B	measures + FKs, order-line grain
dim_product	~50K	product attributes
dim_customer	~5M	customer attributes
dim_date	~4K	calendar attributes
dim_store	~2K	store attributes

Code.

-- Dimensions — small, wide, surrogate-keyed
CREATE TABLE dim_date (
    date_key       INT         PRIMARY KEY,   -- surrogate (e.g. 20260806)
    calendar_date  DATE        NOT NULL,
    year_month     CHAR(7)     NOT NULL,      -- '2026-08'
    quarter        CHAR(2)     NOT NULL,
    day_of_week    SMALLINT    NOT NULL
);

CREATE TABLE dim_product (
    product_key    INT         PRIMARY KEY,   -- surrogate
    sku            TEXT        NOT NULL,       -- natural key
    product_name   TEXT        NOT NULL,
    category       TEXT        NOT NULL,
    brand          TEXT        NOT NULL
);

CREATE TABLE dim_customer (
    customer_key      INT      PRIMARY KEY,    -- surrogate
    customer_id       TEXT     NOT NULL,       -- natural key
    customer_segment  TEXT     NOT NULL,
    loyalty_tier      TEXT     NOT NULL
);

CREATE TABLE dim_store (
    store_key      INT         PRIMARY KEY,
    store_id       TEXT        NOT NULL,
    store_region   TEXT        NOT NULL
);

-- Fact — narrow, big, all foreign keys + measures
CREATE TABLE fact_sales (
    sale_id        BIGINT      PRIMARY KEY,
    date_key       INT         NOT NULL REFERENCES dim_date(date_key),
    product_key    INT         NOT NULL REFERENCES dim_product(product_key),
    customer_key   INT         NOT NULL REFERENCES dim_customer(customer_key),
    store_key      INT         NOT NULL REFERENCES dim_store(store_key),
    quantity       INT         NOT NULL,
    amount_cents   BIGINT      NOT NULL
);

-- The typical analytical query — fact joined to two dimensions
SELECT p.category,
       d.year_month,
       SUM(f.amount_cents) AS revenue_cents,
       SUM(f.quantity)     AS units
FROM   fact_sales   f
JOIN   dim_product  p ON p.product_key = f.product_key
JOIN   dim_date     d ON d.date_key    = f.date_key
WHERE  d.year_month BETWEEN '2026-01' AND '2026-06'
GROUP  BY p.category, d.year_month
ORDER  BY d.year_month, revenue_cents DESC;

Step-by-step explanation.

1. Each dimension has a surrogate integer primary key (date_key, product_key, ...) that the warehouse controls, plus the source system's natural key (sku, customer_id) kept as an attribute. Joins are on the cheap surrogate integers, not the wide natural strings.
2. fact_sales is deliberately narrow: four foreign keys and two measures. This is the big table (a billion rows), so keeping it to keys-and-numbers is what makes it compress well and scan fast.
3. The grain — one row per order line — is enforced by the design: sale_id is unique, and every row carries exactly one product, customer, store, and date key. Any measure you SUM is additive across all four dimensions because the grain is atomic.
4. The analytical query joins fact_sales to dim_product and dim_date. The optimizer scans the fact, broadcasts the two small dimensions, and probes. The WHERE d.year_month BETWEEN ... filter is pushed into a semi-join so the fact scan can skip non-matching partitions if the fact is clustered on date_key.
5. Because dim_product and dim_date are conformed, this exact join shape is reusable for fact_returns — "return revenue by category and month" is the same query against a different fact, and the two results are directly comparable.

Output.

category	year_month	revenue_cents	units
Electronics	2026-01	84,210,000	12,140
Apparel	2026-01	41,880,000	33,910
Electronics	2026-02	79,455,000	11,530
Home	2026-02	22,190,000	8,470

Rule of thumb. Keep facts narrow (keys + additive measures), keep dimensions wide and surrogate-keyed, and declare the grain in a comment at the top of every fact DDL. A star schema whose grain is written down is a star schema whose measures are safe to SUM.

Worked example — conformed-dimension reuse across two facts

Detailed explanation. The point of conforming dimensions is that a single dim_customer and dim_date serve fact_sales and fact_returns identically, so cross-process metrics like "return rate by customer segment" are well defined. Walk through the reuse and the query that only conformed dimensions make coherent.

• The two facts. fact_sales (order lines) and fact_returns (returned lines), both at line grain.
• The shared dimensions. dim_customer, dim_date, dim_product — one definition, joined by both facts.
• The payoff. "Return rate by segment" divides returns by sales, grouped by the same customer_segment value from the same dimension.

Question. Show how one conformed dim_customer lets you compute return rate by customer segment across two fact tables.

Input.

Fact	Grain	Joins conformed dim
fact_sales	order line	dim_customer, dim_date, dim_product
fact_returns	return line	dim_customer, dim_date, dim_product

Code.

-- fact_returns references the SAME conformed dimensions as fact_sales
CREATE TABLE fact_returns (
    return_id      BIGINT  PRIMARY KEY,
    date_key       INT     NOT NULL REFERENCES dim_date(date_key),
    product_key    INT     NOT NULL REFERENCES dim_product(product_key),
    customer_key   INT     NOT NULL REFERENCES dim_customer(customer_key),
    quantity       INT     NOT NULL,
    refund_cents   BIGINT  NOT NULL
);

-- Return rate by customer segment — only coherent because dim_customer conforms
WITH sales AS (
    SELECT c.customer_segment,
           SUM(f.amount_cents) AS sales_cents
    FROM   fact_sales   f
    JOIN   dim_customer c ON c.customer_key = f.customer_key
    GROUP  BY c.customer_segment
),
returns AS (
    SELECT c.customer_segment,
           SUM(r.refund_cents) AS refund_cents
    FROM   fact_returns r
    JOIN   dim_customer c ON c.customer_key = r.customer_key
    GROUP  BY c.customer_segment
)
SELECT s.customer_segment,
       s.sales_cents,
       COALESCE(rt.refund_cents, 0)                            AS refund_cents,
       ROUND(100.0 * COALESCE(rt.refund_cents, 0) / s.sales_cents, 2) AS return_rate_pct
FROM   sales s
LEFT JOIN returns rt ON rt.customer_segment = s.customer_segment
ORDER  BY return_rate_pct DESC;

Step-by-step explanation.

1. fact_returns references the identical dim_customer surrogate key that fact_sales uses. There is one dim_customer table, so customer_segment = 'Premium' means exactly the same set of customers in both fact contexts.
2. The sales CTE aggregates order revenue by segment; the returns CTE aggregates refund amounts by segment. Both group by the same customer_segment column from the same dimension — this is the coherence conforming buys.
3. The final join stitches sales and returns by segment and computes a return rate. Because the segment labels come from one conformed source, the division is meaningful — you are dividing returns and sales for genuinely the same customer population.
4. If the two facts had each carried their own copy of segment labels (unconformed), a rename or a mismatch in one would silently corrupt the return-rate metric. Conforming eliminates that entire class of "the numbers don't tie out" bug.
5. This is also the argument for keeping a star even when you serve OBTs: the conformed dimensions are the governance layer that guarantees cross-mart consistency. Two OBTs built from the same conformed star agree by construction.

Output.

customer_segment	sales_cents	refund_cents	return_rate_pct
Bargain	18,400,000	1,930,000	10.49
Standard	52,100,000	3,120,000	5.99
Premium	90,700,000	2,410,000	2.66

Rule of thumb. Build each shared dimension once and conform it across every fact that needs it. A conformed dim_date and dim_customer are the difference between a warehouse whose cross-process metrics tie out and one where every team's numbers disagree. Draw the bus matrix before you build the first fact.

Worked example — declaring and defending the grain of a fact table

Detailed explanation. Grain is the meaning of one fact row, and getting it wrong is the most expensive modeling mistake because every measure and every join depends on it. Walk through choosing the grain for a sales fact and the consequences of choosing too coarse a grain.

• Candidate grains. (a) one row per order line, (b) one row per order, (c) one row per daily product-store total.
• The atomic choice. One row per order line is the finest, most flexible grain — you can always roll up, never drill down past your grain.
• The trap. A pre-aggregated grain (daily totals) is smaller but loses the ability to answer line-level questions and makes non-additive measures dangerous.

Question. Choose the grain for fact_sales, and show why a too-coarse grain breaks a later question.

Input.

Candidate grain	Rows	Can answer line-level Q?	Additive?
Order line (atomic)	~1B	yes	yes
Order header	~200M	no (line detail lost)	mostly
Daily product-store total	~50M	no	risky (avg not additive)

Code.

-- Atomic grain: one row per order line — safe to SUM anything
-- (grain: exactly one line item of one order)
SELECT SUM(amount_cents) AS revenue    -- additive across every dimension
FROM   fact_sales;

-- A later question: average line value by category.
-- Works ONLY at line grain:
SELECT p.category,
       AVG(f.amount_cents) AS avg_line_value_cents
FROM   fact_sales  f
JOIN   dim_product p ON p.product_key = f.product_key
GROUP  BY p.category;

-- If the fact had been built at DAILY product-store grain with a
-- precomputed avg_line_value, this AVG would be an "average of averages"
-- (WRONG). You cannot recover the true line-level average from
-- pre-aggregated rows:
-- SELECT category, AVG(avg_line_value) ...   -- statistically incorrect

Step-by-step explanation.

1. At order-line grain, every row is one line item, so SUM(amount_cents) is genuinely additive across any dimension combination — sum by product, by customer, by day, in any slice, and the numbers are correct.
2. "Average line value by category" needs the individual line amounts. At line grain, AVG(amount_cents) is exact. This is a question you likely did not anticipate when building the fact — and the atomic grain answered it for free.
3. Had you built the fact at daily product-store grain to save rows, each row would be a pre-aggregated total. The line-level AVG becomes an "average of averages," which is statistically wrong — you cannot recover the true mean from group means without the counts, and even with counts you have thrown away the distribution.
4. The rule "declare the grain, and make it as atomic as your storage budget allows" exists precisely because you cannot drill below your grain later. Rolling up is always possible; rolling down requires rebuilding from source.
5. This grain discipline carries directly into OBT design: the OBT inherits the fact's grain. A one-big-table built from a line-grain fact is a line-grain OBT — you denormalize the dimensions onto each line, but you do not change what a row means.

Output.

Question	Line grain	Daily-total grain
Total revenue	correct	correct
Revenue by category/day	correct	correct
Average line value	correct	wrong (avg of avgs)
Line-count distribution	available	lost

Rule of thumb. Declare the grain in words before writing DDL, and choose the most atomic grain your budget allows. You can always aggregate up from a fine grain; you can never recover detail from a coarse one. "What is the grain?" is the first question an interviewer asks about any fact — answer it in one precise sentence.

Senior interview question on star schema grain and conforming

A senior interviewer might ask: "You're modeling a subscription business with signups, renewals, cancellations, and usage events. Design the star schema — the fact tables, their grains, and the conformed dimensions — and explain how you'd guarantee that 'active customers by plan' means the same thing whether it's computed from the renewals fact or the usage fact."

Solution Using conformed dimensions, declared grains, and a shared date-spine

-- Conformed dimensions — built once, shared by every fact
CREATE TABLE dim_date (
    date_key      INT     PRIMARY KEY,
    calendar_date DATE    NOT NULL,
    year_month    CHAR(7) NOT NULL
);

CREATE TABLE dim_customer (
    customer_key  INT     PRIMARY KEY,   -- surrogate; SCD-2 capable
    customer_id   TEXT    NOT NULL,      -- natural key
    plan          TEXT    NOT NULL,      -- 'free' | 'pro' | 'enterprise'
    valid_from    DATE    NOT NULL,      -- SCD type-2 validity window
    valid_to      DATE,
    is_current    BOOLEAN NOT NULL
);

-- Fact 1 — grain: one row per subscription lifecycle event
CREATE TABLE fact_subscription_events (
    event_id      BIGINT  PRIMARY KEY,
    date_key      INT     NOT NULL REFERENCES dim_date(date_key),
    customer_key  INT     NOT NULL REFERENCES dim_customer(customer_key),
    event_type    TEXT    NOT NULL,      -- 'signup'|'renewal'|'cancel'
    mrr_cents     BIGINT  NOT NULL       -- monthly recurring revenue delta
);

-- Fact 2 — grain: one row per customer per usage-day
CREATE TABLE fact_usage_daily (
    date_key      INT     NOT NULL REFERENCES dim_date(date_key),
    customer_key  INT     NOT NULL REFERENCES dim_customer(customer_key),
    api_calls     BIGINT  NOT NULL,
    active_flag   BOOLEAN NOT NULL,      -- did the customer use the product?
    PRIMARY KEY (date_key, customer_key)
);

-- "Active customers by plan" from the USAGE fact
SELECT dt.year_month,
       c.plan,
       COUNT(DISTINCT u.customer_key) AS active_customers
FROM   fact_usage_daily u
JOIN   dim_date         dt ON dt.date_key     = u.date_key
JOIN   dim_customer     c  ON c.customer_key  = u.customer_key
WHERE  u.active_flag
GROUP  BY dt.year_month, c.plan;

-- "Paying customers by plan" from the SUBSCRIPTION EVENTS fact —
-- same conformed dim_customer, same dim_date, so 'plan' is identical
SELECT dt.year_month,
       c.plan,
       COUNT(DISTINCT e.customer_key) AS paying_customers
FROM   fact_subscription_events e
JOIN   dim_date     dt ON dt.date_key    = e.date_key
JOIN   dim_customer c  ON c.customer_key = e.customer_key
WHERE  e.event_type IN ('signup', 'renewal')
GROUP  BY dt.year_month, c.plan;

Step-by-step trace.

Layer	Choice	Reasoning
dim_date	conformed, one spine	every fact aligns on the same calendar
dim_customer	conformed, SCD-2	one plan definition; history preserved
fact_subscription_events	grain: one lifecycle event	additive MRR deltas
fact_usage_daily	grain: one customer-day	additive usage; distinct-count active
"plan" source	only dim_customer	both facts read the SAME plan attribute
cross-fact metric	join both to dim_customer	active vs paying tie out by plan

Because plan lives only in the conformed dim_customer, both "active customers by plan" (usage fact) and "paying customers by plan" (events fact) read the identical attribute — so the two metrics are directly comparable and no team can drift a private copy of the plan labels. The SCD-2 validity window means a customer who upgraded mid-month is counted under the correct plan for each period.

Output:

year_month	plan	active_customers	paying_customers
2026-05	pro	18,220	19,010
2026-05	enterprise	2,540	2,540
2026-06	pro	18,900	19,440
2026-06	enterprise	2,610	2,615

Why this works — concept by concept:

• Conformed dim_customer — one definition of plan, joined by every fact. "Active by plan" and "paying by plan" cannot disagree on what a plan is, because there is exactly one source of the attribute.
• Declared grains — the events fact is one row per lifecycle event (additive MRR); the usage fact is one row per customer-day (additive usage, distinct-count for actives). Writing the grain down makes every aggregation provably correct.
• Shared date spine — a single conformed dim_date means both facts bucket into the identical year_month, so period-over-period comparisons align exactly.
• SCD type-2 on the dimension — the valid_from/valid_to/is_current window preserves plan history, so a mid-period upgrade is attributed to the right plan in the right period rather than being retroactively rewritten.
• Cost — a handful of small conformed dimensions plus two narrow facts. Join cost is O(fact rows) with broadcast dimensions; the coherence payoff is that every cross-fact metric ties out by construction. The alternative — per-fact private dimensions — is cheaper to build once and catastrophically expensive to reconcile forever.

Dimensional Modeling
Topic — dimensional-modeling
Dimensional modeling — grain and conformed-dimension problems

Practice →

SQL Topic — sql SQL problems on fact-dimension aggregation

Practice →

---

3. One Big Table: denormalize everything into one wide table

one big table pre-joins the star into a single wide, denormalized table — queried with no joins, at the price of duplicated attributes and rebuild-on-change

The mental model in one line: a one big table is a fully denormalized wide table produced by pre-joining a fact to all of its dimensions at build time, so that every dimension attribute is physically inlined next to the measures on every fact row — the query layer touches exactly one object and runs zero joins, which is the pattern's headline advantage, and it pays for that with duplicated attributes, update anomalies, and the need to rebuild (or incrementally patch) the whole table whenever a dimension attribute changes. On a columnar warehouse where storage is cheap and scans are column-pruned, the OBT went from "denormalization is a beginner's mistake" to "denormalization is a legitimate serving-layer optimization for a known set of dashboard queries."

The wide-table pattern.

• Pre-joined at build time. The expensive part (the joins) happens once, when the OBT is materialized, instead of on every query. You move join cost from read time (many times) to write time (once per refresh).
• One row per fact-grain, all context inlined. The OBT inherits the fact's grain. Each row carries the measures plus every dimension attribute the dashboards need — category, customer_segment, store_region, year_month — as first-class columns.
• The build query is a big JOIN. CREATE TABLE obt AS SELECT ... FROM fact JOIN dim1 JOIN dim2 .... This is the entire construction; the sophistication is in clustering, incrementality, and SCD handling, not in the join itself.
• The serving contract. BI tools and analysts query SELECT ... FROM obt WHERE ... GROUP BY ... with no joins, which is exactly what BI tools generate well and analysts write without error.

The pros — why teams reach for it.

• No joins at query time. Zero join planning, zero shuffle, zero broadcast, zero risk of a bad join plan. Predictable single-scan latency.
• BI-tool friendly. Tools like Looker, Tableau, and Power BI generate cleaner SQL against one flat table than against a multi-join star, and self-service users don't have to understand the join graph.
• Column pruning makes it cheap to query. A query touching 4 of 40 columns reads only those 4 columns — the table's width does not tax narrow queries.
• Simplicity of the mental model. "One table, all the columns" is easy to document, easy to grant access to, and easy for downstream consumers to reason about.

The cons — what you pay.

• Storage duplication. Every dimension attribute is repeated on every fact row. Compression blunts this (section 1), but it is never zero, and high-cardinality attributes (long free-text) duplicate poorly.
• Update anomalies. A dimension attribute that changes (a product recategorized) is now wrong on every historical row that inlined the old value — until you rebuild. The star fixed this in one place; the OBT has to touch many rows.
• Rebuild on dimension change. A new attribute or a corrected attribute means re-running the big JOIN (full rebuild) or an incremental patch. This is the OBT's dominant operational cost.
• Grain lock-in. The OBT is shaped for one grain and one set of dimensions. A genuinely new grain or a new dimension attribute is a new build, not a new join.

Common interview probes on the OBT.

• "When does an OBT beat a star?" — fixed dashboard queries, one grain, stable dimensions, BI-tool self-service.
• "What does an OBT cost you?" — storage duplication + update anomalies + rebuild-on-change.
• "How do you keep an OBT fresh?" — scheduled full rebuild or incremental append + dimension-change patch.
• "Does the OBT replace the star?" — no; you build the OBT from a conformed star, which stays the source of truth.

Worked example — building the OBT via a big JOIN materialization

Detailed explanation. The canonical OBT build: pre-join fact_sales to all four dimensions and materialize the result as a clustered wide table. Walk through the build query and the clustering choice that makes the resulting table cheap to scan.

• Source. The conformed star from section 2.
• Target. obt_sales — one row per order line, all dimension attributes inlined.
• Clustering. By the columns dashboards filter and group on (date_key, category).

Question. Write the OBT build query that materializes obt_sales from the star, clustered for dashboard access.

Input.

Source object	Inlined attributes
fact_sales	quantity, amount_cents (measures)
dim_date	calendar_date, year_month, quarter
dim_product	product_name, category, brand
dim_customer	customer_segment, loyalty_tier
dim_store	store_region

Code.

-- Build the one big table by pre-joining the star (Snowflake syntax)
CREATE OR REPLACE TABLE obt_sales
CLUSTER BY (date_key, category) AS
SELECT
    -- keys retained for incremental refresh + drill-back to the star
    f.sale_id,
    f.date_key,
    f.product_key,
    f.customer_key,
    f.store_key,
    -- inlined date attributes
    d.calendar_date,
    d.year_month,
    d.quarter,
    -- inlined product attributes
    p.product_name,
    p.category,
    p.brand,
    -- inlined customer attributes
    c.customer_segment,
    c.loyalty_tier,
    -- inlined store attributes
    s.store_region,
    -- measures
    f.quantity,
    f.amount_cents
FROM   fact_sales   f
JOIN   dim_date     d ON d.date_key     = f.date_key
JOIN   dim_product  p ON p.product_key  = f.product_key
JOIN   dim_customer c ON c.customer_key = f.customer_key
JOIN   dim_store    s ON s.store_key    = f.store_key;

Step-by-step explanation.

1. The build is a single CREATE TABLE AS SELECT with the fact joined to all four dimensions. The joins that a star would run on every dashboard query happen exactly once here, at build time. This is the core trade: pay the join cost once on write instead of many times on read.
2. The surrogate keys (date_key, product_key, ...) are kept in the OBT even though the attributes are inlined. This is deliberate — the keys enable incremental refresh (append only new date_keys) and let you drill back to the star for any attribute you did not inline.
3. Every dimension attribute the dashboards need is projected as a first-class column: category, customer_segment, store_region, year_month. After the build, a dashboard never joins — it selects these columns directly.
4. CLUSTER BY (date_key, category) tells Snowflake to co-locate rows with the same date and category into the same micro-partitions. Dashboards that filter on a date range and group by category then scan far fewer partitions, and the repeated category values within a partition compress into long dictionary runs.
5. The build is O(fact rows) and runs on a schedule (or incrementally). Its cost is bounded and predictable; the query saving it buys — zero joins on thousands of daily dashboard queries — is what justifies it.

Output.

Build metric	Value
Output rows	= fact_sales rows (line grain preserved)
Columns	17 (5 keys + 9 attributes + 2 measures + sale_id)
Joins at build	4 (once)
Joins at query	0
Clustering	(date_key, category)

Rule of thumb. Build the OBT with a single CREATE TABLE AS SELECT that joins the conformed star, keep the surrogate keys for incremental refresh and drill-back, and cluster on the columns dashboards filter and group by. The OBT is a projection of the star, not a replacement for it.

Worked example — querying the OBT with zero joins

Detailed explanation. With obt_sales materialized, the same analytics that took a four-way join in the star become single-scan, no-join queries. Walk through three representative dashboard queries and confirm none of them touch a second table.

• Query A. Revenue by category and month.
• Query B. Units by store region and loyalty tier.
• Query C. Top brands by revenue in a date range.

Question. Rewrite three star dashboard queries against the OBT and confirm zero joins.

Input.

Query	Star version	OBT version
A: revenue by category/month	fact + 2 joins	single scan
B: units by region/tier	fact + 3 joins	single scan
C: top brands in a range	fact + 2 joins	single scan

Code.

-- A: revenue by category and month — no joins
SELECT category,
       year_month,
       SUM(amount_cents) AS revenue_cents
FROM   obt_sales
WHERE  year_month BETWEEN '2026-01' AND '2026-06'
GROUP  BY category, year_month
ORDER  BY year_month, revenue_cents DESC;

-- B: units by store region and loyalty tier — no joins
SELECT store_region,
       loyalty_tier,
       SUM(quantity) AS units
FROM   obt_sales
GROUP  BY store_region, loyalty_tier;

-- C: top 10 brands by revenue in Q1 — no joins
SELECT brand,
       SUM(amount_cents) AS revenue_cents
FROM   obt_sales
WHERE  quarter = 'Q1' AND year_month LIKE '2026-%'
GROUP  BY brand
ORDER  BY revenue_cents DESC
LIMIT  10;

Step-by-step explanation.

1. Query A filters on year_month and groups by category — both are inlined columns, so the engine scans obt_sales once, prunes partitions using the (date_key, category) clustering, and aggregates. No dim_date or dim_product join exists to plan.
2. Query B groups by store_region and loyalty_tier — attributes that in the star lived in dim_store and dim_customer and would have required a three-way join. In the OBT they are columns; the query is a single scan.
3. Query C filters on quarter and year_month and ranks brand. Again, all three are inlined columns. Column pruning means the scan reads only brand, amount_cents, quarter, and year_month — four of seventeen columns — so the table's width costs nothing here.
4. In every case the BI tool (or the analyst) writes flat, join-free SQL. This is the ergonomic win: no join graph to understand, no risk of a fan-out join inflating measures, no bad join plan.
5. The trade you already paid: these attributes are duplicated across every row and frozen at build time. If a brand is renamed, queries A–C show the old name until the next OBT rebuild. That is the update-anomaly cost the next worked example quantifies.

Output.

Query	Objects scanned	Joins	Columns read
A	obt_sales	0	3 of 17
B	obt_sales	0	3 of 17
C	obt_sales	0	4 of 17

Rule of thumb. Every OBT query is SELECT cols FROM obt WHERE ... GROUP BY ... with no joins and column pruning doing the I/O work. If a dashboard query against your OBT still needs a join, an attribute is missing from the table — inline it in the next build rather than joining at query time.

Worked example — when does OBT beat star (and the update-anomaly cost)

Detailed explanation. The OBT wins for fixed, high-frequency dashboard queries over stable dimensions, and loses when dimensions change often or questions are open-ended — because a dimension change forces a rebuild or a wide patch. Quantify both sides for a product recategorization so you can defend the choice with numbers.

• The win. 5,000 dashboard queries/day, each saving four joins, all single-scan.
• The cost. A category rename touches every historical row inlining the old category — a full rebuild or a targeted UPDATE across millions of rows.
• The break-even. OBT pays off when query volume × join-cost-saved exceeds rebuild-cost × change-frequency.

Question. Show the update anomaly a product recategorization causes in the OBT and the two ways to fix it, then state when OBT still wins.

Input.

Factor	Star	OBT
Category rename	1 row in dim_product	N rows in obt_sales
Dashboard query cost	fact + joins	single scan
Change frequency	any	forces rebuild/patch
Query frequency	high	high

Code.

-- STAR: recategorize a product — one row, instantly correct everywhere
UPDATE dim_product
SET    category = 'Home & Kitchen'
WHERE  product_key = 8842;
-- every fact query now reflects the new category immediately

-- OBT: the old category is frozen onto every historical row.
-- Fix option 1 — targeted patch (correct but scans/updates many rows)
UPDATE obt_sales
SET    category = 'Home & Kitchen'
WHERE  product_key = 8842;

-- Fix option 2 — incremental rebuild of affected rows from the star
CREATE OR REPLACE TABLE obt_sales_patch AS
SELECT o.* EXCLUDE (category),
       p.category                        -- re-read the corrected attribute
FROM   obt_sales  o
JOIN   dim_product p ON p.product_key = o.product_key
WHERE  o.product_key = 8842;
-- ... then MERGE the patch back into obt_sales

# Break-even: does the OBT's query saving beat its rebuild cost?
QUERIES_PER_DAY     = 5000
JOINS_SAVED_PER_Q   = 4
SECONDS_SAVED_PER_J = 0.6          # avg join build+probe saved per query
rebuild_seconds     = 900          # full OBT rebuild
dim_changes_per_day = 3            # category-type corrections/day

query_saving_per_day  = QUERIES_PER_DAY * JOINS_SAVED_PER_Q * SECONDS_SAVED_PER_J
rebuild_cost_per_day  = rebuild_seconds * dim_changes_per_day

print(f"query saving/day: {query_saving_per_day:8.0f} s")
print(f"rebuild cost/day: {rebuild_cost_per_day:8.0f} s")
print("OBT wins" if query_saving_per_day > rebuild_cost_per_day else "star wins")
# → query saving/day:    12000 s
# → rebuild cost/day:      2700 s
# → OBT wins

Step-by-step explanation.

1. In the star, recategorizing a product is a one-row UPDATE dim_product. Every fact query joins the dimension at read time, so the correction is visible instantly and everywhere. This is the star's maintainability advantage in one statement.
2. In the OBT, category was inlined at build time, so the old value is frozen on every historical row for that product. Nothing is automatically corrected — this is the update anomaly denormalization introduces.
3. Fix option 1 is a targeted UPDATE obt_sales ... WHERE product_key = 8842, which is correct but scans and rewrites every micro-partition containing that product — potentially expensive on a columnar store that rewrites partitions rather than editing in place.
4. Fix option 2 rebuilds only the affected rows by re-joining them to the corrected dimension and merging back. This is the incremental-patch pattern; it bounds the rewrite to the changed product rather than the whole table.
5. The break-even calculation shows why OBT still wins here: 5,000 daily queries each saving four joins saves ~12,000 seconds/day, while three category corrections costing a 15-minute rebuild each cost ~2,700 seconds/day. Query volume × join saving dominates rebuild cost × change frequency — so for high-traffic, low-volatility dashboards, the OBT is the right call despite the anomaly.

Output.

Scenario	Star	OBT	Winner
Category rename effort	1 row	patch/rebuild many rows	Star
5,000 daily dashboards	fact + 4 joins each	single scan each	OBT
Break-even (this workload)	—	saving 12k s > cost 2.7k s	OBT
Highly volatile dimensions	localized fix	constant rebuilds	Star

Rule of thumb. OBT beats star when query volume × joins-saved dominates rebuild-cost × dimension-change-frequency — i.e. high-traffic dashboards over stable dimensions. Compute the break-even before committing; if your dimensions change hourly and your query volume is modest, the star's localized fix wins.

Senior interview question on OBT design

A senior interviewer might ask: "Your BI team wants a self-service 'sales explorer' dataset. The dimensions (product, customer, store) change a few times a day; there are ~8,000 dashboard queries/day; and there are 1.2 billion fact rows. Design the OBT — the build, the freshness strategy, the clustering, and how you'll handle the daily dimension changes without rebuilding 1.2 billion rows every time."

Solution Using an incrementally-refreshed, clustered OBT with dimension-change patching

-- 1. Initial full build — the big JOIN, clustered for the explorer's access pattern
CREATE OR REPLACE TABLE obt_sales
CLUSTER BY (date_key, category) AS
SELECT f.sale_id, f.date_key, f.product_key, f.customer_key, f.store_key,
       d.year_month, d.calendar_date,
       p.category, p.brand, p.product_name,
       c.customer_segment, c.loyalty_tier,
       s.store_region,
       f.quantity, f.amount_cents
FROM   fact_sales   f
JOIN   dim_date     d ON d.date_key     = f.date_key
JOIN   dim_product  p ON p.product_key  = f.product_key
JOIN   dim_customer c ON c.customer_key = f.customer_key
JOIN   dim_store    s ON s.store_key    = f.store_key;

-- 2. Incremental append — only NEW fact rows since the last watermark.
--    Avoids re-joining 1.2B rows every refresh.
MERGE INTO obt_sales tgt
USING (
    SELECT f.sale_id, f.date_key, f.product_key, f.customer_key, f.store_key,
           d.year_month, d.calendar_date,
           p.category, p.brand, p.product_name,
           c.customer_segment, c.loyalty_tier,
           s.store_region,
           f.quantity, f.amount_cents
    FROM   fact_sales   f
    JOIN   dim_date     d ON d.date_key     = f.date_key
    JOIN   dim_product  p ON p.product_key  = f.product_key
    JOIN   dim_customer c ON c.customer_key = f.customer_key
    JOIN   dim_store    s ON s.store_key    = f.store_key
    WHERE  f.sale_id > (SELECT COALESCE(MAX(sale_id), 0) FROM obt_sales)
) src
ON tgt.sale_id = src.sale_id
WHEN NOT MATCHED THEN INSERT (
    sale_id, date_key, product_key, customer_key, store_key,
    year_month, calendar_date, category, brand, product_name,
    customer_segment, loyalty_tier, store_region, quantity, amount_cents
) VALUES (
    src.sale_id, src.date_key, src.product_key, src.customer_key, src.store_key,
    src.year_month, src.calendar_date, src.category, src.brand, src.product_name,
    src.customer_segment, src.loyalty_tier, src.store_region, src.quantity, src.amount_cents
);

-- 3. Dimension-change patch — re-sync ONLY the products/customers/stores
--    whose attributes changed since the last run (tracked via a CDC feed
--    or dim.updated_at), instead of rebuilding 1.2B rows.
MERGE INTO obt_sales tgt
USING (
    SELECT p.product_key, p.category, p.brand, p.product_name
    FROM   dim_product p
    WHERE  p.updated_at > DATEADD('hour', -1, CURRENT_TIMESTAMP())
) chg
ON tgt.product_key = chg.product_key
WHEN MATCHED THEN UPDATE SET
    tgt.category     = chg.category,
    tgt.brand        = chg.brand,
    tgt.product_name = chg.product_name;

Step-by-step trace.

Concern	Answer	Reasoning
Initial build	full big JOIN, clustered	one-time O(1.2B) materialization
New facts	MERGE on sale_id > watermark	append-only; never re-join old rows
Dimension changes	targeted MERGE on changed keys	patch affected rows only, not 1.2B
Clustering	(date_key, category)	prune + compress for explorer filters
Freshness	facts every 15 min; dims hourly	matches change cadence per source
Source of truth	conformed star	OBT stays a derived projection

After deployment, new sales append via a watermark MERGE (touching only the day's rows), the few daily product/customer/store changes patch only their affected partitions via a keyed MERGE, and the 8,000 daily explorer queries all run as single clustered scans. The 1.2-billion-row table is never fully rebuilt except on schema change.

Output:

Metric	Value
Explorer query joins	0
Explorer p50 latency	~1.5 s (clustered single scan)
Fact refresh cost	O(new rows/day), not O(1.2B)
Dimension patch cost	O(rows for changed keys)
Full rebuild frequency	only on schema change
Source of truth	conformed star (unchanged)

Why this works — concept by concept:

• Big-JOIN materialization once — the four-way join runs at build time, not on 8,000 daily queries. The OBT moves join cost from read (many) to write (once + incremental).
• Watermark MERGE for new facts — appending only sale_id > MAX(sale_id) keeps refresh O(new rows), so a 1.2B-row table refreshes in minutes, not the hours a full rebuild would take.
• Keyed dimension-change patch — instead of rebuilding everything when a category changes, a MERGE on the handful of changed product_keys rewrites only their partitions. This directly answers the update-anomaly cost with a bounded operation.
• Cluster by (date_key, category) — co-locates the explorer's filter/group columns so scans prune partitions and dictionary runs stay long, keeping single-scan latency low and cheap.
• Cost — one derived table (~a few GB over the fact, compressed), a 15-minute fact MERGE, and an hourly dimension patch. In exchange, 8,000 daily queries pay zero join cost. The build is O(new + changed) per cycle; the query saving is O(joins eliminated) × query volume — an overwhelmingly favorable trade for a fixed dashboard workload.

Joins
Topic — joins
Joins — denormalization and pre-join problems

Practice →

ETL Topic — etl ETL problems on incremental materialization

Practice →

---

4. Performance, storage & cost: measuring the trade-off

On a columnar warehouse the trade-off is measurable — scan bytes, compression, and pruning decide whether OBT or star is cheaper for your workload

The mental model in one line: columnar cloud warehouses bill (or bottleneck) primarily on bytes scanned, so the OBT-vs-star cost question reduces to a measurable comparison: the OBT trades a single wider scan with no join for the star's narrower fact scan plus dimension joins, while duplication makes the OBT larger on disk and compression plus clustering decide how much of that width you actually read — and the honest answer to "which is cheaper" is always "measure the scan bytes and the storage bytes for your queries," never a blanket rule. This is the section where you stop arguing philosophy and start reading query_history.

The columnar cost model — what you actually pay for.

• Bytes scanned drives cost. BigQuery bills on-demand queries by bytes scanned; Snowflake and Databricks bill compute-seconds, which are dominated by how much data the scan must read and decode. Either way, the currency is bytes read.
• Column pruning. Reading 4 of 40 columns reads only those 4 columns' bytes. This is why a wide OBT is cheap for narrow queries — width you don't select is width you don't pay for.
• Partition / micro-partition pruning. A filter on a clustered/partitioned column lets the engine skip whole partitions. WHERE year_month = '2026-06' on a date-clustered table reads one month, not the table.
• Compression / encoding. Dictionary, RLE, and delta encodings shrink the bytes on disk and the bytes scanned. Well-clustered low-cardinality columns compress dramatically, which is what makes OBT duplication affordable.

The scan-bytes comparison — OBT vs star.

• OBT query. One scan of the wide table, reading only the columns the query touches. No dimension scans, no join.
• Star query. Scan the fact (its needed columns) + scan each joined dimension (small) + the join's build/probe cost. For most dashboards the dimension scans are tiny; the join cost is the real difference.
• The subtlety. A highly selective dimension filter can make the star cheaper — a bloom-filter semi-join prunes the fact scan hard. But for broad group-bys over inlined attributes, the OBT's single scan usually reads fewer total bytes than fact + join overhead.
• The measurement. Run the same logical query both ways and read bytes_scanned / partitions_scanned from the warehouse's query history. Decide on the number, not the vibe.

Storage vs compute — the two bills.

• Storage bill. The OBT is larger (duplicated attributes) but storage is cheap (cents/GB-month) and compression blunts the duplication. The star is smaller on disk.
• Compute bill. The OBT is usually cheaper per query (no join) but you pay a recurring build/refresh compute cost. The star has no build cost but pays join compute on every query.
• The crossover. High query volume amortizes the OBT's build cost and magnifies its per-query saving; low query volume favors the star (no build to amortize). Storage rarely tips the decision on modern pricing.
• Warehouse specifics. Snowflake: micro-partition clustering + automatic compression; watch clustering-maintenance cost. BigQuery: partitioning + clustering + bytes-scanned billing makes column pruning directly a dollar figure. Databricks: Delta + Z-ordering / liquid clustering + file compaction.

Common interview probes on cost.

• "Why can an OBT be cheaper to query but pricier to store?" — no-join single scan (cheap query) vs duplicated attributes (more storage).
• "How do you make a wide table cheap to scan?" — column pruning + clustering/partitioning on filter columns.
• "When is the star cheaper?" — highly selective dimension filters (bloom-filter pruning) and low query volume (no build to amortize).
• "How do you decide?" — measure bytes_scanned for the real queries; compute build-cost amortization vs per-query saving.

Worked example — query-cost comparison: OBT vs star scan bytes

Detailed explanation. The decisive artifact is a bytes-scanned comparison for the same logical query run against the star and against the OBT. Build the estimate for "revenue by category and month over six months" so the cost difference is a number.

• The query. Revenue by category and month, filtered to six months.
• Star cost. Fact scan (date-clustered, so ~6 months of partitions) reading date_key, product_key, amount_cents, plus dim_product and dim_date scans, plus join.
• OBT cost. One scan of obt_sales (date+category clustered), reading year_month, category, amount_cents only.

Question. Estimate and compare bytes scanned for the star and OBT versions of the query.

Input.

Quantity	Value
Fact rows (6 months)	500,000,000
Star cols read from fact	date_key, product_key, amount_cents (~20 B/row compressed)
dim_product scan	~50K rows (~2 MB)
OBT cols read	year_month, category, amount_cents (~10 B/row compressed)

Code.

-- Read the real numbers from Snowflake after running each query once.
SELECT LEFT(query_text, 40)                 AS q,
       bytes_scanned / POWER(1024, 3)       AS gb_scanned,
       partitions_scanned,
       partitions_total,
       total_elapsed_time / 1000.0          AS seconds
FROM   snowflake.account_usage.query_history
WHERE  start_time > DATEADD('hour', -1, CURRENT_TIMESTAMP())
  AND  (query_text ILIKE '%FROM fact_sales%' OR query_text ILIKE '%FROM obt_sales%')
ORDER  BY start_time DESC;

# Estimate bytes scanned for each plan (compressed, column-pruned)
FACT_ROWS_6MO   = 500_000_000

# Star: fact scan reads 3 columns (~20 B/row compressed) + tiny dim scans
star_fact_bytes = FACT_ROWS_6MO * 20
star_dim_bytes  = 2 * 1024 * 1024              # dim_product + dim_date ~2 MB
star_total_gb   = (star_fact_bytes + star_dim_bytes) / (1024**3)

# OBT: single scan reads 3 inlined columns (~10 B/row compressed, RLE on category)
obt_bytes       = FACT_ROWS_6MO * 10
obt_total_gb    = obt_bytes / (1024**3)

print(f"star scan:  {star_total_gb:5.2f} GB  (fact scan + 2 dim scans + join)")
print(f"obt  scan:  {obt_total_gb:5.2f} GB  (single scan, no join)")
print(f"obt reads {star_total_gb / obt_total_gb:.1f}x fewer bytes, and runs 0 joins")
# → star scan:   9.31 GB  (fact scan + 2 dim scans + join)
# → obt  scan:   4.66 GB  (single scan, no join)
# → obt reads 2.0x fewer bytes, and runs 0 joins

Step-by-step explanation.

1. The star scan reads three columns from the 500M-row fact slice (~20 bytes/row compressed for two integer keys plus a measure), then scans the two small dimensions and performs the join. The dimension bytes are trivial (~2 MB); the join compute is the cost the byte count doesn't show.
2. The OBT scan reads three inlined columns (year_month, category, amount_cents) directly. category is heavily RLE-compressed because the table is clustered by (date_key, category), so its per-row cost is a fraction of a raw integer key — the OBT reads ~10 bytes/row here.
3. The OBT ends up scanning roughly half the bytes and running zero joins. This is the common case for broad group-bys over inlined low-cardinality attributes: the OBT wins both on bytes and on the eliminated join.
4. The query_history query is the point: you do not guess these numbers, you read them. bytes_scanned and partitions_scanned are recorded per query, so the comparison is empirical. Run each version once, read the two rows, decide.
5. The counter-case to keep honest: if the query were "revenue for one specific customer" (highly selective on a high-cardinality dimension), the star's bloom-filter semi-join could prune the fact to a handful of partitions and beat the OBT. Selectivity, not width, flips the result — which is why you measure per query pattern.

Output.

Plan	Bytes scanned	Joins	Notes
Star (fact + 2 dims)	~9.3 GB	2	join compute not in byte count
OBT (single scan)	~4.7 GB	0	category RLE-compressed
Winner (this query)	OBT	—	~2× fewer bytes, no join

Rule of thumb. Never assert OBT-or-star is cheaper — run both versions and read bytes_scanned from the warehouse's query history. Broad group-bys over inlined low-cardinality attributes favor the OBT; highly selective filters on high-cardinality dimensions can favor the star's semi-join pruning.

Worked example — clustering and partitioning strategy

Detailed explanation. A wide table is only cheap to scan if it is clustered/partitioned on the columns queries filter and group by; otherwise every query reads the whole table and the OBT's advantage evaporates. Walk through choosing the clustering keys and the partitioning strategy across the three major warehouses.

• The rule. Cluster/partition on the high-selectivity filter columns first, then a common group-by column.
• Snowflake. CLUSTER BY (date_key, category) — micro-partition pruning.
• BigQuery. PARTITION BY DATE(...) + CLUSTER BY category, ... — partition pruning is a hard byte-cost reduction.
• Databricks. Delta ZORDER BY / liquid clustering on the same columns + file compaction.

Question. Define the clustering/partitioning for obt_sales on each warehouse and explain what each prunes.

Input.

Warehouse	Mechanism	Keys
Snowflake	CLUSTER BY	(date_key, category)
BigQuery	PARTITION BY + CLUSTER BY	DATE(calendar_date) + category, brand
Databricks	ZORDER / liquid clustering	date_key, category

Code.

-- Snowflake — micro-partition clustering
CREATE OR REPLACE TABLE obt_sales
CLUSTER BY (date_key, category) AS
SELECT ... ;   -- (build query from section 3)
-- Snowflake auto-maintains clustering; monitor with:
SELECT SYSTEM$CLUSTERING_INFORMATION('obt_sales', '(date_key, category)');

-- BigQuery — partition (hard prune) + cluster (soft prune)
CREATE OR REPLACE TABLE analytics.obt_sales
PARTITION BY DATE(calendar_date)
CLUSTER BY category, brand AS
SELECT ... ;
-- A filter on calendar_date now scans ONLY the matching date partitions;
-- bytes_scanned (and the bill) drop proportionally.

-- Databricks Delta — liquid clustering (or ZORDER on older runtimes)
CREATE OR REPLACE TABLE obt_sales
CLUSTER BY (date_key, category) AS
SELECT ... ;
-- Older syntax: OPTIMIZE obt_sales ZORDER BY (date_key, category);
OPTIMIZE obt_sales;   -- compact small files so scans read fewer objects

Step-by-step explanation.

1. Snowflake's CLUSTER BY (date_key, category) co-locates rows with the same date and category into the same micro-partitions. A dashboard filtering a date range and grouping by category reads only the relevant micro-partitions, and SYSTEM$CLUSTERING_INFORMATION reports how well-clustered the table currently is so you know when to worry about clustering drift.
2. BigQuery's PARTITION BY DATE(calendar_date) is a hard prune: a WHERE calendar_date BETWEEN ... filter scans only the matching daily partitions, and because BigQuery bills by bytes scanned, that pruning is directly a smaller bill. CLUSTER BY category, brand then sorts within each partition for a second, softer prune.
3. Databricks liquid clustering (or ZORDER BY on older runtimes) reorganizes Delta files so that filtering on the clustered columns reads fewer files. OPTIMIZE compacts many small files into fewer large ones, which reduces per-file overhead on every scan.
4. The ordering of keys matters: put the most selective, most-filtered column first (usually date), then the common group-by (category). This maximizes both partition pruning and the length of dictionary/RLE runs, which compounds the compression benefit.
5. Without clustering, the OBT's single-scan advantage is undermined — an unclustered wide table forces a full scan for every query, so it can read more bytes than a well-clustered star fact. Clustering is not optional for a serving-layer OBT; it is the thing that makes the pattern pay off.

Output.

Warehouse	Prunes on	Effect
Snowflake	micro-partitions by (date, category)	fewer partitions scanned
BigQuery	date partitions (hard) + cluster	fewer bytes billed
Databricks	Delta files by cluster keys	fewer files read after OPTIMIZE

Rule of thumb. Cluster/partition every OBT on its most-filtered column (usually date) first, then a common group-by column. On BigQuery, partitioning is a direct dollar saving via bytes-scanned billing; on Snowflake and Databricks, clustering + compaction is what keeps single-scan latency low. An unclustered OBT is slower than the star it replaced.

Worked example — storage vs compute crossover math

Detailed explanation. The full cost comparison is storage (OBT bigger) plus compute (OBT cheaper per query, but pays a recurring build). Compute the monthly all-in cost for both models at a given query volume so the crossover is explicit.

• Storage. OBT is a few GB bigger; storage is cheap.
• Compute — star. Join cost per query × query volume.
• Compute — OBT. Cheaper scan per query × query volume + recurring build cost.
• Crossover. OBT's per-query saving must exceed its build cost at your query volume.

Question. Compute the monthly cost of star vs OBT at 8,000 queries/day and state the crossover.

Input.

Quantity	Value
Queries/day	8,000
Star compute/query	4.2 credit-seconds
OBT compute/query	1.5 credit-seconds
OBT build/day	900 credit-seconds
Extra OBT storage	5 GB

Code.

# All-in monthly cost: star vs OBT (illustrative credit-seconds)
QUERIES_DAY        = 8000
STAR_PER_QUERY     = 4.2      # credit-seconds (scan + joins)
OBT_PER_QUERY      = 1.5      # credit-seconds (single clustered scan)
OBT_BUILD_DAY      = 900      # credit-seconds/day (incremental refresh)
EXTRA_STORAGE_GB   = 5
STORAGE_COST_GB_MO = 0.02     # $/GB-month (object storage)
CREDIT_SECOND_USD  = 0.0006   # illustrative

def monthly_usd(query_cs, per_query, build_day, storage_gb):
    compute_cs = (QUERIES_DAY * per_query + build_day) * 30
    compute_usd = compute_cs * CREDIT_SECOND_USD
    storage_usd = storage_gb * STORAGE_COST_GB_MO
    return compute_usd + storage_usd

star = monthly_usd(None, STAR_PER_QUERY, 0,           0)
obt  = monthly_usd(None, OBT_PER_QUERY,  OBT_BUILD_DAY, EXTRA_STORAGE_GB)

print(f"star monthly: ${star:8.2f}")
print(f"obt  monthly: ${obt:8.2f}")
print(f"savings: ${star - obt:7.2f}/mo" if obt < star else "star cheaper")
# → star monthly: $  604.80
# → obt  monthly: $  232.30
# → savings: $ 372.50/mo

Step-by-step explanation.

1. Star monthly compute is 8,000 queries × 4.2 credit-seconds × 30 days — the join cost is paid on every single query, so it scales linearly with volume with no fixed build to offset it.
2. OBT monthly compute is (8,000 × 1.5 + 900) × 30 — a cheaper per-query scan plus a fixed daily build/refresh. The build is a small constant relative to the per-query saving at this volume.
3. The extra storage (5 GB × $0.02/GB-month = $0.10/month) is a rounding error. This confirms the section-1 claim: on modern pricing, storage almost never tips the OBT-vs-star decision — compute does.
4. At 8,000 queries/day the OBT is ~$370/month cheaper because the per-query saving (2.7 credit-seconds × 8,000 × 30) dwarfs the daily build cost (900 × 30). The crossover — where OBT stops paying off — is when query volume drops low enough that the fixed build cost is no longer amortized.
5. Solve for the crossover: OBT wins when QUERIES_DAY × (STAR_PER_QUERY − OBT_PER_QUERY) > OBT_BUILD_DAY, i.e. QUERIES_DAY > 900 / 2.7 ≈ 333 queries/day. Below ~333 daily queries the star's zero-build model is cheaper; above it, the OBT wins and the margin grows with volume.

Output.

Model	Monthly compute	Storage	All-in
Star	~$604.80	~$0	~$604.80
OBT	~$232.20	~$0.10	~$232.30
Crossover	—	—	~333 queries/day

Rule of thumb. The OBT-vs-star cost decision is a compute-amortization problem, not a storage problem. OBT wins above a crossover query volume (build_cost / per_query_saving); below it, the star's build-free model is cheaper. Compute your crossover; storage duplication is almost never the deciding cost on a modern columnar warehouse.

Senior interview question on cost measurement

A senior interviewer might ask: "Finance says the warehouse bill doubled after the team flattened everything into one big table. The engineers say OBT is 'obviously cheaper because there are no joins.' Both can't be right. Walk me through how you'd diagnose it, what metrics you'd pull, and what you'd likely find caused the regression."

Solution Using bytes-scanned diagnosis, clustering audit, and query-pattern segmentation

-- 1. Segment the bill by query pattern — which queries got MORE expensive?
SELECT
    CASE
      WHEN query_text ILIKE '%FROM obt_%'  THEN 'obt'
      WHEN query_text ILIKE '%FROM fact_%' THEN 'star'
      ELSE 'other'
    END                                          AS model,
    COUNT(*)                                      AS n,
    SUM(bytes_scanned) / POWER(1024, 4)          AS tb_scanned,
    AVG(partitions_scanned::float / NULLIF(partitions_total, 0)) AS avg_pct_partitions_scanned,
    SUM(credits_used_cloud_services)             AS cloud_credits
FROM   snowflake.account_usage.query_history
WHERE  start_time > DATEADD('day', -14, CURRENT_TIMESTAMP())
GROUP  BY 1
ORDER  BY tb_scanned DESC;

-- 2. Audit clustering health on the OBT — is it actually pruning?
SELECT SYSTEM$CLUSTERING_INFORMATION('obt_sales', '(date_key, category)');
-- A high average_overlaps / average_depth means poor clustering =>
-- queries scan far more micro-partitions than they need.

-- 3. Find the smoking gun: OBT queries with NO selective filter
--    (full-table scans that a star would have pruned via a dim filter)
SELECT LEFT(query_text, 80) AS q,
       partitions_scanned,
       partitions_total,
       bytes_scanned / POWER(1024, 3) AS gb
FROM   snowflake.account_usage.query_history
WHERE  query_text ILIKE '%FROM obt_sales%'
  AND  partitions_scanned > 0.8 * partitions_total   -- scanning ~everything
ORDER  BY bytes_scanned DESC
LIMIT  20;

Step-by-step trace.

Step	What you pull	Likely finding
Segment by model	bytes scanned per model	OBT queries dominate scan bytes
Clustering audit	SYSTEM$CLUSTERING_INFORMATION	OBT poorly clustered / never re-clustered
Full-scan hunt	partitions_scanned ≈ total	ad-hoc queries with no date filter
Build cost	task/warehouse history	nightly full rebuild, not incremental
Wide SELECT *	query_text with SELECT *	BI tool reading all 40 columns

The usual root cause is not "OBT is inherently expensive" but a combination of: the OBT was left unclustered (so every query full-scans it), analysts run SELECT * (defeating column pruning), and the refresh is a nightly full rebuild instead of an incremental MERGE. Each of these turns the OBT's theoretical single-scan saving into a full-table-scan penalty.

Output:

Root cause	Symptom	Fix
No clustering	high partitions_scanned ratio	CLUSTER BY (date_key, category)
SELECT * from BI	reads all columns	project only needed columns
Full nightly rebuild	large task credits	incremental MERGE on watermark
No date filter	full scans	enforce a date predicate / default range
Result: regression	bill doubled	all four compound

Why this works — concept by concept:

• Segment the bill by model — query_history grouped by table pattern shows whether the OBT or the star drives scan bytes, turning "the bill doubled" into "OBT queries scan 3× the bytes" — a diagnosable fact.
• Clustering audit — SYSTEM$CLUSTERING_INFORMATION reveals whether the OBT actually prunes. A wide table with poor clustering full-scans on every query, which is the single most common OBT cost regression.
• Full-scan hunt — filtering for partitions_scanned ≈ partitions_total finds the queries with no selective predicate; these are the ones a star would have pruned via a dimension filter and the OBT does not without clustering.
• SELECT * detection — column pruning is the OBT's cost defense; a BI tool doing SELECT * reads all 40 columns and throws that defense away. Projecting only needed columns restores it.
• Cost — the diagnosis is a few metadata queries (near-free) that convert a religious argument into a fix list: cluster the table, project columns, switch to incremental refresh, enforce a date filter. The OBT is not inherently expensive; an unclustered, SELECT-'d, fully-rebuilt* OBT is. Fixing the four defaults restores the expected saving.

SQL
Topic — sql
SQL problems on scan cost and query tuning

Practice →

Design Topic — design Design problems on warehouse cost trade-offs

Practice →

---

5. Hybrid patterns & the modern-stack verdict

The modern answer is not OBT-or-star — it is a conformed star core with one big table marts published on top, orchestrated by dbt

The mental model in one line: the modern data stack resolves the OBT-vs-star debate by refusing to choose — you model a conformed star core (dimensions + facts) once as the governed source of truth, then publish denormalized one-big-table marts on top of it per use case, using dbt's staging → intermediate → marts layering so the star gives you maintainability and flexibility while each OBT mart gives its dashboards single-scan performance and BI ergonomics. The verdict senior interviewers want is this hybrid, defended by the four axes, not a tribal allegiance to Kimball or to "just flatten it."

The dbt layering that makes the hybrid work.

• Staging. One model per source table, lightly cleaned and renamed — the raw-to-typed layer. stg_orders, stg_products. No business logic, no joins.
• Intermediate. Reusable joins and calculations — the conformed dimensions and the atomic facts are built here. dim_customer, dim_product, fact_sales. This is the star core.
• Marts. The consumption layer. Some marts are the star tables themselves (for ad-hoc analysis); others are denormalized OBTs (obt_sales, finance_obt) built by joining the intermediate star. This is where denormalization happens — late, and per use case.
• The discipline. Denormalize at the marts layer only. Never let a source or staging model be a wide table; the width belongs at the serving edge, built from a normalized core.

Wide dimensions and OBT-per-mart.

• Wide dimensions. Even inside a star, dimensions are denormalized (a snowflake schema normalizes them further; a star deliberately does not). dim_product inlines category, brand, and subcategory rather than splitting them into dim_category. This is "a little OBT inside every dimension."
• One big table per data mart. Each business domain (finance, marketing, product) gets its own OBT shaped to its dashboards, all built from the same conformed core, so cross-mart numbers still tie out because they share dimensions.
• The governance win. The conformed core is the single place metrics are defined; marts are derived. Two OBTs built from the same core cannot disagree on what a customer segment is.

Slowly-changing dimensions inside an OBT.

• The problem. An OBT inlines dimension attributes at build time; if an attribute changes, historical rows are frozen with the old value (SCD type-1 behavior by default).
• SCD type-2 in the OBT. To preserve history, the OBT row must capture the attribute value as of the fact's date. You join the fact to the SCD-2 dimension on both the surrogate key and the validity window (fact.date BETWEEN dim.valid_from AND dim.valid_to), so each fact row inlines the attribute that was current when the event happened.
• The alternative. Keep SCD history in the star dimension and rebuild the OBT so historical rows reflect the point-in-time attribute — the OBT stores the "as-was" value, the star stores the full history.

When each wins — the verdict.

• OBT wins for a single-grain, fixed-query, BI-facing dashboard dataset over stable dimensions.
• Star wins for multi-fact warehouses, ad-hoc/self-service exploration, and frequently-corrected dimensions.
• Both (hybrid) wins for essentially every serious warehouse: a conformed star core for governance and flexibility, OBT marts for dashboard performance.

Common interview probes on the hybrid.

• "How do you structure a dbt project for this?" — staging → intermediate (star core) → marts (some OBT).
• "How do you handle SCD-2 in an OBT?" — join on the validity window to inline the point-in-time attribute.
• "Do you build one OBT or many?" — one per data mart / domain, all from the same conformed core.
• "What's the verdict?" — hybrid: star core + OBT marts, chosen per use case by the four axes.

Worked example — a hybrid mart design (star core + OBT marts)

Detailed explanation. The hybrid: a conformed star core in the intermediate layer feeds two domain OBTs in the marts layer, so finance and marketing each get a wide table shaped to their dashboards while sharing the same dimensions. Walk through the layering.

• Core. dim_customer, dim_date, dim_product, fact_sales, fact_spend (conformed).
• Mart 1. finance_obt — sales revenue by account/segment/month, single scan.
• Mart 2. marketing_obt — spend and attributed revenue by campaign/channel/month.
• The tie-out. Both marts use the same dim_customer and dim_date, so "revenue" means the same thing in both.

Question. Design the two OBT marts on top of one conformed star core and show why their shared metrics agree.

Input.

Layer	Model	Kind
intermediate	dim_customer, dim_date, dim_product	conformed star core
intermediate	fact_sales, fact_spend	atomic facts
marts	finance_obt	OBT (sales grain)
marts	marketing_obt	OBT (campaign-day grain)

Code.

-- marts/finance_obt.sql — one big table for finance dashboards
CREATE OR REPLACE TABLE finance_obt
CLUSTER BY (year_month, customer_segment) AS
SELECT f.sale_id,
       d.year_month,
       c.customer_segment,
       c.account_type,
       p.category,
       f.amount_cents
FROM   fact_sales   f
JOIN   dim_date     d ON d.date_key     = f.date_key
JOIN   dim_customer c ON c.customer_key = f.customer_key
JOIN   dim_product  p ON p.product_key  = f.product_key;

-- marts/marketing_obt.sql — one big table for marketing dashboards
CREATE OR REPLACE TABLE marketing_obt
CLUSTER BY (year_month, channel) AS
SELECT s.spend_id,
       d.year_month,
       c.customer_segment,        -- SAME conformed dim_customer as finance_obt
       s.channel,
       s.campaign,
       s.spend_cents,
       s.attributed_revenue_cents
FROM   fact_spend   s
JOIN   dim_date     d ON d.date_key     = s.date_key
JOIN   dim_customer c ON c.customer_key = s.customer_key;

-- Because both marts share dim_customer + dim_date, this cross-mart
-- comparison ties out: revenue (finance) vs attributed revenue (marketing)
SELECT f.year_month,
       f.customer_segment,
       SUM(f.amount_cents)                AS actual_revenue,
       MAX(m.attributed_revenue)          AS attributed_revenue
FROM   (SELECT year_month, customer_segment, SUM(amount_cents) AS amount_cents
        FROM finance_obt GROUP BY 1, 2) f
JOIN   (SELECT year_month, customer_segment, SUM(attributed_revenue_cents) AS attributed_revenue
        FROM marketing_obt GROUP BY 1, 2) m
  ON   m.year_month = f.year_month AND m.customer_segment = f.customer_segment
GROUP  BY f.year_month, f.customer_segment;

Step-by-step explanation.

1. The conformed core (dim_customer, dim_date, dim_product, fact_sales, fact_spend) is built once in dbt's intermediate layer. This is the governed star — the single definition of every dimension and metric.
2. finance_obt denormalizes fact_sales with the dimensions finance dashboards need (year_month, customer_segment, account_type, category), clustered for finance's access pattern. It is a single-scan wide table.
3. marketing_obt denormalizes fact_spend with marketing's dimensions (channel, campaign) but reuses the same dim_customer and dim_date. This is the crucial move: the two OBTs are different shapes but share the conformed dimensions.
4. Because customer_segment and year_month come from the same conformed dimensions in both marts, the cross-mart query comparing actual revenue (finance) to attributed revenue (marketing) is coherent — the segments and periods line up exactly.
5. Had each mart built its own private customer grouping, the comparison would silently break. The hybrid gets both the OBT's per-dashboard performance and the star's cross-domain coherence, because denormalization happens late (marts) from a shared core (intermediate).

Output.

year_month	customer_segment	actual_revenue	attributed_revenue
2026-05	Premium	90,700,000	61,200,000
2026-05	Standard	52,100,000	40,900,000
2026-06	Premium	94,300,000	63,800,000
2026-06	Standard	54,600,000	42,100,000

Rule of thumb. Build the conformed star core once, then publish one OBT per data mart on top of it. Denormalize late (marts layer) from a shared core so every mart gets single-scan performance while cross-mart metrics tie out by construction. This is the hybrid verdict in one sentence.

Worked example — a dbt OBT model with incremental refresh

Detailed explanation. In dbt, the OBT mart is an incremental model that pre-joins the star core and appends only new fact rows. Walk through the dbt model config and SQL that make the wide table refresh cheaply.

• Materialization. incremental — dbt appends new rows instead of rebuilding.
• Unique key. sale_id — dedupes on merge.
• Incremental predicate. Only rows newer than the max already loaded.
• Cluster. On the dashboard filter/group columns.

Question. Write the dbt incremental OBT model that builds obt_sales from the conformed star.

Input.

Config	Value
materialized	incremental
unique_key	sale_id
incremental_strategy	merge
cluster_by	date_key, category

Code.

-- models/marts/obt_sales.sql
{{
  config(
    materialized='incremental',
    unique_key='sale_id',
    incremental_strategy='merge',
    cluster_by=['date_key', 'category']
  )
}}

SELECT
    f.sale_id,
    f.date_key,
    f.product_key,
    f.customer_key,
    f.store_key,
    d.year_month,
    d.calendar_date,
    p.category,
    p.brand,
    c.customer_segment,
    c.loyalty_tier,
    s.store_region,
    f.quantity,
    f.amount_cents
FROM   {{ ref('fact_sales') }}   f
JOIN   {{ ref('dim_date') }}     d ON d.date_key     = f.date_key
JOIN   {{ ref('dim_product') }}  p ON p.product_key  = f.product_key
JOIN   {{ ref('dim_customer') }} c ON c.customer_key = f.customer_key
JOIN   {{ ref('dim_store') }}    s ON s.store_key    = f.store_key

{% if is_incremental() %}
  -- On incremental runs, only pull facts newer than what we already loaded
  WHERE f.sale_id > (SELECT COALESCE(MAX(sale_id), 0) FROM {{ this }})
{% endif %}

Step-by-step explanation.

1. The config() block declares the model incremental with merge strategy and unique_key='sale_id', so dbt generates a MERGE that inserts new rows and updates matched ones rather than rebuilding the whole table each run.
2. The SELECT is the big JOIN from section 3, but every source is a {{ ref(...) }} to the conformed star models — dbt resolves these to the intermediate-layer tables and builds the dependency DAG so the star is always built before the OBT.
3. The {% if is_incremental() %} block adds the watermark predicate f.sale_id > (SELECT MAX(sale_id) FROM {{ this }}) only on incremental runs. On the first run (full refresh) the predicate is omitted and the whole star is materialized; on subsequent runs only new facts are joined and appended.
4. cluster_by=['date_key', 'category'] propagates to the warehouse's clustering so the resulting OBT prunes on the dashboard filter/group columns — the same clustering discipline from section 4, expressed declaratively.
5. This single model file is the entire OBT pipeline: dbt handles the DAG (star before OBT), the incremental logic, the MERGE, and the clustering. A dbt run --select obt_sales on a schedule keeps the wide table fresh at O(new rows) cost.

Output.

dbt run	Behavior	Rows processed
first (full)	materialize whole star join	all fact rows
incremental	MERGE new facts only	new rows since max(sale_id)
--full-refresh	rebuild from scratch	all fact rows
dimension change	re-run affected mart	patched via separate model

Rule of thumb. Model the OBT as a dbt incremental + merge model that ref()s the conformed star, with cluster_by on the dashboard columns and an is_incremental() watermark predicate. dbt then owns the DAG, the incremental refresh, and the clustering — the OBT becomes one declarative file built on top of the governed core.

Worked example — SCD type-2 handling inside the OBT

Detailed explanation. By default an OBT inlines the current dimension value, losing history (SCD type-1). To preserve point-in-time truth, join the fact to an SCD-2 dimension on the validity window so each fact row inlines the attribute that was current when the event happened. Walk through it.

• The SCD-2 dimension. dim_customer with valid_from, valid_to, is_current.
• The default (wrong for history). Join on customer_key and is_current → every historical fact gets today's segment.
• The fix. Join on customer_key and fact.date BETWEEN valid_from AND valid_to → each fact gets its as-of segment.

Question. Build the OBT so each fact row inlines the customer segment that was current on the sale date, not today's segment.

Input.

Component	Value
dim_customer	SCD-2 (valid_from, valid_to, is_current)
fact_sales	has calendar_date
Goal	inline as-of-sale segment
Join key	customer natural id + validity window

Code.

-- SCD-2 dimension: one row per (customer, validity window)
-- customer_id 'C-77' was 'Standard' before 2026-04, 'Premium' after.
-- valid_from   valid_to     customer_id  customer_segment  is_current
-- 2025-01-01   2026-03-31   C-77         Standard          false
-- 2026-04-01   9999-12-31   C-77         Premium           true

-- WRONG: joins on is_current → every historical sale gets 'Premium'
-- JOIN dim_customer c ON c.customer_id = f.customer_id AND c.is_current

-- RIGHT: join on the validity window → as-of-sale segment
CREATE OR REPLACE TABLE obt_sales_scd2
CLUSTER BY (date_key, customer_segment) AS
SELECT
    f.sale_id,
    f.date_key,
    f.calendar_date,
    f.customer_id,
    c.customer_segment,          -- the segment AS OF the sale date
    f.amount_cents
FROM   fact_sales f
JOIN   dim_customer c
  ON   c.customer_id  = f.customer_id
 AND   f.calendar_date >= c.valid_from
 AND   f.calendar_date <  c.valid_to;   -- point-in-time match

Step-by-step explanation.

1. The SCD-2 dim_customer stores one row per (customer, validity window). Customer C-77 has two rows: Standard valid through 2026-03-31, and Premium valid from 2026-04-01. The history is fully preserved in the dimension.
2. The wrong join (c.is_current) attaches today's segment to every historical sale, so a January sale by C-77 would be labeled Premium even though the customer was Standard then. This retroactively rewrites history — the classic SCD-1-in-an-OBT bug.
3. The right join matches on customer_id and f.calendar_date >= c.valid_from AND f.calendar_date < c.valid_to. This selects the dimension row that was valid on the sale date, so a January sale inlines Standard and an April sale inlines Premium.
4. The OBT row now carries the point-in-time attribute — the "as-was" value frozen correctly. This is how you keep SCD-2 semantics in a denormalized table: the validity-window join bakes the historically-correct value into each row at build time.
5. On rebuild, the same window join reproduces the correct history, so the OBT stays consistent even as the dimension accrues new SCD-2 rows. The star holds the full history; the OBT holds the as-of-event snapshot — both are correct, for their purpose.

Output.

sale_id	calendar_date	customer_id	customer_segment (as-of)
5001	2026-01-15	C-77	Standard
5002	2026-03-20	C-77	Standard
5003	2026-04-10	C-77	Premium
5004	2026-06-02	C-77	Premium

Rule of thumb. To keep SCD-2 semantics in an OBT, join the fact to the dimension on the natural key and the validity window (fact.date BETWEEN valid_from AND valid_to), never on is_current. This inlines the as-of-event attribute so historical rows keep their true past values instead of being retroactively rewritten with today's.

Senior interview question on designing a reporting layer

A senior interviewer might ask: "Design the reporting layer for a company with three data domains — sales, marketing, and product analytics — each with its own BI dashboards but needing consistent shared metrics (customer, date, revenue). Would you use OBT, star, or both? Lay out the dbt project structure, where denormalization happens, and how you guarantee the three domains' numbers tie out."

Solution Using a conformed star core with per-domain OBT marts in a layered dbt project

dbt project structure — layered, denormalize late
==================================================

models/
  staging/                      # 1:1 with sources, cleaned + typed
    stg_sales__orders.sql
    stg_marketing__spend.sql
    stg_product__events.sql

  intermediate/                 # the CONFORMED STAR CORE (governed)
    dim_customer.sql            # SCD-2, shared by ALL domains
    dim_date.sql                # one date spine for everyone
    dim_product.sql
    fact_sales.sql              # grain: order line
    fact_spend.sql              # grain: campaign-day
    fact_product_events.sql     # grain: user-event

  marts/                        # DENORMALIZED OBTs, one per domain
    sales/
      obt_sales.sql             # incremental, cluster by (date, category)
    marketing/
      obt_marketing.sql         # incremental, cluster by (date, channel)
    product/
      obt_product_usage.sql     # incremental, cluster by (date, feature)

Rule: dimensions are conformed in intermediate/ ONCE.
      Every marts/ OBT ref()s the SAME dim_customer + dim_date.
      Denormalization happens ONLY in marts/.

-- intermediate/dim_customer.sql — the single conformed customer dimension
-- (SCD-2; every domain's OBT joins THIS, guaranteeing one definition)
{{ config(materialized='table') }}
SELECT customer_key, customer_id, customer_segment, loyalty_tier,
       valid_from, valid_to, is_current
FROM   {{ ref('stg_sales__customers') }}
-- ... SCD-2 logic ...

-- marts/marketing/obt_marketing.sql — domain OBT built from the shared core
{{ config(materialized='incremental', unique_key='spend_id',
          incremental_strategy='merge', cluster_by=['date_key','channel']) }}
SELECT s.spend_id, d.year_month, d.date_key,
       c.customer_segment,          -- SAME dim_customer as sales + product
       s.channel, s.campaign, s.spend_cents, s.attributed_revenue_cents
FROM   {{ ref('fact_spend') }}   s
JOIN   {{ ref('dim_date') }}     d ON d.date_key     = s.date_key
JOIN   {{ ref('dim_customer') }} c ON c.customer_key = s.customer_key
{% if is_incremental() %}
  WHERE s.spend_id > (SELECT COALESCE(MAX(spend_id),0) FROM {{ this }})
{% endif %}

Step-by-step trace.

Layer	Contents	Denormalized?
staging	1:1 cleaned sources	no
intermediate	conformed dims + atomic facts (the star core)	no (normalized star)
marts/sales	obt_sales	yes (OBT)
marts/marketing	obt_marketing	yes (OBT)
marts/product	obt_product_usage	yes (OBT)
shared dims	dim_customer, dim_date ref()'d by all marts	conformed

Each domain gets its own single-scan OBT tuned to its dashboards, but all three OBTs ref() the identical dim_customer and dim_date from the intermediate layer. Because the conformed dimensions are defined exactly once, "revenue by customer segment in May" is the same number whether sales, marketing, or product analytics computes it — the numbers tie out by construction, and denormalization is confined to the marts layer where it buys dashboard performance without costing governance.

Output:

Domain	Serving object	Shared dims	Metric consistency
Sales	obt_sales	dim_customer, dim_date	tie out
Marketing	obt_marketing	dim_customer, dim_date	tie out
Product	obt_product_usage	dim_customer, dim_date	tie out
Governance	intermediate star core	single definition	source of truth

Why this works — concept by concept:

• Conformed star core in intermediate/ — the dimensions and facts are modeled once, normalized, as the governed source of truth. This is where metric definitions live and where corrections are localized (the star's maintainability + flexibility axes).
• Denormalize only in marts/ — each domain OBT is a late, derived projection of the core. Width lives at the serving edge, so dashboards get single-scan performance (the OBT's query-performance axis) without polluting the core.
• Shared dim ref()s — every OBT ref()s the same dim_customer and dim_date, so three domains cannot drift private definitions. This is the hybrid's decisive property: OBT performance and star coherence.
• Incremental + cluster per mart — each OBT refreshes at O(new rows) and clusters on its own dashboard columns, so each domain optimizes its access pattern independently while sharing the core.
• Cost — one normalized core plus N derived OBTs (a few GB each, compressed) and N incremental refresh jobs. Compared to N independent flat tables (which drift and disagree) or a pure star (which makes every dashboard join), the hybrid pays a small, bounded materialization cost for both governance and performance. The verdict is not OBT-or-star; it is star-core-plus-OBT-marts, and that is the answer senior interviews reward.

Design
Topic — design
Design problems on layered reporting architectures

Practice →

Dimensional Modeling
Topic — dimensional-modeling
Dimensional modeling — hybrid mart and SCD problems

Practice →

---

Cheat sheet — OBT vs star recipes

• When OBT / when star. Pick a one big table for a single-grain, fixed-query, BI-facing dashboard dataset over stable dimensions — the win is zero-join single-scan performance and BI-tool ergonomics. Pick a star schema for multi-fact warehouses, ad-hoc/self-service exploration, and frequently-corrected dimensions — the win is localized fixes and flexibility. Pick the hybrid (star core + OBT marts) for essentially every serious warehouse.
• The four axes. Query performance (OBT: single scan, no join; star: fact + joins), storage (star: attributes once; OBT: inlined but compressed), maintainability (star: fix one dim row; OBT: rebuild/patch), flexibility (star: join any conformed dim; OBT: shaped for known queries). Argue every OBT-vs-star decision across all four, never on speed alone.
• Grain discipline. Declare the grain of every fact in one sentence before writing DDL, and choose the most atomic grain your budget allows — you can always roll up, never drill below your grain. The OBT inherits the fact's grain; a line-grain fact yields a line-grain OBT.
• Conformed dimensions. Build each shared dimension once (dim_date, dim_customer) and join it from every fact and every OBT mart. Conforming is what makes cross-process and cross-mart metrics tie out; draw the Kimball bus matrix (processes × dimensions) before building the first fact.
• Columnar math. Storage duplication in an OBT is real but small — low-cardinality attributes dictionary-encode 20–100×, so an inlined category costs ~hundreds of MB, not the naive tens of GB. Storage almost never tips the decision on modern pricing; compute does.
• Clustering / partitioning. Cluster/partition every OBT on its most-filtered column (usually date) first, then a common group-by column. Snowflake CLUSTER BY, BigQuery PARTITION BY DATE(...) CLUSTER BY ... (partitioning is a direct bytes-scanned dollar saving), Databricks liquid clustering / ZORDER + OPTIMIZE. An unclustered OBT is slower than the star it replaced.
• Storage vs compute economics. The OBT-vs-star cost decision is a compute-amortization problem: OBT wins above a crossover query volume (build_cost / per_query_saving); below it, the star's build-free model is cheaper. Extra OBT storage is pennies; the deciding number is queries/day times join-cost-saved versus the recurring build cost.
• Measure, don't assert. Never claim OBT-or-star is cheaper — run the same logical query both ways and read bytes_scanned / partitions_scanned from the warehouse's query history. Broad group-bys over inlined low-cardinality attributes favor OBT; highly selective filters on high-cardinality dimensions can favor the star's bloom-filter semi-join pruning.
• dbt staging → intermediate → marts. Staging = 1:1 cleaned sources (no joins); intermediate = the conformed star core (dims + atomic facts); marts = the consumption layer where some models are denormalized OBTs. Denormalize late, at the marts layer only, built from a normalized core.
• SCD-in-OBT. To preserve history in a wide table, join the fact to the SCD-2 dimension on the natural key and the validity window (fact.date BETWEEN valid_from AND valid_to), never on is_current — this inlines the as-of-event attribute so historical rows keep their true past values instead of being rewritten with today's.
• Rebuild vs incremental. Never full-rebuild a billion-row OBT on every refresh. Append new facts via a watermark MERGE (O(new rows)) and patch dimension changes via a keyed MERGE on the changed keys (O(changed rows)) — full rebuilds are for schema changes only.
• Diagnosing an OBT cost regression. If the bill jumps after flattening, the usual causes are: the OBT was left unclustered (full scans), analysts run SELECT * (defeats column pruning), and the refresh is a nightly full rebuild (not incremental). Segment query_history by table, audit SYSTEM$CLUSTERING_INFORMATION, hunt partitions_scanned ≈ total, and fix all three defaults.
• Migration cost between models. Star → OBT marts: ~1–2 sprints (build models + cluster + validate tie-out), no source change. Adding a new inlined attribute to an OBT: a rebuild or keyed patch. Splitting a monster dimension out of an OBT into a star join: a re-model. Choose the serving model deliberately; the migration cost is real but the star core makes it reversible.

Frequently asked questions

What is a one big table (OBT)?

A one big table is a single wide, fully denormalized table produced by pre-joining a fact table to all of its dimensions at build time, so that every descriptive attribute — category, customer segment, store region, calendar month — is physically inlined next to the numeric measures on every fact row. The query layer then touches exactly one object and runs zero joins, which is the pattern's headline advantage: predictable single-scan performance and clean, join-free SQL that BI tools generate well. The cost you pay for that simplicity is storage duplication (every attribute repeated per row, though columnar compression blunts it heavily), update anomalies (a changed attribute is wrong on frozen historical rows until rebuild), and a rebuild-on-change operational burden. An OBT is almost always built from a conformed star schema — you model the dimensions properly, then denormalize into the wide serving table — so it is a projection of the star, not a replacement for it.

OBT vs star schema — which should I use?

The honest answer is "it depends on four axes — query performance, storage, maintainability, and flexibility — and usually the right answer is both." A star schema wins maintainability (fix a dimension in one row and every query sees it), flexibility (answer unplanned questions by joining an existing conformed dimension), and raw storage (each attribute stored once). A one big table wins query performance (single scan, no joins) and BI-tool ergonomics (no join graph to understand). Pick a pure OBT for a single-grain, fixed-query dashboard dataset over stable dimensions; pick a pure star for multi-fact warehouses and open-ended self-service exploration. For essentially every serious warehouse the verdict is the hybrid: model a conformed star core once as the governed source of truth, then publish OBT marts on top per use case, so you get the star's coherence and the OBT's dashboard performance simultaneously.

Is the star schema obsolete on a columnar warehouse?

No — the star schema is not obsolete, but its historical justification is weaker than it was on row stores. The star was invented partly to avoid duplicating expensive storage and to avoid costly disk-bound joins; columnar warehouses made storage cheap, made low-cardinality attributes compress 20–100× via dictionary encoding, and made joins against broadcast dimensions fast — so "always normalize into a star" is no longer automatically correct. What the star still provides, and what columnar economics did not erase, is maintainability (localized dimension fixes), flexibility (unanticipated joins), and governance (conformed dimensions as a single metric definition). The modern practice is to keep the star as the conformed core for those properties and denormalize into OBT marts at the serving edge for performance — the star is demoted from "the only model" to "the governed core," not eliminated.

Does an OBT hurt storage cost?

An OBT is larger on disk than the equivalent star because it inlines every dimension attribute on every fact row, but the increase is far smaller than the naive intuition suggests and rarely tips the decision. Low-cardinality attributes — a category with 50 distinct values, a region with a dozen — dictionary-encode to a few bits per row plus a tiny dictionary, and run-length encoding on a clustered table collapses long repeats further, so an inlined column that looks like tens of GB uncompressed is typically hundreds of MB compressed. High-cardinality free-text attributes duplicate less efficiently and are the ones to be careful about inlining. On modern object-storage pricing (cents per GB-month) the extra OBT storage is usually pennies, while the deciding costs are compute (per-query join saving vs recurring build) — so storage is the axis columnar warehouses made least important.

How do you handle slowly-changing dimensions in an OBT?

Because an OBT inlines dimension attributes at build time, its default behavior is SCD type-1 — historical rows silently get whatever the attribute is at rebuild time, which rewrites the past. To preserve history (SCD type-2), the fix is to keep the SCD-2 validity window in the star dimension (valid_from, valid_to, is_current) and join the fact to it on the natural key and the window — fact.event_date >= dim.valid_from AND fact.event_date < dim.valid_to — so each fact row inlines the attribute that was current when the event happened, not today's value. The wrong join is on is_current, which attaches today's segment to every historical fact. Done correctly, the star holds the full dimension history and the OBT holds the correct point-in-time ("as-was") snapshot per fact row, and a rebuild reproduces that history deterministically.

Which model is best for Snowflake / BigQuery?

Both warehouses reward the same hybrid, with warehouse-specific tuning. On Snowflake, the star and the OBT both work well; the key is to CLUSTER BY the OBT on its most-filtered column (date) then a group-by column (category), monitor clustering with SYSTEM$CLUSTERING_INFORMATION, and refresh incrementally rather than full-rebuilding — an unclustered OBT full-scans and can cost more than the star. On BigQuery, on-demand queries are billed by bytes scanned, so PARTITION BY DATE(...) plus CLUSTER BY on the OBT is a direct dollar saving: partition pruning reads only the matching days, and column pruning reads only the selected columns. In both, the recommended shape is a conformed star core (dimensions + atomic facts) built in dbt's intermediate layer, with denormalized OBT marts in the marts layer clustered/partitioned to each dashboard's access pattern — you measure bytes_scanned per query to confirm the OBT actually beats the star for your specific workload.

Practice on PipeCode

• Drill the dimensional-modeling practice library → for the star-schema, grain, conformed-dimension, and one-big-table design problems senior interviewers love.
• Rehearse on the SQL practice library → for the fact-dimension aggregation, scan-cost, and query-tuning patterns that decide OBT-vs-star in practice.
• Sharpen join-elimination intuition on the joins practice library → for the pre-join materialization and denormalization problems at the heart of the wide-table pattern.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the four-axis decision framework against real graded inputs.

Lock in OBT-vs-star decision muscle memory

Docs explain the models. PipeCode drills explain the decision — when a one big table's single scan beats a star's joins, when denormalization's rebuild cost bites, when columnar compression makes storage duplication a non-issue, when the hybrid star-core-plus-OBT-marts pattern earns its place. Pipecode.ai is Leetcode for Data Engineering — model-first practice tuned for the production trade-offs analytics engineers actually defend in interviews.

Practice dimensional modeling problems →
Practice design problems →