Spotting Columns Where the Data Type is Wrong

You sort a "price" column and get 100, 20, 3, 5, 99 instead of 3, 5, 20, 99, 100. You try to calculate an average and get an error. You filter for dates greater than January 1st and get nonsensical results. The problem is the same every time: the column's data type doesn't match its actual content.

Numbers stored as text sort alphabetically, not numerically. Dates stored as strings can't be compared chronologically. Booleans stored as integers (0 and 1) won't behave like true/false in filters. These type mismatches are silent - they don't throw errors, they just produce wrong results. Here's how to find and fix them in ExploreMyData.

How type issues happen

CSV files don't have types. Every value is text, and the tool reading the file has to guess what each column should be. Most of the time the guessing works. But it fails when:

• A numeric column has a few non-numeric values (like "N/A" or "TBD"), so the whole column stays as text
• A date column uses an unusual format the parser doesn't recognize
• A zip code or account ID column looks numeric, gets read as a number, and every leading zero disappears
• A boolean column uses "Yes"/"No" or "1"/"0" instead of true/false
• Currency values include dollar signs or commas ("$1,234.56"), preventing numeric detection

The frustrating part is that the data looks right when you glance at it. The price column shows numbers. The date column shows dates. It's only when you sort, filter, or calculate that the type mismatch surfaces.

Check 1: Column type badges

The fastest way to spot type issues is the badge in each column header. There are three of them and only three: T for text, # for number, D for date. Underneath, DuckDB is tracking VARCHAR, BIGINT, DOUBLE, TIMESTAMP and the rest, but the header collapses all of that into one of three characters, because for deciding "is this column the right kind of thing" the distinction between BIGINT and DOUBLE almost never matters.

There is no boolean badge. A BOOLEAN column shows T and is treated as categorical text throughout the app, which is deliberate: true/false behaves like any other two-value category in the explorer, in equality filters and in search.

Scan across your columns and ask whether each badge matches what the column is for. "price" should read #, not T. "order_date" should read D, not T. "zip_code" should read T, not #, and if it reads # you have already lost data.

The is_refund question: there is no boolean target

A 0/1 flag column looks like it wants to be a boolean, and Convert Type won't take you there. Its dropdown offers exactly three targets: text, numeric, date. So decide what you actually want from the column.

Leave it numeric if you're going to count or sum it. That's the underrated option: with 0/1 values, SUM(is_refund) is the refund count and AVG(is_refund) is the refund rate, straight out of Group & Aggregate with no extra steps. Filtering is is_refund = 1, which is hardly a hardship.

Map it to text if the column is mostly there to be read, grouped and charted. Use Bulk Replace with two groups: 1 becomes "refund", 0 becomes "sale". Now the Column Explorer shows two labelled bars instead of a histogram of ones and zeros, and every chart legend says something a human can parse. You lose the SUM trick, so do this after you've taken the numbers you need, or on a copy of the column.

Make it a real BOOLEAN only if you specifically need the stored type, via a SQL Query step with TRY_CAST(is_refund AS BOOLEAN). Be clear about what you get: DuckDB's cast is happy to read 1 and 0 as true and false, but the resulting column still shows a T badge and still behaves as categorical text in the app. You've changed the storage, not the experience. For most work it isn't worth the step.

Check 2: Column Explorer behavior

Click a column header to open the Column Explorer. The type of explorer that appears tells you how DuckDB is treating the column:

• Numeric explorer: a row of six stats (Sum, Avg, StDev, Min, Max and Nulls, the last only when there are any) above an equal-width histogram with a bin size selector. No median, no quartiles. If you expected this and got a list of values instead, the column is text.
• Categorical explorer: a frequency list of distinct values with counts, and an italic (null) row if there are NULLs. Open a "price" column and find "10.99" sitting there as a text value with a count of 47, and you've confirmed it's stored as text.
• Date explorer: a time-series chart with day, week, month, quarter and year buttons. A date column that shows a frequency list instead is text.

This is the more reliable of the two checks, because it tells you what the column can actually do rather than what it's labelled. Open the explorer and see which of the three you get.

Fix: Convert the type

Once you've identified a mistyped column, fix it with Convert Type.

