Choose DuckDB when an operations analyst needs to query exported CSV files with explicit decimal validation in SQL. Choose SQLite when reconciliation belongs inside an existing application that can validate incoming strings and store integer cents. The decisive issue is the import contract: an identifier that looks numeric must remain an identifier, and a malformed amount must become an exception.
Our executed seven-row example produced five distinct order IDs and a valid, deduplicated total of 25.00 in both controlled workflows. An intentionally unsafe SQLite NUMERIC staging table reduced the ID count to four and returned 45.9 from a naive sum. That is a schema-and-query failure, not evidence that SQLite cannot reconcile money.
Compare the work you must own
| Engine | Useful starting point | Required control | Observed result |
|---|---|---|---|
| DuckDB | Read CSV directly and classify values in SQL | Specify identifiers, decimal scale, rejection and deduplication rules | 5 order IDs; 2 amount exceptions; 1 duplicate; total 25.00 |
| SQLite | Embed a reconciliation ledger in an application | Preserve raw TEXT, validate amounts, convert to integer cents | Same accepted total: 2,500 cents |
DuckDB
DuckDB’s CSV detection infers structure and types; read_csv options let you override them. In our installed 1.5.6 build, automatic detection made all three columns VARCHAR. It preserved both 00017 and 17; it did not turn the amount column into a validated financial measure. Successful ingestion alone therefore established very little.
We repeated the import with header=true, all_varchar=true, then accepted only strings matching -?[0-9]+[.][0-9]{2} before casting to DECIMAL(12,2). DuckDB documents fixed-point decimal arithmetic; exact additions do not imply every operation is exact, since decimal division can produce floating-point results. This example only adds accepted values.
The strength is keeping raw data, exception classification and aggregation in one SQL workflow. The limitation is that the engine cannot decide whether an ambiguous comma means a thousands separator, a decimal separator or a damaged export. That policy still belongs to the business.
SQLite
SQLite’s type affinity rules explain why a NUMERIC column is the wrong raw staging area for this identifier. With Python’s sqlite3 parameterized inserts, both 00017 and 17 became integer 17. The amount 1,000.00 remained text, but the naive SUM treated its numeric prefix as 1; NA contributed no amount. The duplicate line also remained. These independent problems happened to yield a plausible-looking number.
The corrected route used TEXT columns and an application validation step. Python Decimal converted accepted amounts into integer cents before aggregation, while the pair (order_id, line_id) controlled duplicate detection. SQLite’s floating-point explanation is a reason to distinguish binary floating-point storage from a cents ledger, rather than assuming the SQL name DECIMAL creates fixed-scale money storage.
This is a good fit when an application already owns import rules and needs a persistent embedded ledger. It is less convenient for a one-off CSV investigation if the analyst must first build that import layer. We tested Python inserts, not the SQLite CLI’s CSV importer.
The executed fixture and its exceptions
The entire synthetic export was:
order_id,line_id,amount
00017,A,19.90
00017,B,0.10
17,C,8.00
18,D,NA
19,E,"1,000.00"
20,F,-3.00
00017,A,19.90There are seven rows, five textual order IDs, two invalid amount rows and one repeated order/line pair. The valid unique amounts are 19.90, 0.10, 8.00 and -3.00, totaling 25.00. The 25.00 is a partial accepted total: the two exceptions remain unresolved, so it is not the amount to post as a completed reconciliation.
Our DuckDB query was:
WITH classified AS (
SELECT *, CASE
WHEN regexp_full_match(amount, '-?[0-9]+[.][0-9]{2}')
THEN try_cast(amount AS DECIMAL(12,2))
END amount_value FROM raw
), dedup AS (
SELECT *, row_number() OVER (
PARTITION BY order_id,line_id ORDER BY amount
) n FROM classified
)
SELECT count(*), count(DISTINCT order_id),
sum(CASE WHEN amount_value IS NULL THEN 1 ELSE 0 END),
sum(CASE WHEN n>1 THEN 1 ELSE 0 END),
sum(CASE WHEN n=1 THEN amount_value END)
FROM dedup;
-- 7 | 5 | 2 | 1 | 25.00The ordering only resolves the identical duplicate in this fixture. For conflicting versions of a line, sorting by amount is not a defensible winner rule: quarantine the conflict or use a verified event/version field. Likewise, if your export legitimately uses thousands separators, add an explicit locale-aware normalization step and retain the original string.
What changes the choice
Pick DuckDB for a file-first investigation whose reviewers can inspect SQL and exceptions. Pick SQLite when the application’s validated import and cents schema are already the source of truth. Neither engine makes a raw export trustworthy by loading it successfully. A useful acceptance condition is an explained row count, preserved keys, an exception queue and a reproducible deduplication policy, followed by agreement with the source system.
These are local engines rather than hosted seat plans. DuckDB describes its core as MIT-licensed; SQLite describes its code as public domain. Optional support and third-party hosting are separate decisions. For the surrounding workflow, see our database client comparison and the distinction between local validation and managed data integration.
Methodology and limitations
Toolverly Editorial executed this fixture on 17 October 2026 on macOS arm64, Python 3.14.6, DuckDB 1.5.6 and SQLite 3.53.4. We retained the raw CSV, queries, classifications and actual outputs. The controlled SQLite route used the same regex, Decimal-to-cents conversion and textual composite key as the DuckDB route. Official documentation and licensing pages were checked the same day.
We did not test hosted databases, concurrent writers, large-file performance or production financial records. This is a small functional test with synthetic data, not an accounting validation or a speed benchmark. No cloud account or paid plan was used.
By Toolverly Editorial. AI assistance was used to prepare code, research and prose; the local examples were executed and their outputs reviewed before publication.
Photo by Myriam Jessier on Unsplash. Read about our publication or send a correction.