Data warehouse design for a small company: layers, models and rules that prevent rework

Last updated: 24 September 2026.

The expensive mistake is often a quiet join. An account has three contacts and four invoices. A report joins both detail tables to the account and produces twelve rows. Revenue looks plausible, the dashboard loads and nobody notices that each invoice has been counted three times.

Good warehouse design makes that mistake harder to create and easier to detect. Folder names help, but the real protection comes from explicit grain, relationships and reconciliation.

Start with the decision and work backwards

Write the first deliverable in business terms: “Compare acquisition channels by 90-day paid conversion at account level.”

Then specify the inputs. You need account creation, acquisition identity, qualifying payments and a mature observation window. You may not need every page view, support ticket or advertising dimension on day one.

Record the expected action too. If the report supports budget allocation, the owner should understand unknown attribution, incomplete cohorts and the difference between observed revenue and incremental revenue.

Martechdevs designs warehouse and CDP implementations around these source-to-decision paths. The model inventory follows the use case instead of becoming a list of every available connector.

Use three main layers, with intermediate models when needed

This is a recommended design for a small team, not a claim that every project must use identical names. dbt's own project guidance distinguishes staging, intermediate work and business marts, with source data underneath. dbt project structure.

Open full diagram

Raw preserves what the source supplied

Keep source IDs, ingestion timestamps and useful change metadata. Document whether the connector stores current state, append-only events or historical versions.

“Raw” does not automatically mean every past state is retained. A connector that overwrites a customer row can leave you with only the latest value. That is a design fact the model owner must know.

Avoid using this layer directly for executive reporting. Source schemas reflect the vendor's product, and source field names rarely explain your business definitions.

Staging makes each source consistent

Rename fields, cast types, normalise timestamps and apply source-specific cleanup. Keep business joins out of the first cleanup step unless there is a clear reason.

For example, stg_billing_invoices can expose invoice_id, account_reference, invoice_status and amounts in documented units. It should not quietly decide that every invoice is recognised revenue.

Handle malformed values visibly. Converting an invalid amount to zero may keep the pipeline green while corrupting the report. A null plus an exception count can be a more honest outcome.

Intermediate models hold reusable logic

Use this layer when a join or calculation serves several downstream models. Examples include mapping billing customers to product accounts or reducing many contact associations to an agreed account relationship.

Keep each intermediate model explainable in one sentence. If a model performs identity resolution, revenue classification and campaign attribution together, it will be difficult to test or change safely.

Business models expose useful entities and facts

This is where analysts should find an account, an invoice, a daily subscription state or a campaign-day spend record with a clear definition.

A model's grain belongs in its documentation and tests. “One row per account” is a promise. “Usually one row per account unless several contacts exist” is an unresolved design issue.

Ten useful first models for a marketing warehouse

Build only the models required by the initial scope. This list is a reference architecture, not a mandatory first release.

1. `dim_accounts`

One row per business account, with the stable account ID and current descriptive attributes. Document how workspaces, subsidiaries and billing entities map to an account. Keep a separate bridge when the relationship is many-to-many instead of forcing every source into one convenient ID.

2. `dim_contacts`

One row per known person or CRM contact under an explicit identity policy. Record the authoritative contact ID and selected operational attributes. A shared email address or changed email needs a defined treatment. Avoid using email as the permanent key for every downstream model.

3. `bridge_account_contacts`

One row per valid account-contact relationship, with relationship type and relevant dates. This supports people who belong to multiple accounts. Consumers must know that joining through the bridge can multiply rows, so aggregated account metrics should not be summed after an unrestricted contact join.

4. `fct_opportunities`

One row per opportunity in its current state, with amount, currency, stage and ownership. Keep the source amount separate from any converted reporting currency. Define whether pipeline includes renewals, expansions and partner deals before exposing a single pipeline total.

5. `fct_opportunity_snapshots`

One row per opportunity per snapshot date, or a documented version interval if using change history. This supports questions about stage movement and month-end pipeline. Label when the state was observed so readers do not mistake a daily snapshot for a complete event-by-event history.

6. `fct_invoices`

One row per invoice, with status, issue date, due date and clearly named amount fields. If line items are needed, give them their own grain. Keep payments and refunds distinguishable. An invoice amount can support a billing analysis without being a substitute for accounting revenue recognition.

7. `fct_subscription_daily`

One row per subscription per date, or account per date if the business explicitly consolidates subscriptions. Define plan changes, pauses and cancellations. Store the effective date used for the state, and separate scheduled cancellation from a subscription that has already ended.

8. `fct_campaign_spend_daily`

One row per platform, account, campaign, date and currency at the selected aggregation level. Preserve platform IDs and source timezone. A campaign name is a label that can change. If ad-level detail is added, it should live at a different grain rather than silently expanding this model.

9. `fct_account_activation`

One row per account with the first qualifying activation timestamp and the rule version. Include the required observation window and eligibility. A changed activation definition should be traceable; otherwise a historical improvement may reflect a model edit rather than a better product experience.

