From dbt MetricFlow to SLayer¶

TL;DR: SLayer can ingest a dbt MetricFlow semantic layer — semantic_models + metrics — and turn it into queryable SLayer models. This notebook runs the real dbt-labs ACME Insurance benchmark through the converter end-to-end and answers two of its questions, checking each answer against the benchmark's gold SQL.

It runs as three explicit steps (everything lands in a gitignored .cache/ next to this notebook):

Get the data — clone the dbt project at a pinned commit and load its CSVs into a local DuckDB file.
Convert — run the dbt MetricFlow definitions through convert_dbt_to_slayer (its own cell below).
Query — point a SLayer client at the converted models and verify against gold SQL.

Requires network access on first run (a shallow git fetch of ~a few MB). Re-runs are instant from the cache.

Step 1 — Get the data¶

Clone the pinned dbt project and load its ACME Insurance CSVs into DuckDB.

In [1]:

Copied!





import os
import sys

import pandas as pd

# The setup helper lives next to this notebook.
sys.path.insert(0, os.getcwd())

from setup_metricflow import (
    ensure_dbt_data,
    convert_dbt_to_slayer,
    fetch_gold,
    DB_PATH,
    MODELS_DIR,
    DBT_PIN_SHA,
)
from slayer.client.slayer_client import SlayerClient
from slayer.storage.yaml_storage import YAMLStorage

dbt_project = ensure_dbt_data()
print(f"Cloned dbt project @ {DBT_PIN_SHA[:12]} and loaded ACME CSVs into DuckDB.")
import os
import sys

import pandas as pd

# The setup helper lives next to this notebook.
sys.path.insert(0, os.getcwd())

from setup_metricflow import (
    ensure_dbt_data,
    convert_dbt_to_slayer,
    fetch_gold,
    DB_PATH,
    MODELS_DIR,
    DBT_PIN_SHA,
)
from slayer.client.slayer_client import SlayerClient
from slayer.storage.yaml_storage import YAMLStorage

dbt_project = ensure_dbt_data()
print(f"Cloned dbt project @ {DBT_PIN_SHA[:12]} and loaded ACME CSVs into DuckDB.")

Cloned dbt project @ e4bdee5baeaa and loaded ACME CSVs into DuckDB.

Step 2 — Convert dbt MetricFlow to SLayer¶

This is the heart of the demo. convert_dbt_to_slayer parses the project's semantic_models and metrics and runs DbtToSlayerConverter: each dbt semantic model becomes a SLayer model, and each dbt metric folds into a ModelMeasure formula on its source model.

Two of those metrics drive Query 2 below:

loss_payment_amount and loss_reserve_amount are simple metrics with a filter (has_loss_payment = 1 / has_loss_reserve = 1) — the converter pushes each filter down into a filtered aggregate.
total_loss_amount is a derived metric (loss_payment_amount + loss_reserve_amount) — expressed as a formula over the two filtered metrics.

In [2]:

Copied!





result = convert_dbt_to_slayer(
    dbt_project_path=dbt_project,
    models_dir=MODELS_DIR,
    db_path=DB_PATH,
)
print(
    f"Converted {len(result.models)} SLayer models "
    f"({len(result.unconverted_metrics)} metrics could not be converted)."
)
result = convert_dbt_to_slayer(
    dbt_project_path=dbt_project,
    models_dir=MODELS_DIR,
    db_path=DB_PATH,
)
print(
    f"Converted {len(result.models)} SLayer models "
    f"({len(result.unconverted_metrics)} metrics could not be converted)."
)

Converted 28 SLayer models (0 metrics could not be converted).

Step 3 — Query the converted models¶

Point a SLayer client at the freshly converted models.

In [3]:

Copied!

client = SlayerClient(storage=YAMLStorage(base_dir=str(MODELS_DIR)))
client = SlayerClient(storage=YAMLStorage(base_dir=str(MODELS_DIR)))

Reference answers (gold SQL)¶

The benchmark ships a gold SQL query for every question. We run them first, up front, and stash the expected numbers.

Why up front? SLayer opens the DuckDB file through a read-write engine, and DuckDB won't let a second raw connection share the file under a different configuration. So all gold queries run before any SLayer query touches the file.

In [4]:

Copied!





GOLD_CLAIMS_SQL = "SELECT COUNT(*) AS NoOfClaims FROM claim"

GOLD_LOSS_SQL = """
SELECT
    company_claim_number,
    (ca_lp.claim_amount + ca_lr.claim_amount) AS LossAmount
FROM Claim
    INNER JOIN claim_amount ca_lp ON claim.claim_identifier = ca_lp.claim_identifier
    INNER JOIN loss_payment ON ca_lp.claim_amount_identifier = loss_payment.claim_amount_identifier
    INNER JOIN claim_amount ca_lr ON claim.claim_identifier = ca_lr.claim_identifier
    INNER JOIN loss_reserve ON ca_lr.claim_amount_identifier = loss_reserve.claim_amount_identifier
ORDER BY company_claim_number
"""

gold_claims = fetch_gold(DB_PATH, GOLD_CLAIMS_SQL)
gold_loss = fetch_gold(DB_PATH, GOLD_LOSS_SQL)

