Transpiling Semantics¶

The vulcan transpile command converts semantic queries into executable SQL. Use it to preview, debug, and validate semantic logic before execution.

Transpilation converts semantic layer queries (business-friendly) into SQL that your database understands.

What is Transpilation?¶

Transpilation transforms semantic layer queries into database-specific SQL. It converts "business language" (semantic queries) into "database language" (SQL).

Semantic SQL → Native SQL: Converts semantic SQL queries with MEASURE() functions into standard SQL - takes your business-friendly queries and makes them database-ready
REST API Payload → Native SQL: Converts JSON query payloads into executable SQL statements. Use for API-driven applications.
Validation: Catches errors before query execution - find problems before they hit production
Debugging: Inspect the generated SQL to understand query behavior - see exactly what your semantic queries are doing under the hood

Semantic queries are easier to write and understand, but databases need SQL. Transpilation bridges that gap.

Basic Structure¶

Semantic SQL Query Structure¶

Semantic SQL queries follow standard SQL syntax with semantic layer extensions:

SELECT 
  alias.dimension_name,           # Dimensions: attributes for grouping and filtering
  MEASURE(alias.measure_name)  # Measures: aggregated calculations (required wrapper)
FROM alias                        # Semantic model alias (business-friendly name)
CROSS JOIN other_alias            # Optional: join multiple models
WHERE 
  alias.dimension_name = 'value'  # Optional: filter on dimensions
  AND segment_name = true         # Optional: use segments (only = true supported)
GROUP BY alias.dimension_name     # Required: all non-aggregated columns
ORDER BY MEASURE(alias.measure_name)    # Optional: sort results
LIMIT 100                         # Optional: limit result set
OFFSET 0                          # Optional: pagination offset

Key Components:

alias.dimension_name: Reference dimensions using semantic model alias
MEASURE(measure_name): Required wrapper for measures to apply aggregation
FROM alias: Use semantic model alias, not physical model name
CROSS JOIN: Join syntax (join conditions automatically inferred)
segment_name = true: Segments only support = true, not = false

REST API Payload Structure¶

REST API queries use JSON payloads with semantic query definitions:

{
  "query": {
    "measures": ["alias.measure_name"],              # Required: array of measure names
    "dimensions": ["alias.dimension_name"],         # Optional: array of dimension names
    "segments": ["segment_name"],                    # Optional: array of segment names
    "timeDimensions": [{                             # Optional: array of time dimension objects
      "dimension": "alias.time_dimension",           # Required: time dimension member
      "dateRange": ["2024-01-01", "2024-12-31"],    # Optional: date range array or string
      "granularity": "month"                         # Optional: hour, day, week, month, quarter, year
    }],
    "filters": [{                                    # Optional: array of filter objects
      "member": "alias.dimension_name",              # Required: fully qualified member name
      "operator": "equals",                          # Required: filter operator
      "values": ["value1", "value2"]                 # Optional: array of filter values
    }],
    "order": {                                       # Optional: sort order object
      "alias.measure_name": "desc",                  # Member name: "asc" or "desc"
      "alias.dimension_name": "asc"
    },
    "limit": 100,                                    # Optional: maximum rows to return
    "offset": 0,                                     # Optional: rows to skip
    "timezone": "UTC",                               # Optional: timezone for date parsing
    "renewQuery": false                              # Optional: bypass cache if true
  },
  "ttl_minutes": 60                                  # Optional: cache duration in minutes
}

Key Components:

measures: Array of fully qualified measure names: "alias.measure_name"
dimensions: Array of fully qualified dimension names: "alias.dimension_name"
segments: Array of segment names (no alias prefix needed)
timeDimensions: Array of objects with dimension, dateRange, and granularity
filters: Array of filter objects with member, operator, and values
order: Object mapping member names to sort direction ("asc" or "desc")

Basic Usage¶

Transpiling Semantic SQL Queries¶

Convert semantic SQL queries to native SQL:

vulcan transpile --format sql "SELECT MEASURE(total_users) FROM users"

Output: Generated SQL that can be executed directly against your database.

Transpiling REST API Payloads¶

Convert JSON query payloads to native SQL:

vulcan transpile --format json '{"query": {"measures": ["users.total_users"]}}'

Output: Generated SQL from the REST-style query definition.

Command Syntax¶

Basic Format¶

vulcan transpile --format <format> "<query>"

Parameters:

--format (required): Output format: sql or json
"<query>" (required): The semantic query to transpile
For SQL format: Semantic SQL query string
For JSON format: JSON query payload string

