All jobs

Add Semantic Models and Metrics to a dbt Project

Yes. An agent reads your marts, writes MetricFlow semantic models and metrics over them, adds the time spine, validates and queries them locally and opens a PR.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

The agent clones the repository into its own cloud sandbox and reads the project before writing any YAML. In a project like dbt-labs/jaffle_shop_duckdb it reads dbt_project.yml, which requires dbt >=1.11.0, <2.0.0 and materialises models/ as tables with staging/ as views, profiles.yml, which points at a local jaffle_shop.duckdb file, pyproject.toml and requirements.txt, which pin dbt-core 1.12.5, dbt-duckdb 1.11.0 and the metricflow package that dbt-core now depends on, and .sqlfluff with its duckdb dialect. Then it reads the models themselves: models/orders.sql unpivots stg_payments into credit_card_amount, coupon_amount, bank_transfer_amount and gift_card_amount beside a total amount, and models/customers.sql derives first_order, most_recent_order, number_of_orders and customer_lifetime_value per customer. It reads models/schema.yml for the tests and column descriptions, and the three seed CSVs under seeds/ for the grain of the raw data.

From that it writes the semantic layer in two files beside the marts. models/semantic_models/orders.yml declares a semantic model on ref('orders') with order_id as the primary entity and customer_id as a foreign entity, order_date as the time dimension with a default day granularity, status as a categorical dimension, and measures for the order count, the total amount and each of the four payment-method amounts, aggregated with sum. models/semantic_models/customers.yml declares a customers model with customer_id as its primary entity so the two join. Below the semantic models the agent defines metrics: simple metrics order_count and revenue, a ratio metric for the coupon share of revenue built from two of the measures, a cumulative revenue_to_date, and a derived average_order_value that divides the first two. Every metric carries a label and description, and the status values are the five that schema.yml already lists in its accepted_values test.

Cumulative metrics and any query grouped by metric_time need a time spine, which this project does not have, so the agent adds models/metricflow_time_spine.sql, a generate_series of dates in DuckDB SQL, with the time_spine configuration in its YAML naming date_day as the standard granularity column. While reading schema.yml it also notices that the customers model documents a total_order_amount column that the SQL does not produce, since customers.sql names it customer_lifetime_value; a measure cannot point at a column that does not exist, so the agent corrects the YAML to the real column name and calls the mismatch out in the pull request rather than quietly fixing it.

It proves the result in the sandbox. It runs uv sync as the repository's own workflow does, then dbt seed, dbt build and dbt parse, which validates the semantic manifest and fails on an entity or measure that does not resolve. It then runs mf validate-configs and a handful of mf query calls against the DuckDB file, grouping revenue by metric_time__month and by order__status, and revenue_to_date by month, and keeps the result tables. It runs sqlfluff lint on the new SQL and dbt docs generate to confirm the semantic models appear in the catalog, and only then opens the pull request.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "Do the work the Linear issue describes: add MetricFlow semantic models and metrics to this dbt project. Read dbt_project.yml, profiles.yml, pyproject.toml, requirements.txt, .sqlfluff, every model under models/ with its YAML, and the headers of the CSV files under seeds/ first. Write one semantic model per mart under models/semantic_models/, with primary and foreign entities, the order date as the default time dimension, categorical dimensions for low-cardinality columns, and sum and count measures over the amount columns. Define simple, ratio, cumulative and derived metrics over those measures, each with a label and description. Add a time spine model with the time_spine configuration if the project has none. Where the existing YAML documents a column the SQL does not produce, correct it and call it out. Prove it: install the project's dependencies, run dbt seed, dbt build and dbt parse, run mf validate-configs and several mf query calls grouped by month and by a dimension, lint the new SQL with sqlfluff, run dbt docs generate, and include the query results in the pull request. Do not change the existing model SQL. Open a pull request.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
  1. Get an API key. In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
  2. Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
  3. Read what came back. Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

What comes back

What comes back is a pull request that adds a models/semantic_models/ directory with one YAML file per mart holding its semantic model and the metrics defined over it, a metricflow_time_spine.sql model with its configuration, and a one-line correction in schema.yml where the documented column name did not match the SQL. No model SQL changes, so dbt build produces the same tables as before and the diff is YAML plus one small SQL file.

The description pastes the dbt parse result, the mf validate-configs output and the tables each mf query returned, revenue by month, revenue by order status and cumulative revenue, so a reviewer who knows the seed data can check the numbers add up to the order totals in orders. It names every metric with its type and the measures it is built from, and explains why the time spine was needed.

Before merging, a reviewer checks that the entity names match how the rest of the organisation refers to orders and customers, because they become the join keys every downstream metric uses, decides whether the status dimension should be grouped into fewer values, and confirms the grain of the time spine covers the date range of the real data rather than the seeds.

Run it on every Linear issue labelled metrics

Add an automation to the Project with this prompt and a linear trigger on linear.issue.labeled with the label your analytics team uses for semantic layer work. The trigger adds the labelled issue after the prompt, so an issue that asks for a specific metric, a margin by product category or a refund rate, steers the run without any edit to the prompt; a change made by an app integration never fires it, and max_runs_per_hour caps how many issues the automation picks up in an hour.

From your own server, send POST /o/{org}/sessions with the repository and the issue URL in resources so the issue text reaches the agent through your Linear connection, and a request_id that is a UUID v5 of the issue id so a retried call returns the run already started. Set a webhook_url on the key to receive run.completed with data.session_url when the pull request is open.

The use case this job follows, with its automation and trigger: Turn a Linear label into a PR.

Why run it on Rush?

Semantic models look done when dbt parse passes and are only known to be right when a query returns the number the finance team expects. On a laptop that query needs a warehouse connection and a MetricFlow install that matches the project's dbt version; in a Rush sandbox the agent installs the project's own pinned dependencies, builds the marts from seeds into a local DuckDB file and runs the metric queries there, and every command and result table is in the run's record beside the pull request.

The same request runs across every dbt project in an organisation at once, each in its own sandbox with its own dbt parse, which is how a data team with several projects gets consistent entity names and metric definitions in one review cycle instead of one project at a time.

Frequently asked questions

Does this need dbt Cloud or the Semantic Layer service?

No. Semantic models and metrics are plain YAML that dbt Core parses, and the metricflow package queries them locally against the project's adapter. Serving the metrics to BI tools through an API is what the hosted Semantic Layer adds, and the YAML the agent writes is what that service reads too.

Why does it add a time spine model?

MetricFlow needs a table of dates to compute cumulative metrics and to fill gaps when a query groups by metric_time. The agent adds one only when the project has none, as a small generate_series model in the warehouse's dialect, with the time_spine configuration so dbt knows which column is the day grain.

Will it change my existing models?

No. The semantic models reference the marts with ref and read their columns as they are. The only edit to existing YAML is where a documented column name does not match what the SQL produces, because a measure cannot point at a column that does not exist, and the pull request names each such fix.

What if a metric I asked for needs a column the marts do not have?

The agent says so in the pull request instead of inventing the column, and proposes the measure that is possible with the current marts. Adding a column to a mart is a model change with its own tests and belongs in a separate pull request.

Does it work with adapters other than DuckDB?

Yes. The semantic YAML is adapter-neutral. The time spine SQL and the local proof depend on the adapter in profiles.yml; where the warehouse cannot be reached from the sandbox, the agent runs dbt parse and mf validate-configs, which need no connection, and says that the queries were not run.

More dbt jobs

More jobs like this: Turn a Linear label into a PR

Keep reading