I’d choose SQL, Markdown, and Python for SQL-first teams - and YAML when tools need a strict metadata schema. Either way, use one approved metric definition and test its results. A different file format shouldn’t change your revenue total.

Here’s what I compare:

Metric logic: SQL calculates revenue in both approaches.
Business rules: Markdown explains the contract; YAML organizes metadata into structured fields.
Validation: Tests check refunds, exclusions, date boundaries, joins, and finance totals - not just file syntax.
Maintenance: Git reviews, named owners, and CI keep definitions and tests aligned.
Tool access: Linked files need a catalog; YAML needs schema checks. Neither replaces warehouse permissions.

Quick Comparison

Criteria	SQL + Markdown + Python	YAML-centered definitions
Definition	Linked calculation, documentation, and test files	Structured metadata linked to SQL
Review	Direct review across several files	Schema review plus SQL and tests
Machine parsing	Requires file conventions and a generated index	Uses framework-defined fields
Best fit	Teams that work mainly in SQL	Teams that need predictable metadata parsing

My rule: check the business result before choosing the format. The sample fixture totals $350.00, with a $0.01 reconciliation tolerance. But a matching total alone isn’t enough: tests must also catch duplicate joins and offsetting errors.

::: @figure {SQL, Markdown & Python vs. YAML Semantic Layers} :::

Finally. Semantic Layer. Explained in 9 minutes.

::: @iframe https://www.youtube.com/embed/9eV8XHLe_fI :::

1. SQL, Markdown, and Python Files

Give each file one job, tied to the shared ID revenue_usd. Declare dependencies and file links so the loader can connect the calculation, business definition, and tests.

revenue.sql owns the calculation. This SQL calculates recognized revenue in USD. Subtract discounts and refunds ONLY when amount_usd is gross. If the source has already adjusted that amount, subtracting them again would double count.

-- semantic/metrics/revenue.sql
-- metric_id: revenue_usd
-- definition: semantic/metrics/revenue.md
-- validation: semantic/metrics/test_revenue.py
-- dependencies: finance.recognized_revenue,
--               dimensions.plan, dimensions.region

SELECT
    DATE_TRUNC('month', rr.recognized_at AT TIME ZONE 'UTC') AS month_utc,
    p.plan_name,
    g.region_name,
    SUM(rr.amount_usd
        - COALESCE(rr.source_adjusted_discount_usd, 0)
        - COALESCE(rr.source_adjusted_refund_usd, 0)) AS revenue_usd
FROM finance.recognized_revenue AS rr
LEFT JOIN dimensions.plan AS p ON rr.plan_id = p.plan_id
LEFT JOIN dimensions.region AS g ON rr.region_id = g.region_id
WHERE rr.recognized_at >= %(start_utc)s
  AND rr.recognized_at <  %(end_utc)s
  AND rr.currency_code = 'USD'
  AND rr.recognition_status = 'recognized'
  AND rr.is_test = FALSE
  AND rr.is_internal = FALSE
GROUP BY 1, 2, 3;

revenue.md owns the business contract. Use the approved revenue-recognition source, not raw orders. Both joins must be many-to-one. Duplicate dimension keys can inflate totals even when each output row has a different key.

# revenue_usd

Calculation: semantic/metrics/revenue.sql
Validation: semantic/metrics/test_revenue.py
Dependencies: finance.recognized_revenue, dimensions.plan, dimensions.region

Source grain: One row per recognition_id, not per order.
Output grain: One row per UTC month, plan, and region.
Adjustments: Subtract source-approved discount and refund amounts once.
Exclusions: Test, internal, voided, and non-recognized records.
Currency: USD transactions only; no currency conversion dependency.
Time: Half-open UTC months. January 2026 ends before
2026-02-01 00:00:00 UTC. No external calendar dependency.
Reporting: This metric uses UTC months, not America/New_York months.

Relationships: plan_id and region_id are many-to-one lookups.
Dimension keys must be unique. Missing lookups must be handled explicitly.
State whether plan and region labels use current or as-of-recognition values.

Business owner: Controller’s Office.
Technical owner: Analytics Engineering.
Update: Re-run after each source refresh; late adjustments can restate months.
Change control: Finance approval and a versioned pull request for rule changes.
Failure behavior: Failed freshness or reconciliation checks block publication.
Limitations: Not bookings, billings, cash collected, or ARR. Do not label it
GAAP revenue without explicit Finance approval.

test_revenue.py checks the executed SQL - not a second revenue formula. Here, test fixtures run the linked SQL and retrieve an independently approved finance control total for the same period and scope. Generate that control total separately; copying the metric formula would repeat the same logic rather than check it.

