Data layers

Why separate the layers?

Open a source table and almost nothing is safe to assume. A column that looks like a date may be text. Two rows may be separate events, duplicate submissions, or successive versions of the same event. A provider code names an organisation, but perhaps not the organisation that currently owns the pathway. Even “person” may mean a source-specific identifier in one feed and a project-wide identity in another.

An analyst can settle all of that inside one query. The difficulty is that the next analyst has to settle it again — and can make a different, equally plausible choice. A shared project instead resolves each decision once, tests it, and makes the answer available to everything above it. The layers help us decide which model should own each kind of decision.

This is not a conveyor belt where work “starts” at the bottom and “finishes” at the top. A developer may begin by querying a reporting model, discover that a shared concept is missing, add it in modelling, and never create a published output. What matters is the responsibility and intended consumers of the model being changed — the sequence of the work is free to run in any direction.

Read the stack as a translation. At the bottom, data speaks in the language of a source system: its files, columns, codes and accidents. As it moves upward it speaks more of the organisation's language: pathways, appointments, people, registers and providers. At the top it speaks to a particular consumer, under a particular policy.

Follow one domain up the stack

We will use the waiting-list feed throughout. The eventual request might be “how many people are currently waiting at each provider?”, but the project should model the durable domain behind that first output. It needs concepts that make adjacent questions answerable: a submitted pathway record, a pathway's state at a snapshot, a provider, a person, and elapsed waiting time.

Raw — preserve the source evidence

The contract here: a faithful, readable record of what the source supplied.

The waiting-list feed arrives in source-owned tables. Raw models expose every row and every value without deciding what any of it means. They project the physical columns through readable snake_case names, but do not cast, filter, deduplicate or join.

select
    "PSEUDO NHS NUMBER"   as pseudo_nhs_number,
    "WEEK ENDING DATE"    as week_ending_date,
    "REFERRAL TO TREATMENT PERIOD START DATE"
        as referral_to_treatment_period_start_date
from {{ source('wl', 'WL_OpenPathways_Data') }}

These models are generated by scripts — you never write or edit a raw model by hand. That rule creates a valuable point of comparison. If a row exists in the source, it exists here; if it does not, no project logic has invented it. When a later number looks suspicious, raw answers the first diagnostic question without interpretation: “what evidence did we receive?”

Staging — prepare one source for every downstream use

The contract here: one consistently named and typed source object, ready to be used by downstream models.

Source systems rarely arrive ready to join. Identifiers have source-specific names, dates have awkward types, and resubmission mechanics are encoded in columns that only make sense if you know the feed. Staging applies the preparation that should be universal for that source: project names, useful types, consistent nulls and fixes for known delivery quirks. It should normally preserve the source entity and grain.

select
    -- the project's name and type for this source identifier
    pseudo_nhs_number as sk_patient_id,

    -- the feed describes weekly snapshots; express its date consistently
    case
        when dayofweekiso(week_ending_date) = 7 then week_ending_date
        else dateadd('day', -dayofweek(week_ending_date), week_ending_date)
    end as week_ending_date,

    referral_to_treatment_period_start_date
from {{ ref('raw_wl_wl_openpathways_data') }}

-- only when the feed contract says a later submission replaces the
-- same technical source record for every possible downstream use
qualify der_submission_id = max(der_submission_id)
    over (partition by <complete source record key>)

The submission identifier and week-ending date describe how this feed is delivered. Every use of the feed needs the same answer about which resubmission supersedes which, and which reporting week a supplied date represents, so staging settles those mechanics once.

Staging therefore stays close to one source object: normally one staging model per source table and one row for each source entity at its source grain. Two tests should pass. The change should be universal for every reasonable use of the source, and it should not discard a legitimate source record or replace the source entity with a new business concept. Inferring a person, constructing a spell, deciding whether a pathway is currently open and building a clinical register all belong downstream.

Staging may still enforce a declared source invariant. If the feed contract says that an open-pathways table contains only open pathways, removing a row that carries an end date is part of making that source trustworthy for every reader. Every downstream use should inherit that correction, so it passes the universal test. That is different from interpreting a general pathway history and deciding which records the organisation considers open.

SLAM shows how far source preparation can go. Provider contract-monitoring files vary by provider, month and revision, and each submission can restate earlier months. The upstream pipeline reconstructs their changing layouts in DATA_LAKE.SDL. Provider text is parsed and its file and period provenance recorded by models under models/staging/commissioning/slam/. Their current-statement views use the suffix _latest, selecting one statement for each provider, financial year and month. This is more work than most staging models do, but it remains source-conformed: every consumer needs the same interpretation of the file layout, supplied values and cumulative submission history. The base tables retain that history, so current reporting should use those views.

