Set-Theoretic Integrity Audit for Multi-Source Research Data

Role: Lead Analytics Data Architect specialized in relational algebra, research data reconciliation, and warehouse modeling.

Context

• Target Warehouse Platform: {{warehouse_platform}}
• Core Entity Definitions: {{primary_entities}}
• Disparate Source Schemas & Grain: {{source_schemas}}
• Entity Reconciliation & Merging Rules: {{reconciliation_rules}}
• Table Partitioning & Clustering Strategy: {{partitioning_strategy}}
• High-Risk Edge Case Scenarios: {{edge_case_definitions}}

Task

Design a structured data integrity and set-theoretic audit checklist to validate multi-source SQL transformations, ensuring flawless relational joins, grain consistency, and reconciliation accuracy across {{primary_entities}}.

Method

1. Map entity cardinality between all sources in {{source_schemas}} to identify potential many-to-many explosion hazards.
2. Evaluate JOIN predicates against compound business keys to detect accidental Cartesian products and duplicate amplification.
3. Audit set operations (UNION, INTERSECT, EXCEPT) to ensure deduplication semantics match {{reconciliation_rules}}.
4. Check FULL OUTER JOIN coalescing logic for asymmetric null generation across primary keys.
5. Verify deduplication window functions (ROW_NUMBER() vs DENSE_RANK()) to ensure deterministic resolution of ties.
6. Validate data type coercion and collation parity across disparate sources in {{warehouse_platform}} to prevent silent join mismatches.
7. Test join behavior against {{edge_case_definitions}} (e.g., overlapping validity periods, multiple active statuses, surrogate key collisions).
8. Create validation SQL queries that quantify row count delta and set-difference reconciliation metrics at each pipeline stage.

Constraints

• Every checklist section MUST include mandatory reconciliation queries measuring row-count deltas or unmapped keys.
• MUST verify idempotency for all transformational logic across repeated runs.
• MUST NOT approve any transformation containing unconstrained outer joins without explicit null fallbacks.
• Specific to {{warehouse_platform}} SQL capabilities and execution semantics.

Output format

• Executive Audit Charter (100-150 words)
• Section 1: Grain & Key Cardinality Audit (4-5 markdown checklist items with SQL reconciliation queries)
• Section 2: Set Operations & Merging Consistency (4-5 markdown checklist items with set difference validation tests)
• Section 3: Edge Case & Boundary Scenario Validations (4-5 markdown checklist items testing {{edge_case_definitions}})
• Set-Theoretic Integrity Scorecard (tabular evaluation criteria: Dimension, Pass Condition, Critical Failure Threshold)

Self-review

• Confirm all 6 context variables are deeply interwoven into the verification criteria.
• Verify that deterministic tie-breaking logic is explicitly validated.
• Ensure SQL test patterns provided are valid for {{warehouse_platform}}.