Unit Testing

[MichelleArk]

Background

Since the original unit testing discussion was opened, community members have created several packages with their own frameworks for unit testing dbt models. Others have built proprietary solutions on top of dbt. The call is coming from inside Coalesce: unit testing ought to be native functionality.

Unit testing in dbt would provide the ability to check your modeling logic on a small set of static inputs (instead of your full production data) - a low-cost method to ship changes with confidence. Need to refactor an important, mature, heavily-used model? Unit testing is imperative for data teams — whether small or gigantic! — to (a) optimize warehouse spend/performance and (b) increase data quality at a fraction of the cost.

A well-designed unit testing framework can also enable test-driven development, with benefits for iteration speed & quality. Finally, unit testing is a natural thematic extension of our work this year around multi-project collaboration and model governance — they all come together to create stable and reliable interfaces for cross-team collaboration at scale.

I won’t bury the lede further than that - we’re going to start tackling this problem in the next month. The rest of this post overviews a proposal that we’d love to get feedback on from all the testing enthusiasts who have participated in this discussion, and in the broader discussions about unit testing in the modern data stack, over the past few years.

Proposal

Let’s walk through an example (based on a true story!) with a minimal SQL model called my_model that evaluates for each user_id whether they have a valid email address:

select
  users.user_id,
  users.email,
  case
    when regexp_like(users.email,'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') = true
      and accepted_email_domains.tld is not null
        then true
     else false
  end as is_valid_email_address
from {{ ref('users')}} users
left join {{ ref('top_level_domains') }} accepted_email_domains
on users.email_top_level_domain = lower(accepted_email_domains.tld)

The proposed interface for writing a unit test is a data structure, represented in yaml. I can define a corresponding unit test under models/unit_tests.yml to ensure all of my edge cases are captured - emails without ‘.’, emails without ‘@’, emails from invalid domains.

unit:
  - model: my_model # name of the model I'm unit testing
    tests:
     - name: test_is_valid_email_address # this is the unique name of the test
       given: # optional: list of inputs to provide as fixtures
        - input: ref('users')
          rows:
           - {user_id: 1, email: cool@example.com,     email_top_level_domain: example.com}
           - {user_id: 2, email: cool@unknown.com,     email_top_level_domain: unknown.com}
           - {user_id: 3, email: badgmail.com,         email_top_level_domain: gmail.com}
           - {user_id: 4, email: missingdot@gmailcom,  email_top_level_domain: gmail.com}
         - input: ref('top_level_domains')
           rows:
            - {tld: example.com}
            - {tld: gmail.com}
       expect: # required: the expected output given the inputs above
        - {user_id: 1, is_valid_email_address: true}
        - {user_id: 2, is_valid_email_address: false}
        - {user_id: 3, is_valid_email_address: false}
        - {user_id: 4, is_valid_email_address: false}
    - name: test_another_column
      ...

The above example illustrates defining input fixtures inline, and the options to specify reusable input fixtures or configure input formats are described more completely in the product requirements section below.

Running a new dbt command, dbt unit provides the following output:

❯ dbt unit
14:16:34  Running with dbt=1.6.0-rc1
14:16:35  Found 12 models, 0 tests, 2 unit tests, 0 snapshots, 0 analyses, 511 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics, 0 groups
14:16:35  
14:16:35  Concurrency: 5 threads (target='default')
14:16:35  
14:16:35  1 of 2 START unit test my_model__test_is_valid_email_address ................ [RUN]
14:16:35  2 of 2 START unit test my_model__test_another_column ........................ [RUN]
14:16:35  1 of 2 FAIL my_model__test_is_valid_email_address ........................... [ERROR in 0.24s]

Found 1 differing row between actual and expected outputs for my_model:
Actual
- {'user_id': 4, 'is_valid_email_address': true}
                                           ^
Expected
+ {'user_id': 4, 'is_valid_email_address': false}

14:16:35  2 of 2 PASS test_my_model__test_another_column .............................. [ERROR in 0.25s]
14:16:35  
14:16:35  Finished running 2 unit tests in 0 hours 0 minutes and 0.29 seconds (0.29s).
14:16:35  
14:16:35  Completed successfully
14:16:35  
14:16:35  Done. PASS=1 WARN=0 ERROR=1 SKIP=0 TOTAL=2

Uh oh! It looks like our clever regex statement wasn’t as clever as we thought - our model is incorrectly flagging missingdot@gmailcom as a valid email address.

Updating it to '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$' and running dbt unit again does the trick:

> dbt unit
14:16:34  Running with dbt=1.6.0-rc1
14:16:35  Found 12 models, 0 tests, 2 unit tests, 0 snapshots, 0 analyses, 511 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics, 0 groups
14:16:35  
14:16:35  Concurrency: 5 threads (target='default')
14:16:35  
14:16:35  1 of 2 START unit test my_model__test_is_valid_email_address ................ [RUN]
14:16:35  2 of 2 START unit test my_model__test_another_column ........................ [RUN]
14:16:35  1 of 2 PASS my_model__test_my_model ......................................... [PASS in 0.24s]
14:16:35  2 of 2 PASS test_my_model_another_test ...................................... [PASS in 0.25s]
14:16:35  
14:16:35  Finished running 2 unit tests in 0 hours 0 minutes and 0.29 seconds (0.29s).
14:16:35  
14:16:35  Completed successfully
14:16:35  
14:16:35  Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2

Product Requirements

🚀 Features of the proposed spec that would encompass an MVP [P0s]:

• New dbt command - dbt unit - that accepts an optional --select method and runs any associated unit tests.
  • Each unit test is a run of the model being tested, with any ref or source macros resolving to mocked input data as opposed to production datasets.
  • Unit tests are independent from one another, and so can be run in any order, across as many threads as available.
  • It should also be possible to run a single unit test without running all unit tests defined on a model.
• No need to explicitly specify all columns in the input or expected fixtures - only the ones that relevant for the test case. Same thing goes for inputs not relevant to a test case. This enables writing succinct and specific unit tests.
• No need to specify data types in mocked values. This keeps unit tests succinct and portable across adapters, even if the underlying model SQL is not.
• The ability to configure overrides of macros, vars, or environment variables for a given unit test. This is something that’s really tricky to do as a package or dbt wrapper implementation without direct access to dbt’s jinja context(s)… which dbt-core naturally has access to!

-- models/any_name.yml
unit:
  - model: my_model
    tests:
     - name: test_my_model
        expect: []
        overrides: # optional: configuration for the dbt execution environment
        macros:
          is_incremental: false
          dbt_utils.current_timestamp: "2020-02-01"
          # these non-macro jinja context properties should also quietly work
          run_started_at: 123
          invocation_id: abc123
       vars:
         KEY: VALUE
         env_vars:
           KEY: VALUE

• An equality assertion with super clear error messaging.
• Configurable format support for inputs (instead of a list of dictionaries). For example:

unit: 
 - model: my_model
   tests: 
   - name: test_name
     given: 
     - input: ref('my_model_a')
       format: csv
       rows: """
       id, b
       1,  2
       """
   - name: another_test_name
     given: 
     - input: ref('my_moel_a')
       format: csv_file
       rows: tests/fixtures/my_model/another_test_name/my_model_a.csv

⭐️ Features that would make exciting follow-on work [P1s] would be:

• Inlined fixture reuse, which could look something like:

fixtures:
 - name: my_model_a_with_nulls
   rows: 
    - {id: 1, b: 2}
    - {id: null, b: null}
unit: 
 - model: my_model
   tests: 
    - name: test_name
      given: 
      - input: ref('my_model_a')
        fixture: my_model_a_with_nulls

• Parallelism beyond the --threads argument. We could consider --groups <n> and --split <n> arguments that would run a particular split of the unit test collection given a total number of groups, similar to functionality the pytest-split plugin provides
• Syntactic sugar for overriding this
  • It could be under macros or a special-case of input so that it is overridable as a fixture similar to an input even though it typically returns a Relation
• Provide a simpler interface for generating the yaml for one unit test. While we like it as a clear way to represent the data structure of a unit test’s “recipe”, verbose yaml isn’t fun to write by hand — so we’re thinking of ways we can make this experience better, with some mix of codegen and/or the dbt Cloud IDE.
• Unit testing Python models

🚫 A handful of ‘anti-goals’, or features not likely to be prioritized for the first cut of this framework would be:

• Unit testing seeds, snapshots, analyses, or macros directly (they can be tested by making a passthrough model though!)
• Unit testing the outputs of CTEs
  • This is arguably akin to testing private methods - which is a debatable practice in software engineering. Sometimes it helps us sleep at night, but it’s structurally sensitive and is brittle to implementation changes.
  • If it’s really complicated and critical enough to warrant a test - it should probably be refactored into a separate (private, ephemeral) model so that it can be documented, reviewed, and tested independently.
• Unit testing the inputs/outputs of MetricFlow queries
• Assertion that a particular jinja execution error is raised given specific inputs (similar to what pytest.raises provides)

