AdvancedSQL · 25 min

Type Casting: Advanced

At the platform layer, type casting is data-contract design. The warehouse's NUMERIC, the Iceberg table's DECIMAL, the Arrow IPC format's float64, the API's JSON Schema number all have to align. When they don't, data crosses the boundary, fails to deserialize, or silently truncates. The platform-grade conversation is about codec design, schema-aligned cross-system transport, custom domain types, and the role typed schemas play in lineage and data contracts. The interview reads you for whether you've designed the type-aligned data flow at scale, not just written the casts.

Architect codec design across systems (Arrow, Parquet, Iceberg, Avro, Protobuf)

Apply domain modeling with custom types (money, percentages, identifiers) for invariant enforcement

Integrate typed schemas with lineage and data contracts for platform-wide consistency

Reason about type-evolution governance: when types change, the platform mechanisms that catch incompatibilities

Storage Types and Query Performance

Daily Life

Interviews

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

The question that opens the platform-scale type conversation: 'we have data flowing from Postgres source databases through a Kafka stream to an Iceberg lake to a Snowflake warehouse, with consumers reading from Arrow IPC for ML serving. Walk me through the type system across the path.' This is not a CAST question. The platform-grade answer walks the type alignment at each boundary: Postgres NUMERIC to Avro decimal logical type, into Iceberg DECIMAL, into Snowflake NUMBER, into Arrow Decimal128. Each conversion has a precision contract; misalignment at any boundary loses data.

Three platform concerns

First: codec design. Each transport (Arrow, Parquet, Iceberg metadata, Avro, Protobuf) has its own type system. The platform's job is to align them so data crosses without loss. Second: domain types. Money should not be a float; identifiers should not be raw integers without semantic enforcement. Domain modeling at the type level catches a class of bugs that SQL alone doesn't catch. Third: type contracts in lineage. When a column's type changes, the platform's lineage tools and contract testing surface the change before downstream consumers break.

You are being tested on platform-scale typing when you hear:

▸"design the type alignment from Postgres to Iceberg to Snowflake to Arrow"
▸"how do we enforce money handling across the platform"
▸"a column's type changed; how do we catch it before downstream breaks"
▸"the precision is lost between the source and the warehouse"
▸"the Arrow IPC deserialization fails on some rows"

Implicit Casts That Defeat an Index

Daily Life

Interviews

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

The first platform concern is codec alignment. Data leaves a source database in one type system, traverses a serialization format, lands in a storage layer, and is read by a consumer with its own type system. Each step's type system has to align with the next; misalignment is data loss.

Type system mappings

Postgres NUMERIC(20,4) maps to Avro {type: bytes, logicalType: decimal, precision: 20, scale: 4}. Iceberg's DECIMAL(20, 4) reads that Avro and stores it natively. Snowflake's NUMBER(20,4) reads from Iceberg via the connector. Arrow's Decimal128 with the same precision and scale reads from Snowflake for ML serving. Each step is one explicit mapping; the precision contract is preserved. State this when designing: 'the type mapping is explicit at every boundary; the platform documents the canonical mapping per source-target pair.'

Where alignment breaks

