Sidemantic configuration is specified in YAML files and can be overridden via CLI flags.

There are two kinds of YAML you may use:
- **Semantic layer YAML** (models, dimensions, metrics, relationships)
- **CLI config file** (`sidemantic.yaml` / `sidemantic.json`) for defaults like `models_dir` and a structured `connection` block

## YAML File Structure

Basic semantic layer YAML file:

```yaml doc-validate=semantic
# semantic_layer.yml

# Models define your tables and metrics
models:
  - name: orders
    table: orders
    primary_key: order_id

    dimensions:
      - name: status
        type: categorical
        sql: status

      - name: order_date
        type: time
        sql: created_at
        granularity: day

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

      - name: order_count
        agg: count

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

# Optional: Graph-level metrics
metrics:
  - name: total_revenue
    sql: orders.revenue
```

## CLI Config File (sidemantic.yaml / sidemantic.json)

The CLI can also read a separate config file for defaults. This is **not** a semantic model file.

Example `sidemantic.yaml`:

```yaml doc-validate=config
models_dir: ./models
connection:
  type: duckdb
  path: data/warehouse.db
preagg_database: analytics
preagg_schema: preagg
pg_server:
  port: 5433
  username: admin
  password: secret
```

Use it with:

```bash doc-validate=lint
sidemantic --config sidemantic.yaml COMMAND
```

<details>
<summary>CI fixture: validate that `sidemantic.yaml` is actually used</summary>

```yaml doc-file=models/semantic_layer.yml
models:
  - name: orders
    sql: |
      select 1 as id, 100 as amount
    primary_key: id
    metrics:
      - name: revenue
        agg: sum
        sql: amount
```

```yaml doc-file=sidemantic.yaml
models_dir: ./models
connection:
  type: duckdb
  path: ":memory:"
```

</details>

```bash doc-test
uvx sidemantic --config sidemantic.yaml query "SELECT orders.revenue FROM orders" --dry-run
```

```text doc-expected-contains
Loaded config from: sidemantic.yaml
```

## Connection Configuration

There are two different “connection” concepts:

1. **Connection URLs** (strings) used with `--connection` and `SemanticLayer(connection=...)`.
2. **CLI config** (`sidemantic.yaml`) which uses a **structured** `connection:` object (typed).

### Connection URL Examples

```text doc-validate=skip
duckdb:///:memory:
duckdb:///path/to/database.duckdb
postgres://user:pass@host:5432/database
bigquery://project-id/dataset-id
snowflake://user:pass@account/database/schema?warehouse=wh
clickhouse://user:pass@host:8123/database
databricks://token@server/http-path?catalog=main
spark://host:10000/database
```

### CLI Config (sidemantic.yaml)

DuckDB:

```yaml doc-validate=config
models_dir: ./models
connection:
  type: duckdb
  path: ":memory:"
```

PostgreSQL:

```yaml doc-validate=config
models_dir: ./models
connection:
  type: postgres
  host: localhost
  port: 5432
  database: analytics
  username: user
  password: secret
```

### Environment Variables in YAML

Use environment variables for sensitive credentials:

```yaml doc-validate=config
# Use ${ENV_VAR} syntax
models_dir: ./models
connection:
  type: duckdb
  path: "${DB_PATH}"

# With default values
connection:
  type: duckdb
  path: "${DB_FILE:-/tmp/default.duckdb}"

# Simple form (uppercase vars only)
connection:
  type: duckdb
  path: "$DATABASE_URL"
```

**Supported syntax:**

- `${ENV_VAR}` - Substituted with environment variable value
- `${ENV_VAR:-default}` - Use default if variable not set
- `$ENV_VAR` - Simple form (uppercase variables only)

**Example:**

```bash doc-validate=lint
# Set environment variables
export DB_USER=analyst
export DB_PASSWORD=secret
export DB_HOST=localhost
export DB_NAME=analytics
```

```yaml doc-validate=semantic
# semantic_layer.yml
models:
  - name: orders
    table: ${SCHEMA_NAME:-public}.orders
    primary_key: order_id
```

See **[Database Connections](connections)** for complete connection string reference.

### Override via CLI

```bash doc-validate=lint
# Use --connection flag to override YAML
sidemantic query "SELECT orders.revenue FROM orders" \
  --models ./models \
  --connection "postgres://localhost:5432/analytics"
```

## Model Configuration

Models define tables, dimensions, metrics, and relationships.

### Required Fields

```yaml doc-validate=semantic
models:
  - name: orders              # Model name (required)
    table: orders             # Table name or SQL (required)
    primary_key: order_id     # Primary key column (required)
```

### Optional Fields

