Data Type Mismatch Auditing in Snowflake and BigQuery

Data type mismatches don't crash pipelines. They sail straight through, row counts intact, jobs marked green, and land quietly in a downstream table where a finance analyst or a data scientist eventually notices a number that's just slightly wrong. This piece looks at how Snowflake and BigQuery each handle types differently under the hood, where that divergence creates specific, findable failure points, and what an audit actually has to check to catch them before they cost anyone money.

Snowflake and BigQuery diverge at the point where a type coercion either surfaces as an error or disappears into the data. Snowflake resolves most coercions at load time, refuses to change an existing column to a genuinely different type in place, and allows conversions like NUMBER to FLOAT to proceed silently, with precision loss and no visible warning. BigQuery resolves coercions at query time and, depending on which cast function is in use, either errors loudly on failure (CAST) or returns NULL without raising an alert (SAFE_CAST), making the choice of function a structural decision for audit design. An audit built to catch silent conversion errors before they reach downstream tables needs to diff INFORMATION_SCHEMA.COLUMNS against a saved baseline on a schedule, treating any type change as evidence of a column rebuild; apply SAFE_CAST against suspect columns and measure the resulting null rate as a mismatch signal; and for VARIANT columns in Snowflake, query FLATTEN and TYPEOF to detect keys that have shifted type across rows without any structural change appearing in the schema metadata.

Most monitoring setups treat schema drift and schema evolution as the same thing, but they aren't. Evolution is deliberate: someone adds a column, documents it, and the pipeline adjusts. Drift is what happens when an upstream system quietly flips an ID field from integer to a UUID string, or when a null rate climbs sharply over three weeks without anyone touching the schema. Batch pipelines at least fail visibly, often overnight, giving someone a stack trace to chase down the next morning. Streaming systems don't offer that grace period. A malformed type appears in the stream in real time, and if no one's watching the right signal, it just keeps flowing.

How do Snowflake and BigQuery handle data types differently at the architecture level?

Snowflake's numeric system splits along a fault line: NUMBER and DECIMAL for fixed-point values, FLOAT and DOUBLE for floating-point. Converting between the two families isn't symmetric. Push a NUMBER into a FLOAT and Snowflake will do it, but exact decimal representation isn't guaranteed to survive the trip, and rounding or truncation can follow depending on the value involved.

Timestamps get even more particular. Snowflake maintains three separate variants, TIMESTAMP_LTZ, TIMESTAMP_NTZ, and TIMESTAMP_TZ, each carrying its own timezone semantics. Collapse them into one without thinking it through and you can lose time-of-day information or timezone context that downstream logic was quietly depending on. For semi-structured data, Snowflake offers VARIANT, OBJECT, and ARRAY types, which absorb schema changes by storing values in an internal optimized format. That's a real escape hatch for messy JSON, but it trades structural enforcement for flexibility: nothing stops a key that was always a number from becoming a string in some rows.

One constraint shapes everything else about how Snowflake schemas evolve: you cannot change an existing column to a genuinely different type. Widening within a compatible family works fine, you can grow a VARCHAR's length or increase a NUMBER's precision with ALTER TABLE natively. But a cross-type change, say NUMBER to VARCHAR, requires dropping and recreating the column. That single rule is why a type change showing up in an audit diff later on should set off alarms: it means a column was rebuilt.

BigQuery takes a looser stance in some places and a stricter one in others. It allows adding nullable columns and relaxing a REQUIRED field to NULLABLE, and it supports column drops and renames natively, but it will not let you narrow a type. BIGNUMERIC pushes precision substantially higher than Snowflake's ceiling, which is nearly double Snowflake's ceiling of 38, a structural mismatch that causes outright ingestion failures on migration if a value happens to exceed Snowflake's range.

JSON is a first-class type in BigQuery. Snowflake has no equivalent and maps it into VARIANT instead. INTERVAL is native to BigQuery too, while Snowflake defaults to storing it as VARCHAR(30) unless a preview flag enables INTERVAL DAY TO SECOND. BYTES maps to Snowflake's BINARY, close enough in practice but not identical in how downstream functions read it, and BigQuery's ANY TYPE parameter in user-defined functions becomes VARIANT on the Snowflake side, which changes how the function behaves at the edges.

A deeper architectural split produces this. BigQuery is serverless: compute gets allocated dynamically and there's no warehouse to size or manage. Snowflake runs on virtual warehouses that a team explicitly provisions and controls. That difference decides where a type coercion error actually surfaces, at query time in BigQuery, versus load time in Snowflake, which in turn shapes when an audit has a chance to catch it. BigQuery's schema update configuration handles a good chunk of routine drift, appending new columns, relaxing REQUIRED constraints, but it hits a wall the moment a change turns destructive. Type modification, in either platform, generally means someone has to rebuild the table or write a transformation layer to bridge the gap.

