- Home
- Skills
- Data & Databases
- SQL Reconciliation Workbench
SQL Reconciliation Workbench
Reconcile diverging SQL reports using diagnostic SELECTs, membership checks, and synthetic data witnesses.
$5
Works with the AI Agents you already use
SQL Reconciliation Workbench
Example session with this skill installed
Reconcile these two Postgres queries. The 'legacy' query shows higher revenue than the 'new' query for Jan 2024. The metric is 'Net Confirmed Revenue'.
- Read your context and instructions
- Compiled the sql reconciliation workbench
I have analyzed the discrepancies between the legacy and new revenue queries. The divergence stems from how cancelled orders are filtered within the join predicate versus the WHERE clause.
Reconciliation Analysis
| Dimension | Legacy Behavior | New Behavior | Impact |
|---|---|---|---|
| Row Grain | order_id | order_id | Consistent |
| Timezone | Implicit (System) | Explicit UTC | Potential 8-hour shift |
| Cancelled Orders | Included in SUM, filtered in CTE | Excluded via LEFT JOIN predicate | 'New' is more restrictive |
| Null Handling | COALESCE(amount, 0) | SUM(amount) | 'New' returns NULL for empty days |
Diagnostic SQL
Execute this query to identify specific order_ids that exist in the legacy result but are missing from the new result:
WITH legacy_set AS (
-- Legacy Query Logic
SELECT order_id, amount FROM orders WHERE status != 'deleted' AND created_at >= '2024-01-01'
),
new_set AS (
-- New Query Logic
SELECT o.order_id, o.amount
FROM orders o
INNER JOIN confirmations c ON o.order_id = c.order_id
WHERE o.created_at >= '2024-01-01' AT TIME ZONE 'UTC'
)
SELECT
l.order_id,
l.amount as legacy_amt,
n.amount as new_amt
FROM legacy_set l
LEFT JOIN new_set n ON l.order_id = n.order_id
WHERE n.order_id IS NULL;
Synthetic Witness
Scenario: Order #501 was created on 2023-12-31 23:00:00 (Local) but is 2024-01-01 07:00:00 (UTC).
- Legacy Output: Included (uses local system time).
- New Output: Included (uses UTC).
- Discrepancy: If the system time is behind UTC, orders at the end of the month shift into the next period.
Next steps
- Run the Diagnostic SQL to export the list of mismatched
order_ids. - Confirm if the 'Net Confirmed Revenue' definition requires
confirmationstable presence. - Align the
created_atcasting to a single consistent timezone across both queries.
Connects securely to your tools. The creator never sees your data.
About this skill
Diverging dashboard totals and report mismatches consume hours of manual investigation. This skill automates the forensic analysis required to reconcile two SQL queries against a single metric definition, identifying exactly where predicates, joins, or null behaviors cause discrepancies.
What it does
- Metric definition establishes the row grain, keys, time ranges, and aggregation logic to create a source of truth.
- Join analysis identifies how multiplicity, deduplication, and outer join placement impact row counts.
- Null & empty group handling distinguishes between missing rows, zero-value rows, and null totals.
- Synthetic witnessing generates minimal fixtures and hand-derived outputs to prove specific query defects.
- Diagnostic generation provides read-only SELECT statements and CTEs to isolate mismatched keys.
How it works
- Define the contract by providing two SQL queries, the target metric, and relevant schema details.
- Review the rule comparison to see how predicates and cardinality differ between the baseline and the candidate query.
- Execute diagnostic SELECTs provided by the skill to find offsetting errors or membership mismatches.
- Inspect the synthetic witness to understand how specific data patterns trigger the observed discrepancy.
Frameworks & tools
Works with any SQL dialect, defaulting to PostgreSQL. It focuses on analytical SQL, CTEs, and relational algebra without requiring database write access.
Why this beats prompting it yourself
Generic prompts often overlook subtle join multiplicity or timezone casting issues that lead to false positives. This skill follows a strict reconciliation workflow that prioritizes per-key membership over aggregate totals, ensuring you do not miss offsetting errors.
Use cases
- Report migration to verify a new system matches the legacy baseline.
- Metric versioning to document exactly how a definition change affects membership.
- Dashboard debugging to find why two charts showing the same metric don't match.
- Audit preparation to provide a clear paper trail of query logic and expected outputs.
Known limitations
Does not perform live database connections, data tuning, or write operations. DDL generation is excluded to focus on read-only diagnostics.
Find more skills like this on the Claude Skills Marketplace, the Codex Skills Marketplace and the Gemini Skills Marketplace, or browse Claude Skills for Data & Databases.
No live DB connections or DDL generation. Focused on read-only SELECT diagnostics. Requires manual execution of provided SQL.
How to use
- 1
Connect your agent to the Agensi MCP
Copy the prompt and paste it into Claude, ChatGPT, Cursor or any agent that supports MCP. It sets up the connection for you. Manual setup
- 2
Sign in to Agensi
Your agent shows a sign-in link. Sign in and click Allow.
- 3
Ask for what you need
Your agent loads this skill and uses it on your task. It always asks before buying a skill.
Reviews
No reviews yet
Be one of the first to try it. Every listed skill goes through the trust checks below.
Security scanned
Checked by our 8-point scan before listing
Fresh listing
Recently published to Agensi
Money-back guarantee
Doesn't do what its page promises? You get your money back.
Trust & safety
Security scan
Security scan passed 7 days ago