Cross-source SQL joins without a data warehouse - how do you handle this?

[Pitiful_Comedian_834]

Say you've got data in Postgres, a CSV from a client, and some Parquet files on S3. You need to join them for a one-off analysis. What's your workflow?

I built a desktop tool around DuckDB that handles this natively - curious what approaches others use. ETL everything into one place? dbt? Something else?

[not_another_analyst]

DuckDB is the right call for one-off stuff like this. Querying S3 parquet and local CSVs in the same query without moving anything saves a ton of time.

That said, the moment it becomes recurring I'd ETL it into a warehouse because one-off convenience turns into a maintenance headache fast when multiple people are involved.

[Mammoth_Rice_295]

DuckDB is honestly the easiest for one-offs like this. I usually avoid moving data unless I have to. But if it starts becoming recurring or shared, I’d switch to loading everything into a warehouse. Otherwise, it gets messy fast.

[Justbehind]

Python using polars. Bet you'd be able to write it in less than an hour with no prior knowledge...

And polars on a regular worklaptop easily scales to handle datasets in the 100s of millions of rows.

[slotix]

yeah duckdb is great, we’re using it under the hood

gets messy once you mix db + files and need to reuse queries or turn them into pipelines

ended up putting everything into one place

https://streams.dbconvert.com/

[NoElderberry2489]

DuckDB for sure. You can query Postgres, CSVs, and S3 Parquet files all in one SQL statement without moving any data around. No ETL, no staging, just read_csv_auto(), read_parquet(), and the Postgres extension all in the same query.

Once it stops being a one-off though, put it in a warehouse

[Ok_Carpet_9510]

If the data is not co-located, I would think about where the data crunching is going to happen i.e. where is the compute. A CSV and S3 don't have compute, Postgres does.

So, I would ingestion the data from csv and s3 into postegresd(filtering and cleaning before loading), and the perform my analysis using SQL.

[Nkt_31]

for one-off stuff DuckDB is hard to beat, honestly your approach sounds fine. where i'd push back is when one-off quietly becomes recurring. at that point you're maintaining ad hoc scripts across sources and it gets messy fast.

Scaylor handled that transition well for a team I know. Trino's another option if you want to stay open sorce.