-
Notifications
You must be signed in to change notification settings - Fork 1.6k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
[Bug] Limitation of dbt unit tests: not support the use of introspective queries (run_query
, etc.)
#11121
Comments
Thanks for reaching out @ashwini-kumar2 ! I wasn't able to reproduce this. Could you try tweaking the simple example below and see if you're able to trigger the error you reported? Project files and commandsCreate these files:
select 1 as id
{{
config(
materialized='incremental',
)
}}
{% set dummy_query %}
select * from {{ source("my_source", "dummy") }}
{% endset %}
select * from ({{ dummy_query }})
sources:
- name: my_source
database: "{{ target.database }}"
schema: "{{ target.schema }}"
tables:
- name: dummy
unit_tests:
- name: test_name
model: my_model
overrides:
macros:
is_incremental: true
given:
- input: source('my_source', 'dummy')
rows:
- {id: 3}
expect:
rows:
- {id: 3} Run these commands:
|
source('some_schema', 'some_table')
inside a variable
I'm able to reproduce with this models/my_model.sql
macro/dummy_macro.sql
unit_test/dummy_test.yml
|
Thanks for providing more info @ashwini-kumar2 👍 The example you shared looks like it should have Here's a complete working example that looks like it is closest to what you intended. Could you try it and tweak it to reproduce the issue you saw? If you are able to reproduce the issue, could you share all of the following?
Project files and commandsCreate these files:
select 1 as id
{% macro dummy_macro() %}
select * from {{ source("my_source", "dummy") }}
{% endmacro %}
{{
config(
materialized='incremental',
)
}}
{% set dummy_query = dummy_macro() %}
select * from ({{ dummy_query }})
sources:
- name: my_source
database: "{{ target.database }}"
schema: "{{ target.schema }}"
tables:
- name: dummy
unit_tests:
- name: dummy_test
description: "Unit test dummy "
model: dummy_model
overrides:
macros:
is_incremental: true
given:
- input: source('my_source', 'dummy')
format: csv
fixture: dummy_fixture
expect:
format: csv
fixture: dummy_fixture Run these commands:
Get this output (using dbt-duckdb):
|
Thank you @dbeatty10 for the support. I think I'm presenting the issue in chunks. Apologies for that. Here are the complete details to reproduce it macros/dummy_macro_which_calls_score.sql
models/dummy_score.sql
models/my_model.sql
models/sources/DUMMY/sources.yml
models/unit_tests/dummy_model_test.yml
tests/fixtures/unit_test_mocked_data/dummy_score_table.sql
LOGS
|
@ashwini-kumar2 I think we've gotten to the bottom of it! See below for a simplified example that gives the same error as you reported. TLDRThe reason you're getting this error is because:
More detailThis is actually the same underlying thing that is reported in #10759, so I'm going to close this as a duplicate of that one. In the meantime, I've opened dbt-labs/docs.getdbt.com#6652 to clarify this in our product documentation. ReprexEssentially the same as #10759 (comment) Create these files:
select 10 as score
-- depends_on: {{ ref('queried_introspectively') }}
{% if execute %}
{% set query %}
select * from {{ ref("queried_introspectively") }}
{% endset %}
{% set dummy_result = run_query(query) %}
{% endif %}
select 1 as id
unit_tests:
- name: my_model_dummy_test
model: my_model
given:
- input: ref("queried_introspectively")
rows:
- {score: 99}
expect:
rows:
- {id: 1} Run these commands: dbt run -s +my_model --empty
dbt test --select my_model_dummy_test Get an error similar to the following:
|
source('some_schema', 'some_table')
inside a variablerun_query
, etc.)
run_query
, etc.)run_query
, etc.)
Is this a new bug in dbt-core?
Current Behavior
dbt unit tests do not support the use of
source('some_schema', 'some_table')
inside a variable.command to execute dbt unit test
dbt test --select test_name
Expected Behavior
DBT trying to query a CTE which does not exist, Ideally dbt should create the CTE before quering it
Steps To Reproduce
consider this model which uses
source
inside a variableDBT generates below query, dbt tries to query a cte which doesn't exist
However changing the model to this works well with unit test
Relevant log output
when using source inside a variable dbt tries to query a cte which doesn't exists
Environment
Which database adapter are you using with dbt?
other (mention it in "Additional Context")
Additional Context
Azure Databricks
The text was updated successfully, but these errors were encountered: