dbt-core: [CT-1376] [Bug] Invalid Number Precision error for incremental model in 1.3
Is this a new bug in dbt-core?
- I believe this is a new bug in dbt-core
- I have searched the existing issues, and I could not find an existing issue for this bug
Current Behavior
I have an incremental model that works in version 1.2 but not in 1.3. The model has a config block of
config(
materialized='incremental',
strategy='delete+insert',
pre_hook= "{% if is_incremental() %}
TRUNCATE IF EXISTS {{ this }}
{% endif %}",
on_schema_change='sync_all_columns',
)
}}
and when I run the model in 1.3 I get the following error:
SQL compilation error: cannot change column COST_PCT from type NUMBER(38,1) to NUMBER(38,0) because changing the scale of a number is not supported.
Expected Behavior
I’m expecting this model to run incrementally without errors
Steps To Reproduce
Using version 1.2, in Snowflake, create a seed test_seed with the following data
col_1,col_2
1,2
3,4
4,5
0,100
40,10
and create an incremental model as follows:
{{
config(
materialized='incremental',
strategy='delete+insert',
pre_hook= "{% if is_incremental() %}
TRUNCATE IF EXISTS {{ this }}
{% endif %}",
on_schema_change='sync_all_columns'
)
}}
with seed_ref as (
select
to_number(col_1) as col_1,
to_number(col_2) as col_2
from {{ ref('test_seed') }}
)
select
round(col_1 / col_2 * 100, 1) as cost_pct,
round(col_1 * (col_1 / col_2), 2) as cost_dollars
from seed_ref
rerun the incremental model in version 1.3 and you will get the following error:
Invalid number precision: 39. Must be between 0 and 38.
Relevant log output
No response
Environment
- OS: Cloud IDE
- Python:n/a
- dbt: 1.3
Which database adapter are you using with dbt?
snowflake
Additional Context
No response
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Comments: 23 (15 by maintainers)
Commits related to this issue
- Troubleshooting https://github.com/dbt-labs/dbt-core/issues/6107 — committed to dbeatty10/dbt-sandcastles by dbeatty10 2 years ago
Thanks for trying that out – this helps eliminate one possibility.
You see those
describe tablecommands? When you use dbt-core/snowflake 1.2, it must be returningnumber(38, 6), but they must be changing tonumber(38, 2)when you switch to dbt-core/snowflake 1.3.When the incremental model sees the change from
number(38, 6)tonumber(38, 2), it tries to do thealter tablestep to update the data type (which fails) 💥Key question
The key question for us to figure out is:
number(38, 6)in 1.2, but thennumber(38, 2)in 1.3?What is the expression for your column that is triggering the error?
Is it really involving rounding like
round(col_1 / col_2 * 100, 2) as net_price? Or is the expression taking some other form?Out of curiosity, are you using any macros to transform the
net_pricewithin thereservations(either directly or indirectly)?Within Snowflake, the “scale and precision of the output of an arithmetic operation depends on the scale and precision of the input(s).” Each type of arithmetic operation has different rules. For example, dividing a
number(38, 0)by anumber(38, x)will yield anumber(38, 6)per these rules.Examining the data types
If you look in your
logs/dbt.logfile, you will see separate output and commands like the following for dbt-core 1.2:then:
and finally:
Then it without that macro I gave you, it might be slightly different for dbt-core 1.3 (to use a temporary view instead of a temporary table):
then:
and finally:
You could copy these sections of your log file and run them in the Snowflake web UI to inspect the data types via the
describecommands.One more question
Is the SQL logic that you see in the logs for the temporary table/view exactly the same when you run in 1.3 vs. 1.2? Or is it slightly different in some way?
@rloredo I accidentally had the repo as private 😅 – I’ve made it public now so hopefully you can access it.
@dbeatty10 I can’t access the link you shared. I can try tomorrow at work.
I have not yet been able to reproduce this personally yet. I will spin up a repo showing the steps I tried to see if anyone else can reproduce, and I’ll share a link in a follow-up comment.
Here’s where I suspect the issue is arising (which was introduced in https://github.com/dbt-labs/dbt-snowflake/pull/93).
p.s. if this is confirmed to only affect Snowflake, we’ll transfer this issue to the dbt-snowflake repo.
Hey @VersusFacit the steps to reproduce above give me the expected result. First, create the seed and incremental model with 1.2. Then, update to 1.3 and everything will fail. Even if you run a full refresh of the model in 1.3 and then run a normal run, it will fail. If you change the
on_schema_change='sync_all_columns'part or omit it, it will work.Note that this is only failing in Snowflake (at least all of us here are using SF), and for models that were first created using a 1.2 version.
+1 It seems related to on_schema_change parameter.
The workaround I’ve found is to disable the on_schema_change = ‘sync_all_columns’ and use the default that is ignore.