Sidequery

Examples

Practical examples of using Sidemantic for semantic layer queries, from basic metrics to advanced cross-model joins and derived metrics.

Runnable Example (Executed in CI)

Doctest fixture (models/semantic_layer.yml)
models:
  - name: orders
    sql: |
      select * from (values
        (1, 120.00, 'completed'),
        (2, 80.00,  'completed'),
        (3, 50.00,  'pending'),
        (4, 200.00, 'completed')
      ) as t(id, amount, status)
    primary_key: id
    dimensions:
      - name: status
        type: categorical
        sql: status
    metrics:
      - name: revenue
        agg: sum
        sql: amount

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

status,revenue
completed,400.00
pending,50.00

Basic Query

YAML

models:
  - name: orders
    table: orders
    primary_key: id
    metrics:
      - name: revenue
        agg: sum
        sql: amount

Python

from sidemantic import SemanticLayer, Model, Metric

layer = SemanticLayer()

orders = Model(
    name="orders",
    table="orders",
    primary_key="id",
    metrics=[
        Metric(name="revenue", agg="sum", sql="amount")
    ]
)

layer.add_model(orders)

Query with SQL:

layer.sql("SELECT orders.revenue FROM orders")

Or with native Python API:

# Get SQL only
sql = layer.compile(metrics=["orders.revenue"])

# Execute and get results
result = layer.query(metrics=["orders.revenue"])
df = result.fetchdf()

Filtering and Grouping

layer.sql("""
    SELECT orders.revenue, orders.status
    FROM orders
    WHERE orders.status IN ('completed', 'shipped')
""")
Tip

No GROUP BY Needed

The semantic layer automatically groups by dimensions. Just select what you want!

Time Dimensions

YAML

dimensions:
  - name: order_date
    type: time
    sql: order_date
    granularity: day

Python

from sidemantic import Dimension

Dimension(
    name="order_date",
    type="time",
    sql="order_date",
    granularity="day"
)

# Automatic time grouping
layer.sql("""
    SELECT orders.revenue, orders.order_date
    FROM orders
""")

Cross-Model Queries

YAML

models:
  - name: orders
    table: orders
    primary_key: id
    relationships:
      - name: customers
        type: many_to_one
        foreign_key: customer_id

  - name: customers
    table: customers
    primary_key: id
    dimensions:
      - name: region
        type: categorical
        sql: region

Python

from sidemantic import Model, Relationship, Dimension

orders = Model(
    name="orders",
    table="orders",
    primary_key="id",
    relationships=[
        Relationship(name="customers", type="many_to_one", foreign_key="customer_id")
    ]
)

customers = Model(
    name="customers",
    table="customers",
    primary_key="id",
    dimensions=[
        Dimension(name="region", type="categorical", sql="region")
    ]
)

# Automatic join
layer.sql("""
    SELECT orders.revenue, customers.region
    FROM orders
""")

Segments (Named Filters)

YAML

models:
  - name: orders
    table: orders
    primary_key: order_id
    segments:
      - name: completed
        sql: "{model}.status = 'completed'"
      - name: high_value
        sql: "{model}.amount >= 500"

Python

from sidemantic import Model, Segment

orders = Model(
    name="orders",
    segments=[
        Segment(name="completed", sql="{model}.status = 'completed'"),
        Segment(name="high_value", sql="{model}.amount >= 500")
    ]
)

# Use segments in queries
layer.compile(
    metrics=["orders.revenue"],
    segments=["orders.completed", "orders.high_value"]
)

Metric-Level Filters

YAML

models:
  - name: orders
    table: orders
    primary_key: order_id
    metrics:
      - name: completed_revenue
        agg: sum
        sql: amount
        filters:
          - "{model}.status = 'completed'"

Python

from sidemantic import Metric, Model

