How Apache Ossie Ensures Expression Language Portability Across Snowflake, BigQuery, and Databricks

Apache Ossie ensures expression language portability by treating SQL fragments as dialect-aware semantic model components, validating them against Snowflake, BigQuery, and Databricks using sqlglot parsers while gracefully skipping unsupported dialects.

Apache Ossie is an open-source semantic modeling framework that abstracts metric definitions from underlying query engines. Expression language portability stands at the core of its design, allowing data teams to define business logic once and deploy it across heterogeneous SQL environments without syntax errors or semantic drift.

Dialect-Aware Expression Modeling

The OSIExpression Pydantic Model

In python/src/ossie/models.py (lines 106-130), Ossie defines the OSIExpression class that stores generic expressions alongside dialect-specific overrides. This model accepts a list of dialect payloads, enabling developers to specify a default ANSI SQL implementation while providing targeted overrides for Snowflake, BigQuery, or Databricks syntax variations.

Cross-Dialect Validation Pipeline

Mapping Internal Dialects to sqlglot

The validation logic resides in validation/validate.py (lines 63-70), where a static DIALECT_MAP translates Ossie's internal dialect identifiers to sqlglot-compatible strings:

DIALECT_MAP = {
    "ANSI_SQL":   None,          # sqlglot default

    "SNOWFLAKE":  "snowflake",
    "DATABRICKS": "databricks",
    "BIGQUERY":   "bigquery",
}

Two-Phase Parsing Strategy

For each expression, the validator invokes sqlglot.parse_one with the mapped dialect identifier. As implemented in validation/validate.py (lines 60-74), the system first attempts to parse the fragment as a bare expression. If parsing fails, it automatically wraps the fragment in a minimal SELECT … statement and retries, accommodating both raw expressions and partial SQL snippets.

Graceful Degradation for Non-SQL Systems

Not all dialects undergo SQL validation. The SKIP_SQL_VALIDATION list in validation/validate.py (lines 74-75) excludes non-SQL languages like MDX, TABLEAU, and MAQL from parsing checks. This prevents false-positive validation errors while maintaining strict syntax checking for supported SQL dialects.

Practical Implementation Example

When defining metrics in a semantic model YAML file, developers specify expressions per dialect:

metric = {
    "name": "total_revenue",
    "expression": {
        "dialects": [
            {"dialect": "ANSI_SQL",   "expression": "SUM(revenue)"},
            {"dialect": "SNOWFLAKE",  "expression": "SUM(REVENUE)"},
            {"dialect": "BIGQUERY",   "expression": "SUM(revenue)"},
            {"dialect": "DATABRICKS", "expression": "SUM(revenue)"}
        ]
    }
}

Running the validator ensures expression language portability across target platforms:

python validation/validate.py my_model.yaml

If a Snowflake-specific fragment contains syntax errors, the validator reports:

[SQL] Metric 'total_revenue' in model 'my_model' (SNOWFLAKE): Expected SELECT, got ...

Summary

• Dialect enumeration: Ossie supports ANSI_SQL, SNOWFLAKE, BIGQUERY, and DATABRICKS as first-class citizens in its semantic model.
• sqlglot integration: The DIALECT_MAP in validation/validate.py bridges Ossie identifiers to sqlglot parsers for syntax validation.
• Flexible parsing: The validator attempts bare expression parsing before wrapping in SELECT statements, handling diverse SQL fragment types.
• Selective validation: Non-SQL dialects (MDX, TABLEAU, MAQL) bypass parsing via SKIP_SQL_VALIDATION to avoid false negatives.
• Model-driven portability: The OSIExpression Pydantic model stores per-dialect overrides, enabling platform-specific optimizations while maintaining a single source of truth.

Frequently Asked Questions

How does Ossie handle SQL dialects not supported by sqlglot?

Ossie explicitly lists unsupported dialects such as MDX, TABLEAU, and MAQL in the SKIP_SQL_VALIDATION array within validation/validate.py (lines 74-75). These dialects bypass the sqlglot.parse_one validation step entirely, allowing semantic models to reference specialized query languages without triggering validation errors.

What happens when a BigQuery expression fails validation?

The validator outputs a specific error message indicating the metric name, model identifier, and target dialect. For example: [SQL] Metric 'revenue_calc' in model 'sales_model' (BIGQUERY): Expected expression but got .... This immediate feedback enables developers to correct dialect-specific syntax before deployment.

Can developers extend Ossie to support additional SQL dialects?

Yes, by adding entries to the DIALECT_MAP dictionary in validation/validate.py (lines 63-70) and ensuring sqlglot supports the target dialect. The modular design allows new mappings between Ossie internal identifiers and sqlglot dialect strings without modifying the core validation logic.

Where is the expression language portability logic implemented?

The primary implementation spans two files: python/src/ossie/models.py (lines 106-130) defines the OSIExpression storage model, while validation/validate.py (lines 60-75) contains the parsing logic, dialect mapping, and validation orchestration that guarantees cross-platform compatibility.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}