# semantic/metrics/test_revenue.py
# metric_id: revenue_usd
# dependencies: revenue.sql, revenue.md, approved finance control total

from decimal import Decimal

def test_revenue_usd(execute_metric, approved_finance_total):
    rows = execute_metric(
        sql_path="semantic/metrics/revenue.sql",
        metric_id="revenue_usd",
        start_utc="2026-01-01T00:00:00Z",
        end_utc="2026-02-01T00:00:00Z",
    )

    assert rows
    assert all(r["month_utc"] == "2026-01-01" for r in rows)
    assert all(r["revenue_usd"] is not None for r in rows)
    assert all(r["plan_name"] is not None and
               r["region_name"] is not None for r in rows)

    keys = [(r["month_utc"], r["plan_name"], r["region_name"])
            for r in rows]
    assert len(keys) == len(set(keys))
    total = sum(Decimal(str(r["revenue_usd"])) for r in rows)
    assert total == approved_finance_total

Add separate checks for duplicate dimension keys, source freshness, excluded transactions, and records exactly at the UTC month boundary. Revenue isn't always nonnegative: approved adjustments can produce negative values. Run these checks on pull requests and production refreshes. Documentation alone can't enforce the semantic layer contract.[3][4]

Next, compare the same contract in YAML to see what changes in review and machine parsing.

2. YAML-Centered Definitions

YAML stores semantic metadata, not transformation logic. In dbt Semantic Layer, YAML defines entities, dimensions, and metrics. SQL still creates the row-level data.[7][5]

The example below uses legacy MetricFlow YAML to define the same revenue metric. Check the field names against your installed dbt and MetricFlow version.[2][1]

semantic_models:
  - name: recognized_revenue
    # SQL reads finance.recognized_revenue, applies exclusions,
    # normalizes recognized_at to UTC, and subtracts approved
    # discounts and refunds once to produce net_amount_usd.
    model: ref('recognized_revenue')
    defaults:
      agg_time_dimension: recognized_at_utc
    entities:
      - name: recognition
        type: primary
        expr: recognition_id
      - name: plan
        type: foreign
        expr: plan_id
      - name: region
        type: foreign
        expr: region_id
    dimensions:
      - name: recognized_at_utc
        type: time
        type_params:
          time_granularity: month
    measures:
      - name: revenue_usd
        expr: net_amount_usd
        agg: sum

  - name: plan
    model: ref('plan') # SQL reads dimensions.plan
    entities:
      - name: plan
        type: primary
        expr: plan_id
    dimensions:
      - name: plan_name
        type: categorical

  - name: region
    model: ref('region') # SQL reads dimensions.region
    entities:
      - name: region
        type: primary
        expr: region_id
    dimensions:
      - name: region_name
        type: categorical

metrics:
  - name: revenue_usd
    type: simple
    label: Recognized Revenue (USD)
    type_params:
      measure:
        name: revenue_usd

Entities define join paths that MetricFlow uses to resolve valid joins. Here, those are many-to-one lookups from recognition records to plan and region.[7][6] The time dimension sets the grouping date. Querying by UTC month, plan, and region keeps the same output grain as before.

Filters need an explicit home. Here, the SQL model keeps recognized records in USD and excludes test, internal, voided, and non-recognized records. It also applies the same approved adjustments and half-open UTC reporting window. Put universal exclusions in SQL. Metric-specific filters can live in YAML if your installed schema supports them.[1]

YAML describes the metric, but SQL and tests still determine whether it’s correct. All three must agree.[8] When revenue needs to reconcile to finance totals, check that the SQL result still matches the control total.

Revenue Calculations and Correctness Checks

Test business rules, not file syntax. Start with a small USD fixture to check signed amounts, exclusions, and reconciliation before using the pattern with production revenue. The fixture shows how the three files stay aligned when the metric runs against data.

