DataBP POC
DataBP POC / Documents / Design document

Entitlement and billing reconciliation: design document

The decisions, D-1 to D-24, each with its reason and evidence.

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

Design Document

Branch Delivery Document — Level 3 — Design Document

Effort DIG-650 — The DataBP demo: the north-star Hex product — branch main
Governed by Standard: Branch Delivery Documents (K#12 I#27)
References Profiling: not yet done, and the next step. In this product-led demo the three source systems do not exist yet, so profiling decides which source data must be created to drive the app: tables, columns, rows and planted cases, generated to this design's specification (Ray, 2026-11-19 10:02 ET; as the DigestCards design states for a system with no source). After it: expand this design, write the implementation guide, then build in the development environment — related records: the design sketch (knowledge 111 item 805); the POC's notes on DataBP (knowledge 111 item 460); the lab design (knowledge 111 item 459); the demo build tutorial (knowledge 111 item 463); the DataBP project setup (knowledge 111 item 802); Linear DIG-650 and DIG-628
Status v1 — DRAFT — written 2026-11-19 17:59Z (compliance record not built; not presented for agreement)
Audience Ray Serrano, who decides; the session that writes the implementation guide and builds the sources, models and app
Author Ray Serrano; edited by Claude — 2026-11-18
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

0. Target ERD

Not drawn yet. It is drawn as SVG, in crow's-foot notation, from the source-to-target map in section 6.1 once that map is agreed, since the standard writes the map first and draws the diagram from it.

1. Purpose

This design decides the end product of the DataBP demo and works backwards from it to the data it needs: entitlement and billing reconciliation, a Hex app named for its question with the London Stock Exchange's term for the same check on the customer's side (course log, knowledge 111 item 798, entry 321), for one of DataBP's clients, a data provider, that answers whether the provider's records about each subscriber agree, and what changed since the last month-end close. The problem it serves is DataBP's own headline: "Less than half of contracted market data revenue is ever realized", drained by leakage, errors and audits that recover only a fraction of what they cost (databp.com, "The Problem" and "Resolve Revenue Leaks", read 2026-11-19; DataBP calls the figure an estimate and names no source). Ray, 2026-11-19: "the whole point here ... we must hone in on it." Each of the app's five checks is aimed at one of those causes (D-10, R-5). It is written for Ray, who decides, and for the session that builds it; its audience in the demo is Bin Luo, DataBP's CTO.

2. Consumer questions

2.1 Questions from consumers

ID Asked by Asked at Origin Question in the asker's words Answer Decision(s) Test probe State and reason
Q-1 Ray Serrano 2026-11-18 17:00 ET DataBP session "come up with a solid hex real-life useful app that targets databp business context, and we will work our way backwards from there, expanding the source" A reconciliation app for a data provider's data business team: license, entitlement, usage report and invoice per subscriber, and what changed since the last close D-1, D-2, D-3 P-1 Answered in design; proven at testing
Q-2 Ray Serrano 2026-11-18 17:56 ET DataBP session "determine the most effective way to present which data analysis will be compelling to Bin, DataBP's CTO" (wording edited for the site: Ray, 2026-11-20, answer 2y) Present one subscriber whose records disagree, widen to the total, then change a source row live and show the figure move, and the same figure through Claude D-2, D-9 P-2 Answered in design; the audience's own preference is unknown

2.2 Questions to consumers

ID Asked of Raised by and when Question Decision blocked Interim rule Measured stakes and clock State or answer
C-1 Bin Luo, CTO of DataBP Ray Serrano, 2026-11-18 What would he most want to see: which of DataBP's own reports or pains the demo should speak to The order of the presentation steps The presentation of D-9 Unknown; not measurable before the CTO round Open; to be asked in the CTO round if the moment allows

2.3 Questions to source owners

ID Asked of Raised by and when Question Decision blocked Interim rule Measured stakes and clock State or answer
None None: the sources are defined by this design None None None None None None

3. Concept

The product is a reconciliation with memory. A data provider, such as an exchange, licenses its data to subscribers, firms that use the data and pay for it. Four records describe each subscriber: the license (the contract: what it bought and how many units), the entitlement (what the platform lets it receive), the usage report (what it reports it used each month) and the invoice (what it was billed). They should agree. The app names each place they do not, with its dollar amount, and shows what changed since the last month-end close, which only kept history can show.

4. Data reality

Profile result Measured fact Design consequence
R-1, read 2026-11-18 SQL Server DATABP_SRC holds client (20 rows), customer (5,000) and invoice (20,000), with Change Tracking on, current version 29 The tenant and the subscriber exist; product, license, entitlement and usage do not
R-2, read 2026-11-18 invoice carries tenant_id, invoice_id, customer_id, amount, invoice_date, status, updated_at; no product Invoice items with a product are needed (D-6)
R-3, read 2026-11-18 Only client is landed in Snowflake (20 rows, level with the source) The connector's table list grows with each new table (D-4)
R-4, read 2026-11-18 Exchange policies define subscriber entitlement, unit of count and usage reporting; trade press reports audits finding under-reported usage The reconciliation is the industry's audit question (DD-2 for its terms)
R-5, read 2026-11-19 DataBP: less than half of contracted revenue realized, an unsourced estimate. Experts: 50 to 70% of exchange audits find some under-reporting (WatersTechnology, "Taking Control"); exchange executives: amounts not unusual (WatersTechnology, "Audits Are Last Resort"); Citigroup paid the LSE 6.4 million pounds for under-reporting (Risk.net) The app prices each disagreement in dollars and adds the under-reporting check (D-10); the demo cites DataBP's figure as DataBP's estimate, never as a measured fact

5. Decisions

Decision ids are D-1, D-2, D-3, numbered in order within the document.

ID Decision Rationale Supporting evidence
D-1 The viewer is one data provider's data business team, seeing only its own subscribers DataBP's clients are data providers; each sees only its own rows DataBP's client types and named clients (DataBP's site, read 2026-11-18)
D-2 The question: do license, entitlement, usage report and invoice agree per subscriber and product, and what changed since the last close Arithmetic on the provider's own record types can be checked by the audience; a claim about the industry cannot Bin Luo's background (knowledge 111 item 460); DataBP's reporting page names usage and entitlement data
D-3 Three source systems, each its own namespace: the DataBP platform (DATABP_SRC), Salesforce (DATABP_SFDC) and usage reporting (DATABP_USAGE) Data integration is shown where the three systems' keys meet Ray, 2026-11-18 20:55 ET
D-4 All three are SQL Server databases, landed by the one SQL Server CT connector already running No new connector at the Openflow level is needed to show data integration Ray, 2026-11-18 20:55 and 20:57 ET: "we don't have to demonstrate that we can use more connectors"
D-5 Salesforce tables use Salesforce's standard field names: Account, Product2, Opportunity, OpportunityLineItem, Contract, with the custom fields Tenant__c and Subscriber_Id__c The data looks like a real Salesforce extract Salesforce object reference, read 2026-11-18; DIG-650: Salesforce data loaded into SQL Server
D-6 The invoice is split into header and items, each item carrying its product The action happens at item level (the parent-item pattern) R-2
D-7 A license is a Contract with the line items of its won Opportunity; licensed units are the line item's Quantity The license is the contract, as the industry's license agreement Salesforce object reference; Opportunity.ContractId, Salesforce's standard field for the contract associated with an opportunity, joins the two (source profile, knowledge 111 item 813, P-2; Ray, 2026-11-19, answer 1y)
D-8 Disagreements are planted on purpose and written to an answer key; a dbt test requires the reconciliation to find exactly the answer key's rows The figure is proven before it is shown The design sketch (knowledge 111 item 805), section 8.5
D-9 The presentation runs one case, the total, who and where, since the last close, then live and through Claude One figure the audience can trace to rows is worth more than many charts Bin Luo's background (knowledge 111 item 460)
D-10 A fifth classification, entitled beyond reported: a usage report counting fewer units than the entitlement allows Under-reporting is the leakage exchange audits most often find; the four other checks never compare usage report with entitlement DataBP's homepage, "The Problem" (read 2026-11-19); WatersTechnology, "Taking Control": experts estimate 50 to 70% of exchange audits find some under-reporting; Ray, 2026-11-19 09:42 ET
D-11 The platform's product carries product_code, the code Salesforce's Product2.ProductCode and the usage report carry Without a shared code no column joins Salesforce's products, or the usage report's, to the platform's, so a license or a usage report cannot be compared with an entitlement or an invoice item by product Source profile, knowledge 111 item 813, P-1; Ray, 2026-11-19, answer 2a
D-12 An absent document counts as zero units in every check: no license is zero licensed units, no entitlement zero entitled units, no usage report zero reported units Each check counts units beyond the other document; a missing usage report is the furthest case of under-reporting, the leakage exchange audits find most (R-5); without it 12 of the reconciliation's 30 outcomes are undecided Source profile, knowledge 111 item 813, P-6; Ray, 2026-11-19, answer 1y
D-13 The source data is generated in phases; phase one holds the source profile's 14 round-one paths for one data provider and one month, and each later phase adds one round of the method; the product build starts once phase one is measured, and later phases expand the source alongside it, not ahead of it (Ray, 2026-11-19 15:24 ET: "I think the decision to populate the source in phases paid off. Now, while we start working on the dashboard, we can expand [the] source, instead of waiting to be completely done with the source to start with the product"; answer 1y) The data comes first and is queryable, so the design is checked against it while it is written; a small first phase starts the process Ray, 2026-11-19 12:56 ET and answer 3y; Profiling standard, section 1, two paths
D-14 The generator and its answer key live in Integrationals/databp-dbt, on DIG-654's branch The answer key is the expected result of the dbt test that D-8 requires, so it sits beside the models Ray, 2026-11-19, answer 4a
D-15 Today's 20,000 invoices are kept; each phase adds its own invoices, each amount the sum of its items Phase one stays small and leaves the existing rows untouched Ray, 2026-11-19, answer 5a
D-16 Billing counts only the items of invoices whose status is not void; an invoice with no items is not billing A voided invoice cancels its charge, so counting it would hide entitled without billing, the leakage the app shows; an invoice with no items names no product, so no check can use it Source profile, knowledge 111 item 813, P-15; Ray, 2026-11-19, answer 1y
D-17 What changed since the last close is read from each source table's updated_at, the time the source system last changed the row The source states when it changed a row, in every source system the same way, and the design reads it as the source's own fact rather than the pipeline's Source profile, knowledge 111 item 813, P-16; Ray, 2026-11-19, answer 3b
D-18 The demo integrates by medallion layers: bronze, the connector's copies as they land; silver, one cleaned table per source document with its keys conformed across the three systems; gold, the mart. The UDM is the integration layer of a production data platform, and the presentation names it so Ten or more UDM tables would show one answer the mart gives in four, and a demo must read easily; saying where the UDM belongs shows the choice is deliberate Silver and gold as Databricks defines them (medallion architecture, read 2026-11-19); Ray, 2026-11-19 15:44 and 15:46 ET: "we can say for demo we decided not to go with UDM but that it is the real integration for a production data platform"; answer 3y
D-19 Silver holds client, subscriber, product, license_line, entitlement, usage_report and invoice_item; each row's key is its business key qualified by the namespace of the party that issued it, hashed with md5, and each row carries a change hash Salesforce's Subscriber_Id__c and the usage report's product_code are the platform's identifiers, so hashed in the platform's namespace they give the same keys as the platform: the integration, visible in one key; client is the data provider every other silver table belongs to Candidate pattern: qualify a business key by its namespace (knowledge 34 item 268); the reference platform's integrators (knowledge 38 item 374); Ray, 2026-11-19 15:55 ET and answers 1y, and 1y at 16:06 ET adding client
D-20 Key hashing moves from staging to silver: staging keeps renames, casts and the deleted flag, and the business_key macro gains a form for keys of several parts Staging stays one view per source table; the keys are made where the systems meet Ray, 2026-11-19 15:55 ET and answer 2y
D-21 Mart tables are named by suffix: [grain]_metric for a table at a grain, [subject]_dim for a table of qualifiers; dim_client becomes client_dim The standard is the suffix The metric-era mart (knowledge 36 item 309); Ray, 2026-11-19, answer 4a: "the standard is the suffix for _metric and _dim"
D-22 subscriber_product_month_metric holds one row per subscriber, product and month with a license, an entitlement, a usage report or a billed invoice item, each row carrying its own result for the five checks and the two dollar figures (increment 2 guide, knowledge 111 item 816); MetricFlow sums each as a separate total A difference taken after summing lets shortfalls cancel excesses: October 2026 read 2 units, against 24 reported beyond licensed and 22 licensed but not reported (dbt sl query, 2026-11-20 00:56 and 01:15 UTC); per Data Mart Design Principles (knowledge 12 item 104), a per-row reduction feeding a cross-row total is the registered nested-aggregation exception, single-sourced in this table, unit-tested by ut_subscriber_product_month_two_sides, reconciled at layer 6 by assert_subscriber_product_month_reconciles, and retired when MetricFlow can sum a difference taken per subscriber, product and month across those groups in one metric Data Mart Design Principles (knowledge 12 item 104), the exception charter; Ray, 2026-11-19 20:08 ET: "Those are two separate totals", and 20:12 ET: "build the correct atomic table and let mf aggregate"; answers 1y and 2y
D-23 Phase one is amended outside the coverage method's rounds (generator/phase_one_b.py): path PA3, subscriber 43, takes distinct October units, entitled 14, reported 17 and billed 9, with its checks unchanged; four October paths, PA15 to PA18, give the five checks different counts, 8, 7, 3, 6 and 9; and the 18 paths' subscribers get usage reports and invoices for November 2026 through September 2027, the term of their licenses and entitlements, with units drawn from a fixed seed and a report or an invoice sometimes missing. The answer key covers all twelve months, and the generator's verify derives the five checks month by month The dashboard showed subscriber 43's units as 12 four times and three checks at 6 disagreements each, and after October every month failed only on usage reports and invoices never generated, so a month picker would show months no data provider would see. A round of the method adds paths for behaviors, not variety in values, so this is an amendment, not a round; the 200-subscriber phase two stays a later phase (D-13). Billed units enter no check, so they vary freely The Hex app DataBP Reconciliation read live in the browser pane, 2026-11-20 about 05:15 UTC; November 2026 through dbt's MCP, about 05:24 UTC: 6,020 dollars at risk and 12 entitled without billing; Ray, 2026-11-20 00:18 ET: "we need more variability in the numbers", answers 1y2y3b4y at 00:32 ET and 1y at 06:32 ET
D-24 Phase one is amended a second time (generator/phase_one_c.py), October unchanged: in every month from November 2026 through September 2027, subscriber 43's four unit counts differ (it always has a usage report and an invoice, reported units other than 0 and 14, billed units other than 0, 14 and the reported units), and the five checks' counts of disagreements differ; where two counts are equal, one path's reported units are redrawn, or, when only invoices move the two equal checks, one invoice the first amendment left absent is supplied. Draws come from their own fixed seed; documents already loaded change by UPDATE, absent ones are inserted, none is removed; the answer key and the generator's verify follow The first amendment removed October's repeated figures only: after October it billed subscriber 43 its reported units, so the two matched every month, and the checks' counts were 7 and 7 in November, 2 and 2 in June, 9 and 9 in August. A constraint checked by the generator fixes every month at once, where another one-month amendment would not The Hex app, published 2026-11-20 about 15:05 UTC, November: subscriber 43 at 10 reported and 10 billed, two checks at 7; Ray, 2026-11-20 10:24 ET: "november is showing equal values like 10 twice in the one case, seven twice in the totals", answer 2y at 10:27 ET; proved in scratch at about 15:30 to 15:40 UTC, 216 of 216 rows, 0 mismatches

