sidequery/sidemantic-modeler

Sidemantic Modeler

Build semantic layers that map physical database tables to business-friendly dimensions and metrics. Sidemantic generates SQL from these definitions, handling joins, aggregations, granularity, and dialect differences automatically.

Quick Start

2-Minute First Success (CLI-first onboarding path)

uv add sidemantic duckdb

mkdir -p models

cat > models/orders.yml <<'YAML'
models:
  - name: orders
    table: orders
    primary_key: order_id
    dimensions:
      - name: status
        type: categorical
    metrics:
      - name: revenue
        agg: sum
        sql: order_amount
      - name: order_count
        agg: count
YAML

uv run sidemantic validate models/ --verbose
uv run sidemantic info models/
uv run sidemantic query models/ -c duckdb:///data.duckdb \
  "SELECT revenue, status FROM orders ORDER BY revenue DESC LIMIT 5"

Assumes an orders table already exists in data.duckdb with status and order_amount columns.

YAML (preferred for file-based models)

models:
  - name: orders
    table: orders
    primary_key: order_id
    dimensions:
      - name: status
        type: categorical
      - name: order_date
        type: time
        sql: created_at
        granularity: day
    metrics:
      - name: revenue
        agg: sum
        sql: order_amount
      - name: order_count
        agg: count

Load and query:

from sidemantic import SemanticLayer

layer = SemanticLayer.from_yaml("models.yml", connection="duckdb:///data.duckdb")
result = layer.sql("SELECT revenue, status FROM orders")

Python API (advanced, optional)

from sidemantic import Model, Dimension, Metric, SemanticLayer

layer = SemanticLayer(connection="duckdb:///data.duckdb")
Model(
    name="orders",
    table="orders",
    primary_key="order_id",
    dimensions=[
        Dimension(name="status", type="categorical"),
        Dimension(name="order_date", type="time", sql="created_at", granularity="day"),
    ],
    metrics=[
        Metric(name="revenue", agg="sum", sql="order_amount"),
        Metric(name="order_count", agg="count"),
    ],
)
result = layer.sql("SELECT revenue, status FROM orders")

Generate Models from SQL Queries

The fastest path when existing queries are available. The Migrator reverse-engineers semantic models by analyzing SQL: it extracts tables, columns, aggregations, joins, time dimensions, derived metrics, and window functions automatically.

CLI (bootstrap from a folder of .sql files)

# Generate model YAML + rewritten queries from raw SQL
sidemantic migrator --queries queries/ --generate-models output/

# Check coverage: how well do existing models handle these queries?
sidemantic migrator models/ --queries queries/ --verbose

Python API (advanced/automation only)

from sidemantic import SemanticLayer
from sidemantic.core.migrator import Migrator

# Connect to your database (optional but improves inference via information_schema)
layer = SemanticLayer(connection="duckdb:///data.duckdb", auto_register=False)
migrator = Migrator(layer, connection=layer.conn)

# Feed it SQL queries (strings, not files)
queries = [
    "SELECT status, SUM(amount) AS revenue, COUNT(*) AS orders FROM orders GROUP BY status",
    "SELECT DATE_TRUNC('month', created_at), SUM(amount) FROM orders GROUP BY 1",
    "SELECT c.region, SUM(o.amount) / COUNT(DISTINCT c.id) AS rev_per_customer "
    "FROM orders o JOIN customers c ON o.customer_id = c.id GROUP BY 1",
]

report = migrator.analyze_queries(queries)
models = migrator.generate_models(report)          # YAML-ready model dicts
graph_metrics = migrator.generate_graph_metrics(report, models)  # cross-model metrics
rewritten = migrator.generate_rewritten_queries(report)          # semantic SQL

# Write to disk
migrator.write_model_files(models, "output/models/")
migrator.write_rewritten_queries(rewritten, "output/rewritten_queries/")

# Print coverage report
migrator.print_report(report, verbose=True)

What the Migrator auto-detects

| Pattern in SQL | What it generates | |----------------|-------------------| | SUM(amount) / COUNT(*) / AVG(price) | Metric with matching agg | | COUNT(DISTINCT user_id) | Metric with agg: count_distinct | | SUM(amount) AS revenue | Metric named revenue (preserves aliases) | | GROUP BY status | Dimension type: categorical | | DATE_TRUNC('month', created_at) | Dimension type: time, granularity extracted from SQL (here: month) | | JOIN customers ON o.customer_id = c.id | Relationship many_to_one, foreign_key: customer_id | | SUM(a) / NULLIF(COUNT(b), 0) | Derived metric with formula | | SUM(x) OVER (ORDER BY date ROWS ...) | Cumulative metric with window | | SUM(x) OVER (PARTITION BY DATE_TRUNC(...)) | Cumulative metric with grain_to_date | | Cross-model expressions | Graph-level derived metrics |

Workflow: queries first, then refine