-- revenue_fixture.sql  -  Snowflake SQL
WITH lines AS (
  SELECT column1 AS order_id,
         column2 AS order_line_id,
         column3 AS customer_id,
         column4 AS order_date,
         column5 AS product_id,
         column6 AS quantity,
         column7 AS unit_price,
         column8 AS status,
         column9 AS adjustment_type,
         column10 AS currency
  FROM VALUES
    ('O1001','L1','C001','2026-01-05','P100',2,  50.00,'completed','sale','USD'),
    ('O1002','L2','C002','2026-01-20','P200',1, 120.00,'completed','sale','USD'),
    ('O1003','L3','C001','2026-01-25','P100',1, -20.00,'completed','refund','USD'),
    ('O1004','L4','C003','2026-02-03','P300',1,  75.00,'completed','sale','USD'),
    ('O1005','L5','C002','2026-02-10','P200',1, 100.00,'completed','sale','USD'),
    ('O1006','L6','C003','2026-02-15','P300',1, -25.00,'completed','refund','USD'),
    ('O1007','L7','C001','2026-02-18','P999',1,  40.00,'test','sale','USD'),
    ('O1008','L8','C002','2026-02-28','P200',1,  60.00,'canceled','sale','USD')
),
valid_lines AS (
  SELECT
    order_line_id,
    customer_id,
    product_id,
    CAST(order_date AS DATE) AS order_date,
    currency,
    quantity * unit_price AS line_revenue
  FROM lines
  WHERE status NOT IN ('canceled', 'test')
    AND currency = 'USD'
    AND CAST(order_date AS DATE) >= DATE '2026-01-01'
    AND CAST(order_date AS DATE) < DATE '2026-03-01'
)
SELECT
  CAST(DATE_TRUNC('month', order_date) AS DATE) AS revenue_month,
  customer_id,
  product_id,
  'USD' AS currency,
  SUM(line_revenue) AS revenue_usd
FROM valid_lines
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

# revenue.md

- Currency: USD only.
- Input grain: one order line.
- Output grain: one row per calendar month, customer, and product.
- Exclusions: canceled rows, test rows, and unsupported currencies.
- Expected results: January 2026 = $200.00; February 2026 = $150.00.

If the source uses timestamps rather than dates, convert them to the documented reporting time zone before truncating to month. Include January 1, 2026, and exclude March 1, 2026.

Refunds carry a negative sign, so don't require revenue_usd >= 0.

# test_revenue.py  -  rows are dictionaries from the SQL result
from decimal import Decimal

EXPECTED = {
    "2026-01-01": Decimal("200.00"),
    "2026-02-01": Decimal("150.00"),
}

def check_revenue(rows, accounting_total):
    totals, seen = {}, set()

    for row in rows:
        month = row["revenue_month"].isoformat()
        key = (month, row["customer_id"], row["product_id"])
        assert key not in seen, f"Duplicate output grain: {key}"
        seen.add(key)

        assert month in EXPECTED
        assert row["customer_id"] is not None
        assert row["product_id"] is not None
        assert row["currency"] == "USD"
        assert row["revenue_usd"] is not None

        totals[month] = totals.get(month, Decimal("0")) + Decimal(str(row["revenue_usd"]))

    assert totals == EXPECTED
    assert abs(sum(totals.values()) - accounting_total) <= Decimal("0.01")

Use an independently produced accounting control that covers the same period and scope. Here, the results should match a $350.00 control within $0.01.

Add separate failing fixtures for duplicate line IDs, required nulls, unsupported currencies, missing dimensions, and duplicated dimension keys. Compare row counts and sums before and after joins to detect fan-out.

The pattern also works across semantic layers in business intelligence and warehouse engines, with a few syntax changes.

Platform	Changes to the Snowflake example
Snowflake	Runs as shown.
BigQuery	Replace `VALUES` with `UNNEST` of structs; use `DATE_TRUNC(order_date, MONTH)`.
Amazon Redshift	Load the fixture into a table.
PostgreSQL	Use `FROM (VALUES …) AS v(...)` with named columns.

Pair dbt structural tests and custom SQL assertions[3][4] with checks for refunds, exclusions, date boundaries, join cardinality, and reconciliation. A matching total isn't enough: offsetting errors can hide mistakes. When the files change in Git, review these checks to keep the contract intact.

Maintenance and Git Review

Getting the metric right is one step. Keeping its contract intact as files change is the next.

Both approaches work with Git, pull requests, branch protection, code owners, and CI. The difference is how much reviewers need to check. SQL, Markdown, and Python spread a metric across more files; YAML keeps the contract in one place.

For revenue_usd, that means keeping SQL, Markdown, Python, fixtures, and references aligned with the same contract.

Maintenance concern	SQL + Markdown + Python	YAML-centered definitions
Documentation	Markdown supports long business explanations.	YAML supports compact metadata.
Visible logic	SQL and Python show the logic directly.	YAML often hides logic behind framework keys.
File discovery	Naming conventions or a manifest connect related files.	Central definitions make files easier to find, but large files are harder to scan.
Cross-file drift	Separate files need stronger sync checks.	Central contracts can still drift from SQL and tests.
Schema checks	Custom checks validate metadata and file links.	Structured fields support schema validation.
Python environments	Validation needs reproducible environments and pinned packages.	Framework validation may reduce Python dependencies.
Dependencies	Warehouse, package, and file dependencies need tracking.	Frameworks centralize some dependencies, but not external tooling.

A business-rule change puts this trade-off into focus.

