Sidequery

Quickstart

Install Sidemantic, run the demo, and define your first semantic layer.

Installation

pip install sidemantic
# or
uv add sidemantic

Workbench

Try the interactive workbench with demo data (no installation required):

uvx sidemantic workbench --demo

See Workbench for more details on the interactive TUI.

5-Minute Tutorial (Deterministic, Copy/Pasteable)

This tutorial uses a self-contained semantic layer file that runs without any database setup.

Create models/semantic_layer.yml:

models:
  - name: customers
    sql: |
      select * from (values
        (1, 'Alice Johnson', 'North', date '2024-01-01'),
        (2, 'Bob Smith', 'South', date '2024-01-05'),
        (3, 'Carol Davis', 'North', date '2024-01-10')
      ) as t(id, name, region, signup_date)
    primary_key: id
    dimensions:
      - name: region
        type: categorical
        sql: region

  - name: orders
    sql: |
      select * from (values
        (1, 1, 120.00, 'completed', timestamp '2024-01-15 10:12:00'),
        (2, 2, 80.00,  'completed', timestamp '2024-01-20 09:30:00'),
        (3, 1, 50.00,  'pending',   timestamp '2024-02-05 14:05:00'),
        (4, 3, 200.00, 'completed', timestamp '2024-02-18 11:00:00')
      ) as t(id, customer_id, amount, status, created_at)
    primary_key: id
    dimensions:
      - name: id
        type: numeric
        sql: id
      - name: status
        type: categorical
        sql: status
      - name: created_at
        type: time
        sql: created_at
        granularity: day
    metrics:
      - name: revenue
        agg: sum
        sql: amount
      - name: order_count
        agg: count
    relationships:
      - name: customers
        type: many_to_one
        foreign_key: customer_id
      - name: order_items
        type: one_to_many
        foreign_key: order_id

  - name: order_items
    sql: |
      select * from (values
        (1, 1, 'SKU-1', 1),
        (2, 1, 'SKU-2', 1),
        (3, 1, 'SKU-2', 1),
        (4, 2, 'SKU-2', 1),
        (5, 4, 'SKU-3', 1),
        (6, 4, 'SKU-4', 1)
      ) as t(id, order_id, sku, quantity)
    primary_key: id
    dimensions:
      - name: order_id
        type: numeric
        sql: order_id
      - name: sku
        type: categorical
        sql: sku
      - name: quantity
        type: numeric
        sql: quantity
    relationships:
      - name: orders
        type: many_to_one
        foreign_key: order_id

metrics:
  - name: avg_order_value
    type: ratio
    numerator: orders.revenue
    denominator: orders.order_count

Now run five queries.

1) Single-model metric

uvx sidemantic query "SELECT orders.revenue FROM orders" --models ./models

Expected:

revenue
450.00

2) Metric + dimension

uvx sidemantic query "SELECT orders.status, orders.revenue, orders.order_count FROM orders ORDER BY orders.status" --models ./models

Expected:

status,revenue,order_count
completed,400.00,3
pending,50.00,1

3) Cross-model query (auto-joins)

uvx sidemantic query "SELECT customers.region, orders.revenue FROM orders ORDER BY customers.region" --models ./models

Expected:

region,revenue
North,370.00
South,80.00

4) Fan-out correctness (symmetric aggregates)

This query filters through a one-to-many relationship but groups at the order grain.

uvx sidemantic query "SELECT orders.id, orders.revenue FROM orders WHERE order_items.sku = 'SKU-2' ORDER BY orders.id" --models ./models

Expected:

id,revenue
1,120.00
2,80.00

Inspect compiled SQL:

uvx sidemantic query "SELECT orders.id, orders.revenue FROM orders WHERE order_items.sku = 'SKU-2' ORDER BY orders.id" --models ./models --dry-run

You should see sum(distinct ...) + hash(orders_cte.id) in the generated aggregate. See Fan-out & Symmetric Aggregates.

5) Time grain query

uvx sidemantic query "SELECT orders.created_at__month, orders.revenue FROM orders ORDER BY orders.created_at__month" --models ./models

Expected:

created_at__month,revenue
2024-01-01,200.00
2024-02-01,250.00

Definition Syntaxes (YAML, SQL, Python)

Sidemantic supports authoring semantic layers in YAML, SQL DDL, and via the Python API.

YAML

The tutorial above is a complete YAML semantic layer. Editor autocomplete and validation are driven by JSON Schema (see JSON Schema).

SQL DDL

Create models-sql/semantic_layer.sql:

MODEL (
  name orders,
  sql (select * from (values (1, 120.00), (2, 80.00)) as t(id, amount)),
  primary_key id
);

DIMENSION (
  name id,
  type numeric,
  sql id
);

METRIC (
  name revenue,
  agg sum,
  sql amount
);

Query it:

uvx sidemantic query "SELECT orders.revenue FROM orders" --models ./models-sql

Expected:

revenue
200.00

Python API

This snippet loads the YAML tutorial layer and runs a query using the Python API:

from sidemantic import SemanticLayer
from sidemantic.loaders import load_from_directory
from pathlib import Path

layer = SemanticLayer()
load_from_directory(layer, Path("./models"))
result = layer.sql("SELECT orders.revenue FROM orders")
print(result.fetchall())

Expected:

450.00

Editor Autocomplete (YAML)

For YAML definitions, add this comment to get autocomplete in VS Code, IntelliJ, and other editors:

# yaml-language-server: $schema=https://sidemantic.com/schema.json
models:
  - name: orders
    # ... autocomplete works here!

Next Steps