Semantic views

Why declare meaning?

A reporting table answers questions if you already know how to query it: which column is the grain, which flags are QOF registers, how tables join. A Snowflake semantic view writes that knowledge down as part of the pipeline. Once defined, any consumer — BI tools, or the semantic layer chat interface the team is developing — can compose correct queries from named metrics instead of guessing at joins.

Joins are the hard part

To see why this matters, watch what happens when a tool — or an AI agent — is pointed at the warehouse with no semantic layer. It can read table and column names, so simple single-table queries usually work. Joins are where it breaks down, because the information a correct join needs is not written anywhere it can see:

• Which columns are the keys? Nothing in the schema says person_id is the primary key of dim_person_demographics. An agent guessing from column names might join on sk_patient_id in one place and person_id in another — both look plausible.
• Which direction is one-to-many? Join a one-row-per-person dimension to a many-rows-per-person observation table and count people: every patient is now counted once per observation. The query runs, returns confident numbers, and is wrong — the classic fan-out, and nothing in the warehouse flags it.
• Which tables should join at all? Two tables sharing a column name is not evidence they are meant to be joined, but it is exactly the evidence an agent uses. Worse, tables that do share a key can sit over very different populations — a person_id in a GP registration table and the same column in an acute activity table cover different people, on different inclusion rules. Join them naively and the result is not “the population” but their accidental overlap, with no error to tell you so.

A human analyst avoids these traps with knowledge held in their head. The semantic view moves that knowledge into the warehouse: PRIMARY KEY declarations say what the grain is, RELATIONSHIPS say what references what, and a consumer derives joins from the declarations instead of guessing. The fan-out case stops being possible to write by accident, because the metric's aggregation is defined against the right grain.

What one looks like

Semantic views live in models/semantic/, prefixed sem_, materialised as semantic_view. Instead of a SELECT, the body declares structure (abridged from sem_olids_population):

{{
    config(
        materialized='semantic_view',
        schema='SEMANTIC'
    )
}}

TABLES(
    demographics AS {{ ref('dim_person_demographics') }}
        PRIMARY KEY (person_id)
        COMMENT = 'Core patient demographics: registration, geography, ethnicity',
    conditions AS {{ ref('dim_person_conditions') }}
        PRIMARY KEY (person_id)
        COMMENT = 'Boolean flags for all LTC registers (QOF Business Rules v50)',
    ccms AS {{ ref('dim_person_ccms') }}
        PRIMARY KEY (person_id)
        COMMENT = 'Cambridge Comorbidity Score. Continuous score only — the
                   literature defines no risk bands. Persons aged 16+ only.'
)

RELATIONSHIPS(
    conditions (person_id) REFERENCES demographics,
    ccms (person_id) REFERENCES demographics
)

FACTS(
    demographics.age AS age COMMENT = 'Current age in years',
    conditions.total_conditions AS total_conditions
        COMMENT = 'Total number of active conditions',
    ccms.cambridge_comorbidity_score AS cambridge_comorbidity_score
        WITH SYNONYMS = ('CCMS', 'comorbidity score', 'Cambridge score')
        COMMENT = 'Higher = greater comorbidity burden; can be negative.',
    demographics.esp_weight AS esp_weight
        COMMENT = 'ESP 2013 weight for this person''s age band. Use with
                   age_band_esp for age-standardised rates.'
)

DIMENSIONS(
    demographics.gender AS gender COMMENT = 'Patient gender (Male, Female, Unknown)',
    demographics.age_band_nhs AS age_band_nhs COMMENT = 'NHS Digital standard age bands',
    demographics.borough_registered AS borough_registered
        COMMENT = 'Borough where the registered GP practice is located',
    demographics.registered_pcn_name AS pcn_name
        WITH SYNONYMS = ('PCN', 'primary care network')
        COMMENT = 'PCN name of the registered practice'
)

METRICS(
    demographics.patient_count AS COUNT(DISTINCT demographics.person_id)
        COMMENT = 'Total number of patients',
    demographics.active_patient_count AS COUNT(DISTINCT CASE
        WHEN demographics.is_active THEN demographics.person_id END)
        COMMENT = 'Currently registered patients',
    conditions.diabetes_count AS COUNT(DISTINCT CASE
        WHEN conditions.has_diabetes THEN conditions.person_id END)
        COMMENT = 'Patients with diabetes (all types)'
)

The pieces:

• TABLES — which reporting models participate, with primary keys. Note they are still ref()s: semantic views sit on top of the reporting layer in the same DAG.
• RELATIONSHIPS — how they join, declared once, correctly.
• FACTS— row-level numeric attributes at the table's grain: an age, a condition count, a score. Facts are the raw material metrics aggregate; a consumer can also read them directly.
• DIMENSIONS — categorical attributes to slice by, each with a comment explaining what it means.
• METRICS — named, agreed aggregations over the facts. “Diabetes count” is defined exactly once; every consumer gets the same number.
• SYNONYMS — the other names people use. A question about “PCN” or “comorbidity score” resolves to the right field even though neither is the column name.

Querying one yourself

These are queryable today, with regular SQL — analysts use the same views the tools do. A semantic view changes the rules of the query rather than the language: the joins come from the declarations, metrics arrive pre-defined, and you ask for them with AGG():

SELECT
    borough_registered,
    AGG(active_patient_count) AS active_patients,
    AGG(diabetes_count) AS diabetes_patients
FROM REPORTING.SEMANTIC.SEM_OLIDS_POPULATION
WHERE is_active = TRUE
GROUP BY borough_registered
HAVING AGG(patient_count) > 5;

The quirks worth knowing before your first attempt:

• Metrics are wrapped in AGG() — the view supplies the aggregation; AGG(diabetes_count) asks for it at your chosen grouping. Facts use ordinary functions (AVG(age)), never AGG().
• Every selected dimension must appear in GROUP BY, and a metric cannot appear in WHERE — filter on metrics with HAVING AGG(metric).
• Never alias the view (FROM … AS t fails) or prefix its columns as t.column — use bare column names.
• No joins, subqueries, windows or pivots in the same query block as the view. Isolate each semantic-view query in a CTE; ordinary SQL — joins, aliases, window functions — is fine over the CTE results.

Joining views

Cross-view questions — “people with X who also had Y” — follow one pattern: read each view in its own person-grain CTE, join the CTE results on the linkage key, and aggregate only at the end. OLIDS views link to each other on person_id; non-OLIDS views (SUS activity, cost, resource) are reached through the population view's sk_patient_id bridge.

WITH cohort AS (
    SELECT person_id, borough_registered
    FROM REPORTING.SEMANTIC.SEM_OLIDS_POPULATION
    WHERE is_active = TRUE AND has_diabetes = TRUE
    GROUP BY person_id, borough_registered
), events AS (
    SELECT person_id
    FROM REPORTING.SEMANTIC.SEM_OLIDS_APPOINTMENTS
    WHERE is_dna = TRUE
    GROUP BY person_id
)
SELECT
    c.borough_registered,
    COUNT(DISTINCT c.person_id) AS denominator,
    COUNT(DISTINCT e.person_id) AS numerator
FROM cohort AS c
LEFT JOIN events AS e ON c.person_id = e.person_id
GROUP BY c.borough_registered
HAVING COUNT(DISTINCT c.person_id) > 5;

Three habits from the team's guidance are worth copying: suppress every released aggregate at > 5; count people with COUNT(DISTINCT …) so linkage can never inflate a headcount; and keep person_id and sk_patient_id out of final output — they exist for joining, not releasing. Snowflake's documentation also describes a SEMANTIC_VIEW(...) clause form of query; it works, but the project's guidance uses the plain form above.

What exists today

Fourteen views cover the estate, named sem_ plus their domain:

Their descriptions do working duty, not just documentation: sem_olids_conditions warns that its two grains must not be mixed, and sem_olids_vaccinations states that headcounts must count distinct people. A tool reading the view learns the traps as well as the joins.

Who consumes them

Analysts already can, as above. BI tools are the second consumer: Tableau and Sigma read Snowflake semantic views natively, and Snowsight can export a view as a Tableau data source, so a dashboard can sit on the governed metric definitions rather than rebuilding them. Snowflake's own Cortex Analyst would be the other natural consumer — it answers plain-English questions directly from these declarations — but it is not currently enabled for our account.

The team's semantic-layer chat prototype (not yet generally available) shows the machinery end to end. A plain-English question is first routed to one view: each view carries a short description of the questions it serves — current cohort questions to sem_olids_population, trajectories and recheck intervals to sem_olids_observations_history — and the model chooses a view from those descriptions before writing any SQL. The view, not the table, is the unit a question lands on.

The model then composes SQL from the chosen view's declared dimensions and metrics. It never sees the rows a query returns — it writes the query and describes what the chart will show, and the app executes it. Two checks stand between that draft and the user: every referenced field is verified against the view's real columns before execution, so a misremembered name fails fast rather than silently; and a second model reviews the draft for analytical fitness — right cohort, right denominator, right grain, no causal claims from descriptive data — using the same view definitions as its evidence.

Every stage leans on what the view declares. Routing reads the descriptions, generation reads the dimensions, metrics and comments, and validation reads the grain. A vague comment or an undeclared relationship doesn't just read badly — it degrades every answer built on that view.