6. SQL Data Types Analysts Actually Meet

6.5 Practice: Spot Wrong Types in Messy Bank Data (Read-Only)

By Ravindra Bagale Last updated: 31 October 2026

Why. Before a manager sees a total, you should find the rows that would make it wrong.

What. This practice uses only SELECT, so nothing in the database changes. You compare the messy raw_amount text with the clean amount column and list every row that does not match.

How. Flag a row when raw_amount is NULL, or when its CAST value is different from amount.

Steps

Run the first example to list every problem row with a reason.
Count the problem rows with the second example.
Write the row ids in your notes and decide how each one should be fixed by the data owner.
Do not run UPDATE or DELETE in this practice.

Examples

Example 1 · dataset: northstar_bank

SELECT txn_id, raw_amount, amount,
       CASE
         WHEN raw_amount IS NULL THEN 'missing text'
         ELSE 'text does not match amount'
       END AS problem
FROM transactions
WHERE raw_amount IS NULL
   OR CAST(raw_amount AS REAL) <> amount
ORDER BY txn_id;

Output

txn_id	raw_amount	amount	problem
2	1,200.50	1200.5	text does not match amount
13	56,000	56000.0	text does not match amount
15	N/A	91000.0	text does not match amount
20		47000.0	missing text

Meaning. Four rows need attention. Two have commas in the text, one has the text N/A, and one has no text at all. None of them should be trusted for a total.

Example 2 · dataset: northstar_bank

SELECT COUNT(*) AS problem_rows
FROM transactions
WHERE raw_amount IS NULL
   OR CAST(raw_amount AS REAL) <> amount;

Output

problem_rows
4

Meaning. The count gives one number you can report, for example: 4 of 25 imported rows need cleaning.

Ravindra Bagale's Tip

Keep the raw imported column and add a clean column next to it. Then you can always explain where a number came from.