I've been thinking about very similar needs, but perhaps accomplished in a somewhat different way.

It would be really neat if SQLite or DuckDB implemented a way to create (register) read-only external tables (like Apache Iceberg). That way your large OLAP database (Snowflake, etc) could manage (DDL & CRUD) the external table, while SQLite and DuckDB would consume from it and automatically get all of the updates/deltas to the external table.

    clicks -->                     Iceberg
    bits   --> Snowflake/MPP <-->   table   --> DuckDB 
    pieces -->                      on S3 

Snowflake could take care of all the analytical heavy lifting continually preparing/updating/unloading data to the Iceberg table in an analytics ready/friendly form (lags, leads, etc built into data).

DuckDB could watch for changes on the Iceberg table (manifest) and update its internal representation of the external table as it changes. DuckDB can already read parquet files out on S3 so it should be somewhat trivial to implement? DuckDB could then handle all of the lightweight slicing/dicing of the analytics data; projections, counts, pagination, etc

Here's an example of Snowflake managing an Iceberg table: https://www.youtube.com/watch?v=Kz5cWY_vRwU