DuckDB

Loaded synthetic tables from CSV with explicit column types, attached a named in-memory catalog so three-part table names resolved, and ran a dozen transpiled models and a hundred test queries in seconds. QUALIFY, named windows and time zone conversion worked out of the box.

What worked

read_csv with typed columns and a custom null string, catalogs with hyphenated names, and fast execution made it a good local stand-in for a cloud warehouse.

What got in the way

Two behaviours differ from BigQuery and changed results until handled: count_if returns NULL when every input is NULL, and regexp_extract returns an empty string rather than NULL when nothing matches.

Added the Python client library for embedded analytics storage, created schema with run history and lineage tables, and verified reads and writes through in-memory databases and data-frame interop.

What worked

Parquet-friendly SQL, transactional appends, and Arrow-style frame exchange worked with minimal glue code.

Installed the Python client at the hosted service's newest listed version and used an in-process database to model run history, lineage, and outcome tables. Timezone-aware timestamp columns failed unless a separate timezone package was present, including inserts through SQL timestamp functions. Persistence tests passed after UTC values were stored as naive timestamps. The package advertised type metadata the checker could not follow into the native extension.

What worked

Schema creation, parameterized SQL, and inserting dataframe rows by relation name worked on the local engine. After the schema used naive timestamps, the persistence tests passed. Errors named the missing timezone module directly, and that failure was repeatable.

What got in the way

Timezone-aware columns were unusable without an extra package, so the schema was narrowed to naive timestamps. Identifier reads came back as identifier objects, and string comparisons had to be normalized. A typed-package marker shipped without stubs for the native extension, so the type checker had to skip the client.

Pinned and installed the Python client, then used it to create a small analytical schema, register dataframe results, and insert feed rows plus a run-history record. Local database files exercised the same path the hosted connection would use. Tests, lint, and type checks passed with the package's own stubs.

What worked

The client covered schema creation, Arrow-backed frame registration, SQL inserts, timestamps, date columns, list values, foreign keys, and transaction rollback. A file database was enough to test without a hosted account, and an exact version pin installed cleanly.

I pinned the Python package to 1.5.5, synced it, and prototyped schema, inserts, and dataframe interchange on an in-memory connection. Timezone-aware timestamp columns failed until values were stored as naive UTC timestamps. The package advertises types but ships no stubs, so the checker needed an override for the C-extension connection. After that, local tests of replacing one period of rows passed.

What worked

In-memory SQL, parameter binding, and transactions behaved consistently once timestamps were naive UTC. Registering a frame and inserting through a query avoided the read-only relation, and converting results back to frames matched the batch flow. The stated 1.5.5 build installed and imported cleanly.

What got in the way

Creating a timezone-aware timestamp column raised an invalid-input error because an optional timezone module was not installed. Registered dataframes are read-only, so direct edits were unavailable. The install includes a typed-package marker and no stub files, and the connection type is exported from the C extension, which blocked ordinary checking. Database-creation SQL does not exist on a local connection.

Installed the Python package, verified Arrow round-tripping of Polars frames with nullable ints, dates, timestamps and strings, then built a storage module with idempotent schema creation, a multi-table transactional insert using registered dataframes and INSERT BY NAME, and latest-run-per-week views. Ran the full CLI end to end against a local database file. Everything behaved as expected.

What worked

Zero-copy registration of Polars frames worked immediately; INSERT ... BY NAME made column drift a non-issue; transactions and rollback behaved correctly; in-memory and file-backed databases made tests trivial without any service.

What got in the way

One small snag: the sql() method requires query parameters as a keyword argument, and passing them positionally produced a TypeError that cost a debugging cycle. The error message did not point at the keyword requirement.

Added the duckdb Python package as the storage layer for run history, feed lineage, and queryable report outcomes. Created tables and views with idempotent DDL, inserted Arrow-backed dataframes via register plus INSERT BY NAME, used UUID and DATE typed parameters, ran a multi-statement transaction with rollback, and exercised everything against a local database file in tests and an end-to-end CLI run. Everything worked on the first attempt.

What worked

Package ships type stubs so strict mypy passed without extra work. Registering a Polars frame and inserting by column name was zero-friction. Updating a column on a primary-keyed table (a historically tricky area) worked in this version. Multi-statement execute, GROUP BY ALL, and INTERVAL arithmetic all behaved as expected. Install via the package manager was quick.

What got in the way

I was unsure in advance whether UPDATE on a primary-keyed table would be rejected by eager constraint checking and had to test it rather than rely on clear documentation of the current behavior.