Default mappings often choose the wrong target. Default Postgres-to-Avro might map NUMERIC without precision to STRING (because Avro doesn't have unbounded numeric). Default Iceberg-to-Snowflake might use NUMBER(38,9), losing precision if the source was wider. The platform discipline is to override defaults with explicit mappings. State this when designing: 'never use defaults for production type mappings; declare the source-target precision explicitly.'

Arrow as the lingua franca

Apache Arrow has become the cross-system type standard for high-performance data movement. Arrow's type system covers most warehouse types (Decimal128, Decimal256, Timestamp, Date32, structs, lists, dictionaries). Many warehouses now serve Arrow IPC natively (BigQuery, Snowflake via Snowflake Arrow IPC, DuckDB). The platform pattern is to use Arrow as the on-wire format and ensure the type mappings at each boundary preserve Arrow-compatibility. State this when designing: 'Arrow is the cross-system type contract; the warehouse-to-consumer path passes through Arrow IPC for type-safe high-performance transport.'

Iceberg's type evolution rules

Iceberg formalizes type evolution rules in its specification: allowed evolutions are widening (INT to LONG, FLOAT to DOUBLE, DECIMAL to wider DECIMAL); forbidden are narrowing and incompatible changes. The catalog rejects incompatible writes. This is the schema-evolution governance at the storage layer; the platform inherits the discipline by using Iceberg. State this when designing: 'Iceberg's schema evolution rules are the contract; the catalog enforces compatibility on every write.'

Numeric Overflow and Precision Loss at Scale

Daily Life

Interviews

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

Domain types are custom types that encode business invariants. Money is not a float; it's a precise decimal with a currency. An identifier is not an integer; it's a typed token that should not accidentally be added to another integer. Most warehouses don't support custom types directly, but the platform pattern is to enforce the invariants through wrapper functions, validation rules, and code conventions.

Money handling

	'CREATE TABLE transactions (
	transaction_id BIGINT NOT NULL,
	amount NUMERIC( 20 , 4) NOT NULL,
	currency CHAR( 3) NOT NULL,
	CONSTRAINT amount_positive CHECK( amount >= 0)
	)'
	/* Money as NUMERIC with a documented scale and a separate currency column */
	/* explicit precision; no FLOAT */
	/* ISO 4217 currency code */
	/* Aggregates within a currency are safe; cross-currency requires conversion */

The discipline: money columns are NUMERIC with explicit precision (not FLOAT); the currency is a separate column; cross-currency aggregates require a conversion step. The platform's reporting layer enforces these conventions; queries that SUM amounts across currencies without conversion are caught at code review or by dbt tests. State this when designing: 'every money column has a currency; cross-currency operations are explicit; FLOAT in a money column is a CI lint failure.'

Identifier safety

Customer IDs and order IDs should not be addable. SUM(customer_id) is nonsense but legal SQL. The platform pattern is to use type names (or comments) that signal the semantic, plus dbt or code-review enforcement that catches arithmetic on identifier columns. Some engines support distinct types (Postgres CREATE DOMAIN) that make customer_id a typed token; queries that try to add two customer_ids get a type error. State this when designing: 'identifiers are typed tokens; the platform enforces that arithmetic on identifiers is a bug.'

Percentage and ratio types

Conversion rates, retention rates, and other ratios are always between 0 and 1 (or 0 and 100). The platform pattern is to validate the range at write time and document the unit. A retention column that should be 0-1 sometimes stores 0-100 by accident; downstream consumers multiply by 100 again and get 100x the correct value. dbt tests for accepted ranges catch the bug at CI time. State this when designing: 'ratio columns have documented ranges and validation tests; the unit (0-1 vs 0-100) is part of the column contract.'

At Stripe in 2025, the platform team published an internal type-handling standard after a quarter where three separate incidents traced back to money-as-float drift. The standard: every money column is NUMERIC(38,9) with the currency in a paired column; aggregates within a currency are allowed; cross-currency aggregates require an explicit conversion through the rates service; FLOAT in any column whose name contains 'amount' or 'price' or 'cost' is a CI failure. The standard's enforcement layer (dbt tests, CI lints, code review) caught 100% of post-rollout money-float regressions in the first year. The runbook line is 'money handling is platform-wide discipline; the cost of one float-related incident exceeds the engineering cost of enforcing the standard across hundreds of models.'

Cast-Heavy Predicates and Plan Quality

Daily Life

Interviews

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

At platform scale, type changes ripple. When a column's type changes from NUMERIC(10,2) to NUMERIC(20,4), every downstream pipeline that reads it has to handle the change. The platform's lineage and contract testing surface the change before consumers break.

Schema contracts with type declarations

dbt contracts allow per-column type declarations on model outputs. The contract specifies the type and any constraints (NOT NULL, accepted ranges). When the model produces output that violates the contract, the dbt run fails. The contract is the cross-team boundary; producers commit to it, consumers depend on it. State this when designing: 'every shared model has a typed contract; type changes require a contract version bump and a coordinated downstream migration.'

