Benefits of dbt: (Part 2) Documentation & Testing

This is part 2 of a 3 part series on the benefits of dbt:

• Part 1: Dependencies

To recap, the main benefits of dbt are:

• Lowering the technical barrier for building a data pipeline
• Inferring dependencies
• Making documenting & testing easy (which we’ll go through here)
• Adding Python-like functionality to SQL

The bottom 3 benefits all roll in to the first benefit, so I won’t be going in to detail on that aspect as it’s more of a consequence of the other 3.

Documenting and testing your SQL models is normally quite a chore, and often forgotten entirely!

Documenting data models, and what each column means, is usually done in an entirely separate tool — like Confluence or Notion — to where the SQL is written making it less likely that the 2 will be kept in sync. It’s also not a great experience for the developer or the end user.

Pictured: Documenting data models (probably). Image source

Testing data models is a heavily manual process. You can easily enforce things like “what columns does this data model have” or “what datatype should this be”, but running data quality checks after a model is built involves a separate SQL file/statement per check.

Example of how you’d check for errors without dbt

You’d run a manual script like the above, and have to set up errors if it returned any rows. This is fiddly to configure and easy to get wrong — plus you can’t make any of your tests reusable!

dbt introduced a new way of both documenting and testing your data models — .yml(YML or YAML) files. (To be clear here, dbt didn’t invent .yml files!)

An example dim_transactions.yml file

These files live alongside your .sql files, meaning that you can develop your SQL and write your documentation & tests in one tool (wherever you write your SQL).

You’ll also notice that testing any column is as simple as adding a single line to your .yml file! You can also run tests on the whole model (table) as well.

dbt comes with some basic tests, but you can also bring in open source dbt packages for extra tests (such as the dbt_utils.expression_is_true test) and even create your own custom tests (more on that in part 3).

In summary, dbt has made documenting and testing data models far, far easier than before through the use of .yml files.

New to dbt, or someone who wants to learn the advanced concepts? My dbt course is now live on Udemy (link), and covers everything from basic to advanced dbt — including building a full project from scratch, 7 hours of video, and 50 downloadable course materials!