How I Build Gold Views with dbt

The last post was the standard: what earns a table a place in the gold layer, and what keeps it there. This one is the machinery underneath it. Same lakehouse, same organization, but this time I am going to open the dbt project and show how the standard turns into code you can review in a pull request instead of a principle you hope people remember.

That post promised a write-up on the semantic layer. This is not it. This is the layer beneath it: the dbt project that builds the clean gold tables a semantic layer would sit on.

I am staying narrow on purpose. This is about building gold views specifically, not standing up dbt from scratch. Where something is a prerequisite I will point at a starting place and move on.

Prerequisites I am assuming

• A Databricks workspace with Unity Catalog, and silver tables already landed. Gold reads from silver, so silver has to exist first. If you are starting cold, Get started with Unity Catalog is the place to begin.
• dbt installed with the Databricks adapter (dbt-core and dbt-databricks). See Install dbt and the Databricks setup guide for the adapter. I install the CLI as an isolated tool, but any working dbt binary is fine.
• A SQL warehouse for dbt to run against. Create a SQL warehouse covers it, and a serverless one is the easiest to start with.
• A way to authenticate to that warehouse. Locally I mint a short-lived token from a cached Databricks CLI login (see Databricks CLI authentication). In production the scheduled job authenticates as its own identity.
• You have read the previous post. The admission tests, the grain discipline, and the presentation-view escape hatch all carry over. I am implementing them here, not re-arguing them.

Everything below is a real project. Our first dbt project builds the conformed basketball gold layer on top of the existing Second Spectrum and NBA Stats silver tables. I have stripped internal ticket numbers, workspace identifiers, and names, but the shapes are exactly what ships.

Gold is views until a view is too slow

The first decision is the one people find surprising, so I will lead with it. Our gold entities are views, not tables.

dbt’s rule of thumb, which I adopted directly, is a promotion ladder. Every model starts as a view. It becomes a table when it is too slow to query, and incremental when it is too slow to build. Each step up is complexity you have to justify, and most gold models never leave the first rung.

That default lives in one place, dbt_project.yml:

models:
  pse_basketball:
    # Gold marts default to views. Promotion ladder: view -> table ->
    # incremental. Promote only when query or build cost demands it.
    marts:
      +materialized: view
      +tags: ["gold"]

A view stores no rows. It re-runs its SQL against silver whenever someone queries it, so it cannot drift from its source and costs nothing to store. That is what “small and stable” looks like day to day: the default gold object is the cheapest one to change and the hardest one to let rot.

Promotion happens by overriding the config on the one model that needs it, not by touching the default. A model a dashboard hits every fifteen minutes might become a table. A model that is expensive to rebuild across a full season might become incremental. The burden of proof sits on the promotion, never on staying a view.

The shape of the project

Here is the whole project with the noise removed:

dbt/
|
|-- dbt_project.yml            # project config: paths, the marts default, seeds, vars
|-- profiles.yml               # connection: dev + prod targets (no secrets)
|-- packages.yml               # dependencies (dbt_utils)
|
|-- models/
|   |-- staging/
|   |   |-- _sources.yml       # existing silver tables, declared as dbt sources
|   |
|   |-- marts/
|       |-- basketball/
|           |-- gold_player.sql        # one conformed dimension per file
|           |-- gold_team.sql
|           |-- gold_shot.sql          # one fact per file
|           |-- gold_possession.sql
|           |-- ...                    # one .sql per entity or fact
|           |-- _gold_dimensions.yml   # descriptions + grain tests (dimensions)
|           |-- _gold_facts.yml        # descriptions + grain tests (facts)
|
|-- glossary/
|   |-- metrics.yml            # canonical metric + dimension definitions
|
|-- seeds/                     # small hand-maintained lookups

There are three tiers, and they map onto the medallion layers. Sources are silver: data dbt reads but does not build. Marts are gold: the models dbt builds, one file per entity or fact. The YAML files next to the models are where the grain gets declared and tested. dbt treats staging as the layer closest to raw sources. I use it only to register silver, since our silver is already clean and I have no reason to rebuild it.

The rest of dbt_project.yml is ordinary, but two settings do real work later:

name: 'pse_basketball'
version: '1.0.0'
config-version: 2
profile: 'pse_basketball'

model-paths: ["models"]
seed-paths: ["seeds"]
macro-paths: ["macros"]

models:
  pse_basketball:
    marts:
      +materialized: view
      +tags: ["gold"]
      # Persist model + column descriptions into Unity Catalog as COMMENTs.
      +persist_docs:
        relation: true
        columns: true

The gold tag lets me build or test the whole layer with one selector (dbt build --select tag:gold). The persist_docs block turns YAML descriptions into catalog metadata, and it has a sharp edge on views that I will come back to.

Silver is a source, not a model

I do not rebuild silver in dbt. It already exists, built by the ingestion pipelines, and re-deriving it here would only create a second definition to keep in sync. Instead I register the silver tables as dbt sources, so gold models can reference them by name and dbt can trace lineage back to them.

That is what models/staging/_sources.yml holds:

version: 2

# Existing silver tables consumed as dbt sources. We do not rebuild silver;
# the gold marts read from these. (dbt's staging layer ~= our silver.)
sources:
  - name: second_spectrum
    database: basketball          # Unity Catalog catalog
    schema: second_spectrum
    description: Second Spectrum tracking/markings silver.
    tables:
      - name: silver_markings_shots
        description: One row per shot attempt (carries shot quality, region, chance id).
      - name: silver_players
        description: Player dimension with the NBA <-> Second Spectrum id cross-reference.
      - name: silver_teams
        description: Team dimension with the NBA <-> Second Spectrum id cross-reference.

  - name: nba_stats
    database: basketball
    schema: nba_stats
    description: NBA Stats silver (schedule + canonical player/team indexes).
    tables:
      - name: silver_player_index
        description: Canonical NBA player dimension.
      - name: silver_schedule
        description: Game schedule; drives the tip-off-minus-two-days trigger.

Two things here matter.

A source is a reference, not a copy. Declaring silver_markings_shots does not move a single row. It tells dbt the table exists and that gold depends on it, so references resolve and lineage stays complete. If a silver table I depend on disappears, dbt knows the gold layer is downstream of it.

Sources can also follow the environment. Most of our silver lives in the prod basketball catalog no matter where I build, so I hard-code database: basketball. Some silver is built per-environment, and for those I point the source at whichever catalog the current target uses:

  # Coaching-analytics silver built OUTSIDE dbt by a Spark ingest job. Unlike the
  # feeds above (always prod `basketball`), this one follows the env, so it reads
  # from the dbt target's own catalog: dev_basketball in dev, basketball in prod.
  - name: coaching_analytics
    database: "{{ target.database }}"
    schema: coaching_analytics
    description: Coaching-analytics silver produced by a Spark ingest (not dbt-built).
    tables:
      - name: silver_fever_offensive_set_grading
        description: One row per offensive call per grading snapshot.
        columns:
          - name: call_name
            description: Play call, verbatim. Should map to gold_offensive_set; unmapped calls warn.
            tests:
              - relationships:
                  to: ref('gold_offensive_set')
                  field: call_name
                  config: {severity: warn}

{{ target.database }} resolves to the catalog of whichever target I run. That one substitution is why the same source definition reads dev in dev and prod in prod. You can also see a test hanging off a source column there. More on what severity: warn buys me below.

Writing a gold model

A gold model is a .sql file that selects from sources and other models. That is all it is. Here is a full conformed dimension, gold_player.sql:

