Data Architecture & Modeling

Layered Platform Architecture

The architecture separates source acquisition, immutable raw storage, reusable dimensions, curated subject data, subject-area marts and downstream analytics while allowing selected raw and curated datasets to support direct analytical work where appropriate.

Platform Workflow

High-level platform flow showing the Census pathway, the fact-data pathway, subject-area marts and downstream analytics consumption.

Click the image to open the full-size version in a new window.

Schema Structure

Representative PostgreSQL schema views showing how the physical implementation maps to the layered architecture.

load

Landing and preparation layer used heavily for Census and related source structures before downstream conforming and dimensional modeling.

seeds

Incremental and reference datasets used to capture source changes and support repeatable loading into the raw layer.

raw

Source-aligned landing tables that preserve incoming data before downstream cleaning, standardization and modeling.

dimensions

Reusable geographic, Census, lookup and descriptive dimensions shared across subject areas and analytical models.

curate

Cleaned, standardized and enriched subject-area tables and views prepared for reuse in analytical models.

property

Property-focused analytical tables combining claims, disasters, flood risk, policies, loss exposure and related measures.

integration

Cross-domain analytical models that combine multiple curated subject areas into business-facing marts and integrated risk, claims, market and geographic outputs.

sentiment / sentiment_classification / sentiment_temporal

Sentiment-focused structures covering tokenized views, aggregated sentiment outputs, classification groupings and temporal rollups.

Query-Based Integration Diagrams

These diagrams were mapped from actual SQL logic to show how selected models are built from curated datasets, transformations and joins.

City new residential risk exposure integration diagram

integration.city_new_residential_risk_exposure

Cross-dataset fact integration showing how permits, air pollution, NOAA, fires, weather alerts, multiple loss properties, NFIP claims and NFIP policies are aggregated and joined by state, county and city within the latest 12-month permit window.

Click the image to open the full-size version in a new window.

curate.stocks_strategy

Strategy-building flow showing source stock data, calculation steps, rule evaluation and final output flags for momentum and sniper signals.

Click the image to open the full-size version in a new window.

Climate property divergence SQL logic diagram

curate.climate_property_divergence

Joined model showing how fire activity, weather alerts and Zillow property trends are standardized and compared to produce state-level divergence scoring.

Click the image to open the full-size version in a new window.

Representative Analytical Tables

Representative metadata from two integrated analytical models. The tables show the physical column structure and PostgreSQL data types behind the analytical outputs.

integration.city_new_residential_risk_exposure

87 columns · City-level integration of permits, demographics, property exposure, climate, fire, weather, repetitive loss, NFIP claims and NFIP policy data.

