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:
amountis mostly89.90, but 56 rows say1,200.00(a thousands separator) and 12 rows sayN/A,pendingor nothing at all.paid_atis06/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 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

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

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, is1200.0.- 12 rows have an empty
amount; the other 388 add up to 177,666.90. 06/11/2025became 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.
TRY_CAST(amount AS DOUBLE) leaves 68 NULLs but only 12 values are really invalid. Why?