Advanced Features

Feature Overview

Core Capabilities

  • YAML definitions with JSON Schema for autocomplete
  • SQL query interface - write familiar SQL, get semantic results
  • Automatic joins - define relationships once, query across models
  • Multi-hop joins - automatic 2+ hop join path discovery
  • Auto-detected dependencies - no manual dependency declarations needed
  • Multi-dialect - SQLGlot powers cross-database SQL generation

Rich Metric Types

  • Aggregations - sum, avg, count, count_distinct, min, max
  • Ratios - revenue / order_count
  • Derived formulas - (revenue - cost) / revenue
  • Cumulative - running totals, rolling windows
  • Time comparisons - YoY, MoM, WoW with LAG window functions
  • Conversion funnels - signup → purchase rate

Advanced Capabilities

  • Segments - reusable named filters with template placeholders
  • Metric-level filters - auto-applied filters for business logic
  • Jinja2 templating - full conditionals and loops in SQL
  • Inheritance - extend models and metrics (DRY principles)
  • Hierarchies - parent/child dimensions with drill-down API
  • Relative dates - “last 7 days”, “this month”, etc.
  • Ungrouped queries - raw row access without aggregation
  • Pre-aggregations - automatic query routing to materialized rollups
  • Predicate pushdown - filters pushed into CTEs for improved performance

Metadata & Governance

  • Display formatting - format strings and named formats (USD, percent)
  • Drill fields - define drill-down paths for BI tools
  • Non-additivity markers - prevent incorrect aggregation
  • Default dimensions - default time dimensions and granularity

Segments - Reusable Filters

Define named filters that can be reused across queries:

models:
  - name: orders
    table: orders
    segments:
      - name: completed
        sql: "{model}.status = 'completed'"
        description: "Only completed orders"

      - name: high_value
        sql: "{model}.amount > 100"
        description: "High value orders"

Use in queries:

sql = layer.compile(
    metrics=["orders.revenue"],
    dimensions=["orders.region"],
    segments=["orders.completed", "orders.high_value"]
)

The {model} placeholder gets replaced with the actual table alias.

Metric-Level Filters

Filters that automatically apply whenever a metric is used:

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

Every query using completed_revenue will automatically filter to completed orders.

Jinja2 Templating

Use full Jinja2 syntax in SQL fields:

metrics:
  - name: taxed_revenue
    agg: sum
    sql: "{% if include_tax %}amount * 1.1{% else %}amount{% endif %}"
# Use with parameters
layer.compile(
    metrics=["orders.taxed_revenue"],
    parameters={"include_tax": True}
)

Jinja2 supports: - Conditionals: {% if ... %}...{% endif %} - Loops: {% for item in items %}...{% endfor %} - Filters: { name | upper }

Inheritance

Model Inheritance

Extend base models to reduce duplication:

models:
  - name: base_sales
    table: sales
    primary_key: sale_id
    dimensions:
      - name: date
        type: time
        granularity: day
      - name: region
        type: categorical

  - name: filtered_sales
    extends: base_sales  # Inherits all dimensions!
    segments:
      - name: completed
        sql: "{model}.status = 'completed'"

Metric Inheritance

Extend base metrics:

metrics:
  - name: base_revenue
    agg: sum
    sql: amount

  - name: completed_revenue
    extends: base_revenue
    filters: ["{model}.status = 'completed'"]

Child inherits all parent properties and can override or add to them.

Hierarchies & Drill-Down

Define hierarchical dimensions:

from sidemantic import Dimension

# Define hierarchy
country = Dimension(name="country", type="categorical")
state = Dimension(name="state", type="categorical", parent="country")
city = Dimension(name="city", type="categorical", parent="state")

Navigate the hierarchy:

# Get full path
model.get_hierarchy_path("city")
# Returns: ['country', 'state', 'city']

# Drill down
model.get_drill_down("country")  # Returns: 'state'
model.get_drill_down("state")    # Returns: 'city'

# Drill up
model.get_drill_up("city")    # Returns: 'state'
model.get_drill_up("state")   # Returns: 'country'

Relative Date Ranges

Use natural language for date filters:

layer.compile(
    metrics=["orders.revenue"],
    filters=["orders_cte.created_at >= 'last 7 days'"]
)
# Auto-converts to: created_at >= CURRENT_DATE - 7

Supported expressions: - "today", "yesterday", "tomorrow" - "last N days", "last N weeks", "last N months" - "this week", "last week", "next week" - "this month", "last month", "next month" - "this quarter", "last quarter" - "this year", "last year"

Ungrouped Queries

Get raw rows without aggregation (for detail views):

sql = layer.compile(
    metrics=["orders.revenue"],
    dimensions=["orders.customer_id", "orders.order_id"],
    ungrouped=True  # Returns raw rows
)

Without ungrouped=True, you get aggregated results. With ungrouped=True, you get individual order rows.

Metadata Fields

Display Formatting

metrics:
  - name: revenue
    agg: sum
    sql: amount
    format: "$#,##0.00"
    value_format_name: "usd"

dimensions:
  - name: discount_rate
    type: numeric
    format: "0.0%"
    value_format_name: "percent"

Drill Fields

Define drill-down paths for BI tools:

metrics:
  - name: revenue
    agg: sum
    sql: amount
    drill_fields: ["customer.name", "product.name", "order.date"]

Non-Additivity Markers

Mark metrics that can’t be summed across certain dimensions:

metrics:
  - name: avg_order_value
    agg: avg
    sql: amount
    non_additive_dimension: "date"  # Don't sum averages across time!

Default Dimensions

Specify default time dimension and granularity:

metrics:
  - name: daily_revenue
    agg: sum
    sql: amount
    default_time_dimension: "order_date"
    default_grain: "day"