#	Column	Data Type
1	country	text
2	state	text
3	region	text
4	county	text
5	city	text
6	permit_period_start	date
7	permit_period_end	date
8	total_population	numeric
9	housing_units_total	numeric
10	median_household_income	numeric
11	median_home_value	numeric
12	median_year_structure_built	numeric
13	pre_1980_housing_rate	numeric
14	latest_12m_permitted_value	numeric
15	latest_12m_permitted_units	bigint
16	latest_12m_permitted_buildings	bigint
17	latest_12m_1_unit_value	numeric
18	latest_12m_1_unit_units	bigint
19	latest_12m_2_unit_value	numeric
20	latest_12m_2_unit_units	bigint
21	latest_12m_3_4_unit_value	numeric
22	latest_12m_3_4_unit_units	bigint
23	latest_12m_5_plus_unit_value	numeric
24	latest_12m_5_plus_unit_units	bigint
25	air_quality_observation_days	bigint
26	unhealthy_air_quality_days	bigint
27	very_unhealthy_air_quality_days	bigint
28	average_daily_max_aqi	numeric
29	maximum_aqi	integer
30	maximum_air_severity_score	integer
31	fire_detection_count	numeric
32	fire_detection_days	bigint
33	high_confidence_fire_detection_count	numeric
34	average_fire_radiative_power	numeric
35	total_fire_radiative_power	numeric
36	maximum_fire_radiative_power	double precision
37	noaa_observation_months	bigint
38	extreme_heat_months	bigint
39	freezing_months	bigint
40	precipitation_months_over_25_4_mm	bigint
41	snowfall_months	bigint
42	maximum_temperature_centigrade	numeric
43	minimum_temperature_centigrade	numeric
44	average_temperature_centigrade	numeric
45	total_precipitation_mm	numeric
46	total_snowfall_mm	numeric
47	maximum_windspeed_gust	numeric
48	weather_alert_count	bigint
49	severe_weather_alert_count	bigint
50	flood_alert_count	bigint
51	tornado_alert_count	bigint
52	hurricane_tropical_alert_count	bigint
53	reported_weather_damage	numeric
54	maximum_weather_impact_score	smallint
55	multiple_loss_property_count	bigint
56	multiple_loss_snapshot_date	date
57	nfip_repetitive_loss_property_count	bigint
58	nfip_severe_repetitive_loss_property_count	bigint
59	fma_repetitive_loss_property_count	bigint
60	fma_severe_repetitive_loss_property_count	bigint
61	severe_repetitive_loss_property_count	bigint
62	nfip_claim_count	bigint
63	earliest_nfip_claim_date	date
64	latest_nfip_claim_date	date
65	total_nfip_building_claim_payments	real
66	total_nfip_contents_claim_payments	real
67	total_nfip_increased_compliance_payments	real
68	total_nfip_claim_payments	real
69	total_claimed_property_value	numeric
70	average_claim_water_depth	numeric
71	maximum_claim_water_depth	numeric
72	latest_12m_policy_count	numeric
73	latest_12m_building_coverage	numeric
74	latest_12m_contents_coverage	numeric
75	latest_12m_total_nfip_coverage	numeric
76	latest_12m_total_nfip_premium	numeric
77	latest_12m_total_policy_cost	numeric
78	estimated_existing_housing_value	numeric
79	permitted_value_to_existing_housing_value	numeric
80	permitted_units_per_1000_housing_units	numeric
81	multiple_loss_properties_per_1000_housing_units	numeric
82	estimated_new_permitted_flood_loss_proxy	numeric
83	historical_nfip_paid_loss_ratio	numeric
84	nfip_claim_payments_per_housing_unit	numeric
85	recent_climate_hazard_indicator_count	bigint
86	created_utc	timestamp with time zone
87	integration_id	bigint

integration.claims_escalation_triage

86 columns · Claims triage model combining event severity, prior loss, repetitive loss, property exposure, business interruption, recovery indicators, policy information and disaster context.

