Data Warehouse Examples: Fact Tables, Dimensions and Reliable Totals

A useful data warehouse example shows what one row represents, which dimensions describe it and how a business total is calculated. Order lines, inventory snapshots and campaign-day results need different grains and aggregation rules. Start with the decision and expected records, then build the warehouse model that preserves those meanings. A tidy schema is insufficient if its joins or totals answer the wrong question.

Jafrin Akter AloLast updated 11 min read
On this page

Data warehouse examples by business question

The examples below are synthetic and use small populations that can be checked independently.

Data warehouse examples by business question
Business questionFact-table grainUseful dimensionsMain aggregation risk
Product salesOne accepted order lineProduct, customer and order dateRepeated order totals across lines
Closing inventoryOne product-location snapshot at a defined timeProduct, location and snapshot dateAdding balances across time
Campaign efficiencyOne campaign-day resultCampaign, channel and dateAveraging ratios without their denominators
Customer journeyOne defined business eventCustomer, account and event dateConfusing events with unique people

Kimball describes grain as the business meaning of a fact-table row, rather than merely its list of keys. Microsoft's star-schema guidance distinguishes facts used for summarization from dimensions used for filtering and grouping. See fact-table grain and star-schema concepts.

We would write the expected total and a few included and excluded records before drawing the schema. That makes the model testable and helps the business owner explain why a row belongs in the result.

At Martechdevs, data warehouse implementation connects source records to maintained analytical models. We use known examples to verify grain, relationships and metric definitions before those models become the basis of dashboards or activation.

A white floor-plan tile has a pink central section, three lime surrounding sections and cobalt details.

Fact and dimension tables

A fact table records observations at a consistent grain. Its measures might include quantity, extended sales amount, spend or an inventory balance. A dimension table provides descriptive context, such as a product category, customer segment, location or date.

The distinction concerns the table's purpose. A column being numeric does not automatically make it an additive measure. A product identifier is a key; a unit price is a rate; a line amount is an extended value. Those columns support different calculations.

In a star schema, facts relate to dimensions through keys. The expected relationship usually gives each fact one applicable dimension row for the chosen meaning and time. A missing key can drop records from an inner join, while a duplicated dimension key can multiply them. See Microsoft's fact and dimension guidance.

The data warehouse design guide covers the broader architecture and implementation process. The examples here focus on the records, joins and calculations that make that design useful.

Order-line sales example

Assume two hypothetical accepted orders. Order O1 contains two lines, while O2 contains one. Amounts are USD and unit prices are stored as integer cents for the reproducible query.

Order-line sales example
Order and lineCustomer keyProduct keyQuantityUnit price, USD
O1, line 1C111$40
O1, line 2C122$30
O2, line 1C212$40

Product 1 belongs to Core and product 2 belongs to Add-on. The line values are $40, $60 and $80, producing $180 in total sales value across two orders and five units. This fixture excludes tax, shipping, returns and discounts so the arithmetic has one explicit meaning.

The following self-contained SQL runs in SQLite. It includes the sample facts and product dimension, then calculates category totals. Other SQL systems may require small syntax adjustments.

SQL
WITH order_lines(order_id, line_no, customer_key, product_key,
                 quantity, unit_price_cents) AS (
  VALUES ('O1', 1, 'C1', 1, 1, 4000),
         ('O1', 2, 'C1', 2, 2, 3000),
         ('O2', 1, 'C2', 1, 2, 4000)
), products(product_key, category) AS (
  VALUES (1, 'Core'), (2, 'Add-on')
)
SELECT p.category,
       SUM(f.quantity) AS units,
       SUM(f.quantity * f.unit_price_cents) / 100.0 AS sales_usd,
       COUNT(DISTINCT f.order_id) AS orders
FROM order_lines AS f
JOIN products AS p ON p.product_key = f.product_key
GROUP BY p.category
ORDER BY p.category;

The output is Add-on with two units, $60 and one order, plus Core with three units, $120 and two orders. The sales amounts and units add to the overall totals. The grouped order counts do not: O1 appears in both categories, so adding one and two would count three category memberships rather than two distinct orders.

This query assumes every product key resolves to exactly one dimension row. Validate missing and duplicated keys before using the inner join. A correct-looking category chart can otherwise omit a product or duplicate its contribution.

In a production model, the extended line amount may be stored directly after the agreed discount and currency rules are applied. Keep unit price available when useful, but do not sum unit prices as if they were sales value. Kimball's fact-table discussion explains the importance of measures consistent with the declared grain.

Order-level values and relationship multiplication

An order-level value belongs to one order, even when the order has several lines. Shipping, an order-wide discount or a customer-level balance can be repeated when joined to line facts. Summing the repeated value changes its meaning.

Decide whether the measure should remain at order grain or be allocated to lines using an approved rule. An allocation must preserve the order total and document its basis. Do not let the number of lines decide the result accidentally.

