Building the Base Layer: Star Schema, Pre-Computed Flags, and Why Dimension Attributes Don't Belong in Your Fact Table

A base layer that survives contact with four teams has six tables: one fact, three dims, two registries. The fact carries flags. The dims carry attributes. This post is the build: the DDL, the partition spec, the four-phase rollout, and the common mistakes at this scale.

What goes in the fact, what goes in the dim

This is the most important decision in the entire build. Get it wrong and the base layer collapses to a fat-fact-table anti-pattern that re-creates the original problem.

The rule is Kimball’s, and it has not aged: dimension attributes do not belong in fact tables.

A dimension attribute is anything that describes the thing a fact is about, but isn’t an immutable property of the event the fact records. Merchant name describes the merchant; it is not a property of the transaction. The transaction’s merchant_id is what’s immutable. The merchant_name is current-state; it can change if the merchant rebrands.

The fact table records what happened at the moment it happened: the transaction ID, the merchant ID at that moment, the locality ID at that moment, the amount, the timestamp, the status. It does not record what those IDs meant. That meaning lives in the dim tables and is resolved at query time by joining.

Why this matters: if you bake the merchant name into the fact table, and the merchant later rebrands (or merges with another merchant, or gets re-categorized), you face a choice between:

• Updating millions of historical fact rows to reflect the new name (expensive and lossy — you’ve lost the old name)
• Leaving the old name in place (and now your queries return inconsistent names across time)
• Carrying both old and new names somehow (and now your fact table is a snapshot, not an event log)

None of these is what you want. A clean dim table makes a merchant rename a one-row update in dim_merchant — every query immediately sees the new name without any fact table modification.

The same logic applies to:

• Category names (categories get re-organized)
• Locality bracket classification (the “Top 100 / Top 100-200 / High Potential / Others” bucket assignments change quarterly)
• Mega-city groupings (“NCR” might come to include Greater Noida in 2026 that it didn’t include in 2024)
• Merchant priority / tier (a local merchant can be promoted to brand-priority)
• User source-of-acquisition (back-attributable to changing campaigns)
• Pro membership status (changes month to month)

All of these live in the dim. None of them live in the fact.

What’s actually in the fact

The fact table has exactly four kinds of columns:

1. Identity — primary key (txn_id), partition column (stored_at), timestamp at event time (redemption_datetime).
2. Foreign keys — merchant_id, user_id, locality_id, order_id. Pointers to dims. Never resolved into names inside the fact.
3. Immutable classification at event time — product_type (delivery / voucher / pay / etc.), status, ondc_type. These are properties of the transaction itself, not properties of the merchant or user.
4. Raw measures — gmv, cashin, ctm, ctc, platform_fee, delivery_total_cost. Numbers. No team-specific adjustments, no normalization, no formula application.

And one fifth category — the one that makes the whole design work:

5. Pre-computed boolean flags — is_test_txn, is_cross_sell, is_b2b, is_refunded, is_delivered, is_platform_ondc. These encode the common classification decisions that multiple teams need to filter on. The flag is computed once during refresh by joining to the relevant source tables. Then each team’s query is just a WHERE clause on flags.

That’s it. Six categories. Nothing else.

In schema form:

-- base.fact_transactions (illustrative DDL)
CREATE TABLE base.fact_transactions (
  -- partition + cluster keys
  stored_at           DATE,
  txn_id              INT64,
  redemption_date     DATE,

  -- foreign keys to dim tables (current-state attributes live there)
  merchant_id         INT64,
  user_id             INT64,
  locality_id         INT64,
  order_id            INT64,         -- links to fact_orders
  rider_id            INT64,         -- delivery txns only

  -- immutable transaction classification
  product_type        STRING,
  status              STRING,        -- success / used / inactive / refunded / failure
  ondc_type           STRING,

  -- raw measures (no team-specific adjustments)
  gmv                 FLOAT64,
  cashin              FLOAT64,
  ctm                 FLOAT64,
  ctc                 FLOAT64,
  platform_fee        FLOAT64,

  -- delivery-specific (NULL for non-delivery)
  delivery_total_cost FLOAT64,
  delivery_provider   STRING,

  -- pre-computed boolean flags
  is_test_txn         BOOL,
  is_cross_sell       BOOL,
  is_b2b              BOOL,
  is_refunded         BOOL,
  is_delivered        BOOL,
  is_platform_ondc    BOOL,
  is_premium_user     BOOL
)
PARTITION BY stored_at
CLUSTER BY merchant_id, locality_id, product_type;

