Data warehouse / database choices

[ryscheng]

I just wanted to summarize the findings from all of our explorations so far, into BigQuery, Databricks, Databend, Trino, Spark, Clickhouse, Duckdb, etc.

While it would nice to have 1 thing that handled everything, there are 2 dimensions where there are fundamental trade-offs:

1. Scalable big data vs real-time queries

• On one hand, we need an affordable solution for our pipeline, handling 100's TB of data in our dbt pipeline, from the source data all the way down to the marts. This needs to be run at least once per day, while being reasonably affordable. High latencies okay. (e.g. BigQuery, Trino, Spark)
• On the other hand, we need to be able to quickly serve real-time queries against our mart models (<1TB) for our API and website. (e.g. Clickhouse, Postgres, Duckdb, Starrocks)

2. Consumer-pays publicly available data vs high performance on centrally managed data.

• This is where most high performance databases struggle. Most databases are designed for an enterprise environment where 1 credit card can be used to pay for all storage and compute. As an open public good, OSO pipelines are meant to be easily sharable, forkable, etc. We want a method for consumers directly pay for network egress and compute. We also want to be able to support any retail user on the Internet, including students from around the world. BigQuery appears to be the only data warehouse solution that does this well with Analytics Hub and a generous free tier for any Google user.
• With a Lakehouse architecture, we can get to a consumer-pays approach to storage and network egress, but not compute. It's too much to ask users to spin up their own scalable query processing infra.

Here's the current roadmap based on the information we have so far:

• Phase 0: dbt pipeline on BigQuery, copy marts to CloudSQL (Postgres)
• Phase 0.5: dbt pipeline on BigQuery, copy marts to Clickhouse - this should perform better than Postgres on the frontend
• Phase 1: dbt/sqlmesh pipeline with Spark over BigQuery storage, copy marts to Clickhouse - this could be cheaper than BigQuery processing
• Phase 1.5: try Trino instead of Spark? --- Is this cheaper or faster?
• Phase 2: sqlmesh pipeline with Spark over BigQuery storage, copy more models to Clickhouse --- see how much data we can serve in real-time
• Phase 3: Try running more of the pipeline directly in Clickhouse, copy results back to BigQuery or Delta Lakes or Cloudflare R2

TLDR, I think our priorities are:

1. Data sharing capabilities to the public, forkable data pipeline
2. Cost
3. Performance

[ryscheng]

Cross linking some exploration around metrics
#1970

We are currently trying Cube.dev. MetricFlow is the native dbt solution but not open source. sqlmesh metrics would be nice, but both isn't ready yet, and doesn't support Clickhouse.

Other products that David shared:

• https://www.tinybird.co/docs
• https://www.malloydata.dev/getstarted
• https://prql-lang.org/book/
• https://getdozer.io/docs/dozer/