The same issue appears in marketing and CRM data. A customer with several contacts can multiply a contract amount, and an order with several campaign touches can multiply revenue. More relationships do not automatically provide a better reporting model.

Use the marketing metric contracts guide to define the intended measure. Retain the source key and an independent total so the join can be checked before additional dimensions are introduced.

Periodic inventory snapshot example

A snapshot represents state at a defined time or period, rather than a new transaction each time the row is stored. Assume one location records closing inventory for two products at the end of January and February.

Periodic inventory snapshot example
Snapshot monthProduct A unitsProduct B unitsClosing total unitsInterpretation
January100200300Stock at the January cutoff
February1200120Stock at the February cutoff

The two closing totals add numerically to 420, but that is not the stock at either cutoff. A report asking for February closing stock should return 120. A report asking for average month-end stock across these two equally weighted snapshots would return 210, under that explicitly defined calculation.

Kimball's periodic snapshot guidance distinguishes a period grain from an individual transaction. The time meaning belongs in the table and report definition, not only in a chart title.

Zero and missing also differ. Product B's February zero means the sample deliberately records no remaining stock at that cutoff. A missing row might instead mean the source was unavailable or the snapshot failed. Do not turn every missing balance into zero without an approved completeness rule.

State measures often need different time aggregation from transaction measures. The same caution applies to active subscriptions, account balances and pipeline snapshots. Choose a closing value, an average or another defined calculation instead of summing every available date by habit.

Campaign-day performance example

Campaign results commonly arrive at a campaign-date grain, sometimes with additional dimensions such as device, placement or geography. Keep those dimensions explicit: a campaign-day total and a campaign-day-device breakdown are not interchangeable rows.

The following synthetic example has three campaign-day records. All spend is USD.

Campaign-day performance example
Campaign and daySpend, USDClicksRow cost per click, USD
Campaign A, day 1$10050$2.00
Campaign A, day 2$20050$4.00
Campaign B, day 1$150100$1.50

Total spend is $450 and total clicks are 200. Combined cost per click is $450 divided by 200, or $2.25. An unweighted average of the three row ratios is $2.50 and answers a different question because the rows have different click counts.

Store or retain the components needed to recompute the ratio after filtering. When a reader selects Campaign A, the result should become $300 divided by 100 clicks, or $3.00. A precomputed ratio column alone is insufficient for many useful aggregations.

The marketing data warehouse guide places those source feeds in a broader reporting architecture. Reconcile source totals at the same grain, time zone, currency and reporting window before interpreting a difference between tools.

Changing dimensions and historical meaning

Dimensions can change. A product can move category, a customer can move segment and a sales owner can move region. The model needs to specify whether historical facts should use the value at the time of the event or the current value.

Microsoft's star-schema guidance distinguishes Type 1 changes, which overwrite a dimension attribute, from Type 2 changes, which retain versions with validity ranges and keys. The appropriate choice depends on the meaning of the report. See changing dimensions.

For example, moving Product 1 from Core to Premium can either restate all historical sales under its current category or preserve Core for earlier sales. Neither interpretation should happen accidentally. Name the report's policy and maintain the corresponding dimension keys or lookup rule.

Keep the business identifier separate from a particular historical version when versioning is required. A product remains the same business item while its descriptive state can have several valid versions. A join on only the business identifier may match more than one historical row if the time condition is omitted.

Test a fact before the change, one after it and one exactly at the defined boundary. The late-arriving data guide helps address records that arrive after their business period or before their descriptive context is ready.

Warehouse layers and reporting scope

These examples can live within a warehouse architecture that separates source-like input, validated records and business-facing models. The layer names matter less than the responsibility and quality state of each dataset.

Keep the source evidence needed to explain a model while controlling access and retention appropriately. A curated sales table can omit operational fields that remain useful in a restricted source layer for debugging. The report should use the accepted business model rather than whichever table was easiest to find.

A data mart is a scoped analytical collection serving a business area. A marketing mart can combine campaign facts with agreed customer outcomes, while a finance-oriented model may use different recognition and reconciliation rules. Shared keys and definitions help those areas compare results without forcing them into one oversized fact table. See AWS's data-mart distinction.

The warehouse and data lake comparison covers storage and analytical responsibilities. The warehouse and database comparison separates analytical work from operational transaction requirements.

Model validation and failure cases

Model validation and failure cases
CheckKnown caseExpected resultFailure meaning
Grain uniquenessRepeated order-line keyOne accepted row or an explicit correctionDuplicate business observation
Dimension matchFact with unknown product keyDefined unknown handling or quarantineSilent join loss
Relationship cardinalityDuplicate product dimension keyRejection or repaired dimensionMultiplied fact rows
Time aggregationTwo month-end balancesSelected closing value or defined averageStock values summed across time
Ratio aggregationCampaign rows with unequal clicksTotal spend divided by total clicksUnweighted ratio average
Historical dimensionProduct category changeAgreed current or historical interpretationUnintended restatement

