IntermediateSQL · 25 min

Type Casting: Intermediate

Past the basic CAST and TRY_CAST patterns, type casting in real DE work becomes a design question. Timezone-aware vs naive timestamps. Cross-engine schema migrations where the source's NUMERIC(20,4) becomes the target's DECIMAL(38,9). Type coercion in JOINs that subtly changes index usage. Schema evolution where a column's type changes between versions. The patterns are about treating types as part of the contract, not just the syntax that gets the query to compile.

Handle timezone-aware and naive timestamps deliberately; the difference matters for correctness

Manage type compatibility across engines during migrations and federation

Reason about type coercion in JOIN conditions and its effect on indexes

Handle schema evolution where a column's type changes between versions

TRY_CAST and Safe Conversion

Daily Life

Interviews

Spot questions where type casting is the hidden requirement: string dates, numeric strings, decimal precision.

The question that recurs in interviews involving multi-system data: 'we ingest from a Postgres source whose timestamps are TIMESTAMPTZ; we land them in a BigQuery warehouse whose TIMESTAMP is timezone-naive UTC. Walk me through what could go wrong.' This is not a CAST question. The candidate who answers with 'I'd cast at the boundary' is missing the conversation about what each engine's type means and what the correct conversion is. The platform-grade answer walks the type semantics and names the conversion path.

Three concerns this lesson covers

First: timestamp types and timezones. TIMESTAMP, TIMESTAMPTZ, DATETIME each have different semantics across engines; casting between them needs to be deliberate. Second: cross-engine type compatibility. Postgres NUMERIC, Snowflake NUMBER, BigQuery NUMERIC have different precision rules; migrations and federation queries hit the differences. Third: schema evolution casts. When a column's type changes (INT to BIGINT, NUMERIC(10,2) to NUMERIC(20,4)), the migration requires explicit casting and downstream code may need updates. Each pattern fits a real-world failure mode.

You are being tested on intermediate casting when you hear:

▸"the source is TIMESTAMPTZ; the target is TIMESTAMP; conversion?"
▸"NUMERIC(20,4) vs DECIMAL(38,9) in cross-engine migration"
▸"the JOIN is slow because one column is INT and the other is BIGINT"
▸"the column type changed last release; downstream queries need updates"
▸"the precision differs; we're losing pennies on aggregation"

String-to-Date and Date-to-String

Daily Life

Interviews

Write explicit type conversions and use TRY_CAST to handle dirty data that fails conversion.

Timestamp types are the type-casting area that catches the most engineers. The same logical value (a specific moment in time) can be stored as TIMESTAMP (no timezone, just date and time), TIMESTAMPTZ (an absolute moment with timezone awareness), or DATETIME (engine-specific semantics). Casting between them is not always lossless; conventions differ across engines.

The three timestamp flavors

TIMESTAMP (without timezone): stores a date and time, no zone information. When displayed, the engine assumes either UTC or the session zone, depending on the engine. TIMESTAMPTZ (with timezone): stores an absolute moment; on display, converts to the session's timezone. The internal representation is usually UTC. DATETIME: BigQuery-specific; like TIMESTAMP but with different defaults. State this when designing: 'know the engine's default for each type; the same name has different semantics across engines.'

Casting TIMESTAMPTZ to TIMESTAMP


	SELECT
	ts_tz :: TIMESTAMP
	FROM events ;


	SELECT(ts_tz AT TIME ZONE 'UTC') :: TIMESTAMP
	FROM events ;

Casting TIMESTAMPTZ to TIMESTAMP drops the timezone information. The conversion happens in some timezone; which one is the question. Postgres defaults to the session timezone, which means the result varies per user. The fix is to specify the timezone explicitly: convert to UTC first, then cast. The pattern is the same as the date_arithmetic lesson's discipline: storage UTC, computation deliberate, display local. State this when writing: 'I convert to UTC explicitly before dropping the zone; otherwise the result depends on session settings.'

Engines differ on default behavior

Snowflake's default zone is configurable per account. BigQuery's TIMESTAMP is always UTC; DATETIME is naive. Postgres's TIMESTAMPTZ stores UTC internally but converts on display to the session zone. The same cast on the same value can produce different strings depending on the engine and session. State this when designing: 'document the timezone convention per column in the catalog; downstream queries assume the documented convention.'

