Designing models

Good design contains the impact of change

Model design is about deciding where knowledge belongs — not mainly about making SQL shorter or producing a large number of small models. In a well-designed project, a reader can tell what one row means, a developer can confidently change one definition without inspecting or rewriting unrelated logic, and an analyst receives data in a useful shape.

Design begins once the required outcome is clear and discovery has shown what the project already provides and what is missing. The remaining concepts can then be separated and composed. One readiness test before encoding anything: if nobody can describe a person who should be included and one who should not, the population is not yet ready to become SQL.

Model boundaries must balance separation with usability. Putting everything in one model produces convenient output but hides several responsibilities inside one file. Separating every expression produces a deep DAG of fragments that nobody wants to use. A good boundary isolates a coherent concept, grain or reason to change. A good mart then composes those settled pieces generously for its consumers.

Model the domain, not the first question

Most work starts with a question; for instance, we might be asked, “How many people are currently waiting at each provider?” That is a useful place to start because it gives us a real need, a consumer and an output we can validate. The design task is not merely to produce that count. It is to identify the domain concepts that make this whole class of waiting-list questions answerable.

The count depends on several durable concepts:

• a person and a waiting-list pathway;
• the status of that pathway;
• the date on which the status was observed;
• the provider responsible for the pathway;
• the interval between referral and the observation date.

Several natural follow-up questions are already starting to appear in that list. Who has been waiting the longest? What are they waiting for? Which pathways have breached, and at which provider? Each is a different question, but every one of them is answered by the same people, pathways, statuses, providers and intervals — selected, filtered or aggregated differently.

The models we create for those concepts should therefore outlive the original question. If the next request asks for a monthly trend, a patient-level validation list or a long-wait alert, it should be able to compose the same tested pathway, status, provider and duration models. Only the genuinely new part of the question should require new domain logic.

The reason this works is an asymmetry in rates of change. Questions arrive weekly and are shaped by deadlines, audiences and programme priorities. The domain changes slowly, because people, registrations, pathways, observations and providers are what the organisation is. A model built around a question inherits the question's volatility; a model built around a domain concept inherits the concept's stability. Good design lets the thin product layer absorb the churn while the domain models underneath stay still.

Audience filters, chart-level grains and policy thresholds may be entirely appropriate in a published model whose contract names the product and consumer. The problem is allowing those choices to define a model presented as a shared concept. A filter inherited from the first consumer silently narrows the population; a chart-grain aggregation replaces the domain entity with one row per bar; an unnamed threshold becomes part of the shared meaning. The next consumer must discover and undo those decisions, often by creating a slightly different copy of the model and allowing the definitions to drift.

As a dbt project matures, the domain-first approach becomes the easier one. Large parts of the organisation's data are already represented as tested people, pathways, practices, registers, observations and measures. Many new requests can therefore be answered by selecting, composing and aggregating existing models, followed by a published model for the product. When a missing concept is discovered, the work should leave behind another reusable block rather than logic that exists only inside the new dashboard.

This is where the difference from a worksheet becomes most visible. A worksheet often begins again with source identifiers, awkward types, duplicate records, resubmission rules and coded values. It may solve all of those problems correctly, but the solution is trapped inside one analysis. A consumer of well-designed downstream models should not need to know how the feed encoded a date, how technical duplicates were removed or which source columns had to be reconciled. Those concerns have been absorbed by tested models and should effectively disappear from the analytical task.

The result is compounding velocity. Discovering and analysing one product creates new questions, but each iteration begins with more established meaning than the last. Delivery becomes faster not simply because there is less SQL to write, but because fewer definitions need to be rediscovered, fewer source-data risks need to be handled again and review can concentrate on what has actually changed.

There is a quieter benefit. When models are named for domain concepts, the project's vocabulary converges with the organisation's. A user can recognise fct_person_asthma_register as a claim about the world, discuss its criteria and challenge its definition without reading SQL. The DAG stops being an implementation detail and becomes a map of what the organisation means by its own terms — which is why a request that composes existing models can often be agreed in conversation before any code is written.