Advanced Options¶

vulcan transpile --format sql "<query>" [--disable-post-processing]

Options:

--disable-post-processing: Enable pushdown mode for CTE support and advanced SQL features
Default: Post-processing enabled (CTEs not supported)
With flag: Pushdown enabled (CTEs supported, no pre-aggregations)

Transpiling Semantic SQL¶

Basic Query¶

Transpile a simple semantic SQL query:

vulcan transpile --format sql "SELECT MEASURE(total_users) FROM users"

Generated SQL:

SELECT sum("users".user_id) AS total_users
FROM analytics.users AS "users"

Query with Dimensions¶

Transpile queries with dimensions and grouping:

vulcan transpile --format sql "SELECT users.plan_type, MEASURE(total_users) FROM users GROUP BY users.plan_type"

Generated SQL:

SELECT "users".plan_type, sum("users".user_id) AS total_users
FROM analytics.users AS "users"
GROUP BY "users".plan_type

Query with Filters¶

Transpile queries with WHERE conditions:

vulcan transpile --format sql "SELECT MEASURE(total_arr) FROM subscriptions WHERE subscriptions.status = 'active'"

Generated SQL:

SELECT sum("subscriptions".arr) AS total_arr
FROM analytics.subscriptions AS "subscriptions"
WHERE "subscriptions".status = 'active'

Query with Time Grouping¶

Transpile time-based queries:

vulcan transpile --format sql "SELECT DATE_TRUNC('month', subscriptions.start_date) as month, MEASURE(total_arr) FROM subscriptions GROUP BY month"

Generated SQL:

SELECT DATE_TRUNC('month', "subscriptions".start_date) AS month,
       sum("subscriptions".arr) AS total_arr
FROM analytics.subscriptions AS "subscriptions"
GROUP BY DATE_TRUNC('month', "subscriptions".start_date)

Query with Joins¶

Transpile queries joining multiple models:

vulcan transpile --format sql "SELECT users.industry, MEASURE(total_arr) FROM subscriptions CROSS JOIN users GROUP BY users.industry"

Generated SQL:

SELECT "users".industry, sum("subscriptions".arr) AS total_arr
FROM analytics.subscriptions AS "subscriptions"
CROSS JOIN analytics.users AS "users"
WHERE "subscriptions".user_id = "users".user_id
GROUP BY "users".industry

Transpiling REST API Payloads¶

Minimal Query¶

Transpile a basic REST API query:

vulcan transpile --format json '{"query": {"measures": ["users.total_users"]}}'

Generated SQL:

SELECT sum("users".user_id) AS total_users
FROM analytics.users AS "users"

Query with Dimensions¶

Transpile queries with dimensions:

vulcan transpile --format json '{"query": {"measures": ["subscriptions.total_arr"], "dimensions": ["subscriptions.plan_type"]}}'

Generated SQL:

SELECT "subscriptions".plan_type, sum("subscriptions".arr) AS total_arr
FROM analytics.subscriptions AS "subscriptions"
GROUP BY "subscriptions".plan_type

Query with Time Dimensions¶

Transpile time-based queries:

vulcan transpile --format json '{"query": {"measures": ["orders.total_revenue"], "timeDimensions": [{"dimension": "orders.order_date", "dateRange": ["2024-01-01", "2024-12-31"], "granularity": "month"}]}}'

Generated SQL:

SELECT DATE_TRUNC('month', "orders".order_date) AS orders_order_date_month,
       sum("orders".amount) AS total_revenue
FROM analytics.orders AS "orders"
WHERE "orders".order_date >= '2024-01-01T00:00:00.000'
  AND "orders".order_date <= '2024-12-31T23:59:59.999'
GROUP BY DATE_TRUNC('month', "orders".order_date)

Query with Filters¶

Transpile queries with filters:

vulcan transpile --format json '{"query": {"measures": ["subscriptions.total_arr"], "filters": [{"member": "subscriptions.status", "operator": "equals", "values": ["active"]}]}}'

Generated SQL:

SELECT sum("subscriptions".arr) AS total_arr
FROM analytics.subscriptions AS "subscriptions"
WHERE "subscriptions".status = 'active'

Query with Segments¶

Transpile queries using segments:

vulcan transpile --format json '{"query": {"measures": ["subscriptions.total_arr"], "segments": ["subscriptions.active_subscriptions"]}}'

Generated SQL:

SELECT sum("subscriptions".arr) AS total_arr
FROM analytics.subscriptions AS "subscriptions"
WHERE "subscriptions".status = 'active'
  AND "subscriptions".end_date IS NULL

