Lesson 4 of 15 · Lab
Data types and casting: CAST vs parsing
A column's type says what its values are: text, a number, a date or true/false. Casting converts a column to another type so you can calculate with it. It can succeed on every row and still be wrong, so check a few values you know.
This lesson's data comes from fictional Vandelay Coffee Supply, which writes amounts the European way, so 1.240 means €1,240. Its 18 stacked invoice rows still hold amount as text.
This flow casts amount to DOUBLE, the usual type for numbers with decimals, and adds it up. Does it reach the €15,850 in Vandelay's sales report?
Not close. DOUBLE reads the dot as a decimal point, so €1,240 becomes 1.24. Nothing fails, because 1.24 is a valid number.
Replace a cast with a parse
- Summarize opens first in this live flow in Strata, Oplema's browser app for data prep.
totalreads 5,889.97, and nothing is red. - Select the Select block, just before Summarize. INV-1002 shows
amount_euras 1.24. - Press Delete. The flow stays red until you reconnect it.
- In the palette, open Parse and drag Number into the gap.
- Drag Union's right dot to Number's left dot, then Number's right dot to Summarize's left dot. Drop on the dot. New to wiring blocks? Build your first data flow shows how.
- Select Number. Set Column to
amount, Text looks like to the EU option, and Output column toamount_eur. - Select Summarize and read
totalagain.
Swipe sideways to see the canvas, or open this page on a larger screen to work with it.
In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).
The total you should see
total reads 15,850, the sales report's figure, and INV-1002 is 1,240 in Number's preview. rows says 18 and priced says 16, because void and TBC became null.
A parse makes you state the format
That's the sales report's number exactly, so whatever is wrong with that report, it isn't arithmetic.
A cast assumes the text is already in the form the type expects. A parse makes you state the format, so the file's convention becomes a decision you can show someone. Use a parse whenever a number was written for people. Leave the two null rows null, because an unknown price is not a price of zero.
Common types, and when conversion happens
| Type | Holds | Example |
|---|---|---|
| Text (VARCHAR) | Any characters | C2, 00101 |
| Integer | Whole numbers | 18 |
| Decimal or DOUBLE | Numbers with fractions | 1.24 |
| Date | Calendar days | 2026-07-02 |
| Boolean | True or false | true |
Conversion is explicit when you ask for it and implicit when the software decides. Excel's ="5"+3 gives 8 with no request from you. Explicit is safer because it's written down.
A code made of digits is still text. Convert the product code 00101 to a number and it becomes 101, and it no longer matches anything. Don't convert identifiers, and never add them up.
CAST and VALUE
In DuckDB, write CAST(amount AS DOUBLE), or DOUBLE PRECISION in PostgreSQL and FLOAT in SQL Server. Most databases stop with an error on TBC, and TRY_CAST, where it exists, returns null instead. For 1.240, remove the separator first with CAST(REPLACE(amount, '.', '') AS DOUBLE).
Excel's VALUE follows your computer's regional settings, so one file can read differently on two laptops. =NUMBERVALUE(A2, ",", ".") names the decimal and thousands separators. To keep leading zeros, import the column as text. Opening a CSV directly strips them.