{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"everything-as-text-first","title":"Stage as text, then cast in a query you can re-run","url":"https://addaly.com/learn/data-and-sql/everything-as-text-first","position":46,"of":66,"minutes":8,"answer":"Load every column as text into a raw table that is never updated, then cast in a view beside a query that lists and counts every value the cast would refuse.","keyIdea":"Load every column as text into a raw table that is never updated, then cast in a view beside a query that lists and counts every value the cast would refuse, because a typed load has to be right about every row before any row is in."},"body":{"format":"markdown","text":"## Let the loader guess and it will guess on the first thousand rows\n\nDuckDB'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.\n\nBoth 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.\n\n```sql\nCREATE TABLE raw_payments (\n  reference   text,\n  paid_on     text,\n  amount      text,\n  city        text,\n  phone       text,\n  loaded_at   timestamptz DEFAULT now(),\n  load_file   text\n);\n```\n\nNothing 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.\n\n## Cast in a view, and count what refuses\n\nThe typed version is a query over the raw table:\n\n```sql\nCREATE VIEW payments_typed AS\nSELECT reference,\n       to_date(paid_on, 'DD/MM/YYYY')                         AS paid_on,\n       replace(replace(amount, '₹', ''), ',', '')::numeric    AS amount,\n       city, phone, loaded_at\nFROM raw_payments;\n```\n\nRun 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:\n\n> Figure: Stage as text, then cast in a query you can run again. Steps: 1. Load every column as text (nothing can fail; the raw table is the evidence); 2. List what will not cast (with a count of each distinct value); 3. Decide each one (N/A to NULL, (1,200) to a negative); 4. Cast in a view (and next month's file needs no new work). A typed load has to be right about every row before any row is in. Loading as text makes the load trivially right and moves every decision into SQL, next to a count of the rows that needed it. When that count is not zero, it is a list of decisions still to make, not a reason to add a filter.\n\n```sql\nSELECT amount, count(*)\nFROM raw_payments\nWHERE replace(replace(amount, '₹', ''), ',', '') !~ '^-?[0-9]+(\\.[0-9]+)?$'\nGROUP BY amount\nORDER BY count(*) DESC;\n```\n\nThe 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.\n\nDuckDB 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.\n\n## What a wrong type destroys\n\nSome 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.\n\n## Re-runnable is the point\n\nWhen 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.\n\n## The habit\n\nTwo 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."},"check":{"question":"A phone column was loaded with the loader's guessed type, `bigint`. Later, a join to another system's phone numbers, stored as text, matches only a fraction of rows. What has most likely happened, and can the raw table fix it?","options":[{"text":"The bigint column overflowed for numbers longer than ten digits, wrapping them to negative values; re-casting to numeric in the view restores them.","correct":false,"tempting":"Numbers that overflowed would have failed the load rather than wrapping, and casting a stored wrong value cannot recover digits that were never kept."},{"text":"Leading zeros were discarded when the digits became a number, and only a raw table that kept the column as text still holds them.","correct":true},{"text":"The text side has spaces and dashes in the numbers, and normalising both sides to digits-only in the join will make every row match.","correct":false,"tempting":"Normalising the text side helps, but it cannot reinstate a leading zero that the numeric side has already lost."},{"text":"bigint stores phone numbers exactly and the mismatch must come from the other system using a different country code convention.","correct":false,"tempting":"The storage is exact for the digits it keeps, but a leading zero is not a digit an integer can hold, and it is gone."}]},"prev":{"slug":"loading-a-file","title":"Getting a file in without losing a row","url":"https://addaly.com/learn/data-and-sql/loading-a-file"},"next":{"slug":"dates-in-five-formats","title":"Parsing dates that were typed by people","url":"https://addaly.com/learn/data-and-sql/dates-in-five-formats"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Stage as text, then cast in a query you can re-run', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/everything-as-text-first (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Stage as text, then cast in a query you can re-run","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":5,"slug":"data-that-arrives-dirty","title":"Data that arrives dirty"},"answer":null,"keyIdea":"Load every column as text into a raw table that is never updated, then cast in a view beside a query that lists and counts every value the cast would refuse, because a typed load has to be right about every row before any row is in.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"flow","steps":[{"label":"Load every column as text","detail":"nothing can fail; the raw table is the evidence"},{"label":"List what will not cast","detail":"with a count of each distinct value"},{"label":"Decide each one","detail":"N/A to NULL, (1,200) to a negative"},{"label":"Cast in a view","detail":"and next month’s file needs no new work"}],"title":"Stage as text, then cast in a query you can run again","caption":"A typed load has to be right about every row before any row is in. Loading as text makes the load trivially right and moves every decision into SQL, next to a count of the rows that needed it. When that count is not zero, it is a list of decisions still to make, not a reason to add a filter."},"alt":"Figure: Stage as text, then cast in a query you can run again. Steps: 1. Load every column as text (nothing can fail; the raw table is the evidence); 2. List what will not cast (with a count of each distinct value); 3. Decide each one (N/A to NULL, (1,200) to a negative); 4. Cast in a view (and next month’s file needs no new work). A typed load has to be right about every row before any row is in. Loading as text makes the load trivially right and moves every decision into SQL, next to a count of the rows that needed it. When that count is not zero, it is a list of decisions still to make, not a reason to add a filter."}},"blocks":[{"t":"h2","id":"let-the-loader-guess-and-it-will-guess-on-the-first-thousand","runs":[{"text":"Let the loader guess and it will guess on the first thousand rows"}]},{"t":"p","runs":[{"text":"DuckDB’s CSV reader looks at a sample of the file, decides that a column full of digits is "},{"text":"BIGINT","code":true},{"text":", and loads it. The postcode column contains "},{"text":"400001","code":true},{"text":", "},{"text":"110001","code":true},{"text":", "},{"text":"560001","code":true},{"text":" for the first twenty thousand rows and then "},{"text":"SW1A 1AA","code":true},{"text":" 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 "},{"text":"COPY","code":true},{"text":" 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."}]},{"t":"p","runs":[{"text":"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."}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE TABLE raw_payments (\n  reference   text,\n  paid_on     text,\n  amount      text,\n  city        text,\n  phone       text,\n  loaded_at   timestamptz DEFAULT now(),\n  load_file   text\n);"},{"t":"p","runs":[{"text":"Nothing can fail. Every row lands exactly as it was in the file, and the "},{"text":"loaded_at","code":true},{"text":" and "},{"text":"load_file","code":true},{"text":" 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."}]},{"t":"h2","id":"cast-in-a-view-and-count-what-refuses","runs":[{"text":"Cast in a view, and count what refuses"}]},{"t":"p","runs":[{"text":"The typed version is a query over the raw table:"}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE VIEW payments_typed AS\nSELECT reference,\n       to_date(paid_on, 'DD/MM/YYYY')                         AS paid_on,\n       replace(replace(amount, '₹', ''), ',', '')::numeric    AS amount,\n       city, phone, loaded_at\nFROM raw_payments;"},{"t":"p","runs":[{"text":"Run it and Postgres stops at the first amount that is not a number: "},{"text":"invalid input syntax for type numeric: \"N/A\"","code":true},{"text":". 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:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT amount, count(*)\nFROM raw_payments\nWHERE replace(replace(amount, '₹', ''), ',', '') !~ '^-?[0-9]+(\\.[0-9]+)?$'\nGROUP BY amount\nORDER BY count(*) DESC;"},{"t":"p","runs":[{"text":"The regular expression describes what a well-formed number looks like, and the query lists every value that does not match, with its frequency. "},{"text":"N/A","code":true},{"text":" 62 times, an empty string 14 times, "},{"text":"(1,200)","code":true},{"text":" 3 times. Now each of those is a decision: "},{"text":"N/A","code":true},{"text":" becomes NULL, the empty string becomes NULL, and the accounting parentheses become a negative. The decisions go into the view with "},{"text":"CASE","code":true},{"text":" and "},{"text":"nullif","code":true},{"text":", and the count query is kept beside it to prove that nothing is left over."}]},{"t":"p","runs":[{"text":"DuckDB has "},{"text":"try_cast(amount AS DECIMAL(12,2))","code":true},{"text":", which returns NULL instead of erroring, so the failures can be found with "},{"text":"WHERE try_cast(...) IS NULL AND amount IS NOT NULL","code":true},{"text":". Postgres 16 added "},{"text":"pg_input_is_valid(amount, 'numeric')","code":true},{"text":" 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."}]},{"t":"h2","id":"what-a-wrong-type-destroys","runs":[{"text":"What a wrong type destroys"}]},{"t":"p","runs":[{"text":"Some casts lose information and cannot be undone. A phone number loaded as an integer loses its leading zero: "},{"text":"0221234567","code":true},{"text":" becomes "},{"text":"221234567","code":true},{"text":", and no later query can tell whether the zero was there. Account numbers, postcodes, product codes with leading zeros, and anything with a "},{"text":"+","code":true},{"text":" 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 "},{"text":"double","code":true},{"text":" 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."}]},{"t":"h2","id":"re-runnable-is-the-point","runs":[{"text":"Re-runnable is the point"}]},{"t":"p","runs":[{"text":"When the provider sends March’s file, it is appended to "},{"text":"raw_payments","code":true},{"text":" with a new "},{"text":"load_file","code":true},{"text":" value, and the view already handles it. When someone discovers in June that "},{"text":"(1,200)","code":true},{"text":" 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."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"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."}]}],"check":{"question":[{"text":"A phone column was loaded with the loader’s guessed type, "},{"text":"bigint","code":true},{"text":". Later, a join to another system’s phone numbers, stored as text, matches only a fraction of rows. What has most likely happened, and can the raw table fix it?"}],"options":[{"text":[{"text":"The bigint column overflowed for numbers longer than ten digits, wrapping them to negative values; re-casting to numeric in the view restores them."}],"correct":false,"tempting":[{"text":"Numbers that overflowed would have failed the load rather than wrapping, and casting a stored wrong value cannot recover digits that were never kept."}]},{"text":[{"text":"Leading zeros were discarded when the digits became a number, and only a raw table that kept the column as text still holds them."}],"correct":true,"tempting":null},{"text":[{"text":"The text side has spaces and dashes in the numbers, and normalising both sides to digits-only in the join will make every row match."}],"correct":false,"tempting":[{"text":"Normalising the text side helps, but it cannot reinstate a leading zero that the numeric side has already lost."}]},{"text":[{"text":"bigint stores phone numbers exactly and the mismatch must come from the other system using a different country code convention."}],"correct":false,"tempting":[{"text":"The storage is exact for the digits it keeps, but a leading zero is not a digit an integer can hold, and it is gone."}]}],"order":[0,2,1,3],"why":null},"prev":{"slug":"loading-a-file","title":"Getting a file in without losing a row","minutes":9},"next":{"slug":"dates-in-five-formats","title":"Parsing dates that were typed by people","minutes":9}}}