Modelling — build clear domain components

The contract here: a purposeful transformation that prepares data for one or more marts.

Staging has made the waiting-list feed readable, but it has not said what a pathway is. It has not decided whether successive submissions describe one continuing pathway, which complete snapshot counts as current, which provider relationship matters, or how waiting time is measured at a snapshot. Those are claims about the domain, and they should remain useful whether the next request is a provider count, a long-wait cohort, a patient-level view or a trend over time.

with latest_complete_snapshot as (
    select max(week_ending_date) as snapshot_date
    from {{ ref('stg_wl_openpathways_data') }}
)

select
    sk_patient_id,
    patient_pathway_identifier as pathway_id,
    week_ending_date as snapshot_date,
    provider_code,
    treatment_function_code,

    -- one shared definition of elapsed waiting time at this snapshot
    datediff('day',
        referral_to_treatment_period_start_date,
        week_ending_date) as days_waiting_at_snapshot
from {{ ref('stg_wl_openpathways_data') }}
inner join latest_complete_snapshot
    on week_ending_date = snapshot_date

The rows now make claims in the organisation's vocabulary: this is the agreed current snapshot; this record identifies a pathway; this is how long it had been waiting then. The input is already universally prepared as an open-pathways source. Modelling adds reusable domain interpretation rather than rechecking that source contract.

What places a model in this layer is its role: a purposeful component, built to be consumed by other dbt models rather than offered as the supported starting point for analysis. The int_ prefix simply records that role. An intermediate model may still perform substantial domain logic, change grain and produce rows that could be counted — and a coherent piece of complex logic can deserve its own model for readability and testing even when only one mart currently uses it. The model-design lesson develops this idea.

Reporting — build the marts analysts work from

The contract here: a supported business entity or concept that people can analyse directly at a documented grain.

This is the project's equivalent of what dbt calls the marts layer. dbt also calls it an entity layer or concept layer: each mart represents a business-defined thing at its unique grain. A mart might be centred on a person, pathway, provider, appointment or clinical register. The first question is not “which chart needs this?” but “what does one row represent?”

A domain can support several useful marts. From the pathway snapshots, the project can select the latest snapshot and build fct_person_wl_current_count_total — one row per person: how many open pathways does this person have? — and fct_provider_wl_current_count_total — one row per provider: how many open pathway rows are recorded here? It can also retain one row per pathway for analysts who need the detail. These are not three versions of the same table. They are three marts with different core concepts and grains, built from the same shared domain meaning.

A mart should normally contain the useful context analysts need about its core concept. A person mart could include current practice, geography and recent activity; a provider mart could include organisation names and pathway measures. This deliberate width is denormalisation: the joins are performed once in the project instead of being reconstructed in every worksheet. The mart may borrow attributes and summaries from many concepts while preserving one unmistakable core grain.

The reporting mart may also complete the definition of its core concept. The diabetes register, for example, is itself a business-ready clinical fact. Modelling is not the only place where meaning is allowed. Its job is to give supporting transformations clear responsibilities, isolate logic that is complex or independently changeable, and make reusable steps available to more than one mart. That separation of concerns keeps the mart SQL readable without pretending the mart contains no business rules of its own.

This layer holds dimensions (dim_), facts (fct_), point-in-time models (pit_), wide analytical tables (obt_) and data-quality outputs (dq_). A wide model such as dim_person_demographics may compose gender, ethnicity, geography and practice into one useful person row without privately redefining those concepts. The model taxonomy lesson explains how those families turn the project into a searchable map.

Published — serve named data products

The contract here: a governed dataset for a named report, dashboard, extract or application.

Reporting marts are reusable analytical assets. When a table or view is created to power the monthly waiting-times dashboard, a statutory return, an application or a named extract, it becomes a published data product. It can select the product's columns, labels, refresh contract and population from the marts below while leaving those reusable marts available for other questions.

A published model does not need a special legal filter to belong here. Its defining feature is that a named consumer depends on it. The layer makes that dependency visible, gives the product a stable interface and gives the team a clear place to manage ownership, grants and Snowflake tags.