6. Domain specification

6.1 source-to-target map

Two hops, as decision D-18 sets: bronze to silver here, and silver to gold, the mart of decision D-21, below it once agreed. Bronze is the connector's copy of each source table in DATABP_LAB, read through one staging view per table that renames, casts and carries the deleted flag (D-20). Silver is seven tables in schema SILVER, one per source document, each named for the thing it holds (Object Naming, knowledge 12 item 147, rules 1 to 3), each keyed by an md5 hash of its business key qualified by its issuer's namespace (D-19).

The keys, defined once and used in the map by name. A business key is the issuer's code, the table's code and the key's parts, joined by a vertical bar; its hash is md5 of that text.

Key Business key Issuer
client_hk DATABP, CLIENT, client_id The DataBP platform
subscriber_hk DATABP, CUSTOMER, tenant_id, customer_id The DataBP platform
product_hk DATABP, PRODUCT, tenant_id, product_code The DataBP platform
license_line_hk SFDC, OPPORTUNITYLINEITEM, Id Salesforce
entitlement_hk DATABP, ENTITLEMENT, tenant_id, entitlement_id The DataBP platform
usage_report_hk USAGE, USAGE_DECLARATION, tenant_id, declaration_id Usage reporting
invoice_hk DATABP, INVOICE, tenant_id, invoice_id The DataBP platform
invoice_item_hk DATABP, INVOICE_ITEM, tenant_id, invoice_id, item_id The DataBP platform

