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

# MetricFlow v2

MetricFlow is a semantic layer, enabling simplified and trustworthy access to your data.

You model your data once, mapping higher-order concepts (metrics, entities, relationships, etc.) to raw data (tables, columns).
Then you integrate it everywhere (AI agents, BI tools, etc.), ensuring everyone uses the data correctly.

## Modular

MetricFlow's definitions can be split up and shared, enabling both small and massive teams to collaborate
without centralized bottlenecks.

## Expressive

MetricFlow's data model has powerful building blocks for representing complex data relationships and metric logic,
greatly reducing the data preparation and pipelines required to make your data broadly accessible.

```sql
SELECT
  customer.current_address.state,
  AVG(SUM(amount) PER (customer))
FROM store_sales
GROUP BY 1
```

## Vendor Neutral

MetricFlow works with any data warehouse, so you're not locked into a closed platform.

## Open Source

MetricFlow's core engine is free and permissively licensed.

## dbt Native

MetricFlow is the engine that powers the dbt Semantic Layer and has deep integrations with the larger dbt platform.

---

Want to learn more about how it works? See [Data Modeling](/metricflow/architecture).

Ready to jump in? See [Quickstart](/metricflow/quickstart).

# 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](/metricflow/dbt-platform-setup)

4. **Model your data**

    Define the semantic layer directly in your dbt project.

    See [Data Modeling](/metricflow/architecture) and [Inside dbt](/metricflow/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](/metricflow/architecture) 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`.

# Architecture

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 →](/metricflow/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 →](/metricflow/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 →](/metricflow/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("grouped"."revenue") AS "revenue"
FROM (
  SELECT
    "profiles"."plan" AS "plan",
    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
    "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

Calculations can use window functions and `PER`, for metrics like running totals, shares of the total, and averages per customer.
See [Complex metrics](/metricflow/complex-metrics).

# 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](/metricflow/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"
```

# Functions

Functions let you write a SQL expression once and reuse it in datasets, entities and constellations.

```yaml
functions:
  - name: order_size
    expr: CASE WHEN :amount >= 1000 THEN 'large' WHEN :amount >= 100 THEN 'medium' ELSE 'small' END

datasets:
  - name: raw_orders
    calculations:
      - name: size
        expr: order_size(amount => gross)
  - name: refunds
    calculations:
      - name: size
        expr: order_size(amount => refunded)
```

`:amount` is the parameter, and `refunded` is the value passed to it.

# Complex metrics

Many metrics aren't a single aggregate over rows: a running total, a share of the total, a change from the previous period, or an average of each customer's total.
In plain SQL, each takes a window function or a nested subquery, rewritten for every grain a report needs.

MetricFlow SQL has two building blocks for them: window functions over aggregates, and `PER`.
Both can be defined once, as calculations or metrics, and adapt to whatever a query groups by.

## Window functions

A window function over `AGG` runs over the query's rows, after they're grouped.

```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 ()
```

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.
This query groups by `sold_at` and `channel`, so the running total is partitioned by `channel`.
Grouped by `sold_at` alone, the same calculation would span every channel.

A window with no `PARTITION BY` spans every row, so this is each row's share of the query's total.

## PER

`f(aggregate PER (columns))` aggregates in two steps: first `aggregate`, at the query's grain plus `columns`, then `f` over those values, back to the query's grain.

```yaml
datasets:
  - name: orders
    calculations:
      - name: avg_revenue_per_customer
        expr: AVG(AGG(revenue) PER (customer))
```

Input SQL:

```sql
SELECT
  DATE_TRUNC('month', sold_at) AS month,
  AGG(avg_revenue_per_customer),
  MAX(AGG(revenue) PER (customer)) AS top_customer_revenue
FROM orders
GROUP BY ALL
```

Compiled SQL:

```sql
SELECT
  "grouped"."month" AS "month",
  AVG("grouped"."avg_revenue_per_customer") AS "avg_revenue_per_customer",
  MAX("grouped"."avg_revenue_per_customer") AS "top_customer_revenue"
FROM (
  SELECT
    DATE_TRUNC('MONTH', "orders"."sold_at") AS "month",
    SUM((
      "orders"."gross" - "orders"."discount"
    )) AS "avg_revenue_per_customer"
  FROM "shop"."orders" AS "orders"
  GROUP BY
    DATE_TRUNC('MONTH', "orders"."sold_at"),
    "orders"."customer_id"
) AS "grouped"
GROUP BY
  "grouped"."month"
```

