Schema evolution on table

[josephniblo]

Hi, me again,

Curious what you think about the ENABLE_SCHEMA_EVOLUTION option on TABLES? We are interested in using SnowDDL for managing our ingestion tables, for which we'd probably like to allow the ingestion pipeline to evolve the schema automatically.

I guess to support this, SnowDDL would have to allow the table configuration not to specify columns explicitly, or possibly to ignore changes to the underlying table?

[littleK0i]

In my view, it is a harmful option, which should not have been released in its current state.

I'll explain:

1. It can only add new columns. Manual intervention is required for pretty much all other operations.
2. Data types in source files (AVRO, Parquet, ORC, CSV) do not correspond to Snowflake data types. INFER_SCHEMA might not be able to detect the correct NUMBER size, to distringuis NUMBER and FLOAT, to distinguinsh OBJECT and ARRAY from generic VARIANT, etc. etc.
3. If I understand it correctly, comments, masking policies, defaults and many other column-related options cannot be specified.
4. Table cannot be reproduced in DEV environment, when you destroy and apply config again with env_prefix. Not developer-friendly.

---

What we can do instead in regards to SnowDDL:

(1) Use VARIANT
If source data is completely unpredictable and may change at any moment, use VARIANT to store variable part.

(2) Use is_sandbox flag and create / evolve table in pipeline code
In your data loading pipeline, you may get structure of table in Snowflake (DESC TABLE), get structure of source file, compare both and potentially produce SQL to evolve table schema.

You will be able to control every aspect of it, including types conversion, comments, any other extra options. You may skip loading some columns and pad other columns with default values.

(3) Use dynamic TableBlueprint and generate it in Python
If changes in source data structure are predictable and actually should be reviewed and taken into account, you may generate TableBlueprint dynamically based on some external schema.

You will get all the schema evolution features from SnowDDL, which supports everything, including column type changes and column order changes. And you will be able to adapt and adjust your ETL pipelines and downstream views / assets accordingly.

Raw data tables do not exist in the vacuum. Even if new column was "added" by some external force, you still need to take it into account in your transformations and in reporting layer.

---

I may consider adding this option if Snowflake makes this feature more robust and worthwhile. Until then it seems more like a gimmick or a "silver bullet" which might help sales, but creates a lot of issues down the road.

[josephniblo]

Thanks, this is very helpful in directing our thinking about whether to use the feature or not 👍