project quality checks please

[graciegoheen]

Project Quality Checks

Every dbt project starts off as a blank slate. Clean. Organized. Governed. And then, the project grows. And grows. And grows. And as you add more models and more contributors, things start to slip through the cracks. A new model is added that doesn’t quite fit your org’s naming convention. A new column is added, but is missing a description. A model is promoted to public, but doesn’t get an assigned owner. This slow degradation of project quality happens silently in dbt today.

There are many community-driven packages and extensions of dbt that attempt to fill this gap — enabling teams to enforce project quality and alert developers when expectations drift from reality: dbt-checkpoint, @VDFaller’s metadata_checks, dbt-artifacts, dbt-doctor, dblect, tff, dbt-bouncer, sdf’s checks, the list goes on… The proof is out there. And project quality is every-growing in importance as code contributions are exploding in the age of AI.

As a creator and maintainer of one of those packages (dbt-project-evaluator 👽), I know both a) how important this feature is for maintaining a high-quality dbt project and b) how dbt isn’t-quite built for this today. The way dbt-project-evaluator tackles this problem is by unpacking the jinja {{ graph }} variable into a series of models and then adding a data test to each model checking for alignment with dbt best practices. And I’ll be the first to say that while this is clever and does indeed work, it’s a bit of a pain. These checks can only be run after those “core metadata” models have been built, and we don’t have a clean way to slowly introduce checks to an existing problematic project (e.g. there’s no simple mechanism to say “does this PR add a new problem or not?”).

So the pattern goes… the community has identified a problem, has extended dbt is fun-and-freaky ways to solve it, and now… it’s time to build a better out-of-the-box solution directly into dbt.

Why now?

Hopefully you’ve seen the announcement of dbt Core v2 (if not, go check out the blog post ;))! or the github discussions on our re-vamp of dbt-docs and writing to agents schema directly from dbt 👀

The key piece that’s relevant for this discussion is that v2 writes our your project metadata as new parquet artifacts — a high-performance alternative to large JSON files that can be directly queried through DuckDB. We’ve been calling this the “index layer” (though I’ve started thinking of it as “the dbt information schema”), and you can check it out here.

This layer is critical into bringing “project quality checks” to life.

So what might that look like?

We think: it’s all just SQL. Any assertion you can express as a SQL query against your project's metadata should be enforceable as a first-class “check” in dbt.

Let’s say you want to ensure every public model has an owner defined in its YAML. You write that “check” as a SQL query against the “dbt information schema”:

-- SPEC IS TBD

select
	unique_id,
	name
from {{ info_schema('nodes') }}
where resource_type = 'model'
  and access = 'public'
  and owner IS NULL

Just like tests, by default a check that returns 0 rows is a success; if any rows are returned, it’s an error.

Unlike tests, these can run after compile without ever touching your data warehouse.

🔥

Requirements

Here’s what I’m thinking:

• checks should be defined in SQL (just like data tests!)
• checks should query your project metadata (”the dbt information schema” parquets to disk)
• checks should work with state:modified and other selectors to only evaluate a subset of your project
• you should be able to prevent commands from running based on the results of checks
• you should be able to disable checks installed from a dbt package

Open Questions

Ok, that’s the big vision! There is a lot to sort out for how this should all work, and I know there are a ton of community members out there that have built or thought-about-building a solution for this. So please, chime in with your thoughts! Here’s the open questions already in my brain:

1. Should checks be a (1) new node type or a (2) new test type? (2) makes use of existing commands, configs, env vars, etc, but “checks” aren’t associated with a specific model/node, the way that tests are. 🤔

   option 1: new node type

   • Checks are a new node type in you DAG.
   • Checks are .sql files in a checks/ directory.
   • You can customize that directory by configuring check-paths: in your dbt_project.yml.
   • Checks are run via dbt check command (a la dbt lint).
   • Or (maybe?) as a part of dbt build executed in appropriate DAG-order (”as early as possible”). Or as part of dbt compile. Exclude checks with resource_type: selector.

   option 2: new test type

   • metadata_tests (name TBD) are assertions you make about the metadata in your project.
   • dbt tests runs all test types. You can use existing test_type: selector / environment variable to optionally exclude / include specific test types (e.g. exclude unit tests in production environment).
   • dbt build includes all tests, executed in appropriate DAG-order (”as early as possible”).
   • Set severity (and warn_if/error_if thresholds) to control what logs/status should be outputted by a failing test.
   • Set fail calc to control how failures are evaluated.

