In 0.4.0, `dbt test` succeeds but `dbt build` fails #197

davenettle · 2024-01-16T03:24:23Z

Noticed this in our production build when we tried updating to 0.4.0. We had some fairly simple tests that did a couple of mocks and an expect, and we noticed in CI that while dbt test worked, dbt build would fail. So I've tried to replicate the issue by wrangling/simplifying the typical jaffle_shop example.

The files look like:

# models/seed_orders.sql

select
    1 as order_id
    , 1 as customer_id
    , '2000-01-01'::date as order_date
    , 'pending' as status

# models/orders.sql

select * from {{ dbt_unit_testing.ref('seed_orders') }}

# tests/orders_test.sql

{{ config(tags=['unit-test']) }}

{% call dbt_unit_testing.test ('orders',
    'it should do something with orders') %}

    {% call dbt_unit_testing.mock_ref ('seed_orders') %}
        select
            2 as order_id
            , 2 as customer_id
            , '2024-01-01'::date as order_date
            , 'completed' as status
    {% endcall %}

    {% call dbt_unit_testing.expect() %}
        select
            2 as order_id
            , 2 as customer_id
            , '2024-01-01'::date as order_date
            , 'completed' as status
    {% endcall %}
{% endcall %}

# packages.yml

packages:
  - package: EqualExperts/dbt_unit_testing
    version: 0.4.0

Prepare to run:

dbt clean
dbt deps

Run results in 0.4.0:

~/C/jaffle_shop git:main ❯❯❯ dbt test --select orders_test                                                                                                         ⏎ ✖ ✱ ◼
03:15:17  Running with dbt=1.4.5
03:15:17  Found 6 models, 21 tests, 0 snapshots, 0 analyses, 738 macros, 0 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics
03:15:17
03:15:17  Concurrency: 1 threads (target='dev')
03:15:17
03:15:17  1 of 1 START test orders_test .................................................. [RUN]
03:15:17  1 of 1 PASS orders_test ........................................................ [PASS in 0.12s]
03:15:17
03:15:17  Finished running 1 test in 0 hours 0 minutes and 0.24 seconds (0.24s).
03:15:17
03:15:17  Completed successfully

~/C/jaffle_shop git:main ❯❯❯ dbt build --select orders_test                                                                                                          ✖ ✱ ◼
03:16:00  Running with dbt=1.4.5
03:16:00  Found 6 models, 21 tests, 0 snapshots, 0 analyses, 738 macros, 0 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics
03:16:00
03:16:01  Concurrency: 1 threads (target='dev')
03:16:01
03:16:01  1 of 1 START test orders_test .................................................. [RUN]
03:16:01  1 of 1 ERROR orders_test ....................................................... [ERROR in 0.03s]
03:16:01
03:16:01  Finished running 1 test in 0 hours 0 minutes and 0.13 seconds (0.13s).
03:16:01
03:16:01  Completed with 1 error and 0 warnings:
03:16:01
03:16:01  Database Error in test orders_test (tests/orders_test.sql)
03:16:01    syntax error at or near ")"
03:16:01    LINE 17:     ) dbt_internal_test
03:16:01                 ^
03:16:01    compiled Code at target/run/jaffle_shop/tests/orders_test.sql
03:16:01
03:16:01  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Change back to 0.3.4

Update packages.yml to use 0.3.4 and then dbt clean and dbt deps again.

build in 0.3.4

~/C/jaffle_shop git:main ❯❯❯ dbt build --select orders_test                                                                                                          ✖ ✱ ◼
03:17:49  Running with dbt=1.4.5
03:17:49  Found 6 models, 21 tests, 0 snapshots, 0 analyses, 732 macros, 0 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics
03:17:49
03:17:49  Concurrency: 1 threads (target='dev')
03:17:49
03:17:50  1 of 1 START test orders_test .................................................. [RUN]
03:17:50  1 of 1 PASS orders_test ........................................................ [PASS in 0.10s]
03:17:50
03:17:50  Finished running 1 test in 0 hours 0 minutes and 0.19 seconds (0.19s).
03:17:50
03:17:50  Completed successfully
03:17:50
03:17:50  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

Notes

After a bit of digging we noticed that the file target/run/jaffle_shop/tests/orders_test.sql looked like this in 0.3.4:

select
      count(*) as failures,
      count(*) != 0 as should_warn,
      count(*) != 0 as should_error
    from (
      


  
  


  
    
    
    
    
    

    
    
    select 1 as a from (select 1) as t where False    
    
  
  
  

  

      
    ) dbt_internal_test

... but like this in 0.4.0:

select
      count(*) as failures,
      count(*) != 0 as should_warn,
      count(*) != 0 as should_error
    from (
      


  
  


  

      
    ) dbt_internal_test

... which seems to be the reason that the error is showing up as a syntax error.

Thanks for the package, hopefully this is just a simple case of me holding it wrong 😭

The text was updated successfully, but these errors were encountered:

psousa50 · 2024-01-16T10:43:30Z

Hi @davenettle, you're right, there is an issue with the build command with v0.4.0.

Please check v0.4.1, it should fix the issue.

Thank you very much for your report 👍 🙂

davenettle · 2024-01-16T22:25:30Z

Thanks for the commit. I tried it out but unfortunately I noticed something else. When I purposely fail the test, dbt test correctly shows the failure but dbt build passes when it shouldn't.

Test (note different order_id in the expect()):

{{ config(tags=['unit-test']) }}

{% call dbt_unit_testing.test ('orders',
    'it should do something with orders') %}

    {% call dbt_unit_testing.mock_ref ('seed_orders') %}
        select
            2 as order_id
            , 2 as customer_id
            , '2024-01-01'::date as order_date
            , 'completed' as status
    {% endcall %}

    {% call dbt_unit_testing.expect() %}
        select
            1 as order_id /* <-- different order id */
            , 2 as customer_id
            , '2024-01-01'::date as order_date
            , 'completed' as status
    {% endcall %}
{% endcall %}