-- Conformed player dimension.
-- Grain: one row per player.
-- Bridges the NBA player id and the Second Spectrum player id (the markings
-- facts reference players by second_spectrum_player_id; the report shows names).
with ss_players as (
    select * from {{ source('second_spectrum', 'silver_players') }}
),
nba as (
    select * from {{ source('nba_stats', 'silver_player_index') }}
)
select
    ss_players.nba_player_id              as player_id,                  -- PK (canonical NBA id)
    ss_players.second_spectrum_player_id  as second_spectrum_player_id,  -- join key to markings facts
    ss_players.player_full_name,
    ss_players.player_first_name,
    ss_players.player_last_name,
    nba.player_position,
    nba.player_jersey_number,
    nba.player_height,
    nba.most_recent_team_id
from ss_players
left join nba
    on ss_players.nba_player_id = nba.player_id

A few conventions I hold to in every model.

The file opens with a grain sentence. One row per player. If I cannot write that sentence, the model is not ready to exist. This is the same grain-first discipline from the last post, sitting on line two.

Models reference sources and other models through source() and ref(), never hard-coded names. dbt uses those to build the dependency graph, so it always builds gold_player before anything that references it, and the compiled SQL picks up the right catalog for the current target on its own.

The model does one join’s worth of real work. This dimension exists to bridge two vendors’ player ids into one canonical id. That bridge is business meaning, the kind of thing I keep out of silver and concentrate in gold.

The business logic is not always a join. Sometimes it is a single column that encodes a definition, and those are the ones I care most about. From gold_shot.sql:

-- Fact: shot attempts.
-- Grain: one row per shot (shot_id).
select
    shot_id,
    shooter_id,                     -- FK -> gold_player.second_spectrum_player_id
    offense_team_id,                -- FK -> gold_team.second_spectrum_team_id
    shot_made,
    three_point_shot,
    shooter_fouled,                 -- true = shooter was fouled on the attempt
    -- An official field-goal attempt EXCLUDES shots where the shooter was fouled
    -- and missed (those become free throws, not an FGA). Second Spectrum still
    -- marks them as shots, so consumers counting FGA / FG% must filter on this
    -- flag. And-1s (fouled + made) and blocked misses stay true: both are real FGA.
    not (shooter_fouled and not shot_made) as counts_as_fga,
    quantified_shot_quality,        -- vendor passthrough, 0-100
    shot_region,                    -- Second Spectrum's own shot-zone classification
    shot_distance
from {{ source('second_spectrum', 'silver_markings_shots') }}

counts_as_fga is one boolean, and it carries the whole case for the gold layer. The vendor marks a fouled-and-missed shot as a shot. The box score does not count it as a field-goal attempt. Left to compute FG% on their own, different consumers would each get this wrong in a slightly different way. Encoding it once, in the fact table, with the reasoning in a comment, is the kind of business semantics gold exists to hold. It also explains why this belongs in gold and not silver: silver represents what the vendor sent, and the vendor sent a shot.

Declaring the grain, then verifying it

Here is the platform fact that shapes how I test. On Databricks, PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints are informational only. They are never enforced. A table can carry a perfectly declared primary key and quietly accumulate duplicate grain rows underneath it. The last post argued that you therefore have to verify the grain with something executable. This is where that verification lives.

Every model has a companion entry in a schema YAML that declares its columns and attaches tests. For the dimensions, _gold_dimensions.yml:

version: 2

models:
  - name: gold_player
    description: >
      Conformed player dimension. Grain: one row per player. Bridges the NBA
      player id and the Second Spectrum player id (markings reference SS ids).
    columns:
      - name: player_id
        description: Canonical NBA player id (primary key).
        tests: [unique, not_null]
      - name: second_spectrum_player_id
        description: Second Spectrum player id; join key to the markings facts.
        tests: [not_null, unique]
      - name: player_full_name
        description: Display name used in shooter rankings and shot charts.
        tests: [not_null]

The unique and not_null pair on player_id is the grain verification. It is not a constraint the catalog might ignore. It is a query dbt runs on every build that fails the instant two rows share a player_id. “One row per player” stops being a hopeful comment and becomes an assertion the build checks. If it breaks, the build breaks, and I find out at deploy time rather than when a coach asks why a player shows up twice in a shot chart.