Treat a refund-policy change as a coordinated change to revenue_usd, not an isolated SQL edit. If the policy changes at a 30-day boundary, update the SQL calculation, Markdown definition, Python tests, fixture, and catalog links together. State whether “excluded” means excluding a refund event or subtracting the refunded amount - those are different calculations. Document the effective date and whether historical periods change. Tests should cover both sides of the boundary and any historical restatement.

Use one atomic commit, or a short sequence that leaves the repo valid at every step. Assign business and technical owners to the revenue domain, and route reviews through CODEOWNERS rules.[9] Require CI results for the latest commit - not a successful run from an earlier revision.[10]

Pin the Python version and direct dependencies. Record resolved dependencies in a lockfile, and test against the warehouse dialect used in production.

Block broken references, duplicate metric names or aliases, incompatible warehouse schemas, and outdated generated indexes. Report every broken path. Send orphaned definitions and overdue deprecations to their owners for review rather than deleting them: downstream consumers may still depend on them. Keep these checks separate from source-data freshness checks.

The next question is whether these maintenance rules are easier for people to review or for machines to parse.

Machine Readability, Governance, and Choosing an Approach

After Git review, the next step is keeping the revenue_usd contract readable by tools and agents. SQL needs dialect-aware parsing, Markdown needs metadata conventions or front matter, and Python needs controlled execution. For SQL/Markdown/Python workflows, generate JSON metadata or a JSON index in CI.

Give revenue_usd a stable metric ID that doesn't depend on its display name. The index should connect the calculation, documentation, and checks, and specify the owner, grain, currency, time zone, dimensions, and status. Keep table dependencies separate from metric dependencies. Declare join keys and cardinality explicitly - don't infer them from SQL.

Schema validation checks this structure. It does not confirm revenue correctness or verify that joins preserve totals. The structure supports automation, but it doesn't replace warehouse access controls.

Neither format handles query planning or access control. Definition governance controls business meaning. Warehouse roles, masking, and row-level policies control data access. Give AI agents an approved catalog and a permission-checked query interface. For each answer, record the metric ID, commit, and executed query.

In a governed warehouse workflow, Querio keeps context synced to GitHub, exposes inspectable SQL and Python, and uses live read-only warehouse connections in reactive notebooks.

Use SQL/Markdown/Python when analysts work SQL-first and can maintain catalog-generation tooling. Use YAML when strict schemas and predictable downstream parsing matter most. If both coexist, keep one authoritative definition and generate the other representation.

FAQs

::: faq

How can we migrate from YAML without breaking reports?

Map your most important YAML metrics to their warehouse tables and join paths. Move calculations into SQL, business rules into Markdown, and validation checks into Python. Before deployment, test against warehouse data to make sure the new definitions produce the same results as the existing ones.

Store these files alongside your dbt models in Git. Use pull requests for peer review, track changes, and roll back if definitions drift. When results don’t match, inspect the SQL or Python to find the cause. :::

::: faq

How should we version metrics when business rules change?

Treat your SQL and metric definitions like code: track them in Git, review changes through pull requests, and keep a clear way to roll back changes. When a number changes, the version history lets you trace it to a specific commit and explain why.

With a governed semantic layer, you update a definition once, and that change automatically flows to downstream dashboards, reports, and AI-generated analyses. This keeps metrics consistent and prevents version drift. :::

::: faq

How can we detect errors that revenue totals hide?

Don’t rely on high-level totals alone. Use a governed context layer with detailed business rules and logic you can inspect. Revenue totals can hide errors caused by improper joins that multiply rows, internal test accounts, or missing filters such as is_deleted = false.

Keep these definitions in one place so AI-generated and analyst-run queries use the same approved filters, join paths, and exclusions. That makes hidden assumptions visible. :::

File-Based Semantic Layers: SQL, Markdown and Python Instead of YAML

Finally. Semantic Layer. Explained in 9 minutes.

1. SQL, Markdown, and Python Files

2. YAML-Centered Definitions

Revenue Calculations and Correctness Checks

Maintenance and Git Review

Machine Readability, Governance, and Choosing an Approach

FAQs

How can we migrate from YAML without breaking reports?

How should we version metrics when business rules change?

How can we detect errors that revenue totals hide?

Magic happens where
people and AI collaborate

Finally. Semantic Layer. Explained in 9 minutes.

1. SQL, Markdown, and Python Files

2. YAML-Centered Definitions

Revenue Calculations and Correctness Checks

Maintenance and Git Review

Machine Readability, Governance, and Choosing an Approach

FAQs

How can we migrate from YAML without breaking reports?

How should we version metrics when business rules change?

How can we detect errors that revenue totals hide?

Magic happens where people and AI collaborate

Magic happens where
people and AI collaborate