slbt — Semantic Layer Build Tool

Get started · Documentation · AI-assisted workflow · Jaffle Shop demo · Contributing

Unify transformation logic and business meaning. Compile to dbt, document your models, export structured context for AI, and inspect and optimize SQL.

slbt keeps transformations, business definitions and tests in YAML. Its JSON catalogs combine SQL, descriptions, types and dependencies so external AI assistants can generate downstream logic consistent with your definitions, rather than infer business meaning from SQL alone.

Excel supports models, sources, macros, defaults and classifications; CSV supports models and sources.

slbt is an offline build tool for transformation semantics, not a metrics query service or a replacement for dbt's execution engine. dbt handles database connections, materializations, snapshots and test execution. slbt exports documentation as dbt properties for dbt docs; it does not host a documentation site.

Why keep business meaning with transformation logic?

SQL defines calculations; consumers also need their grain, units, inclusion rules and intended use. A payment total is not necessarily net revenue, and distinct order counts per payment method cannot necessarily be summed across methods.

Generate dbt artifacts and structured context from those definitions so people and external AI assistants can review implementation and intent together.

Keep AI-assisted logic consistent and maintainable

AI assistants can produce plausible but different implementations of the same business request. Differences in filters, joins, null handling or aggregation grain can create conflicting business definitions across models.

Reuse accepted rules in downstream models:

AI proposes logic
       ↓
People review definitions, inspect SQL and run tests
       ↓
Accepted logic becomes canonical YAML
       ↓
Downstream models reference approved models
       ↓
Changes are reviewed, tested and regenerated

Assistants may interpret “customer lifetime value” as all payments, completed-order payments or payments minus refunds. Reference an approved customer-value model to reuse its calculation, and make different business definitions explicit.

With slbt, you can:

• Reference existing models and share supported macros instead of copying calculations.
• Review canonical YAML diffs and inspect supported expanded upstream SQL, including selected columns.
• Update canonical definitions and regenerate dbt artifacts and catalogs instead of editing outputs individually.
• Export expressions, descriptions, types, logical column lineage and dependencies for subsequent work.

Reuse reviewed definitions to keep calculations consistent. AI output can still vary. Exported context cannot force an assistant to reuse a model, prevent every conflicting definition or prove business correctness. Offline validation, human review and executed dbt tests serve different purposes; none replaces the others.

Why add slbt to dbt?

dbt already supports descriptions, tests, metadata, reusable models and machine-readable artifacts. slbt adds an optional authoring and review layer; documenting dbt or using AI with it does not require slbt.

Choose slbt when you want to:

• Author transformation expressions and their metadata together in YAML.
• Generate dbt SQL and properties from those canonical definitions.
• Inspect supported upstream transformations offline and preview logical optimization.
• Review supported definitions through editable Excel/CSV catalogs.
• Export scoped, resolved context for external assistants and integrations.

slbt adds a format and compilation step. You may not need it if handwritten dbt SQL, properties and existing tooling meet your needs.

How does this differ from dbt Semantic Layer?

slbt focuses on defining and reviewing transformations before execution. dbt Semantic Layer, powered by MetricFlow, focuses on querying reusable metrics across dimensions, using declared entities, aggregation rules and supported join paths.

Both can carry business meaning. dbt supports semantic annotations in model YAML; MetricFlow supports local development and SQL generation. The managed service and APIs are separate components.

You do not need another semantic layer simply because you use slbt. Add a metric-query layer when consumers need reusable aggregation rules, dimensional queries and governed metric access—not just defined transformation outputs.

Some projects may benefit from both, but slbt generates ordinary dbt artifacts, not MetricFlow semantic models or metric definitions. Using both requires separately maintained metric configuration and integration testing.

See dbt's semantic model specification and Semantic Layer architecture.

Features

AI-assisted authoring uses external assistants; there is no built-in LLM service. JSON is read-only context, not an import format. Logical optimization does not guarantee faster warehouse execution.

Quickstart

Requires Python 3.11+, uv and Git. Install from source; these instructions do not depend on a published PyPI package.

git clone https://github.com/billybillysss/slbt.git
cd slbt
uv sync --locked
uv run slbt --project-dir examples/jaffle_shop validate
uv run slbt --project-dir examples/jaffle_shop inspect gld_customers --columns customer_lifetime_value
uv run slbt --project-dir examples/jaffle_shop export dbt
uv run slbt --project-dir examples/jaffle_shop export catalog --format json

Validation reports Validated 21 models and 3 source tables offline. Inspect prints optimized, expanded SQL without changing files. Exports populate generated subfolders of examples/jaffle_shop/dbt/ and create examples/jaffle_shop/catalog/catalog.json. Installing dependencies needs network access; these slbt commands need no database or warehouse credentials.

