One synthetic healthcare dataset. Two deliberately different models.
10 patients · 295 encounters · 2000 observations AI available
01
Load the operational source
The FHIR snapshot is transformed into normalized relational entities with primary keys, foreign keys, constraints, and indexes. Nested arrays become child tables.
[{'id': '84d5768f-3a21-3da9-aad2-bbf7a3ae2700', 'name': 'LOWELL FAMILY MEDICAL CARE PC', 'type_code': 'prov'}, {'id': '047f6113-bd42-353b-8368-cf2352868915', 'name': 'BOSTON PELVIC HEALTH AND WELLNESS PC', 'type_code': 'prov'}, {'id': '3d5fbf38-c781-3e04-8c7e-5a402611b7d4', 'name': 'DAVIS SQUARE FAMILY PRACTICE', 'type_code': 'prov'}]
Grain is the contract that prevents accidental double-counting. AI may select only trusted semantic-catalog identifiers; application code compiles the model.
CREATE TABLE dim_geography(geography_key INTEGER PRIMARY KEY, city TEXT, state TEXT, country TEXT, UNIQUE(city,state,country));
CREATE TABLE dim_patient(patient_key INTEGER PRIMARY KEY, patient_id TEXT NOT NULL UNIQUE, given_name TEXT, family_name TEXT, gender TEXT, birth_date TEXT, marital_status TEXT, geography_key INTEGER REFERENCES dim_geography(geography_key), postal_code TEXT);
CREATE TABLE dim_date(date_key TEXT PRIMARY KEY, full_date TEXT NOT NULL, year INTEGER, quarter INTEGER, month INTEGER, day INTEGER);
CREATE TABLE dim_code_system(code_system_key INTEGER PRIMARY KEY, system_name TEXT UNIQUE); CREATE TABLE dim_clinical_code(clinical_code_key INTEGER PRIMARY KEY, code TEXT, display TEXT, code_system_key INTEGER REFERENCES dim_code_system(code_system_key), UNIQUE(code,display));
CREATE TABLE dim_organization(organization_key INTEGER PRIMARY KEY, organization_id TEXT UNIQUE, name TEXT, type_code TEXT);
CREATE TABLE fact_claim(fact_key INTEGER PRIMARY KEY, event_id TEXT UNIQUE, patient_key INTEGER REFERENCES dim_patient(patient_key), date_key TEXT REFERENCES dim_date(date_key), clinical_code_key INTEGER REFERENCES dim_clinical_code(clinical_code_key), organization_key INTEGER REFERENCES dim_organization(organization_key), event_count INTEGER NOT NULL, amount REAL);
Source extraction and fact-load mapping
SELECT e.id event_id, e.patient_id patient_id, substr(e.created_at,1,10) date_key, e.claim_type code, e.claim_type display, e.organization_id organization_id, e.total_amount amount FROM claim e;
-- Trusted compiler resolves natural IDs to surrogate keys and executes:
INSERT INTO fact_claim(event_id,patient_key,date_key,clinical_code_key,organization_key,event_count,amount) VALUES(?,?,?,?,?,?,?);
03
Ask the same business question twice
The result should agree; the SQL shape reveals why dimensional models are convenient for aggregation.
How does claim volume vary by organization, patient state, and year?
The operational query counts claims directly from the normalized claim table, joining organization and the current patient address to get organization name and patient state, and deriving year from claim start_at. The analytical query uses the fact table and dimension tables, reading year from dim_date and aggregating event_count and amount by organization, patient geography state, and year.
Operational
SELECT
o.name AS organization_name,
pa.state AS patient_state,
CAST(strftime('%Y', c.start_at) AS INTEGER) AS year,
COUNT(*) AS claim_volume,
SUM(c.total_amount) AS total_amount
FROM claim AS c
JOIN organization AS o
ON o.id = c.organization_id
JOIN patient AS p
ON p.id = c.patient_id
JOIN patient_address AS pa
ON pa.patient_id = p.id
AND pa.is_current = 1
WHERE c.start_at IS NOT NULL
GROUP BY
o.name,
pa.state,
CAST(strftime('%Y', c.start_at) AS INTEGER)
ORDER BY
o.name,
pa.state,
year
LIMIT 20
3 joins · 4 tables
organization_name
patient_state
year
claim_volume
total_amount
AMERICAN CHINESE MEDICAL CENTER PC
MA
2014
1
136.8
AMERICAN CHINESE MEDICAL CENTER PC
MA
2016
1
136.8
AMERICAN CHINESE MEDICAL CENTER PC
MA
2017
1
2754.59
AMERICAN CHINESE MEDICAL CENTER PC
MA
2018
1
2072.98
AMERICAN CHINESE MEDICAL CENTER PC
MA
2019
1
2147.56
AMERICAN CHINESE MEDICAL CENTER PC
MA
2022
1
3061.54
AMERICAN CHINESE MEDICAL CENTER PC
MA
2025
1
2997.33
APPLE VALLEY CENTER
MA
2019
3
1102.1200000000001
BAYADA HOME HEALTH CARE, INC
MA
2020
2
3778.23
BAYADA HOME HEALTH CARE, INC
MA
2021
1
146.18
BENJAMIN HEALTHCARE CENTER
MA
2019
2
237.9
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2016
2
841.0
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2017
2
1272.3999999999999
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2018
1
1679.6
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2019
1
778.78
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2020
1
1255.38
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2021
1
704.2
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2022
1
862.48
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2023
1
352.5
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2024
1
1472.08
EXPLAIN QUERY PLAN
13 | 0 | 0 | SCAN pa
17 | 0 | 0 | SEARCH p USING COVERING INDEX sqlite_autoindex_patient_1 (id=?)
21 | 0 | 0 | SEARCH c USING INDEX idx_claim_patient_date (patient_id=?)
32 | 0 | 0 | SEARCH o USING INDEX sqlite_autoindex_organization_1 (id=?)
38 | 0 | 0 | USE TEMP B-TREE FOR GROUP BY
Analytical
SELECT
o.name AS organization_name,
g.state AS patient_state,
d.year AS year,
SUM(f.event_count) AS claim_volume,
SUM(f.amount) AS total_amount
FROM fact_claim AS f
JOIN dim_organization AS o
ON o.organization_key = f.organization_key
JOIN dim_patient AS p
ON p.patient_key = f.patient_key
JOIN dim_geography AS g
ON g.geography_key = p.geography_key
JOIN dim_date AS d
ON d.date_key = f.date_key
GROUP BY
o.name,
g.state,
d.year
ORDER BY
o.name,
g.state,
d.year
LIMIT 20
4 joins · 5 tables
organization_name
patient_state
year
claim_volume
total_amount
AMERICAN CHINESE MEDICAL CENTER PC
MA
2014
1
136.8
AMERICAN CHINESE MEDICAL CENTER PC
MA
2016
1
136.8
AMERICAN CHINESE MEDICAL CENTER PC
MA
2017
1
2754.59
AMERICAN CHINESE MEDICAL CENTER PC
MA
2018
1
2072.98
AMERICAN CHINESE MEDICAL CENTER PC
MA
2019
1
2147.56
AMERICAN CHINESE MEDICAL CENTER PC
MA
2022
1
3061.54
AMERICAN CHINESE MEDICAL CENTER PC
MA
2025
1
2997.33
APPLE VALLEY CENTER
MA
2019
3
1102.1200000000001
BAYADA HOME HEALTH CARE, INC
MA
2020
2
3778.23
BAYADA HOME HEALTH CARE, INC
MA
2021
1
146.18
BENJAMIN HEALTHCARE CENTER
MA
2019
2
237.9
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2016
2
841.0
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2017
2
1272.3999999999999
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2018
1
1679.6
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2019
1
778.78
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2020
1
1255.38
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2021
1
704.2
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2022
1
862.48
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2023
1
352.5
BOSTON PELVIC HEALTH AND WELLNESS PC
MA
2024
1
1472.08
EXPLAIN QUERY PLAN
13 | 0 | 0 | SCAN f
15 | 0 | 0 | SEARCH o USING INTEGER PRIMARY KEY (rowid=?)
18 | 0 | 0 | SEARCH p USING INTEGER PRIMARY KEY (rowid=?)
21 | 0 | 0 | SEARCH g USING INTEGER PRIMARY KEY (rowid=?)
24 | 0 | 0 | SEARCH d USING INDEX sqlite_autoindex_dim_date_1 (date_key=?)
30 | 0 | 0 | USE TEMP B-TREE FOR GROUP BY
04
Read-only SQL lab
Only one parsed SELECT/CTE is accepted. Writes, PRAGMA, ATTACH, unknown tables, file functions, long execution, and more than 200 returned rows are blocked.
Operational result
id
given_name
family_name
gender
birth_date
marital_status
757963e8-7ab5-8fb1-170f-e8a18b9bed22
Carolann504
Bashirian201
female
2013-10-15
Never Married
6f5397fe-48aa-dd60-3ec3-f846d3442026
Amy965
Hagenes547
female
1978-08-30
Married
8e891115-afb9-bdb7-3b22-f6042ea65d8a
Leatrice181
D'Amore443
female
2021-01-06
Never Married
f24a67bb-bd5e-124d-b96d-6f2e394e6c7d
Dallas143
Bailey598
male
2019-08-11
Never Married
cd1eb208-c940-1245-d459-caf6402bc11e
Eloise59
Senger904
female
1924-01-05
Married
0c18b66b-75ce-fdbf-b2a3-45b9068e07f4
Ernesto186
Delgado712
male
2000-01-13
Never Married
0a4c9694-1422-5288-5dee-731548d4f885
Sheena120
Jacobson885
female
2006-10-15
Never Married
f8387f39-1bce-734a-8e6d-2dd0f2928739
Ricardo560
Satterfield305
male
1981-02-27
Married
a91b8d2d-8d23-f94b-9ec9-481ef2e6fe26
Cameron381
Leuschke194
male
1964-10-30
Married
6564218e-d42d-30d0-8710-f63c4ab668ce
Darell496
Mann644
male
2020-09-12
Never Married
∞
Glossary
Grain
Exactly what one fact row represents.
Fact
An event at a declared grain, with foreign keys and measures.
Dimension
Descriptive context used to filter, group, and label facts.
Measure
A numeric value that can be aggregated under defined rules.
Surrogate key
A warehouse-controlled identifier for a dimension row.
ETL / ELT
Extract, transform, and load—the movement from source to analytical structures.
Normalization
Separate entities to reduce redundancy and enforce integrity.
Denormalization
Deliberately repeat or flatten attributes to simplify analytical reads.
Star schema
A central fact joined directly to denormalized dimensions.
Snowflake schema
A dimensional model whose hierarchies are normalized into related tables.