Prototyping: Duckdb based metrics calculation

[ravenac95]

This is very much still prototypical however, I'm creating a large set of issues to turn this into a functioning production system.

Basically through this fairly large PR I was testing how we could actually integrate all of this into the SQLMesh pipeline that is connected to our iceberg data lake.

Despite this not being fully functional, I will merge this and continue to work on a broader set of issues that will detail the path to getting this all fully deployed on production.

Some general details but through this I was able to take metrics calculation from at least an hours long process for each metric to one that can be run in parallel and calculated within mins for the entire backlog. With further horizontal scaling we'd be able to operate even faster.

Some things learned:

• Apache Arrow Flight server is useful and can be made fast but due to GIL constraints with python and the speed of inserting dataframes from sqlmesh into trino it likely won't be fast at all
• We might want to do this with spark in the future but it's less clear how to run a persistent plugin like we do right now with duckdb and dask. I had done some preliminary searching there and didn't find anything that looked nearly as simple or useful as what we do with duckdb + dask in ~50 LOC.
• Seeing as IO is the limiter, writing to gcs directly and importing into iceberg is likely the fastest (still needs further testing)
• If there was a way to write iceberg tables directly with python that was fast, this would be the fastest method of generating these tables. Pyiceberg doesn't seem to be that tool (potentially more research needs to be done)
• Duckdb doesn't read iceberg tables that quickly and it can't do partitioned reads. Exporting with trino to already processed parquet files is significantly faster when trying to load a table into duckdb. These are probably a few reasons for this
  • Without partitioned reads we can't read a specific time period quickly on duckdb.
  • Trino operates across multiple nodes to do the export.
  • After all this, downloading without processing is significantly faster

[vercel[bot]]

The latest updates on your projects. Learn more about Vercel for Git ↗︎

Name	Status	Preview	Comments	Updated (UTC)
kariba-network	✅ Ready (Inspect)	Visit Preview	💬 Add feedback	Nov 19, 2024 8:03am
oso-www	✅ Ready (Inspect)	Visit Preview	💬 Add feedback	Nov 19, 2024 8:03am

[oso-prs[bot]]

Test deployment unnecessary, no dbt files have been changed.