Finding and reusing models

Begin with a question you can investigate

A request arrives for an asthma dashboard with register membership, management information and current practice. The absence of a table named after that dashboard does not tell us how much work is missing. We need to find the models for its individual concepts and assess what they provide.

Search for the contract you need

Searching starts before the model name. Translate the request into the contract the data must satisfy: its subject, population, time and grain. The output format matters too, but it should not be confused with the underlying domain concept.

Consider a request for "current asthma-register membership, asthma management and current practice". It contains several independently searchable ideas:

Look for a model that fits each part of the question. A name resembling the whole request is not enough to establish that fit. If the exact dashboard output does not exist, that may be the only new work required.

Search names, then inspect the candidates

In VS Code, open the project folder and search filenames for asthma. Use the file picker for names, and Search for text in SQL and YAML. In the project catalogue, search the same subject and open a model's description and lineage. You can read the repository without a working dbt connection.

Expect several candidates: fct_person_asthma_register describes membership, int_asthma_diagnoses_all supplies diagnosis evidence, and int_asthma_management contains management logic. Their names suggest different jobs. They are not interchangeable answers.

If you prefer the terminal, these commands search filenames and list the register's connected models. The + on each side includes both ancestors and descendants; listing them does not build them.

rg --files models | rg asthma
dbt ls -s +fct_person_asthma_register+ --output name

Begin with the most settled useful layer

Layer knowledge gives the search a direction. For general analytical work, reporting marts are normally the first place to look: they contain business-ready facts, dimensions and wide analytical tables at documented grains. Published models are the first choice when the request is for the named product they serve.

Modelling-layer models become relevant when the question needs reusable evidence or a transformation that has not yet been expressed as a business-ready mart. Staging and raw models are implementation foundations. Reaching them from an ordinary business question should be exceptional, because it means the downstream project has not yet settled the required concept.

This is a starting order, not a rule that higher layers are always more correct. A published model may contain policy for another audience. A reporting fact may use the wrong reference time for the question. The layer tells you what kind of promise to expect; the model's contract decides whether that promise fits.

Read the contract before the implementation

Once a candidate model appears, start with its name and YAML rather than reading every CTE. A useful description should identify the subject, population, time and grain. Tests should protect the key or key combination that makes the grain true. Column descriptions should explain units, coding, selection rules and null meaning where those are not obvious.

Illustrative description: one row per person meeting the current asthma register definition at build time. Non-members are absent. Supporting diagnosis and medication evidence explains inclusion.

That description is useful because it settles questions a name cannot. It also rules out using the table as the full population for a historical report or as a list of everyone without asthma. In a real candidate, read the specific criteria and dates, then check that its SQL and tests agree.

Previewing rows can confirm that values look plausible, but it cannot establish the contract. A sample may contain one row per person by chance. It may not include a tie, an unresolved code or a person whose current practice changed. Documentation and tests state the intended behaviour; SQL and data inspection provide evidence that the implementation matches it.

Lineage supplies context that one file cannot

Upstream lineage explains where a model's meaning comes from. It shows whether a current result was selected from a full history, whether a fact reused a shared register and where source-specific preparation was absorbed. Downstream lineage shows which products already rely on the contract and therefore what a change could affect.

Existing consumers are useful evidence, but they are not proof that a model is appropriate for every use. A dashboard may apply a programme filter downstream, or may itself be due for correction. Read lineage to understand scope and consequences, then return to the candidate model's own documented promise.

Trust comes from several kinds of evidence

The existence of a model is not itself a recommendation. Mature projects contain experiments, narrowly scoped programme models, older conventions and definitions that are being replaced. Discovery needs to establish not only that a relation exists, but that it is an appropriate supported interface for the proposed use.

Confidence comes from agreement between several signals. The name places the model in a recognisable family. Its folder and schema identify scope. The description states a usable contract. Grain and semantic tests protect important claims. SQL implements the documented decisions. Lineage shows established inputs and consumers. Recent build results show whether those assertions currently hold.

