Data Analysis · Building a Data Warehouse

Most data warehouse projects are designed backwards. Teams pick a cloud platform first, build tables around it, write ETL to fill those tables, and then ask what the warehouse was supposed to answer. That sequence produces a technically sound system built for the wrong problem. The correct order is requirements, then schema, then ETL, then query layer — each phase depends on the output of the one before it. This guide walks all four with working SQL and a single running example: ClothCo, a clothing e-commerce company that needs to report on daily revenue by product, region, and customer segment. That business requirement starts as a note on paper in Phase 1 and ends as a 10-line SELECT in Phase 4. For the broader picture of how warehousing fits into analytical work, the data analysis overview covers the foundations before you go deep on warehouse architecture.

🛠 Mapping your schema? Use the Star Schema Designer → to lay out fact and dimension tables visually before writing any DDL.

📊 Making the case to leadership? Calculate your projected ROI with the DW ROI Calculator →

What Is a Data Warehouse? (And When Do You Need One?)

A data warehouse is a database built for reading, not writing. Your operational database — the one your app writes orders into — handles thousands of small writes per second. A warehouse handles a different job: a GROUP BY across three years of order data, joining five tables, returning in two seconds. Same underlying data, completely different architecture.

The technical distinction is OLTP versus OLAP. OLTP (Online Transaction Processing) is your production database: normalized schema, write-optimized, one row at a time. OLAP (Online Analytical Processing) is your warehouse: denormalized schema, read-optimized, millions of rows at a time. The same e-commerce order data that lives in a PostgreSQL production database gets restructured into a star schema in the warehouse so analytical queries run fast.

You need a data warehouse when:

  • Analysts need to combine data from two or more source systems — CRM, e-commerce platform, ad accounts
  • Ad hoc SQL queries against your production database are slowing it down or getting blocked by the DBA team
  • Your team is joining data in spreadsheets because the production database is off-limits
  • You need historical snapshots of data the operational system overwrites — like customer segments or product prices that change over time

You don't need a data warehouse when:

  • One database already answers every business question your team has
  • Your total data volume is under 10 GB and growing slowly
  • No one on the team can maintain it after the initial build

The Four Phases of Building a Data Warehouse

The phases run in sequence. Teams that jump straight to tool selection — the most common mistake — spend months loading data nobody queries.

Horizontal flow diagram showing the four phases of building a data warehouse. Phase 1 Requirements Gathering in blue flows via arrow to Phase 2 Schema Design in purple, then to Phase 3 ETL Pipeline in teal, then to Phase 4 Query Layer in green. Each phase box shows what it produces: Phase 1 produces business requirements and grain definition; Phase 2 produces fact tables and star schema; Phase 3 produces extract, transform, load pipeline; Phase 4 produces SQL views and BI connection.
The four phases must run in order. Requirements define what schema to build. Schema defines what ETL to write. ETL fills the tables. The query layer exposes them to BI tools and analysts.
  1. Phase 1: Requirements Gathering — Define the business questions the warehouse must answer, inventory source systems, agree on the grain, set a 90-day scope
  2. Phase 2: Schema Design — Model your data as fact tables and dimension tables in a star schema
  3. Phase 3: ETL Pipeline — Extract data from sources, transform it to match the schema, load it into the warehouse on a schedule
  4. Phase 4: Query Layer — Build SQL views that pre-join the star schema tables and expose clean columns to BI tools

Phase 1 — Requirements Gathering

This is the phase most technical teams skip — and the reason most data warehouses fail. The goal is to answer four questions before writing a single line of SQL.

Question 1: What decisions will the warehouse enable? Start with the reports and dashboards the business actually needs, then work backwards. If the VP of Sales needs weekly revenue by product category and region, that determines your fact table (order line items), your dimensions (date, product, geography), and your grain (one row per order line). Start from the report, not from the source schema.

Question 2: Where does the data live today? List every source system: the transactional database (PostgreSQL, MySQL, SQL Server), the CRM (Salesforce, HubSpot), the e-commerce platform (Shopify), the ad platforms (Google, Meta). Note the update frequency — daily batch, hourly, near-real-time. This determines your ETL schedule in Phase 3.

