Our engine never sees your warehouse password
datahashi

BlogModeling & metrics

Finance close: one ARR across board decks and dashboards

How semi-additive snapshot measures and model tests keep ARR balances consistent across reporting tools in a thin semantic layer.

The datahashi team8 min read

The problem

Finance leads reconcile board decks against operational dashboards before executive reviews. A board presentation states that February ARR closed at 4,200,000, but an operational dashboard shows a conflicting total by summing snapshots across dates. The discrepancy delays reporting and damages trust.

The problem stems from how traditional reporting tools handle balance sheet metrics. Annual Recurring Revenue represents a balance snapshot at the close of a period, not an additive flow of transactions. Standard business intelligence dashboards evaluate SUM(arr_amount) across dates, inadvertently summing closing balances, mid-month contract renewals, and historical revisions. Operations analysts and financial planners write competing SQL queries with slightly different date filters and window functions. Without a single governed metric contract, every business intelligence tool invents its own aggregation logic.

You need a central semantic layer that enforces semi-additive aggregation rules and executes automated verification tests. With datahashi, the engine automatically selects closing snapshot records across time grains while allowing safe summation across dimensional cuts like geography.

+---------------------+       +---------------------+
| Board Deck Slide    |       | Live Ops Dashboard  |
| (Closing: 4,200,000)|       | (Summed snapshots)  |
+---------------------+       +---------------------+
          |                              |
          +--------------+---------------+
                         |
           Conflicting aggregation logic ✗
                         |
                         v
       Raw subscription tables in warehouse

The data

The reporting pipeline relies on two physical warehouse tables. The accounts table stores customer firmographics, subscription tiers, and geographic regions. The subscription_snapshots table records monthly point-in-time ARR balances for every active customer account.

CREATE TABLE accounts (
    account_id VARCHAR PRIMARY KEY,
    account_name VARCHAR NOT NULL,
    tier VARCHAR NOT NULL,
    region VARCHAR NOT NULL
);

CREATE TABLE subscription_snapshots (
    snapshot_id VARCHAR PRIMARY KEY,
    account_id VARCHAR NOT NULL REFERENCES accounts(account_id),
    as_of_date DATE NOT NULL,
    arr_amount DOUBLE NOT NULL,
    status VARCHAR NOT NULL
);

INSERT INTO accounts VALUES
    ('acc_1', 'Acme Corp', 'enterprise', 'north-america'),
    ('acc_2', 'Globex', 'mid-market', 'north-america'),
    ('acc_3', 'Initech', 'enterprise', 'europe'),
    ('acc_4', 'Soylent Inc', 'mid-market', 'europe'),
    ('acc_5', 'Umbrella Co', 'enterprise', 'asia-pacific'),
    ('acc_6', 'Hooli', 'growth', 'north-america');

INSERT INTO subscription_snapshots VALUES
    ('snap_01', 'acc_1', '2026-01-31', 1200000.0, 'active'),
    ('snap_02', 'acc_2', '2026-01-31', 400000.0, 'active'),
    ('snap_03', 'acc_3', '2026-01-31', 900000.0, 'active'),
    ('snap_04', 'acc_1', '2026-02-28', 1200000.0, 'active'),
    ('snap_05', 'acc_2', '2026-02-28', 500000.0, 'active'),
    ('snap_06', 'acc_3', '2026-02-28', 950000.0, 'active'),
    ('snap_07', 'acc_4', '2026-02-28', 350000.0, 'active'),
    ('snap_08', 'acc_5', '2026-02-28', 1200000.0, 'active');

The relationship connects each periodic subscription snapshot back to its parent customer account:

+------------------------+             +------------------+
| subscription_snapshots |             |     accounts     |
+------------------------+             +------------------+
| snapshot_id (PK)       |             | account_id (PK)  |
| account_id (FK) -------+------------>| account_name     |
| as_of_date             |             | tier             |
| arr_amount             |             | region           |
| status                 |             +------------------+
+------------------------+

The model

The semantic model defines two entities, their relationship, the semi-additive measure, and an automated verification test.

The SubscriptionSnapshot entity defines a semi-additive measure named arr_balance. By configuring agg: sum alongside snapshot: as_of_date, the engine instructs the SQL compiler to select the latest snapshot date within each requested time grain before summing across accounts or dimensions.

