altimate_core_validate rejects correct Snowflake SQL: case-sensitive identifier matching against uppercase schema_context, and false TableNotFound with no schema

[anandgupta42]

Summary

altimate_core_validate compares SQL identifiers against schema_context case-sensitively. Snowflake metadata tools (snowflake_get_table_stats, schema_inspect) return UPPERCASE column names, and Snowflake unquoted identifiers are case-insensitive, so any Snowflake query validated against real metadata comes back INVALID with ColumnNotFound even when it is correct. Worse, the engine lowercases the identifiers it extracts from the SQL before comparing, so uppercasing the SQL does not help either: with an uppercase schema every variant fails.

The model then loops: rewrite to uppercase, re-validate, lowercase the schema, re-validate, ask for table stats, re-validate. In the workspace demo recordings this cost three to four validation rounds (30 to 50 seconds) before the model gave up on the validator and ran the query through the engine, where it succeeded first time. Thought titles seen on screen: "Planning query rewrite to avoid false positive error", "Standardizing Snowflake case to uppercase", "Deciding on lowercasing schema context columns", "Verifying validator case handling".

Reproduction (repo at 8877e56c32, bun run from the repo root)

import * as core from "@altimateai/altimate-core"
import { schemaOrEmpty } from "./packages/opencode/src/altimate/native/schema-resolver"

const sql = `select customer_region, sum(net_revenue) as net_revenue
from TPCH_ANALYTICS.PUBLIC_REPORTING.RPT_MONTHLY_SALES_BY_REGION
where order_month >= '1997-01-01' group by 1`

const upper = { "TPCH_ANALYTICS.PUBLIC_REPORTING.RPT_MONTHLY_SALES_BY_REGION": { columns: [
  { name: "ORDER_MONTH", type: "DATE" }, { name: "CUSTOMER_REGION", type: "VARCHAR" }, { name: "NET_REVENUE", type: "NUMBER" } ] } }
const lower = { "tpch_analytics.public_reporting.rpt_monthly_sales_by_region": { columns: [
  { name: "order_month", type: "DATE" }, { name: "customer_region", type: "VARCHAR" }, { name: "net_revenue", type: "NUMBER" } ] } }

console.log(await core.validate(sql, schemaOrEmpty(undefined, upper)))
console.log(await core.validate(sql.toUpperCase(), schemaOrEmpty(undefined, upper)))
console.log(await core.validate(sql, schemaOrEmpty(undefined, lower)))
console.log(await core.validate(sql, schemaOrEmpty(undefined, {})))

Output:

lower SQL / UPPER schema -> {"valid":false,"error":[{"code":"E002","kind":{"column":"order_month","type":"ColumnNotFound"},"message":"Column 'order_month' not found","suggestions":[{"confidence":1,"kind":{"name":"TPCH_ANALYTICS.PUBLIC_REPORTING.RPT_MONTHLY_SALES_BY_REGION.ORDER_MONTH","type":"DidYouMean"}}]}]}
UPPER SQL / UPPER schema -> {"valid":false,"error":[{"code":"E002","kind":{"column":"order_month","type":"ColumnNotFound"}, ...same...}]}
lower SQL / lower schema -> {"valid":true,"error":[]}
no schema               -> {"valid":false,"error":[{"code":"E001","kind":{"table":"rpt_monthly_sales_by_region","type":"TableNotFound"}}]}

The engine's own "DidYouMean" suggestion is the same column in uppercase with confidence 1, which is the tell: it knows the answer and rejects it anyway.

The last line is a second problem: with no schema at all, the engine returns valid:false / TableNotFound, and the tool wrapper (packages/opencode/src/altimate/tools/altimate-core-validate.ts, execute) reports data.valid as-is, so the model sees Validate: INVALID (no schema) with "Table not found" for a correct query. The tool's own description promises existence checks are skipped without a schema.

Suggested fix

• For dialects with case-insensitive unquoted identifiers (Snowflake, and effectively Postgres, Redshift, BigQuery outside quotes), fold both sides to one case in SchemaResolver.normalizeSchemaContext before handing the schema to the engine, or fold in the engine's identifier lookup and keep the quoted-identifier path exact.
• When no schema is supplied, the tool should not report E001/E002 at all (the description already promises existence checks are skipped).
• Add a test that validates a Snowflake query against uppercase metadata from schema_inspect output.

Context

Found while recording the workspace pilot demos; the repro script is packages/opencode/repro-validate-case.ts on the workspace_learning worktree (not for merge).

[anandgupta42]

@sahrizvi assigning to you. This is the one that made the model loop three or four validation rounds in every warehouse demo before it gave up and ran the query through the engine, where it was fine. The repro script in the body runs from the repo root with bun and shows the DidYouMean suggestion pointing at the same column in uppercase with confidence 1.

[sahrizvi]

Shipped in v0.12.2-beta.1 on the npm beta channel (npm i -g @altimateai/altimate-code@beta); latest is still 0.12.1. Release: https://github.com/AltimateAI/altimate-code/releases/tag/v0.12.2-beta.1

[sahrizvi]

Released in v0.12.2 (latest): https://github.com/AltimateAI/altimate-code/releases/tag/v0.12.2