DataBP POC
DataBP POC / The build log

The build log

How the reconciliation POC was built, step by step with its commands, and how it is proven.

This POC answers one question for a data provider on DataBP: do my records about each subscriber agree, and what do the disagreements cost? It runs end to end on a production-shaped platform: three source systems in SQL Server, change data capture into Snowflake, dbt models and governed metrics, a Hex app and an analyst agent, Morgan, that answers in plain words. Every figure on the site comes from one place, the dbt Semantic Layer, and every figure is checked against an answer key computed independently of the platform.

# What Evidence
1 Twelve months of reconciliation, 18 subscriber paths a month 216 of 216 rows equal the answer key
2 Source to Snowflake, by change data capture 25,705 rows on each side, 0 differences
3 dbt project, development and Production 137 of 137 models, seeds and tests pass
4 Governed metrics 17 metrics, read the same through the Semantic Layer, Hex and Morgan
5 Delivery Each change from a design decision, a test plan and a reviewed pull request

1. The question, and what the app shows

A data provider licenses its feeds to subscribers. Four documents describe each subscriber, product and month, and they come from three systems: the license (Salesforce), the entitlement and the invoice (the DataBP platform), and the usage report the subscriber declares (usage reporting). The app runs five checks on them:

# Check What it catches
1 Reported beyond licensed Usage the license does not cover: revenue not billed
2 Entitled beyond licensed Access granted past the contract
3 Billed without entitlement An invoice with no entitlement behind it
4 Entitled without billing Access granted and never invoiced
5 Entitled beyond reported Access unused or unreported: revenue at risk

Two dollar figures follow from the units: unbilled revenue (units reported beyond licensed times the unit price) and revenue at risk (units entitled beyond reported times the unit price). The app shows one subscriber's case step by step, the month's totals, and who and where, with a month picker, hover and click on every indicator, and the formula behind every dollar.

2. The platform, layer by layer

# Layer What we built How we know it works
1 Sources SQL Server on AWS RDS: platform, Salesforce and usage-reporting databases, Change Tracking on every table Rows read back through a secured function; schema and joins profiled
2 Ingestion Snowflake Openflow, bring-your-own-cloud, in our AWS VPC; SQL Server connector Row counts per table and tenant equal SQL Server's
3 Bronze to silver dbt: 12 staging views; 7 silver tables keyed by hashed business keys Unit tests; silver counts reconcile with staging
4 Gold dbt: dimensions and atomic facts; one reconciliation table Five check tests; the answer-key test
5 Metrics dbt Semantic Layer in Production Each metric equals the answer key's total
6 App and agent Hex app; Morgan on AWS Lambda over the Semantic Layer Read live on the site, figure by figure

Sources. The three systems live in one SQL Server instance on AWS RDS, as three databases, so the integration problem is real: Salesforce knows subscribers and products by its own identifiers, the platform by its own, and usage reports by product codes. Every table has SQL Server Change Tracking on, which is what the ingestion reads.

Ingestion. Snowflake Openflow runs in bring-your-own-cloud mode: Snowflake keeps the control screens, while the machines that move data run in our AWS network, in two private subnets with no inbound path from the internet and outbound traffic through a NAT gateway. Openflow's agent builds its own Kubernetes cluster there; the runtime reads SQL Server from inside the network, so SQL Server is never exposed to Snowflake's addresses. The connector carries inserts, updates and deletes into DATABP_LAB in Snowflake, one schema per source database. A landing reconciliation compares every table's rows per tenant on both sides.

Transformation. The dbt project follows a medallion design. Bronze is the connector's copies, read through staging views that only rename, cast and carry the deleted flag. Silver holds one cleaned table per source document, each keyed by an md5 hash of its business key qualified by its issuer's namespace, so Salesforce's subscriber and the platform's subscriber land on the same key. Gold is the mart: dimensions for subscriber and product, atomic facts for licenses, entitlements, usage reports and invoice items, and one table per subscriber, product and month that carries each check's result. A full Universal Data Model integration is the production path; for a POC we chose the medallion layers and said so in the design.

Metrics. The dbt Semantic Layer defines the metrics once: units on each side, units beyond, the two dollar figures and the count of disagreements per check. The Hex app, Morgan and any other consumer ask the Semantic Layer, never the tables, so a number means the same thing everywhere.

App. The Hex app was built by Hex's own agent from SQL cells we wrote on the Semantic Layer; we kept the cells and checked every figure the agent placed. It is embedded on this site and re-queries the Semantic Layer when the month changes.

Any BI tool. We used Hex for convenience and quick access. Because every figure comes from the Semantic Layer, the dashboard is the only part tied to a BI tool. dbt's Semantic Layer connector for Tableau, published on Tableau Exchange, connects Tableau Desktop, Tableau Server and Tableau Cloud live to the same metrics, with the dbt host, the environment and a service token; a Tableau dashboard would show the same 17 metrics with the same definitions, Morgan would agree with it, and the aggregation stays the Semantic Layer's, not the workbook's. Sources, ingestion, models, metrics and Morgan stay exactly as they are.

Morgan. Morgan is an analyst agent: a conversation core with a guard, a persona and a route to the metrics, deployed as an AWS Lambda function that streams its answer. The route speaks to the Semantic Layer's API, lists the metrics, asks for them by dimension and month, and shows the query behind every figure under the answer. Persona and route are plugins, so the same service can run a different persona over a different source.

3. The build, step by step

These are the steps in the order we took them, with the main command or screen for each, so the platform can be built again from this page; the build tutorial keeps every value. For readers who know Informatica PowerCenter: an Openflow runtime plays the part of an integration service instance, a connector the part of a mapping, and the connector's processors the part of its transformations.

  1. The AWS network. A VPC with two private subnets in two availability zones, a NAT gateway in a public subnet for outbound traffic only, and a security group for Openflow. Nothing in the private subnets is reachable from the internet.

  2. The source, SQL Server on AWS RDS, in the same VPC, its port 1433 open to the two private subnets only. Three databases, one per source system, each with Change Tracking and snapshot isolation on, and Change Tracking on every table:

    CREATE DATABASE DATABP_SFDC;   -- likewise DATABP_USAGE; DATABP_SRC holds the platform
    ALTER DATABASE DATABP_SFDC SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON);
    ALTER DATABASE DATABP_SFDC SET ALLOW_SNAPSHOT_ISOLATION ON;
    ALTER TABLE dbo.Contract ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);   -- each table
    
  3. The Openflow deployment, in Snowflake. First, as ACCOUNTADMIN, the privilege that lets SYSADMIN create and own deployments, as Snowflake's setup guide for bring-your-own-cloud gives it for Openflow's current generation:

    USE ROLE ACCOUNTADMIN;
    GRANT CREATE OPENFLOW DEPLOYMENT ON ACCOUNT TO ROLE SYSADMIN;
    

    Then, as SYSADMIN, the deployment itself, of type bring your own cloud, in our own VPC. We made it in Snowsight (Ingestion, Openflow, Deployments, Create); the same deployment in SQL, per Snowflake's reference for CREATE OPENFLOW DEPLOYMENT:

    USE ROLE SYSADMIN;
    CREATE OPENFLOW DEPLOYMENT DATABP_OPENFLOW
      DEPLOYMENT_TYPE = BYOC
      VPC_TYPE = 'PROVIDED';   -- our own VPC; 'MANAGED' makes Snowflake build one
    

    No command returns the CloudFormation template: Snowflake offers it only as a download in the Openflow screen, once the deployment exists, and it is not retrievable over SQL. We downloaded it there. It comes with nearly every value already filled in. An excerpt of ours, its values masked:

    Parameters:
      SnowflakeAccountURL:        { Type: String, Default: <filled in by Snowflake> }
      SnowflakeOAuthClientID:     { Type: String, Default: <filled in by Snowflake> }
      SnowflakeOAuthClientSecret: { Type: String, Default: <filled in by Snowflake> }
      ControlPlaneURL:            { Type: String, Default: <filled in by Snowflake> }
      DataPlaneURL:               { Type: String, Default: <filled in by Snowflake> }
      DeploymentId:               { Type: String, Default: <filled in by Snowflake> }
      # ... 20 more, each filled in by Snowflake or optional with a default
      InfraVPC:             { Type: AWS::EC2::VPC::Id }             # the cloud admin's choice
      PrivateSubnet1:       { Type: AWS::EC2::Subnet::Id }          # the cloud admin's choice
      PrivateSubnet2:       { Type: AWS::EC2::Subnet::Id }          # the cloud admin's choice
      PrivateSecurityGroup: { Type: AWS::EC2::SecurityGroup::Id }   # the cloud admin's choice
    Resources:
      OpenflowAgentInstance:      { Type: AWS::EC2::Instance }
      OpenflowAgentRole:          { Type: AWS::IAM::Role }
      EC2InstanceProfile:         { Type: AWS::IAM::InstanceProfile }
      SnowflakeOAuthSecret:       { Type: AWS::SecretsManager::Secret }
      SnowflakeOAuthSecretPolicy: { Type: AWS::SecretsManager::ResourcePolicy }
      TerraformStateBucket:       { Type: AWS::S3::Bucket }
      EC2InstanceConnectEndpoint: { Type: AWS::EC2::InstanceConnectEndpoint }
    

    Of the template's 30 parameters, the cloud admin supplies four, all from the network built in step 1. Snowflake writes the rest, including the sign-in between the agent and Snowflake.

  4. The stack, in AWS. The template goes to CloudFormation with those four values:

    aws cloudformation create-stack --stack-name databp-openflow \
      --template-body file://databp-openflow.yaml \
      --parameters ParameterKey=InfraVPC,ParameterValue=<vpc> \
                   ParameterKey=PrivateSubnet1,ParameterValue=<private subnet a> \
                   ParameterKey=PrivateSubnet2,ParameterValue=<private subnet b> \
                   ParameterKey=PrivateSecurityGroup,ParameterValue=<security group> \
      --capabilities CAPABILITY_NAMED_IAM
    

    The stack creates Openflow's agent, a small EC2 machine with its IAM role, its secret, and an S3 bucket the template names for Terraform state. The agent then builds a Kubernetes cluster (EKS) in the private subnets, with a management node and node groups that scale from zero for runtimes. The deployment turns ACTIVE in about an hour.

  5. The runtime, the compute a connector runs on, from Openflow's form or in SQL:

    CREATE OPENFLOW RUNTIME DATABP_LAB.OPENFLOW.DATABP_RUNTIME IN DEPLOYMENT "DATABP_OPENFLOW"
      NODE_TYPE = SMALL MIN_NODES = 1 MAX_NODES = 1 EXECUTE_AS_ROLE = DATABP_LAB_EXEC_RL;
    

    On bring-your-own-cloud the runtime reaches SQL Server inside the VPC, so it needs no external access integration. A node starts in the runtime's node group within a minute.

  6. The connector, from Openflow's connector library: Microsoft SQL Server CT, the Change Tracking connector, installed onto the runtime. It arrives as one process group of 60 processors in four groups: snapshot load, incremental load, metrics and staleness prevention. Its parameters, then Enable all controller services, then Start:

    SQLServer Connection URL    jdbc:sqlserver://<RDS endpoint>:1433;encrypt=true;trustServerCertificate=true
    Username, Password          from the RDS-managed secret in AWS Secrets Manager, pasted by the engineer
    Query Interval              10 sec
    Destination Database        DATABP_LAB
    Destination Schema Pattern  ${source.database.name}_${source.schema.name}
    Authentication Strategy     SNOWFLAKE_MANAGED   (the runtime acts as its execute-as role)
    Included Table Names        DATABP_SRC.dbo.client,DATABP_SRC.dbo.customer,DATABP_SRC.dbo.invoice,...
    

    The connector takes a snapshot of each named table, then polls Change Tracking every 10 seconds and merges each change into the landing table in Snowflake through a journal table, one schema per source database: DATABP_SRC_DBO, DATABP_SFDC_DBO, DATABP_USAGE_DBO. A change in SQL Server shows in Snowflake within one to four minutes.

  7. Snowflake roles for reading and for dbt. A read role over the landing schemas, and a service user that signs in by key pair only:

    CREATE ROLE DATABP_DBT_RL;
    CREATE USER DATABP_DBT_SVC TYPE = SERVICE DEFAULT_ROLE = DATABP_DBT_RL DEFAULT_WAREHOUSE = COMPUTE_WH;
    ALTER USER DATABP_DBT_SVC SET RSA_PUBLIC_KEY = '<public key>';
    GRANT ROLE DATABP_LAB_READ_RL TO ROLE DATABP_DBT_RL;
    GRANT CREATE SCHEMA ON DATABASE DATABP_LAB TO ROLE DATABP_DBT_RL;
    GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE DATABP_DBT_RL;
    

    The Semantic Layer gets its own service user and role the same way.

  8. dbt platform. A project on the repository Integrationals/databp-dbt, a Development and a Production environment, both on Snowflake by key pair; the Semantic Layer configured on Production with its service user and a service token. Staging, silver and gold build in one command, and the metrics answer from the command line:

    dbt build
    dbt sl query --metrics unbilled_revenue,revenue_at_risk --group-by metric_time__month
    

    In Production the job "DataBP build" runs dbt build after every merge.

  9. The source data. The generator writes the paths into SQL Server, writes the answer key, and verifies the loaded rows month by month; the landing reconciliation then compares SQL Server with Snowflake:

    python3 generator/run.py lab tables     # tables, with Change Tracking on each
    python3 generator/run.py lab load       # phase one, one transaction
    python3 generator/run.py lab amend      # twelve months
    python3 generator/run.py lab amend2     # no repeated figure within a month
    python3 generator/run.py lab key        # the answer key, a dbt seed
    python3 generator/run.py lab verify     # the five checks, re-derived in SQL
    python3 generator/reconcile_landing.py  # SQL Server against Snowflake
    
  10. The Hex app. SQL cells ask the Semantic Layer directly, for example:

    select * from {{ semantic_layer.query(metrics=['unbilled_revenue', 'revenue_at_risk'],
                                          group_by=[TimeDimension('metric_time', 'MONTH')]) }}
    

    Hex's agent builds the page from those cells, given one prompt through the Hex command line, hex thread create "<prompt>" --project <project id>; the app is published in Hex and embedded on this site at its latest-version address. In Tableau the same step is a connection, not code: the dbt Semantic Layer connector from Tableau Exchange, given the dbt host, the environment and a service token.

  11. Morgan. One command packages the service, writes its secrets to AWS Parameter Store, and creates its role, log group, Lambda function and public address; the persona and the route are two of its settings:

    python3 agent/deploy.py     # the function runs with AGENT_PERSONA=morgan, AGENT_ROUTE=dbt_semantic_layer
    
  12. This site. Its pages are built from their Pilot sources and served by GitHub Pages from the repository's main branch: python3 tools/build.py --env prod.

4. Source data, designed backwards from the app

The source systems are synthetic, so we designed the data from what the app must prove, not from volume. We profiled the existing tables, drew the designed ones, and found two missing joins before any data existed (a contract to its won opportunity, and Salesforce's products to the platform's), which became design decisions. Then we used the Permutation Coverage Method in reverse: the four documents present or absent, and three unit comparisons where both exist, gave 80 combinations; with an absent document counted as zero units, they collapse to 14 distinct paths of the checks' logic. One set of rows per path covers every behavior; more rows on the same path add volume, not coverage.

A generator writes those rows into SQL Server and computes the answer key from the same paths: each check's expected result and both dollar figures, per subscriber, product and month. Its verify act derives the checks again in SQL from the loaded tables, independently of the generator. It grew in phases while the app was built beside it: 14 paths for October 2026; then four more paths and twelve months of usage reports and invoices; then a constraint that no figure repeats within a month, checked by the generator before anything loads. Every change was proved in a scratch schema first, then applied to the lab in one transaction.

5. Decisions that shaped it

# Decision Why it matters
1 An absent document counts as zero units Every check has a defined answer; 80 cases become 14 paths
2 Billing means items of invoices not void The billing checks match how finance counts
3 Keys hashed in silver, by issuer namespace Three systems join on one subscriber and product
4 Atomic rows; the metric layer adds them up One number had hidden two; see below
5 One governed metric layer for every consumer The app and the agent cannot disagree

One number hid two. The first gold tables held license lines and usage reports separately, and a derived metric subtracted the totals: reported units minus licensed units. For one subscriber it was right. For the data provider it read 2 units, because three subscribers' shortfalls (22 units) cancelled seven subscribers' excesses (24 units). The data mart standard we work to already named this case, a per-row reduction feeding a total, and required it to live in one table with its own unit test and reconciliation. Rebuilt that way, each subscriber, product and month carries its own units beyond and units short, and the metric layer sums each as a separate metric: 24 and 22. Following the standard fixed the number without any new logic in the app.

6. How it is proven

Testing follows seven layers, from built-in tests to the semantic surface, and every planned test is named in a test plan before the code.

# Layer What runs
1 Built-in tests Keys not null and unique; relationships across silver
2 Unit tests 15, each seen failing before its logic existed
3 Logic assertions The answer key: every check and dollar, all 216 rows
4 Reconciliations SQL Server to Snowflake; staging to silver; facts to documents
5 Semantic surface and live reads Metrics against the key; the site read figure by figure

The answer key is the center of it: written by the generator from the paths, re-derived in SQL from the source tables, compared row by row by a dbt test in Production, and read again through the Semantic Layer, Hex and Morgan. A mismatch anywhere stops the release.

7. How we deliver

Each piece of work is an issue with a branch, and each branch carries its documents: a design with numbered decisions, a test plan, and an implementation guide where the build has steps. Code merges only through a pull request written to a template whose center is a test tutorial: each risk, the test that catches it, how it was seen failing first, and its result on the exact commit reviewed. After every merge the same steps run: the source change, the landing reconciliation, the development build, the Production job, the metrics against the key, and a live read of the site.

The build was done by Ray Serrano with AI agents doing the hands-on engineering under his decisions: Claude across the platform, Hex's agent on the app's layout. Every step, value and decision was written into a working log as it happened; this build log is drawn from it.

Problems were handled the same way, by evidence. Production's first build failed because its dbt role could not read two schemas the development role could; the grant was fixed, and a check that Production can read every declared source now runs before every pull request. A Semantic Layer outage was narrowed by excluding each cause with one test, from the token to the warehouse, until it cleared on the provider's side. A published app found stale in a live read led to a fixed close for every Hex change: publish, then read live.

8. What comes next

The steps from here, multi-tenant first, have their own page: Next steps.

Every step with its values is kept in Pilot, our knowledge base; the branch documents behind each decision are in Documents.

Entitlement and billing reconciliation

See it running

The live app and Morgan answer from the same governed metrics, on the platform this site describes.