Next steps, and how to get involved!

There are many many implementation considerations and tradeoffs that I’ll leave open for @gshank to run point on as the technical lead for this project over in the epic. Additionally, @graciegoheen and @dbeatty10 will be offering their leadership and support from the product and developer experience perspectives for this initiative.

Please feel free to chime into this discussion with any related thoughts, considerations, and suggestions. We’ll also be aiming to release beta milestones for this functionality, and early beta testers and feedback is always welcome!

To get the ball rolling, here are some open questions we’ve been noodling on so far:

• What would you imagine best practices for unit testing data models to look like?
  • Potentially: Aim for one column in the expected output, to reduce the scope of the unit test and make for a clearer error message when tests fail.
    • Then you only need to define the input columns that are actually relevant to the calculation of that column (also including any required join keys & filter columns). The resulting column is the unit!
  • A unit test should test the logic, not the framework (e.g. dbt materializations or query engines). No need to test that BigQuery joins or limits work as expected :)
• What are your observations, reflections, suggestions on the proposed spec above?
• What related tooling could you imagine building on top of a native unit testing framework in dbt?
• Are there other input format s you’d expect to see in dbt-core?
• Where would you define these unit tests? In your models/ directory? In your tests/ directory? Somewhere else?
• What would your ideal error message look like if a unit test failed?
• How would you author the input data sets? Write them directly in YAML? Or use something else as an intermediate (like Google Sheets or Excel)?

[MichelleArk]

Additional motivation & design goals

Motivation & Design Goals

When writing unit tests for software development, I always find myself going back to Kent Beck’s Test Desiderata, which describes a set of ‘desirable properties’ for unit tests - all of which come together to produce a sense of confidence that our efforts are progressing, that the whole system is working. How might these properties guide the design a unit testing framework in dbt? While all twelve properties are relevant to this problem, there are a handful that feel most relevant and (and/or challenging!) to me when applied to unit testing data models. My notes as bullets, but Kent says it best:

• Writable — Good interface design makes writing unit tests easier. Alternatively, difficult-to-write unit tests are the canary in the bad interface coal mine.
  • This really feels like the crux of the UX challenge for unit testing dbt models. Any way the framework can minimize boilerplate, redundant configuration, or an excess of files is a win on this front.
  • Something I’ve observed in practice is that when tests are difficult to write, the typical response is to jam more test cases in a single test. When bloated tests like this fail - it’s very difficult to diagnose and fix the breaking cases.
• Readable — It can be challenge to write readable unit tests, because you are seeing so little context compared to the whole system.
  • Input fixtures splayed across multiple .csv files (as initially implemented as a v0 at Shopify) are difficult to review or debug. That said, input fixtures in analytics engineering are representations of potentially wide tables and the spec should definitely take this into consideration and allow for some flexibility.
• Specific — If a unit test fails, the code of interest should be short.
• Structure-insensitive — This can be a challenge for unit tests. Too much mocking, especially strict mocking, is a structure sensitivity nightmare.
  • Using a different engine from production is effectively mocking - there’s a tradeoff here between performance & cost vs accuracy.
• Fast — Yep.
  • There is inherent latency associated with making requests to a remote data platform. In addition to optimizing the performance of the framework where possible (caching, early detection of errors), providing the ability for users to run tests selectively and in parallel groups would very helpful!

[tommyh]

I am extremely excited this will be part of dbt-core! A few random thoughts:

1. For the P1 of "Inlined fixture reuse", I'm curious if a fixture should be a "set of rows in a table", or "a row in a table"?

For example, in Ruby on Rails fixtures, if you have a table called websites, then you define the "fixtures" for that table in a file called /test/fixtures/web_sites.yml, where each "fixture" is a row:

rubyonrails:
  id: 1
  name: Ruby on Rails
  url: http://www.rubyonrails.org

google:
  id: 2
  name: Google
  url: http://www.google.com

This is very powerful, because it allows you to have a "name" for different types of rows. For example, for stg_users.yml, you can have a "regular_user" fixture, vs an "admin_user" fixture, etc etc. In the same way that the dbt semantic layer gives codified definitions to metrics, defining fixtures at the row level (instead of the table/ref level), you can create codified examples of types of rows.

Note: The exact same idea is seen in different "factory" libraries like rosie or factory_bot, the main difference being factories have a different way of implementing the actual data, but they are at the same level of abstraction as fixtures.

The other reason I'm rambling about this, it would allow things to be much more composable/dry. Here is what I think it could look like:

fixtures:
 - name: complete
   data:
     id: 1
     b: 2
 - name: incomplete
   data:
     id: 2
     b: null
unit: 
 - model: my_model
   tests: 
    - name: formats_the_b_column_correctly
      given: 
        - input: ref('my_model_a')
          fixtures: [complete]
      expect:
        - {id: 1, b_formatted: "2_lol"}
    - name: excludes_rows_where_b_is_null
      given: 
        - input: ref('my_model_a')
          fixtures: [complete, incomplete]
      expect:
        - {id: 1, b_formatted: "2_lol"}

The P2 of that would be "overrides of inline fixtures". for example:

      given: 
        - input: ref('my_model_a')
          fixtures: [complete({b: 3})]

2. I think it's amazing that the expect statement does NOT need to specify all columns - this is huge (very similar to rspec's hash_including). From your proposal, it appears that the expect statement DOES need to specify each row and in order. While I definitely think this is a sensible default, especially for stg/simple models, I think it might become a bit brittle for complex/dim models, where the number of rows you will have to specify will grow (therby incentiving less tests) and it will be harder to interpret (I'm trying to test an invalid row is filtered out, but it's hard to see that). Some random ways to solve that as a P3, would be expect_any_of which says "these rows should be there, but i don't care if others are". or expect_nothing which says {id: null} should not appear as a row.

3. One implementation question - when a given overrides a ref('my_model_a'), whether it be fixture/csv/etc, how is dbt doing that? Is dbt actually loading that data into the actual my_model_a table? Or is dbt doing some trickery with a CTE and VALUES? There are a few reasons I'm asking this question (which I'm too tired to type out now), but it gets into the tradeoffs between accuracy and parallelism.

[zendesk-cisenbe]

A huge +1 on expect_nothing. With existing packages, I've worked around this by including a "good" input and alongside inputs that should be filtered out in my unit test, and then I have a single row as an expectation, but that feels extraneous with a full-featured solution.

[graciegoheen]

Thanks for the follow up!

I'm curious if a fixture should be a "set of rows in a table", or "a row in a table"?

Broadly, we're thinking about fixtures as a "set of row(s) in a table" - where you could always specify multiple rows or a single row as a fixture. A potential future enhancement could be to union together multiple fixtures as you've described.

It sounds like there's an appetite for more ways to determine if a unit test is "successful" or not - similar to how we have a fail_calc config for tests today. I expect that we will keep our p0 as an equality check, but we should definitely consider adding support for other definitions of success (such as expect nothing or expect_any_of). Technically, "expect nothing" will be possible with the equality test by just setting the expected output to an empty dictionary.

[MichelleArk]

From your proposal, it appears that the expect statement DOES need to specify each row and in order.

Agree that this could lead to brittle and potentially even flaky (in the case of ordering) unit tests! While some data warehouses (or models) may impose an order to a relation, by definition these are really unordered sets of records and I'd expect the sensible default would actually be to assert equality (via expect) without enforcing order to avoid baking in the potentially incorrect assumption of consistent ordering by default. I'd imagine a separate expectation type, similar to what @tommyh proposed above with expect_nothing or expect_any_of for this purpose -- perhaps something like expect_ordered.

[MichelleArk]

Is dbt actually loading that data into the actual my_model_a table? Or is dbt doing some trickery with a CTE and VALUES?

Good question. Those are the two main implementation approaches I see here as well. I think both are technically feasible and would actually have pretty similar implementations under the hood: either using a materialization that leverages existing ephemeral logic for the 'CTE trickery' route, or actually materializing inputs and the 'actual' test model in the warehouse using the existing seed materialization.

