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
- Patterns instantiated: The nested-aggregation exception of Data Mart Design Principles (knowledge 12 item 104), first instance subscriber_product_month_metric at commit 4e6cb23, extended here from two documents to four
- Patterns introduced: None
2. Scope
Deliverables
- 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
- Eleven metrics on it: the two of 4e6cb23 and nine new ones, two by units, two in dollars and five counts of disagreements
- 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)
- tests/assert_checks_match_answer_key.sql, layer 4
- 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.