Type Casting Belongs Between the L and the T

A few years ago I joined a large international wholesaler as a data architect. They ran a European data platform on Snowflake, and the ambition was to merge it with the other regional warehouses into one global platform. Part of my job was to prepare a transition to dbt. That started with an inventory of what we already had: hundreds of handwritten transformation scripts, manually curated over the years. We wanted to understand what we could salvage.

Tracing the flow from staging into the downstream models, I found many columns typed as strings. Dates and amounts were character columns, cast by hand in the scripts that consumed them. The sources were mostly SQL Server and Oracle databases with typed schemas. The information existed at the source, but the warehouse code had to reconstruct it column by column.

In an extract, load, transform (ELT) workflow, an ingestion tool moves data into the data platform and a transformation tool such as dbt builds the models above it. That division leaves a practical question: what types should the transformation author expect to receive?

The dbt guidance on staging  puts type casting in models that correspond one-to-one with source tables, alongside renaming and basic computations. Centralizing those operations there lets downstream models reuse them. It is a sensible place to repair a type once the raw table has arrived.

For a migration like ours, the reason for each conversion matters. A cast embedded in a transformation could be restoring the source type, compensating for a loading problem, or expressing a business choice. The SQL alone does not necessarily tell you which. Before replacing it, I would want to trace the column back through ingestion.

Some type mapping is unavoidable when the source and destination are different databases. Their types and constraints do not always match. Mapping a fixed-length character field to a general text field may be acceptable for analysis, while loading an amount as text leaves consumers responsible for converting it before they can sum it. Both deserve an explicit mapping, even if the first needs little attention.

The transport format does not settle this by itself. Singer  sends JSON records together with schema messages, including types and formats such as date-time. A JSON-based pipeline can therefore carry information beyond the values in each record. What matters is whether the extractor captures the relevant source properties and the loader uses them to create suitable destination columns. We need to inspect that whole path before deciding which conversions remain necessary.

Handwritten casts can make those decisions hard to find. A dbt forum discussion from 2020  shows developers explicitly casting columns to control their output types. The SQL is straightforward. Across a large project, reviewing each declaration and keeping it aligned with the source is the work. A declaration copied from another model can look perfectly reasonable while using the wrong precision.

When we redesigned the wholesaler’s target state, we converged on a single ingestion approach so staged data would match the source types more closely. Even then, the ingestion tool gave us limited freedom to adjust its mappings. Changing that foundation meant refactoring significant parts of the transformation code. The existing scripts had been written for the strings they received, and improving ingestion did not make those dependencies disappear.

I would make source-type normalization a distinct step between loading and business transformation. It could still be implemented as generated SQL or views in a dbt project. The important boundary is that the mapping is declared once, with its error policy, and downstream authors can rely on it.

For a database source, its catalog supplies much of the starting information. Capture the source types and relevant constraints, choose the destination mapping, and generate the normalization layer from that metadata. The source does not decide everything for us. Precision limits, timezone handling, and unsupported types can require engineering decisions. Once agreed, those decisions should become reusable rules rather than assumptions repeated in model code.

A CSV file or a loosely typed API response needs more care. Profiling can suggest that a field contains dates, but a sample cannot guarantee what the next delivery will contain. Someone needs to approve the expected type and decide what happens when a value fails to convert. Rejecting a batch and quarantining a record are different policies; silently returning null should not become the default by accident.

The generated layer also needs checks when a schema changes. A source widening an amount field may require a new destination type. Automating the mapping saves repeated SQL authoring, but it does not remove the need to detect that change and review its consequences.

At the wholesaler, a shared mapping would have given us a clear place to inspect those decisions during the migration. That is what I want from the boundary between L and T: a model author should be able to find out why a column has its type without tracing years of transformation scripts.

Carrying source types from the contract into typed target tables is part of how we’re designing Amfelt’s pipeline layers; configurable error policies are not built yet: Join the Waitlist .