Some products also carry use-specific policy. Published models are split by legal basis — direct_care/ and secondary_use/ — and secondary-use models built on GP-record data apply the national opt-out via an inner join to dim_person_secondary_use_allowed. That filter belongs here because it governs this use of that data — commissioning datasets do not need it — and not because the opted-out person or pathway disappears from the shared domain.

Not every population rule belongs here. “Open pathway” is shared domain meaning and belongs upstream. A reusable clinical cohort may also be a shared fact. Published is for the part that becomes true only because of the consumer: legal basis, disclosure control, contractual scope, product ownership or audience-specific naming.

The SQL operation does not choose the layer

The same SQL operation can settle completely different kinds of ambiguity — which is why rules of thumb like “joins go in modelling” or “filters go in published” break down almost immediately. A single join might attach a source lookup, establish a shared clinical relationship, assemble a convenient person row or apply a secondary-use permission rule: four responsibilities, four different homes.

Ask what becomes true after the transformation — and for whom it should be true:

All four transformations might use a where, qualify or join. Their placement differs because they have different responsibilities and intended consumers: source-wide preparation, reusable modelling, direct analysis or one named product.

Meaning should become more specific, not more contradictory

Moving upward may enrich or narrow meaning, but it should not casually overturn a promise already made below. Reporting may present an open pathway at person or provider grain; it should not quietly use different status codes for “open.” A published output may exclude opted-out people; it should not alter how their waiting time was calculated.

source evidence
    → universally cleaned and standardised source records
        → shared domain events, states and relationships
            → business-ready marts at explicit grains
                → governed products for named uses

This is why lineage is useful in both directions. Read upward and ask, “what new claim is being made here?” Read downward and ask, “which assumptions is this model entitled to inherit?” A surprising answer in either direction often reveals a misplaced definition.

The same journey, in folders and databases

Folders make these promises visible and allow project configuration to enforce them. They are the physical expression of the conceptual boundaries, not the reason those boundaries exist:

models/
├── raw/          source evidence preserved; generated, never hand-edited
├── staging/      universal cleaning and standardisation applied per source
├── modelling/    purposeful transformations that prepare data for marts
├── reporting/    supported, business-ready marts at explicit grains
├── published/    named data products served and governed
└── semantic/     agreed metrics exposed to downstream query tools

Snowflake mirrors the same journey at database level. Source data lands in the data-lake databases; each layer above builds into a database named after it:

DATA_LAKE / DATA_LAKE__NCL          source data lands here (shared)
STAGING                             raw (DBT_RAW schema) and staging models
MODELLING                           int_ building blocks
REPORTING                           dim_ / fct_ / obt_ marts
PUBLISHED_REPORTING__DIRECT_CARE    published products,
PUBLISHED_REPORTING__SECONDARY_USE    split by legal basis

Development mirrors each of these with a DEV__ prefix — DEV__STAGING, DEV__MODELLING and so on — with one exception: the data lake has no mirror. Development reads real source data in place and writes only to the DEV__ databases. A useful consequence: the database in a query's FROM clause always tells you which layer, and which environment, you are reading.

When a boundary is genuinely difficult

Some choices really are close. “Current” could mean the latest snapshot received, the latest complete snapshot across all providers, or the state on an explicitly supplied reporting date. The right answer cannot be inferred from the word or from a prefix. Work through the concept:

1. Name the ambiguity. Is this about reading the feed, defining the domain, choosing an analytical grain or serving a use?
2. Name the scope. Should every source reader, every organisational analysis or only this product inherit the decision?
3. Name the grain and time. What does one row represent, and as of when is the claim true?
4. Name the counterfactual. If the dashboard, geography or audience changed, should this definition remain?
5. Name the promise. What may downstream readers safely stop thinking about after this model?

If the answers pull in different directions, the transformation may be doing two jobs and deserve two models. The point of the layers is not to eliminate judgement. It is to give the judgement a shared vocabulary and make its consequences visible.

Why the separation pays

Every well-kept boundary removes a class of questions. Raw preserves an audit trail. Staging prevents source quirks being reinterpreted in every model. Modelling isolates reusable transformations and gives them more surface area for testing. Reporting provides coherent marts and spares analysts repeated joins. Published gives named data products stable, governed interfaces without contaminating shared models.

That is a stronger benefit than knowing which folder accepts a join. The stack turns trust into something structural: a reader can tell what kind of decisions a model is allowed to contain, what it inherits, and what it promises to everything downstream.

Try reading the decision, not the syntax

For each description, ignore whether it happens to use a cast, join, filter or aggregation. Ask which ambiguity it settles and who should inherit the answer.