Coercion mismatch vectors that audit queries must target

Numeric precision loss is the most dangerous failure because it never announces itself. Snowflake allows a NUMBER(38,0) to convert to FLOAT, and the platform may not surface a visible error even when precision is lost or rounding occurs in the process. BigQuery behaves differently depending on the function: CAST AS NUMERIC converts to fixed-point representation, but SAFE_CAST returns NULL on failure rather than throwing an error. That distinction matters enormously for audit design, since a query built around CAST will fail loudly while one built around SAFE_CAST will fail silently and just hand back nulls.

A value with 76 digits of precision moving from BigQuery into a Snowflake NUMERIC column, capped at 38, doesn't get rejected outright in every case. A value with 76 digits of precision moving from BigQuery into a Snowflake NUMERIC column, capped at 38, doesn't get rejected outright in every case. It can get truncated or rounded without any error surfacing, because the type conversion itself is legal, even though the result no longer matches the source value.

Timestamps carry their own quiet hazards. BigQuery's PARSE_TIMESTAMP function takes an optional third argument specifying timezone, but Snowflake's timestamp functions handle timezone context differently, so preserving the original semantics requires explicit attention during conversion. Convert a TIMESTAMP down to a DATE in Snowflake and the time-of-day component disappears entirely, a loss that a row count check will never catch because the row is still there, just missing information. In Airbyte's BigQuery source mapping, DATE, DATETIME, TIMESTAMP, and TIME all get flattened into a String in ISO8601 format, which keeps the format readable but strips away the type safety that would otherwise catch a malformed value on entry.

Semi-structured types add another layer. JSON moving from BigQuery into Snowflake's VARIANT keeps the data intact but loses the schema enforcement BigQuery applied to it, meaning downstream functions that expect a certain shape may not get one. Arrays are subtler still: BigQuery requires every element in an ARRAY to share the same type, while Snowflake's ARRAY handling differs in how type uniformity is enforced. An array that was perfectly valid under BigQuery's rules may behave differently once it lands in Snowflake, depending on how the receiving type is configured.

None of this is purely structural, either. Null percentage spikes, sudden shifts in cardinality, and changes in value distribution are all signs that a type has effectively changed even when the schema metadata hasn't moved an inch. Both platforms' INFORMATION_SCHEMA views can surface pieces of this, but only if someone's actually querying them on a schedule and comparing results against a baseline.

A concrete failure: what the BigQuery-to-Snowflake NUMERIC-to-FLOAT conversion does to financial data

A table of financial transactions gets replicated from BigQuery into Snowflake, and this is a common scenario. A table of financial transactions gets replicated from BigQuery into Snowflake. The sync finishes clean. Row counts match on both sides, nothing in the load logs looks unusual, and everyone moves on. Six weeks later, someone in finance flags a total that's off by a few cents against the original source.

BigQuery's NUMERIC type stores exact decimal values, and that is why the conversion behaves the way it does. BigQuery's NUMERIC type stores exact decimal values. During the sync, that value passes through an intermediate number type and, unless the mapping was explicitly overridden, is FLOAT in Snowflake, an approximate type. Exact data goes in, approximate data comes out, and nothing in the pipeline throws a warning because, technically, nothing failed.

Fixing it after the fact turns out to be its own hazard. Converting the column to NUMBER(38,9) in place doesn't recover the original precision, it converts whatever FLOAT values are already sitting there, and any value with more than 29 integer digits gets set to NULL in the process. No metadata entry records that the conversion happened. And the precision that was lost the moment the data landed as FLOAT the first time around simply isn't recoverable, because the exact source values were never stored on the Snowflake side to begin with.

The lesson here is about timing, not financial data specifically. It's about timing. The mismatch had to be caught at setup, before the first load ever ran, because a post-load audit can only confirm that damage occurred. It has no way to undo it.

Audit techniques specific to Snowflake: querying INFORMATION_SCHEMA, catching VARIANT sprawl, and monitoring type widening

INFORMATION_SCHEMA.COLUMNS is the obvious starting point, and for good reason. It surfaces DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, and NUMERIC_SCALE for every column in a schema. Querying it on a regular schedule and diffing the results against a saved baseline will catch type changes as they happen. Because Snowflake refuses to let a column change type in place, any difference that shows up in that diff means something got dropped and rebuilt behind the scenes, and that signal should be treated as high severity rather than routine.