Question 3: What is the data quality like? Audit source data before loading anything. Common problems: nulls in key fields, duplicate records, inconsistent date formats in the same column, and referential integrity gaps — order line items whose product IDs don't exist in the product table. Poor source data quality is the single biggest variable in how long ETL development takes. The exploratory data analysis guide covers auditing techniques you can run on source exports before the first table is designed.

Question 4: What does done look like in 90 days? Define a minimum viable warehouse: one business process (sales orders), three to five dimensions (date, product, customer, geography), and two dashboards. Data warehouse projects expand until they don't ship. Agree on scope in writing before the first table is designed.

Running Example — ClothCo Requirements

ClothCo sells clothing online, with transactions in PostgreSQL and customer data in HubSpot. Four questions drive the warehouse build:

ClothCo business requirements — mapping questions to facts and dimensions
Business QuestionFacts NeededDimensions Needed
Revenue by product category per weeksales_amountdim_date, dim_product
Top customers by lifetime valuesales_amount, quantitydim_customer
Regional performance comparisonsales_amountdim_geography
Return rate by productreturn_amountdim_product, dim_date

Grain decision: One row in the fact table equals one order line item — not one order, not one day's total. This is the finest granularity that answers all four questions without losing information. Write the grain as a SQL comment on the CREATE TABLE statement. When a new data engineer joins 18 months from now, they should not have to guess what a fact table row represents.

📐 Once you know your row count and grain, estimate storage costs before choosing a platform — use the Data Warehouse Sizing Calculator →

Phase 2 — Schema Design (Dimensional Modeling)

Schema design translates the requirements output — fact definitions, dimension lists, and grain — into actual database tables. The industry standard for analytical workloads is dimensional modeling, developed by Ralph Kimball and still the dominant approach for warehouses built today.

Dimensional modeling organizes data into two table types:

  • Fact tables — Each row is one event at the defined grain. Columns are numeric measures (sales_amount, quantity_sold, gross_margin) plus integer foreign keys that point to each dimension table.
  • Dimension tables — Each row describes one thing: a product, a customer, a date, a geography. Columns are descriptive attributes: product_name, category, color, size, segment, region.

The result is a star schema: the fact table sits at the center; dimension tables radiate outward. BI tools and analysts join the fact table to whichever dimensions they need. Because dimension tables are denormalized — no sub-tables, no normalization layers — joins are simple and query performance is fast.

Star schema entity diagram for ClothCo. Central fact_sales table in purple showing columns: PK sales_key, FK date_key, FK product_key, FK customer_key, FK geography_key, sales_amount, gross_margin. Four surrounding dimension tables in blue: dim_date at top showing date_key, full_date, month_name, year; dim_product at right showing product_key, product_name, category, unit_cost; dim_customer at bottom showing customer_key, full_name, segment; dim_geography at left showing geography_key, city, region, country. Dashed lines connect each FK in fact_sales to the corresponding PK in each dimension table.
ClothCo's star schema: one fact table at the center, four dimension tables surrounding it. Every row in fact_sales references exactly one row in each dimension via integer surrogate keys — not the natural IDs from the source system.

Star Schema vs. Snowflake Schema — Decision Table

A snowflake schema normalizes the dimension tables further. In a snowflake, dim_product might break into dim_product and dim_category joined by a foreign key. This saves storage but adds query complexity. Here is when each schema makes sense:

Star schema vs. snowflake schema — four criteria for choosing between them
CriterionStar SchemaSnowflake Schema
Query performance✓ Faster — fewer joins requiredSlower — multi-hop joins for every query
Storage usageHigher — denormalized values repeated✓ Lower — normalized, less repetition
SQL complexity for analysts✓ Simpler — direct dimension joinsMore complex — joining through sub-dimensions
Dimension attribute updatesUpdate multiple rows✓ Update in one place
Use whenBI and analytics are the priorityStorage is tightly constrained and dimensions update frequently

