Chapter 7: Validating the Conversion

Generating validation SQL that proves converted data matches documented intent.

Chapter 5 covered workbook validation — Bridger checking that your map is structurally sound. This chapter covers something different: generating SQL that proves your converted data is correct. These are the queries a development team runs after the conversion loads to confirm that what arrived in the target database matches what the mapping specified. Bridger can generate a skeleton of those queries directly from the transformation logic already documented in your mappings.

The Four Test Case Notation Types

Generated validation SQL is stored as TC-* notation tabs on each mapping, alongside the transformation logic and any other notations already attached. Four notation types cover the validation categories, though not every mapping produces all four — which types are generated depends on how the mapping is classified, as described in the generation section below.

TC-Minus holds a MINUS or EXCEPT query comparing source and target values directly. If the query returns rows, something was transformed incorrectly or lost entirely.

TC-Counts holds count-based reconciliation: row counts, distinct value counts, null counts, and numeric aggregates. These confirm that data volumes and distributions are consistent between source and target.

TC-Default holds verification queries for mappings where a constant value was applied — Conversion Default or Database Default sources. These confirm the expected value actually landed in the target column.

TC-Manual holds a stub with the mapping's transformation logic included as context. These are for mappings where automated query generation isn't possible and a developer or analyst needs to write the validation by hand.

If the TC-* notation types don't yet exist in your workbook configuration, Bridger adds them automatically when you run generation for the first time.

Generating Validation SQL

Select one or more mappings in the mappings panel, right-click, and choose Generate Validation SQL…. The dialog presents four options before generation runs.

Dialect controls the SQL syntax of the output — Oracle, SQL Server, PostgreSQL, or MySQL. Bridger remembers the last dialect you used. Choose the dialect that matches your target database.

Existing test cases determines what happens to mappings that already have TC-* notations: skip them and leave the existing SQL intact, or overwrite with freshly generated content.

Remove incompatible types cleans up TC-* notations that don't match the mapping's current classification. For example, if a mapping previously generated a TC-Minus notation but has since been changed to a Conversion Default, enabling this option removes the now-irrelevant TC-Minus when the new TC-Default is written.

Include approved mappings controls whether locked mappings are included. Approved mappings are skipped by default — their transformation logic has been signed off and their validation SQL is typically already in place.

Click Generate and Bridger writes the TC-* notations to each selected mapping. Open any mapping and you'll find the new tabs alongside the existing ones.

What Gets Generated vs. What Needs Refinement

Bridger classifies each mapping before generating, and the classification determines which notation types are produced and how complete they are. The quality of the output depends directly on the quality of the transformation logic — mappings written with consistent templates produce more useful skeletons than free-text logic.

Direct source-to-target mappings produce both a TC-Minus and a TC-Counts notation. The MINUS query is usually runnable as-is for straightforward moves; count reconciliation gives a volume cross-check alongside it.

Conversion Default mappings produce a TC-Default notation verifying that the specified constant value was applied. These are generally complete without further editing.

Database Default and Excluded source mappings produce a TC-Counts null check confirming that the target column contains no unexpected populated values. Straightforward to verify.

Stored Value mappings and Source Group mappings produce TC-Manual stubs. The transformation logic is embedded as context for the developer writing the validation, but the SQL itself requires human authorship — the derivation logic is too specific for automatic generation.

Mappings targeting a special target table (Excluded) are skipped entirely — there is no target column to validate against.

AI-Assisted Refinement

For complex mappings where the generated stub needs significant work, Bridger can package the mapping's full context — source and target column definitions, transformation logic, and generated SQL — into a prompt ready to paste into an external AI assistant.

Right-click a single mapping and choose Copy AI Context for Validation…. A dialog opens showing the formatted context package; copy it to the clipboard and paste it into any AI tool you use. The prompt includes everything the AI needs to suggest refined validation SQL: column data types, the transformation rule, and the skeleton query as a starting point.

This option is only available when a single mapping is selected — the context is mapping-specific and isn't meaningful in bulk.

What's Next

The core workflow is now complete — you've built a map, automated the repetitive work, proven its integrity, reported progress to stakeholders, and generated the validation SQL to prove the conversion itself. The remaining chapters cover the infrastructure that supports larger and longer engagements. Chapter 8: Building Your Library introduces the library as a reusable schema catalog and institutional knowledge store.