Course contents

The Analytics Engineer’s Field Guidedbt 3: ship it like a teamStep 12: dbt-score

dbt-score

Part 2 · dbt · step 12

dbt-score: turn "good practice" into a number

When many people build in the same project, you measure quality instead of hoping for it.

· · ·

Alice's project now has a dozen contributors, and quality is drifting: some models have no description, others no owner. Standards that live in a wiki get ignored.

dbt-score is an open-source linter from Picnic, a Dutch online supermarket. A linter checks code against rules without running it. dbt-score reads target/manifest.json, the map of everything your project declares (step 9). It grades every model, source, seed, snapshot, exposure and macro (each one an entity) out of 10 and hands out medals. Hard-to-maintain becomes a number instead of a feeling.

9.2/10
Project score for my_project, the average across every entity. By default a perfect 10 earns gold, 8.0 or more silver, 6.0 or more bronze, and anything lower (work in progress).

What it checks: the built-in rules

dbt-score ships a set of generic rules. Each rule targets one entity type, so a model rule never runs on a macro (macros get their own, like macro_has_description). The main model rules (the two marked "tables only" skip views):

Rule
What it wants
has_description
The model has a description.
columns_have_description
Every column is documented, not just the model.
has_owner
An owner is set in meta (the config: meta: from step 2 counts), so there's a name to ask.
has_uniqueness_test
Tables only. A table whose key is declared with a primary_key constraint (step 10) also has a unique test on it. No declared key, nothing to check.
sql_has_reasonable_number_of_lines
The model isn't a monster (default max 200 lines).
has_example_sql
The description holds an example query in a ```sql block. Low severity.
has_no_unused_is_incremental
Tables only. A non-incremental model isn't carrying dead is_incremental() code.

Try it now. dbt-score is already in requirements-dev.txt from step 11. From the project root:

# writes target/manifest.json; reads files only, never queries Snowflake
dbt parse

# score every entity; --show all lists passing rules too
dbt-score lint --show all

You get a score and a medal per entity, with the rules each one passed or missed:

Model: dim_customer (score: 10.0)
    OK   dbt_score.rules.generic.has_description
    OK   dbt_score.rules.generic.has_owner
    ...

Model: stg_legacy_orders (score: 7.3)
    OK   dbt_score.rules.generic.has_description
    WARN (medium) dbt_score.rules.generic.has_owner: Model lacks an owner.
    WARN (medium) dbt_score.rules.generic.columns_have_description: Columns lack a description: status.
    ...
Project score: 9.2 

Model: is the entity type. WARN marks a broken rule, with its severity in brackets, and every one lowers the score. The project score averages all entities, most of them cut from this sample.

When do you actually need it?

Once several people share the project. Solo, with ten models, your own review catches everything. With many people, the score reviews every model the same way. It shows a new joiner what "good" looks like, and CI blocks a low score without a reviewer having to ask. Inherited a messy project? Score it, set the floor at today's number, and never let a change pull it down. Until then, skip it: it's a tool for scale.

Write your own rule: public models have a contract

The built-ins cover docs, ownership and testing. Anything specific to your team you add as a small Python function. Here's step 10's advice as a rule: the models other teams depend on carry an enforced contract. You mark those few in _models.yml with config: {access: public}, a dbt setting for "other teams may build on this". The rule leaves every other model alone:

# dbt_score_rules/my_rules.py, at the project root
from dbt_score import Model, RuleViolation, rule

@rule
def public_model_has_contract(model: Model) -> RuleViolation | None:
    """A public model must enforce a contract."""
    if model.access == "public":
        if not model.config.get("contract", {}).get("enforced"):
            return RuleViolation(message=f"{model.name} is public without a contract.")

Python notes. @rule registers the function as a rule, named after the function. -> RuleViolation | None says it returns a violation (with a reason) or nothing, which means pass. model also has name, original_file_path (like models/marts/fct_orders.sql), columns, meta and raw_code (your SQL as written). dbt-score's docs list the rest.

dbt-score loads its built-in rules (dbt_score.rules) plus any folder named dbt_score_rules in the directory you run it from. Run it from the project root, keep the folder there, and you're done: no config, no __init__.py.

Severity, medals and the score

Each rule has a severity: 1 low, 2 medium (most built-ins), 3 high, 4 critical. It has nothing to do with a data test's severity: warn from step 7. Here's the math. A passed rule earns 3 points. A miss earns 3 minus its severity: 2 for low, 1 for medium, 0 for high. A critical miss drops the whole entity to 0. The score is points earned over points possible, times 10.

Take stg_legacy_orders above. It's a view, so the five rules without "tables only" apply. Three passes and two medium misses: 3 + 3 + 3 + 1 + 1 = 11 of 15 points, and 11 / 15 × 10 = 7.3.

All of dbt-score's settings live in the pyproject.toml from step 11, at the project root, below [tool.sqlfmt]:

# pyproject.toml, the dbt-score part
[tool.dbt-score]
fail_project_under = 7.0    # exit code 1 if the project average drops below
fail_any_item_under = 5.0   # exit code 1 if any single entity scores below

[tool.dbt-score.rules."dbt_score.rules.generic.has_description"]
severity = 3                  # a missing description now costs all 3 points

[tool.dbt-score.badges]
second.threshold = 7.5         # silver from 7.5 instead of 8.0

One more key, rule_namespaces, is only for loading rules from elsewhere, like a package your company shares. If you set it, keep "dbt_score.rules" in the list, or the built-ins silently stop running.

Where it runs: the merge gate

The score does its real work in CI, as a gate on every pull request.

Pull requestnew / changed model
dbt-score lintscore the change
project score
≥ the floor?
yes
✓ mergestandard held
no
✗ blockedadd description · owner · tests↺ push a fix, lint again
Start the floor at today's score
Blocking everything below 8/10 on day one just blocks everyone. Set fail_project_under to the project's score today instead. A change that pulls the project below that floor fails. When a merge request raises the score, its author also raises the floor in pyproject.toml, in the same merge request. The floor only moves up, a messy project climbs one merge at a time, and nobody is stuck rewriting old models before they can ship.

The CI job

dbt parse needs a profile, and your ~/.dbt file isn't on the CI runner. So CI gets its own, committed under ci/. It holds no secret, only env_var() names, and steps 14 and 15 reuse it:

# ci/profiles.yml
my_project:
  target: local
  outputs:
    local:                   # DuckDB in a file (step 15): no login at all
      type: duckdb
      path: analytics.duckdb
    pre_prod:                # Snowflake; values come from CI variables
      type: snowflake
      account: "{{ env_var('DBT_SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('DBT_SNOWFLAKE_USER') }}"
      private_key: "{{ env_var('DBT_ENV_SECRET_SNOWFLAKE_KEY') }}"
      role: PRE_PROD_ROLE
      database: PRE_PROD_DB
      warehouse: PRE_PROD_WH
      schema: analytics

Then add a second job to the test stage of the .gitlab-ci.yml from step 11:

score:
  stage: test
  image: python:3.12
  variables:
    DBT_PROFILES_DIR: ci          # dbt reads ci/profiles.yml
  script:
    - pip install -r requirements-dev.txt
    - dbt deps                   # parse needs the packages (step 6)
    - dbt parse --target local   # reads files, never connects
    - dbt-score lint             # exits 1 under a floor: the job fails
  rules:
    - if: '$CI_MERGE_REQUEST_TARGET_BRANCH_NAME == "pre_prod"'

Parsing with local means the job needs no secret at all. With pre_prod, its CI variables would have to be set even to parse. The floors then block a low score on their own, no reviewer required.

Lesson quiz

  1. What does dbt-score grade?

    1. Each model's runtime and credits from the last production run
    2. Each model's SQL style: indentation, keyword casing and comma placement
    3. Each model's metadata against rules: description, owner, tests, config
    4. Each model's data quality, based on how many of its tests failed
  2. Which command must run before dbt-score lint, and why?

    1. dbt test, because the score counts the passing tests
    2. dbt docs serve, because it reads the docs site's pages
    3. dbt parse, because dbt-score reads the manifest it writes
    4. dbt run, because dbt-score inspects the built tables
  3. pyproject.toml sets fail_project_under = 7.0 and fail_any_item_under = 5.0. Alice's merge request lifts the project score from 7.4 to 7.6, but her new model scores 4.5. What does the dbt-score job do?

    1. Fails, since the floor wasn't raised in the same MR
    2. Passes, but asks her to raise the floor to 7.6
    3. Fails, since one model is below the item floor
    4. Passes, since the project score went up