Author: admin

Finance teams live in spreadsheets: monthly P&L packs, budget-vs-actual reports, cost center reviews. Most of that work is copying numbers out of the accounting system and reshaping them by hand. A finance data mart fixes this by giving finance one clean, trusted place to report from.

In this post I’ll walk through a complete finance data mart project, end to end, using SQL for the data model and Python for the ETL. The examples use PostgreSQL, but the same design works on SQL Server, MySQL, Snowflake or any other relational database.

What you’ll learn

• What a data mart is and how it differs from a data warehouse
• How to turn finance requirements into a star schema
• How to build the tables in SQL
• How to load them with a simple, re-runnable Python ETL
• How to check data quality and write P&L and budget-vs-actual queries

Prerequisites

• Comfortable with basic SQL (SELECT, JOIN, GROUP BY)
• Python 3 with pandas, sqlalchemy and psycopg2-binary installed
• A PostgreSQL database (a local install or Docker works fine)
• Some CSV extracts from an accounting system: chart of accounts, cost centers, entities, journal lines and budget

What is a data mart?

A data warehouse holds data for the whole organisation. A data mart is a smaller, subject-focused slice of it, built for one team. A finance data mart only contains what finance needs: the general ledger, the chart of accounts, cost centers, entities, budgets and a calendar that understands the fiscal year.

Because it’s focused, a data mart is faster to build, easier to understand, and much quicker to query.

Step 1: Gather the business requirements

Before writing any SQL, sit down with the finance team and ask what questions they need answered. For this project the requirements are:

• Monthly Profit & Loss by P&L group (Sales, Payroll, Marketing, …)
• Budget vs. actual by cost center and account, with variance and variance %
• Reports by fiscal year and period (here, April to March)
• Drill-down from totals to individual journal lines
• Numbers that reconcile exactly to the general ledger

These requirements decide the grain of our tables (how detailed one row is) and which dimensions we need to slice by.

Step 2: Design the architecture

The data flows through three layers:

1. Source: CSV extracts (or direct queries) from the accounting / ERP system
2. Staging: raw copies of the source data, loaded as-is, so we can always trace back
3. Data mart: clean dimension and fact tables in a star schema, ready for reporting

Reporting tools such as Power BI, Tableau, Excel or plain SQL then read from the data mart layer only.

Step 3: Model the star schema

A star schema has fact tables (the numbers) in the middle, surrounded by dimension tables (the things you filter and group by). Here’s our model:

Notice that the two fact tables have different grains: actuals are stored per journal line, and budgets per month. That’s normal. We’ll line them up at month level when we compare them.

Step 4: Create the tables in SQL

CREATE SCHEMA IF NOT EXISTS staging;
CREATE SCHEMA IF NOT EXISTS finance_mart;

-- Date dimension (one row per day)
CREATE TABLE finance_mart.dim_date (
    date_key       INT PRIMARY KEY,          -- e.g. 20260131
    full_date      DATE NOT NULL,
    month_num      SMALLINT NOT NULL,
    month_name     VARCHAR(3) NOT NULL,
    quarter_num    SMALLINT NOT NULL,
    fiscal_year    SMALLINT NOT NULL,
    fiscal_period  SMALLINT NOT NULL          -- 1..12 within the fiscal year
);

-- Chart of accounts
CREATE TABLE finance_mart.dim_account (
    account_key    SERIAL PRIMARY KEY,
    account_code   VARCHAR(20) NOT NULL UNIQUE,
    account_name   VARCHAR(100) NOT NULL,
    account_type   VARCHAR(20) NOT NULL,     -- Revenue, Expense, Asset, Liability, Equity
    pl_group       VARCHAR(50)               -- e.g. 'Sales', 'Payroll', 'Marketing'
);

-- Cost centers / departments
CREATE TABLE finance_mart.dim_cost_center (
    cost_center_key   SERIAL PRIMARY KEY,
    cost_center_code  VARCHAR(20) NOT NULL UNIQUE,
    cost_center_name  VARCHAR(100) NOT NULL,
    department        VARCHAR(50),
    region            VARCHAR(50)
);

-- Legal entities / companies
CREATE TABLE finance_mart.dim_entity (
    entity_key     SERIAL PRIMARY KEY,
    entity_code    VARCHAR(20) NOT NULL UNIQUE,
    entity_name    VARCHAR(100) NOT NULL,
    currency_code  CHAR(3) NOT NULL
);

-- Fact: general ledger journal lines (grain = one journal line)
CREATE TABLE finance_mart.fact_gl_transactions (
    gl_txn_key       BIGSERIAL PRIMARY KEY,
    date_key         INT NOT NULL REFERENCES finance_mart.dim_date(date_key),
    account_key      INT NOT NULL REFERENCES finance_mart.dim_account(account_key),
    cost_center_key  INT NOT NULL REFERENCES finance_mart.dim_cost_center(cost_center_key),
    entity_key       INT NOT NULL REFERENCES finance_mart.dim_entity(entity_key),
    journal_id       VARCHAR(30) NOT NULL,
    debit_amount     NUMERIC(18,2) NOT NULL DEFAULT 0,
    credit_amount    NUMERIC(18,2) NOT NULL DEFAULT 0,
    net_amount       NUMERIC(18,2) GENERATED ALWAYS AS (debit_amount - credit_amount) STORED,
    load_ts          TIMESTAMP NOT NULL DEFAULT now()
);