The DST conversion edge case

When casting TIMESTAMPTZ to TIMESTAMP via local-time conversion, the daylight saving boundaries produce edge cases. The 2 AM hour skipped in March doesn't exist in local time; the 1 AM hour repeated in November exists twice. Most engines pick one; some throw. Casting through UTC avoids these issues because UTC has no DST. State this when designing: 'cast through UTC for safety; local-time conversion across DST is fragile.'

Decimal Precision and Rounding

Daily Life

Interviews

Avoid the integer division trap (5/2=2) by casting to DECIMAL before dividing, and control precision in financial calculations.

When data flows between engines (Postgres to Snowflake via ETL; BigQuery to Iceberg via export; federated queries across systems), the type systems have to align. Each engine has its own precision rules, default sizes, and edge cases. Migrations and federations are where the differences surface.

Numeric precision differences

Postgres NUMERIC defaults to unlimited precision; specifying NUMERIC(10,2) gives 10 total digits with 2 after the decimal. Snowflake NUMBER defaults to (38,0); use NUMBER(10,2) for explicit precision. BigQuery NUMERIC is (38,9); BIGNUMERIC is (76,38). Casting a Postgres NUMERIC(10,4) to BigQuery NUMERIC works because (10,4) fits in (38,9); casting NUMERIC(38,12) to BigQuery NUMERIC loses precision (the scale of 12 exceeds 9). State this when migrating: 'the precision contract has to fit the target; otherwise data is silently truncated.'

Integer sizes

	-- Integer types by engine
	-- Postgres: SMALLINT (2 bytes), INTEGER (4), BIGINT (8)
	-- Snowflake: NUMBER(38,0) for all integers; uses precision-aware storage
	-- BigQuery: INT64 (8 bytes) only; no smaller integer types
	-- SQL Server: TINYINT (1), SMALLINT (2), INT (4), BIGINT (8)

Casting INT to BIGINT is safe (widening). Casting BIGINT to INT can overflow if the value exceeds 2^31. The migration pattern is to use the largest target type; the storage cost is minimal compared to the migration risk. State this when designing: 'INT to BIGINT for safety; only narrow when the value range is bounded.'

String length

Postgres TEXT and VARCHAR are unbounded by default; specifying VARCHAR(100) enforces a max length. Snowflake VARCHAR is up to 16MB. BigQuery STRING is up to 10MB. Casting VARCHAR(100) to a wider varchar is safe; casting to a narrower one truncates with engine-specific behavior (some warn, some silently truncate, some throw). State this when migrating: 'expand string lengths during migration; never narrow without auditing actual values.'

At Snowflake migrating to ICCID-based identifier columns in 2024, the platform team published a migration playbook for handling cross-engine type compatibility: every cross-engine ETL pipeline declared a 'wider target type policy' that converted source types to the widest compatible target. The playbook reduced cross-engine ETL incidents by 60% in the first six months. The runbook line is 'cross-engine migrations widen types by default; the storage cost is amortized across years of safety.'

Boolean and NULL Cast Edge Cases

Daily Life

Interviews

Parse dates from strings using TO_DATE/PARSE with format strings, and format dates back to strings for output.

When joining columns of different types (INT to BIGINT, VARCHAR(50) to VARCHAR(100), or worse, NUMERIC to VARCHAR), the engine inserts an implicit cast. The cast can prevent index usage and can change the comparison semantics. The discipline is to match the types on both sides of the join.

The mismatched-type join

	/* Join where one side is INT and the other is BIGINT */
	SELECT
	*
	FROM orders AS o
	INNER JOIN customers AS c
	ON o.customer_id = c.customer_id /* If o.customer_id is INT and c.customer_id is BIGINT, the engine casts INT to BIGINT / / for the comparison. Usually the cast is fine; sometimes it prevents index pruning. */

Most cases where both sides are integer types of different widths work fine; the engine handles the widening cast efficiently. The case that bites is comparing a typed column to a string literal: WHERE customer_id = '12345' (where customer_id is INT) forces the engine to cast every customer_id to TEXT for the comparison. The cast prevents using an integer index. The fix is to write the literal as the right type: WHERE customer_id = 12345.

