Expand data type support

[minghungcho]

It would be great if SnowDDL can support use the data type such as STRING or INTEGER.

I am finding myself copying & pasting the following "equivalent" type when working on a SnowDDL yaml file because in many cases I model table with STRING, INTEGER, and DATETIME type. Since Snowflake internally finds the right storage capacity, in many BI design, the exact data type is not important. So if developers can simply model with those data types, it would simplify the maintenance of YAML files.

If SnowDDL can accept the following types from YAML, and convert them to equivalent actual types before Resolver kicks in, it would be convenient.

STRING to VARCHAR(16777216)
INT or INTEGER to NUMBER(38, 0)
DATETIME ito TIMESTAMP_NTZ

[littleK0i]

It seems like a good idea at first, but we ended up enforcing "actual" Snowflake data types and prohibiting "aliases" in config for a few good reasons.

1) It causes confusion for users

INTEGER alias makes it look like Snowflake supports true INTEGER type, but it does not.

For example, try running this query in Snowflake and in PostgreSQL:

SELECT 5::INT / 2;
-- snowflake: 2.5 (because it is actually DECIMAL)
-- postgres: 2 (because it is true INT)

Also, MIN and MAX values are different. If you have someone less experienced with Snowflake working with this code or config, it might cause a lot of confusion and ambiguity.

STRING alias makes it look like Snowflake supports strings of unlimited length, like Postgres, which is not true. Also, VARCHAR columns with maximum length (16777216) cause some Snowflake drivers to allocate disproportionate amount of RAM for datasets. Some software may actually run out of memory, and it is impossible to reduce VARCHAR length without re-creating the whole table.

DATETIME alias hides the fact that Snowflake has multiple TIMESTAMP_* types with different treatment of timezones.

2) It causes technical problems

VARCHAR columns with maximum length (16777216) cause some Snowflake drivers to allocate disproportionate amount of RAM for datasets. Some software may actually run out of memory while reading relatively small amounts of data, and it is impossible to reduce VARCHAR length without re-creating the whole table.

3) Aliases might be added or changed without prior notice

For example, consider this less documented example:

SELECT 'abc'::NVARCHAR2(3 BYTE);

Even if I add some aliases to the code, Snowflake will secretly have more aliases. And currently there is no way to find out all possible combinations.

---

After some careful consideration, we issued the following recommendations:

• Study documentation of "real" data types provided by Snowflake. Do not make any assumptions based on previous DBMS experience;
• Always use "real" data types in config to prevent confusion;
• Avoid using biggest data type sizes automatically (e.g. NUMBER(38,0), VARCHAR(16777216)). Think ahead and use smallest reasonable size instead;

Yes, it does cause some extra work. Yes, smaller types can lead to slightly more errors during ETL process.

But it pays off in the long run, because data engineers are more aware of Snowflake internals and specifics related to data stored in account.