1. Click the green + in the Pipeline panel and select Convert Type from the Transform group.
2. Choose the column (e.g., "price").
3. Select the target type: numeric for prices, date for dates, text for codes like zip.
4. Click Apply.

Two things happen inside that step, and both are worth knowing.

First, text to numeric doesn't just cast. It cleans the string first, stripping currency symbols, thousands separators, spaces and percent signs, and reading a parenthesised value as negative. So $29.99 converts to 29.99 on its own. Do not strip dollar signs and commas with Find & Replace first. It's two wasted steps and it gives you nothing the conversion wasn't already going to do.

Second, the cast is a TRY_CAST() rather than a CAST(). A plain CAST fails the whole query on the first value it can't read; TRY_CAST converts what it can and leaves NULL where it can't. That's what turns "the conversion errored" into "here are the 128 rows that need a decision".

Find the values that won't convert

After converting, some values will be NULL. Those are the ones the cleanup couldn't rescue. To find them, add a Filter on the converted column and set the operator to is Empty. On a numeric column that generates a plain price IS NULL, which is exactly what you want. There is no operator literally called "is null"; is Empty is its name here.

What survives the conversion and what doesn't is more lopsided than people expect:

The cleanup workflow

Convert first, investigate second. That order matters, because the conversion itself does most of the cleaning and tells you precisely what's left:

1. Convert Type to the target type. Formatting is handled; only genuine junk becomes NULL.
2. Filter with is Empty to see exactly what didn't convert.
3. Decide what those rows mean. Remove them, fill them with Fill Missing, or leave them NULL, which is honest and keeps them out of AVG and SUM automatically.
4. Delete the filter step once you've looked.

The cases that genuinely need cleaning before conversion are narrower than the folklore suggests: trailing currency codes like "29.99 USD", and European punctuation like "1.234,56", which converts without complaint into a badly wrong 1.23456. Everything else, leave to the converter.

Each step is visible in the pipeline with its SQL. Delete one that didn't work and everything after it rebuilds. The original file is never modified.

Leading zeros: the one you can't fix downstream

Every other type problem in this article is fixable with a pipeline step. This one isn't, and it's worth understanding why.

When the parser decides zip_code is a number, "02101" is stored as the integer 2101. The zero isn't hidden or formatted away, it's gone; there is no such integer as 02101. Running Convert Type back to text afterwards gives you the string "2101". No operation downstream can recover a digit that was never loaded. The same goes for account numbers, product codes, phone numbers stored bare, and any other identifier that happens to be made of digits.

The fix is at parse time, and it means re-reading the file. Hover the file in the file list and click the gear icon to open Configure CSV Parsing. Open the Type Detection section and you have two ways to go.

Targeted. Put the column in the Column types field using DuckDB struct syntax: {'zip_code': 'VARCHAR'}. List several if you need to: {'zip_code': 'VARCHAR', 'account_no': 'VARCHAR'}. Everything else keeps its detected type, so your dates stay dates and your amounts stay numbers.

Blunt. Tick Load all columns as text. Nothing is inferred, every column arrives as text with its original characters intact, and you convert the ones you want with pipeline steps afterwards. More work, zero surprises, and the right choice when a file has fought you twice already.

Either way, click Reload with options and the file is re-read from the original handle. Two things to know before you do: the gear only appears for CSV, TSV and TXT files, so an Excel import can't be re-parsed this way and you'd need to fix the export or save it as CSV first. And re-parsing replaces the loaded table, so check your pipeline still makes sense against the new types afterwards.

The same dialog is also where you fix a wrong delimiter, a header row that isn't the first row, or a file that needs a few junk lines skipped before the real header. If a file loads with everything in one column, that's the first place to look.

Why this matters more than you think

Type issues are the most common source of subtle data bugs. Your code runs, your report generates, your chart renders. Everything looks fine - except the numbers are wrong because a sort was alphabetical instead of numerical, or an average silently excluded text values. These bugs don't crash; they just give you wrong answers that look plausible.

Checking types should be the first thing you do when you open any dataset. It takes 30 seconds to scan the badges, and it's the only moment when the leading-zeros problem is still fixable.

Check your column types now →