Comment by allan_s
17 hours ago
As somebody new to this and with a use case very similar to yours , what would have been a more suitable solution for this ?
The guy who first built the architecture made the same decision as yours (I.e one local duckdb for each tenant to work as a copy of big query/their data warehouse) and I dont know what the state of the art for this kind of use cases ?
There's a few options.
Clickhouse, as I mentioned, can be a good final layer, as can postgres.
You can still use duckdb for intermediate transformations, even if the final data lives elsewhere.
duckdb can also access various external sources, such as s3, so you could use duckdb for transformations and write "classic" parquet files to S3 and query them with an engine of your choice (which, again, could also be duckdb, but nothing stopping you from using Trino or something along those lines).
All a question of scale, complexity, cost, and latency. For reasonably low latency, shipping a duckdb file to the edge is fine, I think. Makes CI/deployments more complicated. Or you could assemble the actual duckdb file on site - probably easier with K8s and an init container that can scale? Something like that, I don't use K8s for SkaldMaps, but I have experimented a bit.
For SkaldMaps, the backend is written in go and has an abstraction to plug in a different presentation data store, so I would just need to re-wire data platform to write the final tables to e.g. CH instead of duckdb.
Clickhouse has a more intentionally built ingestion system. Duckdb has concurrency limits so you can't have a writer and a reader on the same file if they're not the same process (multiple readers is fine).
But that's not too hard to work around. You can either have a single process that owns both writing and reading that file, or you can do a data lake where you post updates as parquet files into object storage, and duckdb handles the catalog. The Quack protocol also basically fixes this (though still in beta).
With Clickhouse, you can of course still have tenant separation, but you have to do it by managing users within Clickhouse that map to users/tenants of your main app, so that you can restrict SQL access by tenant to only their data store. Not a huge deal but I just like the Unix "it's just a file" simplicity of "Tenant A gets to run arbitrary SQL against their separate read-only, no-ATTACH duckdb file".
DuckDB's sweet spot is for 'smallish' lakehouses. So, ingestion should not be via duckdb, but rather something like Python/DLTHub for small scale or Spark for large scale or Kafka/Debezium/Flink for streaming data.
The CEO/Co-Founder of dltHub/dlt here.
For our community DuckDB is the default data warehouse for local development environment. Last month +90,000 users used dlt (and their AI code editor) to load data into DuckDB.
Because of our proximity to the DuckDB community we are seeing enterprise DuckDB usage first hand. People imo sleep on the data volumes DuckDB can handle. We see Fortune 100 companies use dlt and DuckDB in production on their Lakehouses in hybrid cloud deployments. I can eg mention Stellantis (Chrysler, Jeep, Peugeot etc) because they talk about it publicly.
Ducklake supports postgres for the catalog, so you get the postgres concurrency benefits + duckdb engine to read the parquet files in the bucket.
another variant:
i put duckdb on a lambda and pointed it at s3 for the data. my data was closer to 2GB but the queries were quick and nearly free with superset pointed at it
is your setup running into problems that makes you need something more?
2GB is so tiny it's almost irrelevant when talking about analytics?