Revenue is summed per customer, then averaged: each month's average revenue per customer, rather than per order.

`PER` can also be written in a query, and any aggregate can combine the values, e.g. `MAX`, for the top customer's revenue.

A `PER` can list columns, expressions, or relationships, which stand for their key, e.g. `customer` above is `customer_id`.

## Windows and PER together

Windows and `PER` nest, in either order.

```yaml
datasets:
  - name: orders
    calculations:
      - name: avg_revenue_per_customer_change
        expr: >-
          AGG(avg_revenue_per_customer) - LAG(AGG(avg_revenue_per_customer)) OVER (
            PARTITION BY EXCLUDING sold_at
            ORDER BY sold_at
          )
```

Input SQL:

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

Compiled SQL:

```sql
SELECT
  "grouped"."sold_at" AS "sold_at",
  "grouped"."channel" AS "channel",
  AVG("grouped"."avg_revenue_per_customer") AS "avg_revenue_per_customer",
  AVG("grouped"."avg_revenue_per_customer") - LAG(AVG("grouped"."avg_revenue_per_customer")) OVER (PARTITION BY "grouped"."channel" ORDER BY "grouped"."sold_at") AS "avg_revenue_per_customer_change"
FROM (
  SELECT
    "orders"."sold_at" AS "sold_at",
    "customer"."channel" AS "channel",
    SUM((
      "orders"."gross" - "orders"."discount"
    )) AS "avg_revenue_per_customer"
  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",
    "orders"."customer_id"
) AS "grouped"
GROUP BY
  "grouped"."sold_at",
  "grouped"."channel"
```

A window over a `PER`: the day-over-day change in average revenue per customer, by channel.
A window can also go inside a `PER`, where it runs at the `PER`'s grain, e.g. to average each customer's own change.

## On constellations

A constellation's metrics can use `PER`, by its roles.

```yaml
constellations:
  - name: finance
    metrics:
      - name: avg_margin_per_customer
        expr: AVG(AGG(margin) PER (customer))
```

Input SQL:

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

Compiled SQL:

```sql
SELECT
  "grouped"."channel" AS "channel",
  AVG("grouped"."avg_margin_per_customer") AS "avg_margin_per_customer"
FROM (
  SELECT
    COALESCE("orders"."customer_channel", "shipments"."customer_channel") AS "channel",
    COALESCE(MAX("orders"."revenue"), 0) - COALESCE(MAX("shipments"."cost"), 0) AS "avg_margin_per_customer"
  FROM (
    SELECT
      "customer"."channel" AS "customer_channel",
      "orders"."customer_id" AS "customer",
      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
      "customer"."channel",
      "orders"."customer_id"
  ) AS "orders"
  FULL OUTER JOIN (
    SELECT
      "shipped_to"."channel" AS "customer_channel",
      "shipments"."customer_id" AS "customer",
      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
      "shipped_to"."channel",
      "shipments"."customer_id"
  ) AS "shipments"
    ON "shipments"."customer_channel" = "orders"."customer_channel"
    AND "shipments"."customer" = "orders"."customer"
  GROUP BY
    COALESCE("orders"."customer_channel", "shipments"."customer_channel"),
    COALESCE("orders"."customer", "shipments"."customer")
) AS "grouped"
GROUP BY
  "grouped"."channel"
```

`margin` spans orders and shipments, so each customer's margin is computed from both, joined on their roles, then averaged per channel.
A `PER` over a metric of a single dataset is computed within that dataset.

On a constellation, a window can only go inside a `PER`.
A window over a constellation's metrics, at the query's grain, isn't supported yet.

## In plain SQL

`PER` works on any table, not only datasets.
This is `avg_revenue_per_customer`, from above, written directly over the orders table:

```sql
SELECT
  DATE_TRUNC('month', sold_at) AS month,
  AVG(SUM(gross - discount) PER (customer_id)) AS avg_revenue_per_customer
FROM shop.orders
GROUP BY ALL
```

# 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.yaml`:

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

`orders_extra.yaml`:

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

`customer.yaml`:

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

`customer_extra.yaml`:

```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
```

`finance.yaml`:

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

`finance_extra.yaml`:

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