print("gold — how many claims:", gold_claims)
pd.DataFrame(gold_loss)
GOLD_CLAIMS_SQL = "SELECT COUNT(*) AS NoOfClaims FROM claim"

GOLD_LOSS_SQL = """
SELECT
    company_claim_number,
    (ca_lp.claim_amount + ca_lr.claim_amount) AS LossAmount
FROM Claim
    INNER JOIN claim_amount ca_lp ON claim.claim_identifier = ca_lp.claim_identifier
    INNER JOIN loss_payment ON ca_lp.claim_amount_identifier = loss_payment.claim_amount_identifier
    INNER JOIN claim_amount ca_lr ON claim.claim_identifier = ca_lr.claim_identifier
    INNER JOIN loss_reserve ON ca_lr.claim_amount_identifier = loss_reserve.claim_amount_identifier
ORDER BY company_claim_number
"""

gold_claims = fetch_gold(DB_PATH, GOLD_CLAIMS_SQL)
gold_loss = fetch_gold(DB_PATH, GOLD_LOSS_SQL)

print("gold — how many claims:", gold_claims)
pd.DataFrame(gold_loss)

gold — how many claims: [{'NoOfClaims': 2}]

Out[4]:

	Company_Claim_Number	LossAmount
0	12312701	2200
1	12312702	4400

Query 1 — "How many claims do we have?"¶

A plain row count. In SLayer, *:count is COUNT(*) and is always available without defining a measure.

In [5]:

Copied!





q1 = {"source_model": "claim", "measures": ["*:count"]}
slayer_claims = client.query_sync(q1).data
print("SLayer:", slayer_claims)

# Compare the scalar answer to gold (compare values; column names differ by design).
slayer_count = next(iter(slayer_claims[0].values()))
gold_count = next(iter(gold_claims[0].values()))
assert slayer_count == gold_count, f"{slayer_count} != {gold_count}"
print(f"OK — SLayer and gold agree: {slayer_count} claims")
q1 = {"source_model": "claim", "measures": ["*:count"]}
slayer_claims = client.query_sync(q1).data
print("SLayer:", slayer_claims)

# Compare the scalar answer to gold (compare values; column names differ by design).
slayer_count = next(iter(slayer_claims[0].values()))
gold_count = next(iter(gold_claims[0].values()))
assert slayer_count == gold_count, f"{slayer_count} != {gold_count}"
print(f"OK — SLayer and gold agree: {slayer_count} claims")

SLayer: [{'claim._count': 2}]
OK — SLayer and gold agree: 2 claims

Query 2 — "Total loss by claim number"¶

This is the interesting one. We ask for the derived metric total_loss_amount (= loss_payment_amount + loss_reserve_amount, each a filtered simple metric) grouped by claim number. claim.company_claim_number reaches the claim number through the join the converter inferred from the dbt entities — no manual SQL join needed.

We rename the measure to LossAmount via the name field so the output column reads cleanly.

In [6]:

Copied!





q2 = {
    "source_model": "claim_amount",
    "measures": [{"formula": "total_loss_amount", "name": "LossAmount"}],
    "dimensions": ["claim.company_claim_number"],
    "order": [{"column": "claim.company_claim_number"}],
}
slayer_loss = client.query_sync(q2).data
display(pd.DataFrame(slayer_loss))

# Compare (claim_number, loss) pairs to gold, by value.
slayer_pairs = [(r["claim_amount.claim.company_claim_number"], r["claim_amount.LossAmount"]) for r in slayer_loss]
gold_pairs = [(g["Company_Claim_Number"], g["LossAmount"]) for g in gold_loss]
assert slayer_pairs == gold_pairs, f"{slayer_pairs} != {gold_pairs}"
print(f"OK — SLayer and gold agree on {len(slayer_pairs)} claim(s)")
q2 = {
    "source_model": "claim_amount",
    "measures": [{"formula": "total_loss_amount", "name": "LossAmount"}],
    "dimensions": ["claim.company_claim_number"],
    "order": [{"column": "claim.company_claim_number"}],
}
slayer_loss = client.query_sync(q2).data
display(pd.DataFrame(slayer_loss))

# Compare (claim_number, loss) pairs to gold, by value.
slayer_pairs = [(r["claim_amount.claim.company_claim_number"], r["claim_amount.LossAmount"]) for r in slayer_loss]
gold_pairs = [(g["Company_Claim_Number"], g["LossAmount"]) for g in gold_loss]
assert slayer_pairs == gold_pairs, f"{slayer_pairs} != {gold_pairs}"
print(f"OK — SLayer and gold agree on {len(slayer_pairs)} claim(s)")

	claim_amount.claim.company_claim_number	claim_amount.LossAmount
0	12312701	2200
1	12312702	4400

OK — SLayer and gold agree on 2 claim(s)

Recap¶

Starting from a dbt MetricFlow project we did not write, SLayer:

converted the semantic_models and metrics into queryable models,
resolved a join from dbt entities so a measure on one model could group by a column on another,
expressed a derived metric over two filtered metrics as a single formula,

and the answers matched the benchmark's gold SQL exactly.