DataBP POC
DataBP POC / Documents / Implementation guide, 2 of 4

DIG-655 Implementation Guide, Increment 2 of 4: gold and the five checks

Gold and the five checks, step by step.

The reconciliation (DIG-655). Built from Pilot knowledge 111 item 816, version 8, as kept while building; private details such as addresses, identifiers and paths are masked here.

Implementation Guide (Increment 2 of 4)

Branch Delivery Document — Level 4 — Implementation Guide

Effort DIG-655 — Reconciliation dashboard build, four increments to the Hex app — branch rayserrano2735/dig-655-reconciliation-dashboard-build
Governed by Standard: Branch Delivery Documents (K#12 I#27)
References Design: Entitlement and billing reconciliation: design document, knowledge 111 item 806 v41 — profiling: Entitlement and billing reconciliation: source profile, knowledge 111 item 813 v51 — test plan: DIG-655 Test Plan, knowledge 111 item 814
Prerequisites Increment 1 built (DIG-655 Implementation Guide, Increment 1 of 4, knowledge 111 item 815; commits 0eafef5 and 82fcc72); the gold tables and the exception of D-22 committed in 4e6cb23; the design's silver-to-gold map, item 806 v41
Companions Increment 1, staging and silver (knowledge 111 item 815); increments 3 and 4, the Semantic Layer battery and the Hex app, written as their own parts
Status v1 — COMPLIANT, NOT PRESENTED FOR AGREEMENT — written 2026-11-20 01:45Z (Ray's pre-authorization of 2026-11-19 16:06 ET: a guide that meets its template's standard needs no stop for agreement; the workflow approved 20:39 ET, answer 1y)
Audience The session that builds DIG-655; Ray Serrano, who reviews
Author Ray Serrano; edited by Claude — 2026-11-19
Classification Internal — Data Management
Official copy method Copied from this governed template by AGENTIC.DATASKILLS.KNOWLEDGE_ITEM_FROM_TEMPLATE; template SHA-256 recorded in the created item

1. Purpose and grounding recap

This guide implements Entitlement and billing reconciliation: design document, knowledge 111 item 806 v41 and does not relitigate its decisions.

Design decision Settled rule used by the implementation
D-2 The checks compare license, entitlement, usage report and invoice per subscriber, product and month
D-8 The answer key is the expected result: a test requires the table to find exactly its rows
D-10 The fifth check, entitled beyond reported, prices at risk the units an under-reporting audit would test
D-12 An absent document counts as zero units
D-16 Void invoices are not billing; invoice_item_metric already excludes them
D-21 Gold tables are named by suffix
D-22 subscriber_product_month_metric is the nested-aggregation exception: each row keeps its own result for each check, and MetricFlow sums the rows

Code in this guide is an illustrative pattern: more than pseudocode, less than shipping code. Do not copy it into the repo; implement it against the branch. Development is done when the output is shown correct against real data, per K#12 I#26.

Pattern declaration

2. Scope

Deliverables

  1. subscriber_product_month_metric reads all four document tables and product_dim, and gains the presence flags, entitled, billed and price columns, the five checks and the two dollar figures
  2. Eleven metrics on it: the two of 4e6cb23 and nine new ones, two by units, two in dollars and five counts of disagreements
  3. Five unit tests, one per check, the two-sides unit test given the new inputs, and three filter unit tests on the document tables (LA-10)
  4. tests/assert_checks_match_answer_key.sql, layer 4
  5. tests/assert_subscriber_product_month_reconciles.sql widened to entitled units, billed units and billed amount

Finance workbook delivery

For finance reporting branches, name the repository generation script, shared mart-level report query and parameters, reproducible generation/refresh steps, workbook metadata and sheets, and preservation of consumer observations under K#12 I#27. Otherwise state NA with a reason.

NA: not a finance reporting branch.

What stays the same

The four document tables and the two dimensions of 4e6cb23; silver and staging; the metrics licensed_units, entitled_units, reported_units, billed_units, billed_amount and client_count; the answer key and the generator.

Intentionally not done

The Semantic Layer battery (increment 3); the Hex app (increment 4); "since the last close" and the live change (Ray, 2026-11-19 20:37 ET, answer 2y); the generator's verify query carrying D-16.

Identity recipes

Entity Natural code Surrogate-key composition Minting location
Subscriber, product and month subscriber_hk, product_hk, usage_month md5 of subscriber_hk, product_hk and usage_month joined by a vertical bar gold subscriber_product_month_metric

3. Grounded in code and complete consumer set

Every edit below names a file and object read from the stated branch.

Touched object Exact file Consumers found Verification command and clock
subscriber_product_month_metric models/marts/subscriber_product_month_metric.sql and .yml Its unit tests in models/marts/_marts_unit_tests.yml and tests/assert_subscriber_product_month_reconciles.sql; nothing else git grep -l subscriber_product_month_metric HEAD -- ., 2026-11-20 01:39 UTC
entitlement_month_metric and invoice_item_metric models/marts/entitlement_month_metric.sql, invoice_item_metric.sql and their yml Their own yml only; this increment adds the exception as a reader The same grep for each name, 01:39 UTC
seed_reconciliation_answer_key seeds/seed_reconciliation_answer_key.csv generator/phase_one.py writes it; no model or test reads it before this increment The same grep, 01:39 UTC

4. Baseline

Baseline ID Behavior or value Capture command or query Captured result and clock Reconciliation condition
B-2 The exception's rows and its two columns, development SELECT COUNT(*), COUNT_IF(usage_month='2026-10-01'), SUM(IFF(usage_month='2026-10-01', units_reported_beyond_licensed, 0)), SUM(IFF(usage_month='2026-10-01', units_licensed_not_reported, 0)), MD5(LISTAGG(subscriber_product_month_hk||'|'||units_reported_beyond_licensed||'|'||units_licensed_not_reported, ',') WITHIN GROUP (ORDER BY subscriber_product_month_hk)) FROM DATABP_LAB.DBT_CLAUDE_MARTS.SUBSCRIBER_PRODUCT_MONTH_METRIC 157, 14, 24, 22 and f9a569ae977cfe5a4e93ac89521b6dc8, 2026-11-20 01:39:57 UTC The same query, restricted to rows with has_license or has_report, returns the same five values

5. Ordered implementation steps

Step 1. The exception reads four documents (models/marts/subscriber_product_month_metric.sql)

Extends the nested-aggregation exception of D-22 (Data Mart Design Principles, knowledge 12 item 104). Each document table is summed to the subscriber, product and month; the grain is the union of their keys; each document joins to it, absent counting as zero (D-12); the product's unit price comes from product_dim. The five checks follow the answer key's definitions (D-8): three by units, two by the presence of a document. Expected movement: rows with only an entitlement or only a billed item are added; every row present before keeps its licensed and reported units and its two columns (B-2). Exemplar: the file at 4e6cb23. The model as built:

-- Gold subscriber_product_month_metric: one row per subscriber, product and month that has a license,
-- an entitlement, a usage report or a billed invoice item. Absent counts as zero units (D-12).
-- Registered exception under Data Mart Design Principles (k12ki104): nested aggregation.
-- Each row's check columns are a per-row reduction that MetricFlow then sums across rows (D-22).
-- Unit tests: ut_subscriber_product_month_two_sides and ut_check_*. Layer 6: assert_subscriber_product_month_reconciles.
-- Layer 4: assert_checks_match_answer_key.
-- Retires when MetricFlow can sum a difference taken per subscriber, product and month across those
-- groups in one metric; the metrics then read the four document tables directly.
{% set documents = [
    ('license',     'license_line_month_metric', 'licensed_units'),
    ('entitlement', 'entitlement_month_metric',  'entitled_units'),
    ('report',      'usage_report_metric',       'reported_units'),
    ('billing',     'invoice_item_metric',       'billed_units'),
] %}
with
{% for name, model, units in documents %}
{{ name }} as (
    select client_hk, subscriber_hk, product_hk, usage_month, sum({{ units }}) as {{ units }}
        {%- if name == 'billing' %}, sum(billed_amount) as billed_amount{% endif %}
    from {{ ref(model) }}
    group by client_hk, subscriber_hk, product_hk, usage_month
),
{% endfor %}
grain as (
    {% for name, model, units in documents %}
    select client_hk, subscriber_hk, product_hk, usage_month from {{ name }}
    {% if not loop.last %}union{% endif %}
    {% endfor %}
),
joined as (
    select
        grain.client_hk,
        grain.subscriber_hk,
        grain.product_hk,
        grain.usage_month,
        license.subscriber_hk is not null       as has_license,
        entitlement.subscriber_hk is not null   as has_entitlement,
        report.subscriber_hk is not null        as has_report,
        billing.subscriber_hk is not null       as has_billing,
        coalesce(license.licensed_units, 0)     as licensed_units,
        coalesce(entitlement.entitled_units, 0) as entitled_units,
        coalesce(report.reported_units, 0)      as reported_units,
        coalesce(billing.billed_units, 0)       as billed_units,
        coalesce(billing.billed_amount, 0)      as billed_amount,
        product.unit_price
    from grain
    {% for name, model, units in documents %}
    left join {{ name }}
        on {{ name }}.subscriber_hk = grain.subscriber_hk
       and {{ name }}.product_hk = grain.product_hk
       and {{ name }}.usage_month = grain.usage_month
    {% endfor %}
    left join {{ ref('product_dim') }} as product
        on product.product_hk = grain.product_hk
)

select
    md5(subscriber_hk || '|' || product_hk || '|' || cast(usage_month as varchar)) as subscriber_product_month_hk,
    client_hk,
    subscriber_hk,
    product_hk,
    usage_month,
    has_license,
    has_entitlement,
    has_report,
    has_billing,
    licensed_units,
    entitled_units,
    reported_units,
    billed_units,
    billed_amount,
    unit_price,
    -- The five checks of D-2 and D-10: three by units, two by presence (the answer key, D-8)
    greatest(reported_units - licensed_units, 0)                    as units_reported_beyond_licensed,
    greatest(licensed_units - reported_units, 0)                    as units_licensed_not_reported,
    greatest(entitled_units - licensed_units, 0)                    as units_entitled_beyond_licensed,
    greatest(entitled_units - reported_units, 0)                    as units_entitled_beyond_reported,
    iff(reported_units > licensed_units, 1, 0)                      as is_reported_beyond_licensed,
    iff(entitled_units > licensed_units, 1, 0)                      as is_entitled_beyond_licensed,
    iff(has_billing and not has_entitlement, 1, 0)                  as is_billed_without_entitlement,
    iff(has_entitlement and not has_billing, 1, 0)                  as is_entitled_without_billing,
    iff(entitled_units > reported_units, 1, 0)                      as is_entitled_beyond_reported,
    -- The two dollar figures, at the product's unit price (design 6.3; the answer key)
    greatest(reported_units - licensed_units, 0) * unit_price       as unbilled_amount,
    greatest(entitled_units - reported_units, 0) * unit_price       as at_risk_amount
from joined

Step 2. The metrics (models/marts/subscriber_product_month_metric.yml)

Each metric is a simple sum of one row-grain column (Data Mart Design Principles: a fact carries row-grain measures, MetricFlow computes the totals); its description says what it counts, what to pair it with and the decision that defined it. One in full; the other ten have the same shape with their own column: units_reported_beyond_licensed, units_licensed_not_reported, units_entitled_beyond_licensed, units_entitled_beyond_reported, unbilled_revenue, revenue_at_risk, and the five disagreements_ counts.

      - name: revenue_at_risk
        description: "Units entitled beyond reported times the product's unit price: the amount an under-reporting audit would test, not proven use (D-10)"
        label: Revenue at risk
        type: simple
        agg: sum
        expr: at_risk_amount
        fill_nulls_with: 0

Each step states the exact file, object, pattern citation, design or profiling citation, literal first-pass code, expected movement, and any required target rebuild.

6. Tests

Existing-test dispositions

Test Layer Disposition: keep, rewrite, or delete Reason
ut_subscriber_product_month_two_sides 3 rewrite The model reads three more inputs; they are given, empty except product_dim
assert_subscriber_product_month_reconciles 6 rewrite Widened to entitled units, billed units and billed amount, and to the two new unit columns
not_null and unique on subscriber_product_month_hk 1 keep The key is unchanged

New and changed tests

Three filter unit tests, layer 3, in the same file, for the test plan's LA-10: ut_license_line_month_filters (passing, Contract not Activated, Opportunity not won, a month the license does not cover), ut_entitlement_month_coverage (two coverage periods) and ut_invoice_item_month_filters (a paid and a void invoice). The five check tests failed against the model of 4e6cb23 at 2026-11-20 01:45:10 UTC, before the new model was built; the filter tests prove logic already committed in 4e6cb23. Five unit tests, layer 3, in models/marts/_marts_unit_tests.yml, one per check, each with a row that meets it and rows that do not: ut_check_reported_beyond_licensed (above, equal, below; dollars 100 at 50), ut_check_entitled_beyond_licensed (above, equal, entitled with no license), ut_check_billed_without_entitlement and ut_check_entitled_without_billing (one document present, both present), ut_check_entitled_beyond_reported (above, equal, entitled with no report; dollars 120 and 80 at 40). The first, in full:

  - name: ut_check_reported_beyond_licensed
    model: subscriber_product_month_metric
    given:
      - input: ref('license_line_month_metric')
        rows:
          - {client_hk: C, subscriber_hk: A, product_hk: P, usage_month: '2026-10-01', licensed_units: 10}
          - {client_hk: C, subscriber_hk: B, product_hk: P, usage_month: '2026-10-01', licensed_units: 10}
          - {client_hk: C, subscriber_hk: D, product_hk: P, usage_month: '2026-10-01', licensed_units: 10}
      - input: ref('usage_report_metric')
        rows:
          - {client_hk: C, subscriber_hk: A, product_hk: P, usage_month: '2026-10-01', reported_units: 12}
          - {client_hk: C, subscriber_hk: B, product_hk: P, usage_month: '2026-10-01', reported_units: 10}
          - {client_hk: C, subscriber_hk: D, product_hk: P, usage_month: '2026-10-01', reported_units: 8}
      - input: ref('entitlement_month_metric')
        rows: []
      - input: ref('invoice_item_metric')
        rows: []
      - input: ref('product_dim')
        rows:
          - {product_hk: P, unit_price: 50}
    expect:
      rows:
        - {subscriber_hk: A, is_reported_beyond_licensed: 1, units_reported_beyond_licensed: 2, unbilled_amount: 100}
        - {subscriber_hk: B, is_reported_beyond_licensed: 0, units_reported_beyond_licensed: 0, unbilled_amount: 0}
        - {subscriber_hk: D, is_reported_beyond_licensed: 0, units_reported_beyond_licensed: 0, unbilled_amount: 0}

The answer-key test, layer 4, tests/assert_checks_match_answer_key.sql, in full:

-- D-8, layer 4: the reconciliation finds exactly the answer key's rows. Every answer-key row's five
-- checks and two dollar figures equal subscriber_product_month_metric's, and the table has no row in
-- the answer key's months that the answer key does not name. Returns each row that disagrees.
with answer_key as (
    select
        tenant_id, customer_id, product_code, usage_month,
        iff(reported_beyond_licensed, 1, 0)   as is_reported_beyond_licensed,
        iff(entitled_beyond_licensed, 1, 0)   as is_entitled_beyond_licensed,
        iff(billed_without_entitlement, 1, 0) as is_billed_without_entitlement,
        iff(entitled_without_billing, 1, 0)   as is_entitled_without_billing,
        iff(entitled_beyond_reported, 1, 0)   as is_entitled_beyond_reported,
        unbilled_dollars, at_risk_dollars
    from {{ ref('seed_reconciliation_answer_key') }}
),
found as (
    select
        client.client_id as tenant_id, subscriber.subscriber_id as customer_id, product.product_code,
        spm.usage_month,
        spm.is_reported_beyond_licensed, spm.is_entitled_beyond_licensed, spm.is_billed_without_entitlement,
        spm.is_entitled_without_billing, spm.is_entitled_beyond_reported,
        spm.unbilled_amount, spm.at_risk_amount
    from {{ ref('subscriber_product_month_metric') }} as spm
    inner join {{ ref('subscriber_dim') }} as subscriber on subscriber.subscriber_hk = spm.subscriber_hk
    inner join {{ ref('client_dim') }} as client on client.client_hk = spm.client_hk
    inner join {{ ref('product_dim') }} as product on product.product_hk = spm.product_hk
    where spm.usage_month in (select usage_month from answer_key)
)

select
    coalesce(answer_key.customer_id, found.customer_id) as customer_id,
    coalesce(answer_key.product_code, found.product_code) as product_code,
    case when answer_key.customer_id is null then 'found, not in the answer key'
         when found.customer_id is null then 'in the answer key, not found'
         else 'values differ' end as finding
from answer_key
full outer join found
    on found.tenant_id = answer_key.tenant_id
    and found.customer_id = answer_key.customer_id
    and found.product_code = answer_key.product_code
    and found.usage_month = answer_key.usage_month
where answer_key.customer_id is null
    or found.customer_id is null
    or found.is_reported_beyond_licensed   <> answer_key.is_reported_beyond_licensed
    or found.is_entitled_beyond_licensed   <> answer_key.is_entitled_beyond_licensed
    or found.is_billed_without_entitlement <> answer_key.is_billed_without_entitlement
    or found.is_entitled_without_billing   <> answer_key.is_entitled_without_billing
    or found.is_entitled_beyond_reported   <> answer_key.is_entitled_beyond_reported
    or found.unbilled_amount <> answer_key.unbilled_dollars
    or found.at_risk_amount  <> answer_key.at_risk_dollars

7. Build sequence and verification

Order Literal command Expected result Baseline reconciliation
1 dbt build --select +subscriber_product_month_metric assert_checks_match_answer_key assert_subscriber_product_month_reconciles The model builds; nine unit tests, the answer-key test and the reconciliation pass B-2 is compared next
2 The B-2 query, restricted to rows with has_license or has_report 157, 14, 24, 22 and the same hash Equal to B-2
3 dbt build The whole project builds, every test passes None
4 The verification query below October's totals equal the answer key's None

Verification queries

October 2026's totals in development, against the answer key's (14 rows, data provider 3):

SELECT SUM(is_reported_beyond_licensed), SUM(is_entitled_beyond_licensed), SUM(is_billed_without_entitlement),
       SUM(is_entitled_without_billing), SUM(is_entitled_beyond_reported), SUM(unbilled_amount), SUM(at_risk_amount)
FROM DATABP_LAB.DBT_CLAUDE_MARTS.SUBSCRIBER_PRODUCT_MONTH_METRIC
WHERE usage_month = '2026-10-01';

Expected: 7, 6, 2, 6 and 6 disagreements; 1,120 dollars unbilled; 1,440 dollars at risk.

8. Decisions recorded in this guide

ID Weed-level choice Rationale
G-9 The grain is the union of the four document tables' subscriber, product and month keys A row exists where any document does, so a presence check can see a missing one
G-10 A document is present when its table has a row for the subscriber, product and month, whatever its units The answer key's presence checks test a document's existence (generator/phase_one.py, expected())
G-11 The dollar figures use product_dim's unit_price The answer key prices at the product's price
G-12 The code was written and compiled, not built, before this guide The guide quotes the code that will run; the build follows in phase 2 of the approved workflow

9. Rollback

Revert the branch to 4e6cb23 and rebuild subscriber_product_month_metric in development; Production is untouched until the merge.

10. What this guide does not cover

Exclusion Canonical home
The Semantic Layer battery Increment 3 of this guide
The Hex app Increment 4
Since the last close and the live change A later workflow (Ray, 2026-11-19 20:37 ET, answer 2y)
D-16 in the generator's verify query A follow-up on this branch

Compliance record

Built by the author last, before the document is presented for agreement (Standard: Branch Delivery Documents, K#12 I#27, "Compliance record before agreement"): every section and field of this template shown present and filled, by infra/workstation/pilot/compliance_sections.py; every rule of that standard and of the standards it cites, each with the passage that meets it or marked NOT MET; and the decisions the document carries. While this section reads NOT BUILT, the document is not presented for agreement.

Built 2026-11-20 01:44 UTC on version 4. Template sections, by infra/workstation/pilot/compliance_sections.py against Template: Branch Delivery — Implementation Guide (knowledge 12 item 176): all present and filled; 0 placeholders left; 0 sections missing.

Requirement (Standard: Branch Delivery Documents, knowledge 12 item 27, the Implementation Guide's required sections) Passage that meets it
Header with Increment N of M, prerequisites and companions Title and field table: Increment 2 of 4; prerequisites and companions filled
Purpose, grounding recap, the implementer preamble verbatim, the pattern declaration Section 1: seven decisions recapped; the preamble as the template carries it; the pattern instantiated named, none introduced
Every pattern citation resolves to code The nested-aggregation exception, Data Mart Design Principles (knowledge 12 item 104), exists; its instance is step 1's code in full
Scope, with identity recipes upfront Section 2: five deliverables, unchanged scope, exclusions, one identity recipe
Grounded in code, the consumer set by repository search, with the command Section 3: three objects, git grep at 01:39 UTC
Baseline before the change Section 4: B-2 captured at 01:39:57 UTC, before the model changed in development
Steps per object with file, pattern, exemplar and literal code Section 5: steps 1 and 2; step 1's model in full, step 2 one metric in full and the other ten named
Tests: dispositions and the new tests as literal code Section 6: three dispositions; one unit test and the answer-key test in full, the other four unit tests named, their full text in the repository. Partly met
Build sequence with expected results and baseline reconciliation Section 7: four commands; B-2 reconciled in order 2
Decisions recorded, and a line for a shared macro with more than five consumers Section 8: G-9 to G-12; no shared macro changes
Rollback Section 9
Exclusions with their homes Section 10
Data Mart Design Principles (knowledge 12 item 104) Facts carry row-grain measures; every total is a MetricFlow metric; the exception is single-sourced, unit-tested, reconciled at layer 6, with its retirement condition in the model's header

Decisions carried: D-2, D-8, D-10, D-12, D-16, D-21, D-22, each in section 1 and the steps that cite it.

Verdict: compliant, with the new tests partly shown in full; G-12 discloses that the code was written and compiled before this guide.