Modelling a class of questions does not mean predicting every future use or constructing a complete ontology before delivery. The current request still provides the evidence for what is needed. The aim is to give its durable concepts clear contracts and reusable homes, while keeping presentation, programme and audience choices at their proper scope. A useful test is to imagine that the first dashboard disappeared: the person, pathway and clinical definitions should still describe the organisation's domain and remain available to the next product.

Reusable models need explicit contracts

The compounding benefit of a mature project depends on downstream models being safe to use without reopening their SQL. A recognisable name is not enough. Consumers need to know which records can appear, when each claim is true and what will happen when the model is joined to something else. Those promises form the model's contract.

Population, time and grain define one row

Kimball writes here about fact tables, but the discipline applies throughout this project. Declare a model's grain in plain language before choosing its columns or keys: one row per person, pathway, appointment, clinical observation, or provider and month.

In population health, “one row per person” is rarely a complete promise. The reader also needs to know which people are included and when the claim is true. Together, these three parts form the model's basic contract:

A complete contract can be read as a sentence: this model contains one row for each thing, included when these rules are met, as of this time.

For example, fct_person_diabetes_8_care_processes contains one row per person in the current diabetes-register model, evaluated against the latest available care-process records at build time. It includes completion information for HbA1c, blood pressure, cholesterol, creatinine, urine ACR, foot checks, BMI and smoking status.

The model stays at person grain because it joins to models such as int_hba1c_latest and int_blood_pressure_latest. Those models have already selected one result per person. Joining every observation instead would create several rows for people with several results.

The same failure occurs in any dataset where an entity has repeated child records. Here it is with admissions data:

A join that changes the grain can run successfully and still make every downstream count wrong. Adding distinct may hide the visible duplication without repairing the model's contract. The safe design changes the join, selects the required child record, or aggregates child rows to the target grain before joining.

Population, reference time and grain belong in YAML, with a uniqueness test on the column or column combination that identifies each row. That makes the promise reviewable and gives dbt a way to detect when it stops being true.

Time is part of the model's meaning

Grain is necessary, but it is not sufficient. Two models can contain one row per person and still answer different questions because they make different claims about time. A current-state model describes what is true when the project runs. A historical model records how something changed. A point-in-time model reconstructs what would have been known or true at a specified reference date.

This distinction matters especially in population health. A person's current practice is not necessarily the practice responsible for them at the end of the reporting period. Their age today is not their age when a criterion was assessed. A diagnosis recorded next month must not leak backwards into a register reconstructed for last March.

dim_person_current_practice therefore makes a deliberately current claim. It is useful whenever today's organisational relationship is the required context. The pit_*_register family makes a different promise: register membership is evaluated for a supplied point in time. The prefix is not merely a naming convention; it warns a consumer that dates, eligibility and evidence must all be interpreted relative to the same reference date.

A date column alone does not make a model point-in-time correct. Every time-dependent input must be evaluated at the reference date. Adding a snapshot_date to today's register result would label the rows, but it would not recreate the historical population.

Relationships are part of the contract

A contract must also survive composition. Grain describes one table; cardinality describes what happens when that table meets another. A person can have many observations, medication orders and registrations. A practice has many people. Some relationships are one-to-one only after a rule has selected a current, latest or otherwise preferred record.

A selected ethnicity can be joined safely into a person-grain mart when its model guarantees one result per person. Raw observations cannot: they must first be selected or summarised. A registration history needs a temporal choice as well as a key, because several practices may be correct for the same person at different times. Many-to-many relationships, such as people belonging to several clinically defined populations, may be clearest at their natural relationship grain.

A relationship can be an important domain concept in its own right. Register membership relates a person to a clinically defined population; a registration history relates a person to an organisation during an effective interval. Flattening either relationship into a person model too early can discard dates, create arbitrary choices or make several simultaneously valid relationships look like one attribute.

This is why a visually simple join deserves design attention. If the right-hand model is not unique on the join key, the join changes the left-hand grain. That may be correct when the result is intentionally at relationship grain. It is a defect when the model still claims to be one row per person and isn't.

Important relationships should be documented alongside the inputs, with tests on the uniqueness that makes them safe. A model's row contract depends on those assumptions surviving upstream change.

Define once, then compose for use

Explicit contracts make reusable components safe, but they do not automatically make data convenient. If every analyst has to rediscover the right models and rebuild the same joins, the project has moved complexity without removing it. Good design therefore separates the ownership of a definition from the delivery of useful analytical data.

Definition and delivery are different responsibilities

A model can be wide and convenient without becoming the place where every included concept is defined. The design distinction is:

• Definition: the rule that decides what a concept means and the tests that protect it.
• Delivery: the composition of established concepts into a useful analytical shape.

The central rule is to define concepts independently and compose them generously. A wide model may deliver many concepts while reusing their definitions from upstream models.

obt_person_activity follows the composed design. It provides one useful person-level row covering recent A&E, admitted patient, outpatient and GP activity. Each activity dataset is first summarised in a model that owns that dataset's rules:

select
    person.sk_patient_id,
    ae.attendances_12mo,
    apc.admissions_12mo,
    op.attendances_12mo as outpatient_attendances_12mo,
    gp.appointments_12mo as gp_appointments_12mo
from {{ ref('dim_person_demographics_basic') }} as person
left join {{ ref('fct_person_sus_uec_recent') }} as ae
    using (sk_patient_id)
left join {{ ref('fct_person_sus_apc_recent') }} as apc
    using (sk_patient_id)
left join {{ ref('fct_person_sus_op_recent') }} as op
    using (sk_patient_id)
left join {{ ref('fct_person_gp_recent') }} as gp
    using (sk_patient_id)

The wide model owns the composition and its person grain. It does not privately redefine an A&E attendance, an admission or a GP appointment. Those definitions can be changed and tested independently.

A mart should be organised around a core concept

Reporting models are marts: business-defined entities or concepts at a documented grain. A mart should contain the context analysts commonly need about its core concept.

dim_person_demographics is one current row per person. It includes age, gender, ethnicity, language, practice, geography and deprivation because these attributes are routinely analysed together. Performing those joins once in the project is more useful and consistent than asking every analyst to rebuild them.

The model brings together person identifiers and status, age and age bands, gender, ethnicity, language, practice, wider organisational context, geography, deprivation and analytical weights. Width is useful here because the added columns preserve the core grain and are commonly consumed together. The derivations stay where they are owned: the mart presents ethnicity and practice while their definitions remain in their own reusable models.

Facts, dimensions and the reporting taxonomy

The project's fct_ and dim_ prefixes distinguish models that establish an analytical subject from models that describe one. The terms come from Kimball's dimensional modelling, but population health applies them beyond traditional transaction facts.

A modelling block or a business-ready mart?

int_, fct_ and dim_ do not describe physical shapes. An int_ model is a modelling component intended for other dbt models. A fct_ or dim_ model is a supported reporting mart at a documented population, time and grain.

int_hba1c_latest contains one selected HbA1c result per person, but remains int_ because it is a reusable input to registers and care-process models. By contrast, fct_person_diabetes_register publishes register membership as the subject analysts count, validate and break down.

Start with the traditional pattern

In Kimball's dimensional modelling, a fact is the subject being counted or assessed. A dimension provides reusable context for grouping, filtering and understanding facts.

For GP appointments, the fact contains one row per appointment, with measures such as wait time and duration. Person, practice and date dimensions add context such as ethnicity, neighbourhood and financial year without changing what the fact row represents.

Fact and dimension describe analytical roles. They do not require the delivered mart to keep those roles in separate physical tables.

Population health facts often define a state

Population health extends the pattern to concepts derived from several records and rules, such as register membership, blood-pressure control and vaccination eligibility. These states can be facts because the membership or outcome is itself the subject being counted or assessed.

Use the model's subject, not its columns

Facts and dimensions can share the same grain and column types. What matters is what the row is about. A row in fct_person_diabetes_register asserts register membership; a row in dim_person_ethnicity describes the person. The same distinction separates facts such as blood-pressure control and vaccination eligibility from dimensions such as practice and geography.

