CensusChat Data Architecture

CensusChat Data Architecture

Overview

CensusChat’s data architecture targets sub-2-second query performance over the loaded ACS data (3,144 counties and 239,741 block groups today) while maintaining data quality, consistency, and scalability. The architecture implements a modern lakehouse pattern combining operational and analytical workloads with intelligent caching and optimization strategies.

Data Architecture Layers

graph TB
    subgraph "Data Sources"
        CENSUS[Census Bureau<br/>ACS 5-Year Data<br/>44GB Compressed]
        TIGER[TIGER/Line<br/>Geographic Boundaries<br/>220K+ Areas]
        METADATA[Statistical Metadata<br/>10K+ Variables]
    end
    
    subgraph "Ingestion Layer"
        API_MONITOR[API Monitor<br/>Release Detection]
        EXTRACTOR[Data Extractor<br/>Python/Pandas]
        VALIDATOR[Data Validator<br/>Quality Checks]
        TRANSFORMER[Data Transformer<br/>ETL Pipeline]
    end
    
    subgraph "Raw Storage Layer"
        S3_RAW[S3 Raw Data<br/>Partitioned by Year/Geography<br/>Parquet Format]
        S3_META[S3 Metadata<br/>Schema & Documentation<br/>JSON/YAML]
        S3_BOUNDARIES[S3 Boundaries<br/>Geographic Shapefiles<br/>GeoJSON/Parquet]
    end
    
    subgraph "Processing Layer"
        SPARK[Apache Spark<br/>Distributed ETL<br/>Data Deduplication]
        DUCKDB_ETL[DuckDB ETL<br/>Columnar Optimization<br/>Index Generation]
        GIS_PROCESSOR[GIS Processor<br/>Boundary Processing<br/>Spatial Indexing]
    end
    
    subgraph "Analytical Storage"
        DUCKDB[(DuckDB<br/>Analytics Engine<br/>Columnar Storage)]
        SPATIAL_INDEX[Spatial Index<br/>R-Tree/QuadTree<br/>Geographic Queries]
        PRECOMPUTE[Pre-computed Views<br/>Common Aggregations<br/>Materialized Results]
    end
    
    subgraph "Caching Layer"
        REDIS_QUERY[(Redis Cluster<br/>Query Result Cache<br/>TTL-Based)]
        REDIS_SESSION[(Redis<br/>Session Cache<br/>User State)]
        REDIS_GEO[(Redis<br/>Geographic Cache<br/>Boundary Data)]
    end
    
    subgraph "Operational Storage"
        POSTGRES[(PostgreSQL<br/>User Data<br/>ACID Compliance)]
        CLICKHOUSE[(ClickHouse<br/>Analytics Events<br/>Time-Series)]
        ELASTICSEARCH[(Elasticsearch<br/>Search & Logs<br/>Full-Text Index)]
    end
    
    %% Data Flow Connections
    CENSUS --> API_MONITOR
    TIGER --> API_MONITOR
    METADATA --> API_MONITOR
    
    API_MONITOR --> EXTRACTOR
    EXTRACTOR --> VALIDATOR
    VALIDATOR --> TRANSFORMER
    TRANSFORMER --> S3_RAW
    TRANSFORMER --> S3_META
    TRANSFORMER --> S3_BOUNDARIES
    
    S3_RAW --> SPARK
    S3_BOUNDARIES --> GIS_PROCESSOR
    SPARK --> DUCKDB_ETL
    GIS_PROCESSOR --> DUCKDB_ETL
    
    DUCKDB_ETL --> DUCKDB
    DUCKDB_ETL --> SPATIAL_INDEX
    DUCKDB_ETL --> PRECOMPUTE
    
    DUCKDB --> REDIS_QUERY
    SPATIAL_INDEX --> REDIS_GEO
    
    %% Styling
    classDef source fill:#e3f2fd
    classDef ingestion fill:#f3e5f5
    classDef raw fill:#fff3e0
    classDef processing fill:#e8f5e8
    classDef analytical fill:#ffebee
    classDef cache fill:#f1f8e9
    classDef operational fill:#fce4ec
    
    class CENSUS,TIGER,METADATA source
    class API_MONITOR,EXTRACTOR,VALIDATOR,TRANSFORMER ingestion
    class S3_RAW,S3_META,S3_BOUNDARIES raw
    class SPARK,DUCKDB_ETL,GIS_PROCESSOR processing
    class DUCKDB,SPATIAL_INDEX,PRECOMPUTE analytical
    class REDIS_QUERY,REDIS_SESSION,REDIS_GEO cache
    class POSTGRES,CLICKHOUSE,ELASTICSEARCH operational

