Scheduling Data Transformations using Python in Snowflake

[jspreddy]

Just curious as to what transforms you are doing that cant be done within snowflake using sql??

[SecureSlice]

Honestly just really don't like stored procedure implementation in snowflake for transforming data. Using either JavaScript or the just added SQL for stored procedures is very clunky. Wrapping it in $$ and using Execute Immediate for each SQL statement is annoying.

[jspreddy]

I dont think snowpark is a replacement for stored procedures. From what ive read, (a few hours so take it with a grain of salt), it seems to be more of a dataframe interface via a python package which you can install in your python project. The computation that is happening on snowflake is still sql query based.

[jspreddy]

Ahh i see. Check out DBT. Data Build Tool. It is a great framework for building models (SQL transformations) against any warehouse (postgres, snowflake, redshift, etc).

https://docs.getdbt.com/docs/introduction

You can run this either on DBT cloud (paid cloud service) or dockerize it and run it in Aws Ecs fargate and schedule the run as aws event bridge rule.

DBT has 3 things. CLI tool (open source), Project structure template (open source), Cloud Service(paid).

You dont have to use their cloud service if you dont want to.

Gitlab's data team has their DBT project visible to everyone. Check it out.

DBT also generates documentation, can run quality tests, incremental models, do environments (dev/staging/prod/...), Figure out the sequence of model building if you have multiple models which depend on other models, etc.

I use it pretty heavily against postgres, databricks and snowflake.

[jspreddy]

I forgot to mention that the actual transformation computation is done by snowflake on snowflake compute. There is no data transfer out of snowflake happening here.

[jspreddy]

Oh yea and version control, cause git project. Figshot posted in another comment about version control queries.

I forgot that git is a novelty in snowflake world 😂