Multi-Dialect Output¶
This example shows how the same semantic model and query produce different SQL for each of the eight supported dialects.
The Model¶
Using the Sales Model with Customers, Products, and Orders data objects.
The Query¶
Revenue by customer country, filtered to SMB/MidMarket segments, ordered by revenue descending, limited to 1000 rows.
from orionbelt.models.query import *
query = QueryObject(
select=QuerySelect(
dimensions=["Customer Country"],
measures=["Revenue"],
),
where=[
QueryFilter(field="Customer Segment", op=FilterOperator.IN, value=["SMB", "MidMarket"]),
],
order_by=[QueryOrderBy(field="Revenue", direction=SortDirection.DESC)],
limit=1000,
)
Generated SQL by Dialect¶
SELECT
`Customers`.`COUNTRY` AS `Customer Country`,
CAST(SUM((`Orders`.`PRICE` * `Orders`.`QUANTITY`)) AS NUMERIC(18, 2)) AS `Revenue`
FROM `WAREHOUSE`.`PUBLIC`.`ORDERS` AS `Orders`
LEFT JOIN `WAREHOUSE`.`PUBLIC`.`CUSTOMERS` AS `Customers`
ON (`Orders`.`CUSTOMER_ID` = `Customers`.`CUSTOMER_ID`)
WHERE (`Customers`.`SEGMENT` IN ('SMB', 'MidMarket'))
GROUP BY `Customers`.`COUNTRY`
ORDER BY SUM((`Orders`.`PRICE` * `Orders`.`QUANTITY`)) DESC
LIMIT 1000
Key traits: Backtick-quoted identifiers, NUMERIC for decimal types, DATE_TRUNC() with column-first argument, LOWER() + LIKE for string matching, supports QUALIFY and semi-structured types.
SELECT
"Customers"."COUNTRY" AS "Customer Country",
CAST(SUM(("Orders"."PRICE" * "Orders"."QUANTITY")) AS Decimal(18, 2)) AS "Revenue"
FROM "PUBLIC"."ORDERS" AS "Orders"
LEFT JOIN "PUBLIC"."CUSTOMERS" AS "Customers"
ON ("Orders"."CUSTOMER_ID" = "Customers"."CUSTOMER_ID")
WHERE ("Customers"."SEGMENT" IN ('SMB', 'MidMarket'))
GROUP BY "Customers"."COUNTRY"
ORDER BY SUM(("Orders"."PRICE" * "Orders"."QUANTITY")) DESC
LIMIT 1000
Key traits: Double-quoted identifiers, Decimal(p, s) type syntax, custom time functions (toStartOfMonth(), toStartOfYear()), native type conversion (toInt64(), toFloat64()).
SELECT
`Customers`.`COUNTRY` AS `Customer Country`,
CAST(SUM((`Orders`.`PRICE` * `Orders`.`QUANTITY`)) AS DECIMAL(18, 2)) AS `Revenue`
FROM `WAREHOUSE`.`PUBLIC`.`ORDERS` AS `Orders`
LEFT JOIN `WAREHOUSE`.`PUBLIC`.`CUSTOMERS` AS `Customers`
ON (`Orders`.`CUSTOMER_ID` = `Customers`.`CUSTOMER_ID`)
WHERE (`Customers`.`SEGMENT` IN ('SMB', 'MidMarket'))
GROUP BY `Customers`.`COUNTRY`
ORDER BY SUM((`Orders`.`PRICE` * `Orders`.`QUANTITY`)) DESC
LIMIT 1000
Key traits: Backtick-quoted identifiers (Spark SQL), DECIMAL type, date_trunc() for time grains, lower() + LIKE for case-insensitive matching.
SELECT
"Customers"."COUNTRY" AS "Customer Country",
CAST(SUM(("Orders"."PRICE" * "Orders"."QUANTITY")) AS DECIMAL(18, 2)) AS "Revenue"
FROM "WAREHOUSE"."PUBLIC"."ORDERS" AS "Orders"
LEFT JOIN "WAREHOUSE"."PUBLIC"."CUSTOMERS" AS "Customers"
ON ("Orders"."CUSTOMER_ID" = "Customers"."CUSTOMER_ID")
WHERE ("Customers"."SEGMENT" IN ('SMB', 'MidMarket'))
GROUP BY "Customers"."COUNTRY"
ORDER BY SUM(("Orders"."PRICE" * "Orders"."QUANTITY")) DESC
LIMIT 1000
Key traits: Double-quoted identifiers, DECIMAL type, DATE_TRUNC(), no ILIKE support (uses LOWER() + LIKE workaround), minimal capability set.
SELECT
"Customers"."COUNTRY" AS "Customer Country",
CAST(SUM(("Orders"."PRICE" * "Orders"."QUANTITY")) AS DECIMAL(18, 2)) AS "Revenue"
FROM "PUBLIC"."ORDERS" AS "Orders"
LEFT JOIN "PUBLIC"."CUSTOMERS" AS "Customers"
ON ("Orders"."CUSTOMER_ID" = "Customers"."CUSTOMER_ID")
WHERE ("Customers"."SEGMENT" IN ('SMB', 'MidMarket'))
GROUP BY "Customers"."COUNTRY"
ORDER BY SUM(("Orders"."PRICE" * "Orders"."QUANTITY")) DESC
LIMIT 1000
Key traits: Double-quoted identifiers (PostgreSQL-compatible), DECIMAL type, two-part table refs, date_trunc(), ILIKE, UNION ALL BY NAME.
SELECT
"Customers"."COUNTRY" AS "Customer Country",
CAST(SUM(("Orders"."PRICE" * "Orders"."QUANTITY")) AS NUMERIC(18, 2)) AS "Revenue"
FROM "PUBLIC"."ORDERS" AS "Orders"
LEFT JOIN "PUBLIC"."CUSTOMERS" AS "Customers"
ON ("Orders"."CUSTOMER_ID" = "Customers"."CUSTOMER_ID")
WHERE ("Customers"."SEGMENT" IN ('SMB', 'MidMarket'))
GROUP BY "Customers"."COUNTRY"
ORDER BY SUM(("Orders"."PRICE" * "Orders"."QUANTITY")) DESC
LIMIT 1000
Key traits: Double-quoted identifiers, NUMERIC type, date_trunc() for time grains, ILIKE for case-insensitive matching.
SELECT
"Customers"."COUNTRY" AS "Customer Country",
CAST(SUM(("Orders"."PRICE" * "Orders"."QUANTITY")) AS NUMBER(18, 2)) AS "Revenue"
FROM "WAREHOUSE"."PUBLIC"."ORDERS" AS "Orders"
LEFT JOIN "WAREHOUSE"."PUBLIC"."CUSTOMERS" AS "Customers"
ON ("Orders"."CUSTOMER_ID" = "Customers"."CUSTOMER_ID")
WHERE ("Customers"."SEGMENT" IN ('SMB', 'MidMarket'))
GROUP BY "Customers"."COUNTRY"
ORDER BY SUM(("Orders"."PRICE" * "Orders"."QUANTITY")) DESC
LIMIT 1000
Key traits: Double-quoted identifiers (case-sensitive), NUMBER type, DATE_TRUNC() (uppercase), CONTAINS() for string matching, supports QUALIFY and window filters.
Key Differences¶
Identifier Quoting¶
| Dialect | Style | Example |
|---|---|---|
| ClickHouse, Dremio, DuckDB, Postgres, Snowflake | Double quotes | "column" |
| BigQuery, Databricks | Backticks | `column` |
Time Grain: Monthly Aggregation¶
If the query included "Order Date:month" as a dimension:
String Contains Filter¶
If the query filtered with { field: "Customer Country", op: "contains", value: "United" }:
Compiling for All Dialects¶
from orionbelt.compiler.pipeline import CompilationPipeline
pipeline = CompilationPipeline()
for dialect in ["bigquery", "clickhouse", "databricks", "dremio", "duckdb", "mysql", "postgres", "snowflake"]:
result = pipeline.compile(query, model, dialect)
print(f"--- {dialect} ---")
print(result.sql)
print()