Column profiles

What's in each column.

Auto-generated shape data for every bronze and gold column: row counts, distinct counts, null percentages, value distributions, top-5 values. Hand-written descriptions live in dbt's data dictionary; this page is the observational counterpart.

422 columns across 22 tables · last profiled 2026-10-04 06:00 UTC

main_bronze.raw_311_requests

1,224,700 rows · 28 columns
_dlt_id
VARCHAR
dlt row identifier -- retained at every layer for source-to-pipeline lineage.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 1,224,700
len 14..14
  • +++/wgjplG+Uwg 1 (0.0%)
  • +++tvnMs12/YKw 1 (0.0%)
  • +++uSI1zLrCPjA 1 (0.0%)
  • +++xl7St2jkcnQ 1 (0.0%)
  • ++/Z6xMJUm+IdQ 1 (0.0%)
_dlt_load_id
VARCHAR
dlt load identifier -- retained at every layer for source-to-pipeline lineage.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 2
len 18..18
  • 1791021616.5923402 1,224,699 (100.0%)
  • 1782295215.3161101 1 (0.0%)
_extracted_at
TIMESTAMP WITH TIME ZONE
**PIPELINE METADATA -- not for analysis.** UTC timestamp when this row was last extracted from the SODA endpoint. Advances on every run that touched the row. Use for run-traceability, not for business questions about when an event occurred -- use `date_created` (gold: `opened_dt`) for the latter.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 2
dates 2026-06-24 → 2026-10-03 · span 101 days
_extracted_run_id
VARCHAR
**PIPELINE METADATA -- not for analysis.** ULID of the pipeline run that produced this row's current state. Joins to `main_admin.fct_pipeline_run_raw.run_id` for run-level diagnostics.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 2
len 26..26
  • 01M40KA281T5KQQG9GX4F73XQE 1,224,699 (100.0%)
  • 01KVWH5DY9V93SWX6FGY5T8HCS 1 (0.0%)
_first_seen_at
TIMESTAMP
**PIPELINE METADATA -- not for analysis.** UTC timestamp when this row was first ingested into our warehouse. Preserved across subsequent re-extractions. Distinguishes "when did the city create this case" (`date_created`) from "when did our pipeline first see it" (this column). Maintained by a post-merge UPDATE inside `dlt/somerville_311_pipeline.py`, not by dlt's payload.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 2
dates 2026-06-24 → 2026-10-03 · span 101 days
_source_endpoint
VARCHAR
**PIPELINE METADATA -- not for analysis.** SODA endpoint URL this row was extracted from. Static for this project; present structurally to support multi-source extensions.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 1
len 53..53
  • https://data.somervillema.gov/resource/4pyi-uqq6.json 1,224,700 (100.0%)
accuracy
VARCHAR
Post-resolution survey field for accuracy. Sparse (<1% of rows) -- see [docs/limitations/2024-survey-columns-sparse.md](../../../docs/limitations/2024-survey-columns-sparse.md).
rows 1,224,700 · non-null 8,273 (0.7%) · distinct 8
len 3..34
  • Very Satisfied 6,352 (76.78%)
  • Very Dissatisfied 837 (10.12%)
  • Somewhat Satisfied 348 (4.21%)
  • Neither Satisfied nor Dissatisfied 229 (2.77%)
  • Somewhat Dissatisfied 200 (2.42%)
block_code
VARCHAR
Block identifier. Padded with spaces in source when unknown -- see [docs/limitations/block-code-padded.md](../../../docs/limitations/block-code-padded.md). Cleanup deferred to Silver (MVP 3).
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 763
len 15..15
  • NA 464,644 (37.94%)
  • 250173513001000 13,936 (1.14%)
  • 250173512032001 7,270 (0.59%)
  • 250173501081004 6,654 (0.54%)
  • 250173514043005 6,181 (0.5%)
category
VARCHAR
Mid-level grouping within classification (e.g. Parking, Trash & Recycling).
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 22
len 4..33
  • Information - Various Other 463,990 (37.89%)
  • Information - Parking 200,526 (16.37%)
  • Parking 154,249 (12.59%)
  • Trash & Recycling 85,426 (6.98%)
  • Information - DPW 80,596 (6.58%)
classification
VARCHAR
Top of the type hierarchy. One of Service, Information, Feedback.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 3
len 7..11
  • Information 745,112 (60.84%)
  • Service 468,393 (38.25%)
  • Feedback 11,195 (0.91%)
courtesy
VARCHAR
Post-resolution survey field for courtesy. Sparse (<1% of rows) -- see [docs/limitations/2024-survey-columns-sparse.md](../../../docs/limitations/2024-survey-columns-sparse.md).
rows 1,224,700 · non-null 8,252 (0.7%) · distinct 8
len 3..34
  • Very Satisfied 6,900 (83.62%)
  • Very Dissatisfied 358 (4.34%)
  • N/A 311 (3.77%)
  • Somewhat Satisfied 237 (2.87%)
  • Neither Satisfied nor Dissatisfied 230 (2.79%)
date_created
VARCHAR
Timestamp the request was opened. VARCHAR at bronze; cast to TIMESTAMPTZ in gold.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 884,342
len 22..22
  • 2026-09-27 12:00:00+00 33 (0.0%)
  • 2019-12-13 19:11:00+00 24 (0.0%)
  • 2019-11-22 17:32:00+00 17 (0.0%)
  • 2019-10-25 18:50:00+00 16 (0.0%)
  • 2019-12-06 17:44:00+00 15 (0.0%)
ease
VARCHAR
Post-resolution survey field for ease. Sparse (<1% of rows) -- see [docs/limitations/2024-survey-columns-sparse.md](../../../docs/limitations/2024-survey-columns-sparse.md).
rows 1,224,700 · non-null 6,936 (0.6%) · distinct 8
len 3..34
  • Very Satisfied 5,652 (81.49%)
  • Somewhat Satisfied 578 (8.33%)
  • Very Dissatisfied 266 (3.84%)
  • Neither Satisfied nor Dissatisfied 200 (2.88%)
  • Somewhat Dissatisfied 123 (1.77%)
emergency_readiness_and_response_planning
VARCHAR
Department tag stored as VARCHAR '0' or '1'. Cast to BOOLEAN in gold (`is_emergency_readiness_tag`). Sparse -- only ~4,187 rows of 1.17M carry any dept tag.
rows 1,224,700 · non-null 1,224,699 (100.0%) · distinct 2
len 1..1
  • 0 1,222,596 (99.83%)
  • 1 2,103 (0.17%)
green_space_care_and_maintenance
VARCHAR
Department tag stored as VARCHAR '0' or '1'. Cast to BOOLEAN in gold (`is_green_space_tag`). Sparse.
rows 1,224,700 · non-null 1,224,699 (100.0%) · distinct 2
len 1..1
  • 0 1,212,385 (98.99%)
  • 1 12,314 (1.01%)
id
VARCHAR
Somerville 311 request ID. Primary key from source; also the dlt merge key.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 1,224,700
len 6..7
  • 1000001 1 (0.0%)
  • 1000002 1 (0.0%)
  • 1000003 1 (0.0%)
  • 1000004 1 (0.0%)
  • 1000005 1 (0.0%)
infrastructure_maintenance_and_repairs
VARCHAR
Department tag stored as VARCHAR '0' or '1'. Cast to BOOLEAN in gold (`is_infrastructure_tag`). Sparse.
rows 1,224,700 · non-null 1,224,699 (100.0%) · distinct 2
len 1..1
  • 0 1,203,939 (98.3%)
  • 1 20,760 (1.7%)
most_recent_status
VARCHAR
Latest status of the request (Open / Closed / In Progress / On Hold).
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 4
len 4..11
  • Closed 1,208,891 (98.71%)
  • Open 9,183 (0.75%)
  • In Progress 6,400 (0.52%)
  • On Hold 226 (0.02%)
most_recent_status_date
VARCHAR
Timestamp the most recent status was last set. VARCHAR at bronze; cast in gold.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 791,274
len 22..22
  • 2026-02-06 08:37:00+00 626 (0.05%)
  • 2023-05-15 10:49:00+00 460 (0.04%)
  • 2026-02-06 08:29:00+00 435 (0.04%)
  • 2026-08-10 13:44:00+00 410 (0.03%)
  • 2022-09-20 09:05:00+00 388 (0.03%)
navigating_city_services_and_policies
VARCHAR
Department tag stored as VARCHAR '0' or '1'. Cast to BOOLEAN in gold (`is_city_services_tag`). Sparse.
rows 1,224,700 · non-null 1,224,699 (100.0%) · distinct 2
len 1..1
  • 0 906,601 (74.03%)
  • 1 318,098 (25.97%)
noise_and_activity_disturbances
VARCHAR
Department tag stored as VARCHAR '0' or '1'. Cast to BOOLEAN in gold (`is_noise_tag`). Sparse.
rows 1,224,700 · non-null 1,224,699 (100.0%) · distinct 2
len 1..1
  • 0 1,221,009 (99.7%)
  • 1 3,690 (0.3%)
origin_of_request
VARCHAR
How the request was submitted (Contact Center, Website, etc.). Becomes `origin` in gold.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 3
len 7..14
  • Contact Center 1,134,383 (92.63%)
  • Website 46,497 (3.8%)
  • 311 Mobile App 43,820 (3.58%)
overallexperience
VARCHAR
Post-resolution survey field for overall experience. Sparse (<1% of rows) -- see [docs/limitations/2024-survey-columns-sparse.md](../../../docs/limitations/2024-survey-columns-sparse.md).
rows 1,224,700 · non-null 8,318 (0.7%) · distinct 8
len 3..34
  • Very Satisfied 6,257 (75.22%)
  • Very Dissatisfied 1,050 (12.62%)
  • Somewhat Satisfied 354 (4.26%)
  • Somewhat Dissatisfied 273 (3.28%)
  • Neither Satisfied nor Dissatisfied 209 (2.51%)
public_space_cleanliness_and_environmental_health
VARCHAR
Department tag stored as VARCHAR '0' or '1'. Cast to BOOLEAN in gold (`is_public_space_tag`). Sparse.
rows 1,224,700 · non-null 1,224,699 (100.0%) · distinct 2
len 1..1
  • 0 1,201,098 (98.07%)
  • 1 23,601 (1.93%)
reliable_service_delivery
VARCHAR
Department tag stored as VARCHAR '0' or '1'. Cast to BOOLEAN in gold (`is_reliable_service_tag`). Sparse.
rows 1,224,700 · non-null 1,224,699 (100.0%) · distinct 2
len 1..1
  • 0 1,212,244 (98.98%)
  • 1 12,455 (1.02%)
type
VARCHAR
Leaf level of the source classification/category/type hierarchy. Becomes `request_type` in gold.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 352
len 3..64
  • Welcome desk-information 87,257 (7.12%)
  • Obtain a parking permit inquiry 76,635 (6.26%)
  • Temporary no parking sign posting 46,841 (3.82%)
  • ISD-Buildings inquiry 37,064 (3.03%)
  • Illegal parking 33,829 (2.76%)
voting_and_election_information
VARCHAR
Department tag stored as VARCHAR '0' or '1'. Cast to BOOLEAN in gold (`is_voting_tag`). Sparse.
rows 1,224,700 · non-null 1,224,699 (100.0%) · distinct 2
len 1..1
  • 0 1,222,805 (99.85%)
  • 1 1,894 (0.15%)
ward
VARCHAR
Somerville ward 1-7 as VARCHAR. NULL when unknown.
rows 1,224,700 · non-null 759,598 (62.0%) · distinct 7
len 1..1
  • 3 127,972 (16.85%)
  • 1 118,949 (15.66%)
  • 5 115,383 (15.19%)
  • 2 111,656 (14.7%)
  • 6 104,173 (13.71%)

main_bronze.raw_somerville_at_a_glance

749 rows · 11 columns
_dlt_id
VARCHAR
dlt row identifier -- retained at every layer for source-to-pipeline lineage.
rows 749 · non-null 749 (100.0%) · distinct 749
len 14..14
  • ++KXoxfiAWOd7g 1 (0.13%)
  • +5jTe+JEorcXLQ 1 (0.13%)
  • +AR7TCtMCbnzWA 1 (0.13%)
  • +IgSHTvs3IHlVQ 1 (0.13%)
  • +KkIZcWj3GqXMQ 1 (0.13%)
_dlt_load_id
VARCHAR
dlt load identifier -- retained at every layer for source-to-pipeline lineage.
rows 749 · non-null 749 (100.0%) · distinct 1
len 18..18
  • 1778815133.3910406 749 (100.0%)
_extracted_at
TIMESTAMP WITH TIME ZONE
**PIPELINE METADATA.** UTC timestamp of the run that touched the row.
rows 749 · non-null 749 (100.0%) · distinct 1
dates 2026-05-15 → 2026-05-15
_extracted_run_id
VARCHAR
**PIPELINE METADATA.** ULID of the manual ingestion run.
rows 749 · non-null 749 (100.0%) · distinct 1
len 26..26
  • 01KRMT9SFH4ZZ7Q2NDK1G7GY1X 749 (100.0%)
_source_endpoint
VARCHAR
**PIPELINE METADATA.** Source SODA URL.
rows 749 · non-null 749 (100.0%) · distinct 1
len 53..53
  • https://data.somervillema.gov/resource/jnde-mi6j.json 749 (100.0%)
description
VARCHAR
Sub-label within the topic identifying the exact metric variant (e.g. "Total Population", "30-Year Fixed Median Rent"). Use together with `topic` for human-readable axis labels.
rows 749 · non-null 749 (100.0%) · distinct 81
len 6..57
  • Total Population 31 (4.14%)
  • Bachelor's degree or higher 30 (4.01%)
  • Car, truck, or van 30 (4.01%)
  • Foreign born 30 (4.01%)
  • Mean Travel Time to Work 30 (4.01%)
geography
VARCHAR
Comparison geography. "Somerville" for the city; "Massachusetts" for the state comparator.
rows 749 · non-null 749 (100.0%) · distinct 2
len 10..13
  • Somerville 459 (61.28%)
  • Massachusetts 290 (38.72%)
topic
VARCHAR
Top-level metric grouping (e.g. "Population", "Median Rent Overtime", "Educational Attainment"). 25 distinct values.
rows 749 · non-null 749 (100.0%) · distinct 25
len 7..32
  • Language Use Overtime 75 (10.01%)
  • Owners vs Renters 60 (8.01%)
  • Commute Mode Over Time 56 (7.48%)
  • Place of Work 48 (6.41%)
  • Household Size Owners vs Renters 45 (6.01%)
units
VARCHAR
Unit of measure for `value` (e.g. "People", "USD", "Percent"). Source-provided; not a normalized vocabulary.
rows 749 · non-null 749 (100.0%) · distinct 5
len 4..7
  • Percent 521 (69.56%)
  • Dollars 102 (13.62%)
  • People 76 (10.15%)
  • Minutes 30 (4.01%)
  • None 20 (2.67%)
value
VARCHAR
Numeric metric value in the unit named by `units`. Population: people; rent / income: USD; percentages: 0-100.
rows 749 · non-null 749 (100.0%) · distinct 491
len 1..9
  • 4.7 11 (1.47%)
  • 4.5 8 (1.07%)
  • 5 7 (0.93%)
  • 1.2 6 (0.8%)
  • 4.2 6 (0.8%)
year
VARCHAR
Calendar year for the metric value. 1850-2024 range; most topics 2010-2023 (ACS), Population goes back to 1850.
rows 749 · non-null 749 (100.0%) · distinct 31
len 4..4
  • 2024 171 (22.83%)
  • 2022 51 (6.81%)
  • 2015 41 (5.47%)
  • 2016 41 (5.47%)
  • 2017 41 (5.47%)

main_bronze.raw_somerville_crime

23,448 rows · 17 columns
_dlt_id
VARCHAR
dlt row identifier -- retained at every layer for source-to-pipeline lineage.
rows 23,448 · non-null 23,448 (100.0%) · distinct 23,448
len 14..14
  • ++JKj8TQSMsZZA 1 (0.0%)
  • ++ROvYBdNAoa7A 1 (0.0%)
  • ++SA6Ihg201YEA 1 (0.0%)
  • ++vD2YUqsoZ98A 1 (0.0%)
  • +/IIN/HrGTTxPw 1 (0.0%)
_dlt_load_id
VARCHAR
dlt load identifier -- retained at every layer for source-to-pipeline lineage.
rows 23,448 · non-null 23,448 (100.0%) · distinct 1
len 18..18
  • 1791022633.9007049 23,448 (100.0%)
_extracted_at
TIMESTAMP WITH TIME ZONE
**PIPELINE METADATA.** UTC timestamp of the run that touched the row.
rows 23,448 · non-null 23,448 (100.0%) · distinct 1
dates 2026-10-03 → 2026-10-03
_extracted_run_id
VARCHAR
**PIPELINE METADATA.** ULID of the pipeline run.
rows 23,448 · non-null 23,448 (100.0%) · distinct 1
len 26..26
  • 01M40KA281T5KQQG9GX4F73XQE 23,448 (100.0%)
_first_seen_at
TIMESTAMP
**PIPELINE METADATA.** UTC timestamp when this row was first ingested. Preserved across re-extractions.
rows 23,448 · non-null 23,448 (100.0%) · distinct 1
dates 2026-10-03 → 2026-10-03
_source_endpoint
VARCHAR
**PIPELINE METADATA.** Source SODA URL.
rows 23,448 · non-null 23,448 (100.0%) · distinct 1
len 53..53
  • https://data.somervillema.gov/resource/aghs-hqvg.json 23,448 (100.0%)
blockcode
VARCHAR
Fixed-length 15-character census block code (state + county + tract + block + suffix). Geographic granularity floor -- ~750 blocks across Somerville.
rows 23,448 · non-null 20,650 (88.1%) · distinct 679
len 15..15
  • 250173501061014 671 (3.25%)
  • 250173515001007 388 (1.88%)
  • 250173501061015 357 (1.73%)
  • 250173515003010 330 (1.6%)
  • 250173501061005 318 (1.54%)
