Standardizing integration tests for popular dbt packages

[emmyoop]

dbt Labs has begun the process of testing compatibility of popular dbt packages while testing & releasing changes to dbt in dbt Cloud.

Our goal is to ensure that dbt Cloud customers can confidently receive continuous dbt updates (”Versionless”), while also enabling us to proactively catch any incompatibilities or functional regressions before they are released in final versions of dbt Core OSS. Our focus to start is on packages that are used by a large number of projects, including packages maintained by dbt Labs and external maintainers. To that end, we are asking package maintainers to standardize a few details of their integration testing setup.

Target packages

We will start by testing the packages in the Package hub with the greatest usage. Currently, this includes:

dbt Labs maintained packages:

• ✅ dbt_utils
• ✅ audit_helper
• ✅ codegen
• ✅ dbt_external_tables
• ✅ dbt_project_evaluator

Externally maintained packages, in alphabetical order:

• automate_dv
• dbt_artifacts
• dbt_date
• ✅ dbt_expectations
• tsql_utils
• elementary
• fivetran_utils
• dbt_snowflake_query_tags
• dbt_meta_testing

To support this testing, package maintainers — the experts on the package functionality — will be the ones to maintain their suite of tests. (Most popular packages already do; thank you!) To ensure those tests will run during our automated CI/CD process, dbt Labs will expect a few standard elements of the testing setup. Note that the specific tests which run — whether dbt commands, pytest, or something else — are ultimately up to the package maintainer.

Requirements for package maintainers

1. Tests will live in /integration_tests
2. Supported adapters will be defined in supported_adapters.env in the project root.

Example

The variable will be called SUPPORTED_ADAPTERS and the supported adapters will be listed after it, comma separated. Note that we will only support adapters running in dbt Cloud.

SUPPORTED_ADAPTERS=postgres,snowflake,redshift,bigquery

3. tox is used for running tests
4. Each adapter test will be defined in a specific way in the tox.ini file.

Example

The naming pattern is testenv:dbt_integration_<ADAPTER>.

    [testenv:dbt_integration_postgres]
    changedir = integration_tests
    allowlist_externals = 
        dbt
    skip_install = true
    commands =
        dbt --version
        dbt debug --target postgres
        dbt deps --target postgres
        dbt build --target postgres --full-refresh

5. The profiles.yml used for integration tests will use specific naming conventions for the target, as well as environment variables, so that we can fill the values in our testing process.

Example

    integration_tests:
      target: postgres
      outputs:
        postgres:
          type: postgres
          host: "{{ env_var('POSTGRES_HOST') }}"
          user: "{{ env_var('POSTGRES_USER') }}"
          pass: "{{ env_var('DBT_ENV_SECRET_POSTGRES_PASS') }}"
          port: "{{ env_var('POSTGRES_PORT') | as_number }}"
          dbname: "{{ env_var('POSTGRES_DATABASE') }}"
          schema: "{{ env_var('POSTGRES_SCHEMA') }}"
          threads: 5

Examples

We've already implemented this setup for dbt-utils and are running these tests regularly. To see the setup in action, check out the merged PR #919.

For specific requirements, explore our sample package set up for all supported adapters: dbt-package-testing.

Good to Know

We recommend running your own CI using these same tox commands. This way, you can monitor that changes will pass reliably in your CI to be more confident they will also pass in our CI.

[emmyoop]

dbt Labs has opened issues in all relevant package repositories.

package	issue	pr
dbt_utils	dbt-labs/dbt-utils#948	✔️ dbt-labs/dbt-utils#919
audit_helper	dbt-labs/dbt-audit-helper#109	✔️dbt-labs/dbt-audit-helper#111
codegen	dbt-labs/dbt-codegen#183	✔️ dbt-labs/dbt-codegen#181
dbt_external_tables	dbt-labs/dbt-external-tables#316	✔️dbt-labs/dbt-external-tables#320
dbt_project_evaluator	dbt-labs/dbt-project-evaluator#491	✔️ dbt-labs/dbt-project-evaluator#512
automate_dv	Datavault-UK/automate-dv#248
dbt_artifacts	brooklyn-data/dbt_artifacts#443	brooklyn-data/dbt_artifacts#451
dbt_date	https://github.com/calogica/dbt-date/issues/131	calogica/dbt-date#132
godatadriven/dbt-date
dbt_expectations	https://github.com/calogica/dbt-expectations/issues/318	calogica/dbt-expectations#319
dbt_expectations	metaplane/dbt-expectations#9	✔️ metaplane/dbt-expectations#8
tsql_utils	dbt-msft/tsql-utils#105
elementary	elementary-data/elementary#1700	elementary-data/elementary#1721
fivetran_utils	fivetran/dbt_fivetran_utils#149
dbt_snowflake_query_tags	get-select/dbt-snowflake-query-tags#32	get-select/dbt-snowflake-query-tags#33
dbt_meta_testing	tnightengale/dbt-meta-testing#31

[algarbar]

@emmyoop - This is awesome! Please consider adding dbt_assertions.

(@AxelThevenot for visibility)

[ammarsaf]

Is there any of this package that has something that can test the data type?

[pgoslatara]

@emmyoop Calogica's dbt-date package is no longer supported (similar to their dbt-expectations package). At Xebia Data we are partners with dbt Labs and are willing to maintain this package via godatadriven/dbt-date and have opened a PR to add this to the dbt Hub.

Can you update your list to reflect this? I'm happy to be the point of contact if you have any questions on this.

[emmyoop]

I've updated our testing infra to include this ownership change. In order to be included in the testing you'll need to implement some changes similar to what we had proposed for the calogica version: calogica/dbt-expectations#319

Once you've added this testing pattern and released it, it will get pulled in to our testing processes automatically.

[pgoslatara]

@emmyoop Done in 0.12.0. Let me know if there's anything else I should do.

Do I have any visibility on what testing processes will be run?

[emmyoop]

@pgoslatara our testing resides in an internal repository so you cannot see it. However, I have modeled the recommend CI you use, run_tox.yml to mirror what we're doing for a single package.

Here's the basic internal flow we're using, if you're curious:

1. Build an updated package of core + adapters
2. Download each pinned package listed above from the hub. The version pins are moved once per month. I'm moving them today in fact!
3. Check if the package is set up as expected with tox, supported_adapters.env etc, as documented above
   a. If it's not set up, I do not error, I simply move on to the next package
   b. If it is set up correctly, I will them use the tox commands you have defined and run the tests against all adapters you define as supported against our new core + adapters package

Upon success of all packages (along with many other tests) we will release the new image for dbt Cloud consumption

If there is a failure in any of the package tests it will block the release of the updated image and we will look into what the issue is. If we determine it's the result of a change on our end, we will resolve it. If it is the result of a change on your end we will reach out.

Hope that helps!

[pgoslatara]

That's really helpful, thanks @emmyoop! I have been looking at the dbt-package-testing repo as I built more CI into dbt-date, there are a few rough edges that I will try to open PRs against so that others can benefit.