# Pages

Pages are markdown documents defined alongside the semantic layer.

They hold any context needed to use the semantic layer effectively that doesn't fit into a single metric, role, etc.

## Writing pages

`wiki/glossary.md`:

```md
---
label: Glossary
description: What our metrics mean.
---

[Margin](metric:finance:margin) is what orders earn after costs.
Every order is in the [orders](dataset:orders) dataset.

New here? Start with [onboarding](guides/onboarding.md).
```

The frontmatter sets the page's `label` and `description`, and any `meta`.

Pages link to other objects with ordinary markdown links, using the object's reference as the URL:

| Reference | Links to |
|---|---|
| `dataset:orders`, `entity:customer`, `constellation:finance` | A dataset, entity, or constellation |
| `metric:finance:margin`, `role:finance:customer` | A constellation's metric or role |
| `column:orders:gross`, `calculation:orders:revenue`, `relationship:orders:customer` | A dataset's field |
| `attribute:customer:plan` | An entity's attribute |
| `page:guides/onboarding` | Another page |

To link to another page, use a relative path to its file.

## Registering pages

The top-level `pages` key points at markdown files:

```yaml
pages:
  - path: wiki
```

Every `.md` file under `wiki` becomes a page, named by its path without `.md`: `wiki/glossary.md` is `glossary`, and `wiki/guides/onboarding.md` is `guides/onboarding`.

`path` is relative to the file that declares it. It can be a directory, a single file, or a glob.

## Reading pages

Pages are chunked, indexed, and made searchable through the API and MCP.

# Inside dbt

## Top-level objects

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

```yaml
version: 2

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

Files that define the same object are merged.

Datasets are built on top of dbt models or sources, and can reference them with `ref(...)` or `source(...)`:

```yaml
datasets:
  - name: orders
    source: ref('orders')
    calculations:
      - name: revenue
        expr: SUM(gross - discount)
```

## Inline datasets

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

```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 SQL models

A model configured with `metricflow=true` is written in MetricFlow SQL:

```sql title="models/revenue_by_plan.sql"
{{ config(materialized='table', metricflow=true) }}

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

These are compiled to warehouse SQL whenever dbt compiles them, e.g. in `dbt compile`, `dbt run`, or `dbt build`, and behave like any other model, with Jinja rendering, upstream dependencies, selectors, etc.

Compiling these models needs the `sidecar` extra (see [Quickstart](/metricflow/quickstart)).

# Access

> Control who can query each dataset, entity, constellation and field with dbt platform groups.

MetricFlow supports access control through
[dbt platform groups](https://docs.getdbt.com/docs/platform/manage-access/about-user-access#groups).

Groups let you control who can query each object, and set mandatory filters and masks on data.

## Permissions

Add `permissions` to a dataset, entity, constellation or field (column, metric, etc.) to choose
which groups can use it.

```yaml
datasets:
  - name: raw_orders
    permissions:
      - groups: [finance_team]
```

Only `finance_team` can use `raw_orders`. Use `groups: ['*']` to open an object to everyone.

Objects inherit the permissions of the objects they read:

```yaml
constellations:
  - name: revenue_metrics
    metrics:
      - name: revenue
        expr: AGG(raw_orders.revenue)
```

Running `SELECT AGG(revenue) FROM revenue_metrics` needs access to `raw_orders`.

Add permissions to the constellation to override them:

```yaml
constellations:
  - name: revenue_metrics
    permissions:
      - groups: [finance_team, sales_team]
```

This replaces all inherited permissions, including [`where`](#rows) and [`mask`](#masking).
`sales_team` can now query revenue, even though it can't query `raw_orders`.

Fields can have permissions too:

```yaml
    metrics:
      - name: discounts
        expr: AGG(raw_orders.discounts)
        permissions:
          - groups: [finance_team]
```

To use a field, a group needs access to both the field and its object, so only `finance_team` can
use `discounts`. `SELECT *` leaves out fields the group can't use.

Entities work the same way:

```yaml
entities:
  - name: customer
    permissions:
      - groups: [finance_team]
```

`SELECT customer.plan, AGG(revenue) FROM revenue_metrics GROUP BY ALL` fails for `sales_team`,
because `plan` belongs to `customer`, which has its own permissions.

Objects a group can't use appear in metadata as inaccessible.

## Rows

Add `where` to a permission to filter the rows a group can see.

```yaml
datasets:
  - name: raw_orders
    permissions:
      - groups: [finance_team]
      - groups: [sales_team]
        where: region = 'NA'