Complex Query¶

Transpile complex queries with multiple components:

vulcan transpile --format json '{"query": {"measures": ["subscriptions.total_arr", "subscriptions.total_seats"], "dimensions": ["subscriptions.plan_type", "users.industry"], "filters": [{"member": "subscriptions.status", "operator": "equals", "values": ["active"]}], "timeDimensions": [{"dimension": "subscriptions.start_date", "dateRange": ["2024-01-01", "2024-12-31"], "granularity": "month"}], "order": {"subscriptions.total_arr": "desc"}, "limit": 100}}'

Generated SQL:

SELECT DATE_TRUNC('month', "subscriptions".start_date) AS subscriptions_start_date_month,
       "subscriptions".plan_type,
       "users".industry,
       sum("subscriptions".arr) AS total_arr,
       sum("subscriptions".seats) AS total_seats
FROM analytics.subscriptions AS "subscriptions"
CROSS JOIN analytics.users AS "users"
WHERE "subscriptions".status = 'active'
  AND "subscriptions".start_date >= '2024-01-01T00:00:00.000'
  AND "subscriptions".start_date <= '2024-12-31T23:59:59.999'
  AND "subscriptions".user_id = "users".user_id
GROUP BY DATE_TRUNC('month', "subscriptions".start_date),
         "subscriptions".plan_type,
         "users".industry
ORDER BY sum("subscriptions".arr) DESC
LIMIT 100

Transpiling MySQL Payloads¶

The Vulcan MySQL wire protocol service lets you query your semantic layer using any standard MySQL client or BI tool. Semantic queries are transpiled to native SQL on the server side — you write standard SQL against your semantic models, and Vulcan handles the translation.

Connecting to Vulcan MySQL¶

Connect using any MySQL client:

mysql -h <host> -P <port> -u <username> -p'<api-key>' --enable-cleartext-plugin <tenant_name>.<data_product_name>

Parameters:

Parameter	Description	Example
`-h <host>`	Vulcan MySQL host	`127.0.0.1` (local) or `tcp.my-context.dataos.app` (remote)
`-P <port>`	MySQL port	`3307` (local) or `3306` (remote)
`-u <username>`	Your DataOS username	`johndoe`
`-p'<api-key>'`	Your DataOS API key (no space after `-p`)	`-p'dG9rZW4xMjM0...'`
`<tenant_name>.<data_product_name>`	Database to connect to	`marketing.sales_analytics`

Example — Local connection:

mysql -h 127.0.0.1 -P 3307 -u johndoe -p'dG9rZW4xMjM0NTY3ODk=' --ssl-mode=REQUIRED --enable-cleartext-plugin

Example — Remote connection:

mysql -h tcp.my-context.dataos.app -P 3306 -u johndoe -p'dG9rZW4xMjM0NTY3ODk=' --enable-cleartext-plugin marketing.sales_analytics

On successful connection:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 8.0.0 Vulcan MySQL Wire Protocol

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Discovering Available Models¶

List all available semantic models (tables):

mysql> SHOW TABLES;
+---------------------------+
| Tables_in_sales_analytics |
+---------------------------+
| users                     |
| subscriptions             |
| orders                    |
+---------------------------+
3 rows in set (0.10 sec)

Inspect a model's columns (dimensions and measures):

mysql> DESCRIBE users;
+----------------+--------------+------+-----+
| Field          | Type         | Null | Key |
+----------------+--------------+------+-----+
| user_id        | varchar(255) | YES  |     |
| plan_type      | varchar(255) | YES  |     |
| industry       | varchar(255) | YES  |     |
| total_users    | bigint       | YES  |     |
+----------------+--------------+------+-----+
4 rows in set (0.08 sec)

Basic Query¶

Query a single measure:

mysql> SELECT MEASURE(total_users) FROM users;
+-------------+
| total_users |
+-------------+
|        4528 |
+-------------+
1 row in set (0.45 sec)

Query with Dimensions¶

Query measures grouped by a dimension:

mysql> SELECT users.plan_type, MEASURE(total_users)
    -> FROM users
    -> GROUP BY users.plan_type;
+-----------+-------------+
| plan_type | total_users |
+-----------+-------------+
| free      |        2841 |
| starter   |        1024 |
| pro       |         512 |
| enterprise|         151 |
+-----------+-------------+
4 rows in set (0.62 sec)

Query with Filters¶

Filter results using WHERE conditions:

