Portfolio exposure and execution price

A portfolio team and a risk team can work from the same position records and still report different exposure numbers. A shared model makes the choice of measure, valuation date, and included books explicit.

This example uses independently authored, synthetic cash-equity data. The positions have already been valued in USD. We are modeling reporting definitions, not pricing instruments or calculating total portfolio risk.

Understand the positions

Each row is one position at a valuation date. Atlas and Boreal are fictional issuer identifiers. The Atlas positions hold the same common share class, valued at the same USD closing price on each date.

For Atlas, gross exposure is the sum of absolute position values: $20 million. Net exposure is the signed sum: $4 million. Neither is a substitute for the other.

Load the example

Download finance.zip. It contains the data setup, complete models, two revision packages, queries, independently specified answers, and captured explanation. No API account or Python package is required.

Use the matching native engine and extension. Request evaluation access if you need the development build; the example download contains data and models only. The verified shared-engine CLI is named haybarn; use its actual path:

unzip finance.zip
cd finance
/path/to/matched/haybarn -unsigned -init /dev/null :memory: \
  -cmd "LOAD '/absolute/path/to/crossfield.duckdb_extension';" < session.sql

For an interactive session, omit < session.sql, then run:

.bail on
.read setup.sql
.read run.sql
.read explain.sql

The setup creates a warehouse attachment with positions, limits, and fills. It attaches four semantic catalogs: portfolio, proposed, risk, and trading. Explicit bindings connect the models’ logical source to the physical warehouse. Run setup once per fresh connection.

Select named measures

SELECT issuer, gross_exposure, net_exposure
FROM SEMANTIC portfolio.exposure
GROUP BY issuer
ORDER BY issuer;

SELECT gross_exposure, net_exposure
FROM SEMANTIC portfolio.exposure;

The totals are $23 million gross and $7 million net. The older position and Expansion book are excluded by the current model, even though the query has no WHERE clause.

SELECT book, gross_exposure, net_exposure
FROM SEMANTIC portfolio.exposure
GROUP BY book
ORDER BY book;

Book A: $15m gross, $15m net. Book B: $8m gross, −$8m net.

The Ossie document defines the position fields and metric expressions. The Crossfield manifest declares approved dimensions, units, binding identity, and this population:

as_of = DATE '2024-01-31' AND book IN ('Book A', 'Book B')

That fragment is a model-authored population expression, not an additional query the user must remember. This fixture intentionally fixes the reporting date. A production model with selectable dates needs an explicit as-of interface and temporal aggregation rules; positions should not simply be summed across dates.

Calculate the price actually paid

The fills table contains two active fills for buy order ORD-101: 100 shares at $100 and 900 shares at $102. A third, voided fill at $999 tests the model’s active-fill filter.

SELECT order_id, filled_shares, execution_cost, execution_price
FROM SEMANTIC trading.executions
GROUP BY order_id;

The execution model defines execution_price as SUM(shares * price) / NULLIF(SUM(shares), 0). Crossfield recomputes this ratio from the selected fill population. It does not average precomputed order prices. The metric is marked non-additive; zero total shares yields NULL. Prices are USD per share, excluding fees, for our own order—not a market-wide VWAP benchmark.

Compare exposure with limits

The risk model contains one Atlas limit of $22 million at the same reporting date. Both models explicitly approve semantic joins and identify their issuer fields as the same shared dimension. This is stronger than merely naming both columns issuer.

SET crossfield_mode = 'semantic';

SELECT p.issuer, p.gross_exposure, l.gross_limit
FROM SEMANTIC portfolio.exposure AS p
LEFT JOIN SEMANTIC risk.limits AS l
  ON p.issuer = l.issuer
GROUP BY p.issuer
ORDER BY p.issuer;

The left join preserves Boreal. A missing limit remains NULL. This query reports exposure alongside a limit; it does not block a trade or establish that a portfolio is within every applicable risk limit.

Cross-model queries use semantic or off mode. Strict mode keeps each business query within one attachment. See query composition for full grouping and compatibility rules.

Review a model revision

The proposed revision includes the Expansion book’s $5 million position. It uses the same source records and exposure formulas as the current revision. The two packages have the same package identity and revisions 1 and 2; separate attachment names keep both definitions available.

WITH current AS (
  SELECT gross_exposure FROM SEMANTIC portfolio.exposure
), candidate AS (
  SELECT gross_exposure FROM SEMANTIC proposed.exposure
)
SELECT current.gross_exposure AS current_exposure,
       candidate.gross_exposure AS proposed_exposure,
       candidate.gross_exposure - current.gross_exposure AS change
FROM current CROSS JOIN candidate;

The $5 million difference comes from model scope, not a new trade or changed price. The outer subtraction is an analyst’s reconciliation. It is not a newly approved semantic metric. A model revision preserves a definition; it does not freeze the underlying data.

The model versions guide explains how to keep earlier definitions available and select a reviewed version for future reporting.

Inspect the generated SQL

EXPLAIN (SEMANTIC)
SELECT issuer, gross_exposure, net_exposure
FROM SEMANTIC portfolio.exposure
GROUP BY issuer
ORDER BY issuer;

The captured native SQL contains the January 31/book filter, both exposure formulas, and grouping by issuer. Download the full explanation. EXPLAIN (SEMANTIC) does not execute the report or call providers. Execute the original semantic query to retain its validation.

Verification and scope

The numerical expectations are specified directly from the source rows in expected-results.txt, independently of the compiler. The focused native regression runs with optimization enabled and disabled. The complete downloadable CLI session also runs in both modes. Read the exact verification record.

No live connector, customer account, pricing feed, FX model, derivatives netting, or trading-limit enforcement is included. The retail walkthrough remains a separate example of relationships between facts at different grains.