orders = Model(
    name="orders",
    table="orders",
    primary_key="order_id",
    metrics=[
        Metric(
            name="completed_revenue",
            agg="sum",
            sql="amount",
            filters=["{model}.status = 'completed'"],
        )
    ],
)

Filters are automatically applied in the WHERE clause whenever the metric is used.

Ratio Metrics

YAML

models:
  - name: orders
    table: orders
    primary_key: order_id
    metrics:
      - name: completed_revenue
        agg: sum
        sql: amount
        filters: ["{model}.status = 'completed'"]

      - name: total_revenue
        agg: sum
        sql: amount

      - name: completion_rate
        type: ratio
        numerator: orders.completed_revenue
        denominator: orders.total_revenue

Python

from sidemantic import Metric, Model

orders = Model(
    name="orders",
    table="orders",
    primary_key="order_id",
    metrics=[
        Metric(name="completed_revenue", agg="sum", sql="amount", filters=["{model}.status = 'completed'"]),
        Metric(name="total_revenue", agg="sum", sql="amount"),
        Metric(
            name="completion_rate",
            type="ratio",
            numerator="orders.completed_revenue",
            denominator="orders.total_revenue",
        ),
    ],
)

layer.sql("SELECT orders.completion_rate FROM orders")

Derived Metrics

YAML

models:
  - name: orders
    table: orders
    primary_key: order_id
    metrics:
      - name: revenue
        agg: sum
        sql: amount

      - name: cost
        agg: sum
        sql: cost

      - name: profit
        type: derived
        sql: "revenue - cost"

      - name: margin
        type: derived
        sql: "profit / revenue"

Python

from sidemantic import Metric, Model

orders = Model(
    name="orders",
    table="orders",
    primary_key="order_id",
    metrics=[
        Metric(name="revenue", agg="sum", sql="amount"),
        Metric(name="cost", agg="sum", sql="cost"),
        Metric(name="profit", type="derived", sql="revenue - cost"),
        Metric(name="margin", type="derived", sql="profit / revenue"),
    ],
)

layer.sql("SELECT orders.revenue, orders.profit, orders.margin FROM orders")

Cumulative Metrics

YAML

models:
  - name: orders
    table: orders
    primary_key: order_id
    metrics:
      - name: daily_revenue
        agg: sum
        sql: amount

      - name: running_total
        type: cumulative
        sql: daily_revenue
        window: "7 days"

      - name: mtd_revenue
        type: cumulative
        sql: daily_revenue
        grain_to_date: month

Python

from sidemantic import Metric, Model

orders = Model(
    name="orders",
    table="orders",
    primary_key="order_id",
    metrics=[
        Metric(name="daily_revenue", agg="sum", sql="amount"),
        Metric(name="running_total", type="cumulative", sql="daily_revenue", window="7 days"),
        Metric(name="mtd_revenue", type="cumulative", sql="daily_revenue", grain_to_date="month"),
    ],
)

layer.sql("""
    SELECT order_date, running_total, mtd_revenue
    FROM orders
""")

Relative Date Ranges

# Use natural language date ranges in filters
layer.compile(
    metrics=["orders.revenue"],
    filters=["orders.order_date >= 'last 7 days'"]
)

# Other examples:
# - "today"
# - "yesterday"
# - "last 30 days"
# - "this week"
# - "this month"
# - "this quarter"
# - "this year"

Hierarchies & Drill-Down

YAML

dimensions:
  - name: country
    type: categorical
    sql: country

  - name: state
    type: categorical
    sql: state
    parent: country

  - name: city
    type: categorical
    sql: city
    parent: state

Python

from sidemantic import Dimension

Dimension(name="country", type="categorical", sql="country"),
Dimension(name="state", type="categorical", sql="state", parent="country"),
Dimension(name="city", type="categorical", sql="city", parent="state")

# Use drill-down API
model.get_hierarchy_path("city")  # ['country', 'state', 'city']
model.get_drill_down("state")     # 'city'
model.get_drill_up("city")        # 'state'

