Why does dbt report success when there are actual database errors in the created objects?

[nprime496]

I'm experiencing an issue where dbt reports a successful build, but the created database object actually contains errors that only surface when queried.

dbt run output (reports success):

1 of 1 START sql view model schema.model_name [RUN]
1 of 1 OK created sql view model schema.model_name [OK in 3.75s]
Completed successfully
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

Actual database error (when querying the view):

[BAD_REQUEST] [CAST_INVALID_INPUT] The value 'text,values,here' of type "STRING" cannot be cast to "BIGINT" because it is malformed.

The "database" is Databricks.

The code causing the issue:

-- This creates a view that compiles successfully but fails at query time
union all
select
  objectid as object_id,
  CAST(activity_check AS INT),  -- Contains string data that can't be cast to INT
  -- ... other fields ...
from table_v2

My questions:

1. Is this expected behavior for dbt to run without errors even when the database don't ? ( I tried a lot of keywords to find a definitive answer in the documentation but couldn't find it)
2. Can I configure dbt to actually test/validate the created objects rather than just checking if DDL executed?
3. Are there ways to make dbt catch these runtime errors during the build process?

[benc-db]

A view has a separate existence from any data that it processes. In a sense, it's the table you're reading from in the view that has violated the contract the view is expecting. This is why view creation is fast, because it doesn't actually read the data at creation time.

[vergenzt]

To answer the latter questions:

2. Can I configure dbt to actually test/validate the created objects rather than just checking if DDL executed?
3. Are there ways to make dbt catch these runtime errors during the build process?

Yes. 🙂

One way with data_tests. However these are model-specific, so probably not the right tool for the job since you'd have to specify them for every model.

The simpler approach if you just want to confirm that each view-materialized model you've created successfully evaluates, you could add a post-hook to query it:

# in dbt_project.yml
models:
  ... 
  +post-hook:
  - '{%- if model.config.materialized == 'view' %} select * from {{ this }} {%- endif %}`

This will then attempt to select * from the view just created after each one, and should fail the overall dbt run/build if the select raises an error.

[nprime496]

Thank you very much for taking the time to answer this. I will look into that.