An AI skill that compares SQL billing logic between the Variable Billing and Lighthouse repos — at the metric level — and explains why the numbers may differ.
This is one of the most frequently asked questions that I get, as part of the DIA team. Answering it requires manually digging into two separate repos, comparing SQL logic, and explaining the differences — every single time.
Each comparison requires opening both repos, finding the right SQL files, reading through the logic, and mentally diffing them. This can take 30-60 minutes per product.
Product names differ between repos. Lighthouse may have multiple SQL files per product serving different purposes. The variable_billing folder in Lighthouse is a red herring.
Stakeholders don't want to hear about JOIN conditions and WHERE clauses. They need a simple answer: "here's why the numbers are different for this customer."
The same question comes up for Workers, R2, API Shield, DNS, Stream, and dozens of other products. The investigation pattern is identical each time.
Give it a product name and (optionally) a customer ID. It fetches SQL from both repos, compares at the metric level, and delivers a structured report with validation queries.
Reads variable-usage SQL locally, fetches lighthouse-ingestion SQL via GitLab API. Handles name mismatches automatically.
Doesn't just compare filenames — scans ALL SQL files, extracts output metrics, and matches them across repos by name similarity.
Provide an sf_account_id and all validation queries are pre-filtered to that customer — ready to paste into BigQuery.
Concise mode for stakeholders (plain language, no jargon). Verbose mode for engineers (code snippets, metric maps).
Generates ready-to-run BigQuery SQL that shows both systems' data side-by-side, plus a diff query highlighting mismatches.
Automatically skips the variable_billing/ folder in Lighthouse (which just copies VB output) and compares against the actual independent logic.
Fuzzy-match the product name to directories in both repos
Read all SQL files, extract output metrics from each
Match metrics across repos, compare source tables, filters, formulas
Classify as match / minor / significant, format per output mode
Generate ready-to-run BQ SQL filtered to the customer
The skill was validated against two products with very different characteristics.
Here's what the skill actually produced when asked to compare API Shield for a specific customer. This is what you'd paste into Slack.
Paste into BigQuery to see the data side-by-side for this customer
The skill went through 3 major iterations, each driven by real-world testing and feedback.
api_request_status_by_zone_monthly_query.sql) than the main product file. Switched to metric-level matching across ALL SQL files. Also rewrote concise mode tone guidelines for non-technical stakeholders.Lighthouse has a queries/bigquery/variable_billing/ folder that copies VB output. Comparing against it would always show "match" — misleading. Added explicit exclusion rule.
API Shield simulation showed that the "requests" metric lived in a different file than expected. Switched from file-level to metric-level matching across all SQL files.
First concise mode still used SQL jargon. Added explicit tone guidelines: say "billing system" not "variable-usage pipeline", "enterprise zones" not "zone_plan = ENT".
...but it also took feedback well!
After the first API Shield simulation, OpenCode declared success — saying the skill "handles well" the case where pipelines measure completely different things. But it missed that a different lighthouse file actually had the comparable requests metric. One nudge later, the entire matching approach was redesigned.
api_request_status_by_zone_monthly_query.sql with a requests metric that actually was comparable to VB's sum_requests — but the skill never looked at that file because it was matching by filename, not by metric.