The route

Casting and types

Download the exercise files

World 2 was about reading files; this world is about changing what's in them, and every transform starts from the same question: what type is this column, really? This level hands you a payments export where the amounts and dates arrive as text, and turns them into numbers and timestamps with a SQL node — including the rows that refuse to convert.

Goal

A SQL node that turns payments_raw.csv into clean columns: amount as a number, paid_at as a timestamp, paid_on as a date — with the 12 unparseable amounts left as empty values instead of failing the run.

You'll need

  • The exercise files for this level: payments_raw.csv (400 payments).
  • About 20 minutes.

Open the file in a text editor first. Two columns are trouble:

  • amount is mostly 89.90, but 56 rows say 1,200.00 (a thousands separator) and 12 rows say N/A, pending or nothing at all.
  • paid_at is 06/11/2025 14:31 — day first, European style.

Steps

1. Read it and look at the types

Create a pipeline w3-1-casting with a CSV Source named payments pointing at the file. Add a SQL node (TRANSFORM ▸ Data Processing) named payments_clean, connect it, and start with this query to see what Odara decided about each column:

SELECT arrow_typeof(order_id) AS order_id,
       arrow_typeof(amount)   AS amount,
       arrow_typeof(paid_at)  AS paid_at
FROM input
LIMIT 1

Preview Output: Int64, Utf8, Utf8. order_id became a number on its own; the other two stayed text (Utf8). When the CSV reader finds values it can't fit into a type, it keeps the whole column as text rather than guessing — so the conversion is your job, and you get to decide what happens to the bad values.

2. CAST, and watch it fail

Replace the query with the obvious attempt:

SELECT payment_id, CAST(amount AS DOUBLE) AS amount
FROM input

CAST stops at the first value it can't convert

Cast error: Cannot cast string '1,200.00' to value of Float64 type

CAST is strict: one bad value fails the whole query. That's the right default for data you expect to be clean — you want to hear about the surprise — but not for this file.

3. TRY_CAST, and count what it drops

TRY_CAST does the same conversion but returns an empty value (NULL) when it can't. Before trusting it, count how many values it throws away:

SELECT COUNT(*) FILTER (WHERE TRY_CAST(amount AS DOUBLE) IS NULL) AS unparsed
FROM input

68. That's more than the 12 genuinely invalid values: the 56 amounts with a comma are being dropped too. A silent TRY_CAST would have lost 56 real payments — always count the NULLs it creates.

Fix the separator first, then convert:

SELECT COUNT(*) FILTER (WHERE TRY_CAST(REPLACE(amount, ',', '') AS DOUBLE) IS NULL) AS unparsed,
       SUM(TRY_CAST(REPLACE(amount, ',', '') AS DOUBLE)) AS total
FROM input

12 unparsed — exactly the N/A, pending and empty ones — and a total of 177,666.90.

4. Parse the dates with an explicit format

CAST(paid_at AS TIMESTAMP) fails too: 06/11/2025 14:31 isn't a format it recognises, and it couldn't know whether that's 6 November or 11 June anyway. Tell it:

to_timestamp(paid_at, '%d/%m/%Y %H:%M')

%d day, %m month, %Y four-digit year, %H:%M hours and minutes. Wrap it in CAST(... AS DATE) when you only want the day.

5. Put it together

SELECT payment_id,
       order_id,
       TRY_CAST(REPLACE(amount, ',', '') AS DOUBLE)        AS amount,
       to_timestamp(paid_at, '%d/%m/%Y %H:%M')            AS paid_at,
       CAST(to_timestamp(paid_at, '%d/%m/%Y %H:%M') AS DATE) AS paid_on,
       currency
FROM input

The final query in the SQL node

Add a Console with Row Limit 5, Execute, and expand its output.

Numbers and timestamps, as the Console prints them

Preview Output shows the same values: dates as 2025-11-06, timestamps as 2025-11-06T14:31:00.

Expected result

  • 400 rows. PAY-90001: 89.9, 2025-11-06T14:31:00, 2025-11-06.
  • PAY-90003, which was "1,200.00" in the file, is 1200.0.
  • 12 rows have an empty amount; the other 388 add up to 177,666.90.
  • 06/11/2025 became 6 November — day first, as the format said.

Common pitfalls

Cannot cast string '…' to value of Float64 type. CAST met a value it can't convert. Either clean it first (REPLACE) or switch to TRY_CAST — and then count the NULLs.

TRY_CAST "works" but totals are too low. It turned valid-but-formatted values into NULL too. Count IS NULL before and after every TRY_CAST.

Error parsing timestamp from '06/11/2025 14:31'. CAST only reads ISO-style timestamps. Use to_timestamp with a format string.

Dates come out a month off, or fail after the 12th. Day and month are swapped in the format: %m/%d reads 13/02/2025 as month 13.

What you built

A transform that decides types on purpose instead of hoping inference got them right — and a habit: every lossy conversion gets a NULL count next to it.

Check yourself

One question, drawn from a larger bank. Get it right to pass.

  1. TRY_CAST(amount AS DOUBLE) leaves 68 NULLs but only 12 values are really invalid. Why?