VARIANT columns need a separate strategy entirely, since they're built to absorb schema changes without complaint. Auditing them means actually querying the keys and value types stored inside, using FLATTEN paired with TYPEOF to see what's really in there. Tracking key cardinality over time catches new keys appearing, and INFORMATION_SCHEMA alone is not designed to surface this level of detail. Tracking the distribution of TYPEOF results catches the subtler case: a key that was always NUMBER quietly starting to show up as STRING in a fraction of rows, a behavioral mismatch that no structural check would ever flag.

Timestamp variants deserve their own line of inquiry too. It's worth confirming that TIMESTAMP_LTZ, TIMESTAMP_NTZ, and TIMESTAMP_TZ columns are actually being used the way the pipeline's documentation says they should be, since mixing variants inside a join or an aggregation can produce unexpected results. Querying INFORMATION_SCHEMA.COLUMNS for DATA_TYPE values and flagging anything that doesn't match documented intent closes that gap.

Type widening gets treated as harmless because Snowflake allows it natively, but it shouldn't be ignored. A VARCHAR column growing from 50 characters to 255 is a legal, supported operation, and it's also frequently a sign that a field switched from a fixed code to free-text input somewhere upstream. That's a semantic shift wearing the costume of a structural one, and it deserves a log entry and a review, not a shrug.

Audit techniques specific to BigQuery: INFORMATION_SCHEMA, SAFE_CAST patterns, and schema drift configuration limits

BigQuery's INFORMATION_SCHEMA.COLUMNS plays the same foundational role here. Querying DATA_TYPE, IS_NULLABLE, and COLUMN_DEFAULT on a schedule and comparing against a baseline catches the moment a REQUIRED field relaxes to NULLABLE, or the moment a type change slips through via table recreation. Since BigQuery doesn't support narrowing a type, the same logic applies here as in Snowflake: any type change in the diff means the table was rebuilt, and that should trigger a pipeline validation pass, not just a note in a log.

SAFE_CAST turns out to be one of the more useful audit primitives available, precisely because of how it fails. Instead of erroring out when a cast doesn't work, it returns NULL. Running it against a column where an upstream type change is suspected and then counting the resulting nulls against total row count gives a clean measure of how widespread the incompatibility is. A pattern like SELECT COUNTIF(SAFE_CAST(col AS NUMERIC) IS NULL) / COUNT(*) AS mismatch_rate run on a schedule turns a rising mismatch_rate into an early warning rather than a postmortem finding.

BigQuery's update-schema configuration handles routine drift reasonably well, covering the bulk of everyday cases like appended columns and relaxed constraints. But it stops short of anything destructive: type modifications, column drops, and renames all fall outside what that configuration was built to manage, and an audit that assumes those cases are covered is making a mistake that becomes visible in production later, usually at the worst time.

ML.VALIDATE_DATA_DRIFT is a specialized tool with a scope that is easy to overestimate, and it should not be treated as a substitute for structural type auditing. For actual type-level auditing, INFORMATION_SCHEMA and SAFE_CAST remain the right tools.

Cross-platform auditing: what to validate when data moves between BigQuery and Snowflake

Every cross-platform audit starts from the type mapping table, because that table is where the assumptions live, and assumptions are what drift exploits. BigQuery's TIMESTAMP maps to Snowflake's TIMESTAMP_TZ, not TIMESTAMP_NTZ, so confirming which variant a given pipeline is actually configured to use is not optional. DATETIME maps to TIMESTAMP_NTZ as something closer to an alias. JSON maps to VARIANT. INTERVAL maps to VARCHAR(30) by default unless someone's explicitly enabled the alternative. BYTES maps to BINARY. BIGNUMERIC maps to NUMERIC with precision truncated from 76 down to 38, carrying the same silent rounding risk described earlier. ARRAY maps to ARRAY, but element type enforcement doesn't survive the trip unless the structured ARRAY(T) form gets used deliberately to preserve it. And ANY TYPE, since it only exists as a function parameter in BigQuery rather than a column type, maps to VARIANT on the function side only.

Timezone handling deserves its own explicit check on any migration. Confirming that PARSE_TIMESTAMP's timezone argument in BigQuery SQL has a corresponding CONVERT_TIMEZONE wrapper on the Snowflake side preserves the source's time semantics, and skipping it quietly shifts every timestamp in the dataset by whatever offset got dropped along the way. A migration that skips this step won't fail. It'll just be wrong, in a way that looks completely normal until someone downstream notices the numbers don't add up, and by then the question is why the audit didn't catch it. It's why it didn't.