Facts get the same treatment on their key, plus foreign-key tests, with a deliberate difference in severity. From _gold_facts.yml:

version: 2

# Grain tests (unique / not_null on the PK) are errors: they are the grain
# verification. FK relationships tests are severity:warn so orphan ids surface
# as warnings (handled via an unknown-member row) without blocking the build.
models:
  - name: gold_shot
    description: "Fact: shot attempts. Grain: one row per shot (shot_id)."
    columns:
      - name: shot_id
        description: Second Spectrum shot id (primary key).
        tests: [unique, not_null]
      - name: shooter_id
        description: Shooter, FK to gold_player.second_spectrum_player_id.
        tests:
          - relationships:
              to: ref('gold_player')
              field: second_spectrum_player_id
              config: {severity: warn}
      - name: counts_as_fga
        description: >
          True when the row is an official field-goal attempt. Consumers counting
          FGA / FG% / 3PA must filter on this so totals reconcile to the box score.
        tests: [not_null]

The severity split is intentional, and it maps to the last post’s rules. The grain test is an error, because a duplicate shot is a broken fact table. The foreign-key relationships test is a warning, because an orphan shooter_id is a data-quality signal I want surfaced, but the plan is to resolve unmatched references to a designated unknown-member row rather than let a join drop them silently. A warning puts the orphan in front of me without failing the deploy while I confirm the dimension’s coverage. Once coverage is confirmed, tightening a warn to an error is a one-line change.

This is also where dbt_utils comes in. For a compound grain (one row per player per snapshot, say) a single-column unique test is not enough, and dbt_utils.unique_combination_of_columns tests uniqueness across the set. That is the whole of packages.yml:

packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<2.0.0"]

Metadata that ships with the model

Every description in those YAML files does two jobs. Because of the persist_docs block back in dbt_project.yml, dbt writes them into Unity Catalog as COMMENTs when it builds the model. The documentation is not a separate site that drifts out of date. It is the same YAML that defines the tests, pushed onto the catalog object and refreshed on every build. Someone browsing the table in the Databricks UI sees the grain sentence and the column definitions without opening the repo.

There is one sharp edge here, specific to Databricks, worth knowing before it surprises you. Relation comments persist on both views and tables, but column-level comments persist only on tables. A view in Databricks cannot carry column-level comments. So a gold model that is a view gets its table-level description in the catalog, while its column descriptions live only in the dbt docs.

That interacts with the materialization ladder. Most gold models are views, and for most of them table-level documentation plus the dbt docs is enough. But when a model genuinely needs column-level metadata in Unity Catalog, because a governance tool reads it there or because column tags matter, that need is a legitimate reason to promote the model from a view to a table. It is the one case where a documentation requirement, not query or build cost, moves a model up the ladder. Worth knowing so the promotion is a decision and not a surprise.

One codebase, dev and prod

The connection lives in profiles.yml, and its whole job is to make the environment a runtime choice rather than a code change. Two targets, identical except for the catalog they write to:

# Databricks connection. NO secrets in this file.
# Local: mint a short-lived token from your cached Databricks CLI login into
#   DBT_DATABRICKS_TOKEN. Prod: the scheduled job authenticates as its own identity.
pse_basketball:
  target: dev
  outputs:
    dev:
      type: databricks
      host: <workspace-host>
      http_path: /sql/1.0/warehouses/<warehouse-id>
      catalog: dev_basketball
      schema: coaching_analytics
      token: "{{ env_var('DBT_DATABRICKS_TOKEN', '') }}"
      threads: 4
    prod:
      type: databricks
      host: <workspace-host>
      http_path: /sql/1.0/warehouses/<warehouse-id>
      catalog: basketball
      schema: coaching_analytics
      token: "{{ env_var('DBT_DATABRICKS_TOKEN', '') }}"
      threads: 4