2. When should checks run? dbt check, dbt compile, dbt lint, dbt build?

3. How should users specify modeling layers, naming conventions, etc. that will be used as part of checks? Should we have a set default? dbt-project-evaluator makes uses of project variables, but we could (should?) do something that feels more clean.

   layers: 
     - name: staging
       prefixes: ['stg_']
       folders: ['staging']
     - name: marts
       prefixes: ['fct_', 'dim_']
       folders: ['marts']
   
   ...

4. Beyond the checks framework, should dbt also ship with basic built-in checks or should that be handled by a package (a la dbt_utils for data tests)? If we did include basic built-in checks how would you “turn them on”?

5. What “rules” should be handled by lint vs. checks?

6. Do folks want additional jinja in these checks beyond “ref”-ing the dbt information schema (like run_query(), {% if ... %}, etc.)?

7. What kind of logging do you want these checks to produce?

8. What should the default severity be of checks? Should that be configurable? How?

[VDFaller]

Should checks be a (1) new node type or a (2) new test type?

• Much prefer option 2 to option 1. But I don't like that it would run as part of tests.
  • My workflow during development/discovery, is to play with the shape. I sometimes add data-tests in there to make sure my assumptions are correct. It would be a nuisance to not be able to dbt build because I haven't added table/column descriptions (for columns that may not even make it into prod) without an --exclude test_type:metadata.*
• One of dbt's strong point in its testing is that it has so many tests where you don't ahve to write the sql. Having it be a node that forces sql would kind of a step backwards in my opinion.
• I think the metadata-tests should be able to be configured within dbt_project.yml. The way I setup my metadata checks with the dbt-index was to set it up as a config so that it could inherit and override at deeper levels. 90% of our metadata tests are currently for either staging/intermediate/mart models, or all sources.
• I like the idea of being able to extend checks with custom SQL upon the dbt index.

When should checks run? dbt check, dbt compile, dbt lint, dbt build?

dbt check explicitly NOT included in dbt build. Same reason as above.

How should users specify modeling layers, naming conventions, etc. that will be used as part of checks?

For you exact example that seems like it would be a naming convention check. I think it would be great if that could be an argument of the specific test (similar to how tests currently is)

models:
  jaffle_shop:
    staging:
        metadata-tests:
          - enforce_naming_conventions:
              prefixes: ["_stg"]
    mart:
        metadata-tests:
          - enforce_naming_conventions:
              prefixes: ["_fct", "_dim"]

Though that may not be great if those args would be used elsewhere, maybe jinja vars could be used? Or maybe the prefiexes could just be outside metadata-tests if it's required for enough of them?

Beyond the checks framework, should dbt also ship with basic built-in checks or should that be handled by a package (a la dbt_utils for data tests)

If y'all were to have built ins, the most important one that I think would be used often is the pk/grain test check. Though I would be fine to have them live entirely in a separate package.

What “rules” should be handled by lint vs. checks?

• I'd be fine with them being the same personally, as I would just have them as pre-push commits or CI tests anyway. Separation between yaml and sql makes sense but I don't ahve a strong opinion.

Do folks want additional jinja in these checks beyond “ref”-ing the dbt information schema (like run_query(), {% if ... %}, etc.)?
TBD. I'll be back.

What kind of logging do you want these checks to produce?

I'd want per source/model failures only unless --verbose (or some other switch). Plus a summary line. Ruff like

What should the default severity be of checks? Should that be configurable? How?

same as tests??

[VDFaller]

One big thing I forgot to mention. I think for some of the checks, there are at least a few safe fixes that we could do. (Propogate pass through column descriptions is the one that comes to mind immediately). I don't think you'd be able to do that without destroying people's comments, at least not in rust.

