Testing dbt data transformations

Archive
2

I like the idea of testing transformations on dummy data, but at first glance dtspec looks overly complicated to me.

I’ll give a potential implementation of a "poor man’s dtspec":

  1. Use seeds to create tables with some special prefix - for example test_. For a source table source('jaffle_shop', 'orders'), the test table would be named test_jaffle_shop__orders. For a model ref(customers), the test table would be named test_customers. The idea is to use the naming convention to create a 1 to 1 relationship between sources/models and corresponding test tables.
  2. Find all test seeds that refer to models - either by eliminating the ones that have two underscores __ in their name or by cross-referencing with the graph variable. For each such model:
    1. Somehow temporarily and recursively make the ref('model') macro resolve to test_model and source('source', 'table') resolve to test_source__table.
    2. Run the generated SQL and make sure the output matches the contents of the corresponding test seed.

All this can be attained just by using seeds and with minimal configuration. So what’s the advantage of using actual dtspec?