Development guidelines
When you’re prototyping your use case and fine-tuning the underlying data model,
your priority is iteration speed. dbt has many features that can help speed
up development, like node selection and model preview.
Before you start, we recommend getting familiar with how these features
work with the dbt-materialize
adapter to make the most of your development
time.
Node selection
By default, the dbt-materialize
adapter drops and recreates all models on
each dbt run
invocation. This can have unintended consequences, in particular
if you’re managing sources and sinks as models in your dbt project. dbt allows
you to selectively run specific models and exclude specific materialization
types from each run using node selection.
Exclude sources and sinks
You can manually exclude specific materialization types using the
exclude
flag in
your dbt run invocations. To exclude sources and sinks, use:
dbt run --exclude config.materialized:source config.materialized:sink
YAML selectors
Instead of manually specifying node selection on each run, you can create a YAML selector that makes this the default behavior when running dbt:
# YAML selectors should be defined in a top-level file named selectors.yml
selectors:
- name: exclude_sources_and_sinks
description: >
Exclude models that use source or sink materializations in the command
invocation.
default: true
definition:
union:
# The fqn method combined with the "*" operator selects all nodes in the
# dbt graph
- method: fqn
value: "*"
- exclude:
- 'config.materialized:source'
- 'config.materialized:sink'
Because default: true
is specified, dbt will use the selector’s criteria
whenever you run an unqualified command (e.g. dbt build
, dbt run
). You can
still override this default by adding selection criteria to commands, or adjust
the value of default
depending on the target environment. To learn more about
using the default
and exclude
properties with YAML selectors, check the
dbt documentation.
Run a subset of models
You can run individual models, or groups of models, using the select
flag
in your dbt run invocations:
dbt run --select "my_dbt_project_name" # runs all models in your project
dbt run --select "my_dbt_model" # runs a specific model
dbt run --select "my_model+" # select my_model and all downstream dependencies
dbt run --select "path.to.my.models" # runs all models in a specific directory
dbt run --select "my_package.some_model" # runs a specific model in a specific package
dbt run --select "tag:nightly" # runs models with the "nightly" tag
dbt run --select "path/to/models" # runs models contained in path/to/models
dbt run --select "path/to/my_model.sql" # runs a specific model by its path
For a full rundown of selection logic options, check the dbt documentation.
Model results preview
dbt show
command uses a LIMIT
clause under the hood, which has
known performance limitations
in Materialize.
To debug and preview the results of your models without materializing the
results, you can use the dbt show
command:
dbt show --select "model_name.sql"
23:02:20 Running with dbt=1.7.7
23:02:20 Registered adapter: materialize=1.7.3
23:02:20 Found 3 models, 1 test, 4 seeds, 1 source, 0 exposures, 0 metrics, 430 macros, 0 groups, 0 semantic models
23:02:20
23:02:23 Previewing node 'model_name':
| col |
| -------------------- |
| value1 |
| value2 |
| value3 |
| value4 |
| value5 |
By default, the dbt show
command will return the first 5 rows from the query
result (i.e. LIMIT 5
). You can adjust the number of rows returned using the
--limit n
flag.
It’s important to note that previewing results compiles the model and runs the
compiled SQL against Materialize; it doesn’t query the already-materialized
database relation (see dbt-core
#7391).
Unit tests
Minimum requirements: dbt-materialize
v1.8.0+
map
and list
are
not supported in unit tests yet (see dbt-adapters
#113).
For an overview of other known limitations, check the dbt documentation.
To validate your SQL logic without fully materializing a model, as well as future-proof it against edge cases, you can use unit tests. Unit tests can be a quicker way to iterate on model development in comparison to re-running the models, since you don’t need to wait for a model to hydrate before you can validate that it produces the expected results.
-
As an example, imagine your dbt project includes the following models:
Filename: models/my_model_a.sql
SELECT 1 AS a, 1 AS id, 2 AS not_testing, 'a' AS string_a, DATE '2020-01-02' AS date_a
Filename: models/my_model_b.sql
SELECT 2 as b, 1 as id, 2 as c, 'b' as string_b
Filename: models/my_model.sql
SELECT a+b AS c, CONCAT(string_a, string_b) AS string_c, not_testing, date_a FROM {{ ref('my_model_a')}} my_model_a JOIN {{ ref('my_model_b' )}} my_model_b ON my_model_a.id = my_model_b.id
-
To add a unit test to
my_model
, create a.yml
file under the/models
directory, and use theunit_tests
property:Filename: models/unit_tests.yml
unit_tests: - name: test_my_model model: my_model given: - input: ref('my_model_a') rows: - {id: 1, a: 1} - input: ref('my_model_b') rows: - {id: 1, b: 2} - {id: 2, b: 2} expect: rows: - {c: 2}
For simplicity, this example provides mock data using inline dictionary values, but other formats are supported. Check the dbt documentation for a full rundown of the available options.
-
Run the unit tests using
dbt test
:dbt test --select test_type:unit 12:30:14 Running with dbt=1.8.0 12:30:14 Registered adapter: materialize=1.8.0 12:30:14 Found 6 models, 1 test, 4 seeds, 1 source, 471 macros, 1 unit test 12:30:14 12:30:16 Concurrency: 1 threads (target='dev') 12:30:16 12:30:16 1 of 1 START unit_test my_model::test_my_model ................................. [RUN] 12:30:17 1 of 1 FAIL 1 my_model::test_my_model .......................................... [FAIL 1 in 1.51s] 12:30:17 12:30:17 Finished running 1 unit test in 0 hours 0 minutes and 2.77 seconds (2.77s). 12:30:17 12:30:17 Completed with 1 error and 0 warnings: 12:30:17 12:30:17 Failure in unit_test test_my_model (models/models/unit_tests.yml) 12:30:17 actual differs from expected: @@ ,c +++,3 ---,2
It’s important to note that the direct upstream dependencies of the model that you’re unit testing must exist in Materialize before you can execute the unit test via
dbt test
. To ensure these dependencies exist, you can use the--empty
flag to build an empty version of the models:dbt run --select "my_model_a.sql" "my_model_b.sql" --empty
Alternatively, you can execute unit tests as part of the
dbt build
command, which will ensure the upstream depdendencies are created before any unit tests are executed:dbt build --select "+my_model.sql" 11:53:30 Running with dbt=1.8.0 11:53:30 Registered adapter: materialize=1.8.0 ... 11:53:33 2 of 12 START sql view model public.my_model_a ................................. [RUN] 11:53:34 2 of 12 OK created sql view model public.my_model_a ............................ [CREATE VIEW in 0.49s] 11:53:34 3 of 12 START sql view model public.my_model_b ................................. [RUN] 11:53:34 3 of 12 OK created sql view model public.my_model_b ............................ [CREATE VIEW in 0.45s] ... 11:53:35 11 of 12 START unit_test my_model::test_my_model ............................... [RUN] 11:53:36 11 of 12 FAIL 1 my_model::test_my_model ........................................ [FAIL 1 in 0.84s] 11:53:36 Failure in unit_test test_my_model (models/models/unit_tests.yml) 11:53:36 actual differs from expected: @@ ,c +++,3 ---,2