Export Data Marts to Parquet?

[coussens]

Thanks again for creating such a valuable open-source project!

I had a minor feature improvement suggestion, which is an option to export Data Marts as parquet files. This would benefit both users of SageRx as well as those downloading the exported Data Marts off of the CodeRx website:

• Columns would have data types, so there's no risk of users parsing from CSVs into different data types (e.g., the dreaded accidental conversion of NDCs to integers!)
• File sizes would be much smaller (leading to faster downloads, and less storage required)
• Better performance, integration with apache arrow, etc

This could be readily incorporated into the export_marts DAG in python, or even by incorporating the pg_parquet extension for Postgres.

Let me know what you think!

[jrlegrand]

I'm sorry I haven't responded to this. I did think a good deal about it. I just haven't had the need to do it so far. I think it's a really good idea and also know you've contributed a PR for this - which I do want to get merged some point soon.

Would you take a look at this other PR and let me know what you think about incorporating something like marimo into SageRx?

#413

Also a friend of mine said that marimo, DuckDB, ibis, and Altair will be the future of data everything. I am really not familiar with any of these aside from marimo recently (which I really like) - wondering if you've had any experience with them and what you would say about that.

Thanks!

[coussens]

No worries @jrlegrand! Some thoughts:

1. Did you mean to link to my PR, or a different PR (since you said "this other PR" I'm guessing the latter).
2. I don't personally have any experience (yet) with marimo, but it looks really cool and I'm excited to try it out! What was your thinking in terms of how it would be integrated into SageRx -- i.e. what's the core functionality that you want to add?
3. IMO, your friend sounds wise, haha. I've been a big fan of duckdb since first using it early this year -- frankly, it's unparalleled in terms of performance, ease of use, and portability. Plus, there's been rapid proliferation of APIs for interacting with it. Duckdb also has ridiculously performant zero-copy transfer from Apache Arrow and very efficient/effective use of Parquet metadata for data streaming, making storage of data in Parquet format even more appealing.
4. As a long-time R user (but using Python more and more these days) who loves to avoid writing SQL whenever possible, I'm also naturally a fan of ibis, which closely resembles dbplyr in terms of having "one API to rule them all" for seamless, lazily-evaluated backend-agnostic data work.

IMO, I think a simple, elegant, performant pipeline for SageRx could be achieved with basically no external dependencies outside of Python if it were shifted to:

• Dagster as the orchestrator (which is comes with a lot of perks -- I'm far from an expert, but I've enjoyed my experiences with it a lot more than Airflow) with tables as assets
• Tables written to parquet files rather than having to build a PG database
• Parquet files registered as views/tables in duckdb

Of course, SageRx is your baby, so the path forward it's totally up to you. But I'm happy to help where/when I can!

[jrlegrand]

Def meant to link to a diff PR - I edited my original comment. Here's the correct PR (I merged it into main today): #413

I'm thinking of marimo as the main entry point to do exploratory / development work with SageRx - kind of as a replacement to pgAdmin which comes bundled with SageRx. I do a lot of messing around with writing queries into pgAdmin and they all get lost. With marimo, I would be working in a notebook saved to my local machine and not discoverable by git. If I wanted to, I could easily share a notebook (python file) with you and you could stick it in your /notebooks folder and run it locally. I do feel like I'm just taking a long road to avoid putting this all on Databricks - but my ethos has always been kind of "open source and (hopefully) durable even if a bit clunky and technical".

I have a similar conflicting feeling about duckDB / parquet / etc etc... it's the "am I getting older and stuck in my ways" or "is this a shiny object du jour and will be replaced with gooseDB (made up) in 2 years"... What you described with Dagster -> parquet -> duckDB sounds really interesting - IF my goal is to make something super performant and shed a lot of dependencies... which I don't think is currently my goal. Where would the transformation part come in with your suggested approach? Would I still use dbt but point it at the duckDB database? Or is that where something like dplyr comes in (which I also have heard things about recently but have never heard about before).

Weirdly I am the opposite of you in terms of preference of SQL over Python. I love to write SQL over Python because it feels (to me) like there's closer to a single correct way to do a lot of things vs a ton of different ways to do things with Python - some of which come with a bunch of different dependencies... and i feel like well-formatted SQL is fairly (non-technical) human-readable whereas I find Python code hard to read (even my own old Python code).

If you have time to mess around with marimo + SageRx, would love to hear your feedback!

[coussens]

Re: marimo -- I can totally see what you're going for now, and yes I think that would be a superior option to querying in pgAdmin. It didn't occur to me initially because I personally hadn't used that functionality -- when I used SageRx, I was generally just exporting the desired tables to parquet and then working with the data in R/Python. I also wholeheartedly agree with the goal of being fully open source and durable.

I totally respect the hesitance to completely rebuild the pipeline and concerns about obsolescence -- and you're right, if performance isn't a key concern (which is understandable for this use case), it may not be worth it. However, while DuckDB is definitely on the newer side (but very rapidly gaining momentum), Parquet has been around for a decade and is very mature (it's the underlying file format for virtually all data lakes these days), so I wouldn't worry about it falling too far out of favor anytime soon.

Re: data transformations -- Dagster integrates well with DBT, and DBT also supports DuckDB, so I'm guessing not too much would need to be changed there if you ever wanted to switch.

My bias against using SQL is probably more a reflection of how I learned to interact with data, and that a lot of work that I need to do requires R/Python, and I don't like having to switch back and forth between writing SQL and R/Python in the same script/project. That, and I find that common operations like repeated joins are super clunky in SQL compared to alternatives (I think this post in particular resonates with me). But this is part of the reason that DuckDB is so beautiful in collaborative environments -- people who prefer SQL can simply query it in SQL, and those who don't can instead use APIs like ibis (or dbplyr, the closest R analogue) that effectively write the SQL for you.

At any rate, thanks again for all of your hard work building this extremely valuable data pipeline (as evidenced by the Tuva project relying on SageRx -- congrats!). When I get a chance to play with marimo a bit, I'll be happy to share my thoughts!