DataBP POC
DataBP POC / Documents / Test plan

DIG-655 Test Plan: Reconciliation dashboard build

Every logic path and the test that proves it, written before the code.

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.