mysql> SELECT MEASURE(total_arr)
    -> FROM subscriptions
    -> WHERE subscriptions.status = 'active';
+-----------+
| total_arr |
+-----------+
| 2450000.0 |
+-----------+
1 row in set (0.38 sec)

Query with Time Grouping¶

Group results by time intervals:

mysql> SELECT DATE_TRUNC('month', subscriptions.start_date) AS month,
    ->        MEASURE(total_arr)
    -> FROM subscriptions
    -> GROUP BY month
    -> ORDER BY month DESC
    -> LIMIT 5;
+------------+-----------+
| month      | total_arr |
+------------+-----------+
| 2024-12-01 |  285000.0 |
| 2024-11-01 |  272000.0 |
| 2024-10-01 |  268500.0 |
| 2024-09-01 |  254000.0 |
| 2024-08-01 |  241000.0 |
+------------+-----------+
5 rows in set (1.12 sec)

Query with Joins¶

Join multiple semantic models:

mysql> SELECT users.industry, MEASURE(total_arr)
    -> FROM subscriptions
    -> CROSS JOIN users
    -> GROUP BY users.industry
    -> ORDER BY MEASURE(total_arr) DESC;
+----------------+-----------+
| industry       | total_arr |
+----------------+-----------+
| Technology     |  820000.0 |
| Finance        |  645000.0 |
| Healthcare     |  480000.0 |
| Retail         |  312000.0 |
| Education      |  193000.0 |
+----------------+-----------+
5 rows in set (1.34 sec)

Complex Query¶

Combine multiple dimensions, filters, time grouping, and joins:

mysql> SELECT DATE_TRUNC('month', subscriptions.start_date) AS month,
    ->        subscriptions.plan_type,
    ->        users.industry,
    ->        MEASURE(total_arr),
    ->        MEASURE(total_seats)
    -> FROM subscriptions
    -> CROSS JOIN users
    -> WHERE subscriptions.status = 'active'
    -> GROUP BY month, subscriptions.plan_type, users.industry
    -> ORDER BY MEASURE(total_arr) DESC
    -> LIMIT 10;
+------------+-----------+------------+-----------+-------------+
| month      | plan_type | industry   | total_arr | total_seats |
+------------+-----------+------------+-----------+-------------+
| 2024-12-01 | enterprise| Technology |  125000.0 |         480 |
| 2024-12-01 | pro       | Finance    |   98000.0 |         320 |
| 2024-11-01 | enterprise| Technology |  118000.0 |         460 |
| 2024-11-01 | enterprise| Finance    |   95000.0 |         310 |
| 2024-12-01 | pro       | Healthcare |   72000.0 |         240 |
| 2024-10-01 | enterprise| Technology |  112000.0 |         440 |
| 2024-11-01 | pro       | Finance    |   88000.0 |         290 |
| 2024-12-01 | starter   | Technology |   54000.0 |         180 |
| 2024-10-01 | enterprise| Finance    |   91000.0 |         300 |
| 2024-11-01 | pro       | Healthcare |   68000.0 |         225 |
+------------+-----------+------------+-----------+-------------+
10 rows in set (2.18 sec)

Exit Session¶

mysql> EXIT;
Bye

Use Cases¶

Query Validation¶

Validate semantic queries before execution:

# Check if query syntax is correct
vulcan transpile --format sql "SELECT MEASURE(total_users) FROM users"

If the query is invalid, you'll get an error message indicating the issue. This is way better than finding out at runtime! Catch errors early, fix them, then execute.

Debugging Query Behavior¶

Inspect generated SQL to understand how semantic queries are translated. When queries return unexpected results, transpile them to see what's actually happening:

# See how measures are aggregated
vulcan transpile --format sql "SELECT users.plan_type, MEASURE(total_users) FROM users GROUP BY users.plan_type"

This shows you exactly how Vulcan is interpreting your semantic query. Sometimes the generated SQL reveals issues you didn't expect!

Performance Analysis¶

Review generated SQL to identify optimization opportunities. The generated SQL shows you exactly what the database will execute, so you can spot performance issues:

# Check join conditions and filter placement
vulcan transpile --format sql "SELECT users.industry, MEASURE(total_arr) FROM subscriptions CROSS JOIN users WHERE subscriptions.status = 'active' GROUP BY users.industry"

Look at the generated SQL, are joins efficient? Are filters in the right place? This is your chance to optimize before execution.

Documentation¶

Generate SQL examples for documentation or training. Use transpilation to create SQL examples that show how semantic queries translate:

