Warehouse tables
Tables grouped by medallion tier: bronze mirrors the source, silver (coming with MVP 3) will hold curated derivations, gold is analyst-facing, and admin carries pipeline + data-quality observability. The dotted "dbt" arrows indicate the medallion flow conceptually; solid arrows are foreign-key relationships between tables. Column-level details live in the data dictionary; column-level shape lives on /profile.
flowchart LR
%% Auto-generated by scripts/generate_warehouse_erd.py
%% Tier-grouped overview. Column details live on /profile + /docs/.
classDef bronzeNode fill:#efe0c8,stroke:#a07028,color:#3d2806
classDef silverNode fill:#e8e8e8,stroke:#888888,color:#333333,stroke-dasharray:4 3
classDef goldNode fill:#fff4c8,stroke:#c19d3a,color:#5c4a0b
classDef adminNode fill:#dceefc,stroke:#3a8fc1,color:#0b3a5c
subgraph bronze_group["Bronze — source mirrors"]
b__raw_311_requests["raw_311_requests"]:::bronzeNode
b__raw_somerville_at_a_glance["raw_somerville_at_a_glance"]:::bronzeNode
b__raw_somerville_crime["raw_somerville_crime"]:::bronzeNode
b__raw_somerville_happiness_survey["raw_somerville_happiness_survey"]:::bronzeNode
b__raw_somerville_permits["raw_somerville_permits"]:::bronzeNode
b__raw_somerville_traffic_citations["raw_somerville_traffic_citations"]:::bronzeNode
b__raw_somerville_wards["raw_somerville_wards"]:::bronzeNode
end
subgraph silver_group["Silver — curated (MVP 3, coming soon)"]
s__stg_happiness_survey["stg_happiness_survey"]:::silverNode
end
subgraph gold_group["Gold — analyst-facing"]
g__dim_date["dim_date"]:::goldNode
g__dim_kpi_topic["dim_kpi_topic"]:::goldNode
g__dim_offense_category["dim_offense_category"]:::goldNode
g__dim_offense_code["dim_offense_code"]:::goldNode
g__dim_request_type["dim_request_type"]:::goldNode
g__dim_status["dim_status"]:::goldNode
g__dim_survey_question["dim_survey_question"]:::goldNode
g__dim_survey_wave["dim_survey_wave"]:::goldNode
g__dim_ward["dim_ward"]:::goldNode
g__fct_311_requests["fct_311_requests"]:::goldNode
g__fct_citations["fct_citations"]:::goldNode
g__fct_crime_incidents["fct_crime_incidents"]:::goldNode
g__fct_happiness_survey["fct_happiness_survey"]:::goldNode
g__fct_permits["fct_permits"]:::goldNode
g__fct_somerville_kpi["fct_somerville_kpi"]:::goldNode
end
subgraph admin_group["Admin — pipeline observability"]
a__dim_data_quality_test["dim_data_quality_test"]:::adminNode
a__fct_data_profile["fct_data_profile"]:::adminNode
a__fct_test_run["fct_test_run"]:::adminNode
end
%% Medallion flow (conceptual — not full lineage)
bronze_group -. dbt .-> gold_group
gold_group -. dbt .-> admin_group
%% Foreign-key relationships (from dbt relationships tests)
g__fct_311_requests -->|request_type_id| g__dim_request_type
g__fct_311_requests -->|status_id| g__dim_status
g__fct_311_requests -->|ward| g__dim_ward
g__fct_crime_incidents -->|ward| g__dim_ward
g__fct_crime_incidents -->|offense_code| g__dim_offense_code
g__fct_crime_incidents -->|offense_category| g__dim_offense_category
g__dim_offense_code -->|offense_category| g__dim_offense_category
g__fct_permits -->|ward| g__dim_ward
g__fct_somerville_kpi -->|topic| g__dim_kpi_topic
g__fct_citations -->|ward| g__dim_ward
g__fct_happiness_survey -->|survey_wave_id| g__dim_survey_wave
g__fct_happiness_survey -->|survey_question_id| g__dim_survey_question
Column-level detail by tier
Bronze — Source mirrors — raw, untransformed feeds
Each table mirrors a Socrata source one-to-one. dlt owns the merge; dbt only passes the bronze view through. Audit columns (_extracted_at, _dlt_id, …) are omitted from this diagram — they're on /docs/ and /profile.
erDiagram
%% Auto-generated by scripts/generate_per_tier_erd.py (bronze tier)
%% Columns from dbt schema.yml; audit (_*) columns omitted.
%% Type token `text` is a generic placeholder — see docs/schema.sql
%% for source DDL types.
raw_311_requests {
text id
text classification
text category
text type
text origin_of_request
text date_created
text most_recent_status
text most_recent_status_date
text block_code
text ward
text accuracy
text courtesy
text ease
text overallexperience
text emergency_readiness_and_response_planning
text green_space_care_and_maintenance
text infrastructure_maintenance_and_repairs
text noise_and_activity_disturbances
text reliable_service_delivery
text navigating_city_services_and_policies
text public_space_cleanliness_and_environmental_health
text voting_and_election_information
}
raw_somerville_wards {
text ward_number
text object_id
text ogc_fid
text shape_length_ftus
text shape_area_sqftus
text geometry_wkt_wgs84
text geometry_wkt_source
}
raw_somerville_crime {
text incnum
text day_and_month
text year
text police_shift
text offensecode
text offense
text incdesc
text offensetype
text category
text blockcode
text ward
}
raw_somerville_at_a_glance {
text topic
text description
text year
text value
text units
text geography
}
raw_somerville_traffic_citations {
text citationnum
text dtissued
text police_shift
text address
text chgcode
text chgdesc
text chgcategory
text vehiclemph
text mphzone
text lat
text long
text blockcode
text ward
text warning
}
raw_somerville_permits {
text id
text application_date
text issue_date
text type
text status
text amount
text address
text latitude
text longitude
text work
}
raw_somerville_happiness_survey {
text id
text year
text ward
text survey_method
text survey_language
text tract
text gender
text age
text race_ethnicity
text housing_status
text highest_level_education
text tenure
text household_income
text likely_low_income
text likely_cost_burdened
text language_spoken
text happiness_num
text happiness_label
text life_satisfaction_num
text somerville_satisfaction_num
text feel_safe_somerville_num
text rats_mice_concern_num
text heard_by_city_num
text emergency_response_quality_satisfaction_num
text census_year
text acs_somerville_median_income
text acs_somerville_avg_household
text inflation_adjustment
}
Silver — Curated derivations — coming with MVP 3
The Silver tier is structurally present but holds no tables yet. Plan 24 (MVP 3 — Happiness Survey silver/gold curation) will land the first silver model. The generator reads dbt/models/silver/schema.yml, so a new silver model lands on this diagram on the next ./run.sh.
erDiagram
%% Auto-generated by scripts/generate_per_tier_erd.py (silver tier)
%% Columns from dbt schema.yml; audit (_*) columns omitted.
%% Type token `text` is a generic placeholder — see docs/schema.sql
%% for source DDL types.
stg_happiness_survey {
text respondent_id
text year
text ward
text happiness_num
text life_satisfaction_num
text somerville_satisfaction_num
text beauty_neighborhood_satisfaction_num
text streets_maintenance_satisfaction_num
text education_quality_satisfaction_num
text social_community_events_satisfaction_num
text city_services_information_availability_satisfaction_num
text weight
}
Gold — Analyst-facing star schema — fct + dim tables
Six fact tables (311 requests, crime, citations, permits, KPI snapshots) and seven dimensions (ward, date, request type, status, offense code + category, KPI topic). FK arrows show fct → dim joins. This is what the chat agent queries.
erDiagram
%% Auto-generated by scripts/generate_per_tier_erd.py (gold tier)
%% Columns from dbt schema.yml; audit (_*) columns omitted.
%% Type token `text` is a generic placeholder — see docs/schema.sql
%% for source DDL types.
dim_date {
text date_dt
text year
text quarter
text month
text month_name
text day
text day_of_week
text day_name
text week_of_year
text is_weekend
text fiscal_year
}
dim_request_type {
text request_type_id
text request_type
text first_seen_dt
text last_seen_dt
text request_count
}
dim_status {
text status_id
text status
text is_open
text first_seen_dt
text last_seen_dt
}
fct_311_requests {
text id
text classification
text category
text request_type
text request_type_id
text status
text status_id
text origin
text date_created_dt
text date_created_ts
text most_recent_status_dt
text most_recent_status_ts
text ward
text block_code
text accuracy
text courtesy
text ease
text overallexperience
text is_emergency_readiness_tag
text is_green_space_tag
text is_infrastructure_tag
text is_noise_tag
text is_reliable_service_tag
text is_city_services_tag
text is_public_space_tag
text is_voting_tag
}
dim_ward {
text ward_id
text ward
text ward_name
text geometry_wkt_wgs84
text area_sqm
text area_sqkm
text perimeter_m
}
fct_crime_incidents {
text incident_id
text case_number
text incident_dt
text incident_year
text incident_year_only
text police_shift
text offense_code
text multi_offense_flag
text offense
text offense_type
text offense_category
text ward
text block_code
}
dim_offense_code {
text offense_code
text offense
text offense_type
text offense_category
text is_multi_offense_grouping
text is_active
}
dim_offense_category {
text offense_category
text severity_rank
}
fct_permits {
text permit_id
text permit_number
text application_date
text issue_date
text application_year
text issue_year
text permit_type
text permit_status
text is_issued
text permit_amount
text address
text work_description
text ward
text latitude
text longitude
}
fct_somerville_kpi {
text kpi_id
text topic
text year
text value
text kpi_description
text units
text geography
}
dim_kpi_topic {
text topic
text first_year
text latest_year
text observation_count
text geography_count
text has_massachusetts_benchmark
text has_somerville_data
}
fct_citations {
text citation_id
text citation_number
text citation_ts
text citation_date
text citation_year
text police_shift
text charge_code
text charge_description
text charge_category
text vehicle_mph
text posted_mph_zone
text latitude
text longitude
text ward
text block_code
text address
text warning_flag
text is_warning
}
dim_survey_question {
text survey_question_id
text column_name
text question_label
text topic
text scale_min
text scale_max
text first_wave_asked
text last_wave_asked
text waves_asked_count
}
dim_survey_wave {
text survey_wave_id
text wave_year
text respondent_count
text ward_coverage_pct
text notes
}
fct_happiness_survey {
text survey_observation_id
text survey_wave_id
text survey_question_id
text geography_level
text geography_key
text respondent_count
text mean_score
text median_score
text score_1_count
text score_2_count
text score_3_count
text score_4_count
text score_5_count
text weight_strategy
}
fct_311_requests }o--|| dim_request_type : "request_type_id"
fct_311_requests }o--|| dim_status : "status_id"
fct_311_requests }o--|| dim_ward : "ward"
fct_crime_incidents }o--|| dim_ward : "ward"
fct_crime_incidents }o--|| dim_offense_code : "offense_code"
fct_crime_incidents }o--|| dim_offense_category : "offense_category"
dim_offense_code }o--|| dim_offense_category : "offense_category"
fct_permits }o--|| dim_ward : "ward"
fct_somerville_kpi }o--|| dim_kpi_topic : "topic"
fct_citations }o--|| dim_ward : "ward"
fct_happiness_survey }o--|| dim_survey_wave : "survey_wave_id"
fct_happiness_survey }o--|| dim_survey_question : "survey_question_id"
Admin — Pipeline + data-quality observability
Three observability tables: pipeline runs, data quality test definitions, and test run results. Powers /trust and the drift-fail guardrail in ./run.sh.
erDiagram
%% Auto-generated by scripts/generate_per_tier_erd.py (admin tier)
%% Columns from dbt schema.yml; audit (_*) columns omitted.
%% Type token `text` is a generic placeholder — see docs/schema.sql
%% for source DDL types.
fct_data_profile {
text profiled_at
text table_name
text column_name
text row_count
text null_count
text pct_null
text distinct_count
text pct_distinct
text min_value
text max_value
text min_length
text max_length
text avg_length
}
dim_data_quality_test {
text test_id
text test_type
text table_name
text column_name
text metric
text grain
text expected_value
text tolerance_pct
text is_active
text certified_at
text certified_by
}
fct_test_run {
text run_id
text test_id
text run_at
text actual_value
text expected_value
text variance_pct
text status
text failure_message
}
Semantic layer
Each view in the diagram maps to a
.view.yml file in semantics/views/; each
topic groups views in semantics/topics/. Views are the
contract surface for the chat agent — what it knows about the data,
including the measures it can compute. Measure definitions live on
/metrics.
graph TD
%% Auto-generated by scripts/generate_semantic_layer_diagram.py
classDef topicNode fill:#dceefc,stroke:#3a8fc1,color:#0b3a5c
classDef viewNode fill:#fcf3d9,stroke:#c19d3a,color:#5c4a0b
classDef tableNode fill:#e1f5e6,stroke:#3aa86a,color:#0c4920
subgraph topics_group["Topics"]
T_built_environment["built_environment"]:::topicNode
T_city_context["city_context"]:::topicNode
T_perception["perception"]:::topicNode
T_public_safety["public_safety"]:::topicNode
T_service_requests["service_requests"]:::topicNode
end
subgraph views_group["Views (.view.yml)"]
V_citations["citations
5 measures"]:::viewNode
V_crime["crime
3 measures"]:::viewNode
V_dates["dates"]:::viewNode
V_happiness_survey["happiness_survey
4 measures"]:::viewNode
V_offense_categories["offense_categories
1 measure"]:::viewNode
V_offense_codes["offense_codes
1 measure"]:::viewNode
V_permits["permits
5 measures"]:::viewNode
V_request_types["request_types"]:::viewNode
V_requests["requests
2 measures"]:::viewNode
V_somerville_kpis["somerville_kpis
4 measures"]:::viewNode
V_statuses["statuses"]:::viewNode
V_survey_questions["survey_questions
1 measure"]:::viewNode
V_survey_waves["survey_waves
2 measures"]:::viewNode
V_wards["wards
1 measure"]:::viewNode
end
subgraph tables_group["Base tables (warehouse)"]
BT_main_gold_dim_date(["main_gold.dim_date"]):::tableNode
BT_main_gold_dim_offense_category(["main_gold.dim_offense_category"]):::tableNode
BT_main_gold_dim_offense_code(["main_gold.dim_offense_code"]):::tableNode
BT_main_gold_dim_request_type(["main_gold.dim_request_type"]):::tableNode
BT_main_gold_dim_status(["main_gold.dim_status"]):::tableNode
BT_main_gold_dim_survey_question(["main_gold.dim_survey_question"]):::tableNode
BT_main_gold_dim_survey_wave(["main_gold.dim_survey_wave"]):::tableNode
BT_main_gold_dim_ward(["main_gold.dim_ward"]):::tableNode
BT_main_gold_fct_311_requests(["main_gold.fct_311_requests"]):::tableNode
BT_main_gold_fct_citations(["main_gold.fct_citations"]):::tableNode
BT_main_gold_fct_crime_incidents(["main_gold.fct_crime_incidents"]):::tableNode
BT_main_gold_fct_happiness_survey(["main_gold.fct_happiness_survey"]):::tableNode
BT_main_gold_fct_permits(["main_gold.fct_permits"]):::tableNode
BT_main_gold_fct_somerville_kpi(["main_gold.fct_somerville_kpi"]):::tableNode
end
T_built_environment --> V_permits
T_built_environment --> V_wards
T_built_environment --> V_dates
T_city_context --> V_somerville_kpis
T_perception --> V_happiness_survey
T_perception --> V_survey_questions
T_perception --> V_survey_waves
T_public_safety --> V_crime
T_public_safety --> V_offense_codes
T_public_safety --> V_offense_categories
T_public_safety --> V_citations
T_public_safety --> V_wards
T_public_safety --> V_dates
T_service_requests --> V_requests
T_service_requests --> V_request_types
T_service_requests --> V_statuses
T_service_requests --> V_dates
T_service_requests --> V_wards
V_citations --> BT_main_gold_fct_citations
V_crime --> BT_main_gold_fct_crime_incidents
V_dates --> BT_main_gold_dim_date
V_happiness_survey --> BT_main_gold_fct_happiness_survey
V_offense_categories --> BT_main_gold_dim_offense_category
V_offense_codes --> BT_main_gold_dim_offense_code
V_permits --> BT_main_gold_fct_permits
V_request_types --> BT_main_gold_dim_request_type
V_requests --> BT_main_gold_fct_311_requests
V_somerville_kpis --> BT_main_gold_fct_somerville_kpi
V_statuses --> BT_main_gold_dim_status
V_survey_questions --> BT_main_gold_dim_survey_question
V_survey_waves --> BT_main_gold_dim_survey_wave
V_wards --> BT_main_gold_dim_ward