```

`finance_team` can see every order, and `sales_team` can only see orders in NA. For
`sales_team`, totals like `AGG(revenue)` only add up NA orders.

Permissions are checked in order, and the first one that lists any of a user's groups is used, so
someone in both `finance_team` and `sales_team` can see every order.

## Aggregates

Add `having` to a permission to filter query results after aggregating. Like in SQL, `where` filters
rows before aggregating, and `having` filters the aggregated results.

```yaml
datasets:
  - name: raw_orders
    permissions:
      - groups: [sales_team]
        having: BOOL_AND(region = 'NA') OR COUNT(DISTINCT customer_id) FILTER (WHERE region <> 'NA') > 3
```

`sales_team` can see:

- NA orders, and totals made only from NA orders
- totals that include more than 3 customers outside NA

`SELECT * FROM raw_orders` returns only NA orders.

## Masking

Add `mask` to a field's permission to mask its value for a group.

```yaml
datasets:
  - name: raw_orders
    source:
      location: shop.orders
      columns:
        - name: customer_email
          permissions:
            - groups: [finance_team]
            - groups: [sales_team]
              mask: CONCAT(LEFT(customer_email, 1), '***')
```

`finance_team` can see the full email, and `sales_team` can only see its first
letter, like `j***`. The masked value is used everywhere in the query, including in `WHERE` and
`GROUP BY`.

To reuse a mask, define it as a [function](/metricflow/functions) and pass its name:

```yaml
functions:
  - name: email_mask
    expr: CONCAT(LEFT(:email, 1), '***')

datasets:
  - name: raw_orders
    source:
      location: shop.orders
      columns:
        - name: customer_email
          permissions:
            - groups: [finance_team]
            - groups: [sales_team]
              mask: email_mask
```

`mask: email_mask` is short for `mask: email_mask(email => customer_email)`.

## Policies

To reuse permissions, define a policy and reference it with `policy`:

```yaml
policies:
  - name: pii
    permissions:
      - groups: [finance_team]
      - groups: [sales_team]
        mask: '***'

datasets:
  - name: raw_orders
    source:
      location: shop.orders
      columns:
        - name: customer_email
          policy: pii
        - name: customer_phone
          policy: pii
```

`customer_email` and `customer_phone` get the same permissions. An object or field can't have both
`policy` and `permissions`.

## Source tables

Once a dataset reads a table, the table can only be queried through the dataset.

```yaml
datasets:
  - name: raw_orders
    source:
      location: shop.orders
    permissions:
      - groups: [finance_team]
```

`finance_team` has to query `raw_orders` to read `shop.orders`.

Any other dataset that reads `shop.orders` must set explicit permissions, to prevent accidentally
exposing data. Other tables have the same access as the [login](/metricflow/dbt-platform-setup) mapped to
the user's dbt platform group.

In dbt, you can also add `permissions` to a model in `schema.yml`:

```yaml
models:
  - name: raw_orders
    permissions:
      - groups: [finance_team]
```

# SQL

MetricFlow's server speaks the [Trino](https://trino.io/docs/current/develop/client-protocol.html) client protocol, so any Trino client can query it.
See Trino's [client list](https://trino.io/docs/current/client.html).

Queries are MetricFlow SQL, compiled to your warehouse's SQL and run there.
`EXPLAIN <query>` returns the compiled SQL without running it.

## Connecting

| Setting | Value |
|---|---|
| Host | Your account's Semantic Layer host, e.g. `ab123.semantic-layer.us1.dbt.com` |
| Authentication | A dbt platform token, as a bearer token (Trino calls it an access token, or JWT), or as a password, with any user |
| Catalog | The environment to query, e.g. `prod`. Without one, the server's default |

Queries are read as Trino's SQL dialect.
To write them in your warehouse's dialect instead, send its name in the `X-Dbt-Dialect` header, e.g. `bigquery`.

## Examples

With the [Trino CLI](https://trino.io/docs/current/client/cli.html):

```sh
trino --server https://ab123.semantic-layer.us1.dbt.com \
  --access-token "$DBT_CLOUD_TOKEN" \
  --catalog prod \
  --execute "SELECT customer.plan, AGG(revenue) FROM orders GROUP BY ALL"
