> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/references/interfaces/apis/data-product-apis/querying-data-products/semantic-sql.md).

# Semantic SQL

A SQL-oriented interface for analyzing governed business definitions.

Use Semantic SQL when analysts want SQL authoring while keeping calculations governed by the Data Product. The query references semantic models and members; Vulcan compiles it into warehouse SQL before execution.

Semantic SQL is a strong fit for exploration, notebooks, and SQL-native tools. Prefer [REST](https://v2.dataos.info/references/interfaces/apis/data-product-apis/querying-data-products/rest.md) for programmatically generated queries because structured JSON is safer to compose than SQL strings.

**Endpoint:** `POST /api/v1/query/semantic/sql`

## Query rules

* Submit read-only `SELECT` statements.
* Reference dimensions as `<model>.<dimension>`.
* Wrap every governed measure in `MEASURE(<model>.<measure>)`.
* Group by selected dimensions when the query contains a measure.
* Use `WHERE` for dimensions and segments; use `HAVING` for aggregated measures.
* Use a semantic join only when metadata defines a valid relationship between the models.
* Do not submit DML or DDL such as `INSERT`, `UPDATE`, `DELETE`, `CREATE`, `DROP`, or `ALTER`.

> **Please note that the query format described here is the only supported one.** Ad-hoc queries that use native MySQL functions or syntax outside this format may only work partially or silently return incorrect results, so we recommend avoiding them.

## Semantic model mapping

Every semantic model and metric in your Data Product is exposed as a **table**. Its measures and dimensions become **columns**.

| Semantic layer concept   | SQL representation                                     |
| ------------------------ | ------------------------------------------------------ |
| Semantic model or metric | Table                                                  |
| Dimension                | Column. Select directly.                               |
| Measure                  | Column. Wrap with `MEASURE()` or a matching aggregate. |
| Segment                  | Boolean column: `WHERE seg IS TRUE`.                   |

## Querying a model

Treat a semantic model or metric like any table:

```sql
SELECT
  <dimension>,
  <measure>
FROM <table>
WHERE <filter_by_dimension>
GROUP BY <dimension>
HAVING <filter_by_measure>
ORDER BY <dimension>, <measure>
LIMIT <n>
OFFSET <n>;
```

## Dimensions

Dimensions are plain columns. Reference them in `SELECT`, `WHERE`, `GROUP BY`, or `ORDER BY`:

```sql
SELECT status
FROM orders;
```

Filter by a dimension:

```sql
SELECT status
FROM orders
WHERE status = 'delivered';
```

## Measures

Wrap measures with the `MEASURE()` aggregate, or with an aggregate that matches the measure type (see below).

```sql
SELECT MEASURE(count)
FROM orders;
```

> **Use `MEASURE()` by default.** It works for every measure type and avoids type mismatch errors.

`MEASURE()` works with every measure type. You can also use a type-specific aggregate, but it has to match exactly. Otherwise the API throws `Measure aggregation type doesn't match`.

| Measure type     | Supported aggregates                      |
| ---------------- | ----------------------------------------- |
| `avg`            | `MEASURE` or `AVG`                        |
| `boolean`        | `MEASURE`                                 |
| `count`          | `MEASURE` or `COUNT`                      |
| `count_distinct` | `MEASURE` or `COUNT(DISTINCT ...)`        |
| `max`            | `MEASURE` or `MAX`                        |
| `min`            | `MEASURE` or `MIN`                        |
| `number`         | `MEASURE` or any function from this table |
| `string`         | `MEASURE` or `STRING_AGG`                 |
| `sum`            | `MEASURE` or `SUM`                        |
| `time`           | `MEASURE`, `MAX`, or `MIN`                |

## Joining models

The semantic layer already defines relationships between models. To query across models, use one of three forms: `CROSS JOIN`, comma-separated tables, or `LEFT JOIN` with the `__joinField` key.

`__joinField` is a virtual column on every model. It carries the join key the semantic layer uses. You do not define the join condition; you reference `__joinField` on both sides.

`LEFT JOIN` using `__joinField`:

```sql
SELECT p.name, SUM(o.count)
FROM orders o
LEFT JOIN products p ON o.__joinField = p.__joinField
GROUP BY 1
LIMIT 5;
```

`CROSS JOIN`:

```sql
SELECT p.name, MEASURE(o.count)
FROM orders o
CROSS JOIN products p
GROUP BY 1
LIMIT 5;
```

Comma-separated (implicit join):

```sql
SELECT p.name, MEASURE(o.count)
FROM orders o, products p
GROUP BY 1
LIMIT 5;
```

All three are equivalent. Pick whichever reads most clearly.

## Supported filter operators

| Operator      | Description                                                | Types                      | MySQL syntax                                                                    |
| ------------- | ---------------------------------------------------------- | -------------------------- | ------------------------------------------------------------------------------- |
| `IN`          | Exact match. Supports multiple values.                     | `string`, `number`, `time` | `WHERE customers.country IN ('US', 'UK', 'India')`                              |
| `NOT IN`      | Inverse of `IN`. Supports multiple values.                 | `string`, `number`, `time` | `WHERE customers.country NOT IN ('France')`                                     |
| `LIKE`        | Case-insensitive wildcard match. Supports multiple values. | `string`                   | `WHERE products.brand LIKE '%nike%' OR products.brand LIKE '%adidas%'`          |
| `NOT LIKE`    | Inverse of `LIKE`. Supports multiple values.               | `string`                   | `WHERE products.brand NOT LIKE '%nike%' AND products.brand NOT LIKE '%adidas%'` |
| `>`           | Greater than.                                              | `number`                   | `WHERE orders.total_units > 100`                                                |
| `>=`          | Greater than or equal to.                                  | `number`                   | `WHERE orders.total_units >= 100`                                               |
| `<`           | Less than.                                                 | `number`                   | `WHERE orders.total_units < 100`                                                |
| `<=`          | Less than or equal to.                                     | `number`                   | `WHERE orders.total_units <= 100`                                               |
| `IS NOT NULL` | Member is not `NULL`.                                      | `number`, `string`, `time` | `WHERE customers.phone_number IS NOT NULL`                                      |
| `IS NULL`     | Member is `NULL`.                                          | `number`, `string`, `time` | `WHERE customers.phone_number IS NULL`                                          |
| `BETWEEN`     | Restricts a time dimension to a date range.                | `time`                     | `WHERE orders.order_date BETWEEN '2025-01-01' AND '2025-12-31'`                 |
| `NOT BETWEEN` | Inverse of `BETWEEN`.                                      | `time`                     | `WHERE orders.order_date NOT BETWEEN '2025-01-01' AND '2025-12-31'`             |
| `<` (date)    | Everything before a date.                                  | `time`                     | `WHERE orders.order_date < '2025-01-01'`                                        |
| `>` (date)    | Everything after a date.                                   | `time`                     | `WHERE orders.order_date > '2025-01-01'`                                        |

### Scenario 1: One dimension, one measure, one filter

Total order count per status, delivered orders only:

```sql
SELECT
  status,
  MEASURE(count)
FROM orders
WHERE status = 'delivered'
GROUP BY status
ORDER BY MEASURE(count) DESC
LIMIT 10;
```

### Scenario 2: One dimension, multiple measures, with a filter

Order count, total revenue, and average order value per store region, current year only:

```sql
SELECT
  store_region,
  MEASURE(count)           AS total_orders,
  MEASURE(revenue)         AS total_revenue,
  MEASURE(avg_order_value) AS avg_value
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY store_region
ORDER BY MEASURE(revenue) DESC
LIMIT 10;
```

### Scenario 3: Two models with a cross-model measure

Total order count per product name, across `orders` and `products`, limited to 2025:

```sql
SELECT
  p.name           AS product_name,
  MEASURE(o.count) AS total_orders
FROM orders o, products p
WHERE o.order_date >= '2025-01-01'
GROUP BY p.name
ORDER BY MEASURE(o.count) DESC
LIMIT 10;
```

### Scenario 4: Two models with `WHERE` and `HAVING`

Product name and total revenue for products with more than 500 delivered orders:

```sql
SELECT
  p.name             AS product_name,
  MEASURE(o.count)   AS total_orders,
  MEASURE(o.revenue) AS total_revenue
FROM orders o, products p
WHERE o.status = 'delivered'
GROUP BY p.name
HAVING MEASURE(o.count) > 500
ORDER BY MEASURE(o.revenue) DESC
LIMIT 25;
```

## Quick reference

### Introspection

Use these to see what is available before writing queries:

```sql
-- List all semantic models in the connected data product
SHOW TABLES;

-- Distinguish between metric and semantic model
SELECT TABLE_NAME, TABLE_COMMENT
FROM information_schema.tables
WHERE table_schema = 'public';

-- Column details for a model (dimensions and measures)
DESCRIBE orders;
DESC orders;

-- Columns for a specific model
SHOW COLUMNS FROM orders;

-- Columns with full metadata (distinguish between measure and dimension)
SHOW FULL COLUMNS FROM orders;
```

#### Access-policy warnings

When a query returns no rows, inspect the warnings to determine whether an access policy restricted the result:

```sql
-- Check warnings when a query returns an empty result
SELECT region_name, total_customers
FROM customer_profile
WHERE region_name = 'North';
-- Empty set, 1 warning (12.138 sec)

SHOW WARNINGS;
```

Example response:

```
+---------+------+--------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                  |
+---------+------+--------------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | No rows returned. An access policy was applied to semantic model(s) customer_profile and may have restricted the result. |
+---------+------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.040 sec)
```

> **Note:** If an access policy is applied, `SHOW WARNINGS;` indicates that it may have restricted the query results.

### Common patterns

```sql
-- Dimension and measure
SELECT status, MEASURE(count) FROM orders GROUP BY status;

-- Cross-model
SELECT p.name, MEASURE(o.revenue) FROM orders o, products p GROUP BY p.name;

-- Filter, sort by measure
SELECT store_region, MEASURE(revenue) FROM orders
WHERE store_region = 'APAC'
GROUP BY store_region
ORDER BY MEASURE(revenue) DESC
LIMIT 10;
```

## Transpile without executing

Use `POST /api/v1/query/semantic/sql?transpile_only=true`. A successful request returns `200` with compiled SQL and parameters; no statement is created.

Normal execution returns `202 Accepted`. Continue with the [query lifecycle](https://v2.dataos.info/references/interfaces/apis/data-product-apis/querying-data-products/advanced/query-lifecycle.md).

## Next steps

* See [Cross-model queries](https://v2.dataos.info/references/interfaces/apis/data-product-apis/querying-data-products/advanced/cross-model-queries.md) before combining related semantic models.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://v2.dataos.info/references/interfaces/apis/data-product-apis/querying-data-products/semantic-sql.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