source: warehouse_main

entities:
  - name: Account
    table: accounts
    primary_key: account_id
    dimensions:
      - name: account_id
        column: account_id
        type: string
      - name: account_name
        column: account_name
        type: string
      - name: tier
        column: tier
        type: string
      - name: region
        column: region
        type: string

  - name: SubscriptionSnapshot
    table: subscription_snapshots
    primary_key: snapshot_id
    dimensions:
      - name: snapshot_id
        column: snapshot_id
        type: string
      - name: account_id
        column: account_id
        type: string
      - name: as_of_date
        column: as_of_date
        type: date
        grains: [day, month, quarter, year]
      - name: status
        column: status
        type: string
    measures:
      - name: arr_balance
        expr: arr_amount
        agg: sum
        snapshot: as_of_date

relationships:
  - name: snapshot_account
    from: SubscriptionSnapshot
    to: Account
    cardinality: many_to_one
    keys:
      - [account_id, account_id]

metrics:
  - name: arr
    expr: arr_balance
    description: "Annual recurring revenue balance at end of period"
    unit: USD
    format: currency

tests:
  - name: feb_closing_arr_equals_4_2m
    query:
      metrics: [arr]
      filters:
        - dimension: as_of_date
          op: le
          value: "2026-02-28"
    expect:
      rows:
        - [4200000]

The model contains two crucial features:

  • The snapshot: as_of_date property declares that arr_balance is semi-additive across time. The engine computes the maximum snapshot timestamp within each reporting window and filters to those records before performing sums.
  • The tests block specifies automated assertions. Before promoting this model version, CI pipelines run the test against real warehouse tables to verify that closing ARR for February equals 4200000.

When compiling queries for semi-additive metrics, the SQL generator builds an inner common table expression or subquery. It partitions records by account identifier and orders snapshots descending by as_of_date. The engine keeps only the most recent snapshot row for each account within the requested time interval. Then it sums the balances across the remaining dimensions like geographic region.

Model tests execute during CI validation pipelines. The test runner compiles the specified query, executes it against a test warehouse instance, and compares returned values against the expect block. If upstream data pipelines alter snapshot calculations or drop necessary records, the test fails immediately. Broken models never reach production dashboards.

Consult the model specification for syntax rules on snapshot measures. For more patterns on modeling non-additive metrics, read the guide on advanced modeling.

The queries

Downstream consumers interact through the standard JSON query contract documented in the query specification. Clients request certified metrics instead of writing raw warehouse SQL. Where a request sets include_sql, the engine returns compiled SQL only if it was started with ENGINE_ALLOW_SQL=true. MCP never returns SQL.

Query 1: Regional ARR breakdown across monthly periods

The hero query requests arr grouped by as_of_date at monthly grain and customer region. Analysts need to observe geographic revenue distribution over time:

{
  "metrics": ["arr"],
  "dimensions": [
    {
      "name": "as_of_date",
      "entity": "SubscriptionSnapshot",
      "grain": "month"
    },
    {
      "name": "region",
      "entity": "Account"
    }
  ],
  "order": [
    { "ref": "as_of_date", "desc": false },
    { "ref": "region", "desc": false }
  ],
  "include_sql": true
}

The engine executes the query and returns governed answers:

as_of_date region arr
2026-01-01T00:00:00Z europe 900000
2026-01-01T00:00:00Z north-america 1600000
2026-02-01T00:00:00Z asia-pacific 1200000
2026-02-01T00:00:00Z europe 1300000
2026-02-01T00:00:00Z north-america 1700000

The compiled SQL displays how the semi-additive logic prevents double-counting across dates:

SELECT
  q0."as_of_date" AS "as_of_date",
  q0."region" AS "region",
  CAST(q0."arr_balance" AS DOUBLE) AS "arr"