For ClothCo — and for most teams building a first warehouse — use star schema. Analyst productivity matters more than the marginal storage saving from normalization.

Kimball vs. Inmon — Which Methodology?

Two-panel methodology comparison diagram. Left panel labelled Kimball Bottom-Up shows three stacked star schemas added iteratively over time: Sales star schema built in weeks one through four, Marketing star schema added in quarter two, Finance star schema added in quarter three. Each schema is a small star with a central fact table. Right panel labelled Inmon Top-Down shows a large 3NF enterprise data model at the top taking months to design, with three data marts derived from it at the bottom. A timeline below both panels marks Kimball delivering first value in weeks, Inmon delivering first value in months or years.
Kimball starts with one business process and expands outward. Inmon designs the full enterprise model upfront, then derives data marts from it. For most teams, Kimball ships faster and fails more safely.
Kimball vs. Inmon — five dimensions to compare
DimensionKimball (Bottom-Up)Inmon (Top-Down)
Starting pointOne business process (e.g., sales orders)Full enterprise data model in 3NF
Time to first valueWeeksMonths or years
Team size required1–5 people5–20+ people
Risk levelLower — build incrementally, fix as you goHigher — large upfront design investment
OutputStar schemas per subject area, expanded over timeNormalized 3NF EDW → subject-area data marts

Recommendation for most teams: use Kimball. Build the sales star schema. Ship it in 4–6 weeks. Add the marketing star schema next quarter. The incremental approach means every mistake is fixable and every delivery proves value to stakeholders. Inmon is the right choice for large enterprises with dedicated data architecture teams and an 18-month project runway — not a two-person data team at a 200-person company.

🛠 Ready to design your schema? Map fact tables, dimension tables, and foreign key relationships visually with the Star Schema Designer → before writing DDL.

SQL — Creating the ClothCo Star Schema

Each dimension table gets an integer surrogate key — not the natural key from the source system. Source system IDs can change, be reused, or collide across systems when two sources are merged. Surrogate keys are stable integers generated by the warehouse, independent of anything upstream.

-- Dimension: Date
-- Populated once at build time; covers 10+ years of dates
CREATE TABLE dim_date (
    date_key        INT PRIMARY KEY,         -- surrogate key: YYYYMMDD format
    full_date       DATE NOT NULL,
    day_of_week     VARCHAR(10),
    week_number     INT,
    month_number    INT,
    month_name      VARCHAR(10),
    quarter         INT,
    year            INT,
    is_weekend      BOOLEAN
);

-- Dimension: Product
CREATE TABLE dim_product (
    product_key     INT PRIMARY KEY,
    product_id      VARCHAR(50),             -- natural key from source system
    product_name    VARCHAR(200) NOT NULL,
    category        VARCHAR(100),
    subcategory     VARCHAR(100),
    color           VARCHAR(50),
    size            VARCHAR(20),
    unit_cost       DECIMAL(10,2),
    is_current      BOOLEAN DEFAULT TRUE     -- for SCD Type 2 tracking
);

-- Dimension: Customer
CREATE TABLE dim_customer (
    customer_key    INT PRIMARY KEY,
    customer_id     VARCHAR(50),
    full_name       VARCHAR(200),
    email           VARCHAR(200),
    segment         VARCHAR(50),             -- e.g., 'VIP', 'Standard', 'New'
    city            VARCHAR(100),
    state           VARCHAR(100),
    country         VARCHAR(100)
);

-- Dimension: Geography
CREATE TABLE dim_geography (
    geography_key   INT PRIMARY KEY,
    city            VARCHAR(100),
    state           VARCHAR(100),
    region          VARCHAR(50),             -- e.g., 'Northeast', 'West'
    country         VARCHAR(100)
);