To run the local DuckDB demo with the same dbt project:

uv run dbt build --project-dir examples/jaffle_shop/dbt --profiles-dir examples/jaffle_shop/dbt

It builds bundled seeds, models, a customer snapshot and data tests. See the walkthrough for business rules, expected results, catalog editing and optimization cases.

Example: YAML, dbt SQL and JSON

This is the real payment-method model. Payment counts include zero-value payments; paid_order_count counts distinct order IDs, not payment rows. Silver has already converted cents to currency units.

version: 1
layer: gold
domain: sales
tables:
  - name: gld_payment_method_summary
    description: Payment-method usage, including zero-value payments.
    config:
      materialized: view
      meta:
        owner: payments
    output:
      from:
        ref: slv_payments
        alias: p
      group_by:
        - p.payment_method
      columns:
        payment_method:
          expression: p.payment_method
          data_type: varchar
          data_tests:
            - unique
            - not_null
        payment_count:
          expression: COUNT(*)
          data_type: bigint
        paid_order_count:
          expression: COUNT(DISTINCT p.order_id)
          data_type: bigint
        payment_total:
          expression: CAST(SUM(p.amount) AS DECIMAL(12, 2))
          data_type: decimal(12, 2)

Generated dbt SQL (dbt/models/gld/gld_payment_method_summary.sql):

{{
  config({
    'materialized': 'view',
    'meta': {
      'owner': 'payments',
      'domain': 'sales'
    }
  })
}}

SELECT
  p.payment_method AS "payment_method",
  COUNT(*) AS "payment_count",
  COUNT(DISTINCT p.order_id) AS "paid_order_count",
  CAST(SUM(p.amount) AS DECIMAL(12, 2)) AS "payment_total"
FROM {{ ref('slv_payments') }} AS "p"
GROUP BY
  p.payment_method

The export also emits descriptions, column types and tests as dbt properties. Resolved JSON includes SQL, column expressions and lineage, metadata, dependencies and macro-expansion status. Selected fields from the same model in catalog.json:

{
  "format": "slbt.catalog",
  "format_version": 1,
  "representation": "resolved",
  "models": [
    {
      "name": "gld_payment_method_summary",
      "description": "Payment-method usage, including zero-value payments.",
      "output": {
        "sql": "SELECT p.payment_method AS \"payment_method\", COUNT(*) AS \"payment_count\", COUNT(DISTINCT p.order_id) AS \"paid_order_count\", CAST(SUM(p.amount) AS DECIMAL(12, 2)) AS \"payment_total\" FROM \"ref\".\"slv_payments\" AS \"p\" GROUP BY p.payment_method",
        "columns": [
          {
            "name": "payment_method",
            "data_type": "varchar",
            "data_tests": ["unique", "not_null"],
            "expression": "p.payment_method",
            "lineage": {
              "status": "resolved",
              "upstream_columns": [{"relation": "ref.slv_payments", "column": "payment_method"}]
            }
          },
          {
            "name": "payment_count",
            "data_type": "bigint",
            "expression": "COUNT(*)",
            "lineage": {"status": "resolved", "upstream_columns": []}
          },
          {
            "name": "paid_order_count",
            "data_type": "bigint",
            "expression": "COUNT(DISTINCT p.order_id)",
            "lineage": {
              "status": "resolved",
              "upstream_columns": [{"relation": "ref.slv_payments", "column": "order_id"}]
            }
          },
          {
            "name": "payment_total",
            "data_type": "decimal(12, 2)",
            "expression": "CAST(SUM(p.amount) AS DECIMAL(12, 2))",
            "lineage": {
              "status": "resolved",
              "upstream_columns": [{"relation": "ref.slv_payments", "column": "amount"}]
            }
          }
        ]
      },
      "dependencies": [
        {"relation": "ref.slv_payments", "included": true}
      ],
      "expansion": {"status": "expanded", "macro_refs": []}
    }
  ]
}

JSON is read-only context, not an import format or a fully inlined query. Use inspect to expand loaded upstream models. The JSON reference explains the versioned envelope and deferred runtime macros. README examples are checked against exports in the test suite.

Inspect: generate optimized SQL for selected columns

To trace payment_total, use inspect. It expands upstream logic and generates optimized SQL for the selected columns, pruning unused projections and merging eligible intermediate steps. Operations needed to preserve results, including joins, grouping and ranking, remain intact. No source files change.

uv run slbt --project-dir examples/jaffle_shop inspect gld_payment_method_summary --columns payment_total

SQL output, with CLI headers omitted:

SELECT
  a.payment_total AS payment_total
