How to create a fact table in a Data Warehouse

João Barros 04 de July de 2026 4 min read

The fact table is the heart of any Data Warehouse: it holds the numbers the business wants to measure, such as sales, quantities, or costs. A well-designed fact table is what turns scattered data into a model ready for fast, reliable reports, and the secret is getting the grain and the keys right.

Prerequisites

A database server (we use SQL Server, but the idea applies to any engine).
At least two dimensions already created (for example, DimData and DimProduto).
Basic SQL knowledge (CREATE TABLE and INSERT).
A tool to run queries, such as SQL Server Management Studio or Azure Data Studio.

Step 1: Define the grain of the fact table

The grain is the most important question: "what does one row of this table represent?". Before writing any SQL, decide the level of detail. In this example, the grain is "one row per product sold in each transaction". A clear grain prevents counting errors and duplication later on.

Step 2: Identify the dimensions and the measures

With the grain defined, split the columns into two groups. The foreign keys connect the fact table to the dimensions (who, what, when). The measures are the numeric values you will sum or calculate. Most measures are additive, meaning they can be safely summed across every dimension. In our sales case:

Keys: DataKey, ProdutoKey, ClienteKey.
Measures: Quantidade, ValorVenda, Desconto.

Step 3: Create the fact table in SQL

Now we translate the design into a CREATE TABLE statement. Each foreign key points to the surrogate key of the matching dimension, and the measures use numeric types with decimals for monetary values.

CREATE TABLE FactVendas (
    VendaID      BIGINT IDENTITY(1,1) PRIMARY KEY,
    DataKey      INT NOT NULL,
    ProdutoKey   INT NOT NULL,
    ClienteKey   INT NOT NULL,
    Quantidade   INT NOT NULL,
    ValorVenda   DECIMAL(12,2) NOT NULL,
    Desconto     DECIMAL(12,2) NOT NULL DEFAULT 0,
    CONSTRAINT FK_Vendas_Data
        FOREIGN KEY (DataKey) REFERENCES DimData(DataKey),
    CONSTRAINT FK_Vendas_Produto
        FOREIGN KEY (ProdutoKey) REFERENCES DimProduto(ProdutoKey),
    CONSTRAINT FK_Vendas_Cliente
        FOREIGN KEY (ClienteKey) REFERENCES DimCliente(ClienteKey)
);

Step 4: Load data using the dimension keys

The fact table never stores text like "Lisboa" or "Teclado"; it stores the numeric key that came from the dimension. So when loading, you JOIN the source data with each dimension to fetch the right key. The following example inserts a sale with the keys already resolved:

INSERT INTO FactVendas (DataKey, ProdutoKey, ClienteKey, Quantidade, ValorVenda, Desconto)
SELECT d.DataKey, p.ProdutoKey, c.ClienteKey, 3, 149.70, 0
FROM   DimData    d
JOIN   DimProduto p ON p.CodigoProduto = 'TEC-001'
JOIN   DimCliente c ON c.Email = 'ana@exemplo.pt'
WHERE  d.DataCompleta = '2026-07-04';

Notice there are no descriptive columns in the fact table: everything about "who" and "what" lives in the dimensions.

Step 5: Improve performance with an index

BI queries almost always filter and group by the dimension keys. Creating an index on those columns helps the engine find the right rows without reading the whole table, which is essential when the fact table grows to millions of rows.

CREATE INDEX IX_FactVendas_Data
    ON FactVendas (DataKey);

CREATE INDEX IX_FactVendas_Produto
    ON FactVendas (ProdutoKey);

Verify the result

To confirm the load went well, count the rows and check that every key has a match in the dimensions. A healthy fact table has no orphan keys:

SELECT COUNT(*) AS TotalLinhas,
       SUM(ValorVenda) AS TotalVendido
FROM   FactVendas;

-- Procurar chaves sem dimensao (deve devolver 0 linhas)
SELECT f.VendaID
FROM   FactVendas f
LEFT JOIN DimProduto p ON p.ProdutoKey = f.ProdutoKey
WHERE  p.ProdutoKey IS NULL;

If the second query returns no rows, every sale points to a valid product.

Conclusion

You have created a fact table linked to the dimensions by keys and ready to feed reports in Power BI or any BI tool. The natural next step is to automate the load with an ETL process and keep an eye on the grain as new sources arrive. What will be the first measure your team wants to analyze?