Government Tax & Revenue EDW data model
Data Vault 2.0 raw vault for tax & revenue: hubs (taxpayer/tax_account/tax_return/payment/jurisdiction), links (taxpayer-account, account-return, account-jurisdiction, return-payment), and satellites (taxpayer, account, return, payment details + account-status effectivity + assessment). Hash keys and load metadata are generated by the DDL layer.
Government tax and revenue enterprise data warehouse modeled as a Data Vault 2.0 raw vault: taxpayer, tax account, tax return, payment, and jurisdiction hubs; ownership, filing, and jurisdiction links plus a non-historized return-payment link; and descriptive, assessment, and effectivity satellites — built for integrating registration, filing, collections, and payment systems with the full historization and audit lineage (GDPR, SOX) a revenue authority must retain. It is a Data Vault 2.0 raw vault: hubs for business keys, links for relationships and satellites for descriptive history, with hash keys and hash diffs generated in the DDL.
- 16
- tables
- 102
- columns
- 14
- relationships
- 7
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Hubs hold only business keys
Isolating immutable identifiers (taxpayer_id, tax_account_number, return_id) into hubs lets new collection and audit source systems attach without reworking existing structures — the core agility of Data Vault for an agency integrating decades of legacy systems.
Non-historized return-payment link
Payments are immutable financial events, so the return-payment link is insert-only and carries its measures directly — no satellite needed.
Effectivity satellite for account collection status
A tax account moves between active, delinquent, installment-plan, and lien states; an effectivity satellite records the valid period of each status for as-of and statute-of-limitations reporting.
Assessment split into its own satellite
Audit and penalty determinations arrive on a different cadence and from different units than the filed return; a dedicated assessment satellite keeps audit history queryable and the return satellite stable.
Auditability and GDPR drive Data Vault for tax
Load_date + record_source on every row give a queryable audit trail for SOX controls, while national_id full-masking and per-attribute classification satisfy GDPR data-minimisation on taxpayer PII.
Tables in the Government Tax & Revenue EDW 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.
Hubs (5)
hub_taxpayer
Grain: one row per unique hub_taxpayer business key
| Column | Type | Keys |
|---|---|---|
| taxpayer_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_tax_account
Grain: one row per unique hub_tax_account business key
| Column | Type | Keys |
|---|---|---|
| tax_account_number | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_tax_return
Grain: one row per unique hub_tax_return business key
| Column | Type | Keys |
|---|---|---|
| return_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_payment
Grain: one row per unique hub_payment business key
| Column | Type | Keys |
|---|---|---|
| payment_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_jurisdiction
Grain: one row per unique hub_jurisdiction business key
| Column | Type | Keys |
|---|---|---|
| jurisdiction_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_taxpayer_account
Grain: one row per lnk_taxpayer_account relationship
Which taxpayer owns which tax account.
| Column | Type | Keys |
|---|---|---|
| taxpayer_hk | binary | |
| tax_account_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_account_return
Grain: one row per lnk_account_return relationship
Which tax account a return was filed against.
| Column | Type | Keys |
|---|---|---|
| tax_account_hk | binary | |
| tax_return_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_account_jurisdiction
Grain: one row per lnk_account_jurisdiction relationship
Which jurisdiction administers a tax account.
| Column | Type | Keys |
|---|---|---|
| tax_account_hk | binary | |
| jurisdiction_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_return_payment
Grain: one row per posted payment against a return (non-historized link)
Transactional (non-historized) link — insert-only payment events.
| Column | Type | Keys |
|---|---|---|
| tax_return_hk | binary | |
| payment_hk | binary | |
| payment_event_id | varchar | BK |
| payment_ts | timestamp | |
| payment_method | varchar | |
| payment_status | varchar | |
| amount | decimal | |
| currency | varchar | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_taxpayer_details
Grain: one row per taxpayer_hk per change
Taxpayer descriptive history (PII).
| Column | Type | Keys |
|---|---|---|
| taxpayer_hk | binary | PK |
| load_date | timestamp | PK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| date_of_birth | date | PII |
| national_id | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| mailing_address | varchar | PII |
| taxpayer_type | varchar | |
| filing_status | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_account_details
Grain: one row per tax_account_hk per change
Tax account descriptive history.
| Column | Type | Keys |
|---|---|---|
| tax_account_hk | binary | PK |
| load_date | timestamp | PK |
| tax_type | varchar | |
| account_status | varchar | |
| open_date | date | |
| fiscal_year_end | date | |
| outstanding_balance | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_return_details
Grain: one row per tax_return_hk per change
Tax return descriptive history.
| Column | Type | Keys |
|---|---|---|
| tax_return_hk | binary | PK |
| load_date | timestamp | PK |
| tax_period | varchar | |
| filing_date | date | |
| filing_channel | varchar | |
| gross_income | decimal | |
| taxable_income | decimal | |
| tax_due | decimal | |
| credits_claimed | decimal | |
| return_status | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_payment_details
Grain: one row per payment_hk per change
Payment descriptive history.
| Column | Type | Keys |
|---|---|---|
| payment_hk | binary | PK |
| load_date | timestamp | PK |
| payment_channel | varchar | |
| confirmation_number | varchar | |
| processing_fee | decimal | |
| reversal_reason | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_account_status
Grain: one row per account status per effectivity change
Effectivity satellite tracking tax-account collection status over time.
| Column | Type | Keys |
|---|---|---|
| tax_account_hk | binary | PK |
| load_date | timestamp | PK |
| status_code | varchar | |
| effective_from | date | |
| effective_to | date | |
| is_active | boolean | |
| status_reason | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_assessment
Grain: one row per tax_return_hk per change
Assessment satellite — audit and penalty determinations on a return.
| Column | Type | Keys |
|---|---|---|
| tax_return_hk | binary | PK |
| load_date | timestamp | PK |
| assessment_type | varchar | |
| assessed_amount | decimal | |
| penalty_amount | decimal | |
| interest_amount | decimal | |
| assessment_date | date | |
| assessor_unit | varchar | |
| assessment_status | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_currency
Grain: one row per ISO currency
| Column | Type | Keys |
|---|---|---|
| currency_code | varchar | BK |
| currency_name | varchar | |
| minor_units | smallint |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_taxpayer_account.taxpayer_hk → hub_taxpayer (many-to-one)
- lnk_taxpayer_account.tax_account_hk → hub_tax_account (many-to-one)
- lnk_account_return.tax_account_hk → hub_tax_account (many-to-one)
- lnk_account_return.tax_return_hk → hub_tax_return (many-to-one)
- lnk_account_jurisdiction.tax_account_hk → hub_tax_account (many-to-one)
- lnk_account_jurisdiction.jurisdiction_hk → hub_jurisdiction (many-to-one)
- lnk_return_payment.tax_return_hk → hub_tax_return (many-to-one)
- lnk_return_payment.payment_hk → hub_payment (many-to-one)
- sat_taxpayer_details.taxpayer_hk → hub_taxpayer (many-to-one)
- sat_account_details.tax_account_hk → hub_tax_account (many-to-one)
- sat_return_details.tax_return_hk → hub_tax_return (many-to-one)
- sat_payment_details.payment_hk → hub_payment (many-to-one)
- sat_account_status.tax_account_hk → hub_tax_account (many-to-one)
- sat_assessment.tax_return_hk → hub_tax_return (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
Nonprofit / NGO EDW (Data Vault 2.0)
Public Sector & Nonprofit
- Kimball
Government Budget Execution & Fund Accounting Analytics
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.