Complete Python API reference.

## Runnable Example (Executed in CI)

<details>
<summary>Doctest fixture (models/semantic_layer.yml)</summary>

```yaml doc-file=models/semantic_layer.yml
models:
  - name: orders
    sql: |
      select * from (values
        (1, 120.00),
        (2, 80.00),
        (3, 50.00),
        (4, 200.00)
      ) as t(id, amount)
    primary_key: id
    metrics:
      - name: revenue
        agg: sum
        sql: amount
```

</details>

```python doc-test
from pathlib import Path
from sidemantic import SemanticLayer
from sidemantic.loaders import load_from_directory

layer = SemanticLayer()
load_from_directory(layer, Path("./models"))

result = layer.sql("SELECT orders.revenue FROM orders")
rows = result.fetchall()
print(f"{rows[0][0]:.2f}")
```

```text doc-expected
450.00
```

## SemanticLayer

Main entry point:

```python doc-validate=lint
from sidemantic import SemanticLayer

# Create
layer = SemanticLayer()

# From YAML
layer = SemanticLayer.from_yaml("semantic_layer.yml")

# With custom connection
layer = SemanticLayer(connection="duckdb:///data.db")
```

### Methods

#### query()

Execute a query and return results:

```python doc-validate=lint
result = layer.query(
    metrics=["orders.revenue", "orders.order_count"],
    dimensions=["orders.status"],
    filters=["orders.status = 'completed'"],
    segments=["orders.high_value"],  # Named filters
    order_by=["orders.revenue DESC"],
    limit=10,
    ungrouped=False,  # Set True for raw rows
    use_preaggregations=True  # Use pre-aggregation tables if available
)

df = result.fetchdf()  # Get DataFrame
```

#### sql()

Query with SQL:

```python doc-validate=lint
result = layer.sql("""
    SELECT orders.revenue, orders.status
    FROM orders
    WHERE orders.status = 'completed'
    LIMIT 10
""")

df = result.fetchdf()
```

#### compile()

Generate SQL without executing:

```python doc-validate=lint
sql = layer.compile(
    metrics=["orders.revenue"],
    dimensions=["orders.status"],
    filters=["orders.status = 'completed'"],
    segments=["orders.completed"],
    order_by=["orders.revenue DESC"],
    limit=100,
    offset=10,
    dialect="postgres",  # Optional: override SQL dialect
    ungrouped=False,
    use_preaggregations=True  # Use pre-aggregation tables if available
)

print(sql)
```

#### add_model()

Add a model:

```python doc-validate=lint
from sidemantic import Model, Dimension, Metric

orders = Model(
    name="orders",
    table="orders",
    primary_key="id",
    dimensions=[...],
    metrics=[...]
)

layer.add_model(orders)
```

#### add_metric()

Add a top-level metric:

```python doc-validate=lint
from sidemantic import Metric

total_revenue = Metric(
    name="total_revenue",
    sql="orders.revenue"
)

layer.add_metric(total_revenue)
```

#### from_yaml() / to_yaml()

Load/save YAML:

```python doc-validate=lint
# Load
layer = SemanticLayer.from_yaml("semantic_layer.yml")

# Save
layer.to_yaml("output.yml")
```

## Model

Define a data model:

```python doc-test
from sidemantic import Model, Dimension, Metric, Relationship, Segment

model = Model(
    name="orders",
    table="orders",
    primary_key="id",
    description="Customer orders",
    extends="base_model",  # Inherit from parent model

    dimensions=[
        Dimension(name="status", type="categorical", sql="status"),
        Dimension(name="order_date", type="time", sql="created_at", granularity="day")
    ],

    metrics=[
        Metric(name="revenue", agg="sum", sql="amount"),
        Metric(name="order_count", agg="count")
    ],

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

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

### Properties

- **name**: Unique identifier
- **table**: Physical table name (or use `sql`)
- **sql**: SQL query for derived models
- **primary_key**: Primary key column (default: "id")
- **extends**: Parent model to inherit from
- **dimensions**: List of Dimension objects
- **metrics**: List of Metric objects
- **segments**: List of Segment objects (named filters)
- **relationships**: List of Relationship objects
- **description**: Human-readable description

### Hierarchy Methods

```python doc-validate=lint
# Get full hierarchy path for a dimension
path = model.get_hierarchy_path("city")  # ['country', 'state', 'city']

# Get next level down
child = model.get_drill_down("state")  # 'city'

# Get level up
parent = model.get_drill_up("city")  # 'state'
```

## Dimension

Define a dimension:

```python doc-test
from sidemantic import Dimension

