Government Budget Execution & Fund Accounting Analytics data model
Kimball warehouse with two facts: an appropriation-grain budget fact (adopted, amended, transfers, final budget per fund/program/object-class/version) and an execution-grain posting fact (encumbrance, expenditure, revenue, transfer, available balance). Conformed dimensions cover GASB fund classification, programs and departments, uniform object classes, budget versions (adopted vs amended), vendors, grant funding sources (with CFDA), and a transaction-type junk dimension. Designed for Snowflake; built-in tagging for GASB / SOX compliance reporting.
Public-sector budget execution and fund accounting analytics for state and local governments. Models GASB fund accounting (governmental, proprietary, fiduciary) with the appropriation -> encumbrance -> expenditure lifecycle against adopted and amended budgets, by fund, program, department, object class, vendor, and grant funding source. Supports CAFR-aligned fund-balance reporting, budget-versus-actual, encumbrance reports for unliquidated commitments, budget-execution rate, and available appropriation. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 12
- tables
- 100
- columns
- 16
- relationships
- 2
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Fund accounting at the appropriation grain
GASB fund accounting requires that legal spending authority be tracked by fund + program + object class. An appropriation-grain fact keyed by fund / program / department / object class / fiscal period / budget version reproduces the legal budget book and supports the CAFR fund-balance roll-up.
Separate encumbrance and expenditure tracking
Unlike corporate ledgers, government budgets reserve spending authority via encumbrances (POs / commitments) before expenditures liquidate them. Carrying encumbrance, pre-encumbrance, expenditure, and available_balance side-by-side on the execution fact lets the warehouse produce encumbrance reports and unliquidated-commitment balances without re-deriving them.
Object class for uniform expenditure classification
Object class (personnel, materials, contractual, capital outlay, debt service, transfers) is the uniform GFOA/NACo expenditure classification required for comparable cross-jurisdictional and Census of Governments reporting. Modelling it as a conformed dimension on both facts keeps reporting consistent.
Multi-version budget dimension for adopted-vs-amended variance
Governments routinely amend the adopted budget mid-year (supplemental appropriations, transfers between programs). A budget-version dimension lets the same appropriation grain carry adopted, amended, and working versions for legally-required variance reporting and year-end final-budget vs actual reconciliations.
Grant / funding-source dimension for federal-funds and CFDA tracking
Federal pass-through funds drive Single Audit / Uniform Guidance obligations and require expenditures be tracked by CFDA / Assistance Listing number. A funding-source dimension on both facts enables Schedule of Expenditures of Federal Awards (SEFA) reporting and restricted-vs-unrestricted fund-balance classification.
Tables in the Government Budget Execution & Fund Accounting Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (2)
fct_budget_appropriation
Grain: one row per fund × program × object class × fiscal period × budget version (adopted/amended/transferred)
Appropriation-grain budget fact — adopted, amended, and transferred spending authority.
| Column | Type | Keys |
|---|---|---|
| budget_appropriation_key | bigint | PK |
| fiscal_period_key | bigint | |
| fund_key | bigint | |
| program_key | bigint | |
| department_key | bigint | |
| object_class_key | bigint | |
| budget_version_key | bigint | |
| grant_funding_source_key | bigint | |
| appropriation_ordinance_id | varchar | |
| adopted_budget | decimal | |
| amended_budget | decimal | |
| transfers_in | decimal | |
| transfers_out | decimal | |
| final_budget | decimal | |
| currency | varchar |
fct_budget_execution
Grain: one row per fund × program × object class × fiscal period × posting type (encumbrance/expenditure/revenue)
Execution-grain fact for encumbrance/expenditure/revenue/transfer postings against appropriations.
| Column | Type | Keys |
|---|---|---|
| budget_execution_key | bigint | PK |
| posting_date_key | bigint | |
| fiscal_period_key | bigint | |
| fund_key | bigint | |
| program_key | bigint | |
| department_key | bigint | |
| object_class_key | bigint | |
| vendor_key | bigint | |
| grant_funding_source_key | bigint | |
| transaction_type_key | bigint | |
| accounting_document_id | varchar | |
| purchase_order_id | varchar | |
| actual_expenditure | decimal | |
| encumbrance_amount | decimal | |
| pre_encumbrance | decimal | |
| revenue_amount | decimal | |
| transfer_amount | decimal | |
| available_balance | decimal | |
| currency | varchar |
Dimension tables (10)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_month | smallint | |
| month_number | smallint | |
| month_name | varchar | |
| quarter | smallint | |
| year | smallint | |
| is_business_day | boolean |
dim_fiscal_period
Grain: one row per government fiscal period (typically Jul-Jun or Oct-Sep)
| Column | Type | Keys |
|---|---|---|
| fiscal_period_key | bigint | PK |
| fiscal_period_code | varchar | BK |
| fiscal_year | smallint | |
| fiscal_period_number | smallint | |
| period_start_date | date | |
| period_end_date | date | |
| is_period_closed | boolean | |
| biennial_indicator | boolean |
dim_fund
Grain: one row per fund — GASB-classified governmental / proprietary / fiduciary
| Column | Type | Keys |
|---|---|---|
| fund_key | bigint | PK |
| fund_code | varchar | BK |
| fund_name | varchar | |
| fund_type | varchar | |
| fund_category | varchar | |
| is_major_fund | boolean | |
| gasb_classification | varchar |
dim_program
Grain: one row per government program (the appropriation unit)
| Column | Type | Keys |
|---|---|---|
| program_key | bigint | PK |
| program_code | varchar | BK |
| program_name | varchar | |
| program_category | varchar | |
| strategic_goal | varchar | |
| function | varchar |
dim_department
Grain: one row per organizational department / agency
| Column | Type | Keys |
|---|---|---|
| department_key | bigint | PK |
| department_code | varchar | BK |
| department_name | varchar | |
| agency | varchar | |
| division | varchar | |
| manager | varchar | PII |
| organization_level | smallint |
dim_object_class
Grain: one row per object class — uniform expenditure classification
| Column | Type | Keys |
|---|---|---|
| object_class_key | bigint | PK |
| object_class | varchar | BK |
| object_class_name | varchar | |
| object_category | varchar | |
| naco_uniform_chart | varchar |
dim_budget_version
Grain: one row per budget version (adopted / amended / working / final)
| Column | Type | Keys |
|---|---|---|
| budget_version_key | bigint | PK |
| budget_version | varchar | BK |
| version_name | varchar | |
| version_date | date | |
| approved_by | varchar | |
| status | varchar |
dim_vendor
Grain: one row per vendor / payee receiving public funds
| Column | Type | Keys |
|---|---|---|
| vendor_key | bigint | PK |
| vendor_id | varchar | BK |
| vendor_name | varchar | PII |
| vendor_type | varchar | |
| naics_code | varchar | |
| is_minority_owned | boolean | |
| is_active | boolean |
dim_grant_funding_source
Grain: one row per grant or funding source — federal / state / local / private
| Column | Type | Keys |
|---|---|---|
| grant_funding_source_key | bigint | PK |
| funding_source | varchar | BK |
| source_name | varchar | |
| source_type | varchar | |
| cfda_number | varchar | |
| grant_award_amount | decimal | |
| is_restricted | boolean |
dim_transaction_type
Grain: one row per posting transaction type (junk dimension of posting flags)
| Column | Type | Keys |
|---|---|---|
| transaction_type_key | bigint | PK |
| transaction_type | varchar | BK |
| description | varchar | |
| posting_category | varchar | |
| is_budgetary | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_budget_appropriation.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_budget_appropriation.fund_key → dim_fund (many-to-one)
- fct_budget_appropriation.program_key → dim_program (many-to-one)
- fct_budget_appropriation.department_key → dim_department (many-to-one)
- fct_budget_appropriation.object_class_key → dim_object_class (many-to-one)
- fct_budget_appropriation.budget_version_key → dim_budget_version (many-to-one)
- fct_budget_appropriation.grant_funding_source_key → dim_grant_funding_source (many-to-one)
- fct_budget_execution.posting_date_key → dim_date (many-to-one)
- fct_budget_execution.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_budget_execution.fund_key → dim_fund (many-to-one)
- fct_budget_execution.program_key → dim_program (many-to-one)
- fct_budget_execution.department_key → dim_department (many-to-one)
- fct_budget_execution.object_class_key → dim_object_class (many-to-one)
- fct_budget_execution.vendor_key → dim_vendor (many-to-one)
- fct_budget_execution.grant_funding_source_key → dim_grant_funding_source (many-to-one)
- fct_budget_execution.transaction_type_key → dim_transaction_type (many-to-one)
Related templates
All templates →- Data Vault 2.0
Public Sector / Government EDW (Data Vault 2.0)
Public Sector & Nonprofit
- Data Vault 2.0
Government Tax & Revenue EDW (Data Vault 2.0)
Public Sector & Nonprofit
- Data Vault 2.0
Nonprofit / NGO EDW (Data Vault 2.0)
Public Sector & Nonprofit
- 3NF
Citizen Services 311 & Permits — Normalized 3NF
Public Sector & Nonprofit
From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.