The reconciliation (DIG-655). Built from Pilot knowledge 111 item 814, version 41, as kept while building; private details such as addresses, identifiers and paths are masked here.
Test Plan
Branch Delivery Document — Level 4 — Test Plan
| 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 v46 — implementation guides: DIG-655 Implementation Guide, Increment 1 of 4: staging and silver, knowledge 111 item 815; Increment 2 of 4: gold and the five checks, knowledge 111 item 816 — QA standard: K#2 I#167 |
| Status | v1 — AGREED 2026-11-19 (Ray, answer 2y) — written 2026-11-19 22:44Z |
| Audience | Ray Serrano, who decides; the session that builds and tests each increment |
| 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. Executive summary
| Model | Logic areas | Brute-force paths | Unique paths after collapse |
|---|---|---|---|
| silver usage_report | LA-1 restated usage reports | 4 | 3 |
| silver license_line | LA-2 keys conformed to the platform; LA-3 the license chain | 20 | 9 |
| silver invoice_item | LA-4 change time; LA-5 product lookup | 5 | 5 |
| silver entitlement | LA-6 product lookup | 2 | 2 |
| every silver table | LA-7 deleted at the source; LA-8 change hash | 4 | 4 |
| gold subscriber_product_month_metric (increment 2) | LA-9 the five checks; LA-10 coverage and filters | 208 | 22 |
2. Logic areas
LA-1 Restated usage reports (silver usage_report)
Dimensions: the number of reports for one data provider, subscriber, product and month (one, or two or more); and, with two or more, whether the latest declared_at is unique or shared by two reports. Brute force 4. Collapse: with one report two equal times cannot occur, so 3 paths. Design D-2: one report per month counts.
LA-2 Keys conformed to the platform (silver license_line)
Dimensions: the Account's Tenant__c and Subscriber_Id__c name a platform subscriber (yes or no); the Product2's Tenant__c and ProductCode name a platform product (yes or no). Brute force 4; no collapse, since each pair fails on its own. Design D-19.
LA-3 The license chain (silver license_line)
Dimensions: for a line item, its Opportunity, Contract, Account and Product2 each present (yes or no). Brute force 16. Collapse: any missing piece drops the line item, whichever it is, so the paths are all present and each one missing alone: 5. Design D-7.
LA-4 Change time (silver invoice_item)
Dimensions: the invoice's updated_at against its item's: later, earlier or equal. Brute force 3; no collapse. Design D-17.
LA-5 and LA-6 Product lookup (silver invoice_item, silver entitlement)
Dimension: the row's product_id names a platform product (yes or no). Brute force 2 each; no collapse. Design D-11, D-19.
LA-7 Deleted at the source; LA-8 change hash (every silver table)
LA-7: the staging row is marked deleted (yes or no), 2 paths, one test on subscriber standing for the shared filter. LA-8: a non-key column changes (yes or no), 2 paths, one test on product standing for the shared expression. Design D-19, D-20.
LA-9 The five checks; LA-10 coverage and filters (gold, increment 2)
LA-9: the source profile's round one, 80 combinations collapsed to the 14 paths of profile result P-6 (design D-12). LA-10: seven filters, each one document's (license covers the month, Contract Activated, Opportunity won, entitlement covers the month, invoice in the month, invoice not void, latest usage report): brute force 128; each failing filter only removes its own document, so the paths are all passing and each one failing alone: 8 (D-7, D-16). Their tests are named in the path table below (increment 2 guide, knowledge 111 item 816).
Paths
Each logic area states its dimensions, brute-force count, collapse and reason, then provides this table:
| Path | Scenario | Expected result | Unit-test name |
|---|---|---|---|
| 1 | One report | It is kept | ut_usage_report_latest_wins |
| 2 | Two reports, different declared_at | The later is kept | ut_usage_report_latest_wins |
| 3 | Two reports, same declared_at | The higher declaration_id is kept | ut_usage_report_latest_wins |
| 4 | Subscriber and product both on the platform | subscriber_hk and product_hk equal the platform's own | ut_license_line_conformed_keys |
| 5 | Subscriber not on the platform | subscriber_hk has no match in silver subscriber | relationships test on license_line.subscriber_hk |
| 6 | Product not on the platform | product_hk has no match in silver product | relationships test on license_line.product_hk |
| 7 | Neither on the platform | Both relationships tests fail | the two relationships tests above |
| 8 | Whole chain present | The line item is kept | ut_license_line_chain |
| 9 to 12 | Opportunity, Contract, Account or Product2 missing alone | The line item is dropped | ut_license_line_chain |
| 13 to 15 | Invoice later, item later, or equal | updated_at is the later, or either when equal | ut_invoice_item_updated_at |
| 16, 17 | Item's product found, or not | product_hk set, or null and the not_null test fails | ut_invoice_item_updated_at; not_null on invoice_item.product_hk |
| 18, 19 | Entitlement's product found, or not | product_hk set, or null and the not_null test fails | not_null on entitlement.product_hk |
| 20, 21 | Row deleted at the source, or not | Absent from silver, or present | ut_subscriber_deleted_excluded |
| 22, 23 | Non-key column changed, or not | A new change_hash, or the same | ut_product_change_hash |
| 24 to 37 | The 14 paths of P-6 | Exactly the answer key's checks and dollars per path | assert_checks_match_answer_key on the loaded rows; per check, ut_check_reported_beyond_licensed, ut_check_entitled_beyond_licensed, ut_check_billed_without_entitlement, ut_check_entitled_without_billing, ut_check_entitled_beyond_reported |
| 38 to 45 | All filters pass; each of the seven fails alone | Only the failing filter's document is absent | ut_license_line_month_filters (coverage, Activated, won), ut_entitlement_month_coverage, ut_invoice_item_month_filters (month, void); the latest report by ut_usage_report_latest_wins |
| 46 to 49 | Reported only, licensed only, equal, two license lines (D-22) | Each row keeps its own two sides; no netting | ut_subscriber_product_month_two_sides |
| 50 to 53 | Paths PA15 to PA18, October 2026 (D-23) | Exactly the answer key's checks and dollars per path | assert_checks_match_answer_key on the loaded rows |
| 54 | Every path's subscriber, November 2026 to September 2027, units from a fixed seed (D-23) | Exactly the answer key's checks and dollars per path and month | assert_checks_match_answer_key; generator/run.py verify, month by month |
| 55 | Every month, November 2026 to September 2027: subscriber 43's four unit counts and the five checks' counts (D-24) | All different within the month; the checks and dollars exactly the answer key's | generator/phase_one_c.py all_differ, per month, run by its summary; generator/run.py verify, month by month; assert_checks_match_answer_key; read live in the Hex app |
2.1 Coverage by changed file, all seven layers
Every code file the branch changes or adds, from git diff --name-only against its base at a stated clock, one row per file: every layer filled with its named artifacts, or "n/a: reason" (COVERAGE: EVERY FILE, EVERY LAYER, K#2 I#226). The row count equals the diff's; existing tests on a changed file are kept and named beside the new.
| File | Change | 1 Built-ins | 2 Expectations | 3 Unit tests | 4 Logic assertions | 5 Detectors | 6 Reconciliations | 7 Semantic surface |
|---|---|---|---|---|---|---|---|---|
| macros/business_key.sql | Changed: keys of several parts | n/a: a macro, proved through the models that call it | n/a: no distribution to expect (design 8) | Every silver unit test calls it | n/a: no logic beyond concatenation | n/a: design 8, no detectors | n/a | n/a: increment 3 |
| models/staging/_sources.yml | Changed: 11 tables added | not_null on each source key | n/a: design 8 | n/a: declarations | n/a | n/a | rec_landing_matches_sql_server | n/a |
| models/staging/stg_databp__client.sql and its yml | Changed: hashing moves to silver (D-20) | Its tests move to silver client | n/a: design 8 | n/a: renames only | n/a | n/a | assert_silver_counts_match_staging | n/a |
| models/staging/stg_databp__*.sql, 11 new, with their yml | Added: rename, cast, deleted flag | Keys not_null | n/a: design 8 | n/a: renames only | n/a | n/a | assert_silver_counts_match_staging | n/a |
| models/silver/*.sql, 7 new, with _silver.yml and _silver_unit_tests.yml | Added: the seven silver tables (D-19) | 34: keys not_null and unique (14), foreign keys not_null (10), relationships (10) | n/a: design 8 | 6 unit tests, LA-1 to LA-8 | 3 assertions | n/a: design 8 | assert_silver_counts_match_staging | n/a: no metrics yet |
| models/marts/client_dim.sql and yml, renamed from dim_client (D-21) | Changed: reads silver client | not_null and unique on client_hk, kept | n/a: design 8 | n/a: a pass-through | n/a | n/a | count equals silver client | client_count, same value before and after |
| dbt_project.yml | Changed: silver folder and schema | n/a: configuration | n/a | n/a | n/a | n/a | n/a | n/a |
| tests/*.sql, 4 new | Added: singular tests | n/a | n/a | n/a | the 3 assertions | n/a | the count reconciliation | n/a |
| generator/reconcile_landing.py | Added: the landing reconciliation | n/a: a script | n/a | n/a | n/a | n/a | rec_landing_matches_sql_server itself | n/a |
| README.md | Changed: the layers named | n/a: documentation | n/a | n/a | n/a | n/a | n/a | n/a |
| models/marts/subscriber_dim and product_dim, .sql and .yml, 4 new | Added: dimensions read from silver (D-21) | not_null and unique on each key | n/a: design 8 | n/a: pass-throughs | n/a | n/a | n/a: counts equal silver by construction | n/a: entities only | | models/marts/license_line_month_metric, entitlement_month_metric, usage_report_metric, invoice_item_metric, .sql and .yml, 8 new | Added: atomic facts (design 6.1, silver to gold) | Keys not_null and unique; foreign keys not_null | n/a: design 8 | ut_license_line_month_filters, ut_entitlement_month_coverage, ut_invoice_item_month_filters | n/a | n/a | assert_subscriber_product_month_reconciles reads each | licensed_units, entitled_units, reported_units, billed_units, billed_amount | | models/marts/subscriber_product_month_metric.sql and .yml, 2 new | Added: the nested-aggregation exception (D-22) | Key not_null and unique; foreign keys and unit_price not_null | n/a: design 8 | ut_subscriber_product_month_two_sides and the five ut_check_* | assert_checks_match_answer_key | n/a | assert_subscriber_product_month_reconciles | The eleven metrics, results in 3.1 | | models/marts/_marts_unit_tests.yml, 1 new | Added: nine unit tests | n/a | n/a | The nine tests themselves | n/a | n/a | n/a | n/a | | tests/assert_checks_match_answer_key.sql and assert_subscriber_product_month_reconciles.sql, 2 new | Added: singular tests | n/a | n/a | n/a | The answer-key test | n/a | The reconciliation | n/a | | generator/phase_one_b.py, 1 new | Added: the amendment of phase one (D-23) | n/a: a script | n/a: design 8 | n/a: its checks are computed from the units, never typed | generator/run.py verify, month by month, against the answer key | n/a: design 8 | n/a | n/a | | generator/run.py and generator/phase_one.py | Changed: the acts amend and key; verify month by month, its query taking a month (D-23) | n/a: scripts | n/a | n/a | generator/run.py verify itself | n/a | n/a | n/a | | seeds/seed_reconciliation_answer_key.csv | Changed: 216 rows, 18 paths by twelve months (D-23) | n/a: a seed | n/a | n/a | assert_checks_match_answer_key reads it | n/a | n/a | Each metric per month against the key's totals | | README.md | Changed: the amendment named (D-23) | n/a: documentation | n/a | n/a | n/a | n/a | n/a | n/a |
| generator/phase_one_c.py, 1 new | Added: the second amendment, no repeated figure within a month (D-24) | n/a: a script | n/a: design 8 | n/a: its checks are computed from the units, never typed | all_differ per month; generator/run.py verify, month by month, against the answer key | n/a: design 8 | n/a | n/a | | generator/run.py | Changed: the act amend2; key written from phase_one_c.py (D-24) | n/a: a script | n/a | n/a | generator/run.py verify itself | n/a | n/a | n/a | | seeds/seed_reconciliation_answer_key.csv | Changed: 5 of 216 rows, from phase_one_c.py (D-24) | n/a: a seed | n/a | n/a | assert_checks_match_answer_key reads it | n/a | n/a | Each month's totals against the key's | | README.md | Changed: the second amendment named (D-24) | n/a: documentation | n/a | n/a | n/a | n/a | n/a | n/a |
Pull request 6, branch rayserrano2735/dig-655-no-ties: git diff --name-only 596f2c7...HEAD, 4 files, every one in a row above.
Pull request 4, branch rayserrano2735/dig-655-phase-one-amendment: git diff --name-only fc934f7...78b32e0, 2026-11-20 11:34 UTC, 5 files, every one in a row above.
Brought to git diff --name-only origin/main...HEAD at commit c473306, 2026-11-20 01:48 UTC: 61 files, every one in a row above (the 24 staging files in two rows, the 9 silver files in one, the 17 gold files in five). dbt_project.yml's row also covers the dbt-cloud project-id the dbt sl commands need. Increments 3 and 4 add no model files.
3. Seven-layer proof plan
| Layer | Named artifacts | Expected result | Applicability and reason |
|---|---|---|---|
| 1 — Built-ins | 34 silver tests: 7 primary keys not_null and unique, 10 foreign keys not_null, 10 relationships; client_dim's 2 and the source's 2 kept; the 11 new source keys not_null; each of the 12 staging views' key not_null | All pass | Applies: every new table has keys |
| 2 — Expectations | None | None | NA: no distribution is known before more phases land (design 8, layer 2) |
| 3 — Unit tests | ut_usage_report_latest_wins, ut_license_line_conformed_keys, ut_license_line_chain, ut_invoice_item_updated_at, ut_subscriber_deleted_excluded, ut_product_change_hash; gold's ut_check_reported_beyond_licensed, ut_check_entitled_beyond_licensed, ut_check_billed_without_entitlement, ut_check_entitled_without_billing, ut_check_entitled_beyond_reported, ut_subscriber_product_month_two_sides, ut_license_line_month_filters, ut_entitlement_month_coverage and ut_invoice_item_month_filters in increment 2 | Written and seen failing before the model exists, then passing | Applies: LA-1 to LA-10 |
| 4 — Logic assertions | assert_invoice_amount_equals_items, assert_contract_tenant_matches_account, assert_opportunity_tenant_matches_contract; assert_checks_match_answer_key in increment 2 (D-8) | Zero rows returned | Applies: the profile's checks (design 8, checks from the profile) |
| 5 — Detectors | None | None | NA: the demo runs no detectors (design 8, layer 5) |
| 6 — Reconciliations | rec_landing_matches_sql_server: each landed table's count per tenant against SQL Server's through the agent function; assert_silver_counts_match_staging: each silver table's count against its staging rows not deleted, distinct keys for usage_report; assert_subscriber_product_month_reconciles: the exception's licensed, entitled, reported and billed units and billed amount per month against the four document tables, and each row's sides (D-22) | The two sides agree | Applies: three source systems through one connector |
| 7 — Semantic surface | Increment 3: each of the eleven metrics on subscriber_product_month_metric read for October 2026 through dbt sl query and through dbt's MCP, the route Claude uses, each against the answer key's total; client_count read before and after the merge |
The same value by both routes | Applies from increment 3; in increment 1 only client_count |
3.1 Layer 7 results, development
Read with dbt sl query, grouped by month, October 2026, at 2026-11-20 01:49 UTC, against the totals computed from the generator's paths and the answer key (generator/phase_one.py, the same run): all eleven equal.
| Metric | Semantic Layer | Expected |
|---|---|---|
| units_reported_beyond_licensed | 24 | 24 |
| units_licensed_not_reported | 22 | 22 |
| units_entitled_beyond_licensed | 26 | 26 |
| units_entitled_beyond_reported | 30 | 30 |
| unbilled_revenue | 1,120 | 1,120 |
| revenue_at_risk | 1,440 | 1,440 |
| disagreements_reported_beyond_licensed | 7 | 7 |
| disagreements_entitled_beyond_licensed | 6 | 6 |
| disagreements_billed_without_entitlement | 2 | 2 |
| disagreements_entitled_without_billing | 6 | 6 |
| disagreements_entitled_beyond_reported | 6 | 6 |
The second route, dbt's MCP, reads Production's Semantic Layer. After the merge (main d0757c4) and the job "DataBP build" (run [id], success 2026-11-20 02:25:50 UTC, after Ray's grants to DATABP_DBT_RL on DATABP_SFDC_DBO and DATABP_USAGE_DBO), it returned the same eleven values at 02:26 UTC. The Hex app DataBP Reconciliation, on the same Semantic Layer, returned them again at 02:28 UTC: subscriber 43's case (0 licensed, 12 reported, 600 dollars unbilled), October's totals, and 13 subscribers and products with a disagreement, the answer key's paths PA2 to PA14.
3.2 Full-build guardrails
| Artifacts | Expected result |
|---|---|
| dbt build of the whole project in development, target claude, schemas DBT_CLAUDE_* | No error and no test failing |
Before every pull request: the Production role, DATABP_DBT_RL, can read every source schema the project declares, shown by SHOW GRANTS TO ROLE DATABP_DBT_RL, and to each role it holds, naming USAGE on each schema in models/staging/_sources.yml (DATABP_SRC_DBO reaches it through DATABP_LAB_READ_RL, read 2026-11-20 04:43 UTC) (Ray, 2026-11-19 21:24 ET, answer 2y, after Production's first build of DIG-655 failed on DATABP_SFDC_DBO and DATABP_USAGE_DBO, which development's role could read) |
Every declared schema listed with USAGE; a missing one stops the pull request |
| After every change to an embedded Hex app or its page: the live page read in Chrome, the frame's width and height measured and each figure compared with the answer key (Ray's 1y, 2026-11-19 22:25 ET, after the site's own checks passed while the frame rendered 300 pixels wide) | The frame at 960 pixels; every figure equal to the answer key |
| The structure comparison: each committed DDL file, one per data model the branch maintains, compared with the structure the build left in that environment, after every build in dev, QA and production (Ray, 2026-11-01) | Identical, keys and descriptions included; a difference fails the cycle |
4. Consumer-question probes
| Question | Runnable probe | Expected result |
|---|---|---|
| Q-1 | Increment 4: the Hex app filtered to subscriber 83, path PA5, against the answer key's row for it | The four checks of PA5 and no others |
| Q-2 | Increment 4: change one entitlement's units in SQL Server through the agent function, let the connector and the build run, read the app's figure | The figure moves by the changed units times the price |
4.1 Finance workbook and initial UAT proofs
For finance reporting branches, name row/key/value parity checks against the shared report query, totals at the declared grain, Excel types/date/rounding checks and refresh preservation of consumer observations. State consumer-review cases, dispositions and acceptance evidence for the exact workbook revision under K#12 I#27. Technical verification does not imply consumer acceptance. Otherwise state NA with a reason.
NA: not a finance reporting branch.
5. QA on production-shaped data
5.1 Added and changed tests on QA data
| Test | Expected result |
|---|---|
| Every test of section 3 | DataBP has no QA environment: dbt platform holds Development and Production only (tutorial, knowledge 111 item 463, steps 15 and 21), and both read the same landing tables in DATABP_LAB, so development runs on the data Production reads. The tests run in development before the merge, then once in Production by the job DataBP build after it. A QA certification document (K#2 I#168) is waived for DataBP (Ray, 2026-11-19, answer 1y), since development and Production read the same landing data |
5.2 Regression against production
The job DataBP build runs the whole project in Production after the merge; client_count read before and after must agree.
5.3 Consumer probes and reconciliations on QA data
Q-1, Q-2 and layer 6 rerun in Production after the merge.
5.4 Layer 7 in the QA collection
The metric battery reruns in Production after increment 3's merge.
6. Test artifact inventory
| Artifact classification | Count | Sections that make up the count |
|---|---|---|
| Built-in tests, layer 1 | 61 | 3: 34 silver, 11 new source keys, 12 staging keys, 4 kept; with the 4 singular tests, the 65 tests dbt ran at 22:28 UTC |
| Unit tests, layer 3 | 15 | 2 and 3: 6 in increment 1, 9 in increment 2 |
| Logic assertions, layer 4 | 4 | 3: 3 in increment 1, 1 in increment 2 |
| Reconciliations, layer 6 | 3 | 3 |
| Semantic verifications, layer 7 | client_count now; 22 in increment 3, eleven metrics by two routes | 3 |
| Consumer probes | 2 | 4 |
7. Origin
| Test or proof | Design decision proved |
|---|---|
| ut_usage_report_latest_wins | D-2 |
| ut_license_line_conformed_keys, relationships tests | D-19 |
| ut_license_line_chain | D-7 |
| ut_invoice_item_updated_at | D-17 |
| ut_subscriber_deleted_excluded, ut_product_change_hash | D-19, D-20 |
| assert_invoice_amount_equals_items | D-15 |
| assert_contract_tenant_matches_account, assert_opportunity_tenant_matches_contract | D-1 |
| client_dim tests, client_count before and after | D-21 |
| ut_check_* (five), assert_checks_match_answer_key | D-2, D-8, D-10, D-12 |
| ut_license_line_month_filters, ut_entitlement_month_coverage, ut_invoice_item_month_filters | D-7, D-16 |
| ut_subscriber_product_month_two_sides, assert_subscriber_product_month_reconciles | D-22 |
| rec_landing_matches_sql_server, assert_silver_counts_match_staging | D-3, D-4 |
| generator/run.py verify month by month; assert_checks_match_answer_key over twelve months | D-8, D-23 |
| generator/phase_one_c.py all_differ per month | D-24 |
This plan cites the design's decisions and adds none. A test requiring a missing decision returns to the design.
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-19 22:36 UTC on version 9. Template sections, by infra/workstation/pilot/compliance_sections.py against Template: Branch Delivery — Test Plan (knowledge 12 item 177): all 15 present and filled; 0 placeholders left; 0 sections missing.
| Requirement (Standard: Branch Delivery Documents, knowledge 12 item 27, the Test Plan's required sections) | Passage that meets it |
|---|---|
| The official header | The field table, Status v1 DRAFT with its write time |
| Executive summary, one row per model with business logic | Section 1: six rows, 243 brute-force paths, 45 after collapse |
| Logic areas with dimensions, brute force, collapse and reason, and a path table naming each test | Section 2: LA-1 to LA-10 and the path table, 45 paths, each with its test |
| Every changed file across the seven layers | Section 2.1: 44 files at commit 82fcc72, grouped in 10 rows |
| The seven layers walked, NA with reason | Section 3, all seven; layers 2 and 5 NA with the design's reason |
| One runnable probe per consumer question | Section 4: Q-1 and Q-2; runnable from increment 4, once the app exists. Partly met until then |
| QA: the Branch QA Test and Certification copy before the pull request | Waived for DataBP (Ray, 2026-11-19, answer 1y): no QA environment exists, and development and Production read the same landing data (section 5.1) |
| Artifact inventory agreeing with sections 3 to 6 | Section 6: 61 built-ins, 8 unit tests, 4 assertions, 2 reconciliations, the semantic and probe rows |
| Origin: each test's design decision | Section 7 |
| Checks as source query against target query, never an expected count | Layer 6 pairs SQL Server with Snowflake and staging with silver; no count is a pass condition |
| Unit tests written and run before the behavior they prove | Section 3, layer 3: each failed against a version without its logic at 22:27:07 UTC, then passed at 22:27:26 UTC |
| Agreed before any code | Written 22:23 UTC; the first model code ran at 22:26 UTC; agreement by Ray's pre-authorization of 16:06 ET once this section passes |
Decisions carried: D-1, D-2, D-3, D-4, D-7, D-8, D-12, D-15, D-16, D-17, D-19, D-20, D-21, each in section 7 with its tests; D-8 and D-12 by increment 2's tests.
Verdict: compliant, with the QA requirement waived by Ray and the consumer probes runnable from increment 4.