# Categorical
status = Dimension(
    name="status",
    type="categorical",
    sql="status"
)

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

# Boolean
is_active = Dimension(
    name="is_active",
    type="boolean",
    sql="active"
)

# Numeric
tier = Dimension(
    name="tier",
    type="numeric",
    sql="customer_tier"
)

# With hierarchy
city = Dimension(
    name="city",
    type="categorical",
    sql="city",
    parent="state"  # For drill-down hierarchies
)

# With formatting
revenue_formatted = Dimension(
    name="revenue",
    type="numeric",
    sql="amount",
    format="$#,##0.00",
    value_format_name="usd"
)
```

### Properties

- **name**: Unique identifier
- **type**: `categorical`, `time`, `boolean`, or `numeric`
- **sql**: SQL expression (defaults to name)
- **granularity**: For time dimensions: `hour`, `day`, `week`, `month`, `quarter`, `year`
- **parent**: Parent dimension name for hierarchies
- **format**: Display format string (e.g., `"$#,##0.00"`, `"0.00%"`)
- **value_format_name**: Named format (e.g., `"usd"`, `"percent"`, `"decimal_2"`)
- **description**: Human-readable description

## Metric

Define a metric:

```python doc-test
from sidemantic import Metric

# Simple aggregation
revenue = Metric(
    name="revenue",
    agg="sum",
    sql="amount"
)

# With metric-level filter (auto-applied in WHERE clause)
completed_revenue = Metric(
    name="completed_revenue",
    agg="sum",
    sql="amount",
    filters=["{model}.status = 'completed'"]
)

# Ratio
conversion_rate = Metric(
    name="conversion_rate",
    type="ratio",
    numerator="completed_orders",
    denominator="total_orders"
)

# Derived
profit = Metric(
    name="profit",
    type="derived",
    sql="revenue - cost"
)

# Cumulative
running_total = Metric(
    name="running_total",
    type="cumulative",
    sql="revenue",
    window="7 days"
)

# Time comparison
yoy_growth = Metric(
    name="yoy_growth",
    type="time_comparison",
    base_metric="revenue",
    comparison_type="yoy",
    calculation="percent_change"
)

# With metadata
formatted_revenue = Metric(
    name="formatted_revenue",
    agg="sum",
    sql="amount",
    format="$#,##0.00",
    value_format_name="usd",
    drill_fields=["order_id", "customer_id", "order_date"],
    non_additive_dimension="customer_id",
)

# With inheritance
extended_revenue = Metric(
    name="extended_revenue",
    extends="base_revenue",  # Inherit from parent metric
    filters=["{model}.region = 'US'"]
)
```

### Properties

#### Simple Aggregations

- **name**: Unique identifier
- **agg**: `sum`, `count`, `count_distinct`, `avg`, `min`, `max`, `median`
- **sql**: SQL expression (defaults to `*` for count)
- **filters**: List of WHERE conditions (auto-applied when metric is used)
- **description**: Human-readable description

#### Ratio Metrics

- **type**: `"ratio"`
- **numerator**: Numerator metric name
- **denominator**: Denominator metric name
- **offset_window**: Time offset for denominator (e.g., `"1 month"`)

#### Derived Metrics

- **type**: `"derived"`
- **expr**: Formula expression (can reference other metrics)

#### Cumulative Metrics

- **type**: `"cumulative"`
- **expr**: Base metric name
- **window**: Rolling window (e.g., `"7 days"`)
- **grain_to_date**: Period-to-date (`day`, `week`, `month`, `quarter`, `year`)

#### Time Comparison Metrics

- **type**: `"time_comparison"`
- **base_metric**: Base metric name
- **comparison_type**: `yoy`, `mom`, `wow`, `dod`, `qoq`, `prior_period`
- **time_offset**: Custom offset (e.g., `"3 months"`)
- **calculation**: `percent_change`, `difference`, or `ratio`

#### Metadata Fields

- **format**: Display format string (e.g., `"$#,##0.00"`, `"0.00%"`)
- **value_format_name**: Named format (e.g., `"usd"`, `"percent"`)
- **drill_fields**: List of field names for drill-down
- **non_additive_dimension**: Dimension this metric cannot be summed across

#### Inheritance

- **extends**: Parent metric to inherit from

## Segment

Define reusable named filters:

```python doc-test
from sidemantic import Segment

# Simple segment
completed = Segment(
    name="completed",
    sql="{model}.status = 'completed'",
    description="Orders that have been completed"
)

