SQL Reconciliation Workbench

    1

    Reconcile diverging SQL reports using diagnostic SELECTs, membership checks, and synthetic data witnesses.

    $5

    Secure checkout via Stripe

    Money-back guarantee

    Converts to your local currency at checkout

    Security scanned

    Works with the AI Agents you already use

    Claude CodeClaude CodeCursorCursorCodex CLICodex CLIMuseMuseDotsDots+21 more

    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

    DimensionLegacy BehaviorNew BehaviorImpact
    Row Grainorder_idorder_idConsistent
    TimezoneImplicit (System)Explicit UTCPotential 8-hour shift
    Cancelled OrdersIncluded in SUM, filtered in CTEExcluded via LEFT JOIN predicate'New' is more restrictive
    Null HandlingCOALESCE(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

    1. Run the Diagnostic SQL to export the list of mismatched order_ids.
    2. Confirm if the 'Net Confirmed Revenue' definition requires confirmations table presence.
    3. Align the created_at casting 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

    1. Define the contract by providing two SQL queries, the target metric, and relevant schema details.
    2. Review the rule comparison to see how predicates and cardinality differ between the baseline and the candidate query.
    3. Execute diagnostic SELECTs provided by the skill to find offsetting errors or membership mismatches.
    4. 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.

    No live DB connections or DDL generation. Focused on read-only SELECT diagnostics. Requires manual execution of provided SQL.

    How to use

    1. 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. 2

      Sign in to Agensi

      Your agent shows a sign-in link. Sign in and click Allow.

    3. 3

      Ask for what you need

      Your agent loads this skill and uses it on your task. It always asks before any payment.

    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

    Permissions

    Read Files
    Listed7 days ago

    What's inside

    Frequently Asked Questions

    This skill automates the forensic analysis of SQL discrepancies by generating diagnostic queries and synthetic data 'witnesses' that isolate exactly which predicates, joins, or null-handling logic are causing two reports to diverge.

    The workbench supports any standard SQL dialect, including PostgreSQL, Snowflake, BigQuery, and MySQL, focusing on analytical CTEs and relational algebra that work across most modern data warehouses.

    No, the skill operates on a read-only basis for security; it provides the diagnostic SELECT statements for you to run manually, ensuring it never requires write access or DDL permissions on your database.

    You receive the full logic framework for metric definition, join multiplicity analysis, and synthetic witnessing, along with pre-configured workflows for report migration and audit preparation.

    Unlike generic prompts, this skill uses a specialized workflow that prioritizes per-key membership checks over simple aggregate totals, preventing the 'offsetting errors' that often lead to false positives in standard AI interactions.

    New in Data & Databases