FROM (
  SELECT
    CAST(SUM(CAST(ROUND(p.amount / 100.0, 2) AS DECIMAL(12, 2))) AS DECIMAL(12, 2)) AS payment_total
  FROM source.jaffle_shop.payments AS p
  GROUP BY
    p.payment_method
) AS a;

The query shows source payments, conversion from cents, rounding and aggregation. It retains payment-method grouping even though only the total is selected, and omits unrelated output columns.

Generated relation aliases follow canonical optimization's a, b, c, …, z, aa, ab, … rule, skipping collisions. Here a is generated; authored p and output name payment_total are preserved. SQL and logical-plan views use the same names.

For a logical plan instead of SQL:

uv run slbt --project-dir examples/jaffle_shop inspect gld_payment_method_summary --columns payment_total --view plan

Both commands are offline and read-only. Logical source names still need physical bindings before execution; the plan is not a warehouse EXPLAIN plan. Inspection optimizes by default. Add --optimize false to see expanded SQL without optimization.

Optimize: simplify and rewrite canonical YAML

Unlike inspect, optimize rewrites canonical YAML rather than producing a standalone SQL file. It parses and simplifies transformations, including SQL in steps and output.sql, then reconstructs structured YAML where supported. Other queries remain SQL within YAML. It does not import arbitrary .sql files.

The bundled lifetime-value teaching model has two pass-through steps and an unused status projection. Preview their removal:

uv run slbt --project-dir examples/jaffle_shop optimize -s gld_customer_lifetime_value --preserve-macro false --dry-run

--preserve-macro false expands static macros, including round_money, before optimization. The dry run reports proposed file actions, not a YAML or SQL diff. The excerpts show the teaching model's actual rewrite.

steps:
  projected_orders:
    from:
      ref: gld_orders
      alias: o
    columns:
      customer_id: o.customer_id
      amount: o.amount
      unused_status: o.status
  normalized_orders:
    from:
      step: projected_orders
      alias: p
    columns:
      customer_id: p.customer_id
      amount: p.amount + 0
output:
  from:
    step: normalized_orders
    alias: n
  group_by:
    - n.customer_id
  columns:
    customer_id:
      expression: n.customer_id
      data_type: bigint
      data_tests:
        - unique
        - not_null
    lifetime_value:
      expression: CAST(round_money(SUM(n.amount)) AS DECIMAL(12, 2))
      data_type: decimal(12, 2)
      description: Total payments for customers with orders; unlike gld_customers, customers without orders are absent.

output:
  from:
    ref: gld_orders
    alias: o
  group_by:
    - o.customer_id
  columns:
    customer_id:
      expression: o.customer_id
      data_type: bigint
      data_tests:
        - unique
        - not_null
    lifetime_value:
      expression: CAST(ROUND(SUM(o.amount + 0), 2) AS DECIMAL(12, 2))
      data_type: decimal(12, 2)
      description: Total payments for customers with orders; unlike gld_customers, customers without orders are absent.

Final column names, order, types, descriptions and tests are preserved. The rewrite removes redundant query layers and an unused projection; amount + 0 remains. Optimization does not fold every expression.

To apply the rewrite, use a review branch or a separate project copy:

uv run slbt --project-dir examples/jaffle_shop optimize -s gld_customer_lifetime_value --preserve-macro false
git diff -- examples/jaffle_shop/models/gld/optimization/customer_lifetime_value.yml
uv run slbt --project-dir examples/jaffle_shop validate
uv run slbt --project-dir examples/jaffle_shop export dbt

Re-run dbt data tests before deployment and refresh other generated artifacts as needed. Logical simplification does not guarantee faster warehouse execution. See the inspection and optimization guide for more examples and limitations.

Safety and maturity

slbt is currently 0.2.0. Evaluate it on a version-controlled project before adopting it in production; passing offline validation does not prove business correctness or adapter compatibility.

• inspect and validate are read-only. Preview mutating operations with --dry-run.
• optimize, enrich, import and sync-classifications write canonical files unless previewed. Review a Git diff before exporting or deploying.
• Exports overwrite managed outputs. --prune can delete files in managed scope, including handwritten files placed there. Keep generated directories dedicated.
• Runtime-dependent macros export to dbt, but cannot be inspected offline; optimization skips those models unchanged and reports why.
• Logical plans are not warehouse EXPLAIN plans. Multi-file writes are not crash-safe transactions; do not run concurrent writers against one project.

Read the production checklist, compatibility policy and security policy.

Develop and contribute

uv run pytest
uv build

See Contributing, architecture, testing, release checks and the changelog. Start at the documentation index for configuration, CLI reference and troubleshooting.

License

slbt is MIT licensed. Bundled Jaffle Shop seed data retains its Apache 2.0 license and provenance.