Results in 0.3.4

dbt test

~/C/jaffle_shop git:main ❯❯❯ dbt test --select orders_test                                                                                                           ✖ ✱ ◼
22:22:14  Running with dbt=1.4.5
22:22:14  Unable to do partial parsing because saved manifest not found. Starting full parse.
22:22:15  Found 6 models, 21 tests, 0 snapshots, 0 analyses, 472 macros, 0 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics
22:22:15
22:22:15  Concurrency: 1 threads (target='dev')
22:22:15
22:22:15  1 of 1 START test orders_test .................................................. [RUN]
22:22:15  MODEL: orders
22:22:15  TEST:  it should do something with orders
22:22:15  ERROR: Rows mismatch:
22:22:15  | diff | count | order_id | customer_id | order_date | status    |
22:22:15  | ---- | ----- | -------- | ----------- | ---------- | --------- |
22:22:15  | +    |     1 |        2 |           2 | 2024-01-01 | completed |
22:22:15  | -    |     1 |        1 |           2 | 2024-01-01 | completed |
22:22:15  1 of 1 FAIL 1 orders_test ...................................................... [FAIL 1 in 0.11s]
22:22:15
22:22:15  Finished running 1 test in 0 hours 0 minutes and 0.23 seconds (0.23s).
22:22:15
22:22:15  Completed with 1 error and 0 warnings:
22:22:15
22:22:15  Failure in test orders_test (tests/orders_test.sql)
22:22:15    Got 1 result, configured to fail if != 0
22:22:15
22:22:15    compiled Code at target/compiled/jaffle_shop/tests/orders_test.sql
22:22:15
22:22:15  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

dbt build

~/C/jaffle_shop git:main ❯❯❯ dbt build --select orders_test                                                                                                        ⏎ ✖ ✱ ◼
22:22:20  Running with dbt=1.4.5
22:22:20  Found 6 models, 21 tests, 0 snapshots, 0 analyses, 472 macros, 0 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics
22:22:20
22:22:20  Concurrency: 1 threads (target='dev')
22:22:20
22:22:20  1 of 1 START test orders_test .................................................. [RUN]
22:22:20  MODEL: orders
22:22:20  TEST:  it should do something with orders
22:22:20  ERROR: Rows mismatch:
22:22:20  | diff | count | order_id | customer_id | order_date | status    |
22:22:20  | ---- | ----- | -------- | ----------- | ---------- | --------- |
22:22:20  | +    |     1 |        2 |           2 | 2024-01-01 | completed |
22:22:20  | -    |     1 |        1 |           2 | 2024-01-01 | completed |
22:22:20  1 of 1 FAIL 1 orders_test ...................................................... [FAIL 1 in 0.12s]
22:22:20
22:22:20  Finished running 1 test in 0 hours 0 minutes and 0.21 seconds (0.21s).
22:22:20
22:22:20  Completed with 1 error and 0 warnings:
22:22:20
22:22:20  Failure in test orders_test (tests/orders_test.sql)
22:22:20    Got 1 result, configured to fail if != 0
22:22:20
22:22:20    compiled Code at target/compiled/jaffle_shop/tests/orders_test.sql
22:22:20
22:22:20  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Results in 0.4.1

dbt test

Same as 0.3.4 dbt test

dbt build

~/C/jaffle_shop git:main ❯❯❯ dbt build --select orders_test                                                                                                        ⏎ ✖ ✱ ◼
22:24:05  Running with dbt=1.4.5
22:24:05  Found 6 models, 21 tests, 0 snapshots, 0 analyses, 478 macros, 0 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics
22:24:05
22:24:05  Concurrency: 1 threads (target='dev')
22:24:05
22:24:05  1 of 1 START test orders_test .................................................. [RUN]
22:24:05  1 of 1 PASS orders_test ........................................................ [PASS in 0.03s]
22:24:05
22:24:05  Finished running 1 test in 0 hours 0 minutes and 0.12 seconds (0.12s).
22:24:05
22:24:05  Completed successfully
22:24:05
22:24:05  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

psousa50 · 2024-01-17T09:52:19Z

Hi @davenettle, sorry about that. Please try v0.4.11, I hope it fixes your issue.

Thanks again for the report 👍

davenettle · 2024-01-17T22:43:57Z

Thanks @psousa50, that seems to have fixed it!

psousa50 added the question Further information is requested label Jan 17, 2024

psousa50 closed this as completed Jan 18, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

In 0.4.0, `dbt test` succeeds but `dbt build` fails #197

In 0.4.0, `dbt test` succeeds but `dbt build` fails #197

davenettle commented Jan 16, 2024

psousa50 commented Jan 16, 2024

davenettle commented Jan 16, 2024

psousa50 commented Jan 17, 2024

davenettle commented Jan 17, 2024

In 0.4.0, dbt test succeeds but dbt build fails #197

In 0.4.0, dbt test succeeds but dbt build fails #197

Comments

davenettle commented Jan 16, 2024

Run results in 0.4.0:

Change back to 0.3.4

build in 0.3.4

Notes

psousa50 commented Jan 16, 2024

davenettle commented Jan 16, 2024

Results in 0.3.4

dbt test

dbt build

Results in 0.4.1

dbt test

dbt build

psousa50 commented Jan 17, 2024

davenettle commented Jan 17, 2024

In 0.4.0, `dbt test` succeeds but `dbt build` fails #197

In 0.4.0, `dbt test` succeeds but `dbt build` fails #197