Facts and dimensions, delivered as wide tables

Kimball's roles still govern wide marts: each fact has a declared grain, shared dimensions provide consistent context, and the relationships are documented and tested. The difference is physical. Routine joins are performed in dbt rather than repeated by each consumer.

Columnar storage makes repeated descriptive values relatively cheap, while repeated joins consume compute and risk multiplying rows. Performing common joins in dbt keeps those decisions in reviewed code. Wide marts repeat values and take more work to build, so each derivation must still have one reusable home.

dim_person_ethnicity can remain the canonical model that selects a person's ethnicity while several marts include its resulting ethnic_group column. The value is repeated; the rule that selects it is not.

A semantic layer changes where this responsibility sits: it can define joins and metrics centrally over more modular models. This project does not currently rely on one, so its reporting marts include routine context themselves. dbt documents both patterns in its marts guidance.

Useful boundaries make reuse possible

A definition can only be reused confidently when it has a recognisable home and can change without disturbing unrelated concepts. That makes model boundaries important, but it does not mean that every calculation deserves a separate node in the DAG.

A boundary is useful when logic changes independently

“One model, one job” does not mean one CTE or one calculation per model. A model can perform several transformations when they contribute to one coherent responsibility.

The boundary test is whether the transformations describe the same thing and would normally change for the same reason.

The activity datasets in obt_person_activity have different reasons to change. A&E attendance can change when valid-attendance rules or ECDS handling changes. Emergency admissions depend on admission methods and spell construction. Outpatient activity has its own attendance and DNA handling, while GP appointments depend on a different dataset and status vocabulary.

Keeping those definitions in separate models means a change to spell construction does not require rechecking the A&E or GP selection. The final activity model remains wide because that is useful to consumers.

Good design balances separation and width

Too few boundaries create models that are difficult to change. Too many boundaries create deep DAGs and force consumers to rebuild common joins. The right balance depends on whether the logic changes independently and whether consumers repeatedly need the combined result.

Separation is useful when a definition is reused, has its own grain, changes independently or deserves its own tests and owner. Width is useful when consumers repeatedly rebuild the same joins, the added columns preserve the current grain and the attributes are normally consumed together. The two choices are complementary: reusable models settle the inputs, while a wider mart delivers them.

A simple flag derived from columns already in a model probably does not need a new model. Selecting the latest valid blood pressure does: it has its own grain and rules, and it is reused in several clinical products.

SQL length is not the deciding factor. A long mapping can still have one coherent responsibility, while a short expression can mix several independent policies. The boundary follows meaning and reason to change, not line count.

Reuse protects meaning

dim_nhs_health_check_eligibility excludes people with diabetes, coronary heart disease, stroke, CKD, atrial fibrillation, heart failure and familial hypercholesterolaemia. It references the existing register models rather than deriving those conditions again.

As a result, “has diabetes” has the same meaning in health-check eligibility as it does elsewhere. When the register definition changes, downstream models receive the corrected result through the DAG. Reusing SQL saves time; reusing meaning prevents competing definitions.

The same principle reaches below the SQL into terminology. The clinical codes that decide what counts as diabetes or CKD are definitions too, so they belong in managed, versioned codesets — SNOMED clusters resolved into the project's combined codesets — rather than pasted into each model as a literal list. A model references the cluster by name; a clinical review can amend the codes in one place; and every register and measure that uses them inherits the corrected meaning on the next build.

A private copy of a shared definition is a design smell at every one of these levels. A model that carries its own clinical codes and diagnosis rules may be correct today, but it can drift away from the project's tested registers. Searching for the concept and grain before implementing it again is part of design, not just code reuse.

A worked example: designing beyond the first dashboard

