# How Ossie Handles Multiple SQL Dialects: Native Multi-Database Expression Support

> Discover how Apache Ossie supports multiple SQL dialects by storing logical expressions as dialect-specific SQL renderings. It automatically selects variants and falls back to ANSI SQL when necessary.

- Repository: [The Apache Software Foundation/ossie](https://github.com/apache/ossie)
- Tags: deep-dive
- Published: 2026-07-24

---

**Apache Ossie stores every logical expression as a set of dialect-specific SQL renderings, allowing converters to automatically select the appropriate variant for each target platform while falling back to ANSI SQL when needed.**

Ossie is an open-source semantic modeling framework designed to eliminate vendor lock-in by letting analytics teams define metrics and fields once and deploy them across heterogeneous data platforms. Instead of relying on runtime SQL translation, Ossie treats each expression as a collection of dialect-specific variants, ensuring consistent semantics across multiple SQL dialects including Snowflake, Databricks, and BigQuery.

## The Core Expression Object Model

### Dialect Enumeration in the Specification

The Ossie core specification defines a comprehensive enumeration of supported dialect identifiers in the **Dialects** table of [`core-spec/spec.md`](https://github.com/apache/ossie/blob/main/core-spec/spec.md). Valid values include `ANSI_SQL`, `SNOWFLAKE`, `DATABRICKS`, `BIGQUERY`, `MDX`, `TABLEAU`, and `MAQL`, among others. The [`core-spec/expression_language.md`](https://github.com/apache/ossie/blob/main/core-spec/expression_language.md) file further describes the portable subset of SQL that all implementations must support, ensuring that `ANSI_SQL` expressions behave consistently across converters.

### The Dialects Array Structure

Every field or metric in Ossie contains an **`expression`** object whose `dialects` array holds one or more entries. Each entry specifies a `dialect` (matching the enumeration) and the concrete SQL text for that dialect. According to `core-spec/spec.md#Expression-Object`, this structure allows a single logical concept to carry multiple physical implementations optimized for different engines.

```yaml

# A field with three dialect versions

- name: email_normalized
  expression:
    dialects:
      - dialect: ANSI_SQL
        expression: LOWER(email)
      - dialect: SNOWFLAKE
        expression: LOWER(email)::VARCHAR
      - dialect: BIGQUERY
        expression: SAFE_CAST(LOWER(email) AS STRING)
  description: Normalized email address

```

## Converter Dialect Selection Logic

When a converter exports the model to a specific platform, it implements a deterministic selection algorithm documented in `converters/README.md#Expression-dialect-selection`. The converter scans the `dialects` list and selects the entry whose `dialect` value matches the target platform identifier.

If the platform-specific dialect is absent, the converter automatically falls back to the generic `ANSI_SQL` expression. This guarantees that models remain functional even when optimized variants for every dialect are not provided. Concrete command-line usage and dialect handling examples are provided in the Omni converter documentation at [`converters/omni/README.md`](https://github.com/apache/ossie/blob/main/converters/omni/README.md).

```yaml

# A metric that prefers Snowflake but falls back to ANSI_SQL

- name: total_revenue
  expression:
    dialects:
      - dialect: SNOWFLAKE
        expression: SUM(ORDER_AMT)
      - dialect: ANSI_SQL
        expression: SUM(orders.amount)
  description: Total revenue across all orders

```

## Fallback Behavior and Error Handling

When neither a platform-specific dialect nor `ANSI_SQL` is available, converter behavior depends on the target tool's capabilities. Converters may emit a warning such as `"UNSUPPORTED_DIALECT"` or drop the metric entirely to prevent runtime errors. The Wisdom and Orionbelt converter implementations document these specific error-handling strategies.

## Extensibility and Future-Proofing

The Ossie specification allows new dialect identifiers to be added to the enumeration as the analytics ecosystem evolves. Converters can be extended to recognize these new identifiers while maintaining the deterministic fallback path to `ANSI_SQL`. This architecture ensures that semantic models remain portable and future-proof without requiring rewrites when new databases emerge.

## Summary

- Ossie stores expressions as **arrays of dialect-specific SQL variants** rather than single strings.
- The [`core-spec/spec.md`](https://github.com/apache/ossie/blob/main/core-spec/spec.md) defines the **Dialects enumeration** and **Expression Object** structure.
- Converters automatically select the best matching dialect and **fall back to ANSI_SQL** when needed.
- Unsupported dialects trigger warnings or metric suppression based on converter configuration.
- The model is **extensible**, allowing new dialects to be added without breaking existing implementations.

## Frequently Asked Questions

### What SQL dialects does Ossie support out of the box?

The Ossie core specification defines dialect identifiers including `ANSI_SQL`, `SNOWFLAKE`, `DATABRICKS`, `BIGQUERY`, `MDX`, `TABLEAU`, and `MAQL`. This list is maintained in the Dialects table of [`core-spec/spec.md`](https://github.com/apache/ossie/blob/main/core-spec/spec.md).

### How does Ossie handle unsupported SQL dialects?

When a converter encounters an expression without a matching dialect or valid `ANSI_SQL` fallback, it either emits a warning (such as `"UNSUPPORTED_DIALECT"`) or drops the metric entirely, depending on the specific converter implementation and target tool limitations.

### Can I add custom dialects to the Ossie specification?

Yes, the specification is designed for extensibility. New dialect identifiers can be added to the enumeration, and converters can be extended to recognize them while maintaining backward compatibility through the `ANSI_SQL` fallback mechanism.

### Does Ossie automatically translate SQL between dialects?

No, Ossie does not perform automatic SQL translation. Instead, it relies on model authors to provide explicit dialect variants for each expression. Converters then select the appropriate pre-written variant, ensuring predictable behavior and optimal performance on each platform.