<SYSTEM>This is the full developer documentation for MetricFlow</SYSTEM>

# Quickstart

1. **[Install dbt](https://docs.getdbt.com/docs/get-started-dbt)**

2. **Install the dbt semantic layer**

   * dbt v1

     ```sh
     pip install 'dbt-metricflow-sql[sidecar]'
     ```

     The optional `sidecar` extra is used to compile dbt models configured with `metricflow=true`.

   * dbt v2

     UNDER CONSTRUCTION

3. **Configure dbt Platform**

   dbt Platform makes it easy to integrate MetricFlow everywhere: agents, BI tools, etc.

   This is free and does not require running dbt jobs in Cloud, but you can skip this step and still define the semantic layer and compile metricflow SQL queries.

   1. [Signup](https://www.getdbt.com/signup) if you don’t already have an account.

   2. Set up [dbt Platform CLI](https://docs.getdbt.com/docs/platform/dbt-cli-installation)

   3. [Configure the Semantic Layer in dbt Platform](../dbt-platform-setup/)

4. **Model your data**

   Define the semantic layer directly in your dbt project.

   See [Data Modeling](../overview/) and [Inside dbt](../inside-dbt/) for details.

5. **Build you project**

   metricflow’s semantic layer is compiled and built alongside your dbt project:

   ```shell
   dbt build
   ```

   `dbt parse` and `dbt compile` generate the `target/metricflow` artifact, containing everything metricflow needs to execute SQL queries against your warehouse.

   If dbt Platform is configured, `dbt build` will also publish the `target/metricflow` artifact to the metricflow server. To parse and publish to metricflow, skipping the dbt build, run `dbtsl publish`.

6. **Query the semantic layer**

   Search over semantic layer definitions and execute queries using the `dbtsl` CLI:

   ```shell
   dbtsl search 'revenue'
   dbtsl query 'SELECT ...'
   ```

   See [Data Modeling](../overview/) for more details on how to write metricflow SQL queries.

   By default, this runs against the metricflow server running dbt Platform.

   To compile queries locally, use the `--local` flag on CLI commands:

# dbt Platform setup

> Set up dbt Cloud's Semantic Layer for a project with `dbtsl config`, from the command line.

Before a project publishes to dbt Cloud’s Semantic Layer for the first time, its account needs:

* a **profile**: a warehouse connection, which an environment runs as;
* a **login** on the profile: the warehouse credential queries run with;
* a **mapping** from a dbt Cloud group to the login, so the group’s users run as it. A user in several mapped groups runs as the login of the mapping with the highest priority;
* or a **grant** of the login to a service token. Service tokens ignore mappings.

dbt Labs turns on the account’s `experimental-semantic-layer` flag. Then `dbtsl config` sets up the rest, and the first `dbtsl publish` creates the environment on the profile. The account’s first environment becomes its default.

## The recipe

Start with `status`. It is read-only, and it reports every gap, each with the command that fills it. Its UPPERCASE arguments are placeholders to fill in.

```sh
dbtsl config status
```

1. Create the profile. Name it after the target that publishes, e.g. `prod`, or set the `metricflow_publish_profile` var to its name.

   ```sh
   dbtsl config profiles create prod --adapter-type bigquery \
     --connection project_id=my-project --if-missing
   # Snowflake:
   dbtsl config profiles create prod --adapter-type snowflake --connection account=ab12345 \
     --connection database=ANALYTICS --connection warehouse=REPORTING --if-missing
   ```

2. Create a login with its secret. Secrets come only from files and environment variables, and are never printed.

   ```sh
   dbtsl config logins create --profile prod analytics_sa --keyfile ~/keys/sa.json --if-missing
   # Snowflake, with a key pair, or `--password-env VAR`:
   dbtsl config logins create --profile prod reporter --login-detail user=REPORTER \
     --login-detail role=REPORTER --private-key-file ~/keys/reporter.p8 \
     --private-key-passphrase-env SNOWFLAKE_PASSPHRASE --if-missing
   ```

3. Map a group to the login, or grant the login to a service token, e.g. CI’s. Groups and service tokens are named by their name or id.

   ```sh
   dbtsl config mappings create --profile prod --login analytics_sa \
     --group Analysts --priority 100 --if-missing
   dbtsl config grants create --profile prod --login analytics_sa --service-token ci --if-missing
   ```

4. Publish. This creates the environment on the profile.

   ```sh
   dbtsl publish --target prod --vars '{metricflow_publish_profile: prod}'
   ```

Then `dbtsl config status` reports `Ready.`

## Details

Each command’s options are in its help: `dbtsl config --help`, and e.g. `dbtsl config logins create --help`.

# Overview

MetricFlow is the engine that powers the dbt semantic layer.

MetricFlow is configured using three table-like concepts: **datasets**, **entities**, and **constellations**.

Clients query MetricFlow with simple input SQL, referencing these table-like concepts, and MetricFlow compiles that to complex output SQL against your data warehouse.

Together, these form the building blocks for a composable, modular, and expressive semantic layer.

## Core Concepts

Dataset

Table, layered with virtual columns.

In dbt, datasets are 1-to-1 with models or sources.

Datasets let you centralize complex business logic (aggregations, joins, etc) and query it like any other column.

* **Column** Column of the table.
* **Relationship** Named reference to another table.
* **Calculation** Expression referencing columns, relationships, or other calculations.

[Datasets →](../datasets/)

Entity

Virtual wide table, composed from many datasets.

Entities allow a single business concept to be composed from many tables, owned and maintained by many teams.

* **Source** Dataset the entity is composed of and how to join it.
* **Attribute** Expression referencing one or many source columns.

[Entities →](../entities/)

Constellation

Virtual union of datasets, merged on a set of common roles, and queried as a single table.

Constellations allow metrics to span many tables and let you query them as a single table.

* **Metric** Aggregate expression spanning one or many datasets.
* **Role** What metrics can be grouped and filtered by.
* **Assignment** How a dataset implements each role.

[Constellations →](../constellations/)

# Datasets

Datasets are tables, layered with virtual columns.

Datasets let you centralize complex business logic (aggregations, joins, etc) and query it like any other column.

Here’s how the above might be configured in YAML:

```yaml
datasets:
  - name: orders
    source:
      location: shop.orders
      columns:
        - name: customer_id
        - name: gross
        - name: discount
        - name: order_id
        - name: sold_at
    calculations:
      - name: net 
        expr: gross - discount
      - name: revenue 
        expr: SUM(net)
    relationships:
      - name: customer 
        target: profiles
        key: [{ name: id, expr: customer_id }]
    key: [order_id]
  - name: profiles
    source:
      location: shop.profiles
      columns:
        - name: id
        - name: plan
    key: [id]
```

And queried with MetricFlow SQL extensions:

* Input SQL

  ```sql
  SELECT
    customer.plan, 
    AGG(revenue) 
  FROM orders
  WHERE net > 0 
  GROUP BY ALL
  ```

* Compiled SQL

  ```sql
  SELECT
    "customer"."plan" AS "plan",
    SUM((
      "orders"."gross" - "orders"."discount"
    )) AS "revenue"
  FROM "shop"."orders" AS "orders"
  LEFT JOIN "shop"."profiles" AS "customer"
    ON "customer"."id" = "orders"."customer_id"
  WHERE
    (
      "orders"."gross" - "orders"."discount"
    ) > 0
  GROUP BY
    "customer"."plan"
  ```

## Calculations

Calculations are arbitrary SQL expressions. They can include columns, other calculations, or relationships.

Calculations can be aggregate expressions, in which case they must be queried using the `AGG` function.

## Relationships

Relationships are named references to a another dataset or entity. Relationships can be traversed using the dot notation, and the compiler automatically adds necessary joins.

## Key conditions

Let’s look at a more complex relationship example:

```yaml
datasets:
  # Adding a relationship to the previous `orders` dataset
  - name: orders
    relationships:
      - name: plan
        target: plan_history
        key:
          - { name: customer_id, expr: customer_id }
          - { name: as_of, expr: sold_at }
  # SCD Type 2 dataset
  - name: plan_history
    source:
      location: shop.plan_history
      columns:
        - name: customer_id
        - name: name
        - name: valid_from
        - name: valid_to
    key:
      - customer_id 
      - on: :as_of >= valid_from AND (valid_to IS NULL OR :as_of < valid_to)
```

* Input SQL

  ```sql
  SELECT
    order_id,
    plan.name
  FROM orders
  ```

* Compiled SQL

  ```sql
  SELECT
    "orders"."order_id" AS "order_id",
    "plan"."name" AS "name"
  FROM "shop"."orders" AS "orders"
  LEFT JOIN "shop"."plan_history" AS "plan"
    ON "plan"."customer_id" = "orders"."customer_id"
    AND (
      "orders"."sold_at" >= "plan"."valid_from"
      AND (
        "plan"."valid_to" IS NULL OR "orders"."sold_at" < "plan"."valid_to"
      )
    )
  ```

Datasets can define a unique `key`. Keys can be defined as logical conditions, supporting concepts like “slowly changing dimensions (SCD Type 2)”.

## Multi-hop joins

```yaml
datasets:
  # Adding a relationship to the previous `plan_history` dataset
  - name: plan_history
    relationships:
      - name: details 
        target: plans
        key: [{ name: name, expr: name }]
  # Leaf in this chain of relationships
  - name: plans
    source:
      location: shop.plans
      columns:
        - name: name
        - name: price
    key: [name]
```

* Input SQL

  ```sql
  SELECT
    order_id,
    plan.details.price 
  FROM orders
  ```

* Compiled SQL

  ```sql
  SELECT
    "orders"."order_id" AS "order_id",
    "plan_details"."price" AS "price"
  FROM "shop"."orders" AS "orders"
  LEFT JOIN "shop"."plan_history" AS "plan"
    ON "plan"."customer_id" = "orders"."customer_id"
    AND (
      "orders"."sold_at" >= "plan"."valid_from"
      AND (
        "plan"."valid_to" IS NULL OR "orders"."sold_at" < "plan"."valid_to"
      )
    )
  LEFT JOIN "shop"."plans" AS "plan_details"
    ON "plan_details"."name" = "plan"."name"
  ```

Using the same dot notation, relationships can be traversed to arbitrary depth.

## One-to-many relationships

```yaml
datasets:
  - name: profiles
    relationships:
      - name: orders 
        target: orders
        key: [{ name: customer_id, expr: id }]
```

* Input SQL

  ```sql
  SELECT
    plan,
    AGG(orders.revenue) 
  FROM profiles
  GROUP BY ALL
  ```

* Compiled SQL

  ```sql
  SELECT
    "grouped"."plan" AS "plan",
    SUM(CASE WHEN "grouped"."_group_id" = 0 THEN "grouped"."revenue" END) AS "revenue"
  FROM (
    SELECT
      "profiles"."plan" AS "plan",
      GROUPING("orders"."order_id") AS "_group_id",
      MAX(
        CASE
          WHEN NOT "orders"."order_id" IS NULL
          THEN (
            "orders"."gross" - "orders"."discount"
          )
        END
      ) AS "revenue"
    FROM "shop"."profiles" AS "profiles"
    LEFT JOIN "shop"."orders" AS "orders"
      ON "orders"."customer_id" = "profiles"."id"
    GROUP BY
      GROUPING SETS (
        ("profiles"."plan", "orders"."order_id")
      )
  ) AS "grouped"
  GROUP BY
    "grouped"."plan"
  ```

When a relationship doesn’t reference another dataset’s `key`, it is a one-to-many relationship. A query can `AGG` calculations from a one-to-many relationship, and the compiler applies fan-out protection to avoid double-counting.

## Higher-order calculations

Window functions on calculations - with a couple extensions - allow for higher-order aggregations.

```yaml
datasets:
  - name: orders
    calculations:
      - name: revenue_past_7d 
        expr: >-
          SUM(AGG(revenue)) OVER (
            PARTITION BY EXCLUDING sold_at
            ORDER BY sold_at
            RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
          )
      - name: revenue_share_of_total 
        expr: AGG(revenue) / SUM(AGG(revenue)) OVER ()
      - name: avg_revenue_per_customer 
        expr: AVG(AGG(revenue)) OVER (PARTITION BY INCLUDING customer)
```

* Input SQL

  ```sql
  SELECT
    sold_at,
    customer.channel,
    AGG(revenue_past_7d), 
    AGG(revenue_share_of_total) 
  FROM orders
  GROUP BY ALL
  ```

* Compiled SQL

  ```sql
  SELECT
    "orders"."sold_at" AS "sold_at",
    "customer"."channel" AS "channel",
    SUM(SUM((
      "orders"."gross" - "orders"."discount"
    ))) OVER (
      PARTITION BY "customer"."channel"
      ORDER BY "orders"."sold_at"
      RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW
    ) AS "revenue_past_7d",
    SUM((
      "orders"."gross" - "orders"."discount"
    )) / SUM(SUM((
      "orders"."gross" - "orders"."discount"
    ))) OVER () AS "revenue_share_of_total"
  FROM "shop"."orders" AS "orders"
  LEFT JOIN "marketing"."acquisition" AS "customer"
    ON "customer"."customer_id" = "orders"."customer_id"
  GROUP BY
    "orders"."sold_at",
    "customer"."channel"
  ```

`PARTITION BY EXCLUDING` partitions by everything the query groups by, except the columns it lists.

A window with no `PARTITION BY` spans every row.

`PARTITION BY INCLUDING` first aggregates at a finer grain than the query, adding the columns it lists to the group by.

# Entities

Entities are virtual wide datasets.

They allow a single business concept to be composed from many tables, owned and maintained by many teams.

```yaml
entities:
  - name: customer
    key:
      - name: id
        datatype: INT
    sources: 
      - dataset: profiles
        on: id = :id
      - dataset: acquisition
        on: customer_id = :id
      - dataset: delivery
        on: customer_id = :id
    attributes: 
      - name: plan
        expr: profiles.plan
      - name: channel
        expr: acquisition.channel
      - name: zone
        expr: delivery.zone
datasets:
  - name: orders
    relationships:
      - name: customer
        target: customer 
        key: [{ name: id, expr: customer_id }]
```

* Input SQL

  ```sql
  SELECT
    customer.plan, 
    customer.channel,
    customer.zone,
    AGG(revenue)
  FROM orders
  GROUP BY ALL
  ```

* Compiled SQL

  ```sql
  SELECT
    "customer"."plan" AS "plan",
    "customer_2"."channel" AS "channel",
    "customer_3"."zone" AS "zone",
    SUM((
      "orders"."gross" - "orders"."discount"
    )) AS "revenue"
  FROM "shop"."orders" AS "orders"
  LEFT JOIN "shop"."profiles" AS "customer"
    ON "customer"."id" = "orders"."customer_id"
  LEFT JOIN "marketing"."acquisition" AS "customer_2"
    ON "customer_2"."customer_id" = "orders"."customer_id"
  LEFT JOIN "operations"."delivery" AS "customer_3"
    ON "customer_3"."customer_id" = "orders"."customer_id"
  GROUP BY
    "customer"."plan",
    "customer_2"."channel",
    "customer_3"."zone"
  ```

## Sources

Sources are the datasets an entity is composed of. Each says how it joins to the entity’s key with `on`, where `:placeholders` are the key’s values.

Unlike a SQL view, a query only joins the sources it uses, and an entity can be [defined across many files](../composability/#modularity).

## Attributes

Attributes are an entity’s columns. Each is an expression over zero to many sources.

Relationships can target an entity instead of a dataset, giving the dataset access to all an entity’s sources and attributes.

# Constellations

Constellations are a virtual union of datasets, merged on a set of common “roles”.

They allow metrics to span many tables and let you query them as a single table.

```yaml
constellations:
  - name: finance
    metrics: 
      - name: revenue
        expr: AGG(orders.revenue)
      - name: cost
        expr: AGG(shipments.cost)
      - name: margin
        expr: AGG(revenue) - AGG(cost) 
    roles: 
      - name: date
      - name: customer
        datatype: REF(customer)
    assignments: 
      - dataset: orders
        roles:
          - name: date
            expr: sold_at
          - name: customer
            expr: customer
      - dataset: shipments
        roles:
          - name: date
            expr: shipped_at
          - name: customer
            expr: shipped_to
datasets:
  # Adding another dataset that our `finance` constellation spans
  - name: shipments
    source:
      location: operations.shipments
      columns:
        - name: shipped_at
        - name: shipping
        - name: customer_id
        - name: shipment_id
    key: [shipment_id]
    calculations:
      - name: cost
        expr: SUM(shipping)
    relationships:
      - name: shipped_to
        target: customer
        key: [{ name: id, expr: customer_id }]
```

* Input SQL

  ```sql
  SELECT
    date, 
    customer.channel,
    AGG(margin) 
  FROM finance
  GROUP BY ALL
  ```

* Compiled SQL

  ```sql
  SELECT
    "finance"."date" AS "date",
    "finance"."customer_channel" AS "channel",
    COALESCE("finance"."revenue", 0) - COALESCE("finance"."cost", 0) AS "margin"
  FROM (
    SELECT
      COALESCE("orders"."date", "shipments"."date") AS "date",
      COALESCE("orders"."customer_channel", "shipments"."customer_channel") AS "customer_channel",
      MAX("orders"."revenue") AS "revenue",
      MAX("shipments"."cost") AS "cost"
    FROM (
      SELECT
        "orders"."sold_at" AS "date",
        "customer"."channel" AS "customer_channel",
        SUM((
          "orders"."gross" - "orders"."discount"
        )) AS "revenue"
      FROM "shop"."orders" AS "orders"
      LEFT JOIN "marketing"."acquisition" AS "customer"
        ON "customer"."customer_id" = "orders"."customer_id"
      GROUP BY
        "orders"."sold_at",
        "customer"."channel"
    ) AS "orders"
    FULL OUTER JOIN (
      SELECT
        "shipments"."shipped_at" AS "date",
        "shipped_to"."channel" AS "customer_channel",
        SUM("shipments"."shipping") AS "cost"
      FROM "operations"."shipments" AS "shipments"
      LEFT JOIN "marketing"."acquisition" AS "shipped_to"
        ON "shipped_to"."customer_id" = "shipments"."customer_id"
      GROUP BY
        "shipments"."shipped_at",
        "shipped_to"."channel"
    ) AS "shipments"
      ON "shipments"."date" = "orders"."date"
      AND "shipments"."customer_channel" = "orders"."customer_channel"
    GROUP BY
      COALESCE("orders"."date", "shipments"."date"),
      COALESCE("orders"."customer_channel", "shipments"."customer_channel")
  ) AS "finance"
  ```

## Metrics

Metrics are aggregate SQL expressions that span one to many datasets.

Metrics can reference other metrics.

## Roles

Roles are what you can group and filter metrics by. Roles are abstract - they can be a primitive datatype (e.g. `TIMESTAMP`) or a relationship (e.g. `REF(user)`).

When aggregating metrics, aggregation is first done per dataset, then joined on any roles being grouped by.

## Assignments

All datasets from metrics assign arbitrary SQL expressions to roles, which define how each dataset implements each role.

## Imports

A constellation can import another’s fields:

```yaml
constellations:
  - name: sales
    imports:
      - constellation: finance
        fields:
          # import all fields
          - '*'
          # except these:
          - '!cost'
          - '!margin'
```

* Input SQL

  ```sql
  SELECT
    DATE_TRUNC('quarter', date) AS quarter,
    AGG(revenue)
  FROM sales
  GROUP BY ALL
  ```

* Compiled SQL

  ```sql
  SELECT
    "sales"."quarter" AS "quarter",
    "sales"."revenue" AS "revenue"
  FROM (
    SELECT
      "orders"."quarter",
      "orders"."revenue"
    FROM (
      SELECT
        DATE_TRUNC('QUARTER', "orders"."sold_at") AS "quarter",
        SUM((
          "orders"."gross" - "orders"."discount"
        )) AS "revenue"
      FROM "shop"."orders" AS "orders"
      GROUP BY
        DATE_TRUNC('QUARTER', "orders"."sold_at")
    ) AS "orders"
  ) AS "sales"
  ```

# Composability

Datasets, entities, and constellations are building blocks that all work together.

You can combine them to achieve many classical analytical modeling problems - fact constellations, snowflake schemas, dimension conforming, data vault hub and spokes, etc. - all queryable as simple SQL.

## Modularity

Every object can be split across many files, owned by many teams.

Files that define the same object are merged.

orders.yamlorders_extra.yamlcustomer.yamlcustomer_extra.yamlfinance.yamlfinance_extra.yaml

```yaml
datasets:
  - name: orders
    source:
      location: shop.orders
      columns:
        - name: customer_id
        - name: gross
        - name: sold_at
```

```yaml
datasets:
  - name: orders
    calculations:
      - name: revenue
        expr: SUM(gross)
    relationships:
      - name: customer
        target: customer
        key: [{ name: id, expr: customer_id }]
```

```yaml
entities:
  - name: customer
    key:
      - name: id
```

```yaml
datasets:
  - name: acquisition
    source:
      location: marketing.acquisition
      columns:
        - name: customer_id
        - name: channel
entities:
  - name: customer
    sources:
      - dataset: acquisition
        on: customer_id = :id
    attributes:
      - name: channel
        expr: acquisition.channel
```

```yaml
constellations:
  - name: finance
    roles:
      - name: date
      - name: customer
        datatype: REF(customer)
```

```yaml
constellations:
  - name: finance
    metrics:
      - name: revenue
        expr: AGG(orders.revenue)
    assignments:
      - dataset: orders
        roles:
          - name: date
            expr: sold_at
          - name: customer
            expr: customer
```

# Inside dbt

## Top-level objects

Datasets, entities, and constellations can be added as top-level keys to any dbt schema file:

```yaml
version: 2


sources: [...]
models: [...]
datasets: [...]
entities: [...]
constellations: [...]
```

Files that define the same object are merged.

## Inline datasets

Within dbt, models and sources become datasets if they include any calculations or relationships:

```yaml
models:
  - name: orders
    columns:
      - name: gross
      - name: discount
    calculations:
      - name: revenue
        expr: SUM(gross - discount)
```

Or, to turn any model into a dataset with only columns:

```yaml
models:
  - name: orders
    columns:
      - name: gross
      - name: discount
    dataset: true
```

# MetricFlow v2

New? Start with [Overview](overview/).

# Extensions

MetricFlow SQL is standard SQL, with a few extensions.

## `AGG`

`AGG(name)` aggregates an aggregate calculation of a dataset, or a metric of a constellation, at the query’s grain: its `GROUP BY`, or every row without one.

```sql
SELECT
  customer.plan,
  AGG(revenue)
FROM orders
GROUP BY ALL
```

Aggregate calculations and metrics can only be queried with `AGG`.

## References

A relationship, or a role, has a reference type, `REF(name)`: its value is a row of the dataset or entity `name`.

```yaml
roles:
  - name: customer
    datatype: REF(customer)
```

## Dot notation

`reference.field` reads a field of the row a reference points to, and the compiler adds the join. References chain, to any depth:

```sql
SELECT
  order_id,
  plan.details.price
FROM orders
```

Through a one-to-many relationship, a field can only be aggregated, with `AGG`:

```sql
SELECT
  plan,
  AGG(orders.revenue)
FROM profiles
GROUP BY ALL
```

# Schema

> Every field of a fragment file, and of the types it holds.

## Fragment

A piece of a bundle’s semantic graph: the fragments of a bundle are merged into it.

Datasets, entities and constellations are named like a warehouse’s tables: `sales.orders` is `orders` in the `sales` namespace, and a name without a namespace is in `public`. A reference without a namespace, e.g. a relationship’s `entity: customer`, is in the namespace of the object that declares it. A dataset and a constellation can’t share a name.

| Field             | Type                                    | Default      | Description                                                                                                                                                                                                                                                                           |
| ----------------- | --------------------------------------- | ------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `bundle`          | string                                  | **required** | The name of the bundle this fragment is part of, e.g. a dbt project’s name. Every fragment in a bundle has the same one: it identifies the bundle when it’s published. It’s a segment of the bundle’s URL, so it can’t contain a `/`. Has at least 1 character(s). Matches `^[^/]+$`. |
| `dialect`         | string                                  | **required** | The default SQL dialect used to parse expressions and datatypes.                                                                                                                                                                                                                      |
| `schema_version`  | `"v1"`                                  | **required** |                                                                                                                                                                                                                                                                                       |
| `constellations`  | list of [Constellation](#constellation) | `[]`         | Constellations.                                                                                                                                                                                                                                                                       |
| `datasets`        | list of [Dataset](#dataset)             | `[]`         | Datasets.                                                                                                                                                                                                                                                                             |
| `entities`        | list of [Entity](#entity)               | `[]`         | Entities.                                                                                                                                                                                                                                                                             |
| `generation_meta` | [FragmentMetadata](#fragmentmetadata)   | optional     | Header describing the production of this fragment.                                                                                                                                                                                                                                    |
| `pages`           | list of [Page](#page)                   | `[]`         | Wiki pages.                                                                                                                                                                                                                                                                           |

### FragmentMetadata

Header describing the production of this fragment.

| Field               | Type           | Default | Description                                                 |
| ------------------- | -------------- | ------- | ----------------------------------------------------------- |
| `env`               | string or null | `null`  | Environment                                                 |
| `generated_at`      | string or null | `null`  | When the fragment was generated.                            |
| `generator_name`    | string or null | `null`  | The name of the tool that generated this fragment.          |
| `generator_version` | string or null | `null`  | The version of the tool that generated this fragment.       |
| `invocation_id`     | string or null | `null`  | The invocation that generated this fragment, if applicable. |

## Constellation

A group of fact tables connected by conformed dimensions.

Constellations capture higher-order business domains and enable multi-fact queries. Constellations can include configuration from other constellations.

| Field         | Type                                            | Default      | Description                                 |
| ------------- | ----------------------------------------------- | ------------ | ------------------------------------------- |
| `name`        | string                                          | **required** | The name, unique among its collection’s.    |
| `assignments` | list of [DatasetAssignment](#datasetassignment) | `[]`         | How datasets provide roles, by dataset.     |
| `description` | string or null                                  | `null`       |                                             |
| `imports`     | list of [Import](#import)                       | `[]`         | Other constellations to import fields from. |
| `label`       | string or null                                  | `null`       |                                             |
| `metrics`     | list of [Metric](#metric)                       | `[]`         | Metrics of the constellation.               |
| `roles`       | list of [Role](#role)                           | `[]`         | Roles of the constellation.                 |

### DatasetAssignment

The roles a dataset provides.

| Field     | Type                              | Default      | Description                                                |
| --------- | --------------------------------- | ------------ | ---------------------------------------------------------- |
| `dataset` | string                            | **required** | The name of the dataset, unique among the constellation’s. |
| `roles`   | list of [Assignment](#assignment) | `[]`         | How the dataset provides each role.                        |

### Assignment

Defines how a dataset provides a role.

This is akin to “dimension conforming” in traditional dimensional modeling, allowing cross-table analysis - queries reference roles, and the physical location of roles are resolved per dataset.

| Field         | Type           | Default      | Description                                                      |
| ------------- | -------------- | ------------ | ---------------------------------------------------------------- |
| `expr`        | string         | **required** | A SQL expression, where columns resolve relative to the dataset. |
| `name`        | string         | **required** | The name of the role, unique among the dataset’s assignments.    |
| `description` | string or null | `null`       |                                                                  |
| `label`       | string or null | `null`       |                                                                  |

### Import

Specification for how one constellation imports another.

| Field           | Type                     | Default      | Description                                                                                                                                  |
| --------------- | ------------------------ | ------------ | -------------------------------------------------------------------------------------------------------------------------------------------- |
| `constellation` | string                   | **required** | The name of the constellation to import from.                                                                                                |
| `fields`        | string or list of string | **required** | Which fields to import: `*`, a field name, `!name` to exclude a field, or a list of these, e.g. `['*', '!refunds']`. Has at least 1 item(s). |

### Metric

An aggregatable measurement.

Metrics are typically quantitative, but they can be any aggregatable value. Metrics can span multiple datasets.

| Field         | Type           | Default      | Description                                                                                                                        |
| ------------- | -------------- | ------------ | ---------------------------------------------------------------------------------------------------------------------------------- |
| `expr`        | string         | **required** | A SQL expression, where qualified columns reference fields from datasets and unqualified columns reference other metrics or roles. |
| `name`        | string         | **required** | The name, unique among its collection’s.                                                                                           |
| `datatype`    | string or null | `null`       | Optionally define the datatype. If left null, this will be inferred.                                                               |
| `description` | string or null | `null`       |                                                                                                                                    |
| `label`       | string or null | `null`       |                                                                                                                                    |

### Role

A qualitative property that describes metrics - metrics can be sliced and diced by roles.

Each dataset contributing to a constellation can assign fields to a role.

| Field         | Type           | Default      | Description                                                                                                                                         |
| ------------- | -------------- | ------------ | --------------------------------------------------------------------------------------------------------------------------------------------------- |
| `name`        | string         | **required** | The name, unique among its collection’s.                                                                                                            |
| `datatype`    | string or null | `null`       | A physical data type, or `REF(<name>)` for a reference to an entity or dataset, in which case datasets must implement the role with a relationship. |
| `description` | string or null | `null`       |                                                                                                                                                     |
| `label`       | string or null | `null`       |                                                                                                                                                     |

## Dataset

Physical dataset - serves as the input to the semantic layer graph

| Field           | Type                                              | Default      | Description                                                                                                                                                                                                                   |
| --------------- | ------------------------------------------------- | ------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `name`          | string                                            | **required** | The name, unique among its collection’s.                                                                                                                                                                                      |
| `calculations`  | list of [Calculation](#calculation)               | `[]`         | Derived columns.                                                                                                                                                                                                              |
| `description`   | string or null                                    | `null`       |                                                                                                                                                                                                                               |
| `key`           | list of (string or [KeyCondition](#keycondition)) | `[]`         | What identifies a row: names of fields, whose values identify rows, and conditions, e.g. `[user_id, {on: ':as_of >= valid_from AND :as_of < valid_to'}]`. Relationships binding all of it are many-to-one. Requires `source`. |
| `label`         | string or null                                    | `null`       |                                                                                                                                                                                                                               |
| `parameters`    | list of [Parameter](#parameter)                   | `[]`         | Values a query passes, read by calculations as `:name` and by a `sql` source’s template.                                                                                                                                      |
| `relationships` | list of [Relationship](#relationship)             | `[]`         | References to entities and datasets.                                                                                                                                                                                          |
| `source`        | [DatasetSource](#datasetsource) or null           | `null`       | Source of this dataset (a physical table or a query). Omit it to add calculations and relationships to a dataset whose source another fragment of the bundle defines.                                                         |

### Calculation

A derived column on the dataset.

`expr` is a SQL expression, where columns reference other columns, calculations, or relationships.

| Field         | Type           | Default      | Description                                                          |
| ------------- | -------------- | ------------ | -------------------------------------------------------------------- |
| `expr`        | string         | **required** | A SQL expression, relative to the dataset.                           |
| `name`        | string         | **required** | The name, unique among its collection’s.                             |
| `datatype`    | string or null | `null`       | Optionally define the datatype. If left null, this will be inferred. |
| `description` | string or null | `null`       |                                                                      |
| `label`       | string or null | `null`       |                                                                      |

### KeyCondition

A key part that identifies rows by a condition rather than a field’s value.

For example, `:as_of >= valid_from AND :as_of < valid_to` for versions valid over a period.

| Field    | Type                                          | Default      | Description                                                                                                                                                                             |
| -------- | --------------------------------------------- | ------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `on`     | string                                        | **required** | A SQL condition, where columns are relative to the dataset and values a relationship binds are `:`-prefixed placeholders, which can’t be named like the dataset’s fields or parameters. |
| `params` | list of [KeyParameter](#keyparameter) or null | `null`       | The condition’s placeholders. If left null, they are inferred from `on`, and they must be exactly its placeholders otherwise.                                                           |

### KeyParameter

A placeholder of a key condition, e.g. `:as_of`.

| Field      | Type           | Default      | Description                              |
| ---------- | -------------- | ------------ | ---------------------------------------- |
| `name`     | string         | **required** | The name, unique among its collection’s. |
| `datatype` | string or null | `null`       | The parameter’s data type.               |

### Parameter

A value a query passes on its root table function, e.g. `ds(min_quantity => 90)`.

| Field      | Type           | Default      | Description                                                    |
| ---------- | -------------- | ------------ | -------------------------------------------------------------- |
| `name`     | string         | **required** | The name, unique among its collection’s.                       |
| `datatype` | string or null | `null`       | The parameter’s data type.                                     |
| `default`  | string or null | `null`       | A constant used when a query passes nothing; NULL when absent. |

### Relationship

A relationship to an entity or a dataset, its target.

A key binding all of the target’s key is many-to-one, and any other is one-to-many.

| Field         | Type                                                    | Default      | Description                                                                                                                |
| ------------- | ------------------------------------------------------- | ------------ | -------------------------------------------------------------------------------------------------------------------------- |
| `name`        | string                                                  | **required** | The name, unique among its collection’s.                                                                                   |
| `target`      | string                                                  | **required** | The name of the related entity or dataset. An entity is looked up first, then a dataset.                                   |
| `cardinality` | `"many_to_one"` or `"one_to_many"` or null              | `null`       | Optionally declare the cardinality. If left null, this will be inferred from the key, and it must agree with it otherwise. |
| `description` | string or null                                          | `null`       |                                                                                                                            |
| `key`         | list of [RelationshipKeyColumn](#relationshipkeycolumn) | `[]`         | Expressions on this dataset for the target’s fields.                                                                       |
| `label`       | string or null                                          | `null`       |                                                                                                                            |

### RelationshipKeyColumn

How a relationship binds one of its target’s fields.

| Field  | Type   | Default      | Description                                                                                              |
| ------ | ------ | ------------ | -------------------------------------------------------------------------------------------------------- |
| `expr` | string | **required** | A SQL expression, relative to the dataset.                                                               |
| `name` | string | **required** | A key column of the target entity, or a key part or field of the target dataset, unique among the key’s. |

### DatasetSource

A physical table, or a query.

| Field         | Type                      | Default   | Description                                                                                                   |
| ------------- | ------------------------- | --------- | ------------------------------------------------------------------------------------------------------------- |
| `columns`     | list of [Column](#column) | `[]`      | Columns of the table or query’s rows.                                                                         |
| `description` | string or null            | `null`    |                                                                                                               |
| `label`       | string or null            | `null`    |                                                                                                               |
| `location`    | string or null            | `null`    | The fully-qualified physical table this dataset reads from.                                                   |
| `sql`         | string or null            | `null`    | A query whose rows this dataset reads, e.g. a filtered table. A Jinja template over the dataset’s parameters. |
| `type`        | `"sql"` or `"table"`      | `"table"` | `table` reads `location`; `sql` reads the rows `sql` returns.                                                 |

### Column

A concrete column in the underlying database.

| Field         | Type           | Default      | Description                              |
| ------------- | -------------- | ------------ | ---------------------------------------- |
| `name`        | string         | **required** | The name, unique among its collection’s. |
| `datatype`    | string or null | `null`       | The column’s data type.                  |
| `description` | string or null | `null`       |                                          |
| `label`       | string or null | `null`       |                                          |

## Entity

An identifiable, referencable business object.

Entities are like virtual datasets, composed from many datasets. Attributes act like an entity’s columns.

| Field         | Type                                        | Default      | Description                                                                    |
| ------------- | ------------------------------------------- | ------------ | ------------------------------------------------------------------------------ |
| `name`        | string                                      | **required** | The name, unique among its collection’s.                                       |
| `attributes`  | list of [Attribute](#attribute)             | `[]`         | Properties of the entity.                                                      |
| `description` | string or null                              | `null`       |                                                                                |
| `key`         | list of [EntityKeyColumn](#entitykeycolumn) | `[]`         | The interface that sources must implement and dataset relationships reference. |
| `label`       | string or null                              | `null`       |                                                                                |
| `sources`     | list of [Source](#source)                   | `[]`         | Datasets implementing the entity’s key.                                        |

### Attribute

A property of an entity.

| Field         | Type           | Default      | Description                                                                                                                |
| ------------- | -------------- | ------------ | -------------------------------------------------------------------------------------------------------------------------- |
| `expr`        | string         | **required** | SQL expression, where qualified columns reference fields from a source and unqualified columns reference other attributes. |
| `name`        | string         | **required** | The name, unique among its collection’s.                                                                                   |
| `datatype`    | string or null | `null`       | Optionally define the datatype. If left null, this will be inferred.                                                       |
| `description` | string or null | `null`       |                                                                                                                            |
| `label`       | string or null | `null`       |                                                                                                                            |

### EntityKeyColumn

Specification for an individual entity key column.

| Field      | Type           | Default      | Description                                                         |
| ---------- | -------------- | ------------ | ------------------------------------------------------------------- |
| `name`     | string         | **required** | The name, unique among its collection’s.                            |
| `datatype` | string or null | `null`       | The key column’s data type.                                         |
| `optional` | boolean        | `false`      | Whether dataset relationships may omit referencing this key column. |

### Source

A dataset sourcing the entity.

Entities have attributes that are sourced from many datasets - a source defines such a dataset.

| Field     | Type   | Default      | Description                                                                                                                                                                                                                                                    |
| --------- | ------ | ------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `dataset` | string | **required** | The name of the dataset, unique among the entity’s sources.                                                                                                                                                                                                    |
| `on`      | string | **required** | A SQL expression that defines how the dataset implements the entity’s key. Used as the ON clause of JOIN expressions: columns are relative to the dataset, and entity key values from the other side of the join are referencable with `:`-prefixed variables. |

## Page

A markdown wiki page.

Links to other objects are markdown links with a reference as the URL, e.g. `[revenue](metric:sales:revenue)` or `[onboarding](page:guides/onboarding)`.

| Field         | Type           | Default      | Description                                                                                                                     |
| ------------- | -------------- | ------------ | ------------------------------------------------------------------------------------------------------------------------------- |
| `name`        | string         | **required** | The name, unique among its collection’s.                                                                                        |
| `content`     | string or null | `null`       | The page’s markdown, inline. Exclusive with `path`.                                                                             |
| `description` | string or null | `null`       |                                                                                                                                 |
| `label`       | string or null | `null`       |                                                                                                                                 |
| `path`        | string or null | `null`       | Path to a markdown file holding the page’s content, relative to this fragment file within its bundle. Exclusive with `content`. |

## Fields of every type

Besides the fields in its table, every type in this reference accepts:

* `meta`: object, defaulting to `{}`.