Each flag is computed once during the daily refresh, by joining to the source tables that determine its value (is_test_txn joins to transactions_silver.merchant for the test merchant list; is_cross_sell joins to the campaign tags table; is_b2b joins to the brand B2B txns table). The flag is then a simple BOOL on the fact table.

Each team’s query becomes a WHERE clause:

-- Finance: all txns, no cross_sell filter, refunds handled in formula
WHERE stored_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY)
  AND is_test_txn = FALSE

-- Growth: explicit definitional filters, all visible
WHERE stored_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND is_test_txn = FALSE
  AND is_cross_sell = FALSE
  AND ondc_type NOT IN ('seller_native', 'partner_a_buyer', 'partner_b_buyer', 'partner_a_saas')

-- Delivery ops: orders, not transactions
WHERE stored_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND is_test_txn = FALSE
  AND is_delivered = TRUE
  AND product_type IN ('delivery', 'delivery_fee')

-- Brands: brand merchants, PLT-ONDC only
WHERE stored_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND is_test_txn = FALSE
  AND is_cross_sell = FALSE
  AND is_b2b = FALSE
  AND m.merchant_priority = 1                        -- on the dim, not the fact
  AND (is_platform_ondc = TRUE OR ondc_type = 'non_ondc')

Four teams. Four WHERE clauses. The divergence is now visible — anyone can git diff the queries and see exactly why the numbers differ.

What’s in the dim

Dim tables are full-refreshed daily — WRITE_TRUNCATE. They hold current-state attributes for the entity:

-- dim_merchant (illustrative DDL)
CREATE OR REPLACE TABLE base.dim_merchant AS
SELECT
  m.merchant_id,
  m.merchant_name,
  m.parent_merchant_id,
  m.category_id,
  c.category_name,
  l.locality_id,
  l.locality_name,
  m.merchant_priority,
  m.highlight1,
  m.highlight3,
  CASE
    WHEN m.merchant_priority = 1 AND c.category_id IN (101, 103, 105)
      THEN 'Brand FnB'
    WHEN m.merchant_priority = 1 THEN 'Brand'
    WHEN c.category_id IN (101, 103, 105) THEN 'Local FnB'
    ELSE 'Local Other'
  END AS merchant_type_label,
  m.is_shadowed,
  m.is_closed,
  CASE
    WHEN m.campaign_tags LIKE '%test_merchant%'
      OR c.category_id = 102
      OR m.merchant_id IN (1001, 1002 /* known test list */)
    THEN TRUE
    ELSE FALSE
  END AS is_test_merchant,
  -- commercial attributes from a separate source schema
  p.active_package_type,
  s.merchant_segment,
  s.drr_rate
FROM bronze.merchant m
LEFT JOIN bronze.category c ON m.category_id = c.id
LEFT JOIN bronze.locality l ON m.locality_id = l.id
LEFT JOIN subscription.package p ON m.merchant_id = p.merchant_id AND p.is_active = TRUE
LEFT JOIN subscription.merchant_segment s ON m.merchant_id = s.merchant_id;

The full refresh is fast because the merchant table is small (a few million rows at most), and the joins are local. Run time is typically two minutes.

The dim is the single source of truth for “what is this merchant right now.” Every fact-table query joins to it. The dim is also the place where the is_test_merchant flag is computed — and that flag then becomes is_test_txn on the fact when the fact joins to the dim during its own refresh.

This centralization removes the most common cause of definition drift: the same classification logic copy-pasted into ten scripts. Instead of every analyst writing their own WHERE merchant_id NOT IN (long hardcoded list), there’s one place — dim_merchant.is_test_merchant — that everyone reads from.