FROM (
  SELECT
    "as_of_date",
    "region",
    CAST(SUM("arr_balance") AS DOUBLE) AS "arr_balance"
  FROM (
    SELECT
      "as_of_date",
      "region",
      "arr_balance",
      "__hashi_snap",
      MAX("__hashi_snap") OVER (PARTITION BY "as_of_date", "region") AS "__hashi_snap_pick"
    FROM (
      SELECT
        date_trunc('month', "SubscriptionSnapshot"."as_of_date") AS "as_of_date",
        "Account"."region" AS "region",
        "SubscriptionSnapshot"."as_of_date" AS "__hashi_snap",
        CAST(SUM("SubscriptionSnapshot"."arr_amount") AS DOUBLE) AS "arr_balance"
      FROM "subscription_snapshots" AS "SubscriptionSnapshot"
      JOIN "accounts" AS "Account"
        ON "SubscriptionSnapshot"."account_id" = "Account"."account_id"
      GROUP BY
        date_trunc('month', "SubscriptionSnapshot"."as_of_date"),
        "Account"."region",
        "SubscriptionSnapshot"."as_of_date"
    ) AS "SubscriptionSnapshot_per_snapshot"
  ) AS "SubscriptionSnapshot"
  WHERE "__hashi_snap" = "__hashi_snap_pick"
  GROUP BY "as_of_date", "region"
) AS q0
ORDER BY "as_of_date", "region"

Notice the window function MAX(__hashi_snap) OVER (PARTITION BY as_of_date, region). The engine automatically picks the latest closing date per monthly partition before summing balances across accounts within each region.

Query 2: February total closing ARR

Filtering to February without region dimensions evaluates the total corporate closing ARR:

{
  "metrics": ["arr"],
  "filters": [
    {
      "dimension": "as_of_date",
      "entity": "SubscriptionSnapshot",
      "op": "eq",
      "value": "2026-02-28"
    }
  ],
  "include_sql": true
}

The result returns 4200000, matching the closing snapshot in the sample data:

arr
4200000

Query 3: Running model tests via HTTP

Teams run automated model verification before publishing executive decks by calling POST /v1/model-tests:

{}

The response confirms that the February closing ARR test passed:

{
  "passed": true,
  "summary": {
    "total": 1,
    "passed": 1,
    "failed": 0
  },
  "results": [
    {
      "name": "feb_closing_arr_equals_4_2m",
      "passed": true,
      "rows": 1
    }
  ]
}

Query 4: Refusal on uncertified metric

If an AI agent or analyst attempts to query an uncertified metric such as mrr_gross, the engine refuses the request with an explicit error code:

{
  "metrics": ["mrr_gross"]
}

The server returns HTTP 404:

{
  "error": "model: resolve: metric \"mrr_gross\" is not exposed by the named view",
  "code": "not_found",
  "reason": "not_in_view",
  "ref": "mrr_gross"
}

No database query executes. Rogue reporting tools cannot introduce unauthorized revenue metrics into official communications.

What to notice

  • Temporal double counting prevented. Standard SQL SUM(arr_amount) sums every row in the table across historical snapshots instead of isolating true period closing balances. The snapshot declaration isolates true period closing balances.
  • Safe multi-dimensional slicing. Slicing by region partitions the window calculation automatically. Analysts do not need to author custom window partition clauses by hand.
  • Continuous test verification. Model tests run against live compiled SQL. Schema updates or data pipeline errors trigger immediate test failures during CI validation before code merges to production.
  • Governance over ad-hoc metric creation. Uncertified metric names return immediate errors rather than hallucinated joins. Teams establish a trusted single source of truth for all executive numbers.
  • Isolated Query Runner security. The Query Runner executes standard compiled SQL on your warehouse. It pushes down window functions while keeping warehouse credentials isolated from client tools.

For foundational modeling techniques, see the guide on building your first metric in DuckDB and the post on fan-out safe metrics.

Limits

  • Upstream snapshot dependencies. Your warehouse transformation jobs must generate snapshot records at consistent calendar intervals. The engine assumes clean periodic snapshot records exist in warehouse tables.
  • Gap-filling boundaries. The engine does not backfill missing calendar dates when a subscription record skips an intermediate month. Upstream dimensional tables must provide a continuous date spine if historical gaps occur.
  • Rollup pre-aggregation restrictions. Pre-aggregated rollups do not store semi-additive measures. Window picks cannot safely re-aggregate across arbitrary time grains from pre-summed values.
  • Data entry validation. Metric contracts verify query results against defined expectations, but cannot catch invalid physical data entry in the source database. Upstream data quality checks remain the responsibility of transformation pipelines.

Product notes

An occasional email when we ship something or write something worth reading.