Data Types and CAST

Data Types and CAST

Why a DE casts at the boundary. Source data arrives with the wrong types constantly: an amount stored as the text '4999', a flag as '1'.

In the warehouse this differs. SQLite has dynamic typing with type affinity: it will happily store the string 'oops' in a column you declared INTEGER, and arithmetic on text may silently coerce or return 0. Postgres and Snowflake are strict: they reject a bad value at write time. Because your DDL should port to those strict systems, the DE habit is to CAST explicitly at the boundary rather than trust the source's typing.

CAST. CAST(expr AS type) converts a value. Common targets: INTEGER, REAL (float), TEXT. CAST('4999' AS INTEGER) → 4999; you can now do arithmetic on it reliably.

Worked example:

SELECT
  order_id,
  CAST(total_cents_text AS INTEGER)            AS total_cents,
  CAST(total_cents_text AS INTEGER) / 100.0    AS total_dollars
FROM orders_raw;

Anatomy.

CAST( total_cents_text  AS  INTEGER )
      └─ the value ──┘      └ target type ┘

Guarding junk. If a text column may hold non-numeric junk, casting it in SQLite yields 0 (not an error), which can silently corrupt a sum. The DE fix is to screen non-numeric values out to NULL before they reach a measure. That screening needs conditional logic (CASE) and pattern matching, which you meet later in the course; until then, assume the upstream hands you clean numeric text or a genuine NULL.

Pitfall. CAST('12.99' AS INTEGER) → 12 (truncates, doesn't round). Cast to REAL first if you need the decimal, or cast the cents (an integer) rather than a dollar float. And remember SQLite won't error on a bad cast the way a warehouse does. Test your assumptions.

Recap. CAST(expr AS type) converts values explicitly at the trust boundary; SQLite's lax typing means you must cast (and guard junk) yourself so the model ports to strict warehouses.