1. Collect existing SQL queries (dashboards, reports, ad-hoc analyses)
2. Run migrator.analyze_queries(queries) to generate a first pass
3. Review generated models: rename metrics, add descriptions, fix types
4. Run coverage check to verify queries can be rewritten through the semantic layer
5. Iterate until coverage is high

For the full Migrator API (all methods, outputs, edge cases), load references/generation.md.

Core Workflow

Follow these steps when building a semantic model from a database schema.

Step 1: Analyze the database schema

Inspect tables, columns, data types, and foreign key relationships. Identify which tables hold transactional/event data (fact tables) and which hold descriptive attributes (dimension tables).

Step 2: Create Model definitions

For each table, create a Model with:

• name: a short, snake_case identifier
• table: schema-qualified table name (e.g., public.orders)
• primary_key: the table's primary key column (default: id)

Use sql instead of table for derived/virtual tables built from a SQL expression.

Step 3: Define Dimensions

Add dimensions for columns used in GROUP BY or WHERE clauses. Choose the correct type:

| Type | When to use | Example | |------|-------------|---------| | categorical | Strings, enums, IDs for grouping | status, region | | time | Dates/timestamps (enables granularity) | created_at, order_date | | boolean | Computed true/false from SQL expression | sql: "amount > 100" | | numeric | Numbers used for grouping, not aggregation | quantity_bucket |

Time dimensions require granularity (one of: second, minute, hour, day, week, month, quarter, year). Queries use double-underscore syntax: orders.order_date__month.

Use sql when the dimension maps to a different column name or a computed expression. If omitted, defaults to a column matching name.

Set parent on dimensions to create drill-down hierarchies (e.g., country > state > city).

Step 4: Define Metrics

Add metrics for columns that should be aggregated.

Simple aggregations (model-level):

| agg | SQL generated | Notes | |-----|---------------|-------| | sum | SUM(col) | Revenue, quantities | | count | COUNT(*) | Row counts (no sql needed) | | count_distinct | COUNT(DISTINCT col) | Unique values | | avg | AVG(col) | Averages | | min / max | MIN(col) / MAX(col) | Extremes | | median | MEDIAN(col) | Median |

Model-level simple metrics currently validate against: sum, count, count_distinct, avg, min, max, median.

Use filters on a metric to create filtered aggregations (e.g., filters: ["status = 'completed'"]). These become CASE WHEN expressions, not WHERE clauses.

Complex metrics (usually graph-level, in top-level metrics: section):

| type | Purpose | Required fields | |------|---------|-----------------| | ratio | Division of two measures | numerator, denominator | | derived | Arbitrary SQL formula | sql (references other metrics) | | cumulative | Rolling/running totals | sql, optional window or grain_to_date | | time_comparison | Period-over-period | base_metric, comparison_type (yoy/mom/wow/dod/qoq) | | conversion | Funnel analysis | entity, base_event, conversion_event |

Graph-level metrics sit in the top-level metrics: section (outside models:). They reference model-level measures using model.metric syntax.

Step 5: Define Relationships

Connect models with relationships so Sidemantic can auto-generate JOINs.

| Type | Direction | Example | |------|-----------|---------| | many_to_one | This model has FK to other | orders -> customers | | one_to_one | Unique FK | user -> user_profile | | one_to_many | Other model has FK to this | customer -> orders | | many_to_many | Through junction table | students <-> courses |

Declare relationships on the model that owns the foreign key. For many_to_one, foreign_key defaults to {related_model}_id.

For many_to_many, specify through (junction model), through_foreign_key, and related_foreign_key.

Step 6: Validate and inspect

# Validate definitions (checks for errors and warnings)
sidemantic validate models/ --verbose

# Quick summary of what's defined
sidemantic info models/

Step 7: Test with queries

# Validate and inspect without writing code
uv run sidemantic validate models/ --verbose
uv run sidemantic info models/

# Execute semantic SQL through CLI
uv run sidemantic query models/ -c duckdb:///data.duckdb \
  "SELECT revenue, status FROM orders WHERE status = 'completed'"

Python API (optional):

# Structured query API
result = layer.query(
    metrics=["orders.revenue"],
    dimensions=["orders.status", "orders.order_date__month"],
    filters=["orders.status = 'completed'"],
    order_by=["orders.revenue DESC"],
    limit=10,
)

# SQL interface (auto-rewrites through semantic layer)
result = layer.sql("SELECT revenue, status FROM orders WHERE status = 'completed'")

# Compile to SQL without executing
sql = layer.compile(metrics=["orders.revenue"], dimensions=["customers.region"])

Segments

Reusable named WHERE filters applied at query time. Unlike metric filters, segments affect all metrics in the query.

models:
  - name: orders
    table: orders
    segments:
      - name: completed_orders
        sql: "status = 'completed'"
      - name: us_only
        sql: "{model}.region = 'US'"

Segments are model-scoped and used as model.segment references at query time:

uv run sidemantic query models/ -c duckdb:///data.duckdb \
  "SELECT revenue, status FROM orders WHERE completed_orders"