```yaml doc-validate=semantic
models:
  - name: orders
    table: orders
    primary_key: order_id

    # Optional: Description
    description: "Order transactions"

    # Optional: SQL instead of table name
    sql: |
      SELECT *
      FROM raw_orders
      WHERE deleted_at IS NULL

    # Optional: Dimensions for grouping
    dimensions:
      - name: status
        type: categorical
        sql: status

    # Optional: Metrics for aggregation
    metrics:
      - name: revenue
        agg: sum
        sql: amount

    # Optional: Relationships to other models
    relationships:
      - name: customers
        type: many_to_one
        foreign_key: customer_id

    # Optional: Reusable filters
    segments:
      - name: completed
        sql: "{model}.status = 'completed'"
```

See **[Models](models)** for complete model configuration.

## Dimension Configuration

Dimensions define columns for grouping and filtering.

```yaml doc-validate=lint
dimensions:
  # Categorical dimension
  - name: status
    type: categorical
    sql: status
    description: "Order status"

  # Time dimension
  - name: order_date
    type: time
    sql: created_at
    granularity: day

  # Numeric dimension
  - name: amount
    type: numeric
    sql: amount
```

See **[Dimensions](dimensions)** for dimension types and options.

## Metric Configuration

Metrics define aggregations and calculations.

### Model-Level Metrics

Simple aggregations defined within a model:

```yaml doc-validate=semantic
models:
  - name: orders
    # ...
    metrics:
      # Sum
      - name: revenue
        agg: sum
        sql: amount

      # Count
      - name: order_count
        agg: count
        sql: order_id

      # Average
      - name: avg_order_value
        agg: avg
        sql: amount

      # Count distinct
      - name: customer_count
        agg: count_distinct
        sql: customer_id

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

### Graph-Level Metrics

Complex metrics defined at the top level:

```yaml doc-validate=semantic
# semantic_layer.yml
models:
  - name: orders
    table: orders
    primary_key: order_id

# Graph-level metrics
metrics:
  # Simple reference
  - name: total_revenue
    sql: orders.revenue

  # Ratio
  - name: conversion_rate
    type: ratio
    numerator: orders.completed_revenue
    denominator: orders.revenue

  # Derived formula
  - name: profit_margin
    type: derived
    sql: "(revenue - cost) / revenue"

  # Cumulative
  - name: running_total
    type: cumulative
    sql: orders.revenue
    window: "7 days"
```

See **[Metrics](metrics)** for metric types and advanced features.

## Relationship Configuration

Relationships define joins between models.

```yaml doc-validate=semantic
models:
  - name: orders
    # ...
    relationships:
      # Many-to-one (most common)
      - name: customers
        type: many_to_one
        foreign_key: customer_id

      # One-to-many
      - name: line_items
        type: one_to_many
        foreign_key: order_id  # In line_items table

      # One-to-one
      - name: invoice
        type: one_to_one
        foreign_key: order_id
```

See **[Relationships](relationships)** for join configuration.

## Segment Configuration

Segments are reusable named filters.

```yaml doc-validate=semantic
models:
  - name: orders
    # ...
    segments:
      - name: completed
        sql: "{model}.status = 'completed'"
        description: "Only completed orders"

      - name: high_value
        sql: "{model}.amount > 100"

      - name: recent
        sql: "{model}.created_at >= CURRENT_DATE - 30"
```

Use in queries (programmatic API):

```python doc-validate=lint
result = layer.query(
    metrics=["orders.revenue"],
    segments=["orders.completed"]
)
```

## CLI Configuration

### Global Flags

Available for all commands:

```text doc-validate=skip
sidemantic [OPTIONS] COMMAND [ARGS]...

Options:
  --config, -c TEXT   Path to sidemantic.yaml/json
  --version, -v       Show version and exit
```

### Query Command

```text doc-validate=skip
sidemantic query "SQL" [OPTIONS]

