Support automatic table properties for Redshift/Snowflake and Incremental by Time models

[eakmanrq]

Snowflake should define the time columns as part of the cluster_by key. Redshift should define it as part of it's sort_key key. We need to make sure that if a user defines these in the table properties that it gets merged properly against what is being automatically defined by the model.

Also note that we need to establish canonical terms for these things to make them consistent across engines. Proposal: Partition By:

• Spark: Partition By
• Redshift: Not implemented
• Snowflake: Not implemented
• Bigquery: Partition By

Sort By:

• Spark (Delta): Z-order
• Redshift: Sort Key
• Snowflake: Cluster By
• Bigquery: Cluster By

Dist By:

• Spark: Not Implemented
• Redshift: Dist Key
• Snowflake: Not Implemented
• Bigquery: Not Implemented

So for example with partition by time the reasoning for the engine adapter would be this: "If I support partition by, then use that for the time column. If not, use Sort By. If I support neither then do nothing"

[erindru]

For Redshift, do you see any problem exposing DISTKEY as partition_by and removing the "Dist By" concept altogether since it only applies for Redshift?

[PiotrKatvitski]

For Redshift, do you see any problem exposing DISTKEY as partition_by and removing the "Dist By" concept altogether since it only applies for Redshift?

Dist By (aka sharding, distribution) is also supported by other DB engines, such as Greenplum and Clickhouse. Some databases provide it through PARTITION BY syntax (e.g. partition by hash), but it has vastly different use cases and deserves to be called a different thing.

By distinguishing partitioning from sharding we'll also encourage engineers using sqlmesh to find out the difference between those concepts and make the right choice.