Why Don’t Data Types and Nullability Match Between Coalesce and Snowflake?

Mismatched Data Types

Problem:

Field lengths defined in Coalesce may not match those in Snowflake. For example, Coalesce might show VARCHAR(8) while Snowflake shows VARCHAR(167890). Changing the lengths in Coalesce and recreating the node does not fix the mismatch.

Explanation:

Mismatches between data types occur because Snowflake doesn’t require explicit data types for Dynamic Tables. The initial version of the Dynamic Table package didn’t apply data types defined in the mapping grid to the resulting SQL. This caused discrepancies between what’s defined in Coalesce and what Snowflake actually uses.

Nullability Settings Not Applied

Problem:

Nullable field settings defined in Coalesce don’t always match the behavior in Snowflake. For example, a field marked as FALSE in Coalesce may appear as NULLABLE in Snowflake.

Explanation:

Dynamic Tables don’t support explicit nullability declarations in their syntax. Instead, nullability is inferred from the upstream source tables. This means any nullability settings defined in the Coalesce mapping grid won’t be reflected in the final table in Snowflake.

To control nullability, make sure upstream nodes define the desired null behavior.

To Fix

As of Dynamic Tables version 1.1.10 of our Dynamic Tables package, data types are now included in the CREATE OR REPLACE statements. Any new Dynamic Table nodes created with this version or later will automatically apply the data types defined in the mapping grid.

If you’re still seeing mismatches, confirm that your project is using version 1.1.10 or higher.