category
VARCHAR
Top-level NIBRS category. One of "Crimes against Property" / "Crimes against Person" / "Crimes against Society" / "Other".
rows 23,448 · non-null 23,448 (100.0%) · distinct 4
len 5..23
  • Crimes against Property 14,861 (63.38%)
  • Other 4,705 (20.07%)
  • Crimes against Person 3,435 (14.65%)
  • Crimes against Society 447 (1.91%)
day_and_month
VARCHAR
Calendar day + month (e.g. "1/9"). NULL or blank for sensitive incidents stripped of time at source.
rows 23,448 · non-null 20,650 (88.1%) · distinct 366
len 3..5
  • 8/28 90 (0.44%)
  • 6/8 82 (0.4%)
  • 6/24 78 (0.38%)
  • 11/2 77 (0.37%)
  • 6/15 77 (0.37%)
incdesc
VARCHAR
NIBRS standard definition of the offense type. Generic legal text, not victim-specific narrative.
rows 23,448 · non-null 23,448 (100.0%) · distinct 40
len 61..499
  • All thefts which do not fit any of the Larceny/Theft or specific subcategories identified. 4,367 (18.62%)
  • Other Criminal MV Offenses represent select criminal motor vehicle offenses (with the exception of Driving Under the Influence, Hit and Run of a person, and Vehicular Manslaughter). Examples include: 2,702 (11.52%)
  • An unlawful physical attack by one person upon another where neither the offender displays a dangerous weapon, nor the victim suffers obvious severe or aggravated bodily injury involving apparent brok 1,812 (7.73%)
  • To willfully or maliciously destroy, damage, deface, or otherwise injure any public or private property without the consent of the owner or the person having custody or control of it. 1,787 (7.62%)
  • The unlawful taking of articles from a motor vehicle, locked or unlocked. 1,652 (7.05%)
incnum
VARCHAR
Incident number -- case identifier from SPD records management. Primary key from source; the dlt merge key.
rows 23,448 · non-null 23,448 (100.0%) · distinct 23,448
len 8..9
  • 100000001 1 (0.0%)
  • 100000002 1 (0.0%)
  • 100000003 1 (0.0%)
  • 100000004 1 (0.0%)
  • 100000005 1 (0.0%)
offense
VARCHAR
Offense type label.
rows 23,448 · non-null 23,448 (100.0%) · distinct 40
len 5..31
  • All Other Larceny 4,367 (18.62%)
  • Other Criminal MV Offenses 2,702 (11.52%)
  • Simple Assault 1,812 (7.73%)
  • Destruction/Vandalism Property 1,787 (7.62%)
  • Theft From Motor Vehicle 1,652 (7.05%)
offensecode
VARCHAR
Three-character NIBRS offense code.
rows 23,448 · non-null 23,448 (100.0%) · distinct 40
len 3..19
  • 23H 4,367 (18.62%)
  • 991, 998, 999 2,702 (11.52%)
  • 13B 1,812 (7.73%)
  • 290 1,787 (7.62%)
  • 23F 1,652 (7.05%)
offensetype
VARCHAR
Sub-category grouping similar offense types.
rows 23,448 · non-null 23,448 (100.0%) · distinct 29
len 5..30
  • Larceny/Theft Offenses 6,907 (29.46%)
  • Assault Offenses 3,003 (12.81%)
  • Other Criminal MV Offenses 2,702 (11.52%)
  • Destruction/Vandalism Property 1,787 (7.62%)
  • Fraud Offenses 1,732 (7.39%)
police_shift
VARCHAR
Shift during which the incident was reported (e.g. "First Half (4PM - Midnight)").
rows 23,448 · non-null 20,650 (88.1%) · distinct 3
len 21..27
  • Day Shift (8AM - 4PM) 10,415 (50.44%)
  • First Half (4PM - Midnight) 8,359 (40.48%)
  • Last Half (Midnight - 8AM) 1,876 (9.08%)
ward
VARCHAR
Ward number (1-7) -- **space-padded to length 15 in source** (e.g. "1 "). Trim at join to gold dim_ward.ward. NULL for ~13% of rows.
rows 23,448 · non-null 20,532 (87.6%) · distinct 8
len 15..15
  • 1 6,251 (30.45%)
  • 2 3,984 (19.4%)
  • 4 2,700 (13.15%)
  • 6 2,577 (12.55%)
  • 3 1,884 (9.18%)
year
VARCHAR
Year the incident was reported (VARCHAR; source values "2017"-"2026" as of 2026-05-13).
rows 23,448 · non-null 23,448 (100.0%) · distinct 10
len 4..4
  • 2024 2,990 (12.75%)
  • 2025 2,759 (11.77%)
  • 2023 2,608 (11.12%)
  • 2017 2,378 (10.14%)
  • 2022 2,338 (9.97%)

main_bronze.raw_somerville_happiness_survey

12,583 rows · 155 columns
_dlt_id
VARCHAR
dlt row identifier -- retained at every layer for source-to-pipeline lineage.
rows 12,583 · non-null 12,583 (100.0%) · distinct 12,583
len 14..14
  • ++pRyTPVPQfPmg 1 (0.01%)
  • +/4AFKz8RSvjRw 1 (0.01%)
  • +/X+rYyLuxIxaw 1 (0.01%)
  • +/hK4fwdlM8A3Q 1 (0.01%)
  • +0WDtBJ4Htl1Rg 1 (0.01%)
_dlt_load_id
VARCHAR
dlt load identifier -- retained at every layer for source-to-pipeline lineage.
rows 12,583 · non-null 12,583 (100.0%) · distinct 1
len 17..17
  • 1778813714.756941 12,583 (100.0%)
_extracted_at
TIMESTAMP WITH TIME ZONE
**PIPELINE METADATA.** UTC timestamp of the run that touched the row.
rows 12,583 · non-null 12,583 (100.0%) · distinct 1
dates 2026-05-15 → 2026-05-15
_extracted_run_id
VARCHAR
**PIPELINE METADATA.** ULID of the manual ingestion run.
rows 12,583 · non-null 12,583 (100.0%) · distinct 1
len 26..26
  • 01KRMRYG3HGQJBB8T1SBKF7AN9 12,583 (100.0%)
_source_endpoint
VARCHAR
**PIPELINE METADATA.** Source SODA URL.
rows 12,583 · non-null 12,583 (100.0%) · distinct 1
len 53..53
  • https://data.somervillema.gov/resource/wmeh-zuz2.json 12,583 (100.0%)
acs_somerville_avg_household
VARCHAR
ACS Somerville average household size for the respondent's tract / `census_year`.
rows 12,583 · non-null 12,583 (100.0%) · distinct 7
len 3..4
  • 2.29 7,012 (55.73%)
  • 2.44 1,496 (11.89%)
  • 2.26 1,392 (11.06%)
  • 2.19 1,298 (10.32%)
  • 2.1 1,006 (7.99%)
acs_somerville_median_income
VARCHAR
ACS Somerville median household income for the respondent's census tract / `census_year`.
rows 12,583 · non-null 12,583 (100.0%) · distinct 8
len 5..6
  • 61241 6,167 (49.01%)
  • 100643 1,496 (11.89%)
  • 142553 1,392 (11.06%)
  • 107651 1,298 (10.32%)
  • 126619 1,006 (7.99%)
adult_education_learning_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,360 (10.8%) · distinct 6
len 7..16
  • Not Sure 715 (52.57%)
  • Neutral 289 (21.25%)
  • Satisfied 129 (9.49%)
  • Unsatisfied 128 (9.41%)
  • Very Satisfied 56 (4.12%)
adult_education_learning_satisfaction_num
VARCHAR
rows 12,583 · non-null 645 (5.1%) · distinct 5
len 1..1
  • 3 289 (44.81%)
  • 4 129 (20.0%)
  • 2 128 (19.84%)
  • 5 56 (8.68%)
  • 1 43 (6.67%)
adults_in_household
VARCHAR
rows 12,583 · non-null 1,351 (10.7%) · distinct 7
len 7..20
  • 2 adults 795 (58.85%)
  • 1 adult 320 (23.69%)
  • 3 adults 136 (10.07%)
  • 4 adults 64 (4.74%)
  • Prefer not to answer 21 (1.55%)
age
VARCHAR
Self-reported age bucket (e.g. "35 to 44"). Bucketed at source to reduce quasi-identifier risk.
rows 12,583 · non-null 11,734 (93.3%) · distinct 9
len 8..20
  • 25 to 34 4,071 (34.69%)
  • 35 to 44 2,410 (20.54%)
  • 45 to 54 1,455 (12.4%)
  • 55 to 64 1,378 (11.74%)
  • 65 to 74 1,098 (9.36%)
air_pollution_concern_label
VARCHAR
rows 12,583 · non-null 1,380 (11.0%) · distinct 4
len 8..18
  • Not Concerned 567 (41.09%)
  • Somewhat Concerned 539 (39.06%)
  • Very Concerned 230 (16.67%)
  • Not Sure 44 (3.19%)
air_pollution_concern_num
VARCHAR
rows 12,583 · non-null 1,336 (10.6%) · distinct 3
len 1..1
  • 1 567 (42.44%)
  • 2 539 (40.34%)
  • 3 230 (17.22%)
availability_childcare_0_4_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,366 (10.9%) · distinct 6
len 7..16
  • Not Sure 877 (64.2%)
  • Neutral 256 (18.74%)
  • Unsatisfied 94 (6.88%)
  • Satisfied 55 (4.03%)
  • Very Unsatisfied 55 (4.03%)
availability_childcare_0_4_satisfaction_num
VARCHAR
rows 12,583 · non-null 489 (3.9%) · distinct 5
len 1..1
  • 3 256 (52.35%)
  • 2 94 (19.22%)
  • 1 55 (11.25%)
  • 4 55 (11.25%)
  • 5 29 (5.93%)
availability_ost_activities_6_8_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,360 (10.8%) · distinct 6
len 7..16
  • Not Sure 950 (69.85%)
  • Neutral 243 (17.87%)
  • Unsatisfied 54 (3.97%)
  • Satisfied 46 (3.38%)
  • Very Unsatisfied 40 (2.94%)
availability_ost_activities_6_8_satisfaction_num
VARCHAR
rows 12,583 · non-null 410 (3.3%) · distinct 5
len 1..1
  • 3 243 (59.27%)
  • 2 54 (13.17%)
  • 4 46 (11.22%)
  • 1 40 (9.76%)
  • 5 27 (6.59%)
availability_ost_activities_9_12_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,354 (10.8%) · distinct 6
len 7..16
  • Not Sure 955 (70.53%)
  • Neutral 239 (17.65%)
  • Unsatisfied 49 (3.62%)
  • Satisfied 41 (3.03%)
  • Very Unsatisfied 38 (2.81%)
availability_ost_activities_9_12_satisfaction_num
VARCHAR
rows 12,583 · non-null 399 (3.2%) · distinct 5
len 1..1
  • 3 239 (59.9%)
  • 2 49 (12.28%)
  • 4 41 (10.28%)
  • 1 38 (9.52%)
  • 5 32 (8.02%)
availability_ost_care_pk_5_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,361 (10.8%) · distinct 6
len 7..16
  • Not Sure 913 (67.08%)
  • Neutral 260 (19.1%)
  • Unsatisfied 63 (4.63%)
  • Very Unsatisfied 50 (3.67%)
  • Satisfied 46 (3.38%)
availability_ost_care_pk_5_satisfaction_num
VARCHAR
rows 12,583 · non-null 448 (3.6%) · distinct 5
len 1..1
  • 3 260 (58.04%)
  • 2 63 (14.06%)
  • 1 50 (11.16%)
  • 4 46 (10.27%)
  • 5 29 (6.47%)
basic_needs_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,380 (11.0%) · distinct 6
len 7..16
  • Very Satisfied 887 (64.28%)
  • Satisfied 326 (23.62%)
  • Neutral 99 (7.17%)
  • Unsatisfied 41 (2.97%)
  • Very Unsatisfied 15 (1.09%)
basic_needs_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,368 (10.9%) · distinct 5
len 1..1
  • 5 887 (64.84%)
  • 4 326 (23.83%)
  • 3 99 (7.24%)
  • 2 41 (3.0%)
  • 1 15 (1.1%)
beauty_neighborhood_satisfaction_label
VARCHAR
rows 12,583 · non-null 6,290 (50.0%) · distinct 6
len 7..16
  • Satisfied 2,502 (39.78%)
  • Very Satisfied 1,513 (24.05%)
  • Neutral 1,434 (22.8%)
  • Unsatisfied 569 (9.05%)
  • Very Unsatisfied 266 (4.23%)
beauty_neighborhood_satisfaction_num
VARCHAR
rows 12,583 · non-null 6,284 (49.9%) · distinct 5
len 1..1
  • 4 2,502 (39.82%)
  • 5 1,513 (24.08%)
  • 3 1,434 (22.82%)
  • 2 569 (9.05%)
  • 1 266 (4.23%)
bedrooms
VARCHAR
rows 12,583 · non-null 2,296 (18.2%) · distinct 8
len 9..25
  • 2 bedrooms 923 (40.2%)
  • 3 bedrooms 604 (26.31%)
  • 1 bedroom 451 (19.64%)
  • 4 bedrooms 196 (8.54%)
  • 5 or more bedrooms 50 (2.18%)
buildings_maintenance_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,380 (11.0%) · distinct 6
len 7..16
  • Satisfied 391 (28.33%)
  • Neutral 363 (26.3%)
  • Not Sure 196 (14.2%)
  • Very Satisfied 159 (11.52%)
  • Unsatisfied 157 (11.38%)
buildings_maintenance_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,184 (9.4%) · distinct 5
len 1..1
  • 4 391 (33.02%)
  • 3 363 (30.66%)
  • 5 159 (13.43%)
  • 2 157 (13.26%)
  • 1 114 (9.63%)
built_environment_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,381 (11.0%) · distinct 6
len 7..16
  • Satisfied 444 (32.15%)
  • Neutral 361 (26.14%)
  • Unsatisfied 268 (19.41%)
  • Very Unsatisfied 173 (12.53%)
  • Very Satisfied 124 (8.98%)
built_environment_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,370 (10.9%) · distinct 5
len 1..1
  • 4 444 (32.41%)
  • 3 361 (26.35%)
  • 2 268 (19.56%)
  • 1 173 (12.63%)
  • 5 124 (9.05%)
cars
VARCHAR
rows 12,583 · non-null 2,300 (18.3%) · distinct 7
len 5..20
  • 1 car 1,251 (54.39%)
  • 2 cars 557 (24.22%)
  • 0 cars 375 (16.3%)
  • 3 cars 76 (3.3%)
  • 4 cars 20 (0.87%)
census_year
VARCHAR
ACS reference year for the per-respondent ACS-context columns below.
rows 12,583 · non-null 12,583 (100.0%) · distinct 8
len 4..4
  • 2011 6,167 (49.01%)
  • 2019 1,496 (11.89%)
  • 2024 1,392 (11.06%)
  • 2021 1,298 (10.32%)
  • 2023 1,006 (7.99%)
children_in_household
VARCHAR
rows 12,583 · non-null 1,272 (10.1%) · distinct 7
len 7..20
  • 0 children 1,012 (79.56%)
  • 1 child 139 (10.93%)
  • 2 children 84 (6.6%)
  • Prefer not to answer 23 (1.81%)
  • 3 children 12 (0.94%)
children_yn
VARCHAR
rows 12,583 · non-null 5,955 (47.3%) · distinct 2
len 1..1
  • 0 4,899 (82.27%)
  • 1 1,056 (17.73%)
city_processes_visible_label
VARCHAR
rows 12,583 · non-null 1,378 (11.0%) · distinct 6
len 5..17
  • Agree 415 (30.12%)
  • Neutral 373 (27.07%)
  • Disagree 273 (19.81%)
  • Strongly Agree 125 (9.07%)
  • Strongly Disagree 120 (8.71%)
city_processes_visible_num
VARCHAR
rows 12,583 · non-null 1,306 (10.4%) · distinct 5
len 1..1
  • 4 415 (31.78%)
  • 3 373 (28.56%)
  • 2 273 (20.9%)
  • 5 125 (9.57%)
  • 1 120 (9.19%)
city_services_facilities_accessibility_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,361 (10.8%) · distinct 6
len 7..16
  • Not Sure 707 (51.95%)
  • Neutral 310 (22.78%)
  • Satisfied 170 (12.49%)
  • Very Satisfied 96 (7.05%)
  • Unsatisfied 51 (3.75%)
city_services_facilities_accessibility_satisfaction_num
VARCHAR
rows 12,583 · non-null 654 (5.2%) · distinct 5
len 1..1
  • 3 310 (47.4%)
  • 4 170 (25.99%)
  • 5 96 (14.68%)
  • 2 51 (7.8%)
  • 1 27 (4.13%)
city_services_information_availability_satisfaction_label
VARCHAR
rows 12,583 · non-null 6,059 (48.2%) · distinct 6
len 7..16
  • Satisfied 2,389 (39.43%)
  • Very Satisfied 1,873 (30.91%)
  • Neutral 1,208 (19.94%)
  • Unsatisfied 341 (5.63%)
  • Very Unsatisfied 149 (2.46%)
city_services_information_availability_satisfaction_num
VARCHAR
rows 12,583 · non-null 5,960 (47.4%) · distinct 5
len 1..1
  • 4 2,389 (40.08%)
  • 5 1,873 (31.43%)
  • 3 1,208 (20.27%)
  • 2 341 (5.72%)
  • 1 149 (2.5%)
city_services_quality_satisfaction_label
VARCHAR
rows 12,583 · non-null 2,370 (18.8%) · distinct 6
len 7..16
  • Satisfied 1,075 (45.36%)
  • Very Satisfied 692 (29.2%)
  • Neutral 373 (15.74%)
  • Unsatisfied 135 (5.7%)
  • Very Unsatisfied 69 (2.91%)
city_services_quality_satisfaction_num
VARCHAR
rows 12,583 · non-null 2,344 (18.6%) · distinct 5
len 1..1
  • 4 1,075 (45.86%)
  • 5 692 (29.52%)
  • 3 373 (15.91%)
  • 2 135 (5.76%)
  • 1 69 (2.94%)
