You ask for CAC in the board meeting and get two answers. Marketing shows one number from HubSpot and ad platforms. Finance shows another from the ledger. Product has a third view because free-to-paid conversions don't line up with campaign attribution. Then someone opens a dashboard labeled “source of truth,” and nobody trusts it.
That usually isn't a dashboard problem. It's a modeling problem.
In practice, data modeling in a data warehouse decides whether MRR, churn, CAC, and LTV behave like audited business metrics or like arguments waiting to happen. If the warehouse stores events at the wrong grain, mixes historical states with current states, or leaves business entities loosely defined, every downstream tool inherits the confusion. Better charts won't fix that. Cleaner SQL alone won't fix it either.
A good model gives your warehouse a job description. It defines what a customer is, what a subscription is, when revenue is recognized for reporting, and how those entities connect. Once that structure is clear, a semantic layer can serve consistent answers instead of forcing every analyst to reinvent the same metric.
If you're already debugging conflicting reports, it helps to start with the underlying data quality metrics that reveal trust gaps. But quality checks only tell you something is wrong. Modeling is what makes the numbers line up in the first place.
Table of Contents
- Why Your Metrics Are Conflicting and How to Fix It
- The Building Blocks of a Warehouse Model
- Star vs Snowflake Which Schema Is Right for You
- Real-World Schema Examples for SaaS and E-commerce
- Connecting Your Model to a Semantic Layer
- Implementation and Key Design Decisions
- Common Data Modeling Pitfalls and How to Avoid Them
Why Your Metrics Are Conflicting and How to Fix It
Conflicting metrics usually come from one of three places. Teams define the same business concept differently. They pull from different systems. Or they query the same warehouse tables at different levels of detail.
A founder sees this when finance says churn rose because they count canceled contracts, while customer success says churn is stable because they count logo churn, and product says expansion offsets downgrades anyway. None of those views is necessarily wrong. They're just answering different questions from different models.
Your warehouse doesn't become a source of truth because you loaded data into Snowflake or BigQuery. It becomes one when the model makes the business logic explicit.
That's why data modeling in data warehouse work matters so much. The model is the blueprint that turns raw exports from Stripe, Salesforce, Shopify, HubSpot, and product events into a shared representation of the business. It tells everyone whether “customer” means account, billing entity, workspace, or person. It tells analysts whether MRR lives at the subscription line, invoice, or monthly snapshot level. It tells BI tools what can be joined safely and what can't.
When those definitions stay implicit, every dashboard author makes private assumptions. That's how one CAC excludes salaries, another includes agency spend, and a third counts only closed-won customers tied to paid channels.
The real fix is structural
The fix isn't “make people more careful.” The fix is to model the warehouse so core entities and business events are stable, documented, and reusable.
A strong warehouse model does three things:
- Defines business entities clearly: customer, subscription, order, invoice, plan, product.
- Separates events from context: transactions and usage events live differently from descriptive attributes.
- Creates a clean path to governed metrics: downstream semantic logic can reference the same trusted tables instead of rebuilding definitions.
Once the model is stable, reporting stops depending on whoever wrote the last SQL query.
The Building Blocks of a Warehouse Model
A warehouse is easier to understand if you treat it like a library. The tables are shelves. Columns are the labels on each book. Keys are the index cards that connect one shelf to another. Relationships are how you find all the books about the same subject without guessing.
To ground that idea, this visual is useful:
The practical value of modeling starts with one simple split. According to ER/Studio's overview of warehouse modeling, dimensional modeling became the dominant warehouse pattern because it formalized a practical split between facts and dimensions: facts hold measurable events, while dimensions hold descriptive context, making business questions easier to query at scale. That's why so many analytics teams still default to this structure.
Facts hold the event
A fact table stores something that happened. An order was placed. An invoice was paid. A user started a trial. A subscription renewed. Facts are where your measurable business activity lives.
Typical fact-table columns include:
- Identifiers: order_id, subscription_id, customer_id
- Dates: order_date, billing_period_start, event_timestamp
- Measures: amount, quantity, seats, discount, tax
- Foreign keys: links to customer, product, channel, or plan dimensions
A dimension table stores context. It describes the who, what, where, or which around the event. Customer attributes, plan names, product categories, regions, account owners, and acquisition channels usually belong here.
That split matters because people ask business questions in exactly that pattern. How much revenue came from enterprise customers? Which channels drove first purchases? Which plans have the highest downgrade rate?
Practical rule: If the row answers “something happened,” it probably belongs in a fact table. If it answers “what is this thing,” it probably belongs in a dimension.
Here's a short explainer if your team needs a visual walkthrough before getting hands-on:
Grain decides what you can answer
The most important modeling decision, often understated, is grain. Grain means the exact level of detail each row represents.
If your fact table stores one row per invoice, you can answer invoice questions well. If it stores one row per invoice line, you can answer product mix questions. If it stores one row per customer per month, monthly trend reporting gets easier, but line-level investigations get harder.
Founders often get burned: they ask for MRR by plan, downgrade reason, and acquisition source, but the warehouse stores only monthly account snapshots with no durable connection to plan history or original acquisition. The dashboard looks neat. The metric is fragile.
A useful way to think about grain:
- Fine grain: one row per event. Flexible, auditable, more joins.
- Curated grain: one row per business-ready unit such as customer-month. Easier reporting, less flexibility.
- Aggregate grain: one row per day, week, or month for speed. Great for dashboards, bad for root-cause analysis if used too early.
If the grain isn't explicit, your metric logic drifts fast. Two analysts can write valid SQL against the same table and still get different answers because they've grouped events differently or accidentally counted duplicates.
Star vs Snowflake Which Schema Is Right for You
Teams typically don't need a philosophical debate here. They need a default that keeps reporting simple and fast.
In a star schema, a central fact table connects directly to denormalized dimension tables. In a snowflake schema, those dimension tables are broken into additional related tables. Think of star as one clear hub with direct roads out to each business concept. Think of snowflake as a hub surrounded by side streets and extra intersections.
Why star is the default
For analytics teams, star usually wins because it reduces mental overhead. Analysts can join facts to dimensions directly, BI models stay easier to read, and metric logic doesn't disappear into a maze of lookup tables.
That simplicity is one reason the star schema remains the common recommendation in dimensional modeling practice, especially for business reporting and self-service analytics, as noted earlier in the discussion of dimensional modeling.
Here's the practical comparison.
| Aspect | Star Schema | Snowflake Schema |
|---|---|---|
| Query simplicity | Easier for analysts to understand and query | More complex because dimensions are split across more tables |
| Performance for reporting | Often better for common BI queries because fewer joins are needed | Can be slower or harder to optimize for routine analytics |
| Storage efficiency | Repeats some descriptive data | Reduces redundancy in dimension data |
| Maintenance | Easier for business users and analytics engineers to navigate | Harder to maintain when many dimension branches appear |
| Best fit | Dashboards, ad hoc analysis, semantic models | Cases where dimension normalization is important enough to justify extra complexity |
When snowflake earns its complexity
Snowflake isn't wrong. It's just easy to overuse.
If you have a large, complexly structured dimension with reusable sub-entities, normalizing part of it can make sense. Geography is a classic example. Product hierarchies can be another. The issue is that teams often normalize because it feels more “proper,” not because it helps the business answer questions better.
That trade-off matters. Finance rarely cares whether your country rolls up through a separate region table. They care whether net revenue retention and churn breakouts are stable month after month.
A good operating rule is simple:
- Start with star when your goal is reporting clarity.
- Use snowflake selectively when a dimension has real reuse or governance value.
- Avoid deep normalization in analyst-facing layers unless there's a concrete reason.
If your analysts need a data model diagram to answer “revenue by plan and cohort,” the schema is already doing too much.
Real-World Schema Examples for SaaS and E-commerce
Theory gets clearer once it touches money.
When founders talk about warehouse modeling, they usually don't mean “What schema pattern is academically elegant?” They mean “Can I trust the MRR chart?” or “Why did LTV change when nobody changed the business?”
This kind of architecture often starts from the same warehouse foundations used in a SaaS data warehouse setup for recurring revenue reporting.
A SaaS model for MRR
For SaaS, MRR goes wrong when teams mix billing records, subscription state, and account attributes in one catch-all table. The better pattern is to separate the recurring revenue event or snapshot from the customer and subscription context.
A practical SaaS warehouse model might include:
- fact_mrr_snapshot
- snapshot_month
- account_id
- subscription_id
- plan_id
- mrr_amount
- currency
- status
- dim_account
- account_id
- account_name
- signup_date
- segment
- acquisition_channel
- owner
- dim_subscription
- subscription_id
- account_id
- plan_id
- billing_interval
- start_date
- cancel_date
- dim_plan
- plan_id
- plan_name
- product_family
Why use a snapshot fact here? Because MRR is often reported as a monthly business state, not just a list of invoices. Finance and RevOps usually need a stable monthly view that reflects the account's recurring value at a given point in time.
That model lets you answer questions like:
- Which accounts expanded this month?
- How much MRR sits in each segment?
- What was MRR by acquisition channel at month-end?
It also keeps one dangerous mistake out of the way. You don't want the same table trying to represent current subscription status, invoice history, and monthly revenue posture all at once.
Model MRR at the level where the business reviews it. Keep the lower-level billing events available underneath for audit and reconciliation.
An e-commerce model for LTV
E-commerce LTV usually starts with orders, not subscriptions. The center of gravity is a transaction fact table at the order or order-line level, plus dimensions that let you segment customers and products consistently.
A straightforward model could look like this:
| Table | Purpose | Example columns |
|---|---|---|
| fact_orders | Stores each order event | order_id, customer_id, order_date, gross_revenue, discount_amount, net_revenue |
| dim_customer | Describes the buyer | customer_id, first_order_date, acquisition_channel, region |
| dim_product | Describes what was sold | product_id, category, brand, sku |
| fact_order_lines | Adds product-level detail when needed | order_id, product_id, quantity, line_revenue |
With that structure, LTV becomes a governed calculation built from trusted order history rather than a loose blend of ad platform exports and storefront reports. You can define the revenue base, customer start point, and cohorting rules once, then apply them consistently across retention, repeat purchase, and profitability analysis.
The key lesson in both examples is the same. The schema should mirror the business event you care about, then attach descriptive context cleanly around it. That's what makes MRR and LTV explainable, not just computable.
Connecting Your Model to a Semantic Layer
A well-modeled warehouse is necessary. It still isn't enough.
Most metric disputes don't happen because a fact table is missing. They happen because different tools and people define the same KPI differently after the data leaves the warehouse. One dashboard treats active customers as anyone with a payment in the last month. Another uses product usage. A board slide excludes paused accounts. The SQL is all “correct” relative to local assumptions.
Why semantic consistency breaks
The semantic layer matters. It sits above the warehouse model and gives business metrics a governed definition that multiple tools can reuse.
The hardest part isn't writing a metric formula. It's deciding what belongs in warehouse structure versus semantic logic. The dbt discussion of modern data modeling techniques highlights that semantic layers centralize metric definitions and reduce metric drift, while governance features such as lineage, versioning, and access controls help keep those definitions trustworthy. It also points out a key gap: teams rarely connect those governance concerns to modeling choices like grain, slowly changing dimensions, and whether a metric belongs in the schema or above it.
That gap is where operators feel pain.
A semantic layer works best when the warehouse already gives it clean inputs:
- Consistent keys: account_id should mean the same entity across facts and dimensions.
- Stable grain: the metric engine must know what each row represents.
- Clear canonical tables: one trusted place for subscriptions, orders, accounts, and billing events.
If your team is also formalizing ownership and schema expectations, a lightweight approach to data contracts between producers and analytics consumers can prevent upstream changes from breaking downstream metrics.
What belongs in the model and what belongs above it
A simple rule helps.
Put business entities and reusable event logic in the warehouse model. Put presentation-ready metric definitions and business filters in the semantic layer.
For example:
- Subscription and invoice relationships belong in the warehouse.
- “Net new MRR” as a governed business metric may belong in the semantic layer.
- Customer status history belongs in modeled tables.
- “Active customer” may belong in semantic logic if the business definition changes by use case.
One toolset pattern that teams use is a warehouse modeled in BigQuery, Snowflake, or Databricks, transformations in dbt, and a semantic layer on top. Some teams also use managed services such as HelpWithMetrics to set up semantic definitions on top of the warehouse and return governed answers to business users without rebuilding logic in every dashboard.
Without that layer, each BI tool becomes its own policy engine. That's how metric drift starts.
Implementation and Key Design Decisions
Good warehouse design starts before anyone writes SQL. Teams get into trouble when they jump straight from source tables to dashboard tables and treat modeling like cleanup work instead of architecture.
A more durable approach follows the standard sequence from business understanding to implementation. Databricks describes warehouse design as a progression from conceptual to logical to physical design, where business meaning is separated from storage mechanics in a way that helps preserve governance and performance across platforms like Snowflake, BigQuery, and Databricks, as outlined in its guidance on modern data modeling.
Design from business meaning to platform reality
At the conceptual stage, define the business entities and relationships. For a SaaS company, that usually means accounts, users, subscriptions, invoices, plans, and product usage. For e-commerce, it means customers, orders, products, refunds, and channels.
At the logical stage, choose the modeling style. At this point, you decide whether a dimensional model, a more normalized core, or a hybrid approach makes sense.
At the physical stage, you implement for the platform you use. That includes engine-specific decisions such as partitioning, clustering, naming conventions, data types, and how frequently models refresh.
That sequence sounds formal, but it prevents a common failure mode. Teams often encode business meaning inside platform-specific shortcuts. Then they switch warehouses, add new tools, or expand reporting scope and discover the model can't adapt.
Layer your warehouse so logic has a home
A layered architecture makes warehouse logic maintainable because each layer handles a different job. The RudderStack guide to warehouse modeling describes a layered model of raw, staging, core, analytics, and aggregate, and emphasizes that upper-core layers should avoid complex structures such as arrays and dictionaries while standardizing IDs and naming for analyst usability and cross-system joins.
That advice matters in practice because model debt often comes from logic living in the wrong place.
A clean layering pattern looks like this:
- Raw: landed data from Stripe, Shopify, Salesforce, product events, and ad platforms.
- Staging: renamed fields, deduplication, type cleanup, source-specific preparation.
- Core: canonical business entities at reliable grain.
- Analytics: denormalized marts for reporting and semantic consumption.
- Aggregate: precomputed rollups for speed-sensitive dashboards.
Don't let dashboard SQL become your analytics layer. If the logic matters to the business, give it a named home in the warehouse.
Handle change deliberately
Two design choices separate stable warehouses from brittle ones.
First, decide when to denormalize for usability. In analyst-facing models, denormalization usually helps because it reduces repeated joins and makes business logic easier to inspect. Over-normalization belongs more naturally in operational systems than in reporting layers.
Second, handle slowly changing dimensions on purpose. If a customer changes plan, region, owner, or segment, you need to decide whether reports should overwrite history or preserve the old context.
A simple working distinction:
- Type 1 style behavior: overwrite the old value when you only care about the current state.
- Type 2 style behavior: preserve history when the prior value matters for past reporting.
If sales reassigns an account owner, a current-state overwrite may be fine. If a customer moves from SMB to enterprise and finance wants historical MRR by original segment, you need preserved history.
Most reporting confusion around churn and MRR isn't caused by math. It's caused by unspoken choices about history.
Common Data Modeling Pitfalls and How to Avoid Them
The fastest way to create reporting debt is to treat the warehouse like a dumping ground and hope BI tools sort it out later.
The patterns that create reporting debt
One common mistake is copying the production schema into the warehouse and stopping there. Transactional schemas are built for application writes, not for board reporting. They often hide business meaning across many tables and encourage every analyst to reverse-engineer the product.
Another is mixing grains inside one table. If some rows represent invoices, others represent invoice lines, and others represent monthly account summaries, metric logic becomes fragile fast.
A third mistake is assuming one schema can serve every purpose. Recent modeling guidance increasingly notes that warehouses often need to support both BI and AI or activation use cases, and that modular layers plus support for broader data types are becoming more relevant, as described in MessageGears' discussion of modern warehouse modeling. That matters because the old idea of one schema for everything doesn't match how many teams now use cloud warehouses.
What to do instead
Use this as a practical checklist:
- If you copied app tables directly: build a business-facing core model with canonical entities.
- If one KPI changes by dashboard: move the definition into a semantic layer with named governance.
- If joins break across tools: standardize IDs, naming, and grain before adding more dashboards.
- If BI and activation need different shapes: keep modular layers and let each downstream use case consume the right one.
- If historical reporting keeps changing retroactively: decide where current-state logic ends and historical preservation begins.
The goal isn't theoretical purity. It's trust. When the CEO asks for MRR, your model should make the answer boring.
If your team needs help structuring a warehouse around trusted metrics instead of ad hoc reporting, HelpWithMetrics works inside your existing stack to organize source data, shape the warehouse, and layer governed metric definitions on top so SaaS and e-commerce teams can get auditable answers for MRR, churn, CAC, LTV, and related KPIs.