Suppose a team needs an asthma dashboard. The dashboard will show people on the asthma register, recent asthma management, prescribing measures, demographic breakdowns and practice context. (The Finding models lesson walks through this same scenario from the consumer's side — discovering these models; this section is about why they are designed the way they are.) It would be possible to write one query that reaches into clinical records, medication orders and registration data and returns exactly those columns. That query might answer the ticket, but it would make the dashboard responsible for every concept it happens to use.

The better design begins by recognising that the dashboard contains several claims with different reasons to change. Register membership is a clinical fact. Medication activity is another clinical subject with its own time windows and measures. Ethnicity and current practice describe the people in those facts. The dashboard is a product that composes those subjects for a particular audience.

Intermediate models make the evidence legible

In the project, int_asthma_diagnoses_all identifies asthma diagnosis and resolution evidence at observation grain. int_asthma_medications_all makes relevant medication orders available at order grain. These models create things that can be counted, but that does not make them facts in the reporting sense. Their job is to prepare coherent, reusable evidence inside the modelling layer. Their names and grains reflect that supporting role.

This separation also makes the eventual register SQL readable. The register does not need to contain all of the mechanics for finding coded observations and medication orders. It can focus on the business rule that defines membership: the relevant age threshold, an active diagnosis and recent medication evidence. A reviewer can see the definition without first unpicking source-specific extraction.

The reporting fact owns the clinical concept

fct_person_asthma_register completes the register definition at one row per included person. It combines the prepared evidence, applies the inclusion criteria and retains dates, codes and criterion flags that explain the result. That is appropriate reporting-layer work. Modelling is not the only layer where meaning can be established; it isolates complex or reusable steps so that the business-ready fact can state its central definition clearly.

The fct_prefix describes the analytical role of that result, not how early its rows became countable. Register membership is the subject being measured. By contrast, a person's ethnicity or current practice supplies context about people already in the register, so those models have a dimensional role.

The published model owns the product composition

The asthma register is not the asthma dashboard. A dashboard may also need SABA prescribing, other management measures, demographics and organisational fields. Adding every requirement to the register would make one reusable clinical fact change whenever one product changes.

Instead, any additional clinical subject should first have a business-ready reporting model at a declared grain. A published model such as asthma_dashboard_base can then compose the register, prescribing or management facts and relevant dimensions into the exact shape the dashboard needs. It owns product-specific filters, audience policy, column names and the final delivery grain. The register remains reusable for other analyses, and the published model remains free to evolve with the dashboard.

The result is a set of reusable, clearly defined building blocks. Another question in the asthma domain can compose the existing register, prescribing measures, management facts and person dimensions in a different way without first recovering their logic from a dashboard query. Work already done to define those concepts remains useful beyond the product that first needed it.

A legitimately different question may need another time window, threshold or population rule. It can build that new concept from the appropriate underlying evidence model while leaving the established register and measures unchanged. The difference is then explicit and reviewable. The DAG shows both the shared foundations and the point at which the new definition diverges.

Stable shared models let products vary

The asthma example leaves two kinds of model behind: stable domain models that can support many questions, and a published composition designed for one product. Treating those as different interfaces allows shared meaning to improve deliberately while products continue to respond to their own users and obligations.

Reporting models are supported interfaces

A reporting mart is not just the final SQL file in a chain. Its name, population, time, grain and column meanings form an interface used by analysts and downstream models. Treating that interface deliberately makes changes safer.

Adding a descriptive column that preserves the existing grain is often an additive change. Changing one row per person to one row per person and month is not: every count and join may behave differently even if the old columns still exist. Changing the definition of register membership is similarly consequential because the rows themselves now make a different clinical claim.

When a genuinely new subject or temporal contract is needed, a new model is usually clearer than quietly changing the old one. When the concept is unchanged and only a new attribute is being delivered, extending the existing mart may be simpler. The decision follows the contract, not a preference for creating or avoiding files.

Published models provide another useful boundary. They can change with a dashboard or extract while stable reporting facts continue to serve several products. Conversely, a correction to a shared clinical definition should be made in its reporting fact and allowed to flow to every product that depends on it. Model design makes the intended blast radius visible before the SQL changes.

Rules should live at the scope that owns them

Not every rule is product-specific. An organisation may agree one way to report a measure, assign a current practice or interpret a clinical definition across all of its work. When that rule is authoritative for the whole project, it can belong in a shared model even though it reflects an organisational decision rather than an objective property of the source data.

Programme logic has a narrower authority. A respiratory programme might define its own priority groups, thresholds and reporting periods. That logic is entirely appropriate in dbt, but it should live in the programme's folders or schemas and build on shared asthma, prescribing and person models. The programme should not alter those shared models as though its rules were the only valid interpretation of the domain.

Audience and product rules are narrower again. Shared person demographics can support both direct-care and secondary-use products, while a secondary-use published view applies the relevant opt-out filtering. That filter should not remove people from the shared person model, where other lawful uses still need them.

These scopes can overlap because products use programme definitions and programmes use shared domain concepts. The boundary is about authority: a narrower consumer may compose, filter or extend a shared definition, but it should not silently make its own requirement part of the shared meaning for everyone else.

Clinical meaning must remain visible

A model can have a clear grain, a stable interface and the correct owner while still concealing an important clinical distinction. Reusable models need to preserve uncertainty and enough supporting evidence for another consumer to understand what their results mean.

Unknown is not the same as false

Clinical data often distinguishes “does not meet the definition” from “we do not have enough information to decide”. A missing observation, an explicit negative result, an inapplicable rule and an exclusion are not automatically the same state.

The necessary states need to be understood before they are reduced to a boolean. A model might keep a result such as met, not_met, insufficient_data or not_applicable, together with a simpler flag for consumers that genuinely need one. This keeps missing records from silently becoming clinical conclusions.

The population rule should state how nulls, missing evidence, exclusions and unresolved cases affect inclusion. Those decisions need tests just as the model's grain does.

Important results should remain explainable

A model should usually retain the columns needed to understand and validate its result. A final flag without dates, criteria or contributing values forces every investigation back into the SQL.

fct_person_diabetes_register keeps diagnosis dates, criteria flags and contributing codes alongside is_on_register. This lets a clinician inspect why a person was included. Similarly, fct_person_resource_index retains actual and expected costs, registration exposure and imputation information alongside the final index.

A useful result retains the inputs and flags a reviewer needs to answer “why did this row receive this result?” It should not expose unnecessary source detail, but neither should it reduce an explainable decision to an opaque verdict.

Naming the model

A name is the design's first public statement. Use stable entities, events and states rather than the ticket or first dashboard that needs them.

Put the entity first so related names stay together: dim_person_age, dim_person_ethnicity and dim_person_housebound_status. Use the vocabulary already in the project; a new synonym makes an otherwise good model harder to find.

Choosing a name follows the design decisions already made:

1. State the concept and grain. For example: “current open waiting-list pathways, one row per pathway”.
2. Search the important nouns. Check for an existing model and inspect related names.
3. Choose the role. Use the model's layer and analytical responsibility to select the prefix.
4. Use established vocabulary. Match the terms and word order used by neighbouring models.
5. Add a suffix only when it carries information. Avoid _data, _table, _new and _final.
6. Document the exact promise. State the grain, selection rules and important exceptions in YAML, then protect them with tests.

The command reference keeps the prefix, suffix and family tables available as a quick lookup, and the model taxonomy lesson covers reading and searching these names from the consumer's side.

A model-design checklist

1. Name the domain concept. Identify the entity, event, state or relationship that remains useful beyond the first output.
2. State population, time and grain. Complete “one row per…, included when…, as of…” and identify the uniqueness test that protects it.
3. Search for existing definitions. Follow the project's discovery method and reuse or extend them instead of creating a parallel meaning.
4. Choose the boundary. Separate logic that has its own grain, tests, owner, reuse or reason to change.
5. Design for consumers. Include commonly needed context while preserving the core grain. Prefer a useful denormalised mart when it removes routine consumer joins.
6. Keep scoped rules at the scope that owns them. Shared models may contain agreed organisation-wide definitions. Programme, audience and product rules belong in their respective folders or schemas and should compose shared domain models rather than redefine them for everyone.
7. Handle uncertainty explicitly. Do not silently turn missing evidence or not-applicable cases into false.
8. Retain useful evidence. Keep enough information to explain and validate important results.

Once those boundaries are chosen, the tests and documentation lesson turns each intended contract into something consumers and the pipeline can verify.