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
        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", 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
    sql: 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
        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", 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
    sql: 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
    sql: amount
    filters: ["{model}.status = 'completed'"]

  - name: total_revenue
    agg: sum
    sql: 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
    sql: amount

  - name: cost
    agg: sum
    sql: cost

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

  - name: margin
    type: derived
    sql: "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
    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

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
    sql: country

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

  - name: city
    type: categorical
    sql: 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
        sql: status

  - name: completed_orders
    extends: base_orders
    metrics:
      - name: revenue
        agg: sum
        sql: 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.

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
    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
        sql: 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 .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.