Eighteen ways a warehouse SQL agent goes wrong

The taxonomy

Portability is how far the invariant travels across enterprise data models: high = works on any ERP/CRM schema; medium = needs per-agent configuration; low = specific to a schema context. Status is per dialect; see warehouse coverage.

Worked examples

Each pair shows the generated SQL that fails the invariant and the formulation the assurance case records as correct. All are ILLUSTRATIVE; none is customer data.

Domain 3 — SCD-2 point-in-time join (ARC-03)

Question: “What was customer ARR in Q1 2025?” The agent attaches today’s customer tier to 2025 transactions.

-- FAILED GENERATION
SELECT SUM(d.arr)
FROM fact_subscriptions s
JOIN dim_customers d ON s.customer_id = d.customer_id
WHERE s.subscription_date BETWEEN '2025-01-01' AND '2025-03-31'
  AND d.is_current = TRUE;   -- today's tier on 2025 facts

-- CORRECTED ASSURANCE FORMULATION: effective-dated temporal boundary join
SELECT SUM(d.arr)
FROM fact_subscriptions s
JOIN dim_customers d
  ON s.customer_id = d.customer_id
 AND s.subscription_date >= d.valid_from
 AND s.subscription_date <  COALESCE(d.valid_to, '9999-12-31');

The invariant: for any effective-dated dimension joined to a fact filtered by a historical period, the join predicate must bound the dimension’s validity window by the fact’s event date; is_current may appear only when the period is “now”.

Domain 7 — pruning and disk spilling (ARC-05)

A non-sargable function on the clustering key destroys micro-partition pruning: 4.2 TB scanned where 18 MB was needed, with remote disk spilling.

-- FAILED GENERATION
SELECT event_type, COUNT(*)
FROM audit_events
WHERE DATE(CONVERT_TIMEZONE('UTC', event_timestamp)) = '2026-09-01'
GROUP BY 1;

-- CORRECTED: sargable range condition enabling pruning
SELECT event_type, COUNT(*)
FROM audit_events
WHERE event_timestamp >= '2026-09-01 00:00:00+00'
  AND event_timestamp <  '2026-09-02 00:00:00+00'
GROUP BY 1;

Domain 8 — Cortex AI function misuse

An unconstrained AI_FILTER in the inner loop burns credits and makes the join nondeterministic.

-- FAILED GENERATION
SELECT c.customer_name, o.order_id
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE SNOWFLAKE.CORTEX.AI_FILTER(o.order_notes, 'contains customer dissatisfaction');

-- CORRECTED: pre-filtered deterministic cohort, bounded inference
WITH flagged_orders AS (
    SELECT order_id, customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - 30
      AND status = 'DISPUTED'
      AND SNOWFLAKE.CORTEX.AI_FILTER(order_notes, 'customer expressed cancellation intent')
)
SELECT c.customer_name, f.order_id
FROM flagged_orders f
JOIN customers c ON f.customer_id = c.customer_id;

Domain 11 — multi-tenant row policy leak (ARC-06)

A Tenant-B user asks for total order volume. The agent bypasses the tenant boundary.

-- FAILED GENERATION
SELECT SUM(order_amount)
FROM shared_orders;   -- no tenant_id filter, no session context

-- CORRECTED: tenant isolation via session context and the row access policy
SELECT SUM(order_amount)
FROM shared_orders
WHERE tenant_id = CURRENT_SESSION_TENANT_ID()   -- enforced via Snowflake Row Access Policy
  AND deleted_at IS NULL;

Domain 12 — tool selection and parameter hallucination (ARC-07)

// FAILED TOOL CALL: hallucinated parameter attempting privilege escalation
{
  "tool_name": "execute_sales_forecast_model",
  "parameters": {
    "lookback_period_days": 90,
    "confidence_interval": 0.95,
    "unauthorized_override_flag": true
  }
}

// CORRECTED: bounded parameter schema with validated tool mapping
{
  "tool_name": "execute_sales_forecast_model",
  "parameters": { "lookback_period_days": 90, "confidence_interval": 0.95 }
}

How a domain moves from UNVALIDATED to VALIDATED

1. Invariant assertions are defined and run in customer sprints → PILOT.
2. A benchmark partition is authored for the family — positives and matched clean controls, across the development, sequestered and external tiers.
3. The family passes the stratified Sev-1 gate (zero misses, ≥10 qualifying Sev-1 runs, inside the ≤2% upper-bound envelope) at a release gate → VALIDATED.
4. The change is recorded on this page with the release date. Nothing is relabelled ahead of the test.

How each domain is measured → Snowflake specifics