Give every report the same business definitions

Agree on what the report should measure

A delivery report can count shipments that arrived on time or customers who received their complete order on time. Both calculations are useful. They answer different questions, and a team needs to know which one it is looking at before judging service performance.

For this example, the business chooses complete orders delivered by the original promised date. Canceled orders are excluded. Incomplete orders remain in the count of orders the business was expected to deliver.

The source data contains four eligible orders. Two were complete by the original promise date, so the result is 50%. The runnable fulfillment guide explains every source row and supplies the model. If investment reporting is closer to your work, follow the portfolio example.

Put the definition in a model

The model author records which orders count, what “complete and on time” means, and how to calculate the percentage. The model also names the dimensions people may use to group the results, such as region.

These decisions become reusable definitions. A regional report and a company-wide report can select the same metrics without rebuilding their rules in each query. The business still owns the choice of definition and the quality of its source data.

Crossfield reads supported Apache Ossie definitions and adds rules for their use in DuckDB. The model authoring guide explains the files and their responsibilities.

Attach the model and ask a question

The fulfillment setup loads the source facts into warehouse and attaches the model as operations. The model becomes a catalog alongside the data; attachment checks its source references without copying the source rows.

ATTACH 'fulfillment-current.package.json' AS operations (
    TYPE crossfield,
    BINDINGS '{"warehouse":"warehouse"}'
);

SELECT region, eligible_orders, on_time_orders, on_time_percent
FROM SEMANTIC operations.fulfillment
GROUP BY region
ORDER BY region;

FROM SEMANTIC operations.fulfillment selects the model. The query chooses metrics and a grouping; the model supplies the eligibility and delivery rules. BINDINGS connects its logical source to the physical warehouse catalog.

The setup already performs this attachment. Run it once in a fresh connection, then use the query. The guide includes expected regional and overall results.

Inspect the calculation before running the report

When reviewing a model, you should be able to see how a business definition becomes a calculation. EXPLAIN (SEMANTIC) shows the selected definitions and generated SQL without running the report or calling providers.

EXPLAIN (SEMANTIC)
SELECT region, eligible_orders, on_time_orders, on_time_percent
FROM SEMANTIC operations.fulfillment
GROUP BY region
ORDER BY region;

WITH "__crossfield_fact_bbd41d58-a472-44de-bd04-78121436d06b" AS (
        SELECT
            __cf_source_0.region AS __cf_d0,
            "system".main.count(__cf_source_0.id) AS __cf_metric_0,
            "system".main.sum(
                CASE
                    WHEN (((__cf_source_0.delivered_units >= __cf_source_0.ordered_units) AND (__cf_source_0.last_delivered_date <= __cf_source_0.original_promised_date))) THEN (1)
                    ELSE 0
                END) AS __cf_metric_1,
            ((100.0 * "system".main.sum(
                CASE
                    WHEN (((__cf_source_0.delivered_units >= __cf_source_0.ordered_units) AND (__cf_source_0.last_delivered_date <= __cf_source_0.original_promised_date))) THEN (1)
                    ELSE 0
                END)) / "system".main.count(__cf_source_0.id)) AS __cf_metric_2,
            1 AS __cf_present
        FROM warehouse.main.order_facts AS __cf_source_0
        WHERE ((__cf_source_0.original_promised_date = CAST('2024-01-31' AS DATE)) AND (__cf_source_0.status != 'cancelled'))
        GROUP BY __cf_source_0.region
    )
SELECT
    __cf_keys.__cf_d0 AS region,
    "__crossfield_fact_bbd41d58-a472-44de-bd04-78121436d06b".__cf_metric_0 AS eligible_orders,
    "__crossfield_fact_bbd41d58-a472-44de-bd04-78121436d06b".__cf_metric_1 AS on_time_orders,
    "__crossfield_fact_bbd41d58-a472-44de-bd04-78121436d06b".__cf_metric_2 AS on_time_percent
FROM ((
                SELECT __cf_d0
                FROM "__crossfield_fact_bbd41d58-a472-44de-bd04-78121436d06b"
            ) AS __cf_keys
        LEFT JOIN
            "__crossfield_fact_bbd41d58-a472-44de-bd04-78121436d06b" ON ((__cf_keys.__cf_d0 IS NOT DISTINCT
                        FROM "__crossfield_fact_bbd41d58-a472-44de-bd04-78121436d06b".__cf_d0
                    ))
    )
ORDER BY __cf_keys.__cf_d0

This is captured output from the runnable example. Execute the original semantic query to keep Crossfield’s validation. The explanation reference also explains EXPLAIN (SQL), which binds and optimizes, and EXPLAIN ANALYZE, which executes.

Decide how much freedom query authors should have

Attaching a model makes its definitions available. You can leave ordinary SQL available for exploration, compose semantic results in a larger query, or require supported reporting queries to follow one model’s rules.

USE operations.semantic;
SET crossfield_mode = 'strict';

For example, strict mode can reject a calculation written directly against the source tables when the report must use the model. An application that must prevent users from changing those controls also needs to seal the connection. Host-controlled sessions describe that application boundary.

Keep the checks inside query execution

Crossfield resolves the definitions and builds a DuckDB query. DuckDB binds, optimizes and executes it in the same connection. Crossfield’s checks continue through supported query preparation and execution, including checks that require actual source rows.

People, applications and AI agentsSQL using named business metrics

Inside your DuckDB connection

Attached semantic modelDefinitions · relationships · approved operations

CrossfieldResolve definitions · validate · compile

Validation continues during binding

DuckDBNative query execution

1. Bind & validate
2. Optimize
3. Execute

Your tables and attached sources

Results through the same SQL connection

This engine integration is why Crossfield will ship in Haybarn, Query Farm’s DuckDB distribution. Read about the engine changes and the problems they solve.

Review a definition change before adoption

Suppose the business proposes measuring against revised delivery promises. One previously late order now qualifies. Run both model versions against the same source data to see how much that rule change affects the result.

Whether the revised promise is appropriate is a business decision. Crossfield gives reviewers a way to examine its effect before changing the reports people use. The model versions guide walks through the comparison and explains how to keep earlier definitions available.

Combine reports that different teams maintain

Different teams can maintain separate models and combine their results through approved shared dimensions. In the investment example, the portfolio model calculates exposure while the risk model supplies limits. Their results meet at the issuer dimension.

Each model calculates its own result before the join. The models must agree on the meaning and complete grouping keys of the shared dimension; identical column names are insufficient. A missing limit remains NULL.

These comparisons use semantic or off mode. Strict business queries stay within one attachment. The query composition guide covers the SQL and join rules.