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,sqlalchemyandpsycopg2-binaryinstalled - 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:
- Source: CSV extracts (or direct queries) from the accounting / ERP system
- Staging: raw copies of the source data, loaded as-is, so we can always trace back
- 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:
| Table | Type | Grain / purpose |
|---|---|---|
| fact_gl_transactions | Fact | One row per general ledger journal line |
| fact_budget | Fact | One row per account × cost center × entity × month |
| dim_date | Dimension | One row per day, with fiscal year and period |
| dim_account | Dimension | Chart of accounts with account type and P&L group |
| dim_cost_center | Dimension | Departments / cost centers and their region |
| dim_entity | Dimension | Legal entities and their currency |
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 useFLOATfor financial amounts, because it causes rounding errors.net_amountis 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_entityStep 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!
Leave a Reply