Actually materializing the input/actual datasets is a more accurate representation of how the models are run in production in comparison to the CTE-based approach, and would support a larger set of SQL/dbt functionality than CTEs. For example, syntax that is used sql_headers that may not be valid in a standalone query, or certain types in that can be inserted but not actually declared in a standalone query (dbt-labs/dbt-project-evaluator#290). Do any other limitations come to mind? The tradeoff being performance: actually materializing fixtures/expected/actual in the warehouse, querying them to obtain a diff, and deleting them reliably at the end of the test run all add up to additional latency.

So far we've started with the CTE approach, mostly for sake of simplicity, but I do believe it'd be very worthwhile to spike the seed-based approach and quantify more precisely how much slower/complex that approach would be. @gshank also suggested exploring implementing both strategies and either selecting the strategy based on user configuration or the presence of certain conditions (e.g. a sql_header, or particular type on the model being tested). I think a non-CTE stategy would also be necessary to test complex or custom materializations end-to-end (#8275 (reply in thread)) . Captured this exploration in a spike here: #8499

[mike-weinberg]

thank you @jtcohen6 for pointing me here!

@MichelleArk if you create a view, the history of the existence of that view would be stored in the catalog. on one hand that has tons of benefits.

on the other hand, if the entire northeast region suddenly started creating a large number of views at a very fast rate, it might materially impact the snowflake global services layer and its analogues for redshift and bigquery by hammering the metadata catalog databases with many small writes.

I would recommend doing some back of the envelope math using user telemetry from dbt cloud to take a SWAG at how many unit tests might be run per day globally (probs safest to guess a huge range and take a geometric mean), and then contact snowflake to see if they think that would be problematic.

That might seem like it is not your problem, but I'm willing to bet that users will blame dbt labs for perceptions of unit test slowness even if it due to the capabilities of the underlying datastores, and you'd only notice it after adoption picks up, beacuse adoption picks up.

[JCZuurmond]

@MichelleArk : Awesome write up! I am looking forward to this feature ☺️

Right off the bat, the following comes to mind:

1. To what extend can we use jinja and macro's within the YAMLs to generate test cases? Similar to pytest parametrize.
2. Can we drop into a pdb debugger on an error or when a test fails? And what would the context be? Similar to pytest --pdb flag. For example, if we expected a certain value but we received another value, I would like to view that value - and possibly the values of other columns too.
3. Can we also test for what not to expect? This would be especially helpful for regression test and test driven development:
   i. Add test for value in production that was unexpected
   ii. Show test fails
   iii. Implement solution
   iv. Show test passes
4. Will tags work on unit tests? If so, can it be used with the --select for unit tests? Similar to the pytest marks
5. How is the dbt project compiled when running dbt unit? Is the whole project compiled or only the relevant model? The later would speed up unit tests for big projects.

[graciegoheen]

To answer a few of these:

3. I responded a bit to this here - we should definitely consider other definitions a "successful" unit test (though we will likely stick with an equality check for the initial release). To confirm, you're imagining adding a unit test with an "expected output" and then configuring that test to fail when that output is returned?
4. Great callout! Yes, we should support tags on test so that you can run them via --select

re: your ask about pdb debugger - is there a specific value you would expect to view when a unit test fails? I think it's unlikely we're able to support dropping into a pdb debugger, but want to make sure the error message we return is as helpful as possible for debugging.

[MichelleArk]

These are great callouts :) I'll take a stab at the outstanding ones!

To what extend can we use jinja and macro's within the YAMLs to generate test cases? Similar to pytest parametrize.

I think there's an opportunity for dbt to provide a standard spec for parametrization instead leaning entirely on jinja + macros, which would introduce lots of flexibility for test writers but typically at the cost of readability.

In the current proposal, I could imagine models/tests that lend themselves well to parametrization could be expressed as a series of corresponding input and expect rows - each representing a test case. This is limiting though, because it requires 1:1 relationships between input rows and output rows, and technically this is the same as creating one big unit test which can become difficult to debug. So in terms of requirements, I'd imagine (similar to pytest parametrize): we'd want the ability to: (1) specify 'groups' of inputs/outputs as a test case, as opposed to individual rows, (2) add comments to each test case, and (3) run each parametrized test case in isolation. It'd be helpful to see more concrete examples of duplicative unit tests on dbt models in the wild before we attempt to nail down a design for parametrization, but I think it'd be a great extension to the framework after the first release.

How is the dbt project compiled when running dbt unit? Is the whole project compiled or only the relevant model? The later would speed up unit tests for big projects.

@gshank has actively been exploring this in #8295! The jist so far is: In order to support --select-ion over the dbt project (to power neat functionality like selecting tests only for changed models, or based on lineage, tags, etc), we need to (partial) parse the dbt project in its entirety to apply selection, and then only compile a 'unit test manifest' for the selected unit tests.

[gregmuellegger]

To what extend can we use jinja and macro's within the YAMLs to generate test cases? Similar to pytest parametrize.

Maybe beyond that I would like to add the request for hooks so that plugins could add methods for creating inputs and expected output in a more advanced way.

There are two usecases for such hooks that directly pop into my head:

1. test data creators like faker
   They would allow folks to use realistic, but semi-random values for their tests so that you don't end up naming all customers in every test John Doe.

2. Property-based testing. In Python hypothesis is an example framework for this. (Here is a no-prior-knowledge-required intro into property based testing)

   The gist is you test your code on particular properties instead of particular values. So instead of adding individual numbers to a column, one would specify a strategy to create possible numbers like "positive numbers". Something like hypothesis would then generate a set of numbers that match the property. The expected output again would also be not a specific number but a property you validate on.

   As an example: If you calculate the sum of a column in your model, and all values in the table are positive (property of input values) then you would expect that the sum is also positive (property of the expected values)

   Using hypothesis in Python, this would look like:

   @given(st.lists(st.integers().filter(lambda x: x >= 0)))
   def test_sum_is_positive(list_of_numbers):
       assert sum(list_of_numbers) >= 0

   The benefit is that the strategy intelligently produces values, including really large ones, edge cases like 0, providing an empty list, etc. All on its own and testing one after the other.

   For strings it would test all kind of possibly failing cases like only spaces, empty strings, unicode characters and all the things that one usually doesn't think of while designing simple test cases.

I'm not sure how this could be resembled in the proposed YAML configuration, but I wanted to emphasis that there is real use in extension mechanisms especially for creating input values dynamically as well as logic to check properties of expected values instead of specific values.

[dbeatty10]

💡💡

[Zac-HD]

👋 Hypothesis maintainer here - I'd be happy to chat about this sometime if you're interested; I think it'd be valuable for your users but there are some subtlties in getting a good integration.

[Zatte]

Great initiative, really looking forward to getting this into core!

1. Are there other input format s you’d expect to see in dbt-core?

Would love to see sql which works like a dbt model (aka supports {{ ref('...') }} and other macros). This would allow one to use the expressiveness of SQL and provide quick access to seeds and other production/staging/shared/sampled-data.

2. expect

Could be more expressive. E.g. of other assertions or interpretations; either way i think it needs some clarification in reagards to ordering.

• "[does not] contains rows [in order]"
• "[does not] match the empty set or rows"

3. Incremental materialization testing.

Getting incremental logic right is complex and would love to cover in tests. Not sure it is best solved with unit tests since it requires multiple stages (partial full refresh + [[partial] incremental refresh, ....]...) == full refresh. It would be nice if it could be solved within the scope of unit tests but I don't see how i could with this proposal due to the lack of multi stage runs and assertions.

[MichelleArk]

Getting incremental logic right is complex and would love to cover in tests.

Agree, this is super motivating. Testing the initial/full-refresh run of the model should be doable with what's proposed above. The tricky piece (as you've pointed out!) is actually testing subsequent/incremental runs because they require an initial stage to create an existing model to update.

To support this, dbt's unit testing framework would need to provide the ability to override the is_incremental macro to return True (p0), and provide the ability to specify an input fixture for {{ this }} (p1) in order to mock out the previous state of an incremental model. The expect output would be the set of rows that dbt's incremental materialization would insert/merge into the existing relation in production, as opposed to the resulting updated representation of the model. This may be counterintuitive but is inline with the design goal of of building a framework that makes it easy to 'test the logic, not the framework (e.g. dbt materializations)'.

That said, it would be powerful to provide the capability to test (incremental) materializations end-to-end, especially for custom materializations. I see @gshank has captured much of this in the implementation epic issue here: #8422 🚀

[MichelleArk]

Would love to see sql which works like a dbt model (aka supports {{ ref('...') }} and other macros). This would allow one to use the expressiveness of SQL and provide quick access to seeds and other production/staging/shared/sampled-data.

Hmm, my concern here would be that a unit test would then be able to depend on production data. The implication being that the test can change or break if production data that it's ref-ing starts to change. Ideally, unit tests only break in response to changes made to the logic in development.

This would allow one to use the expressiveness of SQL and provide quick access to seeds...

Access to seeds seems totally valid though, as those are defined by the project and should only change during development. Perhaps what would desirable would be to use a seed's .csv contents as a default input fixture, unless an explicit version is specified for a particular test case. That way, testing a models that depend on a seed won't need require duplicating the seed's .csv contents and input fixture representations just for unit testing purposes.

... and other production/staging/shared/sampled-data.

I believe @graciegoheen has some further thoughts on this :)

[darist]

Fantastic! Looking forward to it! Thanks for prioritizing this effort.

‘anti-goals’, or features not likely to be prioritized ... Unit testing .. macros directly (they can be tested by making a passthrough model though!)

Can you provide an example of how you'd envision the "pass through model" approach?

I'd love to see the need for testing macros get move attention. Might you consider promoting it to the "follow-on" list?

I find myself writing unit tests for macros quite a bit and these are the sort of things see:

• Macros can return something other than strings (eg: a number, a dict, a list). There's a need to be able to compare against expected results of different data types.
• Macros call other macros. There's a need to be able to mock return values of other macros (including built-in functions). There's a need to be able to spy on calls to other macros (eg: to assert that macro_a called macro_b with the expected parameters).
• Macros should validate their inputs and raise exceptions. There's a need to assert that an exception is raised given invalid inputs.
• Often you want to write many tests for one macro (or a few macros) in a single test file.
• Sometimes you don't care about the entire string return value being equal, you just want to make sure it contains something.
• Sometimes you care that the output of a macro is readable (eg: a macro that outputs a full SQL query). Test goldens may be a good option for this. They help make sure that the output stays the same, with the same indentation, etc. Test goldens are famously noisy. A small change can cause many goldens to be updated. There's a need for tooling to easily compare and re-generate test goldens.
• Macros rarely need to talk to the database directly, so macro tests can be run hermetically against a local db instance or a fake db adapter. They can be really fast!

Cheers!

[MichelleArk]

Can you provide an example of how you'd envision the "pass through model" approach?

The type of macros we’re envisioning being able to test with this framework are macros purely used for sql templating. As a simple example, let's say we wanted to test this templating cents_to_dollars macro:

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name, scale=2) %}
    ({{ column_name }} / 100)::numeric(16, {{ scale }})
{% endmacro %}

-- models/test_cents_to_dollars.sql
{{ config(materialized='ephemeral') }}

select 
{{ cents_to_dollars('amount') }} as amount_usd
from {{ ref('payments') }}

unit:
  - model: test_cents_to_dollars
    tests:
     - name: test_cents_to_dollars 
       given:
        - input: ref('payments')
          rows:
           - amount: 1
           - amount: 200
       expect:
        - amount_usd: 0.01
        - amount_usd: 2.00

Side node: writing this out explicitly, I'm realizing you'd either have to create a new payments model (or source) just to test the macro, which is not great. Either that, or use an existing model just for the purposes of having a model to ref against, which also doesn't feel great because it introduces unnecessary coupling. Perhaps the lesser evil would be to use a model that actually depends on the macro being tested in the test_<macro> model (which itself could be ephemeral/disabled to avoid cluttering up the warehouse)

Of course, macros are much more flexible and can be used for much more than just sql templating. You've outlined some great considerations above that highlight the differences in requirements for a framework that tests macros vs models. To support both, the proposed framework would need to be generalized, and support features that are not necessary to unit test dbt models sufficiently (e.g. testing return types other than relations, asserting that exceptions were raised).

I imagine some of the requirements for testing macros would be reasonable extensions of the model unit testing framework as proposed (e.g. more variety in assertions), but ultimately the use case of testing macros would probably be best served by separate spec, outside the scope of this initiative. Super simple example, but perhaps something like:

-- macros/add.sql
{% macro div(x, y) %}
  {% if y == 0 %}
    {{ exceptions.raise_compiler_error("undefined - cannot divide by 0") }}
  {% endif %}
  {{ return( x / y) }}
{% endmacro %}

unit:
  - macro: div
    tests:
      - name: test_div
        given:
          x: 2
          y: 1
        expect: 2
      - name: test_div_by_zero
        given:
          x: 2
          y: 0
        expect_raise_compiler_error: undefined - cannot divide by 0

cc @graciegoheen @dbeatty10 @graciegoheen, if you have any additional thoughts on testing macros!

[jonathanneo]

Hi @MichelleArk / @graciegoheen / @dbeatty10 would you also consider adding tests for macros that affect the behaviour of dbt? For example, generate_custom_schema or generate_alias_name macros that change the fully qualified object name of a dbt model? We rely heavily on these macros and have a lot of custom logic that we would like to be able to test.

[LudovicGayet]

Really nice to see this feature coming !!!

My use case

In one word : End to End pipeline testing

let's say i have a pretty straighforward pipeline : source -> base__ -> ref__ -> fact__ with some ephemeral model

I would like to be able to test the whole pipeline by just defining the input of the source and the expected fact__.
I guess it will be possible with what your offer using some jinja to switch all materialization as ephemeral.

Some answers to your questions :

What related tooling could you imagine building on top of a native unit testing framework in dbt?

I like the idea of the package dbt-coverage. It would be nice to quickly identify which model has test on column, unit_test...

Are there other input format s you’d expect to see in dbt-core?

I was planning to use acsv input

Where would you define these unit tests? In your models/ directory? In your tests/ directory? Somewhere else?

It really depends on how easy i can identify which model has unit_test or not.
If i have a kindof dbt-coverage then i would probably store them in tests/.
If not i would like to have a my_model_unit_test.yml next to my_model so i can identify easily which model has unit_tests.

[graciegoheen]

End to End pipeline testing

The goal of unit testing in dbt would be to test a discrete piece of code logic (i.e. in the context of a single model). What you're talking about - testing changes across multiple models - is definitionally different than what we're calling "unit testing" here and likely wouldn't be supported under this framework.

That being said, there is pre-existing functionality that you could use for this type of end-to-end testing:

• you can configure your sources to point to a different location in your warehouse depending on your target / an environment variable:

sources:
  - name: jaffle_shop
    database: {% if target.name == 'test' %} sample_db {% else %} raw {% endif %}
    schema: "{{ env_var('SAMPLE_DATA_SCHEMA', 'jaffle_shop') }}"  
    tables:
      - name: orders
      - name: customers

• you can use seeds and equality test to confirm the output is as expected (this is how we do integration tests for many of our dbt packages)

[d-cole]

Super exciting!

Syntactic sugar for overriding this

This and overriding is_incremental are really important features for us as it unblocks proper testing of incremental models.

[benjamin-allevius]

Will arrays and arrays of structs (and further nesting) be supported?

[MichelleArk]

Definitely a goal of this work -- we're tracking this requirement in the epic here: #8423

[ZhouSu89]

@MichelleArk Looking forward to this new feature.
I've been implementing an in-house python package for unit testing dbt models. End users can specify unit tests as yaml inputs (similar to dbt test), and run the tests and view the results in command line. The mock data for the unit tests can be specified as inlilne csv or sql and from a file.
Based on my experience developing the package, and the feedback from end users, I can already share some thoughts regarding the requirements.

Core functionality

1. Testing incremental models

   • Ability to test incremental models in both full refresh and incremental mode
   • Ability to specify the current status of an incremental model to test the incremental behavior
   • Ability to test the incremental behavior of models using incremental strategy that requires partitioning (e.g. insert overwrite in BigQuery)

2. Handling complex data types

   • Ability to test models with complex data types (e.g. ARRAY and STRUCT in BigQuery), and specify mock data with complex data types

3. Compatibility with macros and variables

   • Ability to test models calling custom macros and macros from another dbt package
   • Ability to test models using dbt variables. Allowing users to specify the values of dbt variables in the unit test, and copying the default values from project.yml if no values are specified.

4. Result comparison

   • Ability to compare only a subset of columns of interests

5. Ability to test CTEs

   • Ability to extract a CTE and test it as a stand alone model. I understand why testing CTEs is among the ‘anti-goals’, but in practice this feature is highly demanded by the end users. Here is a quote from an end user of the package: "during testing, we noticed that some of our more complex models could really benefit from CTE testing. Of course we could split these up further in ephemeral models - but that would quickly lead to a very unwanted large increase in the number of models (in often already quite large dbt projects)"

Performance requirements

• Speed (<=10s for one test as a benchmark)
• Ability to run multiple tests in parallel (and specify the number of parallel tests)

UX requirements

• Ability to run a specific test (even for models with multiple tests)
• Using the same --select syntax in dbt to filter what tests to run
• Ability to control the log level in the console
• Ability to retain the test schemas for a specific test invocation for further investigation

[graciegoheen]

Ability to test models with complex data types (e.g. ARRAY and STRUCT in BigQuery), and specify mock data with complex data types

Hey, thanks for your thoughts! I'm curious what your acceptable list of supported "complex" data types would be?

[tommyh]

@MichelleArk - with respect to your forth product requirement, "configure overrides of macros, vars, or environment variables for a given unit test" which "dbt-core naturally has access to". While you are building that feature into dbt-core for usage by the dbt unit testing functionality, would it be possible to expose some of that from the dbt python module, so that other testing tools can utilize that?

I am thinking about building some dbt testing tools (similar but different from what you have laid out here), and these building blocks would be super helpful.

Let's imagine my_model.sql:

with
    users AS ( select user_id, email from {{ source('postgres', 'users') }} )
select *
from users

It would be amazing if I could do this (all pseudo code):

1. pass in the ref name

dbt.compile_model('my_model', {
  overrides: {
    sources: {
      postgres: { users: '"raw_test"."users"' },
    }
  }
})

which would return

with
    users AS ( select user_id, email from "raw_test"."users" )
select *
from users

2. pass in static values

dbt.compile_model('my_model', {
  overrides: {
    sources: {
      postgres: { users: "(VALUES (1, 'alice@example.com'), (2, 'bill@example.com'))" },
    }
  }
})

which would return

with
    users AS ( select user_id, email from (VALUES (1, 'alice@example.com'), (2, 'bill@example.com')) )