Salesforce's Account.Tenant__c and Subscriber_Id__c, and the usage report's tenant_id and customer_id, are the platform's identifiers, so their subscriber_hk is built in the platform's namespace and equals the platform's own; product_hk likewise from Product2.Tenant__c and ProductCode, and from the usage report's product_code. An entitlement or invoice item names its product by product_id, so its product_hk is built from the product's code, looked up through product. Each silver row also carries change_hash, md5 of its non-key columns, and landed_at, the connector's insert time (its _SNOWFLAKE_INSERTED_AT), as the reference platform's integrators carry their load time; a deleted row at the source is not in silver.

One row per column of the source profile (knowledge 111 item 813, appendix, whose numbers are in the first column): the 63 Integrate columns, then the 7 Reject columns named as absent.

Profile verdict Source concept and column Target area Destination table and column Identity recipe Pattern Additive? Decision Deliberate collapse, omission, or deferral
Integrate, 1 Data provider: client.client_id Silver client client.client_id; client.client_hk client_hk Silver table No D-19 None
Integrate, 2 Data provider: client.client_name Silver client client.client_name client_hk Silver table No D-19 None
Integrate, 3 Subscriber: customer.tenant_id Silver subscriber subscriber.client_hk; part of subscriber.subscriber_hk subscriber_hk Silver table No D-19 None
Integrate, 4 Subscriber: customer.customer_id Silver subscriber subscriber.subscriber_id; part of subscriber.subscriber_hk subscriber_hk Silver table No D-19 Renamed: subscriber is the industry's word
Integrate, 5 Subscriber: customer.customer_name Silver subscriber subscriber.subscriber_name subscriber_hk Silver table No D-19 None
Integrate, 7 Subscriber: customer.updated_at Silver subscriber subscriber.updated_at subscriber_hk Silver table No D-17 None
Integrate, 8 Invoice: invoice.tenant_id Silver invoice_item invoice_item.client_hk; part of invoice_item.invoice_hk invoice_item_hk Silver table No D-6, D-19 Header columns carried on each item
Integrate, 9 Invoice: invoice.invoice_id Silver invoice_item invoice_item.invoice_id; part of invoice_item.invoice_hk invoice_item_hk Silver table No D-6 None
Integrate, 10 Invoice: invoice.customer_id Silver invoice_item invoice_item.subscriber_hk invoice_item_hk Silver table No D-19 None
Integrate, 11 Invoice: invoice.amount Check Equals the sum of its items' amounts, a silver test None Check No D-15 Not carried: invoice_item.billed_amount carries the money
Integrate, 12 Invoice: invoice.invoice_date Silver invoice_item invoice_item.invoice_date invoice_item_hk Silver table No D-6 None
Integrate, 13 Invoice: invoice.status Silver invoice_item invoice_item.invoice_status invoice_item_hk Silver table No D-16 Not filtered here: gold excludes void
Integrate, 14 Invoice: invoice.updated_at Silver invoice_item invoice_item.updated_at, the later of the invoice's and the item's invoice_item_hk Silver table No D-17 Collapsed: the later wins
Integrate, 15 Product: product.tenant_id Silver product product.client_hk; part of product.product_hk product_hk Silver table No D-19 None
Integrate, 16 Product: product.product_id Silver product product.product_id product_hk Silver table No D-11 None
Integrate, 17 Product: product.product_code Silver product product.product_code; part of product.product_hk product_hk Silver table No D-11, D-19 None
Integrate, 18 Product: product.product_name Silver product product.product_name product_hk Silver table No D-11 None
Integrate, 19 Product: product.unit_price Silver product product.unit_price product_hk Silver table No D-10 None
Integrate, 20 Product: product.updated_at Silver product product.updated_at product_hk Silver table No D-17 None
Integrate, 21 Entitlement: entitlement.tenant_id Silver entitlement entitlement.client_hk; part of entitlement.entitlement_hk entitlement_hk Silver table No D-19 None
Integrate, 22 Entitlement: entitlement.entitlement_id Silver entitlement entitlement.entitlement_id; part of entitlement.entitlement_hk entitlement_hk Silver table No D-19 None
Integrate, 23 Entitlement: entitlement.customer_id Silver entitlement entitlement.subscriber_hk entitlement_hk Silver table No D-19 None
Integrate, 24 Entitlement: entitlement.product_id Silver entitlement entitlement.product_hk, through product's code entitlement_hk Silver table No D-11, D-19 Collapsed into product_hk
Integrate, 25 Entitlement: entitlement.units Silver entitlement entitlement.entitled_units entitlement_hk Silver table Yes D-12 None
Integrate, 26 Entitlement: entitlement.start_date Silver entitlement entitlement.start_date entitlement_hk Silver table No D-2 None
Integrate, 27 Entitlement: entitlement.end_date Silver entitlement entitlement.end_date entitlement_hk Silver table No D-2 None
Integrate, 28 Entitlement: entitlement.updated_at Silver entitlement entitlement.updated_at entitlement_hk Silver table No D-17 None
Integrate, 29 Invoice item: invoice_item.tenant_id Silver invoice_item Part of invoice_item.invoice_item_hk; equals the invoice's tenant_id invoice_item_hk Silver table No D-19 None
Integrate, 30 Invoice item: invoice_item.invoice_id Silver invoice_item Joins the item to its invoice; part of invoice_item.invoice_item_hk invoice_item_hk Silver table No D-6 None
Integrate, 31 Invoice item: invoice_item.item_id Silver invoice_item invoice_item.item_id; part of invoice_item.invoice_item_hk invoice_item_hk Silver table No D-6 None
Integrate, 32 Invoice item: invoice_item.product_id Silver invoice_item invoice_item.product_hk, through product's code invoice_item_hk Silver table No D-11, D-19 Collapsed into product_hk
Integrate, 33 Invoice item: invoice_item.units Silver invoice_item invoice_item.billed_units invoice_item_hk Silver table Yes D-16 None
Integrate, 34 Invoice item: invoice_item.unit_price Silver invoice_item invoice_item.billed_unit_price invoice_item_hk Silver table No D-6 None
Integrate, 35 Invoice item: invoice_item.amount Silver invoice_item invoice_item.billed_amount invoice_item_hk Silver table Yes D-6 None
Integrate, 36 Invoice item: invoice_item.updated_at Silver invoice_item invoice_item.updated_at, the later of the invoice's and the item's invoice_item_hk Silver table No D-17 Collapsed: the later wins
Integrate, 37 License: Account.Id Join Joins Contract.AccountId to the Account None Join No D-7 Not carried
Integrate, 40 License: Account.Tenant__c Silver license_line license_line.client_hk; part of license_line.subscriber_hk license_line_hk Silver table No D-19 None
Integrate, 41 License: Account.Subscriber_Id__c Silver license_line Part of license_line.subscriber_hk, in the platform's namespace license_line_hk Silver table No D-19 None
Integrate, 42 License: Product2.Id Join Joins OpportunityLineItem.Product2Id to Product2 None Join No D-7 Not carried
Integrate, 44 License: Product2.ProductCode Silver license_line Part of license_line.product_hk, in the platform's namespace license_line_hk Silver table No D-11, D-19 None
Integrate, 45 License: Product2.Tenant__c Silver license_line Part of license_line.product_hk license_line_hk Silver table No D-19 None
Integrate, 46 License: Contract.Id Silver license_line license_line.contract_id license_line_hk Silver table No D-7 None
Integrate, 47 License: Contract.AccountId Join Joins the Contract to its Account None Join No D-7 Not carried
Integrate, 48 License: Contract.StartDate Silver license_line license_line.contract_start_date license_line_hk Silver table No D-7 None
Integrate, 49 License: Contract.EndDate Silver license_line license_line.contract_end_date license_line_hk Silver table No D-7 None
Integrate, 51 License: Contract.Status Silver license_line license_line.contract_status license_line_hk Silver table No D-7 Not filtered here: gold keeps Activated
Integrate, 52 License: Contract.Tenant__c Check Equals its Account's Tenant__c, a silver test None Check No D-1 Not carried
Integrate, 53 License: Opportunity.Id Silver license_line license_line.opportunity_id license_line_hk Silver table No D-7 None
Integrate, 55 License: Opportunity.ContractId Join Joins the Opportunity to its Contract None Join No D-7 Not carried
Integrate, 56 License: Opportunity.StageName Silver license_line license_line.opportunity_stage license_line_hk Silver table No D-7 Not filtered here: gold keeps Closed Won
Integrate, 58 License: Opportunity.Tenant__c Check Equals its Contract's Tenant__c, a silver test None Check No D-1 Not carried
Integrate, 59 License: OpportunityLineItem.Id Silver license_line Part of license_line.license_line_hk license_line_hk Silver table No D-19 None
Integrate, 60 License: OpportunityLineItem.OpportunityId Join Joins the line item to its Opportunity None Join No D-7 Not carried
Integrate, 61 License: OpportunityLineItem.Product2Id Join Joins the line item to Product2 None Join No D-7 Not carried
Integrate, 62 License: OpportunityLineItem.Quantity Silver license_line license_line.licensed_units license_line_hk Silver table Yes D-7 None
Integrate, 63 License: OpportunityLineItem.UnitPrice Silver license_line license_line.licensed_unit_price license_line_hk Silver table No D-7 None
Integrate, 64 Usage report: usage_declaration.tenant_id Silver usage_report usage_report.client_hk; part of usage_report.usage_report_hk usage_report_hk Silver table No D-19 None
Integrate, 65 Usage report: usage_declaration.declaration_id Silver usage_report usage_report.declaration_id; part of usage_report.usage_report_hk usage_report_hk Silver table No D-19 Collapsed: the latest report per subscriber, product and month is kept
Integrate, 66 Usage report: usage_declaration.customer_id Silver usage_report usage_report.subscriber_hk usage_report_hk Silver table No D-19 None
Integrate, 67 Usage report: usage_declaration.product_code Silver usage_report usage_report.product_hk usage_report_hk Silver table No D-11, D-19 None
Integrate, 68 Usage report: usage_declaration.usage_month Silver usage_report usage_report.usage_month usage_report_hk Silver table No D-2 None
Integrate, 69 Usage report: usage_declaration.declared_units Silver usage_report usage_report.reported_units usage_report_hk Silver table Yes D-10 None
Integrate, 70 Usage report: usage_declaration.declared_at Silver usage_report usage_report.declared_at; orders restated reports usage_report_hk Silver table No D-2 None
Reject, 6 customer.email None Absent None None No None Omitted: not used by the app
Reject, 38 Account.Name None Absent None None No None Omitted: duplicate of customer.customer_name
Reject, 39 Account.OwnerId None Absent None None No None Omitted: not used by the app
Reject, 43 Product2.Name None Absent None None No None Omitted: duplicate of product.product_name
Reject, 50 Contract.ContractTerm None Absent None None No None Omitted: the period is StartDate to EndDate
Reject, 54 Opportunity.AccountId None Absent None None No None Omitted: duplicate of its Contract's AccountId
Reject, 57 Opportunity.CloseDate None Absent None None No None Omitted: not used by the reconciliation

Completion check, bronze to silver: 63 Integrate rows, each landing in a silver column or naming the join or check it serves; 7 Reject rows named as absent; 0 Defer; every silver column traces to a source column above, to a key defined above, to change_hash, or to landed_at.

The silver-to-gold map: eight gold tables in schema MARTS, named by suffix (D-21), read from the seven silver tables above. A _metric table holds row-grain measures and keys at one declared grain; a _dim table holds qualifiers; MetricFlow computes every population total (Data Mart Design Principles, knowledge 12 item 104). A month row is one month a document covers, from the time spine metricflow_time_spine, so every covered month gets a row and an absent month reads 0.

Profile verdict Source concept and column Target area Destination table and column Identity recipe Pattern Additive? Decision Deliberate collapse, omission, or deferral
Integrate Data provider: client.client_hk Gold client_dim client_dim.client_hk client_hk Dimension No D-21 None
Integrate Data provider: client.client_id Gold client_dim client_dim.client_id client_hk Dimension No D-21 None
Integrate Data provider: client.client_name Gold client_dim client_dim.client_name client_hk Dimension No D-21 None
Integrate Data provider: client.landed_at Gold client_dim client_dim.landed_at; client_dim.landed_date, its date client_hk Dimension No D-21 Kept: Production's dim_client carries it (baseline B-1)
Integrate Subscriber: subscriber.subscriber_hk Gold subscriber_dim subscriber_dim.subscriber_hk subscriber_hk Dimension No D-19 None
Integrate Subscriber: subscriber.client_hk Gold subscriber_dim subscriber_dim.client_hk subscriber_hk Dimension No D-1 None
Integrate Subscriber: subscriber.subscriber_id Gold subscriber_dim subscriber_dim.subscriber_id subscriber_hk Dimension No D-19 None
Integrate Subscriber: subscriber.subscriber_name Gold subscriber_dim subscriber_dim.subscriber_name subscriber_hk Dimension No D-19 None
Integrate Subscriber: subscriber.updated_at Gold subscriber_dim subscriber_dim.updated_at subscriber_hk Dimension No D-17 None
Integrate Product: product.product_hk Gold product_dim product_dim.product_hk product_hk Dimension No D-19 None
Integrate Product: product.client_hk Gold product_dim product_dim.client_hk product_hk Dimension No D-1 None
Integrate Product: product.product_id Gold product_dim product_dim.product_id product_hk Dimension No D-11 None
Integrate Product: product.product_code Gold product_dim product_dim.product_code product_hk Dimension No D-11 None
Integrate Product: product.product_name Gold product_dim product_dim.product_name product_hk Dimension No D-11 None
Integrate Product: product.unit_price Gold product_dim product_dim.unit_price product_hk Dimension No D-10 None
Integrate Product: product.updated_at Gold product_dim product_dim.updated_at product_hk Dimension No D-17 None
Integrate License: license_line.license_line_hk Gold license_line_month_metric license_line_month_metric.license_line_hk; with the month, its key license_line_month_hk license_line_hk and usage_month Atomic fact, one row per license line and covered month No D-7, D-21 None
Integrate License: license_line.client_hk Gold license_line_month_metric license_line_month_metric.client_hk license_line_hk and usage_month Atomic fact No D-1 None
Integrate License: license_line.subscriber_hk Gold license_line_month_metric license_line_month_metric.subscriber_hk license_line_hk and usage_month Atomic fact No D-19 None
Integrate License: license_line.product_hk Gold license_line_month_metric license_line_month_metric.product_hk license_line_hk and usage_month Atomic fact No D-19 None
Integrate License: license_line.contract_start_date and contract_end_date Gold license_line_month_metric license_line_month_metric.usage_month, each month from start to end license_line_hk and usage_month Atomic fact No D-7 Collapsed: the period becomes its months
Integrate License: license_line.licensed_units Gold license_line_month_metric license_line_month_metric.licensed_units; metric licensed_units license_line_hk and usage_month Atomic fact Yes D-7, D-12 None
Integrate License: license_line.contract_status and opportunity_stage Gold license_line_month_metric Filter: only Activated Contracts of Closed Won Opportunities None Filter No D-7 Not carried: used as the filter
Integrate Entitlement: entitlement.entitlement_hk Gold entitlement_month_metric entitlement_month_metric.entitlement_hk; with the month, its key entitlement_month_hk entitlement_hk and usage_month Atomic fact, one row per entitlement and covered month No D-21 None
Integrate Entitlement: entitlement.client_hk Gold entitlement_month_metric entitlement_month_metric.client_hk entitlement_hk and usage_month Atomic fact No D-1 None
Integrate Entitlement: entitlement.subscriber_hk Gold entitlement_month_metric entitlement_month_metric.subscriber_hk entitlement_hk and usage_month Atomic fact No D-19 None
Integrate Entitlement: entitlement.product_hk Gold entitlement_month_metric entitlement_month_metric.product_hk entitlement_hk and usage_month Atomic fact No D-19 None
Integrate Entitlement: entitlement.start_date and end_date Gold entitlement_month_metric entitlement_month_metric.usage_month, each month from start to end entitlement_hk and usage_month Atomic fact No D-2 Collapsed: the period becomes its months
Integrate Entitlement: entitlement.entitled_units Gold entitlement_month_metric entitlement_month_metric.entitled_units; metric entitled_units entitlement_hk and usage_month Atomic fact Yes D-12 None
Integrate Usage report: usage_report.usage_report_hk Gold usage_report_metric usage_report_metric.usage_report_hk usage_report_hk Atomic fact, one row per usage report, the latest for its month No D-2, D-21 None
Integrate Usage report: usage_report.client_hk Gold usage_report_metric usage_report_metric.client_hk usage_report_hk Atomic fact No D-1 None
Integrate Usage report: usage_report.subscriber_hk Gold usage_report_metric usage_report_metric.subscriber_hk usage_report_hk Atomic fact No D-19 None
Integrate Usage report: usage_report.product_hk Gold usage_report_metric usage_report_metric.product_hk usage_report_hk Atomic fact No D-19 None
Integrate Usage report: usage_report.usage_month Gold usage_report_metric usage_report_metric.usage_month usage_report_hk Atomic fact No D-2 None
Integrate Usage report: usage_report.reported_units Gold usage_report_metric usage_report_metric.reported_units; metric reported_units usage_report_hk Atomic fact Yes D-10, D-12 None
Integrate Invoice item: invoice_item.invoice_item_hk Gold invoice_item_metric invoice_item_metric.invoice_item_hk invoice_item_hk Atomic fact, one row per invoice item that bills No D-6, D-21 None
Integrate Invoice item: invoice_item.client_hk Gold invoice_item_metric invoice_item_metric.client_hk invoice_item_hk Atomic fact No D-1 None
Integrate Invoice item: invoice_item.subscriber_hk Gold invoice_item_metric invoice_item_metric.subscriber_hk invoice_item_hk Atomic fact No D-19 None
Integrate Invoice item: invoice_item.product_hk Gold invoice_item_metric invoice_item_metric.product_hk invoice_item_hk Atomic fact No D-19 None
Integrate Invoice item: invoice_item.invoice_date Gold invoice_item_metric invoice_item_metric.usage_month, the invoice date's month invoice_item_hk Atomic fact No D-2 Collapsed: the date becomes its month
Integrate Invoice item: invoice_item.billed_units Gold invoice_item_metric invoice_item_metric.billed_units; metric billed_units invoice_item_hk Atomic fact Yes D-16 None
Integrate Invoice item: invoice_item.billed_amount Gold invoice_item_metric invoice_item_metric.billed_amount; metric billed_amount invoice_item_hk Atomic fact Yes D-16 None
Integrate Invoice item: invoice_item.invoice_status Gold invoice_item_metric Filter: void invoices are not billing None Filter No D-16 Not carried: used as the filter
Integrate License and usage report: license_line_month_metric and usage_report_metric, keys and month Gold subscriber_product_month_metric subscriber_product_month_metric.client_hk, subscriber_hk, product_hk, usage_month; key subscriber_product_month_hk subscriber_hk, product_hk and usage_month Exception fact, nested aggregation No D-22 Collapsed: license lines and reports summed per subscriber, product and month
Integrate License: license_line_month_metric.licensed_units Gold subscriber_product_month_metric subscriber_product_month_metric.licensed_units, 0 when absent subscriber_hk, product_hk and usage_month Exception fact Yes D-12, D-22 None
Integrate Usage report: usage_report_metric.reported_units Gold subscriber_product_month_metric subscriber_product_month_metric.reported_units, 0 when absent subscriber_hk, product_hk and usage_month Exception fact Yes D-12, D-22 None
Integrate Entitlement: entitlement_month_metric.entitled_units Gold subscriber_product_month_metric subscriber_product_month_metric.entitled_units, 0 when absent subscriber_hk, product_hk and usage_month Exception fact Yes D-12, D-22 None
Integrate Invoice item: invoice_item_metric.billed_units and billed_amount Gold subscriber_product_month_metric subscriber_product_month_metric.billed_units and billed_amount, 0 when absent subscriber_hk, product_hk and usage_month Exception fact Yes D-12, D-16, D-22 None
Integrate Each document table's row for the subscriber, product and month Gold subscriber_product_month_metric has_license, has_entitlement, has_report, has_billing subscriber_hk, product_hk and usage_month Exception fact No D-2, D-22 The presence the two presence checks test (guide G-10)
Integrate Product: product_dim.unit_price Gold subscriber_product_month_metric subscriber_product_month_metric.unit_price subscriber_hk, product_hk and usage_month Exception fact No D-10 None
Integrate Derived from the units above Gold subscriber_product_month_metric units_reported_beyond_licensed, units_licensed_not_reported, units_entitled_beyond_licensed, units_entitled_beyond_reported; a metric each subscriber_hk, product_hk and usage_month Exception fact Yes D-2, D-10, D-22 None
Integrate Derived from the units and presence above Gold subscriber_product_month_metric is_reported_beyond_licensed, is_entitled_beyond_licensed, is_billed_without_entitlement, is_entitled_without_billing, is_entitled_beyond_reported, 1 or 0; a count of disagreements each subscriber_hk, product_hk and usage_month Exception fact Yes D-2, D-8, D-10 None
Integrate Derived from the units and unit_price above Gold subscriber_product_month_metric unbilled_amount and at_risk_amount; metrics unbilled_revenue and revenue_at_risk subscriber_hk, product_hk and usage_month Exception fact Yes D-10, D-22 None

