Expectations around SQL expression dialects and conversion

[cpimhoff]

The current version of the specification’s expression sub properties defines a dialect field which encodes how to parse the expression. The expectations around this enumeration is currently underspecified.

• ANSI_SQL
• SNOWFLAKE
• MDX
• TABLEAU

Let’s focus on ANSI_SQL and SNOWFLAKE. These are both SQL dialects, and Snowflake SQL has a lot of overlap with ANSI_SQL. The inclusion of these two naturally begs the question: “well what about other SQL dialects?”. Why does DUCKDB_SQL not end up here? How about MSSQL?

I believe the intent of this field is to grow to support many SQL dialects, and that importers and exporters would not be performing translations of one to another. For example, attempting to importing an OSI entity with a MSSQL expression into Snowflake would result in an error. If not, if the expectation is that Snowflake’s importer is expected to convert MSSQL to SNOWFLAKE then … errr … I am skeptical of that requirement for importers.

If my understanding is correct, I think some language about that expectation should be encoded into the specification.

When OSI expressions are imported into downstream systems, they are expected to import the first expression implementation in a dialect type they support.
When OSI expressions are exported from upstream systems, they are expected to fill in only expression dialects which have been validated to actual work. Exporters should not proactively add expressions in additional dialects as a “convenience”. For example, an exporter from Snowflake semantic views into OSI should not fill in both SNOWFLAKE_SQL and also ANSI_SQL, since the contents of Snowflake view have only been executed in Snowflake, not a strict ANSI environment.

Also, I would suggest normalizing SQL dialect naming to ANSI_SQL and SNOWFLAKE_SQL. 😄