Designing models

Good design contains the impact of change

Model design decides which model should own a definition and what its consumers can expect. 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.

A business definition needs one owner when several models depend on it. A maintained provider list, code set, threshold or date rule should not be copied across model SQL. Put shared data in a shared or correctly scoped model and join to it; use a macro for reused SQL logic and a project variable only for a value supplied per run or environment. A value that defines one model's concept belongs in that model even if it may change. Do not create another DAG node merely to extract a local literal.

Begin with the missing decision

A request gives you something concrete to deliver. Discovery establishes which definitions already exist. Design begins with the remaining decision: perhaps a new time window, a missing clinical measure or the way a product combines existing models. Describe that gap before adding files.

A waiting-list dashboard might need a new provider summary while already having suitable pathway records. Keep the pathway definition available for other uses, and give the summary its own stated grain. You do not need to predict every future report. You do need to avoid making the first report's filters part of a model presented as a shared definition.

Reusable models need explicit contracts

A consumer should be able to understand a model without reading every line of its 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.

State the contract for the new model

The foundations chapter explains grain and join multiplicity. Apply those ideas before writing this model: one row per what, which records qualify, and as of when? A contract here means the model's documented analytical promises.

For an asthma measure, one row per person is only the start. Does it contain all registered people or only register members? Does it assess prescribing in the last year or in a fixed reporting period? Can people with missing evidence appear? Different answers produce different populations even if every result has a unique person key.

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. For a result meant to reproduce what was known last March, a diagnosis first recorded next month must not be included. A retrospective clinical view may deliberately use later-recorded evidence about earlier events. State which interpretation the model provides.

dim_person_current_practicetherefore 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_dateto 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

A clearly described component can still be awkward to use. 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_activityfollows 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.

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.

The same principle applies to coded categories. Keep a code when it is useful for traceability, filtering or joins, but do not make every analyst look up its meaning. An analyst-facing mart should also provide the authoritative label supplied by the source or owned by a shared reference model. A narrow modelling block may keep only the code when the downstream mart adds the label. If no authoritative description exists, do not invent one or hide a maintained mapping in a local case expression.

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.

Use the model's primary purpose

The facts and dimensions example starts with appointments and then clinical states. Use that distinction here: the asthma register records membership as the subject of analysis; ethnicity and practice provide context about its people. A model's primary purpose determines its name, even when another analysis uses it differently.

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_ethnicitycan 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_activityhave 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.

When a separate model earns its place

Separate a definition when it is reused, has its own grain, needs distinct tests or changes independently. Keep related calculations together when they implement one rule. SQL length alone does not decide the boundary.

Width is useful when consumers repeatedly need the same context and the joins preserve the grain. A person mart can include practice and ethnicity while the definitions of those attributes remain in their shared models.

Keep maintained definitions shared

A code set is part of a clinical definition. Reference the project's managed code sets rather than copying codes into several models. The same applies to a maintained provider mapping. A change to that shared input can then be reviewed once, with its consumers visible in the DAG.

A local calculation is different. Converting a duration already in this model from days to weeks does not automatically need a separate model. A reused selection of the latest qualifying observation may deserve one because it has its own grain and clinical rules.

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 modelslesson 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 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 and terminology have narrower authority. A respiratory programme might define its own labels, classifications, priority groups, thresholds and reporting periods. That vocabulary and logic are entirely appropriate in dbt, but they 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 terminology or 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 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.

Give the result a name readers can interpret

After choosing its purpose, population, time and grain, use the project's naming grammar. The shared clinical fact names its subject; the published dashboard model names its product. Check the description and name together. Neither should suggest broader coverage than the model actually provides.

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.