Add Default Dialect at Dataset Level

[dmzolotarev]

Currently, every field must specify its dialect.

But can it possibly be that in one dataset fields will be from a different dialects?
I'm struggling to come up with such usecases, but it any way, we can move dialect to a dataset level, with an option to override it on field level. Fields will inherit dataset-level dialect unless they specify their own dialect

This will reduce verbosity.

Before (verbose):

datasets:
  - name: orders
    source: ANALYTICS.PUBLIC.ORDERS
    fields:
      - name: order_id
        expression:
          dialects:
            - dialect: SNOWFLAKE
              expression: order_id
      - name: amount
        expression:
          dialects:
            - dialect: SNOWFLAKE
              expression: amount

After (concise):

datasets:
  - name: orders
    source: ANALYTICS.PUBLIC.ORDERS
    dialect: SNOWFLAKE           # NEW
    fields:
      - name: order_id
        expression: ORDER_ID             # Uses dataset dialect
      - name: amount                 # Override for multi-dialect
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: amount

[cpimhoff]

I would say that a default SQL dialect may be required at an even higher layer: the whole model. Joining between a dataset sourced in Snowflake vs. a dataset sourced in Databricks doesn't really make much sense, and any consumer of an OSI needs to know what host SQL language to write JOIN statements in between datasets.

[dmzolotarev]

agree!

[mullinsms]

There are existing semantic layers on the market that span data platforms. So you want to optimize the pushdown on each platform before performing the join.

[sfc-gh-wpugh]

I would pull this apart into two parts:

1. How much of the DB specific translation is owned by the OSI provider. E.g. when converting from an OSI query to the SQL query, the provider does the right optimizations / pushdown / etc
2. How do we provide vendor extensions

Directionally, I would like some standard expression language that OSI supports that providers can optimize to have good queries. Most models should be able to be built entirely on this.

Then, for the second case, where you need vendor specific features, the best practice would be:

• Have a core semantic model that is vendor agnostic
• Have a vendor specific model that builds on top of the vendor agnostic
• Ensure our composition model allows you to do this reasonably

[sfc-gh-wpugh]

Yeah. I think this comes down to how we think people are going to use these.

In a world where there is one semantic model that is deployed to several providers, then having different dialects at the field could make sense.

However, if you are going to have one model per provider, and some form of composition to re-use the parts that are common across all, then having the dialect at the top level makes sense.

[khush-bhatia]

dmzolotarev and cpimhoff

Agreed on having a default dialect field at the model level. Does one of you want to create a PR with this change ?