Survey Aggregation Views

Overview

Pre-aggregated views in Unity Catalog (catalog cs / schema cs_dm). The app only reads them and never writes.

Table Definition

dm_vis_jreslso_tokyo23_comp_test (aggregation view)

The central view used for visualization. Other tables selectable in the UI are assumed to share the same column layout.

Logical Name	Physical Name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
Survey aggregation	dm_vis_jreslso_tokyo23_comp_test	question_number	string				◯		Question number, used for display labels and sorting
		question_type	string				◯		Text Select / Free Text; drives UI branching
		question	string				◯		Question text; the grouping key for questions
		choice_text	string				◯		Choice text, or the free-text answer
		sum_answer	bigint				◯		Response count for this choice
		total	bigint				◯		Total responses for the question
		rate	double				◯		Response share (0–1)
		n	bigint				◯		n value exposed by the view
		is_comp	string				◯		Comparison group label; the app only reads 'すべての有効回答'

ut_dm_ana_jreslso_tokyo (respondent attributes)

The respondent attribute table behind the aggregation. A Pydantic model is generated for it, but no API currently reads it.

Logical Name	Physical Name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
Respondent attributes	ut_dm_ana_jreslso_tokyo	user_no	string				◯		Respondent identifier
		性別 (gender)	string				◯
		年齢層 (age group)	string				◯
		婚姻状況 (marital status)	string				◯
		居住地域 (region)	string				◯
		スマホ利用サービス (mobile services used)	string				◯
		家族構成 (household)	string				◯
		職業 (occupation)	string				◯
		企業形態 (company type)	string				◯
		業務端末環境 (work device environment)	string				◯

Relations

• There are no foreign key constraints between the views. ut_dm_ana_jreslso_tokyo relates to the aggregation view only logically, as its source data.

Indexes

• Not documented — these are Unity Catalog views, so the app manages no indexes. Performance depends on SQL Warehouse optimization.

Notes

• No primary key is defined. A row is expected to be unique per (question × choice × comparison group).
• The column definitions above were derived from backend/models/dm_vis_jreslso_tokyo23_comp_test.py and backend/models/ut_dm_ana_jreslso_tokyo.py (auto-generated by backend/generate_models_from_databricks.py). Check Unity Catalog for exact types and null constraints.
• The API row model QuestionDataRow sets extra="allow", so table-specific extra columns pass straight through to the frontend.
• Aggregation queries always filter by is_comp = 'すべての有効回答' (backend/sql/table_data.py).
• To regenerate the models, run python backend/generate_models_from_databricks.py --catalog cs --schema cs_dm --tables <table>. Note that this script overwrites backend/models/__init__.py with only the generated models, so the api / data_sql imports must be restored by hand afterwards.