Type-aware lineage

Column-level lineage tools (Datafold, OpenLineage, dbt's compiled DAG) track which downstream columns depend on which upstream columns. When the upstream's type changes, the tool flags every downstream column that depends on it. The platform team uses the lineage report to coordinate the migration. Without type-aware lineage, type changes propagate as silent failures; with it, the propagation is visible and planned.

Cross-system type registries

Schema registries (Confluent Schema Registry, Iceberg's catalog, Hive Metastore) track typed schemas with versioning. The registry's compatibility check catches incompatible type changes at producer write time. The platform pattern is to make the registry the single source of truth for column types; downstream systems read the registry to know how to deserialize. This is the cross-system version of dbt contracts. State this when designing: 'the registry is the type source-of-truth; producers register schemas; consumers read against registered versions; the platform team owns the compatibility policy.'

Fixing Type Bugs Upstream vs in Query

Daily Life

Interviews

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

The last platform concern is the operational layer: the mechanisms that catch type violations in production, the runbooks for type migrations, the dashboards that surface type-related metrics.

Type-quality metrics

The platform tracks metrics about types: how often safe-cast returns NULL (signals upstream drift); how often a column's type changes (signals migration cadence); how often dbt contract tests fail (signals breaking changes that almost shipped). These metrics surface type discipline as a measurable property; the team can target improvement. State this when designing: 'type-quality is observable; the platform tracks the metrics and reports them on a dashboard.'

Type-migration runbook

When a type changes, the platform follows a documented runbook. Announce the change with timeline. Add the new column or alter the existing one (depending on the engine and change). Backfill historical data with explicit casts. Update dbt contracts and lineage references. Run downstream tests. Monitor for unexpected NULL or precision-loss patterns. Retire the old column after the deprecation window. The runbook is the discipline; without it, every migration becomes a one-off improvisation.

The closing thought

Platform-scale type casting is codec design, domain modeling, type contracts, and operational discipline. The patterns compose: Arrow as the cross-system codec; NUMERIC with currency for money; dbt contracts for type-aware deployment; schema registries for cross-system versioning; type-quality metrics for ongoing observability. The candidate who walks these layers is the platform engineer; the candidate who treats types as 'whatever the column was declared' is the one who hasn't yet built the multi-system data platform. Types at this scale are not syntax; they are the contract that makes the platform reliable.

❯❯❯PUTTING IT ALL TOGETHER

> You are in a data engineering interview at a financial platform. The interviewer asks: 'We have data flowing from Postgres source databases through a Kafka stream to an Iceberg lake to a Snowflake warehouse, with consumers reading from Arrow IPC. Walk me through the type system across the path.'

You walk the boundaries: 'Postgres NUMERIC(20,4) maps to Avro decimal logical type with explicit precision. Iceberg DECIMAL(20,4) reads that. Snowflake NUMBER(20,4) via the connector. Arrow Decimal128 with the same precision and scale.'

Defaults: 'Default mappings often pick the wrong target. Postgres NUMERIC without precision can map to STRING in some defaults; we override with explicit precision at every boundary.'

Domain types: 'Money is NUMERIC with paired currency column. FLOAT for money is a CI lint failure. Cross-currency aggregates require explicit conversion through a rates service.'

Follow-up: 'A column's type changed; how do we catch it before consumers break?' You say: 'dbt contracts with typed declarations; CI fails on contract violations. Column-level lineage flags downstream models that depend on the changed column. Schema registry's compatibility check catches it at producer write time.'

Follow-up: 'How do we know the type discipline is working?' You say: 'Track type-quality metrics: safe-cast NULL rate, type change frequency, contract test failures. The metrics surface type discipline as a measurable property.'

Closing: 'Codec design, domain modeling, type contracts, operational discipline. The patterns compose; the architecture is the deliverable. Types at this scale are the contract that makes the platform reliable.'

KEY TAKEAWAYS

Every hop across the platform is an explicit type mapping with a precision contract: Postgres NUMERIC(20,4) to an Avro decimal logical type, into Iceberg DECIMAL(20,4), into Snowflake NUMBER(20,4), into Arrow Decimal128. Misalignment at any boundary loses data.

Never accept default mappings in production. A default Postgres-to-Avro conversion can turn an unbounded NUMERIC into a string, and a default Iceberg-to-Snowflake mapping to NUMBER(38,9) silently narrows a wider source.

Money is NUMERIC with explicit precision plus a paired ISO 4217 currency column, never a float, and cross-currency aggregates require an explicit conversion step. Stripe's standard after three money-as-float incidents made a float in any column named amount, price, or cost a CI failure.

Encode business invariants that SQL will not enforce on its own: SUM(customer_id) is nonsense but legal, and a ratio column that stores 0 to 100 where consumers expect 0 to 1 produces a 100x error. Postgres CREATE DOMAIN and dbt accepted-range tests are where those contracts live.

Iceberg permits only widening evolution (INT to LONG, FLOAT to DOUBLE, a decimal to a wider decimal) and the catalog rejects narrowing writes, which makes the storage layer itself the governance boundary for type changes.

Make type drift observable and rehearsed: track how often safe-cast returns NULL, how often contract tests fail, and use column-level lineage to identify every downstream consumer before a migration. The runbook is announce, add or alter, backfill with explicit casts, update contracts, test downstream, monitor, then retire the old column.

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

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

Topics covered: Storage Types and Query Performance, Implicit Casts That Defeat an Index, Numeric Overflow and Precision Loss at Scale, Cast-Heavy Predicates and Plan Quality, Fixing Type Bugs Upstream vs in Query

Lesson Sections

Storage Types and Query Performance (concepts: sqlCast)
The question that opens the platform-scale type conversation: 'we have data flowing from Postgres source databases through a Kafka stream to an Iceberg lake to a Snowflake warehouse, with consumers reading from Arrow IPC for ML serving. Walk me through the type system across the path.' This is not a CAST question. The platform-grade answer walks the type alignment at each boundary: Postgres NUMERIC to Avro decimal logical type, into Iceberg DECIMAL, into Snowflake NUMBER, into Arrow Decimal128.
Implicit Casts That Defeat an Index (concepts: sqlCast)
The first platform concern is codec alignment. Data leaves a source database in one type system, traverses a serialization format, lands in a storage layer, and is read by a consumer with its own type system. Each step's type system has to align with the next; misalignment is data loss. Type system mappings Postgres NUMERIC(20,4) maps to Avro {type: bytes, logicalType: decimal, precision: 20, scale: 4}. Iceberg's DECIMAL(20, 4) reads that Avro and stores it natively. Snowflake's NUMBER(20,4) rea
Numeric Overflow and Precision Loss at Scale (concepts: sqlDecimalType)
Domain types are custom types that encode business invariants. Money is not a float; it's a precise decimal with a currency. An identifier is not an integer; it's a typed token that should not accidentally be added to another integer. Most warehouses don't support custom types directly, but the platform pattern is to enforce the invariants through wrapper functions, validation rules, and code conventions. Money handling The discipline: money columns are NUMERIC with explicit precision (not FLOAT
Cast-Heavy Predicates and Plan Quality (concepts: sqlDateFormat)
At platform scale, type changes ripple. When a column's type changes from NUMERIC(10,2) to NUMERIC(20,4), every downstream pipeline that reads it has to handle the change. The platform's lineage and contract testing surface the change before consumers break. Schema contracts with type declarations dbt contracts allow per-column type declarations on model outputs. The contract specifies the type and any constraints (NOT NULL, accepted ranges). When the model produces output that violates the cont
Fixing Type Bugs Upstream vs in Query (concepts: sqlStorageOptimization)
The last platform concern is the operational layer: the mechanisms that catch type violations in production, the runbooks for type migrations, the dashboards that surface type-related metrics. Type-quality metrics The platform tracks metrics about types: how often safe-cast returns NULL (signals upstream drift); how often a column's type changes (signals migration cadence); how often dbt contract tests fail (signals breaking changes that almost shipped). These metrics surface type discipline as