Examples

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

Basic Query

YAML

models:
  - name: orders
    table: orders
    primary_key: id
    metrics:
      - name: revenue
        agg: sum
        expr: 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", expr="amount")
    ]
)

layer.add_model(orders)

Query with SQL:

layer.sql("SELECT 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 revenue, status
    FROM orders
    WHERE 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
    expr: order_date
    granularity: day

Python

from sidemantic import Dimension

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

# Automatic time grouping
layer.sql("""
    SELECT revenue, 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
        expr: 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", expr="region")
    ]
)

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

Segments (Named Filters)

YAML

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

Python

from sidemantic import 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

metrics:
  - name: completed_revenue
    agg: sum
    expr: amount
    filters:
      - "{model}.status = 'completed'"

Python

Metric(
    name="completed_revenue",
    agg="sum",
    expr="amount",
    filters=["{model}.status = 'completed'"]
)

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

Ratio Metrics

YAML

metrics:
  - name: completed_revenue
    agg: sum
    expr: amount
    filters: ["{model}.status = 'completed'"]

  - name: total_revenue
    agg: sum
    expr: amount

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

Python

Metric(name="completed_revenue", agg="sum", expr="amount",
       filters=["{model}.status = 'completed'"]),
Metric(name="total_revenue", agg="sum", expr="amount"),
Metric(name="completion_rate", type="ratio",
       numerator="completed_revenue", denominator="total_revenue")

layer.sql("SELECT completion_rate FROM orders")

Derived Metrics

YAML

metrics:
  - name: revenue
    agg: sum
    expr: amount

  - name: cost
    agg: sum
    expr: cost

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

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

Python

Metric(name="revenue", agg="sum", expr="amount"),
Metric(name="cost", agg="sum", expr="cost"),
Metric(name="profit", type="derived", expr="revenue - cost"),
Metric(name="margin", type="derived", expr="profit / revenue")

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

Cumulative Metrics

YAML

metrics:
  - name: daily_revenue
    agg: sum
    expr: amount

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

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

Python

Metric(name="daily_revenue", agg="sum", expr="amount"),
Metric(name="running_total", type="cumulative",
       expr="daily_revenue", window="7 days"),
Metric(name="mtd_revenue", type="cumulative",
       expr="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
    expr: country

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

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

Python

Dimension(name="country", type="categorical", expr="country"),
Dimension(name="state", type="categorical", expr="state", parent="country"),
Dimension(name="city", type="categorical", expr="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
        expr: status

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

Python

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

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

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

Jinja2 Templates

YAML

metrics:
  - name: filtered_revenue
    agg: sum
    expr: |
      {% if include_tax %}
        amount * 1.1
      {% else %}
        amount
      {% endif %}

Python

Metric(
    name="filtered_revenue",
    agg="sum",
    expr="""
    {% if include_tax %}
        amount * 1.1
    {% else %}
        amount
    {% endif %}
    """
)

layer.compile(
    metrics=["orders.filtered_revenue"],
    parameters={"include_tax": True}
)

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 Queries

YAML

parameters:
  - name: start_date
    type: date
    default_value: "2024-01-01"

  - name: min_amount
    type: number
    default_value: 100

Python

from sidemantic import Parameter

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

layer.sql(
    "SELECT revenue FROM orders WHERE order_date >= {{ start_date }}",
    parameters={"start_date": "2024-06-01"}
)

Warning

Parameter Syntax

DON'T add quotes around {{ param }} - they're added automatically based on type:

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

Multi-Hop Joins

YAML

models:
  - name: orders
    relationships:
      - name: customers
        type: many_to_one
        foreign_key: customer_id

  - name: customers
    relationships:
      - name: regions
        type: many_to_one
        foreign_key: region_id

  - name: regions
    dimensions:
      - name: country
        type: categorical
        expr: country

Python

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

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

regions = Model(
    name="regions",
    dimensions=[
        Dimension(name="country", type="categorical", expr="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 all .lkml, .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.