select *
from users

3. pass in is_incremental (assuming we add one to my_model):

dbt.compile_model('my_model', {
  overrides: {
    is_incremental: True,
    sources: {
      postgres: { users: '"raw_test"."users"' },
    }
  }
})

which would return

with
    users AS ( select user_id, email from "raw_test"."users" )
select *
from users
where created_at > (select max(created_at) from "raw_test"."users")

[paulbakkerbloom]

Awesome that you're working on this @MichelleArk! We're very much looking forward.

Together with @ZhouSu89 I've been involved in the development of our internal unit testing framework. I was more on the end-user side of it. So very happy to share some of the requirements we set or learnings we had during the way + some examples of our input.

Input:

• Our preferred method for input were CSVs from file. We added datatypes to that on a separate line (screenshot 1) to ensure a) the model (actual) was built without errors and 2) to ensure schema comparison for actual vs. expected mock. I saw in your post you're planning on not having specify data types, so I'm wondering you plan to tackle esp. point 1) - for us automatically inferring the schema often caused errors.
• As you can also see, we often added an extra column to add some extra information about a certain row - to make it more clear what we expected to happen to a certain row.

• If a mock contained nested datatypes, we used JSON.
• We specified our tests in models.yml files. As shown below. We could also add mock variables.
• Inline CSV and SQL were supported. But they made the .yml files very lengthy and kind of messy - so it didn't have our preference.

    config:
      meta:
        unit_tests:
          - name: unit_test_x
            mocks:
              input_mock: |
                path_to_input_mock
            expected: |
              path_to_expected_mock
            vars:
              start_date: z

• Since some of our models have many columns, it was important to support only specifying a subset of them in the mocks. Many columns simply just not undergo a transformation that's worthwhile to be tested. Supporting this makes writing the unit tests a lot less cumbersome. We already supported this for the expected mock, as shown below, but (logically) were also looking to add this for the input mocks.

    config:
      meta:
        unit_tests:
          ....
            test_config:
              expected_columns:
                [
                  x,
                  y,
                  z
                ]

• For incremental models, we can add a "current_actual" mock that allows us to define the current "state" of the model we're incrementing on, testing whether the incrementing works as expected.

models:
  - name: test_get_merge_sql__standard
    config:
      meta:
        unit_tests:
          - name: test_standard_merge
            description: |
              Test standard merge behaviour where existing records are updated and 
              new ones are inserted.
            mocks:
              source_mock_dataset__incremental_model_source: |
                INTEGER,DATE,INTEGER
                id,kafka_write_time,kafka_offset
                1,2021-01-01,1
                1,2021-01-02,2
                2,2021-01-01,1
                3,2021-01-02,1
            actual_current: |
              INTEGER,TIMESTAMP,INTEGER
              id,kafka_write_time,kafka_offset
              2,2021-01-01 00:00:00 UTC,1
              1,2021-01-01 00:00:00 UTC,1
            expected: |
              INTEGER,TIMESTAMP,INTEGER
              id,kafka_write_time,kafka_offset
              2,2021-01-01 00:00:00 UTC,1
              1,2021-01-02 00:00:00 UTC,2
              3,2021-01-02 00:00:00 UTC,1
            vars:
              data_interval_start: '2021-01-02'

In terms of output, here are some of the requirements we had:

• Clear error message indicating where actual and expected differ. The error message should be readable for 1) wide tables 2) columns of complex data types and 3) clearly indicate multiple differences (e.g. actual and expected differ in different rows and columns)
• In case an internal error arises because incorrect user input is provided (e.g. wrong field name in the yaml input or insufficient mock inputs is provided), the error message should clearly point to the root cause

[darkdreamingdan]

Would like to chime in and also strongly request CTE testing. Arguments of whitebox vs blackbox testing are age old - however there are very few languages that actively disempower the engineer from taking the decision. I understand trying to set the standards, but agree with previous points raised that breaking out CTEs into multiple models causes a different set of headaches.

Currently, we are testing our CTEs individually and it allows us to interrogate our code with more test cases where needed. Would strongly request that we have the ability to test CTEs in isolation.

[graciegoheen]

however there are very few languages that actively disempower the engineer from taking the decision

This is a very valid point - thank you (and everyone else) for explaining the important of unit testing CTEs for your use cases. I think we should take this out of our "anti-goals" and move it to a p1 requirement - issue linked here.

[wasabigeek]

This is really awesome to hear! Two questions come to mind:

• will dbt unit still require a connection to the database at all, given that dbt test still requires one? I'm hoping it won't be necessary, as this makes such unit tests a lot easier to run in CI for some connection types where we can't have a localised instance (e.g. BigQuery)
• will generic tests fit into this picture as well? At a glance, it doesn't seem so (although it won't be hard to introduce our own unit tests for those) On second thought, it probably doesn't make sense to mock data for some of the generic tests 🤔

[rachelwigell]

The UX you're proposing for testing is extraordinarily similar to the one we've rigged up in our Airflow repo for doing unit testing for data transforms, and we'd love to have similar functionality available in dbt.

We use Snowflake as our data warehouse and have encountered some special issues working with it in the test environment, so I wanted to share a tech blog we wrote about our experience in case it's helpful to your team: https://github.com/BookBub/public-docs/blob/main/snowflake_testing_tech_blog.md

[ssillaots-boku]

Hey!
Any updates when this will be released?

[graciegoheen]

Hi! We're working on this now, and it's currently planned to be released in 1.8

Check out the epic if you want to follow along :)

[paulbakkerbloom]

Hi! I was wondering if there are any plans to support, for input mocks, defining a subset of the columns used by the tested model? E.g. the model is

SELECT
  a,
  b,
  c
FROM t0

but for the t0 mock you only define:

SELECT
  CAST(1 AS INT64) AS a

This can come in handy when e.g. the tested model has many input columns but only transforms a few - it would avoid the tedious task of defining all those columns in the mocks while you don't really care about their value and they clutter the mock so it's less easy to see what the test is actually about.

[dbeatty10]

That's a great question about enabling writing specific unit tests that are succinct to maximize their readability and writability 🧠

Our intent and plan is that you can specify only the columns that are relevant for the test case. i.e., no need to specify all columns in the input or expected rows -- just the ones you need.

The most recent iteration of the spec we are imagining is here and looks like this:

unit-tests:
  - name: test_is_valid_email_address # this is the unique name of the test
    model: my_model # name of the model I'm unit testing
    given: # optional: list of inputs to provide as fixtures
      - input: ref('users')
        rows:
         - {user_id: 1, email: cool@example.com,     email_top_level_domain: example.com}
         - {user_id: 2, email: cool@unknown.com,     email_top_level_domain: unknown.com}
         - {user_id: 3, email: badgmail.com,         email_top_level_domain: gmail.com}
         - {user_id: 4, email: missingdot@gmailcom,  email_top_level_domain: gmail.com}
      - input: ref('top_level_domains')
        rows:
         - {tld: example.com}
         - {tld: gmail.com}
    expect: # required: the expected output given the inputs above
      - {user_id: 1, is_valid_email_address: true}
      - {user_id: 2, is_valid_email_address: false}
      - {user_id: 3, is_valid_email_address: false}
      - {user_id: 4, is_valid_email_address: false}
  - name: test_another_column
    ...

[paulbakkerbloom]

Awesome! So what will be done behind the scenes to the columns that are missing but are required by the model? E.g. are they replaced with NULLs?

[dbeatty10]

Yep, I think that's what we're doing right now.

[graciegoheen]

In case you missed it, we just published a new roadmap post highlighting our plan to deliver unit testing to y'all in dbt-core 1.8 (Spring 2024)! Thanks for all of your feedback thus far :)

[dbrtly]

Thank you. This is a sore spot right now. Testing against the real data doesn't cut it. Too slow/brittle/expensive.

If at all possible, I would strongly prefer to pass mocked inputs and expected outputs as json or select statements in addition to csv.

[graciegoheen]

Thanks for your feedback! Our initial spec provides 3 ways to define mocked inputs & expected outputs.

In-line dictionary:

unit-tests:
  - name: test_my_model
    model: my_model
    given:
      - input: ref('my_model_a')
        format: dict
	rows:
          - {id: 1, name: gerda}
          - {id: 2, b: michelle}    
...

In-line csv:

unit-tests:
  - name: test_my_model
    model: my_model
    given:
      - input: ref('my_model_a')
        format: csv
	rows: |
          id,name
          1,gerda
          2,michelle
...

Fixture csv file:

unit-tests:
  - name: test_my_model
    model: my_model
    given:
      - input: ref('my_model_a')
        format: csv
        fixture: my_model_a_fixture
...

# tests/fixtures/my_model_a_fixture.csv
1,gerda
2,michelle

Would this satisfy your use case? What are the advantages you see for json and/or select statements? Thanks!

[daniel-bartley]

In-line dictionary is close enough to what I imagined.

[Junobijlard]

