migrate-dataform: promote the acuantia port's rules into SQL_RULES, and fix the SAFE_CAST rule

[ihistand]

Porting a real 800-action BigQuery Dataform project (acuantia) to Supabase produced a catalogue of BigQuery→PostgreSQL rewrites. Almost all of them are deterministic, and almost none are in SQL_RULES. Every one is a rule the next customer will otherwise rediscover by hand.

Fix first: SAFE_CAST is actively harmful

{ pattern: /\bSAFE_CAST\s*\(/g, replacement: "CAST(", kind: "rewrite",
  note: "SAFE_CAST → CAST — Postgres CAST raises on bad input; verify the data or guard it" }

This silently strips the safety and leaves a note asking the user to verify. What it actually produces is a run that fails hours later, in a different file, with invalid input syntax for type bigint: "0750069967 " — and the failing SQL is code the converter itself wrote. In acuantia the NULL-on-bad-input behaviour was load-bearing: the sources carry Excel error strings (#DIV/0!, #N/A), SAP identifiers with stray characters, and non-numeric HubSpot team ids.

PostgreSQL 16 added pg_input_is_valid(text, type), which asks exactly the question SAFE_CAST asks:

SAFE_CAST(x AS t)  ->  case when pg_input_is_valid(x::text, 't') then x::t end

Guard on the text form, cast x itself, so an already-numeric value converts normally. Rewrite to this rather than flagging — it is exact, not approximate. (One divergence to note in the docs: for a fractional numeric cast to an integer type, BigQuery rounds where this yields NULL.)

Rules to add as kind: "rewrite"

All deterministic, all verified against a real project and a live PostgreSQL:

BigQuery	PostgreSQL	notes
SAFE_DIVIDE(a, b)	a / nullif(b, 0)	currently only flagged
REGEXP_EXTRACT(x, p)	substring(x from p)	both return the first capture group, else the whole match
DATE_SUB/ADD(d, INTERVAL n u)	(d -/+ interval 'n u')
DATE_DIFF(a, b, DAY)	(a::date - b::date)
DATE_DIFF(a, b, HOUR)	epoch of the hour-truncated difference	BigQuery counts boundaries crossed; dividing the raw interval is off by one
arr[OFFSET(n)] / [SAFE_OFFSET(n)]	arr[n + 1]	currently only flagged
SPLIT(x, d)	string_to_array(x, d)
SPLIT(x, '')	string_to_array(x, NULL)	empty delimiter splits per character; an empty string in PG returns one element
EXTRACT(DAYOFWEEK FROM x)	(extract(dow from x) + 1)	BigQuery is 1-based from Sunday, dow 0-based
COLLATE(x, '') / x COLLATE ''	x	collation stripping; the function form is a syntax error in PG
CAST(… AS DATETIME)	… as timestamp
`ident`	"ident"	skip project.dataset.table and interpolated tokens
r'…'	'…'	PG reads the r as a type name
# comment	-- comment
"literal" in value position	'literal'	IN-lists, equality, aggregate separators only
GROUP BY ALL	group by 1, 2, 5	ordinals of non-aggregate, non-window items
SELECT * EXCEPT (…)	explicit column list	see below
… NOT ENFORCED	commented-out PG constraint	see below

Two of these need more than lexical matching, and are worth it — they were the two largest classes in acuantia:

SELECT * EXCEPT (141 sites / 132 files) needs the star's relation resolved: a ref to a declaration gives columnTypes; a ref to a project view resolves recursively; CTEs and subqueries resolve in-file; a qualified star binds through the FROM/JOIN chain. It must sweep to a fixpoint, since a star over a view is only knowable once that view names its own columns. 126 of 141 resolved automatically.

Identifier casing — the extract creates columns with quoteIdent(), so BigQuery's casing survives, while PostgreSQL folds unquoted identifiers to lowercase AND BigQuery matched case-insensitively. So Email must be quoted, email referring to it must become "Email", and a bare quoted column in a select list should alias back to lowercase so the case-folding stops at the source boundary instead of propagating through the graph. ~700 references in acuantia. Needs a real parser, not regex — my regex version was bitten twice by aliases shadowing source column names.

Keep as kind: "flag" — these need intent, not syntax

STRUCT (jsonb vs composite vs restructure depends on downstream), QUALIFY, FARM_FINGERPRINT (no equivalent; whether values must match BigQuery's is a business question), PARSE_DATE/FORMAT_DATE (format tokens are translatable but silently wrong-on-some-rows if mismatched), UNNEST. Their notes should point at a documented worked example rather than "consider jsonb".

Two structural findings

The converter must also rewrite includes/. Shared JS helpers emit SQL, and being template literals they are invisible to a .sqlx scan. In acuantia, split(x, d)[SAFE_OFFSET(n)] inside four helper functions failed two views on every single run while looking like an unfixed tail item. The file has a SQLANVIL-MIGRATE header comment listing findings, so the converter knows they exist — it just cannot act on them.

A --report output would carry more weight than any single rule. What made acuantia tractable was not knowing the rewrites; it was a classified inventory — every remaining construct grouped by class, with counts, file lists and a recommended treatment. That converts an unbounded slog into a finite list someone can work or descope. The information already exists in the findings pass; it just needs emitting as structured output rather than only as inline markers.