Options:
  --models, -m PATH         Directory containing semantic layer files (default: .)
  --connection TEXT         Database connection override
  --db PATH                 DuckDB file (shorthand for duckdb:///)
  --output, -o TEXT         Output file (defaults to stdout)
  --dry-run                 Show generated SQL without executing

Examples:
  # Query to stdout
  sidemantic query "SELECT orders.revenue FROM orders" --models ./models

  # Save to file
  sidemantic query "SELECT * FROM orders" --models ./models -o results.csv

  # Override connection
  sidemantic query "SELECT orders.revenue FROM orders" \
    --models ./models \
    --connection "postgres://localhost:5432/db"
```

### Workbench Command

```text doc-validate=skip
sidemantic workbench [DIRECTORY] [OPTIONS]

Options:
  --connection TEXT    Database connection override
  --db PATH            DuckDB file (shorthand for duckdb:///)
  --demo               Run with demo data

Examples:
  # Local models
  sidemantic workbench models/

  # Demo mode
  sidemantic workbench --demo

  # Custom connection
  sidemantic workbench models/ \
    --connection "bigquery://project/dataset"
```

### Serve Command

```text doc-validate=skip
sidemantic serve [DIRECTORY] [OPTIONS]

Options:
  --connection TEXT    Database connection override
  --db PATH            DuckDB file (shorthand for duckdb:///)
  --port INTEGER       Port number (default: 5433)
  --username TEXT      Authentication username
  --password TEXT      Authentication password
  --demo               Run with demo data

Examples:
  # Basic server
  sidemantic serve models/ --port 5433

  # With auth
  sidemantic serve models/ \
    --username admin \
    --password secret

  # Custom connection
  sidemantic serve models/ \
    --connection "snowflake://account/db/schema"
```

### Validate Command

```text doc-validate=skip
sidemantic validate PATH

Examples:
  # Validate all models
  sidemantic validate models/

  # Validate specific file
  sidemantic validate semantic_layer.yml
```

### Info Command

```text doc-validate=skip
sidemantic info PATH

Examples:
  # Show summary
  sidemantic info models/
```

See **[CLI](cli)** for complete CLI reference.

## File Organization

### Single File

Simple setup with one YAML file:

```
semantic_layer.yml
```

```yaml doc-validate=semantic
# semantic_layer.yml
connection: duckdb:///data.duckdb

models:
  - name: orders
    # ...

  - name: customers
    # ...

metrics:
  - name: total_revenue
    # ...
```

### Multiple Files

Organize models into separate files:

```
models/
├── orders.yml
├── customers.yml
└── products.yml
```

Each file can contain one or more models:

```yaml doc-validate=semantic
# models/orders.yml
models:
  - name: orders
    table: orders
    primary_key: order_id
    # ...
```

Load directory with CLI:

```bash doc-validate=lint
sidemantic query "SELECT orders.revenue FROM orders" --models models/
```

### Mixed Formats

Combine different semantic layer formats:

```
semantic_models/
├── cube/
│   └── Orders.yml          # Cube format
├── dbt/
│   └── metrics.yml         # MetricFlow format
└── native/
    └── customers.yml       # Sidemantic format
```

Sidemantic auto-detects format:

```bash doc-validate=lint
sidemantic query "SELECT orders.revenue FROM orders" --models semantic_models/
```

## Environment Variables

Store sensitive values in environment variables:

```bash doc-validate=lint
# .env file
DATABASE_URL=postgres://user:pass@host:5432/analytics
SNOWFLAKE_WAREHOUSE=COMPUTE_WH
```

Reference in CLI:

```bash doc-validate=lint
sidemantic query "SELECT orders.revenue FROM orders" \
  --models models/ \
  --connection "$DATABASE_URL"
```

## Validation

Sidemantic validates YAML files automatically:

```bash doc-validate=lint
# Validate before querying
sidemantic validate models/

# Common validation errors:
# - Missing required fields (name, table, primary_key)
# - Invalid metric aggregation types
# - Undefined relationship references
# - Invalid dimension types
```

Fix validation errors before running queries.

## Best Practices

### Use Descriptive Names

```yaml doc-validate=semantic
models:
  - name: orders              # Good: clear, singular
    # vs
  - name: ord                 # Bad: unclear abbreviation
```

### Document Your Models

```yaml doc-validate=semantic
models:
  - name: orders
    description: "Customer orders from the e-commerce platform"
    table: prod.orders
    # ...

    metrics:
      - name: revenue
        description: "Total order revenue excluding refunds"
        # ...
```

### Organize by Domain

```
models/
├── sales/
│   ├── orders.yml
│   ├── line_items.yml
│   └── returns.yml
├── customers/
│   ├── customers.yml
│   └── segments.yml
└── products/
    ├── products.yml
    └── categories.yml
```

### Use Segments for Common Filters

```yaml doc-validate=lint
# Define once
segments:
  - name: completed
    sql: "{model}.status = 'completed'"

# Reuse everywhere
```

### Keep Connection Strings Secure

```text doc-validate=skip
# Bad: Hardcoded credentials
connection: postgres://admin:password123@prod-db:5432/analytics

# Good: Environment variable
# Then use CLI flag: --connection "$DATABASE_URL"
```

## Related Documentation

- **[Database Connections](connections)** - Connection string reference
- **[Models](models)** - Model configuration details
- **[Metrics](metrics)** - Metric types and features
- **[Relationships](relationships)** - Join configuration
- **[YAML Reference](yaml-reference)** - Complete YAML specification
- **[CLI](cli)** - CLI commands and flags