civic_participation_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,376 (10.9%) · distinct 6
len 7..16
  • Satisfied 468 (34.01%)
  • Neutral 361 (26.24%)
  • Very Satisfied 295 (21.44%)
  • Not Sure 115 (8.36%)
  • Unsatisfied 96 (6.98%)
civic_participation_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,261 (10.0%) · distinct 5
len 1..1
  • 4 468 (37.11%)
  • 3 361 (28.63%)
  • 5 295 (23.39%)
  • 2 96 (7.61%)
  • 1 41 (3.25%)
crossing_street_safety_concern_label
VARCHAR
rows 12,583 · non-null 2,376 (18.9%) · distinct 4
len 8..18
  • Somewhat Concerned 940 (39.56%)
  • Not Concerned 869 (36.57%)
  • Very Concerned 557 (23.44%)
  • Not Sure 10 (0.42%)
crossing_street_safety_concern_num
VARCHAR
rows 12,583 · non-null 2,366 (18.8%) · distinct 3
len 1..1
  • 2 940 (39.73%)
  • 1 869 (36.73%)
  • 3 557 (23.54%)
cultural_religious_minority_yn
VARCHAR
rows 12,583 · non-null 2,039 (16.2%) · distinct 2
len 1..1
  • 0 1,684 (82.59%)
  • 1 355 (17.41%)
describe_yourself_prefer_not_to_answer_yn
VARCHAR
rows 12,583 · non-null 2,226 (17.7%) · distinct 2
len 1..1
  • 0 2,039 (91.6%)
  • 1 187 (8.4%)
different_backgrounds_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,384 (11.0%) · distinct 6
len 7..16
  • Satisfied 548 (39.6%)
  • Neutral 352 (25.43%)
  • Very Satisfied 310 (22.4%)
  • Unsatisfied 91 (6.58%)
  • Not Sure 47 (3.4%)
different_backgrounds_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,337 (10.6%) · distinct 5
len 1..1
  • 4 548 (40.99%)
  • 3 352 (26.33%)
  • 5 310 (23.19%)
  • 2 91 (6.81%)
  • 1 36 (2.69%)
difficulty_paying_food_yn
VARCHAR
rows 12,583 · non-null 2,232 (17.7%) · distinct 2
len 1..1
  • 0 2,074 (92.92%)
  • 1 158 (7.08%)
difficulty_paying_healthcare_yn
VARCHAR
rows 12,583 · non-null 1,256 (10.0%) · distinct 2
len 1..1
  • 0 1,146 (91.24%)
  • 1 110 (8.76%)
difficulty_paying_housing_yn
VARCHAR
rows 12,583 · non-null 2,232 (17.7%) · distinct 2
len 1..1
  • 0 1,979 (88.66%)
  • 1 253 (11.34%)
difficulty_paying_other_yn
VARCHAR
rows 12,583 · non-null 2,232 (17.7%) · distinct 2
len 1..1
  • 0 2,197 (98.43%)
  • 1 35 (1.57%)
difficulty_paying_prefer_not_to_answer_yn
VARCHAR
rows 12,583 · non-null 2,303 (18.3%) · distinct 2
len 1..1
  • 0 2,232 (96.92%)
  • 1 71 (3.08%)
difficulty_paying_utilities_yn
VARCHAR
rows 12,583 · non-null 2,232 (17.7%) · distinct 2
len 1..1
  • 0 1,986 (88.98%)
  • 1 246 (11.02%)
difficulty_paying_yn
VARCHAR
rows 12,583 · non-null 2,232 (17.7%) · distinct 2
len 1..1
  • 0 1,799 (80.6%)
  • 1 433 (19.4%)
disability_yn
VARCHAR
rows 12,583 · non-null 3,298 (26.2%) · distinct 2
len 1..1
  • 0 2,991 (90.69%)
  • 1 307 (9.31%)
education_quality_satisfaction_label
VARCHAR
rows 12,583 · non-null 8,736 (69.4%) · distinct 6
len 7..16
  • Neutral 3,706 (42.42%)
  • Satisfied 2,121 (24.28%)
  • Unsatisfied 989 (11.32%)
  • Very Satisfied 814 (9.32%)
  • Not Sure 765 (8.76%)
education_quality_satisfaction_num
VARCHAR
rows 12,583 · non-null 7,971 (63.3%) · distinct 5
len 1..1
  • 3 3,706 (46.49%)
  • 4 2,121 (26.61%)
  • 2 989 (12.41%)
  • 5 814 (10.21%)
  • 1 341 (4.28%)
efforts_become_more_modern_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,370 (10.9%) · distinct 6
len 7..16
  • Satisfied 514 (37.52%)
  • Neutral 332 (24.23%)
  • Very Satisfied 201 (14.67%)
  • Not Sure 162 (11.82%)
  • Unsatisfied 100 (7.3%)
efforts_become_more_modern_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,208 (9.6%) · distinct 5
len 1..1
  • 4 514 (42.55%)
  • 3 332 (27.48%)
  • 5 201 (16.64%)
  • 2 100 (8.28%)
  • 1 61 (5.05%)
efforts_decrease_rats_mice_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,370 (10.9%) · distinct 6
len 7..16
  • Neutral 324 (23.65%)
  • Satisfied 304 (22.19%)
  • Unsatisfied 266 (19.42%)
  • Very Unsatisfied 212 (15.47%)
  • Not Sure 177 (12.92%)
efforts_decrease_rats_mice_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,193 (9.5%) · distinct 5
len 1..1
  • 3 324 (27.16%)
  • 4 304 (25.48%)
  • 2 266 (22.3%)
  • 1 212 (17.77%)
  • 5 87 (7.29%)
efforts_reducing_ghg_emissions_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,359 (10.8%) · distinct 6
len 7..16
  • Satisfied 430 (31.64%)
  • Neutral 384 (28.26%)
  • Not Sure 221 (16.26%)
  • Very Satisfied 187 (13.76%)
  • Unsatisfied 86 (6.33%)
efforts_reducing_ghg_emissions_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,138 (9.0%) · distinct 5
len 1..1
  • 4 430 (37.79%)
  • 3 384 (33.74%)
  • 5 187 (16.43%)
  • 2 86 (7.56%)
  • 1 51 (4.48%)
emergency_response_quality_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,367 (10.9%) · distinct 6
len 7..16
  • Satisfied 427 (31.24%)
  • Very Satisfied 401 (29.33%)
  • Not Sure 342 (25.02%)
  • Neutral 162 (11.85%)
  • Unsatisfied 19 (1.39%)
emergency_response_quality_satisfaction_num
VARCHAR
Satisfaction with emergency response, 1-5 Likert.
rows 12,583 · non-null 1,025 (8.1%) · distinct 5
len 1..1
  • 4 427 (41.66%)
  • 5 401 (39.12%)
  • 3 162 (15.8%)
  • 2 19 (1.85%)
  • 1 16 (1.56%)
encourage_commercial_development_label
VARCHAR
rows 12,583 · non-null 1,372 (10.9%) · distinct 6
len 5..17
  • Agree 385 (28.06%)
  • Neutral 315 (22.96%)
  • Strongly Agree 228 (16.62%)
  • Disagree 185 (13.48%)
  • Not Sure 146 (10.64%)
encourage_commercial_development_num
VARCHAR
rows 12,583 · non-null 1,226 (9.7%) · distinct 5
len 1..1
  • 4 385 (31.4%)
  • 3 315 (25.69%)
  • 5 228 (18.6%)
  • 2 185 (15.09%)
  • 1 113 (9.22%)
enjoy_create_art_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,381 (11.0%) · distinct 6
len 7..16
  • Satisfied 473 (34.25%)
  • Neutral 360 (26.07%)
  • Very Satisfied 360 (26.07%)
  • Not Sure 131 (9.49%)
  • Unsatisfied 38 (2.75%)
enjoy_create_art_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,250 (9.9%) · distinct 5
len 1..1
  • 4 473 (37.84%)
  • 3 360 (28.8%)
  • 5 360 (28.8%)
  • 2 38 (3.04%)
  • 1 19 (1.52%)
feel_part_community_label
VARCHAR
rows 12,583 · non-null 1,383 (11.0%) · distinct 5
len 5..17
  • Agree 572 (41.36%)
  • Neutral 388 (28.05%)
  • Strongly Agree 219 (15.84%)
  • Disagree 143 (10.34%)
  • Strongly Disagree 61 (4.41%)
feel_part_community_num
VARCHAR
rows 12,583 · non-null 1,383 (11.0%) · distinct 5
len 1..1
  • 4 572 (41.36%)
  • 3 388 (28.05%)
  • 5 219 (15.84%)
  • 2 143 (10.34%)
  • 1 61 (4.41%)
feel_safe_somerville_label
VARCHAR
rows 12,583 · non-null 1,381 (11.0%) · distinct 5
len 5..17
  • Agree 596 (43.16%)
  • Strongly Agree 554 (40.12%)
  • Neutral 144 (10.43%)
  • Disagree 46 (3.33%)
  • Strongly Disagree 41 (2.97%)
feel_safe_somerville_num
VARCHAR
Sense of safety in Somerville, 1-5 Likert. Pairs with crime + traffic-citations data once joined.
rows 12,583 · non-null 1,381 (11.0%) · distinct 5
len 1..1
  • 4 596 (43.16%)
  • 5 554 (40.12%)
  • 3 144 (10.43%)
  • 2 46 (3.33%)
  • 1 41 (2.97%)
gender
VARCHAR
Self-reported gender. Open-ended values bucketed at source (Man / Woman / Non-binary / Prefer not to answer / etc.).
rows 12,583 · non-null 12,048 (95.7%) · distinct 4
len 3..20
  • Woman 7,281 (60.43%)
  • Man 4,525 (37.56%)
  • Prefer not to answer 134 (1.11%)
  • Nonbinary 108 (0.9%)
getting_around_convenience_satisfaction_label
VARCHAR
rows 12,583 · non-null 5,120 (40.7%) · distinct 6
len 7..16
  • Satisfied 1,894 (36.99%)
  • Very Satisfied 1,469 (28.69%)
  • Neutral 973 (19.0%)
  • Unsatisfied 481 (9.39%)
  • Very Unsatisfied 297 (5.8%)
getting_around_convenience_satisfaction_num
VARCHAR
rows 12,583 · non-null 5,114 (40.6%) · distinct 5
len 1..1
  • 4 1,894 (37.04%)
  • 5 1,469 (28.73%)
  • 3 973 (19.03%)
  • 2 481 (9.41%)
  • 1 297 (5.81%)
grocery_access_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,379 (11.0%) · distinct 6
len 7..16
  • Very Satisfied 590 (42.78%)
  • Satisfied 452 (32.78%)
  • Neutral 179 (12.98%)
  • Unsatisfied 118 (8.56%)
  • Very Unsatisfied 36 (2.61%)
grocery_access_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,375 (10.9%) · distinct 5
len 1..1
  • 5 590 (42.91%)
  • 4 452 (32.87%)
  • 3 179 (13.02%)
  • 2 118 (8.58%)
  • 1 36 (2.62%)
happiness_label
VARCHAR
Headline happiness label paired with `happiness_num`.
rows 12,583 · non-null 12,232 (97.2%) · distinct 5
len 5..12
  • Happy 5,899 (48.23%)
  • Very Happy 3,361 (27.48%)
  • Neutral 2,302 (18.82%)
  • Unhappy 444 (3.63%)
  • Very Unhappy 226 (1.85%)
happiness_num
VARCHAR
Headline happiness score, 1-5 Likert (1 = very unhappy, 5 = very happy).
rows 12,583 · non-null 12,232 (97.2%) · distinct 5
len 1..1
  • 4 5,899 (48.23%)
  • 5 3,361 (27.48%)
  • 3 2,302 (18.82%)
  • 2 444 (3.63%)
  • 1 226 (1.85%)
health_services_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,381 (11.0%) · distinct 6
len 7..16
  • Satisfied 481 (34.83%)
  • Very Satisfied 451 (32.66%)
  • Neutral 263 (19.04%)
  • Unsatisfied 117 (8.47%)
  • Very Unsatisfied 37 (2.68%)
health_services_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,349 (10.7%) · distinct 5
len 1..1
  • 4 481 (35.66%)
  • 5 451 (33.43%)
  • 3 263 (19.5%)
  • 2 117 (8.67%)
  • 1 37 (2.74%)
heard_by_city_label
VARCHAR
rows 12,583 · non-null 1,384 (11.0%) · distinct 6
len 5..17
  • Neutral 494 (35.69%)
  • Agree 361 (26.08%)
  • Disagree 186 (13.44%)
  • Strongly Disagree 141 (10.19%)
  • Not Sure 101 (7.3%)
heard_by_city_num
VARCHAR
Feels heard by the city, 1-5 Likert. Pairs with 311 responsiveness measures.
rows 12,583 · non-null 1,283 (10.2%) · distinct 5
len 1..1
  • 3 494 (38.5%)
  • 4 361 (28.14%)
  • 2 186 (14.5%)
  • 1 141 (10.99%)
  • 5 101 (7.87%)
highest_level_education
VARCHAR
Highest education level attained.
rows 12,583 · non-null 1,376 (10.9%) · distinct 6
len 5..48
  • Master's degree or higher 705 (51.24%)
  • College degree (e.g., associate's or bachelor's) 525 (38.15%)
  • High school diploma or GED 81 (5.89%)
  • Prefer not to answer 49 (3.56%)
  • Some school 15 (1.09%)
household_income
VARCHAR
Household income bucket. Bucketed at source.
rows 12,583 · non-null 11,442 (90.9%) · distinct 8
len 16..20
  • $200,000 or more 3,118 (27.25%)
  • $100,000 to $149,999 2,102 (18.37%)
  • $75,000 to $99,999 1,534 (13.41%)
  • $50,000 to $74,999 1,301 (11.37%)
  • $25,000 to $49,999 1,175 (10.27%)
household_size
VARCHAR
rows 12,583 · non-null 2,240 (17.8%) · distinct 7
len 8..20
  • 2 people 999 (44.6%)
  • 1 person 544 (24.29%)
  • 3 people 367 (16.38%)
  • 4 people 238 (10.62%)
  • 5 people 47 (2.1%)
housing_condition_satisfaction_label
VARCHAR
rows 12,583 · non-null 5,075 (40.3%) · distinct 6
len 7..16
  • Very Satisfied 2,013 (39.67%)
  • Satisfied 1,775 (34.98%)
  • Neutral 824 (16.24%)
  • Unsatisfied 332 (6.54%)
  • Very Unsatisfied 126 (2.48%)
housing_condition_satisfaction_num
VARCHAR
rows 12,583 · non-null 5,070 (40.3%) · distinct 5
len 1..1
  • 5 2,013 (39.7%)
  • 4 1,775 (35.01%)
  • 3 824 (16.25%)
  • 2 332 (6.55%)
  • 1 126 (2.49%)
housing_options_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,370 (10.9%) · distinct 6
len 7..16
  • Unsatisfied 354 (25.84%)
  • Very Unsatisfied 290 (21.17%)
  • Neutral 285 (20.8%)
  • Satisfied 225 (16.42%)
  • Very Satisfied 162 (11.82%)
housing_options_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,316 (10.5%) · distinct 5
len 1..1
  • 2 354 (26.9%)
  • 1 290 (22.04%)
  • 3 285 (21.66%)
  • 4 225 (17.1%)
  • 5 162 (12.31%)
housing_status
VARCHAR
Renter / Owner / Other. Demographic anchor for housing-affordability questions.
rows 12,583 · non-null 6,036 (48.0%) · distinct 4
len 3..20
  • Rent 3,119 (51.67%)
  • Own 2,851 (47.23%)
  • Prefer not to answer 40 (0.66%)
  • Other 26 (0.43%)