-- Fact: Sales
-- Grain: one row per order line item
CREATE TABLE fact_sales (
    sales_key       BIGINT PRIMARY KEY,
    order_id        VARCHAR(50) NOT NULL,    -- natural key from source
    date_key        INT REFERENCES dim_date(date_key),
    product_key     INT REFERENCES dim_product(product_key),
    customer_key    INT REFERENCES dim_customer(customer_key),
    geography_key   INT REFERENCES dim_geography(geography_key),
    quantity        INT NOT NULL,
    unit_price      DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    sales_amount    DECIMAL(10,2) NOT NULL,  -- (unit_price * quantity) - discount
    cost_amount     DECIMAL(10,2),
    gross_margin    DECIMAL(10,2)            -- sales_amount - cost_amount
);

🔍 Paste this DDL into the SQL Schema Visualizer → to generate an ER diagram of your star schema automatically.

Phase 3 — Building the ETL Pipeline

ETL (Extract, Transform, Load) moves data from source systems into the warehouse schema on a schedule. The pipeline runs through three distinct layers, each with a different job.

Vertical stack diagram showing five layers from top to bottom. At the top, Source Systems layer showing PostgreSQL, Shopify, HubSpot icons side by side. An Extract arrow points down to the Staging Layer box, which shows raw tables matching source schema with a note: VARCHAR types, no transformation. A Transform arrow points down to the Transform Layer box, which shows business logic, type casting, deduplication, surrogate key lookup. A Load arrow points down to the Presentation Layer box showing the final star schema tables: fact_sales, dim_product, dim_customer, dim_date, dim_geography. A final arrow points down to the BI Tools layer showing Tableau, Power BI, and Looker icons.
Three-layer pipeline: staging catches raw data as-is, the transform layer applies business logic, and the presentation layer holds the final star schema that BI tools query. Each layer has one job — mixing responsibilities across layers creates hard-to-debug failures.
  • Staging layer — Raw data, unchanged, lands here first. One staging table per source system table. No transformations. This layer exists to protect the presentation layer from upstream schema changes — if a source column is renamed, staging catches it before the fact table breaks.
  • Transform layer — Cleanse, deduplicate, type-cast, derive new columns, resolve surrogate keys, apply business logic. This is where your rules live.
  • Presentation layer — The final star schema tables: fact_sales, dim_product, dim_customer, and so on. BI tools only ever query this layer.

ETL vs. ELT: the difference is where transformation happens.

ETL vs. ELT — when each approach makes sense
ApproachTransform before or after loading?Best for
ETLTransform BEFORE loading into the warehouseTraditional on-premises systems; sensitive data that must not land raw
ELTLoad raw FIRST, then transform inside the warehouseCloud warehouses (Snowflake, BigQuery, Redshift) with enough compute for in-database transformation

Modern cloud warehouses have more than enough compute to transform inside the database. Load raw data fast with Fivetran or Airbyte, then write your transformation logic in SQL using dbt. The ELT pattern is faster to implement than traditional ETL tools and version-controls the transformation logic in git alongside your schema DDL.

SQL — Staging Layer for ClothCo

The staging table mirrors the source orders table with raw VARCHAR types throughout. No casting, no cleaning — just land the data.

-- Staging table mirrors the source system orders table
-- Raw VARCHAR types throughout — transformation happens downstream
CREATE TABLE stg_orders (
    raw_id          BIGINT PRIMARY KEY,
    source_order_id VARCHAR(50),
    order_date      VARCHAR(30),             -- raw string; transform layer handles type casting
    customer_id     VARCHAR(50),
    product_id      VARCHAR(50),
    qty             VARCHAR(20),             -- may arrive as string from API responses
    price           VARCHAR(20),
    discount        VARCHAR(20),
    city            VARCHAR(100),
    country         VARCHAR(100),
    loaded_at       TIMESTAMP DEFAULT NOW()
);

SQL — Loading the Fact Table from Staging

This INSERT joins four dimension tables to resolve surrogate keys, casts raw VARCHAR columns to their target types, and calculates derived measures. The WHERE NOT EXISTS clause handles incremental loading — rows already in the fact table are skipped on each run.