[sophieml]

I have many thoughts that need a bit more time in the sous vide 🧠 but my only strong opinion at the moment is:

Do folks want additional jinja in these checks beyond “ref”-ing the dbt information schema (like run_query(), {% if ... %}, etc.)?

IMO accessing ANY information beyond what's available within the context of the dbt project means that we're moving away from metaprogramming and into data test territory. I'm guessing that some amount of Jinja will be necessary so that checks will work on projects with heavy conditional logic, but I think that subset should be limited to Jinja that evaluates without a warehouse connection.

[kverburg]

Love the fact that thinking around this is getting more incorporated into the core of dbt and the simplicity for dbt information schema + sql tests is I think a beautiful combination.

On the questions:

Should checks be a (1) new node type or a (2) new test type?

Tests do sound most logical, but I might see a use case for "meta data" nodes as well. I can imagine that someone might want to create "views" on top of the dbt information schema and build tests on those views. Guess everything could be done in the tests itself, but if you want to keep things DRY a model might be needed for some things.

When should checks run?

Separate command, absolutely. Incorporating it into dbt build makes as much sense as running dbt lint in build, none. I don't want to run this in prod pipelines and I don't want to start excluding it. This is a pre-commit/CI step.

How should users specify modeling layers, naming conventions, etc. that will be used as part of checks?

I do think a separate top level key, like you have in your example, would be the cleanest. For three reasons: 1) I would consider it different from thing that need to happen to the data, which I have in the models/seeds/etc. 2) I don't want to add things to multiple of the keys we have now, if rules cover both. 3) Having the rules in models, could clutter the project quite a bit. Hence doing the exact opposite of what these are rules are trying to achieve.

Beyond the checks framework, should dbt also ship with basic built-in checks or should that be handled by a package (a la dbt_utils for data tests)?

I like the current split with utils quite a lot. Couple of tests built-in, extra things in utils. This should be the same for the checks. Naming conventions and some other low hanging fruit built-in, "public model without owner" and "source CTE's" in the package. Would even argue that they can be added to the current dbt utils.

But this brings me to the package<>"local project" structure. If we make the checks work more like other things in dbt, that means that whatever you define in a package, only is for the thing of that package if you install it. For instance a tag set in a package, is set on the models of that package. Not on the models in the project where you install this. This would be different for checks I imagine. Thing set in a package, would define the checks on the place where you install it.

This raises the question: will that happen automatically with the install, or do you need to explicitly "import" the checks from the package. For both ways you could make an argument. But I do think there needs to be a clear direction on this, as it has risks of double and contradicting checks.

What “rules” should be handled by lint vs. checks?