#	Column	Data Type
1	rank	bigint
2	event_date	date
3	state	text
4	county	text
5	census_tract	character varying
6	tract_geoid	text
7	county_geoid	text
8	tract	text
9	area_name	text
10	event_type	text
11	event_score	integer
12	prior_loss_score	integer
13	repetitive_loss_score	integer
14	property_exposure_score	integer
15	business_interruption_proxy_score	integer
16	recovery_delay_proxy_score	integer
17	recovery_complexity_score	numeric
18	complexity_tier	text
19	recommended_action	text
20	why_flagged	text
21	event_severity_availability	text
22	event_severity_data_status	text
23	county_alert_context_availability	text
24	county_alert_context_data_status	text
25	prior_loss_availability	text
26	prior_loss_data_status	text
27	repetitive_loss_availability	text
28	repetitive_loss_data_status	text
29	property_exposure_availability	text
30	property_exposure_data_status	text
31	business_interruption_availability	text
32	business_interruption_data_status	text
33	recovery_delay_availability	text
34	recovery_delay_data_status	text
35	utility_access_disruption_availability	text
36	utility_access_disruption_data_status	text
37	local_repair_capacity_availability	text
38	local_repair_capacity_data_status	text
39	insured_location_availability	text
40	insured_location_data_status	text
41	open_claim_status_availability	text
42	open_claim_status_data_status	text
43	policy_terms_availability	text
44	policy_terms_data_status	text
45	reserve_history_availability	text
46	reserve_history_data_status	text
47	dispute_litigation_signal_availability	text
48	dispute_litigation_signal_data_status	text
49	max_precipitation_mm	bigint
50	max_windspeed_gust	integer
51	max_windspeed_avg	numeric
52	max_snowfall_mm	bigint
53	max_snowdepth_mm	integer
54	min_temp_centigrade	numeric
55	max_temp_centigrade	numeric
56	noaa_observation_count	bigint
57	county_alert_severity	text
58	county_alert_group	text
59	county_alert_type	text
60	county_alert_impact_score	integer
61	county_alert_count	bigint
62	claim_count_10yr	numeric
63	total_paid_10yr	real
64	repetitive_loss_property_count	bigint
65	total_repetitive_losses	numeric
66	policy_count	numeric
67	building_coverage	numeric
68	contents_coverage	numeric
69	policy_building_replacement_cost	numeric
70	small_business_policy_count	numeric
71	nonprofit_policy_count	numeric
72	state_owned_policy_count	numeric
73	median_home_value	integer
74	median_year_structure_built	integer
75	median_structure_age	integer
76	expected_annual_loss_score	real
77	expected_annual_loss_rating	character varying
78	expected_annual_loss_building	real
79	annualized_loss_ratio_building	real
80	annualized_loss_ratio_national_percentile	real
81	disaster_count_5yr	bigint
82	recent_disaster_type	text
83	most_recent_disaster_date	date
84	most_recent_claim_month	date
85	most_recent_repetitive_loss_date	date
86	marts_utc	timestamp with time zone

Sample Data

Current representative records from integration.city_new_residential_risk_exposure. Data is retrieved directly from PostgreSQL when this page is generated.

City New Residential Risk Exposure

Selected records showing demographic, housing, permit, environmental, insurance and loss information within the same analytical model.

data_through_date	refreshed_utc	state	county	city	total_population	median_home_value	latest_12m_permitted_value	unhealthy_air_quality_days	fire_detection_days	multiple_loss_property_count	nfip_claim_count	latest_12m_policy_count	recent_climate_hazard_indicator_count
2026-08-01	2026-08-24 21:31 UTC	Arkansas	Lonoke	Carlisle	1,040	131,100	420,000	0	0	18	1	5	0
2026-08-01	2026-08-24 21:31 UTC	Arkansas	Lonoke	Lonoke	2,270	211,900	4,749,717	0	0	21	1	2	0
2026-08-01	2026-08-24 21:31 UTC	Florida	Citrus	Crystal River	14	264,200	10,402,272	0	1	1,250	1	94	1
2026-08-01	2026-08-24 21:31 UTC	Florida	Duval	Jacksonville	384	563,200	536,133,378	0	1	639	23	21,080	12
2026-08-01	2026-08-24 21:31 UTC	Florida	Hillsborough	Tampa	65	1,370,400	1,062,472,761	2	0	840	14	43,500	8
2026-08-01	2026-08-24 21:31 UTC	Florida	Polk	Lakeland	114	541,700	121,419,561	0	1	310	0	1,356	8
2026-08-01	2026-08-24 21:31 UTC	Georgia	Fulton	Atlanta	228	473,200	871,003,261	8	0	365	12	2,698	15
2026-08-01	2026-08-24 21:31 UTC	Indiana	Floyd	New Albany	82	184,100	8,768,585	0	0	38	0	158	0
2026-08-01	2026-08-24 21:31 UTC	Indiana	Vigo	Terre Haute	30	171,300	121,809,812	0	0	45	1	472	7
2026-08-01	2026-08-24 21:31 UTC	Kentucky	Daviess	Owensboro	52	292,900	27,077,172	0	0	43	0	180	6