-- Insert new order lines into fact_sales from staged data
-- Joins to dimensions to resolve surrogate keys
INSERT INTO fact_sales (
    order_id, date_key, product_key, customer_key, geography_key,
    quantity, unit_price, discount_amount, sales_amount, cost_amount, gross_margin
)
SELECT
    s.source_order_id,
    d.date_key,
    p.product_key,
    c.customer_key,
    g.geography_key,
    CAST(s.qty AS INT),
    CAST(s.price AS DECIMAL(10,2)),
    COALESCE(CAST(s.discount AS DECIMAL(10,2)), 0),
    -- sales_amount = (price x qty) minus discount
    (CAST(s.price AS DECIMAL(10,2)) * CAST(s.qty AS INT))
        - COALESCE(CAST(s.discount AS DECIMAL(10,2)), 0),
    p.unit_cost * CAST(s.qty AS INT),
    -- gross_margin = sales_amount minus cost_amount
    ((CAST(s.price AS DECIMAL(10,2)) * CAST(s.qty AS INT))
        - COALESCE(CAST(s.discount AS DECIMAL(10,2)), 0))
    - (p.unit_cost * CAST(s.qty AS INT))
FROM stg_orders s
JOIN dim_date       d ON d.full_date   = CAST(s.order_date AS DATE)
JOIN dim_product    p ON p.product_id  = s.product_id AND p.is_current = TRUE
JOIN dim_customer   c ON c.customer_id = s.customer_id
JOIN dim_geography  g ON g.city = s.city AND g.country = s.country
-- Incremental load: skip rows already in the fact table
WHERE NOT EXISTS (
    SELECT 1 FROM fact_sales f WHERE f.order_id = s.source_order_id
);

Handling Slowly Changing Dimensions (SCD)

A slowly changing dimension is any attribute that changes over time. How you handle it determines whether historical reports break when attributes are updated.

SCD Type 1 — Overwrite (no history preserved)

Use when the old value was wrong and history does not matter. Example: fixing a misspelled customer name.

-- SCD Type 1: overwrite the old value directly
UPDATE dim_customer
SET full_name = 'Jane Smith'
WHERE customer_id = 'C1042';

SCD Type 2 — New row with effective dates (history preserved)

Use when history matters. If a customer moves from Standard to VIP and you need to attribute old sales to Standard and new sales to VIP, Type 2 is required. Close the old row, insert a new one.

-- SCD Type 2: close the existing row
UPDATE dim_customer
SET valid_to     = CURRENT_DATE,
    is_current   = FALSE
WHERE customer_id = 'C1042'
  AND is_current  = TRUE;

-- Insert the updated row with the new segment value
INSERT INTO dim_customer
    (customer_id, full_name, segment, city, state, country, valid_from, is_current)
VALUES
    ('C1042', 'Jane Smith', 'VIP', 'Austin', 'TX', 'USA', CURRENT_DATE, TRUE);

🗺 Diagram your source-to-presentation data flows before writing pipeline code — use the ETL Pipeline Mapper →

ETL and ELT Orchestration Tools

ETL and ELT tools — what each does and when to reach for it
ToolTypeBest for
Apache AirflowOpen-source orchestratorComplex DAG-based pipelines; full control over scheduling and retry logic
dbt (data build tool)SQL transformation frameworkELT transforms in SQL; version-controlled, tested, documented automatically
FivetranManaged extractionNo-code extraction from 300+ SaaS connectors with pre-built schemas
AirbyteOpen-source extractionSelf-hosted or cloud; broader connector library; more customizable than Fivetran
AWS GlueManaged ETLAWS-native workloads; Spark-based transformation at scale
Azure Data FactoryManaged ETLAzure-native workloads; visual pipeline designer