The string-vs-typed-column trap

Joining a string column to a numeric column is the canonical type-coercion bug. The engine has to cast one side; usually it casts the typed side to text. The query runs but doesn't use indexes; the slowness is invisible until production. State this when reviewing: 'both sides of every JOIN match types; type mismatches are a perf bug in disguise.'

Aligning types during ingestion

The platform pattern is to enforce type alignment at ingestion. If customer_id is INT in one source and BIGINT in another, the warehouse layer casts both to BIGINT before downstream models reference them. The alignment happens once at the silver layer; downstream queries reference the aligned types. State this when designing: 'silver layer enforces consistent types across sources; downstream models inherit the type contract.'

Dialect Differences in Casting Rules

Daily Life

Interviews

Discuss how column types affect storage size, join performance (int vs string keys), and predicate pushdown eligibility.

The last common intermediate pattern: schema evolution. A column's type changes between releases (INT to BIGINT to support larger values; NUMERIC(10,2) to NUMERIC(20,4) for higher precision; TIMESTAMP to TIMESTAMPTZ for timezone awareness). The migration needs explicit casting and downstream code may need updates.

The migration pattern

Three steps. First: alter the column to the new type (or add a new column with the new type). Second: backfill historical data with explicit casts. Third: update downstream queries that may have been written assuming the old type. The discipline is to test the downstream queries before declaring the migration complete; some queries that worked on INT may behave subtly differently on BIGINT (especially around overflow comparisons).

Dual-typed columns during migration

For long-running migrations, the pattern is to add a new column alongside the old one. The new column gets populated with the new type; downstream queries gradually shift to the new column; the old column is dropped after a deprecation window. State this when designing: 'dual-typed columns during the migration window let consumers switch on their own cadence; the old column drops after deprecation.'

Versioned schema contracts

Like the JSON schema registry pattern from the semi-structured lesson, typed schemas benefit from versioning. dbt contracts allow per-column type declarations; CI fails when a model's output doesn't match the contract. The contract is the boundary; type changes go through the contract review. State this when designing: 'typed columns have versioned contracts; changes are coordinated through the contract layer.'

The closing thought

Intermediate casting is timestamp types, cross-engine compatibility, JOIN type alignment, and schema evolution. Each pattern fits a real-world DE workload; the discipline is to treat types as part of the data contract, not just the SQL that compiles. The candidate who walks the type-as-contract conversation is the one who has shipped through migrations; the candidate who treats types as 'whatever makes the query run' is the one who hasn't yet.

❯❯❯PUTTING IT ALL TOGETHER

> You are in a data engineering interview at a multi-warehouse company. The interviewer asks: 'We ingest from a Postgres source whose timestamps are TIMESTAMPTZ; we land them in a BigQuery warehouse whose TIMESTAMP is timezone-naive UTC. Walk me through what could go wrong.'

You name the type semantics: 'TIMESTAMPTZ on Postgres stores UTC internally; the display converts to session timezone. BigQuery TIMESTAMP is always UTC; DATETIME is naive. Casting TIMESTAMPTZ to TIMESTAMP needs an explicit zone choice.'

Conversion: 'Cast through UTC explicitly. (ts_tz AT TIME ZONE \'UTC\')::TIMESTAMP. Otherwise the result depends on session settings.'

Follow-up: 'NUMERIC(20,4) on the source; the target is BigQuery NUMERIC.' You say: 'BigQuery NUMERIC is (38,9). The (20,4) fits, so no precision loss. If the source had (38,12), the scale of 12 exceeds 9 and we'd lose precision. The migration policy is widen by default.'

Follow-up: 'A JOIN is slow.' You say: 'Check type compatibility on both sides. INT to BIGINT is fine; INT to TEXT (or implicit cast on a literal) breaks index pruning. The silver layer enforces type alignment.'

Closing: 'Type casting at this depth is about treating types as part of the contract. Document the conventions, align types at the silver layer, version schema changes through the contract layer.'

KEY TAKEAWAYS