10. `mart_acquisition_cohorts`

One row per cohort period, acquisition grouping and agreed segment. Include eligible accounts, mature accounts, conversions and unknown attribution counts. Derive rates from their numerators and denominators. Do not average percentages across groups with different population sizes.

Star schema or wide tables?

A star schema separates measurable facts from descriptive dimensions. Wide tables place commonly used attributes together for simpler consumption. A small team can use both.

Keep core facts at stable grains, then build purpose-specific wide models for common questions. An account health model might reasonably combine current plan, activation status and recent usage into one account row.

The danger is a wide model that mixes several detailed facts. An invoice row, product event and opportunity do not share a natural one-to-one relationship. Joining them directly can multiply values.

For the account with three contacts and four invoices, aggregate invoice totals to account grain first if the report needs account revenue. Then join a single account-level contact summary, or keep contact detail in a separate drill-down.

A DISTINCT at the end is not a general repair. It can remove legitimate repeated values while leaving the underlying relationship wrong.

Naming rules should reveal meaning

Use consistent lowercase names and a small vocabulary. A workable convention is stg_source_entity for staging, int_purpose for intermediate models, dim_entity for dimensions and fct_event_or_state for facts.

Name units directly: amount_minor_units, amount_usd, duration_seconds. Use suffixes such as _at for timestamps and _date for dates if that is the convention your team adopts.

Distinguish created_at, source_updated_at and loaded_at. They answer different questions. A freshly loaded record can describe an old event, while a recently updated record can reflect a correction to earlier business activity.

For booleans, define null behaviour. is_activated = false means something different from unknown activation status because product events failed to load. Preserve that uncertainty when it affects decisions.

Avoid a field called simply revenue. Use the specific measure, currency and period, then document exclusions.

Keep metric definitions beside the logic

A useful metric definition includes:

• Business question and owner.
• Entity and calculation grain.
• Numerator, denominator and time basis.
• Included and excluded states.
• Currency and timezone rules.
• Late-data and revision behaviour.
• Known limitations and rule version.

Here is an original example:

metric: account_paid_conversion_30d
owner: revenue_operations
grain: account_creation_cohort
numerator: eligible accounts with a successful first payment within 30 days
denominator: eligible accounts with a complete 30-day observation window
exclude: internal accounts, test accounts, duplicate account records
time_basis: account creation timestamp in UTC
unknowns: report missing billing mappings separately
revision_policy: refresh recent cohorts when late billing records arrive

This definition should inform SQL, dashboards and AI context. Copying a shortened version into each tool invites disagreement. The context engineering guide shows how to expose maintained business definitions to an agent.

Test business assumptions, not only SQL syntax

dbt supports reusable data tests such as uniqueness, non-null values, accepted values and relationships, alongside custom checks. These are useful building blocks, but a business reconciliation often needs its own query. dbt data tests.

Start with the promises that protect the decision:

Key checks. Account IDs are unique where the model promises one row per account. Required IDs are present.

Relationship checks. Invoices map to valid accounts, or the unmatched population is explicitly measured.

Reconciliation checks. Modelled invoice totals agree with source totals under the same filters and currency rules.

Temporal checks. Activation cannot precede account creation unless the business has a documented migration case. Snapshot dates arrive as expected.

Distribution checks. A major change in null source, event volume or subscription states triggers review. Set thresholds from normal behaviour and business impact.

Do not assume a declared primary key protects every table. Snowflake's constraint behaviour differs by table type; many constraints on standard tables are informational rather than enforced. Snowflake constraints.

Design history and reruns early

Use change capture or snapshots for mutable source records when historical questions require them. dbt snapshots can track observed changes, but the result depends on when the snapshot runs and which source changes are visible. Snapshot documentation.

If a deal moves through three stages between daily snapshots, a snapshot may only observe the final stage. Use a source event history when the question requires every transition.

Every recurring load should have a rerun strategy. Reprocessing the same period should not create duplicate business records. Late updates, source deletions and corrected identifiers need defined behaviour.

For event sources such as GA4's BigQuery export, recent date tables can change. Incremental models need a lookback or another update strategy that reflects the source's behaviour.

Build order and delivery expectations

Use gates rather than promising a universal completion date.

First, agree on definitions and inspect source access. Next, land a representative sample and prove the identity joins. Then build staging and the first business model, reconcile it and expose one useful report or sync. Finally, add monitoring, documentation and a recovery procedure before expanding scope.

A planning estimate should separate active implementation from waiting for access, procurement, source repairs and business decisions. “Three connectors” is not enough information to estimate a build: one may expose clean history while another requires a custom mapping and backfill.

Ask for evidence at each gate. A working connector is not evidence that account revenue is correct. A correct report on one sample date is not evidence that the daily pipeline will handle changes.

At Martechdevs, we connect source systems to tested customer and revenue models, with documentation your team can maintain. Start with the free martech AI audit, or explore an AI assistant built around your approved workflows.