Select statements are not easily readable so would try to avoid them. We need something else than CSV for complex types (e.g. nested table, repeated field). This might be caught in your dict implementation though. Will this be a python dict? If not, why not implement this as json? E.g. escape characters should follow some standard and then you quickly end up with json instead of a dict I guess. Op do 23 nov. 2023 om 18:31 schreef Grace Goheen ***@***.***>

…

Thanks for your feedback! Our initial spec provides 3 ways to define mocked inputs & expected outputs. In-line dictionary: unit-tests: - name: test_my_model model: my_model given: - input: ref('my_model_a') format: dict rows: - {id: 1, name: gerda} - {id: 2, b: michelle} ... In-line csv: unit-tests: - name: test_my_model model: my_model given: - input: ref('my_model_a') format: csv rows: | id,name 1,gerda 2,michelle ... Fixture csv files: unit-tests: - name: test_my_model model: my_model given: - input: ref('my_model_a') format: csv fixture: my_model_a_fixture ... # tests/fixtures/my_model_a_fixture.csv 1,gerda 2,michelle Would this satisfy your use case? What are the advantages you see for json and/or select statements? Thanks! — Reply to this email directly, view it on GitHub <#8275 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AEMALOFDDS5OISR6VJJWEC3YF6B5JAVCNFSM6AAAAAA3AOLPASVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM3TMNJUGAZDQ> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[graciegoheen]

Thanks for the feedback!

We have an open issue to handle more complex data types - starting with ARRAY and JSON, but would be curious if there's specific data types that are relevant to your use case.

[dgokeeffe]

In my experience, the most efficient way of creating realistic test data is to define that data using code with tools such as faker, dbldatagen, SDV, etc..

Static test cases quickly become unwieldly to handle, as you're always playing a cat and mouse game between what the changes in production are, and the test cases you need to replicate. Imagine a simple change rippling through 1000 test cases, you're not going to want to write another test again.

Code is always easier to change than data. Ideally the solution in dbt caters for this.

[graciegoheen]

In my experience, the most efficient way of creating realistic test data is to define that data using code with tools such as faker, dbldatagen, SDV, etc..

Thanks for the input!

I'm curious how you define the expected outputs in this case. I'm imagining you generate some random 1000 test cases for your inputs, but then do you go through and manually write out the expected outputs?

[dgokeeffe]

This is a bit tricky, but the test harness at runtime knows what the input is, so if you wanted a like for like comparison the only solution is to also do the transformation in your test harness to create the output (which is basically a form of double checking).

The alternative is to forgo doing exact comparisons and with the knowledge of what is being generated switch to do a softer style of test, i.e., check the schema or the ranges or whatever.

[dgokeeffe]

In practicality, it just struck me what I would really like actually @graciegoheen, and it's something that was floating about in the previous issue. I want a plugin or a pattern for using pytest or behave with dbt at the unit test level.

What this would require is some sort of way to call a model or a sequence of models to be created in dbt from these Python native test frameworks.

I think that is the solution which will give the most flexibility and also give dbt the ability to leverage all the features that are in these test frameworks. I think it's probably also the path of least resistance for implementation too.

[timvw]

To me this seems you're looking for a "property based testing" approach..
eg: https://fsharpforfunandprofit.com/pbt/

[jschintz]

Also excited to see this! Some opinions

Unit testing should only occur on contracted models.
We probably want to encourage DRY by limiting the spaces where unit testing occurs. By making unit tests on endpoint edges, over testing is minimalist, This makes sure rules aren't broken downstream.
There's already debate with this and CTEs. Unit tests should be so important they are tested across the dag. It would be cool to identify upstream models that break the unit test.

Unit testing should be linked to versions specs. We will constantly break these rules. Please make it easy for devs. Snowplow is a great example.

Should unit testing be enforced at the semantic layer?
I want to know my semantic layer works. How do I know all my upstream models have compatible unit tests?

When is all this yaml too much? New devs are going to be even more confused. This needs a really good package to get adopted by my team.

[smomen]

Hi friends -

@jtcohen6 pinged me about this a while ago after we discussed the state of testing in dbt. My claim which preceded that discussion was that dbt has successfully applied many of the successful paradigms of software engineering (e.g. source control; dependency management) to analytics/data, but has thus far lagged on perhaps the most important one that is critical for enterprise software - testing. Of course I hadn’t seen this rich discussion yet 🙂

Belated but just want to transfer some learnings after ~3 years of building/maintaining a homegrown test platform across a large data eng team (+ many more years of testing with CSVs ;) ).

Why do we test, again?

To a first order, yes, testing does allow engineers to provide automated, protective guarantees about the behavior of their software. And that’s a win. “But at what cost?” Good testing practices and tools introduce very short term, very local friction but beyond that, actually improve productivity and quality simultaneously. You should very soon see these effects, else your solution needs improvement ;) :

• speed up development across large teams, as developers feel safer making big changes/refactoring their dbt project and making sweeping changes to expectations if necessary
• improve the health of dbt software - e.g. as developers get more aggressive with refactoring as a side effect of tests, their refactorings mean they can change the shape of their projects. some trite examples:
  • remove unused/undocumented/icky models without fear of breaking downstream things
  • move CTEs into models in a large project
  • rename columns
  • and so on… basically reduce tech debt — both incrementally and in large sweeping PRs — where it was once exhausting/prohibitive.

I’d interview early adopters of unit testing to see if these effects are being felt - it usually takes some months before they’re obvious but tech leads will see signals early.

