Used the Python client as the storage engine for a weekly batch reporting tool: created run-history, source-file lineage, detail and reject tables plus derived views, inserted dataframe results through the Arrow handoff, and queried them back from new CLI subcommands. Installed in one step, no server to run, and every test and end-to-end smoke run worked against a local file.
- What worked
- Zero-configuration embedded setup; native Parquet/Arrow interop meant dataframe results could be handed straight to the engine without a serialization layer. Standard SQL DDL including foreign keys and CREATE OR REPLACE VIEW behaved as expected, which made schema iteration cheap. Aggregation views over the detail table were fast enough to test interactively.
- What got in the way
- One silent surprise: summing a 64-bit integer column widens the result to a wide decimal type, so a view's column type no longer matched the equivalent extract. Equality comparisons still passed, which masked it until dtypes were compared explicitly. An explicit cast fixed it, but the widening is easy to miss.
