Storing as parquet, and using hive path partitioning, you can get passable batch performance assuming you queries are not mostly scans and aggregates across large portions of the data. On the order of ten seconds for regex matching on columns for example.

I thought it was a great way to give analysts direct access to data to do adhoc queries while they were getting familiar with the data.

You should give us at MotherDuck a try for this use case.

(Co-founder)

Wouldn't that be pretty slow? :) Why not store it on the EC2 instance directly? Or locally on your computer?

1 TB is .. not a lot of data .. I rent a server with 15 TB for ~50$ per month and buying a new 2TB disk is less than 100$.

DuckDB supports partial reading of Parquet files (also via HTTPS and S3) [1], so it can limit the scans to the required columns in the Parquet file. It can also perform filter pushdown, so querying data in S3 can be quite efficient.

Disclaimer: I work at DuckDB Labs.

[1] https://duckdb.org/docs/data/parquet/overview#partial-readin...

oooh, cool

Does DuckDB supports partial reading of .duckdb files hosted externally?

> Why not store it on the EC2 instance directly?

Since the data in S3 receives updates every few hours, querying it directly ensures queries are on up-to-date data, whereas creating a copy of the dataset on the EC2 would necessitate periodically checking for updates and moving the deltas across to the EC2 (not insurmountable; but complexity worth avoiding if possible).

ah, I see. I though you decided t put it there (also, what the sibling said makes it a better idea)

We just wrote a post[0] very similar to what you're thinking. Let me know if you have any questions.

0 - https://www.definite.app/blog/duckdb-datawarehouse

We use it for simple use-cases and experimentation, especially because it works so well with various data formats and polars. Personally, I prefer to maintain proper SQL queries against duckdb including re-usable views, and then use polars for the remaining fiddling/exploration.

In combination with sqlalchemy, it is trivial to lift an app to other OLAP systems.

I suggest you try using StarRocks to query the data lake directly. I know many large companies, including Tencent and Pinterest, are doing this. StarRocks has a truly mature vectorized execution engine and a robust distributed architecture. It can provide you with impressive query performance.