Flagship case study
BudgetDB is the anchor system: a SQL/Postgres decision layer for budget, software spend, headcount allocation, QA, and leadership reporting.
Business question
The project reframes raw operational and budget data into a reusable reporting system: cleaned tables, analytics-ready views, QA checks, and executive outputs.
BudgetDB code
1CREATE VIEW analytics.v_software_cost_per_employee_company_2025 AS2WITH employee_count AS (3 SELECT COUNT(*)::int AS total_employees_20254 FROM analytics.dim_employee5 WHERE (start_date IS NULL OR start_date <= '2025-12-31'::date)6 AND (end_date IS NULL OR end_date >= '2025-01-01'::date)7),8company_spend AS (9 SELECT SUM(COALESCE(total_spend_2025, 0))::numeric AS total_software_spend_202510 FROM analytics.vendors_2025_clean11)12SELECT13 c.total_software_spend_2025,14 e.total_employees_2025,15 ROUND(c.total_software_spend_2025 / NULLIF(e.total_employees_2025, 0), 2)16 AS software_cost_per_employee_202517FROM company_spend c18CROSS JOIN employee_count e;Architecture
BudgetDB shows the full operating path from source data to trusted executive reporting.
Identity resolution
The commission export and payroll table do not always agree on names. BudgetDB uses a three-tier bridge instead of a silent fuzzy guess: reviewed manual overrides, normalized name keys, then explicit UNMATCHED flags.
Tier 1
Known typos and aliases get explicit reviewed mappings in employee_commission_name_map.
Tier 2
Punctuation, case, and role tags are stripped so formatting noise can match clean dimensions.
Tier 3
Rows that still fail are surfaced in an audit view instead of disappearing from totals.
Full warehouse architecture
1CREATE VIEW analytics.commission_2025_joined_payroll AS2WITH mapped AS (3 SELECT c.*, m.employee_id AS mapped_employee_id4 FROM analytics.commission_2025_clean c5 LEFT JOIN analytics.employee_commission_name_map m6 ON m.commission_employee_name_raw = c.employee_name_raw7)8SELECT9 m.employee_name_raw,10 m.employee_name_clean,11 m.commission_month_date,12 m.commission_amount,13 COALESCE(m.mapped_employee_id, e.employee_id) AS employee_id,14 CASE15 WHEN m.mapped_employee_id IS NOT NULL THEN 'MAP_OVERRIDE'16 WHEN e.employee_id IS NOT NULL THEN 'NAME_KEY_EXACT'17 ELSE 'UNMATCHED'18 END AS match_method,19 CASE20 WHEN COALESCE(m.mapped_employee_id, e.employee_id) IS NULL THEN 'UNMATCHED'21 ELSE 'MATCHED'22 END AS join_status23FROM mapped m24LEFT JOIN analytics.dim_employee e25 ON m.mapped_employee_id IS NULL26 AND m.name_key_full = e.name_key_full;Scenario engine
BudgetDB stores named multiplier sets in a scenario table and applies them to the baseline monthly fact table. A vendor-spend reduction scenario becomes one row insert plus a reusable view, not a rebuilt planning workbook.
Input
Named multiplier sets define payroll, vendor, and commission assumptions over a date window.
Model
The view joins each scenario to fact_cost_monthly_2025 and computes scenario totals automatically.
Output
Leadership can compare baseline versus scenario impact without changing source tables.
Case study
The case study is intentionally structured like a product system: problem, inputs, architecture, validation, output, and impact.
Problem
Operations and budget data often lives across spreadsheets, vendor exports, employee tables, and one-off reporting workflows. The business risk is that leaders make decisions from inconsistent totals, stale calculations, or dashboards without clear QA.
Data sources
Budget workbooks, vendor/software spend, employee and headcount data, department/category mappings, and supporting operational exports. The portfolio version uses anonymized or synthetic data while preserving the structure of the workflow.
Postgres warehouse design
The system is modeled around cleaned dimensions, budget facts, vendor spend, employee/headcount tables, composable views, and executive-facing rollups that can be reused instead of rebuilt for each question.
SQL models and views
Core SQL models calculate software cost per employee, allocate software spend by team, summarize department/category spend, and prepare dashboard-ready outputs for operating reviews.
QA checks
Reconciliation queries compare source totals against modeled fact totals and return variance plus pass/review status before outputs are trusted for executive reporting.
Business impact
BudgetDB demonstrates how product thinking and analytics engineering turn messy operational data into decision infrastructure: faster review cycles, clearer spend ownership, and more trustworthy executive dashboards.
Executive outputs
Screenshots support the story, while the primary evidence is the model and QA logic behind the reporting layer.