Starting stack for most teams: Fivetran or Airbyte for extraction (they handle connector logic so you don't have to), dbt for SQL transformation, and dbt Cloud's built-in scheduler to orchestrate runs. Add Apache Airflow when pipeline complexity justifies it — not before.

Phase 4 — The Query Layer

The query layer is the interface between your warehouse tables and your users. Without it, analysts query raw fact and dimension tables directly — 15-line JOIN statements every time anyone wants a revenue number. When the schema changes, every saved analyst query breaks.

The query layer typically has two parts:

  • Presentation layer (SQL views): Views that pre-join fact and dimension tables, rename columns to business-friendly names, and calculate common metrics. BI tools connect to these views, not raw tables.
  • Data mart layer (optional): Subject-area schemas scoped to one team — a marketing mart, a finance mart. Built on top of the core star schema; no data duplication.
Three-box architecture diagram showing the query layer. Left box labelled Warehouse Tables contains fact_sales with four FK references and dim_date, dim_product, dim_customer, dim_geography listed below. A center arrow labelled SQL Views points to the middle box labelled vw_sales_analytics, which lists the exposed columns: full_date, year, category, customer_name, region, sales_amount, gross_margin. A second arrow points to the right box labelled BI Tools, showing Tableau, Power BI, and Looker as connection targets. A note beneath the BI box reads: analysts query the view, not raw tables.
The presentation view sits between the star schema and BI tools. When the underlying schema changes — a new column added to dim_product, for example — you update the view once instead of fixing every analyst's saved query.

SQL — Query Layer Views for ClothCo

-- Presentation view: pre-joins the star schema for BI tool consumption
-- Analysts and BI tools connect to this view, not to raw fact/dimension tables
CREATE VIEW vw_sales_analytics AS
SELECT
    dd.full_date,
    dd.week_number,
    dd.month_name,
    dd.year,
    dp.product_name,
    dp.category,
    dp.subcategory,
    dc.full_name         AS customer_name,
    dc.segment           AS customer_segment,
    dg.city,
    dg.state,
    dg.region,
    dg.country,
    fs.quantity,
    fs.unit_price,
    fs.discount_amount,
    fs.sales_amount,
    fs.gross_margin
FROM fact_sales      fs
JOIN dim_date        dd ON fs.date_key      = dd.date_key
JOIN dim_product     dp ON fs.product_key   = dp.product_key
JOIN dim_customer    dc ON fs.customer_key  = dc.customer_key
JOIN dim_geography   dg ON fs.geography_key = dg.geography_key;

With the view in place, the original business requirement — revenue by product category per week — becomes a clean 10-line query:

-- BI query: revenue by product category per month
-- Runs against the view, not the raw star schema
SELECT
    year,
    month_name,
    category,
    SUM(sales_amount)   AS total_revenue,
    SUM(gross_margin)   AS total_margin,
    COUNT(*)            AS order_lines
FROM vw_sales_analytics
GROUP BY year, month_name, category
ORDER BY year DESC, month_name, total_revenue DESC;

Connecting BI Tools to the Query Layer

BI tool connection options — connection method and typical use case
BI ToolConnection MethodBest for
TableauLive connection or extractVisual analytics; ad hoc exploration by analysts
Power BIDirectQuery or Import modeMicrosoft ecosystem; broad user base
LookerLookML semantic layer on top of viewsEnterprise self-service; governed metrics across multiple teams
MetabaseDirect SQL connectionLightweight, open-source; quick to set up for smaller teams
dbt + BIdbt Semantic Layer (metric definitions)Consistent KPI definitions shared across all connected tools

The rule: expose the presentation view (vw_sales_analytics) to your BI tool, never the raw fact table. This concentrates JOIN complexity in one place and makes the warehouse resilient to schema changes. When dim_product gains a new column, add it to the view — analysts see it automatically in every tool. For the KPI tracking patterns analysts apply once data is in a BI tool, the KPI examples guide covers the metrics warehouses are built to calculate.

Data Warehouse Best Practices

1. Never query staging tables from BI. Staging is for loading only. The data there is raw, untyped, and potentially duplicate. BI tools connect to the presentation layer exclusively. This boundary is not optional.

2. Use surrogate keys, not natural keys. Source system IDs can change, be reused, or collide across systems when two sources are combined. Surrogate keys are integers generated by the warehouse and independent of anything upstream. Build the habit from the first table.

3. Document the grain on the CREATE TABLE statement. Write it as a comment: -- Grain: one row per order line item. When a new engineer joins 18 months later, they should not have to guess. If two people on the team disagree about what a fact table row represents, the fact table is broken before a single row loads.

4. Build incrementally from day one. Full loads — truncate and reload — work for small tables but fail at scale. Design your ETL with incremental logic from the start: WHERE NOT EXISTS or WHERE loaded_at > last_run_timestamp. Retrofitting incremental logic onto a full-load ETL is painful and error-prone.

5. Run validation checks before go-live. At minimum: (a) row count in the fact table matches the source, (b) SUM(sales_amount) in the warehouse matches total revenue in the source, (c) no NULL values in any foreign key column. Catch data problems in testing, not in a VP's dashboard on Monday morning.

6. Version-control every SQL file. Every CREATE TABLE, INSERT, and CREATE VIEW statement belongs in a git repository. Use dbt for transformation SQL — it handles lineage, documentation, and automated testing. A warehouse whose SQL exists only on someone's laptop is a liability waiting to surface.

Common Mistakes That Kill Data Warehouse Projects

Mistake 1 — Starting with the tool. "We'll use Snowflake" is not a data warehouse strategy. Tool selection belongs after requirements, not before. Picking a cloud platform before defining business questions is building a house by choosing the brand of nails first. The tool is a consequence of requirements, not the starting point.

Mistake 2 — Trying to build everything at once. Your first deliverable should cover one subject area: sales orders, or marketing leads, or finance invoices — not all three. Ship the sales star schema in 4–6 weeks. That gives stakeholders something to react to and gives the team a schema pattern to repeat on the next subject area.

Mistake 3 — Using source system primary keys as fact table foreign keys. Source IDs can change, get reused, or collide across systems when two data sources are merged. An order ID from Shopify is not a stable foreign key in a warehouse that also ingests from your wholesale portal. Always generate surrogate keys in the warehouse.

Mistake 4 — Loading data without a staging layer. If a source system renames a column or changes a data type, your fact table INSERT breaks. A staging layer catches that upstream change before it reaches the presentation layer. Teams that skip staging spend hours debugging ETL failures caused by source changes they did not control and cannot roll back.

Mistake 5 — Applying SCD Type 1 everywhere because it is simpler. Type 1 (overwrite) destroys the ability to report history accurately. If your sales incentive structures changed midyear and customer segments shifted, you need Type 2 to attribute revenue correctly to the pre- and post-change periods. Know which dimensions change meaningfully over time and use Type 2 for those.

Mistake 6 — Skipping the query layer. Teams that connect BI tools directly to raw fact tables end up with hundreds of saved queries that duplicate JOIN logic. When dim_product changes, every query breaks. The query layer concentrates that logic in one place. It takes two hours to build and saves weeks of ongoing maintenance across the warehouse's lifetime.

FAQ — Building a Data Warehouse

How long does it take to build a data warehouse?

A minimum viable warehouse — one subject area, three to five dimensions, two working dashboards — takes 4–8 weeks with one to two dedicated engineers. The biggest time variable is source data quality. If your production database has nulls in key fields, duplicate records, and inconsistent date formats, ETL development expands significantly before a single row loads cleanly. A full enterprise warehouse covering multiple subject areas typically runs 6–18 months. Any estimate more precise than that range is a guess until a source data audit is complete.

What does it cost to build a data warehouse?

Cloud warehouse platforms charge per storage and compute: Snowflake starts around $23/TB/month for storage; BigQuery charges $0.020/GB for active storage and $5/TB for queries (or flat-rate slots for predictable costs). For a 1 TB warehouse with moderate query load, platform costs typically run $200–$2,000/month depending on query volume and compression. Add engineering time — a data engineer at $100K/year for six months is $50K — plus ETL tooling ($0–$500/month for open-source or managed) and BI tooling ($50–$150/user/month for Tableau or Power BI). Total first-year cost range for a small warehouse: $80,000–$300,000. The DW ROI Calculator helps map costs against time savings and analytical output for a business case.

Should I use Kimball or Inmon methodology?

Use Kimball for a first warehouse, a small-to-medium team, or any situation where "ship something in six weeks" matters. Kimball is bottom-up: start with the sales order business process, build its star schema, deliver it to stakeholders, then add the next business process. Inmon is top-down: design the full normalized enterprise data model first, then derive subject-area data marts from it. Inmon is appropriate for large enterprises with dedicated data architecture teams and an 18-month build runway. For a team of two or three building their first warehouse, Kimball ships and Inmon doesn't.

What is the difference between a data warehouse and a data lake?

A data warehouse stores structured, cleaned, modeled data optimized for SQL queries and business reports. A data lake stores raw, unstructured, or semi-structured data — JSON event logs, images, clickstream data — at low cost per GB. Data lakes are cheap to store things in and expensive to analyze from without prior transformation. The modern approach is a lakehouse: raw data lands in cloud object storage (S3, Azure Blob, GCS), structured in a warehouse format using Apache Iceberg or Delta Lake, then queried with the same SQL tools as a traditional warehouse. For a team deciding between the two patterns, the lakehouse question is primarily one of data team maturity and the volume of unstructured data the business actually uses.

What is SCD Type 2 and when do I need it?

SCD Type 2 (Slowly Changing Dimension Type 2) preserves history when a dimension attribute changes, by adding a new row rather than overwriting the old one. Each row gets valid_from and valid_to dates plus an is_current flag marking the active version. Use it when historical accuracy matters — if a customer moved from Standard to VIP in June and you need to attribute pre-June sales to Standard and post-June sales to VIP, Type 2 is required. If the change was a data correction (the wrong segment was entered originally), Type 1 (overwrite) is correct. The question to ask for every dimension: "If this attribute changes, do historical reports need to reflect the value at the time of the event, or only the current value?"

What is the query layer in a data warehouse?

The query layer is the abstraction between raw warehouse tables and business users or BI tools. It consists of SQL views or materialized views that pre-join fact and dimension tables, calculate common metrics, and expose column names in business terms — customer_key becomes customer_name, date_key becomes full_date. BI tools and analysts query the views, not the raw tables. When the underlying schema changes, you update the view once instead of fixing every saved query in every dashboard. The query layer is two hours of work that removes weeks of ongoing maintenance over the warehouse's lifetime.

What ETL tool should I use?

For most teams: Fivetran or Airbyte for extraction (they handle 150–300+ source connectors with no custom code), dbt for transformation (SQL-based, version-controlled, fully testable with dbt test), and dbt Cloud's built-in scheduler to start. This ELT stack requires no custom ETL code, runs entirely in SQL, and handles the full lineage from source to presentation layer out of the box. When pipeline complexity grows — dozens of sources, complex dependencies between pipelines, custom operators needed — graduate to Apache Airflow. The exploratory data methods that help you understand source data quality before you start building are covered in the exploratory data analysis guide.

What is a star schema in a data warehouse?

A star schema is a database design where a central fact table containing numeric measures sits at the center, surrounded by dimension tables containing descriptive attributes. The fact table connects to each dimension through a foreign key — a surrogate integer pointing to that dimension's primary key. When drawn on paper, the structure looks like a star: fact in the middle, dimensions radiating outward. Queries are fast because analysts only join the fact to the specific dimensions they need, and dimension tables are denormalized so there are no additional join hops to sub-tables. It is the standard schema for Kimball-style data warehouse design.

  1. Kimball, R., & Ross, M. (2013). The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd Edition. Wiley. The foundational reference for star schema design and dimensional modeling methodology.
  2. Inmon, W. H. (2005). Building the Data Warehouse, 4th Edition. Wiley. The original top-down enterprise data warehouse methodology and reference architecture.
  3. dbt Labs. dbt Documentation: What is dbt? docs.getdbt.com. The ELT transformation framework referenced in Phase 3; covers modeling, testing, and lineage.
  4. Fivetran. ETL vs ELT: What is the Difference? fivetran.com. Covers the technical distinction and when each pattern applies in modern cloud warehouse architectures.
  5. Kimball Group. Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3. kimballgroup.com. Detailed treatment of SCD Types 1, 2, and 3 with practical implementation guidance.
← Back to Data Analysis guides