The pre-computed boolean flag pattern

Pre-computed flags are the part of this design that punches above its weight. They are what makes the four-team divergence query-friendly instead of conversation-heavy.

A flag is a BOOL column on the fact table that encodes a single classification decision. Each flag has:

1. A name in present-tense affirmative form — is_test_txn, not not_excluded or excluded_flag. Reading queries should be easy.
2. A single computational source — is_test_txn is computed from dim_merchant.is_test_merchant and the transaction’s own properties. One source, one definition.
3. A documented owner — the team that owns the source data also owns the flag’s definition. If the definition needs to change, they own the change.

The flags typically built at this scale:

Each flag captures one and only one decision. Compound classification (“brand transaction” = merchant_priority = 1 AND voucher_source IN ('self', 'database') AND category_id != 102) is not a flag — it’s a WHERE clause that combines multiple flags + a dim attribute. Keep the flags atomic. Compound conditions belong in the team’s queries, not in the fact.

(The temptation when designing flags is to anticipate every query’s needs and bake in every combination. Resist this. Flags get more useful as they get more atomic. Compound flags become stale the moment one of their inputs changes; atomic flags survive every change.)

Partition and cluster strategy

The base layer is structurally fast because the fact tables are partitioned and clustered in a way that prunes most queries to a small subset of data.

For fact_transactions:

• Partition by stored_at (a DATE column derived from DATE(created_at, 'Asia/Kolkata')). Every query that filters on a date range — and every query does — prunes the partitions BigQuery scans.
• Cluster by merchant_id, locality_id, product_type (in that order). The three most common filters. Clustering reduces scan size by 5-50× depending on the query.

For fact_orders:

• Partition by stored_at.
• Cluster by delivery_provider, merchant_id, locality_id.

The partition key is the dominant cost driver. If the partition isn’t right, no amount of clustering will save you. Base layers partitioned by created_at timestamp (not date) lose most of the partition pruning benefit — a common pattern in environments that didn’t draw the partition spec carefully. The DATE_TRUNC(created_at, DAY) cast at refresh time is the move.

Pick cluster keys by running INFORMATION_SCHEMA.JOBS_BY_PROJECT against the existing analytics queries, looking at the top fifty most-frequent WHERE clauses, and picking the three columns that appear most often.

Retention is six months rolling for fact tables. Partition expiry is set to 180 days. This is the right horizon for the vast majority of operational analytics; longer-window analyses (year-over-year, multi-year cohort) get a separate fact_transactions_archive table or run on the raw CDC.

(Six months is a deliberate choice. Twelve months doubles the storage and doubles the scan size on common queries for marginal analytical value. Three months is too short for quarterly reports. Six is the local optimum.)

The four-phase rollout

The actual migration. The base layer doesn’t replace existing tables on day one. It coexists for a few months while teams migrate at their pace. Forcing a hard cut creates a political problem you don’t want.

Phase 1 — Dimensions (weeks 1-2)

Build the four dim tables. They are independent of each other and independent of the facts. The dims are the lowest-risk starting point — analysts can immediately benefit from a clean dim_merchant for their existing scripts even before the facts are live.

Communicate: “these tables exist now; use them when joining for merchant/locality attributes.”

Success criterion: five existing scripts updated to use the dim tables in week two.

Phase 2 — Facts (weeks 3-4)

Build fact_transactions and fact_orders. Validate against the existing canonical tables (the finance CM table, the engineering all-txn-data table). Run in parallel for two weeks.

Validation queries to run nightly during this phase:

-- Row count comparison
SELECT 'fact' AS source, stored_at, COUNT(*) AS n FROM base.fact_transactions
WHERE stored_at = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND is_test_txn = FALSE
GROUP BY stored_at
UNION ALL
SELECT 'existing', DATE(created_at, 'Asia/Kolkata'), COUNT(*) FROM existing_canonical_tbl
WHERE DATE(created_at, 'Asia/Kolkata') = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND is_test_order != 1
GROUP BY DATE(created_at, 'Asia/Kolkata');

