From Spreadsheets to a Small Data Warehouse: An SMB Architecture

There is a specific moment when the amount of data outgrows the spreadsheet holding it: two people open the "official" revenue file, get different totals, and spend twenty minutes figuring out whose copy is stale. A natural thing to happen once data keeps scaling but the infrastructure around it does not.

The good news is that the fix does not require Snowflake, Databricks, or a hire. A single SQL database, organized into three schemas, is enough to carry a small business for years (likely forever). Here is the shape I build.

The Symptoms

A few signs it is time to move past spreadsheets, before touching any architecture:

• The same number means two different things depending on which file you open.
• Someone rebuilds the same pivot table by hand every month.
• A report has a footnote explaining when it was last refreshed, because nobody is sure anymore.
• The person who built the "master" spreadsheet's macros has left, and nobody wants to touch it because it just works.
• Data gets copy-pasted daily, then pushed through a macro — silently praying it still works.

If two or more of these sound familiar, the problem is not a Power BI problem yet — it is a "where does data live" problem. Fix that first.

The Shape of a Small Warehouse

In simple terms, there are three layers: the source, where data gets extracted in its rawest form; staging, where it gets cleaned up and made ready to insert; and the mart, where it actually lands. Staging is the critical layer — it's where we stop, check everything is in order, and make sure we're not introducing duplicates or re-inserting the same values over and over, before anything reaches the mart.

Getting data into the mart usually also means pulling repeated values out into their own tables. Imagine the raw data repeats an item code and item name on every single row — with only 2 items but 250 rows, that item name just got repeated 250 times. So we extract it into a dedicated dimension table (dim_product): the fact table keeps just the item code, and dim_product holds the code plus the name, once each. Two rows now explain what used to take 250, and that saving compounds with every dimension you add on a growing dataset. The bigger win is maintenance, not storage: rename an item and you change one row in dim_product, instead of hunting down 250 rows in the raw data.

sources (CRM, billing, spreadsheets, ads)
        │
        ▼
  raw       – exact copies, untouched, timestamped
        │
        ▼
  staging   – typed, deduplicated, one row per business key
        │
        ▼
  mart      – star schema: fact_* and dim_* tables
        │
        ▼
  Power BI / reports

Three schemas in one database, not three separate systems. That distinction matters: at SMB scale, the complexity that actually bites you is disorganization, not lack of horsepower. A single Azure SQL Database (or Postgres, if that is what you already run) handles the volume a small business generates without breaking a sweat.

Quick tip: Resist the urge to skip straight to the mart. The raw and staging layers feel like overhead on day one, but they are what let you fix a bug in your transformation logic without going back to the source system to re-extract everything.

Why Not Point Power BI Straight at the Source Systems?

Power Query already copies data out of your CRM or billing tool every time it refreshes — so why not skip the warehouse and let each report do its own extraction?

Because that copy is invisible, undocumented, and thrown away after every refresh. Nothing keeps history, nothing catches a duplicate, and if the source system's schema changes, it silently breaks a report instead of a controlled pipeline step. A real staging layer gives you a place to catch that duplicate row before it inflates a KPI, and a real mart gives you the same clean model I described in Working with multiple fact tables in Power BI — except now that model is built once in SQL, not reconstructed inside every report that touches the data.

Layer 1: raw

Land exactly what the source gives you. No renaming, no filtering, no "fixing" — that comes later. The only additions are a load timestamp and, where it applies, the source file or batch identifier. This layer's only job is reproducibility: if staging logic turns out to be wrong, you reprocess from here instead of re-pulling from a system that may not remember what it sent you last month.

CREATE TABLE raw.customers (
    _raw_id         INT            IDENTITY(1,1) NOT NULL,
    customer_id     INT            NOT NULL,
    customer_name   NVARCHAR(200)  NULL,
    region          NVARCHAR(100)  NULL,
    email           NVARCHAR(200)  NULL,
    _loaded_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME(),
    _source_file    NVARCHAR(255)  NULL
);

Quick tip: Naming conventions can be rocket science, and it mostly comes down to personal preference when you have the choice. My take — snake_case all the way: it saves typing brackets and lowercase just types faster than CamelCase, which is arguably more readable, so it's a genuine trade-off either way. Regardless, I like a _-prefix for columns that carry no business meaning and exist purely for audit or bookkeeping — _raw_id above being the case in point.

Every load appends; nothing here gets updated in place. Storage is cheap, and an append-only raw layer means you can always answer "what did the source actually send us, and when."

Layer 2: staging

This is where types get enforced and duplicates get resolved — one row per business key, always the latest version. A small ROW_NUMBER() window function does the job without needing a separate tool:

SELECT
    customer_id,
    customer_name,
    region,
    email,
    _loaded_at
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY _loaded_at DESC
        ) AS rn
    FROM raw.customers
) AS deduped
WHERE rn = 1;

Keep staging boring: type conversions, trimming, deduplication, maybe a lookup to resolve a code to a label. Business logic — how revenue is defined, which statuses count as "active" — belongs one layer further in, not here. Staging's job is to make the data trustworthy, not meaningful.

Layer 3: mart — the star schema

This is the layer Power BI (or any BI tool) should actually query: fact_* tables holding measures, dim_* tables holding the descriptive attributes to slice them by. If you have more than one fact table, this is also exactly where you decide how they share dimensions — the model-view approach from the multiple fact tables post applies here just as much as inside Power BI itself, except you are shaping the model in SQL views once, instead of inside every report file that touches it.

CREATE VIEW mart.fact_sales AS
SELECT
    s.sale_id,
    s.customer_key,
    d.date_key,
    s.amount
FROM staging.sales AS s
JOIN mart.dim_date AS d
    ON d.calendar_date = CAST(s.sale_date AS DATE);

For most SMB reporting, a Type 1 dimension — new values simply overwrite old ones — is the right default. Full change-history (Type 2) is a real requirement sometimes, but it is also the single most common piece of accidental over-engineering I see in small data models. Add it when a specific question needs it ("what was this customer's region when the deal closed"), not by default.

The Pipeline Between Layers

Moving data raw → staging → mart is a small, scheduled job — nightly is more than enough cadence for the reporting rhythm most SMBs actually run on. This is precisely the shape of pipeline covered in Simple ETL Configuration with Pydantic Settings: a typed settings object, predictable paths, and a script that runs the same way every time. No streaming, no orchestration platform — a scheduled Python job executing a few SQL scripts in order gets you there.

What This Buys You

• One place to answer "what changed, and when." The raw layer keeps a paper trail spreadsheets never had.
• Simple Power BI models. The hard work of untangling relationships happens once, in the mart, not inside every report.
• Decoupling. Swap or add a source system without every downstream report breaking on the same day.
• A system one person can hold in their head. For a business without a dedicated data team, that is not a nice-to-have — it is the whole point.

What to Skip at This Scale

No data lake, no Databricks or Snowflake, no streaming ingestion, no Type 2 history on every dimension, no dbt project until the team actually grows into needing one. One well-organized SQL database and a scheduled job outlasts most of the tooling built to replace it — this is the same architecture I put in place under the Data Warehouse step of my Data & Analytics services for clients who have exactly this spreadsheet problem and no interest in buying more platform than they need.