Casting TIMESTAMPTZ to TIMESTAMP drops the zone in whichever zone the session happens to be in, so write (ts_tz AT TIME ZONE 'UTC')::TIMESTAMP and make the conversion explicit; UTC also has no DST, which removes the skipped and repeated local hours.

The same type name means different things per engine: BigQuery TIMESTAMP is always UTC while DATETIME is naive, Snowflake's default zone is an account setting, and Postgres TIMESTAMPTZ stores UTC but displays in the session zone, so document the convention per column.

Cross-engine precision has to fit the target: Postgres NUMERIC(10,4) lands cleanly in BigQuery NUMERIC(38,9), but NUMERIC(38,12) silently loses scale, and BigQuery offers only INT64 where Postgres has four integer widths.

Widen by default during migration. INT to BIGINT is safe, narrowing can overflow past 2^31, and narrowing a VARCHAR truncates with behavior that varies by engine between warning, silent cut, and error.

A typed column compared to a string literal, as in WHERE customer_id = '12345', forces a cast on every row and kills index use; align types on both sides of every join at the silver layer so downstream models inherit one type contract.

Schema evolution runs as alter or add, backfill with explicit casts, then update downstream queries, with a dual-typed column during long migrations and a versioned contract that fails CI when a model's output type drifts.

Implicit casts hide bugs that only show up in production at 2 AM

Category: SQL
Difficulty: intermediate
Duration: 25 minutes
Challenges: 0 hands-on challenges

Topics covered: TRY_CAST and Safe Conversion, String-to-Date and Date-to-String, Decimal Precision and Rounding, Boolean and NULL Cast Edge Cases, Dialect Differences in Casting Rules

Lesson Sections

TRY_CAST and Safe Conversion (concepts: sqlCast)
The question that recurs in interviews involving multi-system data: 'we ingest from a Postgres source whose timestamps are TIMESTAMPTZ; we land them in a BigQuery warehouse whose TIMESTAMP is timezone-naive UTC. Walk me through what could go wrong.' This is not a CAST question. The candidate who answers with 'I'd cast at the boundary' is missing the conversation about what each engine's type means and what the correct conversion is. The platform-grade answer walks the type semantics and names th
String-to-Date and Date-to-String (concepts: sqlCast)
Timestamp types are the type-casting area that catches the most engineers. The same logical value (a specific moment in time) can be stored as TIMESTAMP (no timezone, just date and time), TIMESTAMPTZ (an absolute moment with timezone awareness), or DATETIME (engine-specific semantics). Casting between them is not always lossless; conventions differ across engines. The three timestamp flavors TIMESTAMP (without timezone): stores a date and time, no zone information. When displayed, the engine ass
Decimal Precision and Rounding (concepts: sqlDecimalType)
When data flows between engines (Postgres to Snowflake via ETL; BigQuery to Iceberg via export; federated queries across systems), the type systems have to align. Each engine has its own precision rules, default sizes, and edge cases. Migrations and federations are where the differences surface. Numeric precision differences Postgres NUMERIC defaults to unlimited precision; specifying NUMERIC(10,2) gives 10 total digits with 2 after the decimal. Snowflake NUMBER defaults to (38,0); use NUMBER(10
Boolean and NULL Cast Edge Cases (concepts: sqlCast)
When joining columns of different types (INT to BIGINT, VARCHAR(50) to VARCHAR(100), or worse, NUMERIC to VARCHAR), the engine inserts an implicit cast. The cast can prevent index usage and can change the comparison semantics. The discipline is to match the types on both sides of the join. The mismatched-type join Most cases where both sides are integer types of different widths work fine; the engine handles the widening cast efficiently. The case that bites is comparing a typed column to a stri
Dialect Differences in Casting Rules (concepts: sqlStorageOptimization)
The last common intermediate pattern: schema evolution. A column's type changes between releases (INT to BIGINT to support larger values; NUMERIC(10,2) to NUMERIC(20,4) for higher precision; TIMESTAMP to TIMESTAMPTZ for timezone awareness). The migration needs explicit casting and downstream code may need updates. The migration pattern Three steps. First: alter the column to the new type (or add a new column with the new type). Second: backfill historical data with explicit casts. Third: update