# Create SQL reference from semantic queries
vulcan transpile --format sql "SELECT MEASURE(total_arr) FROM subscriptions WHERE subscriptions.status = 'active'"

Use this for documentation. Show both the semantic query (easy to understand) and the generated SQL (what actually runs).

Common Errors and Solutions¶

Error: "Unknown member: X"¶

Cause: Member doesn't exist in semantic model or is misspelled.

Solution:

Verify member exists in your semantic model - check your semantic model definitions
Check spelling and casing (case-sensitive) - users.plan_type is different from users.Plan_Type
Use fully qualified format: alias.member_name - always include the alias prefix

This error usually means you've made a typo or the member doesn't exist yet. Double-check your semantic model!

Error: "Measure not found: X"¶

Cause: Measure referenced without proper qualification or doesn't exist.

Solution:

Use MEASURE(measure_name) wrapper for SQL format - measures need the MEASURE() wrapper in SQL
Use fully qualified format: alias.measure_name for JSON format - JSON format uses dot notation
Verify measure is defined in semantic model - make sure the measure actually exists

The format differs between SQL and JSON, so make sure you're using the right syntax for each!

Error: "Model not found: X"¶

Cause: Alias doesn't match any semantic model.

Solution:

Check semantic model aliases in your semantics/ directory
Verify alias spelling and casing
Ensure semantic models are properly defined

Error: "Invalid JSON format"¶

Cause: JSON payload is malformed.

Solution:

Validate JSON syntax
Ensure proper quoting of strings
Check array and object structure

Error: "Projection references non-aggregate values"¶

Cause: Non-aggregated columns not in GROUP BY, or measures missing MEASURE() wrapper.

Solution:

Add all non-aggregated columns to GROUP BY - if you select a column, it needs to be in GROUP BY (unless it's aggregated)
Use MEASURE() wrapper for all measures in SQL format - measures must be wrapped in MEASURE()

This is a SQL rule, you can't mix aggregated and non-aggregated columns without GROUP BY. The error is telling you exactly what's wrong!

Best Practices¶

Validate Before Execution¶

Always transpile queries before running them in production. It's like checking your work before turning it in:

# Good: Validate first
vulcan transpile --format sql "SELECT MEASURE(total_users) FROM users"
# Review output, then execute - make sure the SQL looks right

# Bad: Execute without validation
# Direct execution without checking generated SQL - don't do this

Transpilation catches errors early. Use it!

Use Transpilation for Debugging¶

When queries return unexpected results, transpile to inspect generated SQL. The generated SQL often reveals what's actually happening:

# Debug query behavior
vulcan transpile --format sql "SELECT users.plan_type, MEASURE(total_users) FROM users GROUP BY users.plan_type"
# Compare generated SQL with expected behavior - does it match what you think should happen?

Sometimes the issue isn't with your semantic query, it's with how it's being translated. Transpilation shows you the translation.

Document Query Patterns¶

Use transpilation output to document common query patterns:

# Generate SQL examples for documentation
vulcan transpile --format sql "SELECT MEASURE(total_arr) FROM subscriptions WHERE subscriptions.status = 'active'"

Test Both Formats¶

When building applications, test both SQL and JSON formats:

# Test SQL format
vulcan transpile --format sql "SELECT MEASURE(total_users) FROM users"

# Test equivalent JSON format
vulcan transpile --format json '{"query": {"measures": ["users.total_users"]}}'

Choose Appropriate Mode¶

Select post-processing or pushdown mode based on needs:

Post-processing (default): Use for queries that benefit from pre-aggregations and caching
Pushdown (--disable-post-processing): Use when you need CTEs or complex SQL structures

Integration with Development Workflow¶

Pre-commit Validation¶

Add transpilation checks to your development workflow:

# Validate semantic queries in CI/CD
vulcan transpile --format sql "SELECT MEASURE(total_users) FROM users"

Query Testing¶

Use transpilation to generate test SQL:

# Generate SQL for testing
vulcan transpile --format sql "SELECT users.plan_type, MEASURE(total_users) FROM users GROUP BY users.plan_type"
# Use output in test assertions

Performance Tuning¶

Analyze generated SQL for optimization:

# Review join conditions and filter placement
vulcan transpile --format sql "SELECT users.industry, MEASURE(total_arr) FROM subscriptions CROSS JOIN users WHERE subscriptions.status = 'active' GROUP BY users.industry"

Next Steps¶

Learn about Semantic Models that define the queryable members
Explore Business Metrics for time-series analysis
See the Semantics Overview for the complete picture