No single signal is enough. A polished description can be stale. A passing uniqueness test cannot establish the intended population. Several downstream consumers can all depend on the same historical mistake. When the signals disagree, that disagreement is part of the work: clarify the contract, speak to the owner and correct the shared model before building another product on top.

Source freshness and coverage matter too, but they should be interpreted at the boundary that owns them. An analyst using a current reporting mart needs to know whether it built successfully and the period it represents. They should not need to relearn every feed-delivery column used to establish that answer.

Reuse, compose, extend or create

Discovery ends with a design decision. "A related model exists" does not automatically mean it should be reused unchanged, and "the exact output does not exist" does not automatically justify a new shared definition.

Reuse when an existing model already expresses the required concept at the required time and grain. Compose when several settled concepts need to be brought together for a new question or product. Extend when a broadly useful attribute belongs to an existing model's subject and preserves its contract. Create when the project lacks a shared concept, temporal contract or analytical subject.

Product-specific composition normally belongs in published. A new organisation-wide clinical fact belongs in reporting. Reusable evidence that makes that fact readable may belong in modelling. The decision is not about minimising the number of files; it is about leaving each new piece of knowledge at the scope where future users can find and trust it.

Reuse also respects scope. A model in a respiratory programme folder may be well designed and heavily used while still encoding thresholds that are authoritative only for that programme. Another programme can reuse its shared asthma inputs without adopting its policy. Similarly, a secondary-use published model may apply exclusions that are inappropriate for direct care.

Extending an existing model is safest when the new field belongs to the same subject, preserves population, time and grain, and is useful to the model's intended consumers. If an addition gives the model a second subject or makes it change for a product-specific reason, composition in a new model is clearer.

Discovery should leave a trace

When a search concludes that no suitable model exists, record what was checked in the issue or pull request: the concepts searched, nearby models considered and the contract mismatch that makes new work necessary. This gives reviewers a way to challenge the conclusion and prevents the next developer from repeating the same search without context.

When the right model does exist, use its name with ref() rather than copying its SQL or hardcoding its warehouse relation. That makes the reuse visible in lineage and ensures future corrections to the shared definition flow into the new composition.

What the search established

Suppose the request is for an asthma dashboard containing register membership, recent SABA prescribing, management measures, demographics and current practice. Searching "asthma" reveals several kinds of model rather than one ready-made dashboard table. (The Designing modelslesson revisits this same scenario from the builder's side: how these models came to be designed this way.)

fct_person_asthma_register is the reporting fact for register membership. int_asthma_diagnoses_all and int_asthma_medications_all are supporting evidence models at observation and medication-order grain. int_asthma_management contains management logic already used by programme models. Person demographics and current-practice dimensions supply descriptive context.

The absence of asthma_dashboard_basedoes not mean the analysis should return to clinical-record sources. It means the product composition is missing. If SABA or management measures do not yet have suitable business-ready reporting models, those are genuine domain gaps to fill first. A published dashboard model can then compose the register, those reporting facts and the relevant dimensions at the product's required grain.

Another asthma question can reuse the same register and measures in a different composition. A legitimately different threshold or time window can branch from the appropriate evidence model and receive its own clear definition. In neither case does the next developer have to rediscover how source records encode asthma diagnoses or medication orders.

The result of this search is a defined piece of work: which models fit, which do not and what is missing. Designing models continues the asthma example from that decision.

A discovery checklist

1. State the required subject, population, time and grain.
2. Search important nouns, related vocabulary and established naming families.
3. Start with published or reporting models and move upstream only when the required meaning is missing.
4. Read the candidate's description, grain tests and important column documentation.
5. Inspect ancestors for foundations and descendants for existing use and change impact.
6. Decide explicitly whether the request calls for reuse, composition, extension or a new domain concept.
7. If new work is required, leave reusable meaning outside the product-specific output.