-- Aggregate measure comparison
-- (GMV, cashin, total counts — within 0.1% tolerance)

If the validation passes for two weeks, the facts are declared ready.

Success criterion: row counts match existing canonical within 0.1% for the last 30 days; team representatives sign off on the documented intentional differences (cross_sell in/out, ONDC type filters).

Phase 3 — Team migration (weeks 5-8)

Migrate the highest-volume scripts. Prioritize by query volume × team impact:

• P1: Finance cashflow scripts (~40 scripts). High volume, finance signs off after parallel-run validation.
• P1: Growth retention reports (~25 scripts). High visibility.
• P2: Referral burn reports (~50 scripts). Mid-priority but easy migration.
• P2: Brands dashboards (~20 scripts).
• P3: Delivery CPO monitoring (~15 scripts).

For each script: replace raw table joins with FROM base.fact_transactions f JOIN base.dim_merchant m ON ..., add team-appropriate flag filters in WHERE, validate output matches previous output, keep old script as backup for two weeks, then delete.

Success criterion: scripts migrated own roughly 50% of the analytics team’s daily query volume.

Phase 4 — Domain marts (weeks 9-12, optional)

Once the base layer is stable, build team-specific mart tables. These are the only layer where denormalization is acceptable — they are team-owned, rebuilt daily, encode the team’s filters permanently, and serve as the fastest source for the team’s dashboards.

-- mart.finance_transactions: fact + dims + finance filters
CREATE OR REPLACE TABLE mart.finance_transactions
PARTITION BY stored_at
CLUSTER BY merchant_id, mega_city
AS
SELECT
  f.*,
  m.merchant_name, m.category_name, m.merchant_priority, m.merchant_group,
  l.mega_city, l.locality_bracket
FROM base.fact_transactions f
JOIN base.dim_merchant m ON f.merchant_id = m.merchant_id
JOIN base.dim_locality l ON f.locality_id = l.locality_id
WHERE f.stored_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY)
  AND f.is_test_txn = FALSE;
-- (finance includes cross_sell, all ONDC types — no further filters)

This is the place where each team’s view of reality lives, permanently. Other teams should not query the finance mart; they have their own.

Decommissioning the redundancy

The last step. Once the marts are stable and the team scripts are off the legacy canonical tables, the old tables can be deprecated. The typical sequence:

• Freeze the legacy table writers (move to “paused” status, don’t delete).
• Wait 30 days. If nothing breaks, the table is truly dead.
• Document the redirect: “the canonical replacement for legacy.transaction_table is mart.finance_transactions. Migration guide here.”
• After another 30 days, delete the data, keep the empty table with a redirect comment for another quarter.
• After that, drop the table.

This is the part of the work that doesn’t get written up because it’s not glamorous. It is also the part that determines whether the base layer truly displaces the original problem, or just sits alongside it forever as one more table. Decommission the redundancy. Otherwise you have seven “base-ish” tables instead of three.

A short note on cost impact

Order-of-magnitude impact for a workload of this shape:

• Raw-table query volume: roughly 60% reduction in the typical environment — from thousands of references down to a few hundred, as scripts migrate to fact_transactions (illustrative, drawn from the shape of environments at this scale).
• Wallet-table joins (the most expensive per-query operation): roughly 90% reduction once Phase 3 is complete.
• BigQuery scan-hour reduction: 40-60% on the affected queries, driven by partition pruning + clustering on the base layer.

These are typical numbers for environments where the original problem was uncontrolled raw-table joins. If your environment has a different shape (lots of large-scan single-table reports, very wide tables, etc.) the savings profile shifts.

Where to go from here

You now have the full base-layer build. Six tables, four dims and two facts, daily refresh, 6-month rolling fact retention, partition + cluster strategy, four-phase rollout, redundancy decommission.

If you’d rather build it yourself, this two-post series is the runbook. The hard part is not the SQL. The hard part is the political work of agreeing that the four teams produce four different numbers on purpose, and that the base layer’s job is to make the difference visible, not to erase it. Once the team agrees on that, the rest is implementation.