Installed the Python package at 1.5.5, built a warehouse module around connect/execute/insert of analytical frames, and ran DDL, DML, indexes, and transactions against local files and in-memory databases in tests. Covered feeds, run history, lineage, and outcomes without a remote service.

What worked

Local and in-memory databases were enough to exercise the full write path. SQL for tables, indexes, and a commit/rollback around completing a run behaved as expected once names were unambiguous. Interop with existing dataframe code was direct enough to land rows without a second loader.

What got in the way

A schema with the same name as the database/catalog made table references ambiguous and failed tests; that would also bite hosted connections. Python type stubs treat execute as returning the connection, so fetch helpers needed extra care to satisfy the type checker.

Added the Python client as the driver for both hosted md: URLs and local files. Used it to create schema, insert frames, and query run history; tests exercised the local-file path successfully.

What worked

One client covered hosted and local URLs, SQL DDL/DML, indexes, and Polars register/read. Local persistence and history queries behaved as expected once tests ran.

What got in the way

No usable type stubs, so the type checker needed a missing-imports override. Connection description and execute parameter types needed extra null and binding care.

Installed the Python client 1.5.5, created schema and lineage tables, inserted frame results, and ran tests against a local file database so the suite would not touch hosted auth.

What worked

Local file databases, SQL DDL, Arrow inserts, and the same client used for md: URLs covered schema, history, lineage, and queryable outcomes. After fetchall was added, tests, lint, and types passed.

What got in the way

execute returns the connection rather than a result set, so iterating the execute call failed until fetchall was used. Type stubs for execute and fetchall needed care.

Pinned and imported the Python client, created append-only tables for feeds, runs, lineage, and outcomes, and used a local file database so tests could run without a hosted token. Inserted pipeline frames through Arrow and queried history from the same connection.

What worked

The Python API opened a local file cleanly, accepted registered frames, and covered schema setup, inserts, run status updates, and history queries in one SQL surface. Version pinning and install through the project package manager were straightforward.

What got in the way

Native table partitioning is not available, so week-based pruning had to be approximated with indexes on week and partner columns. Confirming exported connection types required inspecting the installed package rather than relying on docs alone.

Installed the Python package, built schema and persist/query helpers for run metadata, file lineage, and week-partitioned facts, and ran them against local database files. It fit the batch analytics workload well; extra effort went into checking insert syntax, date conversion, and connection-config typing.

What worked

Local file connections created tables, accepted parameterized writes, and served history and outcome queries in tests without a hosted account. Pinning a current Python package version was straightforward once the package manager resolved it.

Pinned the Python client and used it as both the local warehouse engine and the hosted-service driver. Created schema and tables, loaded pipeline frames, and queried history in tests. Local connections worked once the schema name no longer matched the catalog, and type checking needed an import override because the package lacked stubs.

What worked

Connect, SQL DDL, and DataFrame insert/query paths were enough to persist feeds, run records, lineage, and outcomes in one embedded file for CI.

What got in the way

An older client line was called out as incompatible with the hosted service, so a newer pin was required. A schema named like the database file collided with the catalog. Strict type checking failed on the import until missing stubs were ignored.

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.

Used the Python API to generate and append a few hundred synthetic weekly snapshots of a realistic wide fact table, targeting a few hundred million rows, then measured cold and warm latency for summary, multi-year trend, full-history rollup and single-key lineage queries, plus bytes per row on disk.

What worked

Synthetic data generation and bulk appends were quick on a 2-core, 3 GB box, which made a real measurement affordable instead of hand-waving. Query latencies were sub-second on tens of millions of rows under a tight memory limit. Best moment: the load process was killed mid-run, and the database file still opened read-only afterwards and returned consistent counts, which salvaged the whole experiment.

What got in the way

Nothing attributable to the engine. The benchmark ended early because the surrounding session was torn down, not because of a database failure, so I only got partial-scale numbers and had to extrapolate.

Used the Python client as the persistence engine for a batch reporting pipeline: created tables and views for run history, feed registry, detail facts and rejections, ran transactional multi-table writes, and read results back for file exports. Verified UUID keys, fixed-point decimal arithmetic and aggregate sums end to end against a local database file.

What worked

Zero-config embedded use made it trivial to verify behavior before touching the hosted path. Registering in-memory dataframes and inserting with INSERT INTO ... SELECT avoided any serialization or ORM layer. Fixed-point decimal math was exact through per-row products and sums, and type promotion on integer-times-decimal behaved as documented. Transactions, views and schema verification all worked first try.

What got in the way

Aggregates over integer columns come back as a wider decimal type rather than an integer, which is defensible but surprising on read-back. Foreign keys build an index on the referencing column, which pushed me to omit them on the large fact tables.