-- Fact: monthly budget (grain = account x cost center x entity x month)
CREATE TABLE finance_mart.fact_budget (
    date_key         INT NOT NULL REFERENCES finance_mart.dim_date(date_key),  -- first day of month
    account_key      INT NOT NULL REFERENCES finance_mart.dim_account(account_key),
    cost_center_key  INT NOT NULL REFERENCES finance_mart.dim_cost_center(cost_center_key),
    entity_key       INT NOT NULL REFERENCES finance_mart.dim_entity(entity_key),
    budget_amount    NUMERIC(18,2) NOT NULL,
    PRIMARY KEY (date_key, account_key, cost_center_key, entity_key)
);

A few design choices worth pointing out:

• Surrogate keys (account_key, etc.) keep the fact tables small and protect you if a source code changes.
• NUMERIC(18,2) is used for money. Never use FLOAT for financial amounts, because it causes rounding errors.
• net_amount is a generated column (debit minus credit), so every query uses the same sign convention.

Step 5: Build the date dimension in Python

The date dimension is generated rather than extracted. This is where fiscal-year logic lives, so it only has to be written once.

import pandas as pd
from sqlalchemy import create_engine, text

# Update with your own connection details
engine = create_engine("postgresql+psycopg2://user:password@localhost:5432/finance")

def build_dim_date(start="2024-01-01", end="2027-12-31", fy_start_month=4):
    """Build a date dimension. fy_start_month=4 means the fiscal year runs April to March."""
    d = pd.DataFrame({"full_date": pd.date_range(start, end, freq="D")})
    d["date_key"] = d["full_date"].dt.strftime("%Y%m%d").astype(int)
    d["month_num"] = d["full_date"].dt.month
    d["month_name"] = d["full_date"].dt.strftime("%b")
    d["quarter_num"] = d["full_date"].dt.quarter
    # Apr 2026 - Mar 2027 is labelled FY2027
    rolls_over = (d["month_num"] >= fy_start_month) & (fy_start_month > 1)
    d["fiscal_year"] = d["full_date"].dt.year + rolls_over.astype(int)
    d["fiscal_period"] = (d["month_num"] - fy_start_month) % 12 + 1
    d["full_date"] = d["full_date"].dt.date
    return d[["date_key", "full_date", "month_num", "month_name",
              "quarter_num", "fiscal_year", "fiscal_period"]]

build_dim_date().to_sql("dim_date", engine, schema="finance_mart",
                        if_exists="append", index=False)

Step 6: Load the staging layer

Staging is a straight copy of the source files. Keeping it untouched means you can always compare the data mart back to what the source system gave you.

def load_staging(csv_path, table_name):
    """Copy a raw extract into the staging schema as-is."""
    df = pd.read_csv(csv_path)
    df.columns = [c.strip().lower() for c in df.columns]
    df.to_sql(table_name, engine, schema="staging", if_exists="replace", index=False)
    print(f"Loaded {len(df):,} rows into staging.{table_name}")

load_staging("exports/chart_of_accounts.csv", "gl_accounts")
load_staging("exports/cost_centers.csv",      "cost_centers")
load_staging("exports/entities.csv",          "entities")
load_staging("exports/journal_lines.csv",     "gl_journal_lines")
load_staging("exports/budget.csv",            "budget")

Step 7: Load the dimensions

Dimensions are loaded with an upsert: new accounts are inserted, and existing ones are updated if their name or grouping changed.

INSERT INTO finance_mart.dim_account (account_code, account_name, account_type, pl_group)
SELECT DISTINCT account_code, account_name, account_type, pl_group
FROM staging.gl_accounts
ON CONFLICT (account_code) DO UPDATE
SET account_name = EXCLUDED.account_name,
    account_type = EXCLUDED.account_type,
    pl_group     = EXCLUDED.pl_group;

-- Repeat the same pattern for dim_cost_center and dim_entity

Step 8: Load the facts

The fact load looks up the surrogate keys from each dimension. It first deletes the period being loaded, so the job is idempotent: you can safely re-run it after a late journal or a failed load.

def load_gl_facts(start_date, end_date):
    """Reload GL facts for a period. Deleting first makes the job safe to re-run."""
    start_key = int(start_date.replace("-", ""))
    end_key = int(end_date.replace("-", ""))
    with engine.begin() as conn:   # one transaction: all or nothing
        conn.execute(text("""
            DELETE FROM finance_mart.fact_gl_transactions
            WHERE date_key BETWEEN :s AND :e
        """), {"s": start_key, "e": end_key})

        result = conn.execute(text("""
            INSERT INTO finance_mart.fact_gl_transactions
                (date_key, account_key, cost_center_key, entity_key,
                 journal_id, debit_amount, credit_amount)
            SELECT TO_CHAR(s.posting_date::date, 'YYYYMMDD')::INT,
                   a.account_key, c.cost_center_key, e.entity_key,
                   s.journal_id, COALESCE(s.debit, 0), COALESCE(s.credit, 0)
            FROM staging.gl_journal_lines s
            JOIN finance_mart.dim_account     a ON a.account_code     = s.account_code
            JOIN finance_mart.dim_cost_center c ON c.cost_center_code = s.cost_center_code
            JOIN finance_mart.dim_entity      e ON e.entity_code      = s.entity_code
            WHERE s.posting_date::date BETWEEN :sd AND :ed
        """), {"sd": start_date, "ed": end_date})
    print(f"Loaded {result.rowcount:,} GL lines for {start_date} to {end_date}")

