Guide · updated 2026-11-04
SQL versus pandas: the cleaning differences that change results
Missing keys in joins
In SQL, NULL = NULL is not true, so a join never matches two missing keys. pandas merge does match <NA> to <NA>. If both tables contain a missing key, pandas attaches the lookup row's attributes to every order without a key. Remove missing keys from the lookup side before merging.
What counts as whitespace
DuckDB's trim(x) removes spaces only. pandas .str.strip() removes every Unicode whitespace character, including the no-break space. Neither default is the rule; the rule is the character set your brief names, so pass it explicitly in both tools.
Text that looks like missing
pandas.read_csv treats strings such as NA, N/A and null as missing by default. DuckDB's read_csv only treats empty fields as NULL by default. For cleaning, read text as text (dtype="string", keep_default_na=False) and decide what missing means with a rule.
Exact decimals
DECIMAL(p,s) in DuckDB is exact. In pandas, numeric columns default to float64, which can't represent most decimal fractions exactly. Use decimal.Decimal values (an object column) or integer cents for money.
Reshaping
DuckDB's UNPIVOT drops NULL values unless you ask for INCLUDE NULLS; pandas melt keeps them. A pivot in SQL is conditional aggregation where you decide the columns; pandas pivot uses whatever values appear unless you reindex to declared columns.