The holy grail of testing in SWE is “test driven development (TDD)” (https://en.wikipedia.org/wiki/Test-driven_development), whereby developers feel so empowered by their testing tools that they write the tests first, before even writing their logic (omg). This is most often a pipedream but does happen with some combo of great tools and v. skilled/experienced engineers. A good north star… (I’m imagining a future where product folks write down some test expectations in a spreadsheet and plain english of what transformation logic should do, and gen ai writes the PR with passing tests. )

Some land mines/ideas

(having built an internal dbt testing tool for ~50 engineers)

Testing introduces friction in the development process. How to minimize extra pain such that engineers have no qualms about updating a large set of tests on code written years ago, or even consider writing tests first? Testing has to be ergonomic:

• It should be easy to read expectations (both in PRs in a “diff” state, and during local dev). Consider something that autoformats CSV format into its most legible form - fixed width/autoformatted.
• Testing should not add a ton of surface area (e.g. files/artifacts) to a dev’s (or a reviewer’s) changeset. Consider consolidating all the relevant artifacts into a single file, rather than N CSVs. Think: what does a one line SQL change and associated test change look like in PR form?
• Testing should have some minimum amount of programmability/boilerplate reduction/reusability/composability e.g. parametrization. This might be a tough one, but consider pytest’s parametrization/fixtures for inspo.
• Testing should allow for flexibility in how one defines expectations
• Testing should be “free” - i.e. ideally not expend cloud costs. Do as much local work as possible - I can imagine transpiling the sql into duckdb dialect for local testing.
• testing in data has special friction b/c capturing expectations: simple business logic, complex expected value

Here’s a screenshot of the dbt test spec format that’s the core of the tool I’d built on previous team - it’s finely tuned for a specific team, there's things I don't like about it, etc. so just intended for inspo. It’s HOCON format - a more composable, flexible version of YAML or JSON (common in Scala land) (e.g. you can reference variables!). The entire test spec for a model (or set of models if desired) can be contained in one file. FWIW, having tens of these test specs completely greatly accelerated the efficiency of our team and prevented myriad bugs from leaking into production.

https://docs.spongepowered.org/stable/en/server/getting-started/configuration/hocon.html

https://blog.ometer.com/2015/09/07/json-like-config-a-spectrum-of-underoverengineering/

[smomen]

@graciegoheen @MichelleArk One key "tool" that helped folks get off the ground - b/c remember, you want folks who already have a dbt project in production to "start" writing unit tests in spite of their existing practices/habits - is to autogenerate the above test spec based on live/preexisting data. We had a couple ways of doing this:

• if folks had a small test database they were working with, we'd sample the input and output tables and create this spec for you/autoformat it.
• allow folks to sample a few rows from their production database for inputs; we'll run the dbt model/selector specified and then download the output tables into the spec. We had loud warnings here re: not leaking sensitive data into the file which ended up in source control.

This is pretty much the opposite of test driven development but is not at-odds with a healthy engineering workflow/gets people writing tests that protect their "current" models (even if they're wrong!) and forces any new devs on those models to have to change tests accordingly. Basically you can seed an entire dbt project with no tests -> turn it into one with full coverage with a single command.

Can't expect folks to handwrite tests for hundreds of models that exist today, too many unwieldy CSVs :)

[kgmcquate]

@smomen Regarding autogenerating tests, I recently started dbt-testgen because I couldn't find another tool that does this. I agree that it's kind of backwards TDD, but it is useful for getting started.

[noel]

@kgmcquate thats cool. Might be a good feature to include in dbt-coves.

I had thought a while back of having something that would sample data like is done when creating an ML model and generating train and test data.

[dbeatty10]

Hey folks! Dropping by to let you know a community feedback / office hours session that we'll be running in a couple weeks.

Thursday, 18 January, 8am Pacific: Unit testing as native functionality in dbt

• See the initial design in action
• We'd love to have you join and hear your feedback!

Some supporting resources:

• All the awesome discussion above 😎
• GitHub epic

[MarcinZegar]

Hi @dbeatty10 , is unit testing still planned for 1.8? Is there any timeline for its release?

[graciegoheen]

That's right! We're planning on releasing unit testing in 1.8 which is currently scheduled for the spring. You can read more in our roadmap update here.

[afranzi]

Hey there! I'm quite new to DBT discussions, so I hope this finds good.
We developed a similar solution using pytest-postgres documented in medium - unittesting dbt .

Checking the proposal and the comments, we would take advantage with something like:

given

• input: [ref | source] - we should be able to define sources too and create them based on the source.yml definition, only if all the data types are documented properly should we be able to create the table dynamically.
• format: [csv | json | seed] - allow to define seed files, otherwise the unit.yml file could get messy and have too many things on it.
  • seed (optional): path to seed
  • csv (optional): csv inline
  • json (optional): json inline

do

• selector - Define a DBT selector about all the models we expect to execute, this should allow us to play with the granularity of the test.

then

• expect
  • format: [csv | json | seed]
  • seed: path to seed

Since we have all the dbt tests capabilities, having the selector option should allow us to take advantage of them too.

Others

We could expect that given the dbt lineage of the selector, the unittest validates that all the source nodes are provided and defined in the given section before executing anything.

It could also return a warning if some defined sources in the given section are not impacted/contemplated by the defined selector.

unit: 
 - model: my_model
   tests: 
   - name: test_name
     given: 
       - input: ref('my_model_a')
         format: seed
         seed: ref('test_001/input/model_a.csv')
     do:
       selector: "@marketing"
     then:
       - expect: ref('campaigns')
         format: seed
         seed: ref('test_001/output/campaigns.csv')

[dbeatty10]

Here's the Zoom recording from the feedback session earlier today:

https://dbtlabs.zoom.us/rec/share/kPEECDqWh3Sbp4ApWbJ8_q3uGbyNcH7FGmm03MkhVOudV2gKrgvqvNn6T1z0QZuK.CcDkzLIvZPQGd2_B?startTime=1705593243000
Passcode: P3?%EP57

[ssillaots-boku]

You talked about that when dbt build command is ran then before actually building the model the unit tests are ran first. But under the hood it means that a temporary table is created in the database which will get deleted after the unit test? I would guess it would be with _test suffix as in groceries_test?

[dbrtly]

+1 on the build command should not include unit tests by default.

The customary use of build to run and audit a selector with one command is convenient. I perceive unit tests as relevant to developer experience and code review but I don't want to run them on the production environment, slowing down all the jobs configured with build.

[dbrtly]

Stupid question: in the demo Grace shows three separate, uniquely named unit tests but comments out two of them when the tests are run. Is the option for multiple tests for the same model a design goal?

I prefer this because small, narrow-scoped tests are easier to debug and maintain.

[graciegoheen]

Is the option for multiple tests for the same model a design goal?

Yep! You can define multiple unit tests for the same model as long as they have unique names :)

[noel]

Great presentation of the feature.

Some comments:

1. I like the idea of an environment var to not run the unit tests in production, but it seems like it should be the other way around. e.g. by default don't run the unit tests, if you want to run them in ci / dev, then set an environment var or pass a flag. What's the rationale for the inverse?
2. +1 to fixtures from SELECT. I can imagine having a fixtures database. Not a must have, but would be good.
3. Since you leverage info from upstream model for data types, is there any limitation when the upstream is a deferred model?

[graciegoheen]

I like the idea of an environment var to not run the unit tests in production, but it seems like it should be the other way around. e.g. by default don't run the unit tests, if you want to run them in ci / dev, then set an environment var or pass a flag. What's the rationale for the inverse?

We were thinking if they were excluded by default folks might be confused - "I added this unit test but now when I try to run dbt test/build nothing is happening" - could you say more about why you'd expect/want them to be excluded by default?

[dbrtly]

The unit tests are for evidence at project edit or code review time to validate that the code fulfilled the intended functionality.

Tests should be deterministic so that whether they succeed or fail on the first run, we can trust that result. We mocked the inputs with the same static data on each test instance. Rerunning the tests without changing the mocks, code or tests should reproduce the initial result.

The alternative is we can’t trust the test results.
Non-deterministic tests == lower credibility and lower utility from unit tests.

Assuming reproducible tests which succeeded at code review, what would we learn by rerunning the unit tests in production?

[graciegoheen]

Assuming reproducible tests which succeeded at code review, what would we learn by rerunning the unit tests in production?

I totally agree with you that we'd recommend not running unit tests in production. We should absolutely implement an environment variable that allows folks to turn "off/on" unit tests for a given environment.

I think my question here is should folks have to:

• turn unit tests off for production
• turn unit tests on for development/ci

Currently, I was thinking it would be more confusing to add a unit test and have nothing happen unless you remember to explicitly turn them on for development.

[dbrtly]

Suggestions to facilitate clear comms for humans:

• new command dbt unit-test for unit tests

• add new command “audit” as synonym for the existing “test” command. References industry jargon (example “write-audit-publish” pattern)

• consider remapping “test” command to “unit-test” command in dbt v2.0

[djayatillake]

@graciegoheen @MichelleArk this is great to see!

In the webinar recording, Grace mentions that you need not provide all of the columns the model requires for test input data where the test does not require them. I can see this working fine where there is no aggregation in the model and the grain of the data doesn't change in the model.

However, where the model does aggregate data, I can see a situation where only specifying a subset of the group by fields could cause issues with the output of an aggregate function... therefore the unit test not reflecting reality of model use. How do the internals of unit test work in this context? Do unit tests require all fields from a group by be present in the test input data, or will it just group by the provided fields?

[dbeatty10]

@williebsweet Combining your sentiment with #9405, here is an idea:

💡 In addition to being able to mock the given inputs and expected outputs, be able to mock the transformation logic also.

The key outcome being that the output of macros can be tested with less syntax overhead.

Assuming that we've refactored the key logic into a testable macro named is_valid_email_address, see below for a couple syntax variants.

Note that in these cases, everything is mocked since mock_wizards and mock_model wouldn't really be models in your production dbt project.

Idea 1

  - name: test_valid_email_address

    given:
      - input: ref("mock_wizards")
        rows:
          - {wizard_id: "1", email: cool@example.com,     email_top_level_domain: example.com}
          - {wizard_id: "2", email: cool@unknown.com,     email_top_level_domain: unknown.com}
          - {wizard_id: "3", email: badgmail.com,         email_top_level_domain: gmail.com}
          - {wizard_id: "4", email: missingdot@gmailcom,  email_top_level_domain: gmail.com}

    mock_model:
      rows:
        - {wizard_id: '{{ wizard_id }}', is_valid_email_address: '{{ is_valid_email_address("email", "email_top_level_domain") }}'}

    expect:
      rows:
        - {wizard_id: "1", is_valid_email_address: true}
        - {wizard_id: "2", is_valid_email_address: false}
        - {wizard_id: "3", is_valid_email_address: false}
        - {wizard_id: "4", is_valid_email_address: false}

Idea 2

  - name: test_valid_email_address

    given: 
      - input: ref("mock_wizards")
        rows:
          - {wizard_id: "1", email: cool@example.com,     email_top_level_domain: example.com}
          - {wizard_id: "2", email: cool@unknown.com,     email_top_level_domain: unknown.com}
          - {wizard_id: "3", email: badgmail.com,         email_top_level_domain: gmail.com}
          - {wizard_id: "4", email: missingdot@gmailcom,  email_top_level_domain: gmail.com}

    mock_model:
        format: sql
        rows: |
          select
            wizard_id,
            {{ is_valid_email_address("email", "email_top_level_domain") }} as is_valid_email_address
          from {{ ref("mock_wizards") }}

    expect: 
      rows:
        - {wizard_id: "1", is_valid_email_address: true}
        - {wizard_id: "2", is_valid_email_address: false}
        - {wizard_id: "3", is_valid_email_address: false}
        - {wizard_id: "4", is_valid_email_address: false}

Idea 3

  - name: test_valid_email_address

    given: 
      - input: []

    mock_model: |
      select
        1 as wizard_id,
        {{ is_valid_email_address("'cool@example.com'", "'example.com'") }} as is_valid_email_address

      union all

      select
        2 as wizard_id,
        {{ is_valid_email_address("'cool@unknown.com'", "'unknown.com'") }} as is_valid_email_address

      union all

      select
        3 as wizard_id,
        {{ is_valid_email_address("'badgmail.com'", "'gmail.com'") }} as is_valid_email_address

      union all

      select
        4 as wizard_id,
        {{ is_valid_email_address("'missingdot@gmailcom'", "'gmail.com'") }} as is_valid_email_address

    expect: 
      rows:
        - {wizard_id: "1", is_valid_email_address: true}
        - {wizard_id: "2", is_valid_email_address: false}
        - {wizard_id: "3", is_valid_email_address: false}
        - {wizard_id: "4", is_valid_email_address: false}

Idea 4

  - name: test_valid_email_address

    given: []

    mock_model:

      rows:
        - {wizard_id: '1', is_valid_email_address: '{{ is_valid_email_address("'cool@example.com'", "'example.com'") }}'}
        - {wizard_id: '2', is_valid_email_address: '{{ is_valid_email_address("'cool@unknown.com'", "'unknown.com'") }}'}
        - {wizard_id: '3', is_valid_email_address: '{{ is_valid_email_address("'badgmail.com'", "'gmail.com'") }}'}
        - {wizard_id: '4', is_valid_email_address: '{{ is_valid_email_address("'missingdot@gmailcom'", "'gmail.com'") }}'}

    expect:
      rows:
        - {wizard_id: "1", is_valid_email_address: true}
        - {wizard_id: "2", is_valid_email_address: false}
        - {wizard_id: "3", is_valid_email_address: false}
        - {wizard_id: "4", is_valid_email_address: false}

[dbrtly]

In principle, I would prefer to test macros directly rather than when they are wrapped in a model because the logic is simpler.

Column-level macros are easy to test today. For example:

-- test_macro__is_valid_email_address.sql
with 
test_cases as( 
select 
    True as expect,
     {{ is_valid_email_address(
          "'cool@example.com'", 
          "'example.com'") }}
     as actual,
     'always passes' as description 

union all select 
    False,
     {{ is_valid_email_address(
          "'cool@gmailcom'", 
          "'example.com'") }},
     'Invalid Domain: missing dot'
)

select * from test_cases 
where expect != actual

Further, I would prefer to mock the macros so that a model test never fails because a macro has changed.

Throw an error if you mock a macro without testing it separately would be ideal.

[dbeatty10]

@dbrtly Good point that you can test column-level macros using singular data tests 💡

Here's how your example might look if the transformation logic can be mocked:

  - name: test_valid_email_address

    given: 
      - input: ref("mock_wizards")
        rows:
          - {expected: true, email: cool@example.com,      email_top_level_domain: example.com}
          - {expected: false, email: cool@unknown.com,     email_top_level_domain: unknown.com}
          - {expected: false, email: badgmail.com,         email_top_level_domain: gmail.com}
          - {expected: false, email: missingdot@gmailcom,  email_top_level_domain: gmail.com}

    mock_model:
        format: sql
        rows: |
          with test_cases as (
          
            select
              expected,
              {{ is_valid_email_address("email", "email_top_level_domain") }} as actual
          
            from {{ ref("mock_wizards") }}
          
          )
          
          select * from test_cases
          where actual != expected

    expect: []

[daniel-bartley]

This example is a bit weird to me. I'm not sure what's it's trying to test. Modified it to deduplicate.
Here I want to test the deduplication but not the is_valid_email_address macro.

I like mock inputs where inputs include overriding source(), ref(), and custom macros.

I don't like overriding this() without safety guardrails. I can imagine the mock_model idea being abused and leading to maintenance problems. I'm wary of the possibility of a developer mocking the model under test and creating misleading test results. Potential hard-to-find bugs. Being able to trust test results is important for credibility and team buy-in.

Can we do this:

  - name: test_mock_wizards

    given: 
      - input: is_valid_email_address(input_string)
        returns: "foo@example.com"
        
      - input: ref("mock_wizards")
        rows:
          - {wizard_id: "1", email: cool@example.com, email_top_level_domain: example.com, ingest_at: "2024-02-14T12:00:00"}
          - {wizard_id: "1", email: cool@example.com, email_top_level_domain: example.com, ingest_at: "2024-02-01T12:00:00"}

    expect: 
      rows:
        - {wizard_id: "1", email: foo@example.com, email_top_level_domain: example.com, ingest_at: "2024-02-14T12:00:00"}

[dbeatty10]

Yep, you can do that by configuring overrides within your unit tests. Here's an example:

# models/_unit.yml

unit_tests:

  - name: demo_unit_test_overrides
    description: Just a demonstration of configuring overrides -- doesn't actually test anything interesting

    overrides:  # optional: configuration for the dbt execution environment
      macros:
        is_valid_email_address: foo@example.com
        invocation_id: bbxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
        run_started_at: 2024-02-17 00:00:00.000000+00:00
        current_timestamp: "cast('2020-02-17' as timestamp)"
        is_incremental: true
      vars:
        event_type: some_event_type
      env_vars:
        DBT_USER: some_user

    given: []

    model: my_model

    expect:
      format: csv
      rows: |
        id,my_timestamp
        1,2020-02-17

-- models/my_model.sql

select
    1 as id,
    {{ is_incremental() }} as is_incremental,
    {{ current_timestamp() }} as my_timestamp,
    '{{ var("event_type", "activation") }}' as my_var,
    '{{ env_var("DBT_USER", "default_user") }}' as my_env_var,
    '{{ run_started_at }}' as run_started_at,
    '{{ invocation_id }}' as invocation_id

dbt test --select demo_unit_test_overrides

[Lorenitz]

Hey @graciegoheen!

It would be great if we could have a release tagged v1.8-beta. This would allow us to conduct testing even while the unit tests are not fully ready.

[graciegoheen]

We've got a beta release tentatively scheduled for Feb 28th!

[Lorenitz]

Amazing! Thank you so much! :)

