Stage as text, then cast in a query you can re-run

Let the loader guess and it will guess on the first thousand rows

DuckDB’s CSV reader looks at a sample of the file, decides that a column full of digits is BIGINT, and loads it. The postcode column contains 400001, 110001, 560001 for the first twenty thousand rows and then SW1A 1AA for a customer in London. Depending on the version and the options, that row fails, the whole column falls back to text, or the load stops. Postgres’s COPY does not guess at all; it insists on a table with declared types, and it aborts the entire load on the first value that does not fit, leaving you with nothing.

Both behaviours come from the same fact: a typed load has to be right about every row before any row is in. The way around it is to make the load trivially right by declaring every column as text.

CREATE TABLE raw_payments (
  reference   text,
  paid_on     text,
  amount      text,
  city        text,
  phone       text,
  loaded_at   timestamptz DEFAULT now(),
  load_file   text
);

Nothing can fail. Every row lands exactly as it was in the file, and the loaded_at and load_file columns say when and from where, so next month’s file can be appended without confusion. This table is never updated. It is the evidence.

Cast in a view, and count what refuses

The typed version is a query over the raw table:

CREATE VIEW payments_typed AS
SELECT reference,
       to_date(paid_on, 'DD/MM/YYYY')                         AS paid_on,
       replace(replace(amount, '₹', ''), ',', '')::numeric    AS amount,
       city, phone, loaded_at
FROM raw_payments;

Run it and Postgres stops at the first amount that is not a number: invalid input syntax for type numeric: "N/A". That is useful information delivered in the least useful way, because it says nothing about how many rows are affected or which. Find them first, with a test that cannot fail:

SELECT amount, count(*)
FROM raw_payments
WHERE replace(replace(amount, '₹', ''), ',', '') !~ '^-?[0-9]+(\.[0-9]+)?$'
GROUP BY amount
ORDER BY count(*) DESC;

The regular expression describes what a well-formed number looks like, and the query lists every value that does not match, with its frequency. N/A 62 times, an empty string 14 times, (1,200) 3 times. Now each of those is a decision: N/A becomes NULL, the empty string becomes NULL, and the accounting parentheses become a negative. The decisions go into the view with CASE and nullif, and the count query is kept beside it to prove that nothing is left over.

DuckDB has try_cast(amount AS DECIMAL(12,2)), which returns NULL instead of erroring, so the failures can be found with WHERE try_cast(...) IS NULL AND amount IS NOT NULL. Postgres 16 added pg_input_is_valid(amount, 'numeric') for the same purpose. Both are cleaner than the regex and do the same job: separate the rows that convert from the rows that need a decision.

What a wrong type destroys

Some casts lose information and cannot be undone. A phone number loaded as an integer loses its leading zero: 0221234567 becomes 221234567, and no later query can tell whether the zero was there. Account numbers, postcodes, product codes with leading zeros, and anything with a + at the front all belong in text for the same reason. A number with more than fifteen significant digits, such as a 16-digit card or transaction reference, loaded as a double is rounded to the nearest representable value, so two different references can become the same number. A DuckDB sniffer or a spreadsheet will make both of these mistakes for you without asking, and the raw-as-text stage is the only defence.

Re-runnable is the point

When the provider sends March’s file, it is appended to raw_payments with a new load_file value, and the view already handles it. When someone discovers in June that (1,200) was a refund and not a negative sale, the view changes in one place and every report built on it changes together. When an auditor asks what the original value was, it is in the raw table, untouched. None of that is possible if the cleaning happened in a spreadsheet before the load.

The habit

Two tables, or a table and a view: one that holds exactly what arrived, and one that holds what you decided it meant. The decisions live in SQL, next to a count of the rows that needed them. When the count is not zero, that is a list of decisions still to make, not a reason to add a filter.