Write a staging model

Place and name it

The file goes in models/staging/{domain}/ and is named stg_{source}_{table}.sql, mirroring the raw model it reads. Placement is not tidiness: in this project the folder is configuration — it decides the schema the model builds into and the materialisation it gets. Check a neighbouring model before inventing a new path; if your file sits where its siblings sit, its configuration is already correct.

models/staging/shared/stg_reference_opening_hours.sql

The contract, and why each rule exists

select
    organisation_code,
    upper(trim(site_code)) as site_code,
    day_of_week,
    opens_at::time as opens_at,
    closes_at::time as closes_at
from {{ ref('raw_reference_opening_hours') }}

One raw model, through ref(). The raw model is the project's single stable interface to that feed. Read it and you inherit that stability; go around it — a source() call or a hardcoded table — and your model breaks dev/prod separation and disappears from the part of the lineage everyone else relies on.

Explicit columns, never select *. The column list is the promise downstream models build against. With *, a column added to the feed appears downstream unannounced and a removed one breaks consumers with no warning from you. Naming the columns makes every change to the interface a deliberate, reviewable act.

Clean and cast here, once. The upper(trim(...)) and the ::time casts are the point of the layer: every downstream model inherits them, so nobody ever parses that string or wonders about stray whitespace again. This is also where names move to the project's conventions — is_/has_ for booleans, _date, _at, _id suffixes — so the whole warehouse speaks one language.

Keep every row. A staging model is a faithful copy, cleaned. Dropping rows is a business decision — “only active registrations”, “exclude test patients” — and business decisions belong in the modelling layer, where they are named, visible and reusable. The only rows staging may remove are true technical duplicates.

No joins. The moment you want one, you have started a different job. A join in staging buries logic where nobody will look for it and computes it for every consumer whether they want it or not. Wanting a join is the signal to open an int_ model instead — that is not a workaround, it is the architecture working.

Look at the output, not just the status

dbt show -s stg_reference_opening_hours
dbt compile -s stg_reference_opening_hours

A green build proves the SQL ran; it says nothing about whether the rows are right. Read the sample from dbt show against what you know of the source: is the grain what you expected, are the nulls where you expected them, did the casts behave — a ::time on a malformed string, for example, fails at build, but a lossy rename fails silently. Thirty seconds of looking at rows here saves a review round later.

• Correct folder and stg_ name
• One raw ref() and explicit columns
• No joins or business filters
• dbt show returns sensible rows