Inheritance

YAML

models:
  - name: base_orders
    table: orders
    dimensions:
      - name: status
        type: categorical
        sql: status

  - name: completed_orders
    extends: base_orders
    metrics:
      - name: revenue
        agg: sum
        sql: amount

Python

from sidemantic import Dimension, Metric, Model

base = Model(
    name="base_orders",
    table="orders",
    dimensions=[Dimension(name="status", type="categorical", sql="status")]
)

extended = Model(
    name="completed_orders",
    extends="base_orders",
    metrics=[Metric(name="revenue", agg="sum", sql="amount")]
)

Child models inherit all dimensions, metrics, and relationships from parent.

Ungrouped Queries

# Get raw rows without aggregation
layer.compile(
    metrics=["orders.revenue"],  # Still available
    dimensions=["orders.order_id", "orders.customer_id"],
    ungrouped=True  # No GROUP BY
)

Parameterized Filters (Deprecated)

YAML

# Parameters are not loaded from YAML.
# Register them in Python via layer.graph.add_parameter().

Python

from sidemantic import Parameter

layer.graph.add_parameter(Parameter(name="start_date", type="date", default_value="2024-01-01"))
layer.graph.add_parameter(Parameter(name="min_amount", type="number", default_value=100))

sql = layer.compile(
    metrics=["orders.revenue"],
    filters=[
        "orders.order_date >= {{ start_date }}",
        "orders.amount >= {{ min_amount }}"
    ],
    parameters={"start_date": "2024-06-01", "min_amount": 250}
)
Warning

Parameter Syntax and Scope

Parameters are deprecated and only interpolated in query() / compile() filters. The SQL interface (layer.sql) does not render {{ ... }}.

DON'T add quotes around {{ param }} - they're added automatically by the parameter type:

  • ❌ WHERE date >= '{{ start_date }}' (double quotes)
  • ✅ WHERE date >= {{ start_date }}

Multi-Hop Joins

YAML

models:
  - name: orders
    table: orders
    primary_key: order_id
    relationships:
      - name: customers
        type: many_to_one
        foreign_key: customer_id

  - name: customers
    table: customers
    primary_key: customer_id
    relationships:
      - name: regions
        type: many_to_one
        foreign_key: region_id

  - name: regions
    table: regions
    primary_key: region_id
    dimensions:
      - name: country
        type: categorical
        sql: country

Python

from sidemantic import Dimension, Model, Relationship

orders = Model(
    name="orders",
    table="orders",
    primary_key="order_id",
    relationships=[
        Relationship(name="customers", type="many_to_one", foreign_key="customer_id")
    ]
)

customers = Model(
    name="customers",
    table="customers",
    primary_key="customer_id",
    relationships=[
        Relationship(name="regions", type="many_to_one", foreign_key="region_id")
    ]
)

regions = Model(
    name="regions",
    table="regions",
    primary_key="region_id",
    dimensions=[
        Dimension(name="country", type="categorical", sql="country")
    ]
)

# Spans 2 hops: orders -> customers -> regions
layer.sql("""
    SELECT orders.revenue, regions.country
    FROM orders
""")

Multi-Format Loading

Load semantic models from different formats (Cube, LookML, Hex, MetricFlow, etc.) all at once:

from sidemantic import SemanticLayer, load_from_directory

layer = SemanticLayer(connection="duckdb:///data.db")
load_from_directory(layer, "semantic_models/")

# Query across all formats seamlessly
result = layer.query(
    metrics=["orders.revenue", "products.avg_price"],
    dimensions=["customers.region", "products.category"]
)

load_from_directory() automatically:

  • Discovers .sql, .lkml, .malloy, .yml, .yaml files
  • Detects the format and parses with the right adapter
  • Infers relationships from foreign key naming (customer_id → customers)
  • Builds the join graph

See the multi_format_demo for a complete working example with Cube, Hex, and LookML.