dbt build --target dev builds the whole gold layer as views into dev_basketball. dbt build --target prod builds the identical models into basketball. No model names a catalog, because the models use source() and ref(), which resolve to the target’s catalog at compile time. Combined with the {{ target.database }} trick in the sources file, a model never hard-codes an environment, so promoting from dev to prod is a change of flag rather than a change of code.

Two details keep this safe to commit. The token is read from an environment variable, never written in the file, so profiles.yml holds no secrets. And default('') means that if the variable is unset, dbt gets an empty token and fails to connect cleanly, rather than reaching for some ambient credential. I would rather a missing token be an obvious connection error than a mystery.

I run dbt build, not dbt run, because build interleaves models and their tests in dependency order. It builds a model, tests it, then builds what depends on it. A grain violation stops the graph at the broken model instead of pushing a duplicate downstream. In a layer whose entire promise is grain discipline, that ordering matters.

Where the metric definitions go

There is a rule from the last post I have been careful not to break here: gold tables store data, and metric definitions are not data. Nothing in these models defines “points per possession.” The models produce the clean rows a metric is computed against, and the definition lives somewhere governed and singular.

Today that somewhere is a glossary file in the same repo, glossary/metrics.yml, reviewed in pull requests like everything else:

metrics:
  - name: ppp
    label: Points Per Possession
    description: >
      Points scored per graded offensive possession for a set family, from summed
      points divided by summed possessions.
    metric_type: ratio
    numerator: points
    denominator: poss
    expr: sum(points) / nullif(sum(poss), 0)
    additivity: non_additive          # a ratio: never average row-level ppp; re-aggregate the components
    additive_components: [points, poss]
    grain: one row per (season, as_of_date, set_group)
    source: <catalog>.coaching_analytics.gold_offensive_set_efficiency
    verified: true

That one entry carries what a column cannot. It records that the metric is a ratio, so it is non-additive: you never average row-level ppp, you re-aggregate points and poss and divide. It names the components you are allowed to sum. It states the grain the definition assumes. Bake that into a column on a wide table and you have copied a definition into a place it will quietly drift. Write it here once and every consumer points at the same source of truth.

The file is shaped like a specification rather than documentation, because that is what it will become. When we adopt Unity Catalog metric views, this glossary is their spec: the same definitions, promoted from a reviewed YAML file into a governed platform object. That is the semantic-layer post I keep promising, and the reason it comes after this one. The definitions cannot move until the tables under them are trustworthy, which is what this whole post has been about.

The prime cut

The last post was a set of principles. The dbt project is what makes them hold. Gold defaults to views, so the entity layer stays cheap to change and hard to let rot. Silver is a source, so there is one definition of clean data and gold never rebuilds it. Every model opens with its grain and backs it with a unique test, because Databricks will not enforce a key and a comment is not verification. Descriptions ship into the catalog on every build. One codebase covers dev and prod, because the target swaps the catalog and the code stays put. The metric definitions wait in a glossary instead of hiding in columns.

None of it is exotic. It is dbt used plainly on Databricks, in service of a layer that stays small, stable, and reusable, this time because the build enforces it.

Sources

• dbt Labs, Materializations. The view / table / incremental ladder and how model configs override the default.
• dbt Labs, Add sources to your DAG. Declaring source tables, source(), and source freshness.
• dbt Labs, Add data tests to your DAG. The generic unique, not_null, and relationships tests, plus test severity.
• dbt Labs, persist_docs. Pushing model and column descriptions down to the warehouse as comments.
• dbt Labs, Databricks setup. The dbt-databricks adapter: profile fields, catalog, http_path, auth.
• dbt Labs, dbt_utils. unique_combination_of_columns for compound-grain verification.
• Databricks, Constraints on Databricks. Primary key, foreign key, and unique constraints are informational and unenforced; only NOT NULL and CHECK enforce.