SQL style vs dbt style. Yes, some of dbt best practices are sql style (import cte's), but I think that is the best split I could think of. So something like:

• lint: Current SQLFluff, import CTEs, no hard table names (only ref), no create statements, eg
• check: Freshness set, documentation coverage, naming conventions

Do folks want additional jinja in these checks beyond “ref”-ing the dbt information schema (like run_query(), {% if ... %}, etc.)?

I have a check now that checks whats gets build in the warehouse vs what is in jinja docs, but dbt (Fusion) should be able to understand that without checking the warehouse. So if you can do that kind of thing, you don't need to be able to query the warehouse. But what about in the cases where you cannot predict the outcome because of introspective queries, will these models be excluded from checks?

What kind of logging do you want these checks to produce?

Something that we can use to automate fixes. Problem now with having it as dbt tests is that a failing test does not tell me the actual problem. It tells me that "x columns miss documentation", dbt project evaluator has a post hook to fix this, but that is just a work around that you would rather mitigate. So give rather a long list of issues, and what they exactly are than a summary which I still need to dive into.

What should the default severity be of checks? Should that be configurable? How?

Agree with @VDFaller here, easy: just as data tests.

[pempey]

Open Questions

1. I do not like the idea of them being included with all of the the other data_tests but that is mostly because I do not want business users getting lost in the documentation of what is effectively an administrative check. I want them documented but I would want it to live in a different place and not be easily confused. I guess that leans me toward option 1 where along with some standard tests, like documentation coverage, other custom test can be defined as SQL. I would not what this run with anything else at this time. It is to new and it impact and noise of running it is not know.

2. A separate check command.

3. Modeling layers are not something that we use and I have only seen them in the context of the Docs v2. If these kind of tests configs were locked behind this construct it would might mean lots of work to be able to even adopt the checks. Are layers a new Node type? Or just a project organization recommendation?

4. I like the idea of basic checks and I would confirue them at the project level and I would include things like docs and data_test coverage.

5. I think that lint covers only what is inside the sql file, everting outside of that would fall into the domain of check.

6. I am always for more customization generally and if I will already be predominant making our own custom tests I think I would want to be able to extent it with jinja

7. If we get into the realm of running custom sql then I think the logging can just be what checks were run against what nodes.

8. I think the default should be broad project level checks that would produce something almost like a report. Like a total model description coverage target: set the percentage and alert if it is less than the target. I would similarly think a basic data test coverage would be good: set the percentile of models that at least one column in a model have of one of a list of the inbuilt data_tests (unique, not null , etc) and alert if less than the target. Any tests built in should not depend on project structure or naming conventions as those are so dependant on the application of the dbt project, but I think most would agree documentation and data tests generally improve a project.

Musings

I have not been able to implement these kind of checks yet, but I have always wanted to. The checks that are top of mind for me is overlapping YAML selectors and documentation coverage.

My first idea of where I would use this would be CI jobs or pre-commit hooks, something I would do to test the integrity of the full project and less about checking the individual changes. I can see a use case for checking individual changes but it is not where my mind went to find the most value.

What about local state checks by bringing in a static reference index? Would we be able to query against both? Could that be configured?

[jochemvandooren]

You forgot about dbt-score! 😁 Nice to see official support for this, as it's really useful when projects grow larger and larger.

Should checks be a (1) new node type or a (2) new test type?

I think these checks should be a new node type, considering I don't want to run them during build or test. I'd rather run them separately, ideally during CI with a separate command.

Beyond the checks framework, should dbt also ship with basic built-in checks or should that be handled by a package (a la dbt_utils for data tests)? If we did include basic built-in checks how would you “turn them on”?

I am curious what some built-in checks would be? In dbt-score we have struggled with defining checks that are actually useful for all users. Being able to turn them on or off with some yaml config would suffice in my opinion.

What should the default severity be of checks? Should that be configurable? How?

Depends on how the severity is used in the output/result. I think it should also be up to the end-user how "strict" they want their checks to be. That's also how we implemented it in dbt-score; scoring, severities and rules can be freely configured so end-users can do whatever they want. Ultimately it's very hard to decide for data projects that you don't own what rules should apply to them.

[jsnb-devoted]

Really looking forward to any version of this feature. My preferred implementation of this effectively looks just like sdf checks so most of whats noted below is in line with that:

Should checks be a (1) new node type or a (2) new test type?

New node type please
Checks are .sql files in a checks/ directory would by my preference

When should checks run? dbt check, dbt compile, dbt lint, dbt build?

To me this is a new primitive that warrants its own command. Like the other commands (run, build, test) it would require compile to run first and thats ok with me

Beyond the checks framework, should dbt also ship with basic built-in checks or should that be handled by a package

I'd vote to be handled by a package. I think I'm more interested in authoring our own rules rather than conforming to existing ones

What “rules” should be handled by lint vs. checks?

checks differ to me in that they rely on looking at the compiled metadata of the project vs lint rules look at the contents of the file.

Do folks want additional jinja in these checks beyond “ref”-ing the dbt information schema (like run_query(), {% if ... %}, etc.)?

I'd be interested in seeing how far folks can get with no jinja including no refs/sources. Unless folks are planning on chaining checks there aren't really any dependencies to manage. To me I would prefer to author the checks like this:

SELECT * FROM nodes WHERE xxxx

rather than this

SELECT * FROM {{ ref('nodes') }} WHERE xxx