id
VARCHAR
Respondent-response ID. Primary key from source; year-prefixed integer (sample: 1100001 = 2011 respondent #00001).
rows 12,583 · non-null 12,583 (100.0%) · distinct 12,583
len 7..7
  • 1100001 1 (0.01%)
  • 1100002 1 (0.01%)
  • 1100003 1 (0.01%)
  • 1100004 1 (0.01%)
  • 1100005 1 (0.01%)
immigrant_yn
VARCHAR
rows 12,583 · non-null 2,039 (16.2%) · distinct 2
len 1..1
  • 0 1,802 (88.38%)
  • 1 237 (11.62%)
inflation_adjustment
VARCHAR
Multiplier to convert this row's income figures to a common reference year (typically the latest wave).
rows 12,583 · non-null 12,583 (100.0%) · distinct 8
len 1..6
  • 1.429 6,167 (49.01%)
  • 1.2593 1,496 (11.89%)
  • 1 1,392 (11.06%)
  • 1.1872 1,298 (10.32%)
  • 1.0572 1,006 (7.99%)
information_vote_label
VARCHAR
rows 12,583 · non-null 1,383 (11.0%) · distinct 6
len 5..17
  • Agree 522 (37.74%)
  • Strongly Agree 469 (33.91%)
  • Neutral 216 (15.62%)
  • Disagree 93 (6.72%)
  • Not Sure 46 (3.33%)
information_vote_num
VARCHAR
rows 12,583 · non-null 1,337 (10.6%) · distinct 5
len 1..1
  • 4 522 (39.04%)
  • 5 469 (35.08%)
  • 3 216 (16.16%)
  • 2 93 (6.96%)
  • 1 37 (2.77%)
language_spoken
VARCHAR
Primary language spoken at home (separate from `survey_language` -- some respondents take an English survey but speak another language at home).
rows 12,583 · non-null 5,459 (43.4%) · distinct 3
len 12..29
  • English only 4,643 (85.05%)
  • English and other language(s) 533 (9.76%)
  • Non-English language(s) only 283 (5.18%)
lgbtqia_yn
VARCHAR
rows 12,583 · non-null 2,039 (16.2%) · distinct 2
len 1..1
  • 0 1,586 (77.78%)
  • 1 453 (22.22%)
life_satisfaction_label
VARCHAR
rows 12,583 · non-null 12,339 (98.1%) · distinct 6
len 7..16
  • Satisfied 5,572 (45.16%)
  • Very Satisfied 4,254 (34.48%)
  • Neutral 1,946 (15.77%)
  • Unsatisfied 400 (3.24%)
  • Very Unsatisfied 160 (1.3%)
life_satisfaction_num
VARCHAR
Life-satisfaction score, 1-5 Likert.
rows 12,583 · non-null 12,332 (98.0%) · distinct 5
len 1..1
  • 4 5,572 (45.18%)
  • 5 4,254 (34.5%)
  • 3 1,946 (15.78%)
  • 2 400 (3.24%)
  • 1 160 (1.3%)
likely_cost_burdened
VARCHAR
Derived 0/1 flag: respondent likely spends >30% of income on housing.
rows 12,583 · non-null 1,835 (14.6%) · distinct 2
len 1..1
  • 0 1,177 (64.14%)
  • 1 658 (35.86%)
likely_low_income
VARCHAR
Derived 0/1 flag: respondent is likely below the local low-income threshold based on income + household size.
rows 12,583 · non-null 1,866 (14.8%) · distinct 2
len 1..1
  • 0 1,520 (81.46%)
  • 1 346 (18.54%)
neighbors_satisfaction_label
VARCHAR
rows 12,583 · non-null 2,378 (18.9%) · distinct 6
len 7..16
  • Satisfied 807 (33.94%)
  • Neutral 762 (32.04%)
  • Very Satisfied 503 (21.15%)
  • Unsatisfied 200 (8.41%)
  • Very Unsatisfied 62 (2.61%)
neighbors_satisfaction_num
VARCHAR
rows 12,583 · non-null 2,334 (18.5%) · distinct 5
len 1..1
  • 4 807 (34.58%)
  • 3 762 (32.65%)
  • 5 503 (21.55%)
  • 2 200 (8.57%)
  • 1 62 (2.66%)
neurodivergent_yn
VARCHAR
rows 12,583 · non-null 1,166 (9.3%) · distinct 2
len 1..1
  • 0 1,044 (89.54%)
  • 1 122 (10.46%)
parks_proximity_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,382 (11.0%) · distinct 6
len 7..16
  • Satisfied 471 (34.08%)
  • Very Satisfied 464 (33.57%)
  • Neutral 227 (16.43%)
  • Unsatisfied 124 (8.97%)
  • Very Unsatisfied 75 (5.43%)
parks_proximity_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,361 (10.8%) · distinct 5
len 1..1
  • 4 471 (34.61%)
  • 5 464 (34.09%)
  • 3 227 (16.68%)
  • 2 124 (9.11%)
  • 1 75 (5.51%)
priced_out_concern_label
VARCHAR
rows 12,583 · non-null 2,372 (18.9%) · distinct 4
len 8..18
  • Very Concerned 978 (41.23%)
  • Somewhat Concerned 700 (29.51%)
  • Not Concerned 649 (27.36%)
  • Not Sure 45 (1.9%)
priced_out_concern_num
VARCHAR
rows 12,583 · non-null 2,327 (18.5%) · distinct 3
len 1..1
  • 3 978 (42.03%)
  • 2 700 (30.08%)
  • 1 649 (27.89%)
public_spaces_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,379 (11.0%) · distinct 6
len 7..16
  • Satisfied 600 (43.51%)
  • Very Satisfied 383 (27.77%)
  • Neutral 264 (19.14%)
  • Unsatisfied 81 (5.87%)
  • Very Unsatisfied 30 (2.18%)
public_spaces_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,358 (10.8%) · distinct 5
len 1..1
  • 4 600 (44.18%)
  • 5 383 (28.2%)
  • 3 264 (19.44%)
  • 2 81 (5.96%)
  • 1 30 (2.21%)
race_ethnicity
VARCHAR
Self-reported race / ethnicity. Open-ended.
rows 12,583 · non-null 11,981 (95.2%) · distinct 7
len 5..25
  • White 9,823 (81.99%)
  • Asian or Pacific Islander 795 (6.64%)
  • Hispanic or Latino 599 (5.0%)
  • Black or African American 325 (2.71%)
  • Two or more races 214 (1.79%)
rats_mice_concern_label
VARCHAR
rows 12,583 · non-null 2,383 (18.9%) · distinct 4
len 8..18
  • Very Concerned 941 (39.49%)
  • Somewhat Concerned 892 (37.43%)
  • Not Concerned 510 (21.4%)
  • Not Sure 40 (1.68%)
rats_mice_concern_num
VARCHAR
Concern about rats / mice, 1-5 Likert. Pairs directly with the rat-complaints data app and 311 rodent requests.
rows 12,583 · non-null 2,343 (18.6%) · distinct 3
len 1..1
  • 3 941 (40.16%)
  • 2 892 (38.07%)
  • 1 510 (21.77%)
relief_heat_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,376 (10.9%) · distinct 6
len 7..16
  • Satisfied 481 (34.96%)
  • Very Satisfied 413 (30.01%)
  • Neutral 269 (19.55%)
  • Unsatisfied 105 (7.63%)
  • Not Sure 67 (4.87%)
relief_heat_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,309 (10.4%) · distinct 5
len 1..1
  • 4 481 (36.75%)
  • 5 413 (31.55%)
  • 3 269 (20.55%)
  • 2 105 (8.02%)
  • 1 41 (3.13%)
rent_mortgage
VARCHAR
rows 12,583 · non-null 2,085 (16.6%) · distinct 11
len 19..26
  • $2,500 to $2,999 per month 381 (18.27%)
  • $2,000 to $2,499 per month 336 (16.12%)
  • $3,000 to $3,499 per month 299 (14.34%)
  • $1,500 to $1,999 per month 185 (8.87%)
  • No rent or mortgage 169 (8.11%)
repurpose_street_parking_label
VARCHAR
rows 12,583 · non-null 1,376 (10.9%) · distinct 6
len 5..17
  • Strongly Agree 401 (29.14%)
  • Strongly Disagree 334 (24.27%)
  • Agree 221 (16.06%)
  • Neutral 208 (15.12%)
  • Disagree 178 (12.94%)
repurpose_street_parking_num
VARCHAR
rows 12,583 · non-null 1,342 (10.7%) · distinct 5
len 1..1
  • 5 401 (29.88%)
  • 1 334 (24.89%)
  • 4 221 (16.47%)
  • 3 208 (15.5%)
  • 2 178 (13.26%)
restaurants_shops_businesses_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,381 (11.0%) · distinct 6
len 7..16
  • Satisfied 573 (41.49%)
  • Very Satisfied 465 (33.67%)
  • Neutral 184 (13.32%)
  • Unsatisfied 109 (7.89%)
  • Very Unsatisfied 42 (3.04%)
restaurants_shops_businesses_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,373 (10.9%) · distinct 5
len 1..1
  • 4 573 (41.73%)
  • 5 465 (33.87%)
  • 3 184 (13.4%)
  • 2 109 (7.94%)
  • 1 42 (3.06%)
social_community_events_satisfaction_label
VARCHAR
rows 12,583 · non-null 5,373 (42.7%) · distinct 6
len 7..16
  • Very Satisfied 2,026 (37.71%)
  • Satisfied 1,964 (36.55%)
  • Neutral 928 (17.27%)
  • Unsatisfied 200 (3.72%)
  • Very Unsatisfied 147 (2.74%)
social_community_events_satisfaction_num
VARCHAR
rows 12,583 · non-null 5,265 (41.8%) · distinct 5
len 1..1
  • 5 2,026 (38.48%)
  • 4 1,964 (37.3%)
  • 3 928 (17.63%)
  • 2 200 (3.8%)
  • 1 147 (2.79%)
somerville_satisfaction_label
VARCHAR
rows 12,583 · non-null 12,223 (97.1%) · distinct 6
len 7..16
  • Satisfied 5,388 (44.08%)
  • Very Satisfied 4,313 (35.29%)
  • Neutral 1,762 (14.42%)
  • Unsatisfied 461 (3.77%)
  • Very Unsatisfied 292 (2.39%)
somerville_satisfaction_num
VARCHAR
Satisfaction with Somerville as a place to live, 1-5 Likert.
rows 12,583 · non-null 12,216 (97.1%) · distinct 5
len 1..1
  • 4 5,388 (44.11%)
  • 5 4,313 (35.31%)
  • 3 1,762 (14.42%)
  • 2 461 (3.77%)
  • 1 292 (2.39%)
streets_layout_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,385 (11.0%) · distinct 6
len 7..16
  • Satisfied 448 (32.35%)
  • Neutral 378 (27.29%)
  • Unsatisfied 262 (18.92%)
  • Very Unsatisfied 161 (11.62%)
  • Very Satisfied 133 (9.6%)
streets_layout_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,382 (11.0%) · distinct 5
len 1..1
  • 4 448 (32.42%)
  • 3 378 (27.35%)
  • 2 262 (18.96%)
  • 1 161 (11.65%)
  • 5 133 (9.62%)
streets_maintenance_satisfaction_label
VARCHAR
rows 12,583 · non-null 6,313 (50.2%) · distinct 6
len 7..16
  • Satisfied 1,759 (27.86%)
  • Neutral 1,609 (25.49%)
  • Unsatisfied 1,233 (19.53%)
  • Very Unsatisfied 945 (14.97%)
  • Very Satisfied 753 (11.93%)
streets_maintenance_satisfaction_num
VARCHAR
rows 12,583 · non-null 6,299 (50.1%) · distinct 5
len 1..1
  • 4 1,759 (27.93%)
  • 3 1,609 (25.54%)
  • 2 1,233 (19.57%)
  • 1 945 (15.0%)
  • 5 753 (11.95%)
student_yn
VARCHAR
rows 12,583 · non-null 5,782 (46.0%) · distinct 2
len 1..1
  • 0 5,415 (93.65%)
  • 1 367 (6.35%)
survey_language
VARCHAR
Language the respondent took the survey in (English, Spanish, Portuguese, Haitian Creole observed in recent waves).
rows 12,583 · non-null 6,133 (48.7%) · distinct 2
len 7..16
  • English 5,953 (97.07%)
  • Another Language 180 (2.93%)
survey_method
VARCHAR
Paper, Online, Phone, etc. -- channel through which the response was collected.
rows 12,583 · non-null 12,583 (100.0%) · distinct 2
len 5..6
  • Paper 11,252 (89.42%)
  • Online 1,331 (10.58%)
tenure
VARCHAR
Length of Somerville residency, bucketed.
rows 12,583 · non-null 5,909 (47.0%) · distinct 8
len 12..20
  • 21 years or more 1,475 (24.96%)
  • 1 to 3 years 1,333 (22.56%)
  • 4 to 7 years 1,144 (19.36%)
  • 8 to 10 years 588 (9.95%)
  • 11 to 15 years 561 (9.49%)
tract
VARCHAR
Census tract identifier. Geographic granularity below ward when populated; sparse across waves.
rows 12,583 · non-null 1,385 (11.0%) · distinct 25
len 11..11
  • 25017350400 91 (6.57%)
  • 25017351203 83 (5.99%)
  • 25017350701 73 (5.27%)
  • 25017351001 73 (5.27%)
  • 25017351101 70 (5.05%)
transportation_bicycle_yn
VARCHAR
rows 12,583 · non-null 3,819 (30.4%) · distinct 2
len 1..1
  • 0 2,563 (67.11%)
  • 1 1,256 (32.89%)
transportation_car_yn
VARCHAR
rows 12,583 · non-null 3,819 (30.4%) · distinct 2
len 1..1
  • 1 3,065 (80.26%)
  • 0 754 (19.74%)
transportation_other_yn
VARCHAR
rows 12,583 · non-null 2,355 (18.7%) · distinct 2
len 1..1
  • 0 2,337 (99.24%)
  • 1 18 (0.76%)
transportation_prefer_not_to_answer_yn
VARCHAR
rows 12,583 · non-null 3,829 (30.4%) · distinct 2
len 1..1
  • 0 3,819 (99.74%)
  • 1 10 (0.26%)
transportation_public_transit_yn
VARCHAR
rows 12,583 · non-null 3,819 (30.4%) · distinct 2
len 1..1
  • 1 2,672 (69.97%)
  • 0 1,147 (30.03%)
transportation_rideshare_yn
VARCHAR
rows 12,583 · non-null 2,355 (18.7%) · distinct 2
len 1..1
  • 0 1,607 (68.24%)
  • 1 748 (31.76%)
transportation_walk_yn
VARCHAR
rows 12,583 · non-null 3,819 (30.4%) · distinct 2
len 1..1
  • 1 3,190 (83.53%)
  • 0 629 (16.47%)
veteran_yn
VARCHAR
rows 12,583 · non-null 2,039 (16.2%) · distinct 2
len 1..1
  • 0 1,993 (97.74%)
  • 1 46 (2.26%)
veterans_in_household
VARCHAR
rows 12,583 · non-null 1,349 (10.7%) · distinct 4
len 2..20
  • No 1,271 (94.22%)
  • Yes 48 (3.56%)
  • Prefer not to answer 24 (1.78%)
  • Not Sure 6 (0.44%)
ward
VARCHAR
Ward 1-7 (text type from source). NULL for ~50% of rows (all 2011 + a few elsewhere -- the 2011 wave did not collect ward). Required join key to gold dim_ward when not NULL.
rows 12,583 · non-null 6,358 (50.5%) · distinct 7
len 1..1
  • 6 1,089 (17.13%)
  • 5 1,019 (16.03%)
  • 3 981 (15.43%)
  • 7 946 (14.88%)
  • 2 927 (14.58%)
water_sewer_reliability_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,360 (10.8%) · distinct 6
len 7..16
  • Satisfied 561 (41.25%)
  • Very Satisfied 520 (38.24%)
  • Neutral 154 (11.32%)
  • Unsatisfied 54 (3.97%)
  • Very Unsatisfied 38 (2.79%)
water_sewer_reliability_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,327 (10.5%) · distinct 5
len 1..1
  • 4 561 (42.28%)
  • 5 520 (39.19%)
  • 3 154 (11.61%)
  • 2 54 (4.07%)
  • 1 38 (2.86%)
work_opportunities_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,371 (10.9%) · distinct 6
len 7..16
  • Neutral 574 (41.87%)
  • Satisfied 235 (17.14%)
  • Not Sure 233 (16.99%)
  • Unsatisfied 146 (10.65%)
  • Very Satisfied 127 (9.26%)
work_opportunities_satisfaction_num
VARCHAR
rows 12,583 · non-null 1,138 (9.0%) · distinct 5
len 1..1
  • 3 574 (50.44%)
  • 4 235 (20.65%)
  • 2 146 (12.83%)
  • 5 127 (11.16%)
  • 1 56 (4.92%)
year
VARCHAR
Survey wave year. Biennial: 2011, 2013, 2015, 2017, 2019, 2021, 2023, 2025.
rows 12,583 · non-null 12,583 (100.0%) · distinct 8
len 4..4
  • 2011 6,167 (49.01%)
  • 2019 1,496 (11.89%)
  • 2025 1,392 (11.06%)
  • 2021 1,298 (10.32%)
  • 2023 1,006 (7.99%)
youth_workforce_readiness_satisfaction_label
VARCHAR
rows 12,583 · non-null 1,359 (10.8%) · distinct 6
len 7..16
  • Not Sure 924 (67.99%)
  • Neutral 238 (17.51%)
  • Satisfied 87 (6.4%)
  • Unsatisfied 44 (3.24%)
  • Very Satisfied 42 (3.09%)
youth_workforce_readiness_satisfaction_num
VARCHAR
rows 12,583 · non-null 435 (3.5%) · distinct 5
len 1..1
  • 3 238 (54.71%)
  • 4 87 (20.0%)
  • 2 44 (10.11%)
  • 5 42 (9.66%)
  • 1 24 (5.52%)

main_bronze.raw_somerville_permits

64,521 rows · 15 columns
_dlt_id
VARCHAR
dlt row identifier -- retained at every layer for source-to-pipeline lineage.
rows 64,521 · non-null 64,521 (100.0%) · distinct 64,521
len 14..14
  • ++/W8K4Cm2GDoQ 1 (0.0%)
  • ++2eJTFqVRy+aA 1 (0.0%)
  • ++5I2dc3mfjSeA 1 (0.0%)
  • ++6slTTAJ4npaw 1 (0.0%)
  • ++BEurglXbYhVA 1 (0.0%)
_dlt_load_id
VARCHAR
dlt load identifier -- retained at every layer for source-to-pipeline lineage.
rows 64,521 · non-null 64,521 (100.0%) · distinct 1
len 18..18
  • 1778814252.6616669 64,521 (100.0%)
_extracted_at
TIMESTAMP WITH TIME ZONE
**PIPELINE METADATA.** UTC timestamp of the run that touched the row.
rows 64,521 · non-null 64,521 (100.0%) · distinct 1
dates 2026-05-15 → 2026-05-15
_extracted_run_id
VARCHAR
**PIPELINE METADATA.** ULID of the manual ingestion run.
rows 64,521 · non-null 64,521 (100.0%) · distinct 1
len 26..26
  • 01KRMSEXD1SFAASNZTPB712JW4 64,521 (100.0%)
_source_endpoint
VARCHAR
**PIPELINE METADATA.** Source SODA URL.
rows 64,521 · non-null 64,521 (100.0%) · distinct 1
len 53..53
  • https://data.somervillema.gov/resource/vxgw-vmky.json 64,521 (100.0%)
address
VARCHAR
Property address (public information for permits). Not an applicant identifier.
rows 64,521 · non-null 64,521 (100.0%) · distinct 13,766
len 0..99 · empty strings 1
  • 375 Canal St 526 (0.82%)
  • 70 Inner Belt Rd 236 (0.37%)
  • 84 Washington St 191 (0.3%)
  • 455 Grand Union Blvd 120 (0.19%)
  • 236 Highland Ave 115 (0.18%)
amount
VARCHAR
Permit fee amount in USD. Numeric.
rows 64,521 · non-null 64,521 (100.0%) · distinct 3,394
len 4..10
  • 60.00 10,153 (15.74%)
  • 100.00 3,215 (4.98%)
  • 200.00 2,685 (4.16%)
  • 90.00 1,891 (2.93%)
  • 0.00 1,890 (2.93%)
application_date
TIMESTAMP WITH TIME ZONE
Date the permit application was submitted (calendar_date from source).
rows 64,521 · non-null 64,521 (100.0%) · distinct 3,184
dates 2014-02-13 → 2023-05-15 · span 3,378 days
id
VARCHAR
Permit ID, year-prefixed (e.g. "B14-001277" = Building 2014 #001277). Primary key from source.
rows 64,521 · non-null 64,521 (100.0%) · distinct 64,521
len 10..12
  • B14-000001 1 (0.0%)
  • B14-000002 1 (0.0%)
  • B14-000003 1 (0.0%)
  • B14-000004 1 (0.0%)
  • B14-000008 1 (0.0%)
issue_date
TIMESTAMP WITH TIME ZONE
Date the permit was issued. May be NULL for non-issued applications (Withdrawn, Denied, Under Review).
rows 64,521 · non-null 64,521 (100.0%) · distinct 2,294
dates 2014-05-21 → 2023-10-24 · span 3,443 days
latitude
VARCHAR
Geocoded latitude (WGS84). Precise enough for point-in-polygon spatial join to dim_ward.
rows 64,521 · non-null 64,521 (100.0%) · distinct 10,684
len 1..19
  • 0 2,029 (3.14%)
  • 42.390716552734375 1,407 (2.18%)
  • 42.400691986083984 323 (0.5%)
  • 42.394022900000003 253 (0.39%)
  • 42.38140869140625 252 (0.39%)
longitude
VARCHAR
Geocoded longitude (WGS84).
rows 64,521 · non-null 64,513 (100.0%) · distinct 10,140
len 1..19
  • 0 2,029 (3.15%)
  • -71.101310729980469 1,423 (2.21%)
  • -71.121932983398438 342 (0.53%)
  • -71.096717834472656 255 (0.4%)
  • -71.080474699999968 253 (0.39%)
status
VARCHAR
Permit status. Mostly "Issued" (~97%). Known data quality issue: some rows carry dates as status values (e.g. "08/17/2022"). 21 rows have NULL status. See limitations entry.
rows 64,521 · non-null 64,500 (100.0%) · distinct 18
len 0..25 · empty strings 6
  • Issued 62,378 (96.71%)
  • Closed 1,089 (1.69%)
  • Withdrawn 270 (0.42%)
  • Approved 236 (0.37%)
  • Online Application Receiv 169 (0.26%)
type
VARCHAR
Permit type. 20+ values observed; mostly "Residential Building" (~73%) and "Commercial Building" (~15%). No `accepted_values` test -- long tail acceptable. **11 rows have NULL type** (data quality issue at source); no `not_null` test for this reason -- see limitations entry.
rows 64,521 · non-null 64,510 (100.0%) · distinct 28
len 5..32
  • Residential Building 47,421 (73.51%)
  • Commercial Building 9,863 (15.29%)
  • Construction 5,541 (8.59%)
  • Certificate of Zoning Compliance 542 (0.84%)
  • Day care or Child care 360 (0.56%)
work
VARCHAR
Freeform description of permitted work (e.g. "Remove and replace 1500 sf of roofing"). Occasionally includes street numbers in the text; no applicant names observed.
rows 64,521 · non-null 64,473 (99.9%) · distinct 49,052
len 2..1486
  • Certificate of inspection 1,014 (1.57%)
  • Dumpster on street 546 (0.85%)
  • Water heater 239 (0.37%)
  • Water heater replacement 216 (0.34%)
  • Replace water heater 214 (0.33%)

main_bronze.raw_somerville_traffic_citations

73,038 rows · 20 columns
_dlt_id
VARCHAR
dlt row identifier -- retained at every layer for source-to-pipeline lineage.
rows 73,038 · non-null 73,038 (100.0%) · distinct 73,038
len 14..14
  • +++yxiFPmmCngA 1 (0.0%)
  • ++00t1jqnL1K0Q 1 (0.0%)
  • ++1o8eteY9H9Hw 1 (0.0%)
  • ++2pgrHpi3Qk6g 1 (0.0%)
  • ++414MIBoOxTdg 1 (0.0%)
_dlt_load_id
VARCHAR
dlt load identifier -- retained at every layer for source-to-pipeline lineage.
rows 73,038 · non-null 73,038 (100.0%) · distinct 1
len 18..18
  • 1791022649.8032546 73,038 (100.0%)
_extracted_at
TIMESTAMP WITH TIME ZONE
**PIPELINE METADATA.** UTC timestamp of the run that touched the row.
rows 73,038 · non-null 73,038 (100.0%) · distinct 1
dates 2026-10-03 → 2026-10-03
_extracted_run_id
VARCHAR
**PIPELINE METADATA.** ULID of the pipeline run.
rows 73,038 · non-null 73,038 (100.0%) · distinct 1
len 26..26
  • 01M40KA281T5KQQG9GX4F73XQE 73,038 (100.0%)
_first_seen_at
TIMESTAMP
**PIPELINE METADATA.** UTC timestamp when this row was first ingested. Preserved across re-extractions.
rows 73,038 · non-null 73,038 (100.0%) · distinct 1
dates 2026-10-03 → 2026-10-03
_source_endpoint
VARCHAR
**PIPELINE METADATA.** Source SODA URL.
rows 73,038 · non-null 73,038 (100.0%) · distinct 1
len 53..53
  • https://data.somervillema.gov/resource/3mqx-eye9.json 73,038 (100.0%)
address
VARCHAR
Address of violation. Intersection-level text (e.g. "RTE 28 & RTE 38 Somerville, MA") -- not a street number. Use `lat`/`long` for precise location.
rows 73,038 · non-null 73,038 (100.0%) · distinct 4,386
len 18..55
  • BOSTON AV & BROADWAY Somerville, MA 2,915 (3.99%)
  • 90 WASHINGTON ST Somerville, MA 2,726 (3.73%)
  • 220 WASHINGTON ST Somerville, MA 2,302 (3.15%)
  • MCGRATH HWY & MEDFORD ST Somerville, MA 1,971 (2.7%)
  • BROADWAY & SCHOOL ST Somerville, MA 1,770 (2.42%)
blockcode
VARCHAR
Census block code at the violation location (matches the crime data convention).
rows 73,038 · non-null 73,038 (100.0%) · distinct 622
len 15..15
  • 250173504001003 7,817 (10.7%)
  • 250173515004004 4,072 (5.58%)
  • 250173515001005 3,523 (4.82%)
  • 250173504002000 2,998 (4.1%)
  • 250173515003003 2,912 (3.99%)
chgcategory
VARCHAR
Top-level violation category (109 distinct values; high-cardinality, no accepted_values test).
rows 73,038 · non-null 51,704 (70.8%) · distinct 109
len 19..68
  • No Inspection/Sticker (C90 S20) 15,063 (29.13%)
  • Speeding (C90 S17, C90 S18) 14,382 (27.82%)
  • Use Electronic Device While Operating Mv (C90 S13b) 5,800 (11.22%)
  • Improper Operation Of Motor Vehicle (C90 S16) 2,015 (3.9%)
  • Mv Lights Violation (C90 S7) 1,787 (3.46%)
chgcode
VARCHAR
Massachusetts General Laws violation code (e.g. "90/13B" = MGL Ch 90 Sec 13B = electronic device use while driving). Source value is space-padded.
rows 73,038 · non-null 73,038 (100.0%) · distinct 135
len 15..15
  • 89/9 15,397 (21.08%)
  • 90/20/B 15,063 (20.62%)
  • 90/18/A 11,546 (15.81%)
  • 90/13B 5,800 (7.94%)
  • 90/17/A 2,829 (3.87%)
chgdesc
VARCHAR
Plain-language violation description.
rows 73,038 · non-null 73,038 (100.0%) · distinct 134
len 16..75
  • Fail To Stop/Yield C89 S9 15,397 (21.08%)
  • No Inspection/Sticker C90 S20 15,063 (20.62%)
  • Speeding In Violation Special Regulation C90 S18 11,546 (15.81%)
  • Use Electronic Device While Operating Mv C90 S13b 5,800 (7.94%)
  • Speeding C90 S17 2,829 (3.87%)
citationnum
VARCHAR
Citation number + violation suffix (e.g. "T2725339-1"). Primary key from source; the dlt merge key. Each row is one violation; a citation with N violations has N rows with N distinct citationnum values.
rows 73,038 · non-null 73,038 (100.0%) · distinct 73,038
len 3..17
  • &0401994-1 1 (0.0%)
  • -T3312490-1 1 (0.0%)
  • 000579AE-1 1 (0.0%)
  • 000597AE-1 1 (0.0%)
  • 000620AE-1 1 (0.0%)
dtissued
TIMESTAMP WITH TIME ZONE
Timestamp the citation was issued (text from source; cast to TIMESTAMP in silver).
rows 73,038 · non-null 73,038 (100.0%) · distinct 62,259
dates 2017-01-01 → 2026-09-02 · span 3,531 days
lat
VARCHAR
Geocoded latitude (text from source; cast to DOUBLE in silver). Precise enough for point-in-polygon ward joins independent of the `ward` column.
rows 73,038 · non-null 73,038 (100.0%) · distinct 398
len 4..7
  • 42.3806 3,563 (4.88%)
  • 42.3996 3,000 (4.11%)
  • 42.379 2,395 (3.28%)
  • 42.3773 2,214 (3.03%)
  • 42.3819 1,866 (2.55%)
long
VARCHAR
Geocoded longitude (text from source).
rows 73,038 · non-null 73,038 (100.0%) · distinct 566
len 5..8
  • -71.1169 3,458 (4.73%)
  • -71.0849 2,984 (4.09%)
  • -71.1114 2,975 (4.07%)
  • -71.1168 2,796 (3.83%)
  • -71.0924 2,333 (3.19%)
mphzone
VARCHAR
Posted speed limit at the violation location, for speeding violations.
rows 73,038 · non-null 13,010 (17.8%) · distinct 25
len 1..3
  • 20 8,668 (66.63%)
  • 30 2,397 (18.42%)
  • 25 1,642 (12.62%)
  • 35 266 (2.04%)
  • 17 4 (0.03%)
police_shift
VARCHAR
Shift during which the citation was issued (e.g. "Day Shift (8AM - 4PM)").
rows 73,038 · non-null 73,038 (100.0%) · distinct 3
len 21..27
  • Day Shift (8AM - 4PM) 48,405 (66.27%)
  • First Half (4PM - Midnight) 15,570 (21.32%)
  • Last Half (Midnight - 8AM) 9,063 (12.41%)
vehiclemph
VARCHAR
Speed of vehicle in mph for speeding violations; NULL for non-speeding (most warnings).
rows 73,038 · non-null 13,016 (17.8%) · distinct 65
len 1..5
  • 32 1,831 (14.07%)
  • 33 1,413 (10.86%)
  • 34 1,128 (8.67%)
  • 35 1,103 (8.47%)
  • 31 1,090 (8.37%)
ward
VARCHAR
Ward 1-7 from source. NULL for ~0.12% of rows (84 of 67,311).
rows 73,038 · non-null 72,952 (99.9%) · distinct 7
len 1..1
  • 1 18,531 (25.4%)
  • 6 17,702 (24.27%)
  • 2 11,776 (16.14%)
  • 4 8,289 (11.36%)
  • 5 6,825 (9.36%)
warning
VARCHAR
Y if the citation was issued as a written warning (no fine); N if it carries a monetary fine. ~76% warnings, ~24% fines.
rows 73,038 · non-null 73,038 (100.0%) · distinct 2
len 1..1
  • Y 56,407 (77.23%)
  • N 16,631 (22.77%)

main_bronze.raw_somerville_wards

7 rows · 11 columns
_extracted_at
TIMESTAMP
**PIPELINE METADATA.** UTC timestamp when the shapefile was loaded.
rows 7 · non-null 7 (100.0%) · distinct 1
dates 2026-05-13 → 2026-05-13
_source_filename
VARCHAR
**PIPELINE METADATA.** Source shapefile filename inside the Socrata ZIP blob.
rows 7 · non-null 7 (100.0%) · distinct 1
len 9..9
  • Wards.shp 7 (100.0%)
_source_srid
VARCHAR
**PIPELINE METADATA.** Source projection identifier. Static `EPSG:2249` for this dataset.
rows 7 · non-null 7 (100.0%) · distinct 1
len 9..9
  • EPSG:2249 7 (100.0%)
_source_url
VARCHAR
**PIPELINE METADATA.** Canonical Socrata permalink for the source dataset.
rows 7 · non-null 7 (100.0%) · distinct 1
len 41..41
  • https://data.somervillema.gov/d/ym5n-phxd 7 (100.0%)
geometry_wkt_source
VARCHAR
Polygon geometry as WKT in source projection (EPSG:2249). Retained for round-trip back to shapefile + projection-aware spatial joins.
rows 7 · non-null 7 (100.0%) · distinct 7
len 5040..14243
  • POLYGON ((756644.5781758875 2976687.8969471455, 756398.0576715469 2976311.9459273964, 756286.7511356324 2976140.4101812243, 756153.4193813056 2975937.5706918985, 756104.3299125582 2975863.7555508167, 1 (14.29%)
  • POLYGON ((758343.3415106386 2973599.87359564, 758941.973187387 2973325.2038693875, 758976.2201661319 2973397.5144203156, 759492.9254975617 2973146.1812615544, 759651.54624331 2973070.1069386452, 75965 1 (14.29%)
  • POLYGON ((761276.85795331 2970927.19193314, 761352.2163983881 2970893.6398348957, 761371.6763332188 2970884.975482136, 761660.5264773071 2970756.3691120595, 761683.5644889772 2970746.1125708967, 76170 1 (14.29%)
  • POLYGON ((762523.4358407259 2964908.8883529752, 762763.1348363906 2964839.296628639, 762890.6109593809 2965082.961495638, 762958.1229634732 2965206.45173423, 763006.0792323947 2965293.772753805, 76309 1 (14.29%)
  • POLYGON ((763499.2035742253 2966731.1914668977, 763714.7008466423 2966634.1644299775, 763785.1022806317 2966773.341645062, 763874.8632559776 2966931.9236769825, 763899.5052670538 2966977.2283763885, 7 1 (14.29%)
geometry_wkt_wgs84
VARCHAR
Polygon geometry as WKT in WGS84 (EPSG:4326). Lat/lng-friendly; the analyst-facing geometry column.
rows 7 · non-null 7 (100.0%) · distinct 7
len 5140..14516
  • POLYGON ((-71.07427900108763 42.39309899961018, -71.0740860018009 42.39258799976325, -71.0735211583326 42.39182976997553, -71.07349600145173 42.391795999847545, -71.07348103083005 42.391775903157594, 1 (14.29%)
  • POLYGON ((-71.08383300109487 42.39884799989367, -71.08392300148097 42.398614999511686, -71.08396200170195 42.39851399934423, -71.08413100090387 42.39802000028164, -71.0842990010188 42.39753900007536, 1 (14.29%)
  • POLYGON ((-71.1026960018169 42.38817599943017, -71.10190000118217 42.387906999493175, -71.10163700084198 42.38828800068709, -71.10130200044755 42.38872199988386, -71.10121000162377 42.388846000129774, 1 (14.29%)
  • POLYGON ((-71.10633900092013 42.38318799958328, -71.105453001071 42.38299400015622, -71.1049770018959 42.38366099989914, -71.10472500153928 42.38399900003641, -71.10454600158724 42.38423799974551, -71 1 (14.29%)
  • POLYGON ((-71.11085129838102 42.399717951268364, -71.11057287036216 42.39962494055033, -71.1105009715738 42.39960092174777, -71.10943376086496 42.39924440149201, -71.10934864314048 42.399215968043876, 1 (14.29%)
object_id
INTEGER
ESRI internal OBJECTID. Pipeline metadata; not for analysis.
rows 7 · non-null 7 (100.0%) · distinct 7
range 1..7 · mean 4.00 · p50 4, p95 6.7
ogc_fid
INTEGER
OGC feature id assigned by the spatial reader. Pipeline metadata; not for analysis.
rows 7 · non-null 7 (100.0%) · distinct 7
range 0..6 · mean 3.00 · p50 3, p95 5.7 · zeros 1
shape_area_sqftus
DOUBLE
Polygon area in square US survey feet (source projection unit). Gold layer converts to sq km.
rows 7 · non-null 7 (100.0%) · distinct 7
range 1.36808e+07..2.88832e+07 · mean 16820058.46 · p50 1.45066e+07, p95 2.5619e+07
shape_length_ftus
DOUBLE
Polygon perimeter length in US survey feet (source projection unit). Gold layer converts to meters.
rows 7 · non-null 7 (100.0%) · distinct 7
range 20326.8..28680.7 · mean 23077.47 · p50 21213.6, p95 28338.2
ward_number
INTEGER
Ward number (1-7). Joins to `fct_311_requests.ward` (which stores ward as VARCHAR -- cast at the join).
rows 7 · non-null 7 (100.0%) · distinct 7
range 1..7 · mean 4.00 · p50 4, p95 6.7

main_gold.dim_date

4,140 rows · 11 columns
date_dt
DATE
Calendar date. Primary key for this dimension.
rows 4,140 · non-null 4,140 (100.0%) · distinct 4,140
dates 2015-07-01 → 2026-10-30 · span 4,139 days
day
INTEGER
Day of month 1-31.
rows 4,140 · non-null 4,140 (100.0%) · distinct 31
range 1..31 · mean 15.73 · p50 16, p95 29
day_name
VARCHAR
Full English day name (Monday, Tuesday, ...).
rows 4,140 · non-null 4,140 (100.0%) · distinct 7
len 6..9
  • Friday 592 (14.3%)
  • Thursday 592 (14.3%)
  • Wednesday 592 (14.3%)
  • Monday 591 (14.28%)
  • Saturday 591 (14.28%)
day_of_week
INTEGER
Day-of-week index. DuckDB extract(dow): 0=Sunday, 6=Saturday.
rows 4,140 · non-null 4,140 (100.0%) · distinct 7
range 0..6 · mean 3.00 · p50 3, p95 6 · zeros 591
fiscal_year
INTEGER
Fiscal year. Currently equals calendar year -- Somerville fiscal calendar mapping TBD.
rows 4,140 · non-null 4,140 (100.0%) · distinct 12
range 2015..2026 · mean 2020.66 · p50 2021, p95 2026
is_weekend
BOOLEAN
Boolean -- TRUE for Saturday and Sunday.
rows 4,140 · non-null 4,140 (100.0%) · distinct 2
true 28.6%
month
INTEGER
Month number 1-12.
rows 4,140 · non-null 4,140 (100.0%) · distinct 12
range 1..12 · mean 6.58 · p50 7, p95 12
month_name
VARCHAR
Full English month name (January, February, ...).
rows 4,140 · non-null 4,140 (100.0%) · distinct 12
len 3..9
  • August 372 (8.99%)
  • July 372 (8.99%)
  • October 371 (8.96%)
  • September 360 (8.7%)
  • December 341 (8.24%)
quarter
INTEGER
Calendar quarter 1-4.
rows 4,140 · non-null 4,140 (100.0%) · distinct 4
range 1..4 · mean 2.53 · p50 3, p95 4
week_of_year
INTEGER
ISO week-of-year 1-53.
rows 4,140 · non-null 4,140 (100.0%) · distinct 53
range 1..53 · mean 26.85 · p50 27, p95 50
year
INTEGER
Four-digit year (YYYY) extracted from date_dt.
rows 4,140 · non-null 4,140 (100.0%) · distinct 12
range 2015..2026 · mean 2020.66 · p50 2021, p95 2026

main_gold.dim_kpi_topic

25 rows · 7 columns
first_year
SMALLINT
Earliest year observed for this topic.
rows 25 · non-null 25 (100.0%) · distinct 6
range 1850..2024 · mean 2008.84 · p50 2010, p95 2024
geography_count
BIGINT
Number of distinct geographies observed for this topic (typically 1 = Somerville-only, or 2 = Somerville + Massachusetts benchmark).
rows 25 · non-null 25 (100.0%) · distinct 2
range 1..2 · mean 1.84 · p50 2, p95 2
has_massachusetts_benchmark
BOOLEAN
TRUE when the topic includes Massachusetts benchmark rows.
rows 25 · non-null 25 (100.0%) · distinct 2
true 84.0%
has_somerville_data
BOOLEAN
TRUE when the topic includes Somerville rows.
rows 25 · non-null 25 (100.0%) · distinct 1
true 100.0%
latest_year
SMALLINT
Most recent year observed for this topic.
rows 25 · non-null 25 (100.0%) · distinct 2
range 2022..2024 · mean 2023.84 · p50 2024, p95 2024
observation_count
BIGINT
Total observations for this topic across all years and geographies.
rows 25 · non-null 25 (100.0%) · distinct 15
range 6..75 · mean 29.96 · p50 30, p95 59.2
topic
VARCHAR
KPI topic name. Natural key.
rows 25 · non-null 25 (100.0%) · distinct 25
len 7..32
  • Age Group 1 (4.0%)
  • Age of Housing Stock 1 (4.0%)
  • Commute Mode 1 (4.0%)
  • Commute Mode Over Time 1 (4.0%)
  • Commute Time 1 (4.0%)

main_gold.dim_offense_category

4 rows · 2 columns
offense_category
VARCHAR
NIBRS top-level category name. PK.
rows 4 · non-null 4 (100.0%) · distinct 4
len 5..23
  • Crimes against Person 1 (25.0%)
  • Crimes against Property 1 (25.0%)
  • Crimes against Society 1 (25.0%)
  • Other 1 (25.0%)
severity_rank
INTEGER
Editorial ordinal -- 1=Person, 2=Property, 3=Society, 4=Other.
rows 4 · non-null 4 (100.0%) · distinct 4
range 1..4 · mean 2.50 · p50 2.5, p95 3.85

main_gold.dim_offense_code

40 rows · 6 columns
is_active
BOOLEAN
TRUE for all 39 rows today. Placeholder for future code retirement.
rows 40 · non-null 40 (100.0%) · distinct 1
true 100.0%
is_multi_offense_grouping
BOOLEAN
TRUE when offense_code is a multi-code grouping string (2 rows today). FALSE for atomic NIBRS codes (37 rows).
rows 40 · non-null 40 (100.0%) · distinct 2
true 5.0%
offense
VARCHAR
NIBRS offense name.
rows 40 · non-null 40 (100.0%) · distinct 40
len 5..31
  • Aggravated Assault 1 (2.5%)
  • All Other Larceny 1 (2.5%)
  • All Other Offenses 1 (2.5%)
  • Arson 1 (2.5%)
  • Burglary/Breaking And Entering 1 (2.5%)
offense_category
VARCHAR
NIBRS top-level category.
rows 40 · non-null 40 (100.0%) · distinct 4
len 5..23
  • Crimes against Property 21 (52.5%)
  • Other 8 (20.0%)
  • Crimes against Person 7 (17.5%)
  • Crimes against Society 4 (10.0%)
offense_code
VARCHAR
NIBRS offense code or source-side grouping string. PK.
rows 40 · non-null 40 (100.0%) · distinct 40
len 3..19
  • 09A 1 (2.5%)
  • 100 1 (2.5%)
  • 11A - 11D, 36A, 36B 1 (2.5%)
  • 120 1 (2.5%)
  • 13A 1 (2.5%)
offense_type
VARCHAR
NIBRS offense type -- sub-category grouping.
rows 40 · non-null 40 (100.0%) · distinct 29
len 5..30
  • Larceny/Theft Offenses 6 (15.0%)
  • Fraud Offenses 5 (12.5%)
  • Assault Offenses 3 (7.5%)
  • All Other Offenses 1 (2.5%)
  • Arson 1 (2.5%)

main_gold.dim_request_type

352 rows · 5 columns
first_seen_dt
DATE
Earliest date_created across all requests of this type.
rows 352 · non-null 352 (100.0%) · distinct 177
dates 2015-07-01 → 2026-09-22 · span 4,101 days
last_seen_dt
DATE
Most recent date_created across all requests of this type.
rows 352 · non-null 352 (100.0%) · distinct 121
dates 2016-11-22 → 2026-09-30 · span 3,599 days
request_count
BIGINT
Total number of requests of this type, all-time.
rows 352 · non-null 352 (100.0%) · distinct 299
range 1..87257 · mean 3479.26 · p50 567, p95 16369.2
request_type
VARCHAR
Human-readable request type display value (sourced from bronze.type).
rows 352 · non-null 352 (100.0%) · distinct 352
len 3..64
  • 1 A Feedback 1 (0.28%)
  • Recreation (Rec & Youth) 1 (0.28%)
  • 311 Constituent Services feedback 1 (0.28%)
  • 311 connectivity issues 1 (0.28%)
  • Abandoned Property or Illegal Dumping 1 (0.28%)
request_type_id
VARCHAR
Surrogate primary key -- md5 hash of request_type.
rows 352 · non-null 352 (100.0%) · distinct 352
len 32..32
  • 005d2bccdf69d48e975b1577d837acbe 1 (0.28%)
  • 011a874ae0eacbb5d6c892e5ed1eaaf1 1 (0.28%)
  • 034dfb96901067ae4d14e3a58020d90b 1 (0.28%)
  • 0388b77fda588f777c357c808bcac7b6 1 (0.28%)
  • 03cf2040c14fdf4ec3a93f481a3ea1bc 1 (0.28%)

main_gold.dim_status

4 rows · 5 columns
first_seen_dt
DATE
Earliest date_created among requests with this status.
rows 4 · non-null 4 (100.0%) · distinct 4
dates 2015-07-01 → 2018-09-04 · span 1,161 days
is_open
BOOLEAN
Boolean -- FALSE for Closed; TRUE for Open / In Progress / On Hold.
rows 4 · non-null 4 (100.0%) · distinct 2
true 75.0%
last_seen_dt
DATE
Most recent date_created among requests with this status.
rows 4 · non-null 4 (100.0%) · distinct 3
dates 2026-09-24 → 2026-09-30 · span 6 days
status
VARCHAR
Human-readable status display value.
rows 4 · non-null 4 (100.0%) · distinct 4
len 4..11
  • Closed 1 (25.0%)
  • In Progress 1 (25.0%)
  • On Hold 1 (25.0%)
  • Open 1 (25.0%)
status_id
VARCHAR
Surrogate primary key -- md5 hash of status.
rows 4 · non-null 4 (100.0%) · distinct 4
len 32..32
  • 03f4a47830f97377a35321051685071e 1 (25.0%)
  • c3bf447eabe632720a3aa1a7ce401274 1 (25.0%)
  • c9413dd7682a45da9ecc9efa8cea47d7 1 (25.0%)
  • dc62632f03eee6898d4d6fb982f8870a 1 (25.0%)

main_gold.dim_survey_question

8 rows · 9 columns
column_name
VARCHAR
Original source column name on bronze (e.g. `happiness_num`).
rows 8 · non-null 8 (100.0%) · distinct 8
len 13..55
  • beauty_neighborhood_satisfaction_num 1 (12.5%)
  • city_services_information_availability_satisfaction_num 1 (12.5%)
  • education_quality_satisfaction_num 1 (12.5%)
  • happiness_num 1 (12.5%)
  • life_satisfaction_num 1 (12.5%)
first_wave_asked
INTEGER
Earliest survey wave year where this question cleared the >50% non-NULL filter. Per the Phase D matrix.
rows 8 · non-null 8 (100.0%) · distinct 3
range 2011..2015 · mean 2012.25 · p50 2012, p95 2014.3
last_wave_asked
INTEGER
Latest survey wave year where this question cleared the >50% non-NULL filter. Note: `education_quality` last_wave_asked = 2021 because the column was not asked in 2023 and was only partially populated in 2025.
rows 8 · non-null 8 (100.0%) · distinct 2
range 2021..2025 · mean 2024.50 · p50 2025, p95 2025
question_label
VARCHAR
Human-readable label for the question. Prefixed `[paraphrase]` -- the bronze source does not carry per-question wording, so these are short paraphrases from the column-name semantics. A future plan with access to the survey instrument can supply verbatim wording.
rows 8 · non-null 8 (100.0%) · distinct 8
len 38..78
  • [paraphrase] Life satisfaction overall 1 (12.5%)
  • [paraphrase] Overall happiness right now 1 (12.5%)
  • [paraphrase] Satisfaction with Somerville as a place to live 1 (12.5%)
  • [paraphrase] Satisfaction with availability of information about city services 1 (12.5%)
  • [paraphrase] Satisfaction with education quality 1 (12.5%)
scale_max
INTEGER
Upper bound of the Likert scale (always 5 for the 8 surviving columns).
rows 8 · non-null 8 (100.0%) · distinct 1
range 5..5 · mean 5.00 · p50 5, p95 5
scale_min
INTEGER
Lower bound of the Likert scale (always 1 for the 8 surviving columns).
rows 8 · non-null 8 (100.0%) · distinct 1
range 1..1 · mean 1.00 · p50 1, p95 1
survey_question_id
VARCHAR
Slug form of the question (e.g. `happiness`, `life_satisfaction`). Surrogate primary key.
rows 8 · non-null 8 (100.0%) · distinct 8
len 9..38
  • beauty_neighborhood 1 (12.5%)
  • city_services_information_availability 1 (12.5%)
  • education_quality 1 (12.5%)
  • happiness 1 (12.5%)
  • life_satisfaction 1 (12.5%)
topic
VARCHAR
Thematic grouping of the question. 5 buckets cover the 8 columns.
rows 8 · non-null 8 (100.0%) · distinct 5
len 12..19
  • core_wellbeing 3 (37.5%)
  • community_education 2 (25.0%)
  • city_services 1 (12.5%)
  • civic_information 1 (12.5%)
  • neighborhood 1 (12.5%)
waves_asked_count
INTEGER
Count of waves where this question cleared the threshold. Ranges 6-8 for the surviving columns. Non-contiguous for `social_community_events` (skipped 2011 and 2017) and `education_quality` (last full wave was 2021).
rows 8 · non-null 8 (100.0%) · distinct 3
range 6..8 · mean 7.00 · p50 7, p95 8

main_gold.dim_survey_wave

8 rows · 5 columns
notes
VARCHAR
Wave-specific analytical notes. The 2011 wave row flags the ward coverage gap and the city-only handling in fct_happiness_survey.
rows 8 · non-null 8 (100.0%) · distinct 2
len 46..136
  • Standard biennial wave; ward coverage healthy. 7 (87.5%)
  • Distributed differently from later waves; ward not collected. City-level only -- excluded from ward aggregation in fct_happiness_survey. 1 (12.5%)
respondent_count
INTEGER
Count of distinct respondents in this wave. 2011 has 6,167 (49% of total dataset); other waves range 186-1,496.
rows 8 · non-null 8 (100.0%) · distinct 8
range 186..6167 · mean 1572.88 · p50 1152, p95 4532.15
survey_wave_id
VARCHAR
Wave year as VARCHAR (e.g. `2011`). Surrogate primary key. Joins to fct_happiness_survey.survey_wave_id.
rows 8 · non-null 8 (100.0%) · distinct 8
len 4..4
  • 2011 1 (12.5%)
  • 2013 1 (12.5%)
  • 2015 1 (12.5%)
  • 2017 1 (12.5%)
  • 2019 1 (12.5%)
ward_coverage_pct
DOUBLE
Share of respondents in this wave with a non-NULL ward (0-100). 0.0 for 2011; 96-99.5 for 2013-2025.
rows 8 · non-null 8 (100.0%) · distinct 8
range 0..100 · mean 86.45 · p50 99.05, p95 99.825 · zeros 1
wave_year
INTEGER
Wave year as INTEGER (1850-style sort-friendly form).
rows 8 · non-null 8 (100.0%) · distinct 8
range 2011..2025 · mean 2018.00 · p50 2018, p95 2024.3

main_gold.dim_ward

7 rows · 9 columns
_extracted_at
TIMESTAMP
Lineage -- UTC timestamp when the shapefile was last ingested.
rows 7 · non-null 7 (100.0%) · distinct 1
dates 2026-05-13 → 2026-05-13
_source_url
VARCHAR
Lineage -- canonical Socrata permalink for the source dataset.
rows 7 · non-null 7 (100.0%) · distinct 1
len 41..41
  • https://data.somervillema.gov/d/ym5n-phxd 7 (100.0%)
area_sqkm
DOUBLE
Polygon area in square kilometers. Wards are ~1.3-2.7 sq km.
rows 7 · non-null 7 (100.0%) · distinct 7
range 1.27099..2.68334 · mean 1.56 · p50 1.34771, p95 2.38009
area_sqm
DOUBLE
Polygon area in square meters, converted from the source projection (NAD83 / MA Mainland, US survey feet).
rows 7 · non-null 7 (100.0%) · distinct 7
range 1.27099e+06..2.68334e+06 · mean 1562640.81 · p50 1.34771e+06, p95 2.38009e+06
geometry_wkt_wgs84
VARCHAR
Polygon geometry as WKT in WGS84 (EPSG:4326). Lat/lng-friendly; use ST_GeomFromText to re-parse for spatial operations.
rows 7 · non-null 7 (100.0%) · distinct 7
len 5140..14516
  • POLYGON ((-71.07427900108763 42.39309899961018, -71.0740860018009 42.39258799976325, -71.0735211583326 42.39182976997553, -71.07349600145173 42.391795999847545, -71.07348103083005 42.391775903157594, 1 (14.29%)
  • POLYGON ((-71.08383300109487 42.39884799989367, -71.08392300148097 42.398614999511686, -71.08396200170195 42.39851399934423, -71.08413100090387 42.39802000028164, -71.0842990010188 42.39753900007536, 1 (14.29%)
  • POLYGON ((-71.1026960018169 42.38817599943017, -71.10190000118217 42.387906999493175, -71.10163700084198 42.38828800068709, -71.10130200044755 42.38872199988386, -71.10121000162377 42.388846000129774, 1 (14.29%)
  • POLYGON ((-71.10633900092013 42.38318799958328, -71.105453001071 42.38299400015622, -71.1049770018959 42.38366099989914, -71.10472500153928 42.38399900003641, -71.10454600158724 42.38423799974551, -71 1 (14.29%)
  • POLYGON ((-71.11085129838102 42.399717951268364, -71.11057287036216 42.39962494055033, -71.1105009715738 42.39960092174777, -71.10943376086496 42.39924440149201, -71.10934864314048 42.399215968043876, 1 (14.29%)
perimeter_m
DOUBLE
Polygon perimeter in meters, converted from US survey feet.
rows 7 · non-null 7 (100.0%) · distinct 7
range 6195.63..8741.91 · mean 7034.03 · p50 6465.91, p95 8637.5
ward
VARCHAR
Ward number (1-7) as VARCHAR. Matches `fct_311_requests.ward` for natural-key joins.
rows 7 · non-null 7 (100.0%) · distinct 7
len 1..1
  • 1 1 (14.29%)
  • 2 1 (14.29%)
  • 3 1 (14.29%)
  • 4 1 (14.29%)
  • 5 1 (14.29%)
ward_id
VARCHAR
Surrogate key -- md5(ward). Matches the SK pattern of the other gold dims.
rows 7 · non-null 7 (100.0%) · distinct 7
len 32..32
  • 1679091c5a880faf6fb5e6087eb1b2dc 1 (14.29%)
  • 8f14e45fceea167a5a36dedd4bea2543 1 (14.29%)
  • a87ff679a2f3e71d9181a67b7542122c 1 (14.29%)
  • c4ca4238a0b923820dcc509a6f75849b 1 (14.29%)
  • c81e728d9d4c2f636f067f89cc14862c 1 (14.29%)
ward_name
VARCHAR
Human-readable label, "Ward N". Derived (the source shapefile has no name column -- just numbers).
rows 7 · non-null 7 (100.0%) · distinct 7
len 6..6
  • Ward 1 1 (14.29%)
  • Ward 2 1 (14.29%)
  • Ward 3 1 (14.29%)
  • Ward 4 1 (14.29%)
  • Ward 5 1 (14.29%)

main_gold.fct_311_requests

1,224,700 rows · 26 columns
accuracy
VARCHAR
Post-resolution survey field for accuracy. Sparse -- see docs/limitations/2024-survey-columns-sparse.md.
rows 1,224,700 · non-null 8,273 (0.7%) · distinct 8
len 3..34
  • Very Satisfied 6,352 (76.78%)
  • Very Dissatisfied 837 (10.12%)
  • Somewhat Satisfied 348 (4.21%)
  • Neither Satisfied nor Dissatisfied 229 (2.77%)
  • Somewhat Dissatisfied 200 (2.42%)
block_code
VARCHAR
Block identifier. Carried through from source -- still padded with spaces when unknown; cleanup deferred to Silver. See docs/limitations/block-code-padded.md.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 763
len 15..15
  • NA 464,644 (37.94%)
  • 250173513001000 13,936 (1.14%)
  • 250173512032001 7,270 (0.59%)
  • 250173501081004 6,654 (0.54%)
  • 250173514043005 6,181 (0.5%)
category
VARCHAR
Mid-level grouping within classification.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 22
len 4..33
  • Information - Various Other 463,990 (37.89%)
  • Information - Parking 200,526 (16.37%)
  • Parking 154,249 (12.59%)
  • Trash & Recycling 85,426 (6.98%)
  • Information - DPW 80,596 (6.58%)
classification
VARCHAR
Top of the type hierarchy: Service / Information / Feedback.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 3
len 7..11
  • Information 745,112 (60.84%)
  • Service 468,393 (38.25%)
  • Feedback 11,195 (0.91%)
courtesy
VARCHAR
Post-resolution survey field for courtesy. Sparse -- see docs/limitations/2024-survey-columns-sparse.md.
rows 1,224,700 · non-null 8,252 (0.7%) · distinct 8
len 3..34
  • Very Satisfied 6,900 (83.62%)
  • Very Dissatisfied 358 (4.34%)
  • N/A 311 (3.77%)
  • Somewhat Satisfied 237 (2.87%)
  • Neither Satisfied nor Dissatisfied 230 (2.79%)
date_created_dt
DATE
Date the request was opened. FK to dim_date.date_dt.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 4,110
dates 2015-07-01 → 2026-09-30 · span 4,109 days
date_created_ts
TIMESTAMP
Full timestamp the request was opened (precision retained alongside date_created_dt for time-of-day analysis).
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 884,342
dates 2015-07-01 → 2026-09-30 · span 4,108 days
ease
VARCHAR
Post-resolution survey field for ease. Sparse -- see docs/limitations/2024-survey-columns-sparse.md.
rows 1,224,700 · non-null 6,936 (0.6%) · distinct 8
len 3..34
  • Very Satisfied 5,652 (81.49%)
  • Somewhat Satisfied 578 (8.33%)
  • Very Dissatisfied 266 (3.84%)
  • Neither Satisfied nor Dissatisfied 200 (2.88%)
  • Somewhat Dissatisfied 123 (1.77%)
id
VARCHAR
Somerville 311 request ID -- natural key sourced from bronze.id.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 1,224,700
len 6..7
  • 1000001 1 (0.0%)
  • 1000002 1 (0.0%)
  • 1000003 1 (0.0%)
  • 1000004 1 (0.0%)
  • 1000005 1 (0.0%)
is_city_services_tag
BOOLEAN
Department tag -- navigating city services and policies. Boolean. Sparse.
rows 1,224,700 · non-null 1,224,699 (100.0%) · distinct 2
true 26.0%
is_emergency_readiness_tag
BOOLEAN
Department tag -- emergency readiness and response planning. Boolean. Sparse.
rows 1,224,700 · non-null 1,224,699 (100.0%) · distinct 2
true 0.2%
is_green_space_tag
BOOLEAN
Department tag -- green space care and maintenance. Boolean. Sparse.
rows 1,224,700 · non-null 1,224,699 (100.0%) · distinct 2
true 1.0%
is_infrastructure_tag
BOOLEAN
Department tag -- infrastructure maintenance and repairs. Boolean. Sparse.
rows 1,224,700 · non-null 1,224,699 (100.0%) · distinct 2
true 1.7%
is_noise_tag
BOOLEAN
Department tag -- noise and activity disturbances. Boolean. Sparse.
rows 1,224,700 · non-null 1,224,699 (100.0%) · distinct 2
true 0.3%
is_public_space_tag
BOOLEAN
Department tag -- public space cleanliness and environmental health. Boolean. Sparse.
rows 1,224,700 · non-null 1,224,699 (100.0%) · distinct 2
true 1.9%
is_reliable_service_tag
BOOLEAN
Department tag -- reliable service delivery. Boolean. Sparse.
rows 1,224,700 · non-null 1,224,699 (100.0%) · distinct 2
true 1.0%
is_voting_tag
BOOLEAN
Department tag -- voting and election information. Boolean. Sparse.
rows 1,224,700 · non-null 1,224,699 (100.0%) · distinct 2
true 0.2%
most_recent_status_dt
DATE
Date the most recent status was set.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 4,063
dates 2015-07-01 → 2026-09-30 · span 4,109 days
most_recent_status_ts
TIMESTAMP
Full timestamp the most recent status was set.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 791,274
dates 2015-07-01 → 2026-09-30 · span 4,109 days
origin
VARCHAR
How the request was submitted (Contact Center, Website, etc.). Sourced from bronze.origin_of_request.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 3
len 7..14
  • Contact Center 1,134,383 (92.63%)
  • Website 46,497 (3.8%)
  • 311 Mobile App 43,820 (3.58%)
overallexperience
VARCHAR
Post-resolution survey field for overall experience. Sparse -- see docs/limitations/2024-survey-columns-sparse.md.
rows 1,224,700 · non-null 8,318 (0.7%) · distinct 8
len 3..34
  • Very Satisfied 6,257 (75.22%)
  • Very Dissatisfied 1,050 (12.62%)
  • Somewhat Satisfied 354 (4.26%)
  • Somewhat Dissatisfied 273 (3.28%)
  • Neither Satisfied nor Dissatisfied 209 (2.51%)
request_type
VARCHAR
Leaf-level request type. Sourced from bronze.type.
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 352
len 3..64
  • Welcome desk-information 87,257 (7.12%)
  • Obtain a parking permit inquiry 76,635 (6.26%)
  • Temporary no parking sign posting 46,841 (3.82%)
  • ISD-Buildings inquiry 37,064 (3.03%)
  • Illegal parking 33,829 (2.76%)
request_type_id
VARCHAR
FK to dim_request_type.request_type_id (md5 of request_type).
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 352
len 32..32
  • a4c0cec48fd1ad98885aab3d0e85b85a 87,257 (7.12%)
  • c9f6e35f4bf98ad87d1e8cb726e321f1 76,635 (6.26%)
  • 9389d95e00f9683cbe4a59a385ce591e 46,841 (3.82%)
  • 37995e991599b3414222b45563710445 37,064 (3.03%)
  • ea2e915036e40f18288ffbd0f239f137 33,829 (2.76%)
status
VARCHAR
Most recent status display value (Open / Closed / In Progress / On Hold).
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 4
len 4..11
  • Closed 1,208,891 (98.71%)
  • Open 9,183 (0.75%)
  • In Progress 6,400 (0.52%)
  • On Hold 226 (0.02%)
status_id
VARCHAR
FK to dim_status.status_id (md5 of status).
rows 1,224,700 · non-null 1,224,700 (100.0%) · distinct 4
len 32..32
  • 03f4a47830f97377a35321051685071e 1,208,891 (98.71%)
  • c3bf447eabe632720a3aa1a7ce401274 9,183 (0.75%)
  • c9413dd7682a45da9ecc9efa8cea47d7 6,400 (0.52%)
  • dc62632f03eee6898d4d6fb982f8870a 226 (0.02%)
ward
VARCHAR
Somerville ward 1-7 as VARCHAR. NULL when unknown.
rows 1,224,700 · non-null 759,598 (62.0%) · distinct 7
len 1..1
  • 3 127,972 (16.85%)
  • 1 118,949 (15.66%)
  • 5 115,383 (15.19%)
  • 2 111,656 (14.7%)
  • 6 104,173 (13.71%)

main_gold.fct_citations

73,038 rows · 21 columns
_extracted_at
TIMESTAMP WITH TIME ZONE
Lineage -- UTC timestamp when the most recent pipeline run touched this row.
rows 73,038 · non-null 73,038 (100.0%) · distinct 1
dates 2026-10-03 → 2026-10-03
_extracted_run_id
VARCHAR
Lineage -- ULID of the pipeline run that touched this row.
rows 73,038 · non-null 73,038 (100.0%) · distinct 1
len 26..26
  • 01M40KA281T5KQQG9GX4F73XQE 73,038 (100.0%)
_source_endpoint
VARCHAR
Lineage -- Socrata SODA URL the row came from.
rows 73,038 · non-null 73,038 (100.0%) · distinct 1
len 53..53
  • https://data.somervillema.gov/resource/3mqx-eye9.json 73,038 (100.0%)
address
VARCHAR
Intersection or street address where the citation was issued.
rows 73,038 · non-null 73,038 (100.0%) · distinct 4,386
len 18..55
  • BOSTON AV & BROADWAY Somerville, MA 2,915 (3.99%)
  • 90 WASHINGTON ST Somerville, MA 2,726 (3.73%)
  • 220 WASHINGTON ST Somerville, MA 2,302 (3.15%)
  • MCGRATH HWY & MEDFORD ST Somerville, MA 1,971 (2.7%)
  • BROADWAY & SCHOOL ST Somerville, MA 1,770 (2.42%)
block_code
VARCHAR
15-character census block code (source blockcode).
rows 73,038 · non-null 73,038 (100.0%) · distinct 622
len 15..15
  • 250173504001003 7,817 (10.7%)
  • 250173515004004 4,072 (5.58%)
  • 250173515001005 3,523 (4.82%)
  • 250173504002000 2,998 (4.1%)
  • 250173515003003 2,912 (3.99%)
charge_category
VARCHAR
Broader category for the violation (source chgcategory). 109 distinct values -- not tested for accepted_values.
rows 73,038 · non-null 51,704 (70.8%) · distinct 109
len 19..68
  • No Inspection/Sticker (C90 S20) 15,063 (29.13%)
  • Speeding (C90 S17, C90 S18) 14,382 (27.82%)
  • Use Electronic Device While Operating Mv (C90 S13b) 5,800 (11.22%)
  • Improper Operation Of Motor Vehicle (C90 S16) 2,015 (3.9%)
  • Mv Lights Violation (C90 S7) 1,787 (3.46%)
charge_code
VARCHAR
MGL chapter/section reference for the violation (source chgcode).
rows 73,038 · non-null 73,038 (100.0%) · distinct 135
len 15..15
  • 89/9 15,397 (21.08%)
  • 90/20/B 15,063 (20.62%)
  • 90/18/A 11,546 (15.81%)
  • 90/13B 5,800 (7.94%)
  • 90/17/A 2,829 (3.87%)
charge_description
VARCHAR
Source-side description of the violation (chgdesc).
rows 73,038 · non-null 73,038 (100.0%) · distinct 134
len 16..75
  • Fail To Stop/Yield C89 S9 15,397 (21.08%)
  • No Inspection/Sticker C90 S20 15,063 (20.62%)
  • Speeding In Violation Special Regulation C90 S18 11,546 (15.81%)
  • Use Electronic Device While Operating Mv C90 S13b 5,800 (7.94%)
  • Speeding C90 S17 2,829 (3.87%)
citation_date
DATE
Date of citation issuance (DATE).
rows 73,038 · non-null 73,038 (100.0%) · distinct 3,150
dates 2017-01-01 → 2026-09-02 · span 3,531 days
citation_id
VARCHAR
Surrogate PK -- md5(citation_number).
rows 73,038 · non-null 73,038 (100.0%) · distinct 73,038
len 32..32
  • 00002f62ebd70b1e106850e759746b1e 1 (0.0%)
  • 00010be5215248aeae0fcaf012436cb1 1 (0.0%)
  • 0001d24ac648e7839c1db43858c47577 1 (0.0%)
  • 0002f429e196e3530948d9afaef4f5a9 1 (0.0%)
  • 00036e3e9562ad0a7f07c60947c13a21 1 (0.0%)
citation_number
VARCHAR
Source citation number with numeric suffix. Multi-violation tickets share the root; 67,311 rows = 61,603 distinct roots + 5,708 supplementary-violation rows.
rows 73,038 · non-null 73,038 (100.0%) · distinct 73,038
len 3..17
  • &0401994-1 1 (0.0%)
  • -T3312490-1 1 (0.0%)
  • 000579AE-1 1 (0.0%)
  • 000597AE-1 1 (0.0%)
  • 000620AE-1 1 (0.0%)
citation_ts
TIMESTAMP WITH TIME ZONE
Full timestamp the citation was issued (source dtissued, TIMESTAMP WITH TIME ZONE).
rows 73,038 · non-null 73,038 (100.0%) · distinct 62,259
dates 2017-01-01 → 2026-09-02 · span 3,531 days
citation_year
SMALLINT
Year of citation issuance (SMALLINT).
rows 73,038 · non-null 73,038 (100.0%) · distinct 10
range 2017..2026 · mean 2021.24 · p50 2021, p95 2026
is_warning
BOOLEAN
Boolean -- TRUE when warning_flag = "Y".
rows 73,038 · non-null 73,038 (100.0%) · distinct 2
true 77.2%
latitude
DOUBLE
Geocoded latitude (WGS84). 0% NULL.
rows 73,038 · non-null 73,038 (100.0%) · distinct 398
range 42.3427..42.6217 · mean 42.39 · p50 42.3923, p95 42.4058
longitude
DOUBLE
Geocoded longitude (WGS84). 0% NULL.
rows 73,038 · non-null 73,038 (100.0%) · distinct 566
range -71.3147..-71.0545 · mean -71.10 · p50 -71.0964, p95 -71.0823 · negative 73,038
police_shift
VARCHAR
Police shift during which the citation was issued. Same vocabulary as crime ("Day Shift (8AM - 4PM)", "First Half (4PM - Midnight)", "Last Half (Midnight - 8AM)").
rows 73,038 · non-null 73,038 (100.0%) · distinct 3
len 21..27
  • Day Shift (8AM - 4PM) 48,405 (66.27%)
  • First Half (4PM - Midnight) 15,570 (21.32%)
  • Last Half (Midnight - 8AM) 9,063 (12.41%)
posted_mph_zone
INTEGER
Posted speed limit at the citation location (TRY_CAST from VARCHAR).
rows 73,038 · non-null 13,010 (17.8%) · distinct 25
range 3..250 · mean 22.87 · p50 20, p95 30
vehicle_mph
INTEGER
Vehicle speed in MPH (TRY_CAST from VARCHAR; NULL when uncastable). ~82.5% NULL -- recorded only on speed-related violations. 4 rows have implausible speeds >100 (data-entry errors at source, passed through).
rows 73,038 · non-null 13,016 (17.8%) · distinct 65
range 0..62021 · mean 41.01 · p50 35, p95 46 · zeros 1
ward
VARCHAR
Somerville ward 1-7 (source-published, not spatially derived). 84 rows NULL (~0.12%).
rows 73,038 · non-null 72,952 (99.9%) · distinct 7
len 1..1
  • 1 18,531 (25.4%)
  • 6 17,702 (24.27%)
  • 2 11,776 (16.14%)
  • 4 8,289 (11.36%)
  • 5 6,825 (9.36%)
warning_flag
VARCHAR
Source "Y" / "N" indicator. 51,422 Y / 15,889 N (76% warnings, 24% fines).
rows 73,038 · non-null 73,038 (100.0%) · distinct 2
len 1..1
  • Y 56,407 (77.23%)
  • N 16,631 (22.77%)

main_gold.fct_crime_incidents

23,448 rows · 17 columns
_extracted_at
TIMESTAMP WITH TIME ZONE
Lineage -- UTC timestamp when the most recent pipeline run touched this row.
rows 23,448 · non-null 23,448 (100.0%) · distinct 1
dates 2026-10-03 → 2026-10-03
_extracted_run_id
VARCHAR
Lineage -- ULID of the pipeline run that touched this row.
rows 23,448 · non-null 23,448 (100.0%) · distinct 1
len 26..26
  • 01M40KA281T5KQQG9GX4F73XQE 23,448 (100.0%)
_first_seen_at
TIMESTAMP
Lineage -- UTC timestamp when this incident was first ingested. Preserved across re-extractions.
rows 23,448 · non-null 23,448 (100.0%) · distinct 1
dates 2026-10-03 → 2026-10-03
_source_endpoint
VARCHAR
Lineage -- Socrata SODA URL the row came from.
rows 23,448 · non-null 23,448 (100.0%) · distinct 1
len 53..53
  • https://data.somervillema.gov/resource/aghs-hqvg.json 23,448 (100.0%)
block_code
VARCHAR
15-character census block code. NULL for sensitive incidents (same 2,798 rows as NULL incident_dt). Block-level analysis on the full dataset is therefore similarly bounded.
rows 23,448 · non-null 20,650 (88.1%) · distinct 679
len 15..15
  • 250173501061014 671 (3.25%)
  • 250173515001007 388 (1.88%)
  • 250173501061015 357 (1.73%)
  • 250173515003010 330 (1.6%)
  • 250173501061005 318 (1.54%)
case_number
VARCHAR
Source case number from Somerville Police. Non-sensitive incidents use 8-char `YYxxxxxx` format; sensitive incidents use 9-char `1xxxxxxxx` sentinel format.
rows 23,448 · non-null 23,448 (100.0%) · distinct 23,448
len 8..9
  • 100000001 1 (0.0%)
  • 100000002 1 (0.0%)
  • 100000003 1 (0.0%)
  • 100000004 1 (0.0%)
  • 100000005 1 (0.0%)
incident_dt
DATE
Date the incident was reported. NULL for ~12.5% of rows (sensitive incidents -- day-and-month withheld at source). See docs/limitations/crime-sensitive-incidents-no-month.md.
rows 23,448 · non-null 20,650 (88.1%) · distinct 3,509
dates 2017-01-01 → 2026-09-02 · span 3,531 days
incident_id
VARCHAR
Surrogate PK -- md5(case_number). Matches the gold-dim pattern.
rows 23,448 · non-null 23,448 (100.0%) · distinct 23,448
len 32..32
  • 0000b61eaa75d4b5e6146da0d1f64f25 1 (0.0%)
  • 000d5b8375797759eb36049f3bea0ecc 1 (0.0%)
  • 0011252322b47d267310abd4e603a9fe 1 (0.0%)
  • 0014706cbcbb1a6574c558f0783d43d0 1 (0.0%)
  • 0019b4a8d3533026935d453038b56809 1 (0.0%)
incident_year
SMALLINT
Year the incident was reported. Populated for all rows.
rows 23,448 · non-null 23,448 (100.0%) · distinct 10
range 2017..2026 · mean 2021.56 · p50 2022, p95 2026
incident_year_only
BOOLEAN
TRUE when only the year is known (sensitive incident with day-and-month stripped at source). Filter `WHERE NOT incident_year_only` to exclude these rows from sub-annual time-series.
rows 23,448 · non-null 23,448 (100.0%) · distinct 2
true 11.9%
multi_offense_flag
BOOLEAN
TRUE when offense_code is a multi-code grouping string. 2,875 rows (12.9%); filter `WHERE NOT multi_offense_flag` for single-NIBRS-code analysis.
rows 23,448 · non-null 23,448 (100.0%) · distinct 2
true 13.1%
offense
VARCHAR
NIBRS offense name (denormalized from dim_offense_code for query convenience). E.g. "Motor Vehicle Theft".
rows 23,448 · non-null 23,448 (100.0%) · distinct 40
len 5..31
  • All Other Larceny 4,367 (18.62%)
  • Other Criminal MV Offenses 2,702 (11.52%)
  • Simple Assault 1,812 (7.73%)
  • Destruction/Vandalism Property 1,787 (7.62%)
  • Theft From Motor Vehicle 1,652 (7.05%)
offense_category
VARCHAR
rows 23,448 · non-null 23,448 (100.0%) · distinct 4
len 5..23
  • Crimes against Property 14,861 (63.38%)
  • Other 4,705 (20.07%)
  • Crimes against Person 3,435 (14.65%)
  • Crimes against Society 447 (1.91%)
offense_code
VARCHAR
rows 23,448 · non-null 23,448 (100.0%) · distinct 40
len 3..19
  • 23H 4,367 (18.62%)
  • 991, 998, 999 2,702 (11.52%)
  • 13B 1,812 (7.73%)
  • 290 1,787 (7.62%)
  • 23F 1,652 (7.05%)
offense_type
VARCHAR
NIBRS offense type (denormalized from dim_offense_code). Sub-category grouping similar offenses.
rows 23,448 · non-null 23,448 (100.0%) · distinct 29
len 5..30
  • Larceny/Theft Offenses 6,907 (29.46%)
  • Assault Offenses 3,003 (12.81%)
  • Other Criminal MV Offenses 2,702 (11.52%)
  • Destruction/Vandalism Property 1,787 (7.62%)
  • Fraud Offenses 1,732 (7.39%)
police_shift
VARCHAR
Shift during which the incident was reported. NULL for sensitive incidents.
rows 23,448 · non-null 20,650 (88.1%) · distinct 3
len 21..27
  • Day Shift (8AM - 4PM) 10,415 (50.44%)
  • First Half (4PM - Midnight) 8,359 (40.48%)
  • Last Half (Midnight - 8AM) 1,876 (9.08%)
ward
VARCHAR
Somerville ward (TRIM applied; source pads to length 15). 7 valid ward strings (1-7), NULL for ~13% of rows, "CAM" for 2 cross-jurisdiction rows. See docs/limitations/crime-ward-coverage-gaps.md.
rows 23,448 · non-null 20,532 (87.6%) · distinct 8
len 1..3
  • 1 6,251 (30.45%)
  • 2 3,984 (19.4%)
  • 4 2,700 (13.15%)
  • 6 2,577 (12.55%)
  • 3 1,884 (9.18%)

main_gold.fct_happiness_survey

428 rows · 14 columns
geography_key
VARCHAR
Ward number 1-7 when geography_level=`ward`; NULL when geography_level=`city`. Deliberately NOT relationship-tested against dim_ward -- the city rows would fail. A future plan that splits the fact into per-geography views can wire the relationships test on the ward-only view.
rows 428 · non-null 371 (86.7%) · distinct 7
len 1..1
  • 1 53 (14.29%)
  • 2 53 (14.29%)
  • 3 53 (14.29%)
  • 4 53 (14.29%)
  • 5 53 (14.29%)
geography_level
VARCHAR
One of `city` (city-wide aggregation across all respondents in the wave with a non-NULL score) or `ward` (per-ward aggregation, 2013+).
rows 428 · non-null 428 (100.0%) · distinct 2
len 4..4
  • ward 371 (86.68%)
  • city 57 (13.32%)
mean_score
DOUBLE
Arithmetic mean of the Likert scores at this grain. Unweighted.
rows 428 · non-null 428 (100.0%) · distinct 398
range 2.42361..4.59259 · mean 3.81 · p50 3.92926, p95 4.3299
median_score
DOUBLE
Median Likert score at this grain. Robust to outliers; usually integer-valued for Likert 1-5 distributions.
rows 428 · non-null 428 (100.0%) · distinct 6
range 2..5 · mean 3.83 · p50 4, p95 4.5
respondent_count
INTEGER
Count of respondents at this grain who returned a non-NULL score for this question. Surfaces as the denominator for top/bottom-two-box measures.
rows 428 · non-null 428 (100.0%) · distinct 174
range 9..6039 · mean 267.36 · p50 143.5, p95 1262.95
score_1_count
INTEGER
Count of respondents who scored 1 (lowest Likert value -- "very dissatisfied" / "very unhappy").
rows 428 · non-null 428 (100.0%) · distinct 58
range 0..276 · mean 10.68 · p50 4, p95 41 · zeros 74
score_2_count
INTEGER
Count of respondents who scored 2.
rows 428 · non-null 428 (100.0%) · distinct 69
range 0..632 · mean 18.80 · p50 7, p95 69.55 · zeros 47
score_3_count
INTEGER
Count of respondents who scored 3 (neutral).
rows 428 · non-null 428 (100.0%) · distinct 116
range 0..2050 · mean 57.42 · p50 28, p95 235.45 · zeros 1
score_4_count
INTEGER
Count of respondents who scored 4.
rows 428 · non-null 428 (100.0%) · distinct 153
range 1..2888 · mean 106.52 · p50 53, p95 466.4
score_5_count
INTEGER
Count of respondents who scored 5 (highest -- "very satisfied" / "very happy").
rows 428 · non-null 428 (100.0%) · distinct 137
range 0..2100 · mean 73.93 · p50 35, p95 320.85 · zeros 2
survey_observation_id
VARCHAR
Surrogate PK -- md5(survey_wave_id || '|' || geography_level || '|' || coalesce(geography_key, 'NULL') || '|' || survey_question_id).
rows 428 · non-null 428 (100.0%) · distinct 428
len 32..32
  • 002b5750dcc0472224866fd5f16f5867 1 (0.23%)
  • 005d9342fe2dc073f2e78838e2678a76 1 (0.23%)
  • 0128fc0045aeab0e00926be82a8034f8 1 (0.23%)
  • 01c2eda1a07fc4b6f8b168da5530984c 1 (0.23%)
  • 02732be4a07adcc0186d62abaed89bb4 1 (0.23%)
survey_question_id
VARCHAR
FK to dim_survey_question.survey_question_id.
rows 428 · non-null 428 (100.0%) · distinct 8
len 9..38
  • happiness 57 (13.32%)
  • life_satisfaction 57 (13.32%)
  • somerville_satisfaction 57 (13.32%)
  • beauty_neighborhood 56 (13.08%)
  • streets_maintenance 56 (13.08%)
survey_wave_id
VARCHAR
FK to dim_survey_wave.survey_wave_id (the wave year as text).
rows 428 · non-null 428 (100.0%) · distinct 8
len 4..4
  • 2015 64 (14.95%)
  • 2019 64 (14.95%)
  • 2021 64 (14.95%)
  • 2025 64 (14.95%)
  • 2013 56 (13.08%)
weight_strategy
VARCHAR
**RESERVED, NULL TODAY.** Names the weighting strategy applied to the row when populated (e.g. `raked_age_ward_2020_acs`). Companion to silver.stg_happiness_survey.weight. See docs/limitations/survey-gold-unweighted.md.
rows 428 · non-null 0 (0.0%) · distinct 0

main_gold.fct_permits

64,521 rows · 18 columns
_extracted_at
TIMESTAMP WITH TIME ZONE
Lineage -- UTC timestamp when the most recent pipeline run touched this row.
rows 64,521 · non-null 64,521 (100.0%) · distinct 1
dates 2026-05-15 → 2026-05-15
_extracted_run_id
VARCHAR
Lineage -- ULID of the pipeline run that touched this row.
rows 64,521 · non-null 64,521 (100.0%) · distinct 1
len 26..26
  • 01KRMSEXD1SFAASNZTPB712JW4 64,521 (100.0%)
_source_endpoint
VARCHAR
Lineage -- Socrata SODA URL the row came from.
rows 64,521 · non-null 64,521 (100.0%) · distinct 1
len 53..53
  • https://data.somervillema.gov/resource/vxgw-vmky.json 64,521 (100.0%)
address
VARCHAR
Property address (public information for permits, by design).
rows 64,521 · non-null 64,521 (100.0%) · distinct 13,766
len 0..99 · empty strings 1
  • 375 Canal St 526 (0.82%)
  • 70 Inner Belt Rd 236 (0.37%)
  • 84 Washington St 191 (0.3%)
  • 455 Grand Union Blvd 120 (0.19%)
  • 236 Highland Ave 115 (0.18%)
application_date
DATE
Date the permit application was submitted. Cast from source TIMESTAMP to DATE.
rows 64,521 · non-null 64,521 (100.0%) · distinct 3,184
dates 2014-02-13 → 2023-05-15 · span 3,378 days
application_year
SMALLINT
Year of application_date (SMALLINT).
rows 64,521 · non-null 64,521 (100.0%) · distinct 10
range 2014..2023 · mean 2018.32 · p50 2018, p95 2022
is_issued
BOOLEAN
Boolean -- TRUE when permit_status = "Issued". Filter on this for analyst-clean issued-permit counts (96.68% of rows).
rows 64,521 · non-null 64,500 (100.0%) · distinct 2
true 96.7%
issue_date
DATE
Date the permit was issued. NULL for non-issued applications (Withdrawn, Denied, Under Review).
rows 64,521 · non-null 64,521 (100.0%) · distinct 2,294
dates 2014-05-21 → 2023-10-24 · span 3,443 days
issue_year
SMALLINT
Year of issue_date (SMALLINT). NULL when issue_date is NULL.
rows 64,521 · non-null 64,521 (100.0%) · distinct 10
range 2014..2023 · mean 2018.39 · p50 2018, p95 2022
latitude
VARCHAR
Geocoded latitude (WGS84). 8 rows are NULL.
rows 64,521 · non-null 64,521 (100.0%) · distinct 10,684
len 1..19
  • 0 2,029 (3.14%)
  • 42.390716552734375 1,407 (2.18%)
  • 42.400691986083984 323 (0.5%)
  • 42.394022900000003 253 (0.39%)
  • 42.38140869140625 252 (0.39%)
longitude
VARCHAR
Geocoded longitude (WGS84). 8 rows are NULL.
rows 64,521 · non-null 64,513 (100.0%) · distinct 10,140
len 1..19
  • 0 2,029 (3.15%)
  • -71.101310729980469 1,423 (2.21%)
  • -71.121932983398438 342 (0.53%)
  • -71.096717834472656 255 (0.4%)
  • -71.080474699999968 253 (0.39%)
permit_amount
VARCHAR
Permit fee amount in USD. Source DOUBLE.
rows 64,521 · non-null 64,521 (100.0%) · distinct 3,394
len 4..10
  • 60.00 10,153 (15.74%)
  • 100.00 3,215 (4.98%)
  • 200.00 2,685 (4.16%)
  • 90.00 1,891 (2.93%)
  • 0.00 1,890 (2.93%)
permit_id
VARCHAR
Surrogate PK -- md5(permit_number). Matches the gold-dim pattern.
rows 64,521 · non-null 64,521 (100.0%) · distinct 64,521
len 32..32
  • 0001199640812bbe598e7c0f0624244c 1 (0.0%)
  • 00015134f2a48bae64d97e8bf2c4662c 1 (0.0%)
  • 0001ecab4cbee82f6d4709f011600537 1 (0.0%)
  • 0001ff96d564fe31e3fbeef5a3bb42bc 1 (0.0%)
  • 0005f79a2bb05eb63baf6f92a59f608b 1 (0.0%)
permit_number
VARCHAR
Year-prefixed natural key from source (e.g. "B14-001277" = Building, 2014, #001277). Preserved for back-reference to the public permit log.
rows 64,521 · non-null 64,521 (100.0%) · distinct 64,521
len 10..12
  • B14-000001 1 (0.0%)
  • B14-000002 1 (0.0%)
  • B14-000003 1 (0.0%)
  • B14-000004 1 (0.0%)
  • B14-000008 1 (0.0%)
permit_status
VARCHAR
Permit status. ~97% "Issued". 21 NULL, 6 empty-string, and 3 rows carry dates as status values (source DQ issue documented in permits-static-since-2023).
rows 64,521 · non-null 64,500 (100.0%) · distinct 18
len 0..25 · empty strings 6
  • Issued 62,378 (96.71%)
  • Closed 1,089 (1.69%)
  • Withdrawn 270 (0.42%)
  • Approved 236 (0.37%)
  • Online Application Receiv 169 (0.26%)
permit_type
VARCHAR
Permit type. ~73% Residential Building, ~15% Commercial Building, 20+ values. **11 rows have NULL type** per the documented source data quality issue (Plan 21 honest-test precedent).
rows 64,521 · non-null 64,510 (100.0%) · distinct 28
len 5..32
  • Residential Building 47,421 (73.51%)
  • Commercial Building 9,863 (15.29%)
  • Construction 5,541 (8.59%)
  • Certificate of Zoning Compliance 542 (0.84%)
  • Day care or Child care 360 (0.56%)
ward
VARCHAR
Somerville ward 1-7 derived via spatial join. NULL for ~3.4% of rows (geocoded outside Somerville). See permits-spatial-ward-derivation limitation.
rows 64,521 · non-null 62,337 (96.6%) · distinct 7
len 1..1
  • 5 10,770 (17.28%)
  • 6 10,437 (16.74%)
  • 3 9,417 (15.11%)
  • 1 8,985 (14.41%)
  • 2 8,461 (13.57%)
work_description
VARCHAR
Freeform description of permitted work. Source-side text; no applicant names observed.
rows 64,521 · non-null 64,473 (99.9%) · distinct 49,052
len 2..1486
  • Certificate of inspection 1,014 (1.57%)
  • Dumpster on street 546 (0.85%)
  • Water heater 239 (0.37%)
  • Water heater replacement 216 (0.34%)
  • Replace water heater 214 (0.33%)

main_gold.fct_somerville_kpi

749 rows · 10 columns
_extracted_at
TIMESTAMP WITH TIME ZONE
Lineage -- UTC timestamp when the most recent pipeline run touched this row.
rows 749 · non-null 749 (100.0%) · distinct 1
dates 2026-05-15 → 2026-05-15
_extracted_run_id
VARCHAR
Lineage -- ULID of the pipeline run that touched this row.
rows 749 · non-null 749 (100.0%) · distinct 1
len 26..26
  • 01KRMT9SFH4ZZ7Q2NDK1G7GY1X 749 (100.0%)
_source_endpoint
VARCHAR
Lineage -- Socrata SODA URL the row came from.
rows 749 · non-null 749 (100.0%) · distinct 1
len 53..53
  • https://data.somervillema.gov/resource/jnde-mi6j.json 749 (100.0%)
geography
VARCHAR
Geographic scope (typically 'Somerville').
rows 749 · non-null 749 (100.0%) · distinct 2
len 10..13
  • Somerville 459 (61.28%)
  • Massachusetts 290 (38.72%)
kpi_description
VARCHAR
Source-side editorial description of the KPI (source `description`).
rows 749 · non-null 749 (100.0%) · distinct 81
len 6..57
  • Total Population 31 (4.14%)
  • Bachelor's degree or higher 30 (4.01%)
  • Car, truck, or van 30 (4.01%)
  • Foreign born 30 (4.01%)
  • Mean Travel Time to Work 30 (4.01%)
kpi_id
VARCHAR
Surrogate PK -- md5(topic + '|' + year + '|' + COALESCE(description, '') + '|' + COALESCE(geography, '')). The natural primary key is (topic, year, description, geography) because (a) categorical topics have multiple rows per (topic, year) differentiated by description, AND (b) the source includes both Somerville and Massachusetts benchmark rows at the same (topic, year, description) — geography distinguishes them.
rows 749 · non-null 749 (100.0%) · distinct 749
len 32..32
  • 0018549ded36f31be74e9ff4f247765c 1 (0.13%)
  • 002466b2324f129bfdfe8b76529dbf3e 1 (0.13%)
  • 0058d8a74587aa2a47232df6ba5c2c89 1 (0.13%)
  • 009a96d121ec14aa45b2dbdd51a75189 1 (0.13%)
  • 00f44766159b6f146e8becaf816f4a47 1 (0.13%)
topic
VARCHAR
rows 749 · non-null 749 (100.0%) · distinct 25
len 7..32
  • Language Use Overtime 75 (10.01%)
  • Owners vs Renters 60 (8.01%)
  • Commute Mode Over Time 56 (7.48%)
  • Place of Work 48 (6.41%)
  • Household Size Owners vs Renters 45 (6.01%)
units
VARCHAR
Units for the value (USD, percent, count, etc.).
rows 749 · non-null 749 (100.0%) · distinct 5
len 4..7
  • Percent 521 (69.56%)
  • Dollars 102 (13.62%)
  • People 76 (10.15%)
  • Minutes 30 (4.01%)
  • None 20 (2.67%)
value
DOUBLE
Observed value (DOUBLE). TRY_CAST from source VARCHAR.
rows 749 · non-null 749 (100.0%) · distinct 491
range 0.8..911300 · mean 27281.00 · p50 28.7, p95 106979
year
SMALLINT
Year of observation (SMALLINT). Range 1850-2024.
rows 749 · non-null 749 (100.0%) · distinct 31
range 1850..2024 · mean 2016.43 · p50 2019, p95 2024