[Feature] Support Unit Testing on Materialized Views

[bdewilde]

Is this a new bug?

• I believe this is a new bug
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

As of dbt v1.8.1, if a model depends upon another model for which materialized="materialized_view", any unit tests on the former model fail with a compilation error: Not able to get columns for unit test X from relation Y because the relation doesn't exist. If the referenced model is materialized as a table, no error is raised; if the model being tested is a materialized view (and the reference model is a table), no error is raised.

Expected Behavior

I would expect unit tests to function regardless of referenced model's materialization. I don't see this listed as a limitation here.

Steps To Reproduce

file: model1.sql

{{ config(materialized="materialized_view") }}

SELECT 1 AS col1
UNION ALL
SELECT 2 AS col1

file: model2.sql

{{ config(materialized="table") }}

SELECT * FROM {{ ref("model1") }}

file: _unit_tests.yml

unit_tests:
  - name: test_matview
    model: model2
    given:
      - input: ref("model1")
        rows:
          - { col1: 1 }
          - { col1: 2 }
    expect:
      rows:
        - { col1: 1 }
        - { col1: 2 }

$ dbt-postgres test --select test_matview

Relevant log output

Extension executing `dbt test`...
15:52:37  Running with dbt=1.8.1
15:52:37  Registered adapter: postgres=1.8.1
15:52:37  Unable to do partial parsing because saved manifest not found. Starting full parse.
15:52:41  Found 2 models, ...
15:52:41
15:52:41  Concurrency: 2 threads (target='dev')
15:52:41
15:52:41  1 of 1 START unit_test model2::test_matview .................................... [RUN]
15:52:41  1 of 1 ERROR model2::test_matview .............................................. [ERROR in 0.03s]
15:52:41
15:52:41  Finished running 1 unit test in 0 hours 0 minutes and 0.14 seconds (0.14s).
15:52:41
15:52:41  Completed with 1 error and 0 warnings:
15:52:41
15:52:41    Compilation Error in model model1 (models/_unit_tests.yml)
  Not able to get columns for unit test 'model1' from relation "db"."schema"."model1" because the relation doesn't exist

  > in macro get_fixture_sql (macros/unit_test_sql/get_fixture_sql.sql)
  > called by model model1 (models/marts/_unit_tests.yml)

Environment

- OS: macOS 14
- Python: 3.9.13
- dbt-postgres: 1.8.1

Additional Context

No response

[amychen1776]

Thank you so much for providing such an easy, reproducible example! We will take a look at this

[bdewilde]

Hi @amychen1776 , just making sure I'm following: The decision was not to support this functionality, but to document that it isn't supported?

[amychen1776]

@bdewilde For the short term, we are going to document this limitation. But we do want to tackle this support and the core team is going to look into scoping this out.