# unit\_test: wrong data\_type length

**URL:** <https://discourse.getdbt.com/t/unit-test-wrong-data-type-length/19888>\
**Category:** Help\
**Tags:** testing, snowflake, dbt-core\
**Created:** [July 18, 2025, 10:27am UTC](https://discourse.getdbt.com/t/unit-test-wrong-data-type-length/19888 "2025-07-18T10:27:26Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Florian1](https://avatars.discourse-cdn.com/v4/letter/f/c2a13f/32.png) [@Florian1](https://discourse.getdbt.com/u/Florian1)\
**Post date:** [July 18, 2025, 10:27am UTC](https://discourse.getdbt.com/t/unit-test-wrong-data-type-length/19888/1 "2025-07-18T10:27:26Z")

</div>

I have an issue with the data type length in a unit test. The database is Snowflake.

### Unit test basis

- the tested model has as source the view ‘TECH’.

```auto
create or replace view TECH_SCHEMA.TECH(
	TECH_ID,
	SYSTEM,
	STATUS,
	INSERTED_AT
) as
select TECH_ID,
        SYSTEM,
        STATUS,
        TECH_INSERTED_AT
from TECH_SCHEMA.TECH_HIST
qualify ROW_NUMBER() OVER (PARTITION BY TECH_ID
                     ORDER BY "INSERTED_AT" DESC) = 1
;

```

- this view ‘TECH’ is based on the table ‘TECH\_HIST’:

```auto
create or replace TABLE TECH_SCHEMA.TECH_HIST (
	TECH_ID NUMBER(38,0) NOT NULL,
	SYSTEM VARCHAR(16777216),
	STATUS NUMBER(4,0) DEFAULT 0,
	INSERTED_AT TIMESTAMP_NTZ(9) DEFAULT CURRENT_TIMESTAMP()
);

```

- view ‘TECH’ and table ‘TECH\_HIST’ are created and maintained outside dbt

In the unit test the input for ‘status’ in the source ‘TECH’ is set to ‘1000’:

```auto
    given:
      - input: source('TECH_SCHEMA', 'TECH')
        rows:
          - {tech_id: '1', system: 'test_system', tech_status: '1000', inserted_at: '1999-01-01 00:00:00'}

```

### Unit test run

However, the compiled code for the source ‘TECH’ looks like:

```auto
-- Fixture for TECH
select 
    
        try_cast('1' as NUMBER(38,0))
     as tech_id, 
    
        try_cast('test_system' as character varying(16777216))
     as system, 
    
        try_cast('1000' as NUMBER(3,0))
     as status,
	
        try_cast('1999-01-01 00:00:00' as TIMESTAMP_NTZ)
     as tech_inserted_at_ts

```

I have no clue why ‘status’ is of type NUMBER(3,0) here and not NUMBER(4,0) as it is defined in the table. - This, of course, leads to a unit\_test fail.  
Anyone any tips / hints / ideas what might go wrong here?

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [July 18, 2025, 3:21pm UTC](https://discourse.getdbt.com/t/unit-test-wrong-data-type-length/19888/2 "2025-07-18T15:21:48Z")

</div>

dbt tests infer data types automatically which explains why you’re seeing this issue. There’s a couple possible paths forward and they all rely on setting an explicit data type on that column in a yaml file. There is an open github issue for data types in testing: [https://github.com/dbt-labs/dbt-core/issues/9884](https://github.com/dbt-labs/dbt-core/issues/9884)

source configuration:

````auto
sources:
  - name: TECH_SCHEMA
    tables:
      - name: TECH
        columns:
          - name: tech_id
            data_type: number(38,0)
          - name: system
            data_type: varchar(16777216)
          - name: status
            data_type: number(4,0) # Explicit definition
          - name: inserted_at
            data_type: timestamp_ntz(9)```
model contract
```models:
  - name: your_model
    config:
      contract:
        enforced: true
    columns:
      - name: status
        data_type: number(4,0)```
Try that and see if it helps. More on data types here: <https://docs.getdbt.com/reference/resource-properties/data-types>

<sub>Note: `@Abigail Green (CHG Healthcare)` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1752852041200739?thread_ts=1752834469.771399&cid=CBSQTAPLG). It might not have transferred perfectly.</sub>
````

---

<div class="post-metadata">

**Author:** ![Florian1](https://avatars.discourse-cdn.com/v4/letter/f/c2a13f/32.png) [@Florian1](https://discourse.getdbt.com/u/Florian1)\
**Post date:** [July 28, 2025, 7:11am UTC](https://discourse.getdbt.com/t/unit-test-wrong-data-type-length/19888/3 "2025-07-28T07:11:19Z")

</div>

Thank you for the hint. I added the information to my source configuration and the contract to my tested model, as suggested. However, the result is the same.

```auto
try_cast('1000' as NUMBER(3,0))
     as status,

```

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [July 28, 2025, 2:54pm UTC](https://discourse.getdbt.com/t/unit-test-wrong-data-type-length/19888/4 "2025-07-28T14:54:46Z")

</div>

Did you set the data type in your Yaml file for the model?

Note: `@Abigail Green (CHG Healthcare)` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1753714476760289?thread_ts=1752834469.771399&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![Florian1](https://avatars.discourse-cdn.com/v4/letter/f/c2a13f/32.png) [@Florian1](https://discourse.getdbt.com/u/Florian1)\
**Post date:** [July 29, 2025, 6:47am UTC](https://discourse.getdbt.com/t/unit-test-wrong-data-type-length/19888/5 "2025-07-29T06:47:54Z")

</div>

Yes, I set the “sources” part in my SOURCES.yml and the “models” part in my TECH\_TARGET.yml - Like suggested in your previous post

---

<div class="post-metadata">

**Author:** ![Florian1](https://avatars.discourse-cdn.com/v4/letter/f/c2a13f/32.png) [@Florian1](https://discourse.getdbt.com/u/Florian1)\
**Post date:** [August 4, 2025, 10:57am UTC](https://discourse.getdbt.com/t/unit-test-wrong-data-type-length/19888/6 "2025-08-04T10:57:11Z")

</div>

I finally found a solution: I had to run the create statement of the view again (no change to it). Now it works. Even without contract and explicit data type definition.

Cause: The table was recreated after the view was created, including a new length for the column. Since the view showed the data with the new length, this was not noticed before.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [August 11, 2025, 10:57am UTC](https://discourse.getdbt.com/t/unit-test-wrong-data-type-length/19888/7 "2025-08-11T10:57:14Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.