Budget-NEP Program Correlation

Year-by-year comparison of infrastructure programs across Budget and NEP databases

🚨 Critical Database Discovery

Budget & NEP Databases Do NOT Match 1-to-1

📊 Budget Database

Tables: budget_2017 to budget_2025
Columns: 24 per table
Amount Column: amt (numeric)
Description: dsc (text)
UACS Codes: bigint fields

📈 NEP Database

Tables: budget_2020 to budget_2026
Columns: 16 per table
Amount Column: amount (numeric)
Description: description (text)
UACS Codes: varchar fields
⚠️

Impact: Despite schema differences, the NEP integration is successful because adapted queries handle the column mapping differences transparently.

🔍 NEP-Budget Database Correlation Analysis

Table Overlap

6 Years

2020-2025 shared between databases

Schema Difference

8 Columns

Budget: 24 cols, NEP: 16 cols

Data Types

Adapted

bigint → varchar for UACS codes

📋 Detailed Column Mapping

Aspect Budget Database NEP Database Adaptation
Primary Key id (integer) id (integer) ✓ Identical
Amount Field amt (numeric) amount (numeric) → Column name change
Description dsc (text) description (text) → Column name change
Fiscal Year year (integer) fiscal_year (varchar) → Name + type change
Department Code department (bigint) org_uacs_code (varchar) → Name + type change
Agency Code agency (bigint) region_code (varchar) → Name + type change
Sort Order sorder (bigint) sort_order (bigint) → Column name change
Total Columns 24 columns 16 columns → Schema reduction

✅ Successful Integration Despite Differences

🔧 Technical Adaptations

  • ✓ Query column name mapping (amt → amount)
  • ✓ WHERE condition adjustments for nullable fields
  • ✓ Unified API endpoints serve both databases
  • ✓ Identical frontend interface for different backends

📊 User Experience

  • ✓ Seamless navigation between Budget and NEP
  • ✓ Identical data browsing and filtering
  • ✓ Same visualization and analysis tools
  • ✓ Transparent database differences to users

📈 Current Data Statistics

💰 Budget Database (2017-2025)

Coverage: 9 fiscal years
Schema: 24 columns each
Detail Level: High (UACS breakdowns)
Data Types: bigint for codes

📈 NEP Database (2020-2026)

Coverage: 7 fiscal years
Schema: 16 columns each
Detail Level: Medium (simplified)
Data Types: varchar for codes

Conclusion: Despite fundamental schema differences, the NEP integration provides users with the same powerful analysis capabilities through transparent query adaptations.