AI can help inspect columns, write SQL, and find anomalies, but CSV has no built-in schema and does not guarantee that every row follows the same format. Before a formal analysis, check encoding and parsing, column types, nulls, duplicate rows, unique keys, dates and time zones, units, and outliers. Record the findings as Plan checkpoints or SQL artifacts rather than leaving them as a warning in chat.
The examples below use orders.csv, expected to contain order_id, customer_id, created_at, amount, currency, and status. The SQL uses DuckDB syntax; replace the names and valid values with your own data contract.
1. Encoding, delimiters, and parsing errors
Do not begin with a chart. First confirm that every row is actually split into the same fields. UTF-8 versus Big5, commas versus semicolons, commas inside quotes, and embedded newlines can all shift columns. Preview the data, then inspect the parser's inference:
SELECT *
FROM read_csv('orders.csv')
LIMIT 20;
The DuckDB CSV documentation notes that CSV has no schema and varies widely. The reader detects formats and types automatically, but when the result is wrong, explicitly set delim, quote, escape, or header. Do not silently skip errors with ignore_errors = true and proceed to calculation. The core CSV reader directly supports UTF-8, UTF-16, and Latin-1. For Big5 and other encodings, confirm that the encodings extension is installed and loaded, or convert the file to UTF-8 first. See the DuckDB Encodings Extension.
2. Column types: were numbers parsed as text?
Start with DuckDB DESCRIBE to inspect the inferred schema:
DESCRIBE SELECT * FROM read_csv('orders.csv');
Thousands separators, currency labels, or N/A can turn an entire amount column into VARCHAR. Mixed date formats may also prevent correct sorting. Before calculating, use TRY_CAST to quantify conversion failures:
SELECT
count(*) AS rows,
count(*) FILTER (
WHERE amount IS NOT NULL
AND TRY_CAST(amount AS DECIMAL(18, 2)) IS NULL
) AS invalid_amount_rows
FROM read_csv('orders.csv', all_varchar = true);
3. Missing values: distinguish NULL, empty strings, and placeholders
A blank in CSV may become NULL or ''; N/A, -, and unknown are different again. Do not count only IS NULL:
SELECT
count(*) AS rows,
count(*) FILTER (WHERE order_id IS NULL OR trim(order_id) = '') AS missing_order_id,
count(*) FILTER (WHERE customer_id IS NULL OR trim(customer_id) = '') AS missing_customer,
count(*) FILTER (WHERE amount IS NULL) AS missing_amount
FROM read_csv('orders.csv', all_varchar = true);
Whether a missing value should remain, be imputed, or be excluded is a business decision, not something an Agent should guess.
4. Duplicate rows: was the file appended twice?
Identical rows often come from duplicate exports or merges. Compare the total row count with distinct rows:
WITH src AS (
SELECT * FROM read_csv('orders.csv')
)
SELECT
(SELECT count(*) FROM src) AS rows,
(SELECT count(*) FROM (SELECT DISTINCT * FROM src)) AS distinct_rows;
If full-row distinct is unsuitable, group by explicit business fields instead. Preserve the source and decision rule before deleting duplicates: two apparently identical events can both be legitimate.
5. Unique keys: is order_id really one row per order?
The unique key determines the grain and whether later JOINs fan out:
SELECT order_id, count(*) AS n
FROM read_csv('orders.csv')
GROUP BY order_id
HAVING count(*) > 1
ORDER BY n DESC
LIMIT 50;
If an order can contain multiple items, a non-unique order_id may be entirely valid; the real grain may be order_id + line_id. The goal is not to eliminate repetition but to document the correct grain.
6. Dates and time zones: crossing a day is more than a formatting issue
First quantify parsing success and the range:
SELECT
min(TRY_CAST(created_at AS TIMESTAMPTZ)) AS min_ts,
max(TRY_CAST(created_at AS TIMESTAMPTZ)) AS max_ts,
count(*) FILTER (
WHERE created_at IS NOT NULL
AND TRY_CAST(created_at AS TIMESTAMPTZ) IS NULL
) AS invalid_ts
FROM read_csv('orders.csv', all_varchar = true);
Then determine whether the source is UTC, Taipei time, or local time without an offset. Month-end boundaries, day boundaries, and daylight saving time can change cohorts. State the reporting time zone in the Plan before converting and truncating dates.
7. Units and currencies: the same column does not imply the same scale
amount = 100 might mean dollars, cents, USD, or TWD. Inspect every currency and its scale:
SELECT currency, count(*) AS rows,
min(TRY_CAST(amount AS DOUBLE)) AS min_amount,
max(TRY_CAST(amount AS DOUBLE)) AS max_amount
FROM read_csv('orders.csv', all_varchar = true)
GROUP BY currency
ORDER BY rows DESC;
For currency conversion, the exchange-rate date and source are part of the metric definition. Do not let AI choose a "reasonable" rate on its own.
8. Outliers and valid ranges: flag first, do not delete immediately
A negative amount may be a refund or an error; an unusually large value may be an enterprise order. Use quantiles and business rules to identify candidates:
WITH typed AS (
SELECT TRY_CAST(amount AS DOUBLE) AS amount
FROM read_csv('orders.csv', all_varchar = true)
)
SELECT
min(amount) AS min_amount,
quantile_cont(amount, 0.5) AS median,
quantile_cont(amount, 0.99) AS p99,
max(amount) AS max_amount
FROM typed;
Outlier handling should preserve the original value, the reason for the flag, and the effect before and after exclusion. A statistical outlier is not necessarily a business error.
Turn the eight checks into Plan checkpoints
Do not paste results only into chat. A formal Plan might say:
Checkpoint A: Confirm parser settings, column types, and date parsing failure rates
Checkpoint B: Confirm grain, unique keys, and duplicate-row handling
Checkpoint C: Confirm reporting time zone, currencies, and units
Checkpoint D: List missing-value and outlier rules and report the effect before and after exclusion
In Lantide Data, an Agent can explore local CSV, Excel, and Parquet files and their schemas, then put these checks into a Plan. The user reviews the Plan before Execute. Query logic remains in persistent SQL tabs, results can be cached for later steps, and the Report should disclose limitations. This turns "the data may be bad" from a chat warning into rerunnable, reviewable evidence. See the SQL-first workflow and Plan → Execute → Report.
Lantide does not automatically make dirty data correct or decide how the business should treat missing values, refunds, and exchange rates. It provides a local DuckDB query layer and reviewable artifacts. Local-first also does not guarantee that data never enters model context when you use an external model; inspect the AI profile and connection settings.
Conclusion
Run these eight checks on a small sample first. Confirm parsing, grain, time, and units before asking AI to perform a formal analysis. The best thing to automate is not skipping data quality—it is saving and rerunning the same quality SQL, while making every Report explain what was and was not handled.