Add a buffered managed table support

[turbolytics]

The tumbling window adds support for a specialize managed table called a "tumbling window" table.

This should add support for a buffered table which will flush whatever data is in the table at the time of flushing. This allows for buffering data to disk, and then flushing at a later time.

The managed table will allow outputing data in batches.

[ForeverAngry]

What would you think about an arrow ipc buffer for this feature? It would help with accumulating right sized batches, while letting your work with out of memory sized data. I'd be interested in contributing. Let me know!

[turbolytics]

oh cool! I'm still pretty new to arrow ecosystem so didn't even consider IPC buffers, but am super interested in learning more.

Do you have a recommendation on a good resource to learn more about them? Is this a good one?

https://arrow.apache.org/docs/python/ipc.html

https://arrow.apache.org/docs/python/api/ipc.html

---

I had no idea that arrow even supported IPC, i'm blown away the more I work with arrow they've thought through so much 🤯.

Would IPC's allow for seamlessly storing arrow data out of process (any python file-like object? including disk-based or memory-based)? Building on arrow and IPC would support keeping the arrow continuity? Like the writes and data reads will all be arrow Tables? and it looks like the IPC api supports reading data in batches too?

---

Nothing is set in stone with the sql-flow api, we're still trying to just get people interested in using it :) so I def don't think we have to adhere to the current managed table setup at all. In fact, the current managed table API feels quite janky to me overall:

https://github.com/turbolytics/sql-flow/blob/main/dev/config/examples/tumbling.window.yml

The managed table is a table inside of duckdb so we're handing control over to duckdb to efficiently store/read/write data:

https://github.com/turbolytics/sql-flow/blob/main/dev/config/examples/tumbling.window.yml#L3-L10

• What would the config file for a buffered managed table look like?
• Would IPC allow running duckdb queries over it? Would end users even need to? I'd imagine that the data is already passed through DuckDB SQL (using sql-flow handler) before the data is stored in the IPC table?

The sql-flow config is quite brittle and naive right now, and i'm trying to imagine how to adapt it to accommodate the buffered table.

Sorry for the meandering rants.

Yes, i think the ability to support IPC data configurable to be in memory, on disk, and batch sizes would be valuable overall. And any help you're willing to provide would be greatly appreciated! I'm still trying to imagine how to slot it in and expose it to end users :)

[turbolytics]

Just for some context, the end user problem that I was thinking buffered managed table would help support is to support webhooks as a source.

So like:

• sql-flow would expose an HTTP endpoint.
• User would register that endpoint with a service that emits data through webhooks
• sql-flow begins receiving webhook data (imagine like stripe webhooks, or auth0 tenant log webhook data).
• sql-flow writes the data out to the disk-backed buffered table for persistence
• sql-flow will flush data from the disk-backed buffered table at configurable intervals/batch-sizes

[ForeverAngry]

Just for some context, the end user problem that I was thinking buffered managed table would help support is to support webhooks as a source.

So like:

• sql-flow would expose an HTTP endpoint.
• User would register that endpoint with a service that emits data through webhooks
• sql-flow begins receiving webhook data (imagine like stripe webhooks, or auth0 tenant log webhook data).
• sql-flow writes the data out to the disk-backed buffered table for persistence
• sql-flow will flush data from the disk-backed buffered table at configurable intervals/batch-sizes

Yeah, IPC is interesting to me for these use-cases, because you can serialize the data in the arrow format, and persist it to disk, and then load it back to memory with memory mapping. I like to use hive-style partitioning to create partitions that way i can fill up those partitions and then manage the partitioned size, in terms of when to flush data to create a properly sized file.

Am glad you gave some more context on the use case, the feature i was looking to add was more for appending to Iceberg. Where the strategy is similar, but I would like to use pyarrows parquet tools to do some fun stuff. DuckDBs integration with arrow and arrow flight is really a big game changer! I also love the declarative pipeline framework you created - pretty clean!

[turbolytics]

@ForeverAngry Perfect! Yes if we're just focused on iceberg i think that IPC would be an amazing fit. I was hoping we could give more control over buffering/batching. The current naive implementation only allows specifying the number of records to include in a batch which can create some suboptimal parquet segments. Would the IPC support specifying a specific batch size in bytes? I think that would be really powerful too