[ntnhaatj]

hi @MichelleArk,
great that it has been supported in dbt 1.8, thank you all you guys for your great contribution!

according to your idea

Testing should be “free” - i.e. ideally not expend cloud costs. Do as much local work as possible - I can imagine transpiling the sql into duckdb dialect for local testing.

Has this been supported in dbt v1.8 yet?
If native support is unavailable, do we have any solutions to run the SQL transpiler (e.g., SQLGLot) to transpile the model into another in-memory engine (e.g., DuckDB) before executing unit tests?

[b-per]

Transpiling is not supported today, no.

The cost on the warehouse is minimal, but if you want to run the tests with 0 DW cost, on DuckDB for example, you could write your models so that they work independently of the data warehouse, leveraging cross database macros.

Then, you could use the DuckDB target for running those unit tests.

[megetron3]

Running tests in-memory can significantly impact performance and enable parallel test execution, making it a valuable feature for the dbt unit tests framework.

For me, running this on DuckDB in-memory:

dbt run --models "stg_customers top_level_email_domains" --empty && dbt test --select "stg_customers top_level_email_domains test_is_valid_email_address"

shows that the database is still empty with no stg_customers, suggesting that both commands are executed on different instances.

14:32:20  Running with dbt=1.8.0
14:32:21  Registered adapter: duckdb=1.8.0
14:32:21  [WARNING]: Configuration paths exist in your dbt_project.yml file which do not apply to any resources.
There are 1 unused configuration paths:
- models.test.example
14:32:21  Found 6 models, 4 data tests, 406 macros, 1 unit test
14:32:21
14:32:21  Concurrency: 1 threads (target='dev')
14:32:21
14:32:21  1 of 2 START sql view model main.stg_customers ................................. [RUN]
14:32:21  1 of 2 OK created sql view model main.stg_customers ............................ [OK in 0.04s]
14:32:21  2 of 2 START sql view model main.top_level_email_domains ....................... [RUN]
14:32:21  2 of 2 OK created sql view model main.top_level_email_domains .................. [OK in 0.01s]
14:32:21
14:32:21  Finished running 2 view models in 0 hours 0 minutes and 0.18 seconds (0.18s).
14:32:21
14:32:21  Completed successfully
14:32:21
14:32:21  Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2
14:32:23  Running with dbt=1.8.0
14:32:24  Registered adapter: duckdb=1.8.0
14:32:24  [WARNING]: Configuration paths exist in your dbt_project.yml file which do not apply to any resources.
There are 1 unused configuration paths:
- models.test.example
14:32:24  Found 6 models, 4 data tests, 406 macros, 1 unit test
14:32:24
14:32:24  Concurrency: 1 threads (target='dev')
14:32:24
14:32:24  1 of 1 START unit_test dim_customers::test_is_valid_email_address .............. [RUN]
14:32:24  1 of 1 ERROR dim_customers::test_is_valid_email_address ........................ [ERROR in 0.04s]
14:32:24
14:32:24  Finished running 1 unit test in 0 hours 0 minutes and 0.14 seconds (0.14s).
14:32:24
14:32:24  Completed with 1 error and 0 warnings:
14:32:24
14:32:24    Compilation Error in model stg_customers (models/test_is_valid_email_address.yml)
  Not able to get columns for unit test 'stg_customers' from relation "memory"."main"."stg_customers"

  > in macro get_fixture_sql (macros/unit_test_sql/get_fixture_sql.sql)
  > called by model stg_customers (models/test_is_valid_email_address.yml)
14:32:24
14:32:24  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1