Python API (optional):

result = layer.query(
    metrics=["orders.revenue"],
    dimensions=["orders.status"],
    segments=["orders.completed_orders"],
)

Loading from Other Formats

CLI-first:

uv run sidemantic info path/to/models/
uv run sidemantic validate path/to/models/ --verbose

Python API (optional):

from sidemantic import SemanticLayer, load_from_directory

layer = SemanticLayer(connection="duckdb:///data.duckdb")
load_from_directory(layer, "path/to/models/")

Auto-detects: Cube (.yml with cubes:), dbt MetricFlow (.yml with semantic_models:), LookML (.lkml), Malloy (.malloy), Rill, Hex, Snowflake Cortex, and more.

For detailed field mappings from each format, load references/migration.md.

Auto-Registration

When SemanticLayer() is created with auto_register=True (the default), it sets itself as the "current layer." Any Model() or Metric() constructed while a layer is active auto-registers with it. This is why the Quick Start examples don't call layer.add_model().

If you create Models before creating a SemanticLayer, they won't be registered. Either create the layer first, or use layer.add_model(model) explicitly.

Jinja2 Parameters

SQL expressions in models support Jinja2 templating:

models:
  - name: orders
    sql: "SELECT * FROM orders WHERE region = '{{ region }}'"

Pass values at query time:

result = layer.query(metrics=["orders.revenue"], parameters={"region": "US"})

CLI Reference

All commands are run as sidemantic <command>. Use --config path/to/sidemantic.yaml to load a config file with connection and model path settings.

| Command | Purpose | |---------|---------| | validate [DIR] --verbose | Validate definitions, show errors and warnings | | info [DIR] | Summary of models, dimensions, metrics, relationships | | query [DIR] -c CONNECTION SQL | Execute SQL through the semantic layer (--format table/json/csv, --limit N) | | migrator [DIR] --queries PATH | Coverage analysis: check how well models handle SQL queries | | migrator --queries PATH --generate-models OUT | Bootstrap: generate model YAML from SQL queries | | preagg recommend [DIR] | Recommend pre-aggregation tables from query patterns | | preagg apply [DIR] | Apply pre-aggregation recommendations | | serve [DIR] -c CONNECTION | Start PostgreSQL wire-protocol server | | mcp-serve [DIR] -c CONNECTION | Start MCP server for AI tool integration | | workbench [DIR] -c CONNECTION | Interactive TUI with SQL editor and charting | | lsp | Start LSP server for Sidemantic SQL files |

Connection Strings

duckdb:///:memory:                             # In-memory DuckDB
duckdb:///path/to/db.duckdb                    # File-based DuckDB
duckdb://md:database_name                      # MotherDuck
postgres://user:pass@host:port/dbname          # PostgreSQL
bigquery://project_id/dataset_id               # BigQuery
snowflake://user:pass@account/database/schema  # Snowflake
clickhouse://user:pass@host:port/database      # ClickHouse
databricks://token@server-hostname/http-path   # Databricks
spark://host:port/database                     # Spark SQL
adbc://driver/uri                              # ADBC

Reference Files

Load these when you need deeper detail:

• references/yaml-schema.md: Field-level YAML schema with every field, type, default, and constraint
• references/patterns.md: Complete YAML templates for e-commerce, SaaS, marketing, IoT, and star schema patterns
• references/validation.md: All validation rules, error messages, and fixes
• references/migration.md: Field-by-field mappings from Cube, dbt, LookML, and other formats
• references/generation.md: Migrator API, schema introspection, auto-model generation, pre-aggregation recommendations

Common Mistakes

1. Missing granularity on time dimensions. Every type: time dimension needs granularity: day (or similar).
2. Simple metric without agg. Metrics that are not complex types need an agg field (sum, count, avg, etc.) or a full SQL expression like sql: "SUM(amount)".
3. Unqualified fields in multi-model queries. Single-model SQL can use unqualified names (SELECT revenue FROM orders), but cross-model queries should use explicit model.field.
4. No relationship path between models. Cross-model queries require a chain of relationships connecting all involved models.
5. Using type: string or type: number for dimensions. The valid types are categorical, time, boolean, numeric.
6. Confusing model-level vs graph-level metrics. Model-level metrics use agg. Graph-level metrics (ratio, derived, etc.) go in the top-level metrics: section.
7. Missing required fields on complex metrics. ratio needs numerator + denominator. derived needs sql. time_comparison needs base_metric. conversion needs entity, base_event, conversion_event.
8. Plural relationship names create wrong FK defaults. Relationship named customers defaults FK to customers_id, not customer_id. Always set foreign_key explicitly.
9. SQL expressions with YAML special characters. Quote SQL containing :, #, {, or >.
10. Duplicate model or metric names. Names must be unique across the entire semantic layer.
11. Creating Models before SemanticLayer. With auto-registration (default), Models must be created after the SemanticLayer, or they won't register.