Completion check, silver to gold: 53 rows, all 70 columns of the eight gold tables in development (counted 2026-11-20 01:47 UTC) traced to a silver column, a key, the time spine, a document's presence or a derivation above. Not carried to gold, on purpose: each silver table's change_hash and landed_at (client's landed_at excepted), license_line's contract_id, opportunity_id and licensed_unit_price, entitlement's entitlement_id, usage_report's declaration_id and declared_at, invoice_item's invoice_id, item_id and billed_unit_price; no check reads them.

6.2 Dictionary

Term Business meaning source-to-target mapping Mapping state Decision Verified example
Market data The prices and trades that financial markets produce, sold by exchanges and data vendors to investment firms; not marketing data, which is about the customers a product is sold to (course log, knowledge 111 item 798, entry 317, from TRG Screen and Law Insider) None: the industry of DataBP's clients, not a column Not applicable D-1 None yet
Subscriber The party a vendor permits to receive market data through a subscriber entitlement (course log, entry 312, from an NYSE filing of 2010-05-12) DATABP_SRC.dbo.customer to the subscriber role PROPOSED D-1 None yet: no rows generated
Vendor Market data vendors, broker-dealers, private network providers and other entities that control subscribers' access to data through subscriber entitlement controls (course log, entry 313, from the same NYSE filing) None: not in this version's sources Not mapped DD-2 None yet
License The right an exchange grants a data recipient in its market data agreement: limited, non-exclusive, non-transferable, revocable, for the agreement's term, to use the exchange's data and distribute it to subscribers who hold a subscriber agreement (course log, entry 318, from the MEMX Market Data Agreement, section 4) Salesforce Contract with its won Opportunity's line items PROPOSED D-7 None yet
Entitlement A vendor's permitting a subscriber to receive access to data through an exchange-approved subscriber entitlement control (course log, entry 314, from the NYSE filing) DATABP_SRC.dbo.entitlement PROPOSED D-3 None yet
Unit of count The unit used to measure the level of use of market data to be invoiced, distinguishing display from non-display use; CME Group's default for display terminals is the device (course log, entry 315, from Nasdaq European Data Policies and CME Group) OpportunityLineItem.Quantity, entitlement.units, usage_declaration.declared_units and invoice_item.units, all counted in one unit of count PROPOSED D-7, DD-2 None yet
Usage report The monthly count of reportable units per information product, per location or subscriber, sent to the exchange (course log, entry 316, from Euronext Reporting Guidelines v1.6, section 2.3) DATABP_USAGE.usage_declaration PROPOSED D-3, DD-2 None yet
Declaration A data recipient's statement of how it uses an exchange's data, such as ICE's annual Non-Display Use Declaration; not a usage report (course log, entry 319, from the ICE Market Data Vendor Guide, section 2.4.1) None: not in this version's sources; the table name usage_declaration holds usage reports, not declarations Not mapped DD-2 None yet
Invoice item One billed line: product, units, unit price, amount DATABP_SRC.dbo.invoice_item PROPOSED D-6 None yet

6.3 Domain content

The source design, presentation steps, metrics and planted disagreements are in the design sketch (knowledge 111 item 805), sections 3, 5 and 8, and move here section by section as each is agreed. Metrics for the Semantic Layer, by their names in the project (increment 2 guide, knowledge 111 item 816): on the document tables, licensed_units, entitled_units, reported_units, billed_units and billed_amount (billed revenue), and client_count on client_dim; on subscriber_product_month_metric (D-22), units_reported_beyond_licensed (units beyond license), units_licensed_not_reported, units_entitled_beyond_licensed, units_entitled_beyond_reported, unbilled_revenue (units reported beyond licensed times the product's unit price), revenue_at_risk (units entitled beyond reported times the product's unit price: the amount an exchange's audit of under-reporting would test, not proven use), and one count of disagreements per check: disagreements_reported_beyond_licensed, disagreements_entitled_beyond_licensed, disagreements_billed_without_entitlement, disagreements_entitled_without_billing and disagreements_entitled_beyond_reported (the under-reporting check of D-10). Dollars exist for the two checks the answer key prices; the other three are counted.

6.4 Instance models

None yet: no source rows exist. An instance model is added for one real subscriber once the generator has loaded the three source systems and the build has run.

6.5 Worked examples

Acme Capital subscribes to a data provider's equities feed. Its license says 10 units. The platform entitles it to 12. Its usage report for October says 14. Its October invoice bills 10. The app shows three facts for Acme Capital: usage beyond the license by 4 units, entitlement beyond the license by 2, and billing that matches the license but not the usage. At the feed's unit price of 50 dollars, 4 units beyond license is 200 dollars unbilled for October.

Acme Capital also subscribes to the provider's options feed. Its license says 8 units and the platform entitles it to 8. Its usage report for October says 5, and its October invoice bills 5. The app shows one fact for this product: entitlement beyond the usage report by 3 units, the under-reporting check of D-10. At the feed's unit price of 40 dollars, that is 120 dollars at risk for October. Across its two products Acme Capital shows three of the five checks; the planted one case carries all five (design sketch, knowledge 111 item 805, section 8.5).

7. Deferred decisions

ID Deferred decision Reason Unblocking condition Owner
DD-1 The UDM destinations of entitlement, usage report and invoice item The UDM model for these three is not yet chosen Withdrawn 2026-11-19: the demo does not integrate into the UDM (D-18) Ray
DD-2 Whether to rename the design's terms to the industry's (unit of count, usage report) and add the vendor between provider and subscriber On hold (Ray, 2026-11-19); until then the dictionary carries the industry's terms and the sources keep their table names Ray lifts the hold Ray

8. Testing plan

Layer Required proof or artifact Applicability and reason
1: built-in dbt tests not_null and unique on every key of the new staging and integration models; relationships from items to their headers Applies: every new model has keys
2: dbt-expectations Not planned in this version NA: no distributional expectation is known before the rows are generated
3: PCM unit tests Unit tests of the reconciliation's five classifications, one mocked case each Applies: the classifications are the app's logic
4: logic assertions The reconciliation finds exactly the answer key's rows (D-8) Applies: the answer key is the expected result
5: detectors Not planned in this version NA: the demo does not run detectors
6: reconciliation Row counts per source table against Snowflake landing, per tenant Applies: three source systems land through one connector
7: semantic surface The disagreement metrics return the same value through the Semantic Layer, Hex and Claude over dbt's MCP Applies: the app's figure is a Semantic Layer metric
Full-build guardrails, not a methodology layer dbt build passes on the full project before the app is refreshed Applies to every build

Consumer-question probes

Question Runnable probe Expected result
Q-1 The app shows each planted disagreement for the one-case subscriber The answer key's rows for that subscriber, and no others
Q-2 A source row changed in SQL Server reaches the app's figure after the connector and the build The figure moves by the changed amount

Checks from the profile

Profile result Check Layer Planned artifact
None: no profile None None None

9. Handoff boundary

The implementation guide covers: the generator and its answer key; the DDL of the new tables in DATABP_SRC, DATABP_SFDC and DATABP_USAGE, with Change Tracking; the connector's table list; the dbt models from staging to the reconciliation; the Semantic Layer metrics; and the Hex app. This design decides what and why; the guide decides how, citing these decision ids. This design stays at the conceptual level; the mart design and the metric-era mart (knowledge 36 item 309, where [grain]_metric is the settled table naming) come after it, at the implementation level, and never replace it (Ray, 2026-11-19: each document type has its own purpose).

Finance workbook and initial UAT

For finance reporting branches, state the workbook's consumer, scope, business questions, expected behavior, preliminary-review conditions and final acceptance criteria under K#12 I#27. Otherwise state NA with a reason.

NA: this is not a finance reporting 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.

NOT BUILT.