The reconciliation (DIG-655). Built from Pilot knowledge 111 item 815, version 7, as kept while building; private details such as addresses, identifiers and paths are masked here.
Implementation Guide (Increment 1 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 v38 — profiling: Entitlement and billing reconciliation: source profile, knowledge 111 item 813 v51 — test plan: DIG-655 Test Plan, knowledge 111 item 814 |
| Prerequisites | Phase one loaded in SQL Server and copied by the connector, customer included (tutorial, knowledge 111 item 463, step 26); the design's bronze-to-silver map, item 806 v38; the test plan, item 814 |
| Companions | Increments 2 to 4, gold, the Semantic Layer metrics and the Hex app, are written as their own parts once the silver-to-gold map is agreed |
| Status | v1 — AGREED 2026-11-19 (Ray, answer 2y) — written 2026-11-19 22:44Z |
| 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 v38 and does not relitigate its decisions.
| Design decision | Settled rule used by the implementation |
|---|---|
| D-6 | An invoice item carries its product; the header's columns ride on each item |
| D-7 | A license is a Contract with its won Opportunity's line items; silver keeps the chain whole and leaves the Activated and Closed Won filters to gold |
| D-11 | The product's code is the identifier shared by the platform, Salesforce and usage reporting |
| D-16 | Void invoices are excluded in gold; silver keeps invoice_status |
| D-17 | Change times are each table's updated_at; an item's is the later of its own and its invoice's |
| D-18 | Medallion layers: bronze is the connector's copies, silver one table per source document, gold the mart |
| D-19 | Seven silver tables, each keyed by md5 of its business key qualified by its issuer's namespace |
| D-20 | Hashing happens in silver; staging only renames and carries the deleted flag; business_key takes keys of several parts |
| D-21 | Mart tables by suffix: dim_client becomes client_dim |
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: Source Staging pattern (knowledge 14 item 37), one view per landed table
- Patterns introduced: The issuer-qualified business key, a candidate pattern (knowledge 34 item 268), not yet in knowledge 14; step 4 shows its first instance in full
2. Scope
Deliverables
- macros/business_key.sql takes keys of several parts
- Eleven landed tables declared as sources, three source systems
- Eleven new staging views, and stg_databp__client without its hashes
- Seven silver tables in schema SILVER, with their key and relationship tests and six unit tests
- client_dim in place of dim_client, read from silver client
- Four singular tests and generator/reconcile_landing.py
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 phase-one generator and its answer key; the metric client_count, its definition unchanged; metricflow_time_spine; the connector's table list.
Intentionally not done
The gold metric table and dimensions other than client_dim, the Semantic Layer metrics and the Hex app (increments 2 to 4); the generator's verify query carrying D-16; dropping Production's old DIM_CLIENT table, an act after the merge.
Identity recipes
| Entity | Natural code | Surrogate-key composition | Minting location |
|---|---|---|---|
| Data provider | DATABP, CLIENT, client_id | md5 of DATABP | CLIENT |
| Subscriber | DATABP, CUSTOMER, tenant_id, customer_id | md5 of the business key | silver subscriber; also license_line, entitlement, usage_report, invoice_item from their own columns |
| Product | DATABP, PRODUCT, tenant_id, product_code | md5 of the business key | silver product; license_line from Product2, usage_report from its code, entitlement and invoice_item through product |
| License line | SFDC, OPPORTUNITYLINEITEM, Id | md5 of the business key | silver license_line |
| Entitlement | DATABP, ENTITLEMENT, tenant_id, entitlement_id | md5 of the business key | silver entitlement |
| Usage report | USAGE, USAGE_DECLARATION, tenant_id, declaration_id | md5 of the business key | silver usage_report |
| Invoice | DATABP, INVOICE, tenant_id, invoice_id | md5 of the business key | silver invoice_item |
| Invoice item | DATABP, INVOICE_ITEM, tenant_id, invoice_id, item_id | md5 of the business key | silver invoice_item |
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 |
|---|---|---|---|
| business_key macro | macros/business_key.sql | models/staging/stg_databp__client.sql, the only consumer on main | git grep -l business_key origin/main -- models macros tests seeds, 2026-11-19 22:30 UTC |
| stg_databp__client | models/staging/stg_databp__client.sql | models/marts/dim_client.sql and its yml | git grep -l stg_databp__client origin/main -- models macros tests seeds, 22:30 UTC |
| dim_client and its metric client_count | models/marts/dim_client.sql and .yml | Inside the repository, only its own yml and README.md; outside it, Production's table DATABP_MARTS.DIM_CLIENT and the Semantic Layer's client_count | git grep -l dim_client origin/main -- . and git grep -l client_count origin/main -- ., 22:30 UTC |
4. Baseline
| Baseline ID | Behavior or value | Capture command or query | Captured result and clock | Reconciliation condition |
|---|---|---|---|---|
| B-1 | client_count and the rows of the client dimension, Production | SELECT COUNT(DISTINCT client_hk), MD5(LISTAGG(client_hk||'|'||client_name, ',') WITHIN GROUP (ORDER BY client_hk)) FROM DATABP_LAB.DATABP_MARTS.DIM_CLIENT |
20 and e0a1ebaca27467791c2081f687724967, 2026-11-19 22:28:07 UTC | The same query on DBT_CLAUDE_MARTS.CLIENT_DIM after the build returns the same two values |
5. Ordered implementation steps
Step 1. Keys of several parts (macros/business_key.sql)
Changes the existing macro; its one consumer, stg_databp__client, stops calling it in step 3, and the seven silver models call it in step 4. A single id keeps its old output, so the change is backward compatible. Design D-19, D-20. Exemplar: the file itself.
{#- A row's business key inside its issuer's namespace (Harbor's pattern, D-19):
ISSUER|TABLE|part, or ISSUER|TABLE|part1|part2 when id_expr is a list of parts (D-20). -#}
{% macro business_key(source_code, table_code, id_expr) -%}
{%- set parts = id_expr if id_expr is not string else [id_expr] -%}
'{{ source_code }}|{{ table_code }}'
{%- for part in parts %} || '|' || cast({{ part }} as varchar){% endfor %}
{%- endmacro %}
Step 2. Sources for the eleven new landed tables (models/staging/_sources.yml)
Instantiates Source Staging (knowledge 14 item 37). Three sources, one per source system (D-3, D-4): databp_src (DATABP_SRC_DBO), databp_sfdc (DATABP_SFDC_DBO), databp_usage (DATABP_USAGE_DBO). Each table is declared by its uppercase identifier, with not_null on its source key. Exemplar: the existing client entry. Delta per table: its name, identifier and key column.
- name: databp_sfdc
database: DATABP_LAB
schema: DATABP_SFDC_DBO
config:
loaded_at_field: _SNOWFLAKE_UPDATED_AT
tables:
- name: account
identifier: ACCOUNT
columns:
- name: id
data_tests: [not_null]
Step 3. Staging views (models/staging/stg_databp__*.sql and .yml)
Instantiates Source Staging (knowledge 14 item 37). One view per landed table, every source column kept and renamed to snake case, plus is_deleted, landed_at and changed_at from the connector's columns; no hash (D-20). Exemplar: models/staging/stg_databp__client.sql, rewritten first in this shape. Names follow Object Naming (knowledge 12 item 147, section 2, row 5) with this project's prefix (guide decision G-1): platform tables stg_databp__
| Test | Layer | Disposition: keep, rewrite, or delete | Reason |
|---|---|---|---|
| source not_null and unique on client.client_id | 1 | keep | The source key is unchanged |
| not_null and unique on stg_databp__client.client_bk and client_hk | 1 | delete | The keys move to silver (D-20); silver client's not_null and unique on client_hk replace them |
| not_null and unique on dim_client.client_hk | 1 | keep, renamed with the model | client_dim keeps its key |
New and changed tests
Unit tests, layer 3, in models/silver/_silver_unit_tests.yml, first instance in this repository, aligned with the reference architecture; each was run against a version of its model without the logic it proves and failed, 2026-11-19 22:27:07 UTC, then passed against the real model at 22:27:26 UTC. The first, in full:
unit_tests:
# LA-1, paths 1 to 3 (D-2): one report kept; the later of two kept; on equal declared_at, the higher declaration_id kept
- name: ut_usage_report_latest_wins
model: usage_report
given:
- input: ref('stg_databp__usage_usage_declaration')
rows:
- {tenant_id: 3, declaration_id: 1, customer_id: 3, product_code: EQUITIES, usage_month: '2026-10-01', declared_units: 10, declared_at: '2026-11-05 00:00:00', is_deleted: false}
- {tenant_id: 3, declaration_id: 2, customer_id: 23, product_code: OPTIONS, usage_month: '2026-10-01', declared_units: 12, declared_at: '2026-11-05 00:00:00', is_deleted: false}
- {tenant_id: 3, declaration_id: 3, customer_id: 23, product_code: OPTIONS, usage_month: '2026-10-01', declared_units: 15, declared_at: '2026-11-20 00:00:00', is_deleted: false}
- {tenant_id: 3, declaration_id: 4, customer_id: 43, product_code: EQUITIES, usage_month: '2026-10-01', declared_units: 12, declared_at: '2026-11-05 00:00:00', is_deleted: false}
- {tenant_id: 3, declaration_id: 5, customer_id: 43, product_code: EQUITIES, usage_month: '2026-10-01', declared_units: 13, declared_at: '2026-11-05 00:00:00', is_deleted: false}
expect:
rows:
- {declaration_id: 1, reported_units: 10}
- {declaration_id: 3, reported_units: 15}
- {declaration_id: 5, reported_units: 13}
The other five, by name and what they mock: ut_license_line_conformed_keys (one chain; expects subscriber_hk 6e7a1ee374a6187c721a8a2ea0017019, the md5 of DATABP|CUSTOMER|3|23); ut_license_line_chain (five line items, four with one piece missing; expects one row); ut_invoice_item_updated_at (three items, header later, item later, equal); ut_subscriber_deleted_excluded (one row deleted; expects the other); ut_product_change_hash (three rows; two equal hashes, one different).
Key and relationship tests, layer 1, in models/silver/_silver.yml: not_null and unique on each table's own key (14); not_null and relationships on each foreign key, client_hk of subscriber and product to client, subscriber_hk and product_hk of license_line, entitlement, usage_report and invoice_item to subscriber and product (20). The pattern, one table:
- name: license_line
columns:
- name: license_line_hk
data_tests: [not_null, unique]
- name: subscriber_hk
data_tests:
- not_null
- relationships:
arguments:
to: ref('subscriber')
field: subscriber_hk
- name: product_hk
data_tests:
- not_null
- relationships:
arguments:
to: ref('product')
field: product_hk
Singular tests in tests/: assert_invoice_amount_equals_items, assert_contract_tenant_matches_account, assert_opportunity_tenant_matches_contract (layer 4) and assert_silver_counts_match_staging (layer 6). The first, in full:
-- Layer 4, profile column 11 (D-15): an invoice with items carries the sum of its items' amounts.
-- Returns each invoice whose amount differs from its items' sum; passing returns no rows.
select
invoice.tenant_id,
invoice.invoice_id,
invoice.amount,
sum(item.amount) as items_amount
from {{ ref('stg_databp__invoice') }} as invoice
inner join {{ ref('stg_databp__invoice_item') }} as item
on item.tenant_id = invoice.tenant_id
and item.invoice_id = invoice.invoice_id
where not invoice.is_deleted and not item.is_deleted
group by invoice.tenant_id, invoice.invoice_id, invoice.amount
having invoice.amount <> sum(item.amount)
7. Build sequence and verification
| Order | Literal command | Expected result | Baseline reconciliation |
|---|---|---|---|
| 1 | dbt build --select "+models/silver" --exclude "test_type:unit" with each silver model's logic removed |
The red versions build | None |
| 2 | dbt test --select "test_type:unit" |
All six unit tests fail on the logic each proves | None |
| 3 | dbt build --select "+models/silver" with the real models |
Everything up to silver builds, all tests pass | None |
| 4 | dbt build |
The whole project builds, every test passes: 93 of 93, 2026-11-19 22:28 UTC | B-1 is compared next |
| 5 | The B-1 query on DBT_CLAUDE_MARTS.CLIENT_DIM | 20 and e0a1ebaca27467791c2081f687724967 | Equal to B-1: pass, 22:28:46 UTC |
| 6 | python3 generator/reconcile_landing.py, inside the Snowflake wrapper, with AWS credentials |
0 differences | 50 table and tenant pairs, 25,117 rows each side, 0 differences, 22:29:59 UTC |
Verification queries
The conformed keys, read in development: subscriber 83's key from the platform, Salesforce's license, the entitlement and the usage report.
SELECT src, LEFT(hk, 8) FROM (
SELECT 'platform' src, subscriber_hk hk FROM DATABP_LAB.DBT_CLAUDE_SILVER.SUBSCRIBER WHERE subscriber_id = 83
UNION ALL SELECT 'salesforce', subscriber_hk FROM DATABP_LAB.DBT_CLAUDE_SILVER.LICENSE_LINE
WHERE subscriber_hk = (SELECT subscriber_hk FROM DATABP_LAB.DBT_CLAUDE_SILVER.SUBSCRIBER WHERE subscriber_id = 83)
UNION ALL SELECT 'entitlement', subscriber_hk FROM DATABP_LAB.DBT_CLAUDE_SILVER.ENTITLEMENT
WHERE subscriber_hk = (SELECT subscriber_hk FROM DATABP_LAB.DBT_CLAUDE_SILVER.SUBSCRIBER WHERE subscriber_id = 83)
UNION ALL SELECT 'usage', subscriber_hk FROM DATABP_LAB.DBT_CLAUDE_SILVER.USAGE_REPORT
WHERE subscriber_hk = (SELECT subscriber_hk FROM DATABP_LAB.DBT_CLAUDE_SILVER.SUBSCRIBER WHERE subscriber_id = 83));
Result, 2026-11-19 22:28:46 UTC: the same key, 816e4e62..., from all four; and 13 of 13 license lines name a subscriber in silver subscriber.
8. Decisions recorded in this guide
| ID | Weed-level choice | Rationale | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| G-1 | Staging names keep the exemplar stg_databp__client for the platform and add the source system for the others: stg_databp__sfdc_
9. RollbackNothing is merged: the branch stays unmerged until all four increments are built (DIG-655). To undo increment 1 in development, reset the branch to main's 1b2aa4d and drop the schemas DBT_CLAUDE_SILVER and DBT_CLAUDE_STAGING's new views; Production is untouched until the merge. 10. What this guide does not cover
Compliance recordBuilt 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 Built 2026-11-19 22:36 UTC on version 5. Template sections, by infra/workstation/pilot/compliance_sections.py against Template: Branch Delivery — Implementation Guide (knowledge 12 item 176): all 20 present and filled; 0 placeholders left; 0 sections missing.
Decisions carried: D-6, D-7, D-11, D-16, D-17, D-18, D-19, D-20, D-21, each in section 1 and the steps that cite it. Verdict: compliant, with the new tests partly shown in full; G-8 discloses that the code ran before the guide was written. |