Core Data Assets

Census ACS 5-Year Dataset

Dataset Specifications:

• Volume: 44GB compressed, 250+ tables, 10,000+ variables
• Records: 3,144 counties and 239,741 block groups loaded today; designed to extend to further geographic levels
• Coverage: National → State → County → Metro → Tract → Block Group
• Temporal: 2009-present with annual updates
• Quality: 90%+ response rates, government-authoritative methodology

Data Structure:

erDiagram
    GEOGRAPHY {
        string geoid PK
        string name
        string state_code
        string county_code
        enum geography_type
        polygon boundary
        float area_sqmi
        int population
    }
    
    DEMOGRAPHIC_DATA {
        string geoid FK
        string table_id
        string variable_id
        float estimate
        float margin_of_error
        int year
        datetime updated_at
    }
    
    VARIABLE_METADATA {
        string variable_id PK
        string table_id FK
        string concept
        string label
        string universe
        enum data_type
        string formula
    }
    
    TABLE_METADATA {
        string table_id PK
        string title
        string subject
        string universe
        string description
        int variables_count
        datetime release_date
    }
    
    GEOGRAPHY ||--o{ DEMOGRAPHIC_DATA : contains
    VARIABLE_METADATA ||--o{ DEMOGRAPHIC_DATA : describes
    TABLE_METADATA ||--o{ VARIABLE_METADATA : contains

Key Tables and Variables:

Population Demographics:
  - B01001: Sex by Age
  - B25001: Housing Units
  - B08303: Travel Time to Work
  - B19013: Median Household Income

Economic Data:
  - B25064: Median Gross Rent
  - B25077: Median Value (Owner-Occupied Housing)
  - B08124: Means of Transportation to Work
  - C24010: Sex by Occupation

Educational Attainment:
  - B15003: Educational Attainment
  - B14001: School Enrollment
  - B07009: Geographical Mobility by Educational Attainment

Race and Ethnicity:
  - B02001: Race
  - B03002: Hispanic or Latino Origin by Race
  - B25003: Tenure by Race of Householder

Geographic Intelligence

Geographic Hierarchy:

graph TD
    NATION[Nation<br/>United States<br/>1 Entity]
    STATE[States<br/>50 States + DC + PR<br/>52 Entities]
    COUNTY[Counties<br/>3,143 Entities<br/>FIPS Codes]
    METRO[Metro Areas<br/>384 MSAs<br/>Core-Based Statistical]
    TRACT[Census Tracts<br/>85,000+ Entities<br/>~4,000 Population]
    BLOCKGROUP[Block Groups<br/>220,000+ Entities<br/>~1,500 Population]
    
    NATION --> STATE
    STATE --> COUNTY
    COUNTY --> METRO
    COUNTY --> TRACT
    TRACT --> BLOCKGROUP
    
    %% Cross-references
    METRO -.-> COUNTY
    METRO -.-> STATE

Boundary Data Storage:

Storage Format:
  - Raw: TIGER/Line Shapefiles from Census Bureau
  - Processed: GeoJSON and Parquet with spatial indexes
  - Simplified: Multiple resolution levels for performance
  - Cached: Pre-computed boundary relationships

Spatial Indexing:
  - R-Tree index for efficient spatial queries
  - QuadTree index for geographic visualization
  - Boundary intersection pre-computation
  - Distance calculation optimization

Update Frequency:
  - Annual updates from Census Bureau TIGER/Line
  - Immediate updates for boundary corrections
  - Version control for historical boundary changes
  - Automated validation of boundary topology

DuckDB Analytics Engine

Configuration and Optimization

DuckDB Configuration:

-- Memory and Threading
SET memory_limit = '32GB';
SET threads = 16;
SET max_memory = '75%';

-- Performance Optimizations
SET enable_object_cache = true;
SET preserve_insertion_order = false;
SET enable_profiling = true;

-- Storage Configuration
SET temp_directory = '/tmp/duckdb';
SET default_order = 'ASC';

Storage Layout:

graph TB
    subgraph "DuckDB Storage Structure"
        subgraph "Tables"
            GEOGRAPHY_TABLE[geography<br/>Spatial Data + FIPS]
            DEMOGRAPHIC_TABLE[demographic_data<br/>Partitioned by Year]
            METADATA_TABLE[variable_metadata<br/>Lookup Tables]
        end
        
        subgraph "Indexes"
            GEO_INDEX[Geographic Index<br/>geoid, state_code]
            YEAR_INDEX[Temporal Index<br/>year, table_id]
            VARIABLE_INDEX[Variable Index<br/>variable_id, concept]
        end
        
        subgraph "Views"
            POPULATION_VIEW[population_summary<br/>Pre-aggregated by Geography]
            INCOME_VIEW[income_summary<br/>Median Income by Area]
            HOUSING_VIEW[housing_summary<br/>Housing Characteristics]
        end
    end
    
    GEOGRAPHY_TABLE --> GEO_INDEX
    DEMOGRAPHIC_TABLE --> YEAR_INDEX
    METADATA_TABLE --> VARIABLE_INDEX
    
    DEMOGRAPHIC_TABLE --> POPULATION_VIEW
    DEMOGRAPHIC_TABLE --> INCOME_VIEW
    DEMOGRAPHIC_TABLE --> HOUSING_VIEW

Partitioning Strategy:

Temporal Partitioning:
  - Partition by year for time-series queries
  - Separate partitions for each ACS release
  - Automated partition pruning for date ranges
  - Parallel processing across partitions

Geographic Partitioning:
  - State-level partitioning for large queries
  - Regional groupings for cross-state analysis
  - Urban/rural partitioning for demographic patterns
  - FIPS code range partitioning for spatial queries

Variable Partitioning:
  - Subject-based partitioning (demographics, economics, housing)
  - Table-level partitioning for related variables
  - Frequently accessed variables in hot partitions
  - Archive partitions for historical data

Query Optimization Patterns

Common Query Patterns:

-- Geographic Aggregation Pattern
SELECT 
    g.name,
    SUM(d.estimate) as total_population,
    AVG(CASE WHEN d.variable_id = 'B19013_001E' THEN d.estimate END) as median_income
FROM geography g
JOIN demographic_data d ON g.geoid = d.geoid
WHERE g.geography_type = 'county'
    AND g.state_code = '06'  -- California
    AND d.year = 2022
GROUP BY g.geoid, g.name;

-- Time Series Pattern
WITH yearly_data AS (
    SELECT 
        year,
        SUM(CASE WHEN variable_id = 'B01001_001E' THEN estimate END) as population
    FROM demographic_data d
    JOIN geography g ON d.geoid = g.geoid
    WHERE g.geography_type = 'state'
        AND g.state_code = '06'
    GROUP BY year
)
SELECT 
    year,
    population,
    LAG(population) OVER (ORDER BY year) as prev_population,
    ((population - LAG(population) OVER (ORDER BY year)) / 
     LAG(population) OVER (ORDER BY year) * 100) as growth_rate
FROM yearly_data
ORDER BY year;

-- Spatial Proximity Pattern
SELECT 
    target.name as target_area,
    nearby.name as nearby_area,
    ST_Distance(target.boundary, nearby.boundary) as distance_miles
FROM geography target
CROSS JOIN geography nearby
WHERE target.geoid = '06037'  -- Los Angeles County
    AND nearby.geography_type = 'county'
    AND ST_DWithin(target.boundary, nearby.boundary, 100000)  -- 100km
    AND target.geoid != nearby.geoid
ORDER BY distance_miles
LIMIT 10;

Performance Optimizations:

Index Strategies:
  - Composite indexes on (geoid, year, variable_id)
  - Spatial indexes using R-Tree for geographic queries
  - Hash indexes for exact match lookups
  - Covering indexes to avoid table lookups

Query Execution:
  - Vectorized execution for analytical workloads
  - Parallel processing across CPU cores
  - Memory-mapped file access for large datasets
  - Lazy evaluation for nested queries

Materialized Views:
  - Pre-computed aggregations for common queries
  - Geographic summary tables by administrative level
  - Time-series aggregations for trend analysis
  - Statistical summaries with confidence intervals

Caching Architecture

Redis Cluster Configuration

Cluster Topology:

graph TB
    subgraph "Redis Cluster - Query Cache"
        MASTER1[Master Node 1<br/>Slots 0-5460<br/>Query Results]
        REPLICA1[Replica Node 1<br/>Read-Only<br/>Failover Ready]
        
        MASTER2[Master Node 2<br/>Slots 5461-10922<br/>Geographic Data]
        REPLICA2[Replica Node 2<br/>Read-Only<br/>Failover Ready]
        
        MASTER3[Master Node 3<br/>Slots 10923-16383<br/>Session Data]
        REPLICA3[Replica Node 3<br/>Read-Only<br/>Failover Ready]
    end
    
    subgraph "Sentinel Cluster"
        SENTINEL1[Sentinel 1<br/>Health Monitor]
        SENTINEL2[Sentinel 2<br/>Health Monitor]
        SENTINEL3[Sentinel 3<br/>Health Monitor]
    end
    
    MASTER1 --> REPLICA1
    MASTER2 --> REPLICA2
    MASTER3 --> REPLICA3
    
    SENTINEL1 -.-> MASTER1
    SENTINEL1 -.-> MASTER2
    SENTINEL1 -.-> MASTER3
    
    SENTINEL2 -.-> MASTER1
    SENTINEL2 -.-> MASTER2
    SENTINEL2 -.-> MASTER3
    
    SENTINEL3 -.-> MASTER1
    SENTINEL3 -.-> MASTER2
    SENTINEL3 -.-> MASTER3

Caching Strategies:

Query Result Caching:
  - Key Pattern: "query:{hash}:{user_tier}"
  - TTL: 1 hour for real-time data, 24 hours for historical
  - Eviction: LRU with memory pressure monitoring
  - Compression: Gzip compression for large result sets

Geographic Boundary Caching:
  - Key Pattern: "geo:{type}:{fips}:{resolution}"
  - TTL: 7 days (boundaries change infrequently)
  - Preloading: Warm cache with frequently accessed boundaries
  - Serialization: MessagePack for efficient storage

Session and User Caching:
  - Key Pattern: "session:{user_id}" and "profile:{user_id}"
  - TTL: 24 hours for sessions, 1 hour for profiles
  - Invalidation: Immediate on profile updates
  - Security: Encrypted session data

Aggregation Caching:
  - Key Pattern: "agg:{table}:{geography}:{year}"
  - TTL: 6 hours for statistical aggregations
  - Pre-computation: Background jobs for popular aggregations
  - Warming: Predictive cache warming based on usage patterns

Cache Performance Optimization

Memory Management:

Memory Allocation:
  - 70% for query results (most volatile)
  - 20% for geographic data (stable, frequently accessed)
  - 10% for session data (small but critical)

Eviction Policies:
  - Query Cache: LRU with usage frequency weighting
  - Geographic Cache: TTL-based with manual eviction
  - Session Cache: TTL-based with idle timeout

Monitoring:
  - Hit ratio target: >85% overall, >90% for geographic data
  - Memory usage alerts at 80% capacity
  - Latency monitoring with <1ms target for cache hits
  - Eviction rate monitoring to optimize TTL values

Data Pipeline Architecture

ETL Pipeline

Data Ingestion Flow:

graph LR
    subgraph "Source Monitoring"
        MONITOR[Census API<br/>Release Monitor<br/>Daily Check]
        WEBHOOK[API Webhook<br/>Release Notification<br/>Real-time]
    end
    
    subgraph "Extraction"
        DOWNLOAD[Bulk Download<br/>Parallel Threads<br/>Resume Support]
        INCREMENTAL[Incremental Extract<br/>Delta Detection<br/>Change Tracking]
    end
    
    subgraph "Validation"
        SCHEMA[Schema Validation<br/>Data Type Checking<br/>Constraint Verification]
        QUALITY[Quality Checks<br/>Completeness Analysis<br/>Outlier Detection]
        COMPARISON[Historical Comparison<br/>Trend Validation<br/>Anomaly Detection]
    end
    
    subgraph "Transformation"
        CLEAN[Data Cleaning<br/>Null Handling<br/>Format Standardization]
        ENRICH[Data Enrichment<br/>Geographic Mapping<br/>Metadata Addition]
        OPTIMIZE[Format Optimization<br/>Columnar Conversion<br/>Compression]
    end
    
    subgraph "Loading"
        STAGE[Staging Load<br/>Temporary Tables<br/>Validation]
        PRODUCTION[Production Load<br/>Atomic Deployment<br/>Rollback Capability]
        INDEX[Index Rebuild<br/>Statistics Update<br/>Optimization]
    end
    
    MONITOR --> DOWNLOAD
    WEBHOOK --> INCREMENTAL
    DOWNLOAD --> SCHEMA
    INCREMENTAL --> SCHEMA
    SCHEMA --> QUALITY
    QUALITY --> COMPARISON
    COMPARISON --> CLEAN
    CLEAN --> ENRICH
    ENRICH --> OPTIMIZE
    OPTIMIZE --> STAGE
    STAGE --> PRODUCTION
    PRODUCTION --> INDEX

Pipeline Configuration:

Scheduling:
  - Daily monitoring for new Census releases
  - Weekly full data refresh and validation
  - Monthly historical data archive and cleanup
  - Quarterly geographic boundary updates

Error Handling:
  - Retry logic with exponential backoff
  - Dead letter queue for failed records
  - Data quality threshold enforcement
  - Automatic rollback on validation failures

Monitoring:
  - Pipeline execution time tracking
  - Data quality metric collection
  - Error rate monitoring and alerting
  - Resource utilization optimization

Scalability:
  - Parallel processing for large datasets
  - Dynamic resource allocation based on data volume
  - Horizontal scaling for peak processing periods
  - Queue-based processing for consistent throughput

Data Quality Framework

Quality Dimensions:

Accuracy:
  - Cross-validation with multiple Census sources
  - Statistical consistency checks across geographic levels
  - Margin of error validation for survey data
  - Historical trend validation for outlier detection

Completeness:
  - Required field validation for all records
  - Geographic coverage verification
  - Variable availability checking across years
  - Missing data pattern analysis and reporting

Consistency:
  - Cross-table relationship validation
  - Geographic hierarchy integrity checks
  - Temporal consistency across data releases  
  - Unit of measurement standardization

Timeliness:
  - Data freshness monitoring and alerts
  - Processing time optimization and tracking
  - User notification for delayed updates
  - SLA compliance monitoring for data delivery

Validity:
  - Data type and format validation
  - Range and constraint checking
  - Business rule enforcement
  - Schema compliance verification

Quality Monitoring Dashboard:

graph TB
    subgraph "Data Quality Metrics"
        ACCURACY[Accuracy Score<br/>95.8%<br/>✅ Target: >95%]
        COMPLETENESS[Completeness<br/>99.2%<br/>✅ Target: >99%]
        TIMELINESS[Data Freshness<br/>18 hours<br/>✅ Target: <24h]
        CONSISTENCY[Consistency<br/>97.1%<br/>⚠️ Target: >98%]
    end
    
    subgraph "Quality Trends"
        TREND_CHART[Quality Trend<br/>30-day Rolling Average<br/>📈 Improving]
        ISSUE_LOG[Recent Issues<br/>2 Open, 15 Resolved<br/>🔧 Action Required]
    end
    
    subgraph "Automated Actions"
        ALERT[Quality Alert<br/>Consistency Below Target<br/>📧 Team Notified]
        REMEDIATION[Auto-Remediation<br/>Schema Validation Fix<br/>✅ Applied]
    end
    
    ACCURACY --> TREND_CHART
    COMPLETENESS --> TREND_CHART
    TIMELINESS --> TREND_CHART
    CONSISTENCY --> ISSUE_LOG
    
    CONSISTENCY --> ALERT
    ALERT --> REMEDIATION

Data Governance and Security

Data Access Control

Role-Based Access Control:

Data Access Tiers:
  Public Data:
    - Pre-aggregated Census data (no individual records)
    - Geographic boundaries and metadata
    - Public documentation and schemas
    - Read-only access for all users

  Professional Data:
    - Full table access with query limits
    - Historical data across all years
    - Advanced statistical calculations
    - Export capabilities with attribution

  Enterprise Data:
    - Unlimited query access and API calls
    - Custom data processing and analysis
    - Bulk export and integration capabilities
    - Priority support and custom schemas

  Administrative Data:
    - User management and analytics data
    - System performance and usage metrics
    - Data quality and pipeline monitoring
    - Configuration and security settings

Data Privacy Framework:

Privacy by Design:
  - No personally identifiable information (PII) in Census data
  - Aggregated data only (minimum population thresholds)
  - Automatic data minimization and retention policies
  - Privacy impact assessments for new data sources

Compliance Standards:
  - GDPR compliance for EU users (data portability, deletion)
  - CCPA compliance for California residents
  - SOC 2 Type II certification for enterprise customers
  - FedRAMP compliance pathway for government users

Data Retention:
  - Raw data: 7 years with automated archival
  - Processed data: 5 years in active storage
  - User data: Account lifetime plus 30 days
  - Logs and analytics: 2 years with anonymization

Backup and Disaster Recovery

Backup Strategy:

graph TB
    subgraph "Production Data"
        DUCKDB_PROD[DuckDB<br/>Production Database]
        REDIS_PROD[Redis<br/>Cache Cluster]
        POSTGRES_PROD[PostgreSQL<br/>User Database]
    end
    
    subgraph "Backup Systems"
        CONTINUOUS[Continuous Backup<br/>15-minute Incremental<br/>AWS S3]
        DAILY[Daily Full Backup<br/>Complete Snapshot<br/>Multi-Region]
        WEEKLY[Weekly Archive<br/>Long-term Storage<br/>Glacier Deep Archive]
    end
    
    subgraph "Disaster Recovery"
        HOT_STANDBY[Hot Standby<br/>Different AZ<br/>< 1 minute RTO]
        WARM_STANDBY[Warm Standby<br/>Different Region<br/>< 15 minute RTO]
        COLD_BACKUP[Cold Backup<br/>Archive Storage<br/>< 4 hour RTO]
    end
    
    DUCKDB_PROD --> CONTINUOUS
    REDIS_PROD --> CONTINUOUS
    POSTGRES_PROD --> CONTINUOUS
    
    CONTINUOUS --> DAILY
    DAILY --> WEEKLY
    
    CONTINUOUS --> HOT_STANDBY
    DAILY --> WARM_STANDBY
    WEEKLY --> COLD_BACKUP

Recovery Procedures:

Recovery Time Objectives (RTO):
  - Critical Services: < 15 minutes
  - Query Processing: < 30 minutes
  - Full System: < 2 hours
  - Historical Data: < 4 hours

Recovery Point Objectives (RPO):
  - User Data: < 1 minute (continuous replication)
  - Query Cache: < 15 minutes (acceptable loss)
  - Analytics Data: < 1 hour (can be regenerated)
  - Configuration: < 5 minutes (version controlled)

Testing Schedule:
  - Monthly: Automated backup integrity checks
  - Quarterly: Disaster recovery drills with full team
  - Annually: Complete system recovery simulation
  - Ad-hoc: Post-incident recovery validation

Performance Monitoring

Database Performance Metrics

DuckDB Monitoring:

Query Performance:
  - Average query execution time by complexity
  - 95th percentile response times for different query types
  - Query throughput (queries per second)
  - Slow query identification and optimization opportunities

Resource Utilization:
  - Memory usage patterns and peak allocation
  - CPU utilization during query processing
  - I/O throughput for data loading and queries
  - Network bandwidth utilization for result delivery

Connection Management:
  - Active connection count and pool utilization
  - Connection creation and termination rates
  - Query queue depth and wait times
  - Connection timeout and error rates

Cache Performance Metrics:

Redis Cluster Monitoring:
  - Cache hit ratio by data type and time period
  - Memory utilization and eviction rates
  - Network latency between cluster nodes
  - Failover frequency and recovery times

Cache Effectiveness:
  - Query result cache hit ratio (target: >85%)
  - Geographic data cache hit ratio (target: >90%)
  - Session cache hit ratio (target: >95%)
  - Cache warming effectiveness and timing

Performance Impact:
  - Response time improvement from caching
  - Database load reduction through cache hits
  - Cost savings from reduced database queries
  - User experience improvement metrics

This comprehensive data architecture provides the foundation for CensusChat’s high-performance analytical capabilities while maintaining data quality, security, and scalability requirements.