3 ms·

I solved this problem (to my personal satisfaction) by adapting sqlalchemy for this. Sqlalchemy was not designed for complex data warehouse ELT for sure, but al

I solved this problem (to my personal satisfaction) by adapting sqlalchemy for this. Sqlalchemy was not designed for complex data warehouse ELT for sure, but all it took was abstract out a few "quirks" of sqlalchemy and I was able to get a framework that's as expressive as spark, and hence extremely modularizable (not to mention testable), and compiles to regular SQL very easily.

andreareina 6y ago
You still need to test against a live database instance right?
- ramraj07 6y ago
  Sometimes yes, sometimes no! If the sql you're using is testable syntactically in sqlite itself, then your tests can be very light and done on a in memory sqlite database. The standardization of a dataframe like object allows me to infer the schema going into and out of a method and automatically create and tear down the expected tables! However sometimes you use custom SQL for your specific dialect so the test has to happen on that database. But you can still write some isolation wrappers to run them on a development db. They probably won't run in a second so you might have to keep them in a separate pipeline or something
chrisjc 6y ago
Any chance you could get into a little more detail about this framework? Sounds very interesting. Any reason you developed your framework instead of using a tool like dbt?
- ramraj07 6y ago
  I will go into brief details, but will clear with my org and post more on HN later for sure. But the gist is I try to extract any sqlalchemy representation as an 'alias' object like: ORMModel.__table__.alias() select([query.c.id]).alias() And wrap it with a class that's called a DataFrame. This class can then have methods implemented such as 'with_column_renamed' and 'join' which can just take simple strings and do all the sqlalchemy heavy boilerplate inside. The goal is to make it almost similar to spark code, where each operation returns the DataFrame with the logic applied (continuously updating the internal alias object). With this DataFrame, we always know the schema of the query, so it makes it easy to write generic transformer functions and pass them to an 'apply' method just like in spark. You can always reach out to the power of sqlalchemy in any step to customize the logic, and write custom compilers for temp tables, etc. This also means you can write unit tests in a very generic fashion if you have standard transformations that process one DataFrame. One reason I didn't go with dbt is that we already have our codebase in python, and going with dbt would have fully siloed the pipelines to Jinja SQL. Also not a huge fan of a pipeline mandatorily split into hundreds of files often having just a few lines each!

andreareina 6y ago

You still need to test against a live database instance right?

ramraj07 6y ago
Sometimes yes, sometimes no! If the sql you're using is testable syntactically in sqlite itself, then your tests can be very light and done on a in memory sqlite database. The standardization of a dataframe like object allows me to infer the schema going into and out of a method and automatically create and tear down the expected tables! However sometimes you use custom SQL for your specific dialect so the test has to happen on that database. But you can still write some isolation wrappers to run them on a development db. They probably won't run in a second so you might have to keep them in a separate pipeline or something

chrisjc 6y ago

Any chance you could get into a little more detail about this framework? Sounds very interesting. Any reason you developed your framework instead of using a tool like dbt?

ramraj07 6y ago
I will go into brief details, but will clear with my org and post more on HN later for sure. But the gist is I try to extract any sqlalchemy representation as an 'alias' object like: ORMModel.__table__.alias() select([query.c.id]).alias() And wrap it with a class that's called a DataFrame. This class can then have methods implemented such as 'with_column_renamed' and 'join' which can just take simple strings and do all the sqlalchemy heavy boilerplate inside. The goal is to make it almost similar to spark code, where each operation returns the DataFrame with the logic applied (continuously updating the internal alias object). With this DataFrame, we always know the schema of the query, so it makes it easy to write generic transformer functions and pass them to an 'apply' method just like in spark. You can always reach out to the power of sqlalchemy in any step to customize the logic, and write custom compilers for temp tables, etc. This also means you can write unit tests in a very generic fashion if you have standard transformations that process one DataFrame. One reason I didn't go with dbt is that we already have our codebase in python, and going with dbt would have fully siloed the pipelines to Jinja SQL. Also not a huge fan of a pipeline mandatorily split into hundreds of files often having just a few lines each!