```

With Python's [`trino`](https://github.com/trinodb/trino-python-client) package:

```python
import os

import trino

connection = trino.dbapi.connect(
    host="ab123.semantic-layer.us1.dbt.com",
    port=443,
    http_scheme="https",
    auth=trino.auth.JWTAuthentication(os.environ["DBT_CLOUD_TOKEN"]),
    catalog="prod",
)
cursor = connection.cursor()
cursor.execute("SELECT customer.plan, AGG(revenue) FROM orders GROUP BY ALL")
print(cursor.fetchall())
```

From a dbt project, `dbtsl query` does the same with the project's settings:

```sh
dbtsl query "SELECT customer.plan, AGG(revenue) FROM orders GROUP BY ALL"
```

# API

Besides [SQL](/metricflow/integrations/sql), MetricFlow's server has a JSON API, on the same host, for reading the semantic layer's objects.

Requests authenticate with a dbt platform token, as `Authorization: Bearer <token>`, and name the environment to read in the `X-Trino-Catalog` header, e.g. `prod`. Without it, they read the account's default environment.

## Endpoints

| Endpoint | Does |
|---|---|
| `POST /v1/statement` | Runs a query, over Trino's protocol (see [SQL](/metricflow/integrations/sql)) |
| `GET /v1/search?q=<terms>&limit=<n>` | Searches objects and pages, for up to 100 results, 10 by default |
| `GET /v1/objects?id=<id>&id=...` | Gets up to 100 objects by id, each `null` if there's none |

Ids are references, e.g. `metric:finance:margin` or `p:glossary` (see [Pages](/metricflow/pages)).

## Examples

```sh
curl "https://ab123.semantic-layer.us1.dbt.com/v1/search?q=margin" \
  -H "Authorization: Bearer $DBT_CLOUD_TOKEN" \
  -H "X-Trino-Catalog: prod"
```

```json
{"results": [{"id": "p:glossary", "section": [], "text": "...", "score": 1.0}]}
```

```sh
curl "https://ab123.semantic-layer.us1.dbt.com/v1/objects?id=metric:finance:margin&id=p:glossary" \
  -H "Authorization: Bearer $DBT_CLOUD_TOKEN" \
  -H "X-Trino-Catalog: prod"
```

```json
{"objects": {"metric:finance:margin": {...}, "p:glossary": {...}}}
```

# MCP

AI agents can use MetricFlow through [dbt's remote MCP server](https://docs.getdbt.com/docs/dbt-ai/about-mcp), which gives them tools to search the semantic layer, read its objects and pages, and run queries.

MetricFlow's tools in dbt's remote MCP server aren't enabled by default. Reach out to us to get them enabled for your account.

# 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, or with a glob `path`, one for each markdown file it matches.

    Links to other objects are markdown links with a reference as the URL, e.g.
    `[revenue](metric:sales:revenue)` or `[onboarding](page:guides/onboarding)`. A relative link
    to another page's markdown file, e.g. `[onboarding](guides/onboarding.md)`, becomes a
    reference to that page.

    A markdown file may start with YAML frontmatter setting the page's `name`, `label`,
    `description` and `meta`, e.g. `---
label: Onboarding
---`.

| Field | Type | Default | Description |
| --- | --- | --- | --- |
| `content` | string or null | `null` | The page's markdown, inline. Exclusive with `path`. |
| `description` | string or null | `null` |  |
| `label` | string or null | `null` |  |
| `name` | string or null | `null` | The name, unique among its collection's. Required with `content`. With a file `path`, it defaults to its frontmatter's, else the file's name without `.md`, e.g. `onboarding` for `wiki/onboarding.md`. |
| `path` | string or null | `null` | Path to a markdown file holding the page's content, relative to this fragment file within its bundle. Or a glob, as dbt's `path:` selector: a page for each `.md` file it matches, or that's under a directory it matches, e.g. `wiki` or `wiki/**/*.md`. Each is named by its path within the glob's directory, its leading names without wildcards, without `.md`, e.g. `guides/onboarding` for `wiki/guides/onboarding.md`, unless its frontmatter names it; then the page sets no `name`, `label` or `description`. Exclusive with `content`. |


## Fields of every type

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

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

# 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
```
