Upstream Fix vs Query-Side Cast

Concepts covered: Type Casting, Safe Casting, Null Handling

The interview version: "An analyst found that amount lands in the warehouse as a string, so they added CAST(amount AS DECIMAL(20,4)) to their dashboard query. Ship it, or fix it upstream?" Either answer can be right. What gets graded is whether the choice follows from the situation, and whether the candidate notices that a cast inside one dashboard fixes the type for exactly one reader.

Where the bug was born

A common birthplace is the connector. Debezium's decimal.handling.mode decides how a PostgreSQL NUMERIC travels: precise sends an exact decimal and string sends text. double sends a binary float, which reintroduces every rounding problem the source column was declared to avoid. A string amount in a warehouse is very often a connector setting chosen once, years ago, to make a deserializer error go away. Fixing it there corrects every node downstream at once.

The decision, situation by situation

The second row is where most strong answers land: 1 cast, in 1 place, owned by 1 team. A staging model that converts the type once, with every downstream model reading from it, gives the speed of a query-side patch without the drift of many, and leaves a single line to delete when the source is corrected.

-- stg_payments: the only place amount is converted
SELECT payment_id,
       TRY_CAST(amount_raw AS DECIMAL(20, 4)) AS amount,
       currency,
       amount_raw,
       ingested_at
FROM raw_payments;

-- Daily reject check: alerts when unconvertible amounts appear
SELECT CAST(ingested_at AS DATE) AS ingest_date,
       COUNT(*) AS rejected_rows
FROM raw_payments
WHERE amount_raw IS NOT NULL
  AND TRY_CAST(amount_raw AS DECIMAL(20, 4)) IS NULL
GROUP BY CAST(ingested_at AS DATE);

The upstream fix then lands as an enforced contract. A dbt model contract declares each output column's data_type and fails the build when the model produces anything else; a schema registry rejects an incompatible producer schema at write time. Either one turns the next type drift into a failed deploy instead of a wrong dashboard.

models:
  - name: fct_payments
    config:
      contract:
        enforced: true
    columns:
      - name: payment_id
        data_type: bigint
      - name: amount
        data_type: numeric(20,4)
      - name: currency
        data_type: char(3)

Overreach in both directions

This question has a failure mode on each side. Demanding a source-system change and a registry policy for a one-off analysis of a vendor CSV reads as someone who cannot ship. Accepting the dashboard cast when the same column feeds the reconciliation join reads as someone who has never inherited 40 models carrying 40 slightly different casts. The interviewer is waiting for the sentence that names the readers: "Who else reads this column, and what does each of them do with it today?"

Ask that question about the reconciliation job and the answer is clear. The payment_ref column feeds a join over 2.4 billion rows every night, the per-row conversion is paid by every consumer, and the padded refs prove the text carries spellings the BIGINT never had. The fix belongs in the ledger team's schema, with a single staging cast and its reject count holding the line until that change ships.

Do

• Ask what type each side of a join or filter holds before tuning compute
• Count rows where 2 predicates disagree before moving a cast
• Widen an operand inside SUM, and use COUNT_BIG on large SQL Server tables
• Upgrade every reader before a producer writes a wider type
• Keep each query-side cast in 1 staging model with a reject count

Avoid

• Land join keys as text because the source sent text, and the same goes for dates and money
• Sum money in FLOAT or DOUBLE and expect 2 runs to agree
• Run INT to BIGINT as one ALTER on a large PostgreSQL table taking writes
• Let TRY_CAST turn bad values into NULLs nobody counts
• Demand a source fix for a one-off analysis over a feed you do not own

About this lesson

This section is part of the Type Casting: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.