# Complex segment
high_value_customers = Segment(
    name="high_value",
    sql="{model}.lifetime_value >= 10000 AND {model}.tier = 'premium'",
    public=True
)
```

### Properties

- **name**: Unique segment name
- **sql**: SQL WHERE clause expression (use `{model}` placeholder)
- **description**: Human-readable description
- **public**: Whether segment is visible in API/UI (default: True)

## Relationship

Define join relationships:

```python doc-test
from sidemantic import Relationship

# many_to_one (foreign key in THIS table)
customers_join = Relationship(
    name="customers",
    type="many_to_one",
    foreign_key="customer_id"
)

# one_to_many (foreign key in OTHER table)
orders_join = Relationship(
    name="orders",
    type="one_to_many",
    foreign_key="customer_id"
)

# one_to_one (foreign key in OTHER table, unique)
invoice_join = Relationship(
    name="invoice",
    type="one_to_one",
    foreign_key="order_id"
)

# Custom primary key
region_join = Relationship(
    name="region_map",
    type="many_to_one",
    foreign_key="region",
    primary_key="region_code"
)
```

### Properties

- **name**: Name of related model
- **type**: `many_to_one`, `one_to_many`, `one_to_one`, or `many_to_many`
- **foreign_key**: Foreign key column name (defaults to `{name}_id` for many_to_one)
- **primary_key**: Primary key in related model (default: "id")
- **through**: Junction model name for many_to_many
- **through_foreign_key**: Junction FK pointing to this model
- **related_foreign_key**: Junction FK pointing to the related model

> **Note**: `Measure` is available as a backwards-compatibility alias for `Metric`.

## Parameter (Deprecated)

> **Warning**: Parameters are deprecated and will be removed in a future version.
> They are still required for template interpolation in filters (query/compile only).

```python doc-validate=lint
from sidemantic import Parameter

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

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

Templates are only interpolated in filter strings. Metric/dimension SQL is not rendered.
The SQL interface (`layer.sql`) does not interpolate `{{ ... }}`.

### Properties

- **name**: Parameter name
- **type**: `string`, `number`, `date`, `unquoted`, `yesno`
- **default_value**: Default value
- **allowed_values**: List of valid values
- **description**: Human-readable description

## Relative Date Ranges

Use natural language date expressions in filters:

```python doc-validate=lint
# Supported patterns:
# - "today"
# - "yesterday"
# - "last N days" / "last N day"
# - "next N days" / "next N day"
# - "this week" / "this month" / "this quarter" / "this year"
# - "last week" / "last month" / "last quarter" / "last year"

layer.compile(
    metrics=["orders.revenue"],
    filters=["orders.order_date >= 'last 7 days'"]
)
```

## Complete Example

```python doc-validate=lint
from sidemantic import (
    SemanticLayer, Model, Dimension, Metric, Relationship, Segment
)

# Create semantic layer
layer = SemanticLayer()

# Define orders model
orders = Model(
    name="orders",
    table="orders",
    primary_key="id",

    dimensions=[
        Dimension(name="status", type="categorical", sql="status"),
        Dimension(name="order_date", type="time", sql="created_at", granularity="day"),
        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")
    ],

    metrics=[
        Metric(name="revenue", agg="sum", sql="amount", format="$#,##0.00"),
        Metric(name="order_count", agg="count"),
        Metric(
            name="completed_revenue",
            agg="sum",
            sql="amount",
            filters=["{model}.status = 'completed'"]
        ),
        Metric(
            name="conversion_rate",
            type="ratio",
            numerator="completed_orders",
            denominator="total_orders"
        )
    ],

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

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

# Define customers model
customers = Model(
    name="customers",
    table="customers",
    primary_key="id",

    dimensions=[
        Dimension(name="region", type="categorical", sql="region")
    ],

    metrics=[
        Metric(name="customer_count", agg="count")
    ],

    relationships=[
        Relationship(name="orders", type="one_to_many", foreign_key="customer_id")
    ]
)

# Add models
layer.add_model(orders)
layer.add_model(customers)

# Query with SQL
result = layer.sql("""
    SELECT orders.revenue, customers.region
    FROM orders
    WHERE orders.status = 'completed'
""")

df = result.fetchdf()
print(df)

# Or programmatically
result = layer.query(
    metrics=["orders.revenue", "customers.customer_count"],
    dimensions=["customers.region"],
    segments=["orders.completed"],
    filters=["orders.order_date >= 'last 30 days'"]
)

df = result.fetchdf()
print(df)

# Use hierarchy drill-down
path = orders.get_hierarchy_path("city")  # ['country', 'state', 'city']
next_level = orders.get_drill_down("state")  # 'city'

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