How to control dbt model's column datatype?

Help
,
1

Problem

We are in the process of migrating the transformation logic written in stored procedures to dbt. One of the challenges that we have already identified as part of migration is the dbt model’s column datatype not matching with the one currently being transformed through stored procedure. In stored procedures, we are just transforming the data whereas the schema of any table is defined upfront by the database admins. But I believe when we shift to dbt, the schema of a dbt model is controlled within dbt itself since it re-creates the table (everytime or during full-refreshes depending on the materialization) with the datatypes based on the data returned by the model query.

Example

For example, one of the column datatype is NUMBER(38,0) originally. When migrated to dbt, the same column datatype is NUMBER(18,5). For now, we have done explicit datatype casting in the model sql. But this seems to be too much of effort when we take into consideration many of the tables have such issues.

Are there any better alternatives to have control over column datatypes and the overall schema of a model in dbt?

2

You could try using contracts with data types contract | dbt Developer Hub

But now I am not sure if it will cast your column to the right type, or just tell you if it matches the defined data type. Anyway, I would give it a try