The marketing data quality guide helps turn those checks into operating evidence. A warehouse test should explain the business consequence of failure, not merely show that a query returned a number.

A practical modeling sequence

  1. Select one business decision and define the result it needs.
  2. Declare what one fact row represents, including its time meaning.
  3. Identify descriptive dimensions and their valid keys.
  4. Define additive amounts, counts, balances and ratios separately.
  5. Build a small sample with independently known totals.
  6. Test joins, missing keys, repeated rows and historical changes.
  7. Reconcile the report after representative filters are applied.
  8. Assign source and model owners before adding more business areas.

Use the dashboard templates tool to agree the report's question and layout after the model's numbers reconcile. A new visualization should not become the place where conflicting definitions are silently resolved.

Fact-grain selection and repair

Selection workflow
The business question and time meaning select an individual-event transaction fact or a defined snapshot. Key, join and aggregation tests follow both routes; grain or relationship repair returns to the original requirement before a maintained report definition.
Read diagram as text
  • Business question and time meaning to Individual event grain.
  • Individual event grain to Transaction fact design: Yes.
  • Individual event grain to Defined snapshot design: No.
  • Transaction fact design to Key join and aggregation tests.
  • Defined snapshot design to Key join and aggregation tests.
  • Key join and aggregation tests to Accepted records and totals.
  • Accepted records and totals to Grain or relationship repair: No.
  • Grain or relationship repair to Business question and time meaning.
  • Accepted records and totals to Report definition and ownership: Yes.
  • Report definition and ownership to Maintained analytical model
Open full diagram

Data warehouse example checklist

  • A business definition of one fact row.
  • Stable keys and an explicit source population.
  • One applicable dimension row per intended fact relationship.
  • Separate rules for amounts, distinct counts, balances and ratios.
  • Currency, units and time cutoffs beside the measures.
  • Known ordinary, missing, repeated and changed records.
  • A declared current-versus-historical dimension policy.
  • Reconciliation before and after joins and report filters.
  • A source owner and a continuing model owner.
  • Repeated acceptance checks after grain or relationship changes.

The marketing data audit skill helps preserve the source and model evidence. Keep the small examples with the warehouse documentation so a later maintainer can verify the same business meaning.

Keep reading

Browse all Data Warehouse guides

Questions people ask

Can category-level distinct order counts be added to obtain the overall order count?

Category-level distinct order counts cannot always be added to obtain the overall count. One order can contain products from several categories and appear once in each category's result. Recompute distinct orders over the complete selected population instead. Additive sales amounts and non-additive distinct counts can coexist in the same report, so each measure needs its own aggregation rule.

Does a numeric column automatically belong among additive warehouse measures?

A numeric column is not automatically an additive measure. Identifiers, unit prices, rates and balances can be numeric while requiring different treatment from transaction amounts. Define what the value represents and which dimensions it can be summed across. Retain the components needed to calculate ratios and the time meaning needed to interpret snapshots before exposing the field to report authors.

Can an absent inventory snapshot be safely replaced with zero?

An absent inventory snapshot cannot safely be replaced with zero without a completeness rule. Zero can mean a known empty balance, while absence can mean a missing source, failed load or excluded entity. Preserve that distinction in the model and report. Check the expected product-location population at each cutoff before treating missing rows as measured zero stock.

Should a product-category change always rewrite historical sales reporting?

A product-category change should rewrite historical reporting only when that is the agreed interpretation. A current-category view can intentionally restate earlier sales, while a historical-category view preserves the description valid at the event time. Choose the dimension-change approach and join rule accordingly, then test records around the change boundary. See Microsoft's changing-dimension guidance.

Can a successful fact-to-dimension join still return the wrong sales total?

A successful join can return the wrong sales total when dimension keys are duplicated, facts are missing matches or the relationship uses the wrong historical version. The database can execute valid SQL without knowing the intended business grain. Reconcile key coverage, row counts and known amounts before and after the join, rather than treating query completion as evidence of correctness.

Sources

  1. Kimball fact tables and grain
  2. Kimball periodic snapshot facts
  3. Microsoft star schemas, measures and changing dimensions
  4. AWS data warehouse, lake and mart distinctions
Portrait of Jafrin Akter Alo

Written by

Jafrin Akter Alo

Data Engineer and Co-founder at Martechdevs and Growth and Analytics Partners

Jafrin writes practical guides to marketing technology, customer data, analytics and AI workflows. Martechdevs is a Growth and Analytics Partners company.

View LinkedIn profile

Book a Free Call

Thirty minutes on your stack and what it needs. Free, and no pitch.

Thirty minutes, and you leave with a plan

We look at what you have, what it is costing you and what to do about it first.

  • 30 minutes
  • Video call, link on booking
  • No obligation

What we'll cover

  • Your current stack and where it hurts
  • Quick wins we can ship first
  • An integration roadmap
  • Timeline, scope and next steps

Calendar not loading? Open it in a new tab or email hello@martechdevs.com.