Harlequin: DuckDB IDE for the terminal

For those asking what DuckDB is: columnstore databases like DuckDB may be slower at data ingestion, but are very quick at multi-GB sums, counts, and aggregations.

Re: Harlequin: DuckDB IDE for the terminal

#8

Post by maxmcd » Wed, Sep 20, 2023, 7:40 PM UTC

tschellenbach wrote: post #3

what are the benefits of DuckDB?

it’s like a columnar store sqlite, better performance for analytical data

Re: Harlequin: DuckDB IDE for the terminal

#9

Post by hobs » Wed, Sep 20, 2023, 8:05 PM UTC

tschellenbach wrote: post #3

what are the benefits of DuckDB?

I like it for writing easy sql locally transforming data frames without having to think too hard about it.

Re: Harlequin: DuckDB IDE for the terminal

#10

Post by zlurker » Wed, Sep 20, 2023, 8:07 PM UTC

quadrature wrote: post #6

If anyone here is using DuckDB in production i'd love to hear what your stack looks like over the entire lifecycle of extract->transform->load.

We orchestrate our ETL pipelines with dagster. We only use duckdb in a few of them but are slowly replacing pandas etls with it. For some of our bigger jobs we use spark instead.

Essentially it's: 1. Data sources from places such as s3, sftp, rds 2. Use duckdb to load most of these with only extensions (I dont believe there's one for sftp, so we just have some python code to pull the files out.) 3. transform the data however we'd like with duckdb. 4. convert the duckdb table to pyarrow 5. Save to s3 with delta-rs

FWIW, we also have this all execute externally from our orchestration on an EC2 instance. This allows us to scale vertically.