load_gl_facts("2026-04-01", "2026-09-30")

Budgets are loaded the same way into fact_budget, using the first day of each month as the date_key.

Step 9: Check data quality

Finance users will only trust the data mart if the numbers tie out. Run these checks after every load:

-- 1. Every journal must balance (debits = credits)
SELECT journal_id, SUM(net_amount) AS imbalance
FROM finance_mart.fact_gl_transactions
GROUP BY journal_id
HAVING ABS(SUM(net_amount)) > 0.01;

-- 2. Staging rows that were dropped because the account is not in dim_account
SELECT DISTINCT s.account_code
FROM staging.gl_journal_lines s
LEFT JOIN finance_mart.dim_account a ON a.account_code = s.account_code
WHERE a.account_key IS NULL;

-- 3. Row counts and totals should match the source extract
SELECT COUNT(*) AS lines, SUM(debit_amount) AS total_debits, SUM(credit_amount) AS total_credits
FROM finance_mart.fact_gl_transactions;

Check 2 is important: an inner join silently drops lines with unknown codes. Any account showing up there needs to be added to the chart of accounts before the numbers will reconcile.

Step 10: Report on it

Monthly P&L by group

SELECT d.fiscal_year,
       d.fiscal_period,
       a.pl_group,
       -- Revenue is stored as a credit (negative net), so flip its sign for reporting
       SUM(CASE WHEN a.account_type = 'Revenue' THEN -f.net_amount
                ELSE f.net_amount END) AS amount
FROM finance_mart.fact_gl_transactions f
JOIN finance_mart.dim_date    d ON d.date_key    = f.date_key
JOIN finance_mart.dim_account a ON a.account_key = f.account_key
WHERE a.account_type IN ('Revenue', 'Expense')
GROUP BY d.fiscal_year, d.fiscal_period, a.pl_group
ORDER BY d.fiscal_year, d.fiscal_period, a.pl_group;

Budget vs. actual with variance

WITH actual AS (
    SELECT d.fiscal_year, d.fiscal_period, f.account_key, f.cost_center_key,
           SUM(f.net_amount) AS actual_amt
    FROM finance_mart.fact_gl_transactions f
    JOIN finance_mart.dim_date d ON d.date_key = f.date_key
    GROUP BY 1, 2, 3, 4
),
budget AS (
    SELECT d.fiscal_year, d.fiscal_period, b.account_key, b.cost_center_key,
           SUM(b.budget_amount) AS budget_amt
    FROM finance_mart.fact_budget b
    JOIN finance_mart.dim_date d ON d.date_key = b.date_key
    GROUP BY 1, 2, 3, 4
)
SELECT b.fiscal_year,
       b.fiscal_period,
       c.cost_center_name,
       a.account_name,
       b.budget_amt,
       COALESCE(ac.actual_amt, 0)                 AS actual_amt,
       COALESCE(ac.actual_amt, 0) - b.budget_amt  AS variance,
       ROUND(100.0 * (COALESCE(ac.actual_amt, 0) - b.budget_amt)
             / NULLIF(b.budget_amt, 0), 1)        AS variance_pct
FROM budget b
LEFT JOIN actual ac USING (fiscal_year, fiscal_period, account_key, cost_center_key)
JOIN finance_mart.dim_account     a ON a.account_key     = b.account_key
JOIN finance_mart.dim_cost_center c ON c.cost_center_key = b.cost_center_key
WHERE a.account_type = 'Expense'
ORDER BY variance DESC;

Point Power BI or Excel at these queries (or turn them into views) and the finance team has a self-service P&L and budget-vs-actual report that refreshes itself.

Best practices I’d recommend

• Agree on the sign convention (debits positive, credits negative) early and document it.
• Schedule the ETL with cron, Windows Task Scheduler or Airflow, and log row counts for every run.
• Keep history in dimensions if account groupings change over time (look up “slowly changing dimensions type 2”).
• Add a currency layer if you have multiple entities in different currencies.
• Wrap reports in views so the business logic lives in one place.

Wrapping up

That’s a complete finance data mart: clear requirements, a star schema with GL and budget facts, a re-runnable Python ETL, data quality checks, and ready-to-use P&L and budget-vs-actual queries. Start small with one entity and a few months of data, reconcile it with finance, and then grow it.

In a future post I’ll cover connecting this data mart to Power BI and building the P&L dashboard. If you have questions or want me to cover something specific, leave a comment below!