Maintainable ETL Practices at Stitchfix
multithreaded.stitchfix.com
Maintainable ETL Practices at Stitchfix
1–10 of 20 posts
Re: Maintainable ETL Practices at Stitchfix
#2Huge +1 to leveraging SQL as much as possible during ETL.
We recently moved our data warehouse to Snowflake. Along the way, we adopted an ELT paradigm instead of ETL, where you load your raw data into the warehouse and then perform any transformations in the warehouse. A tool called dbt (https://www.getdbt.com/) has made this a joy. You write SQL select queries in SQL files, and dbt creates a DAG based on this SQL so it can efficiently create tables/views in parallel. dbt also comes with some nifty testing facilities to validate your data once it is transformed.
This is a great article, thank you for writing it!
Re: Maintainable ETL Practices at Stitchfix
#3Huge +1 to leveraging SQL as much as possible during ETL. We recently moved our data warehouse to Snowflake. Along the way, we adopted an ELT paradigm instead of ETL, where you load your raw data into the warehouse and then perform any transformations in the warehouse. A tool called dbt ( https://www.getdbt.com/ ) has made this a joy. You write SQL select queries in SQL files, and dbt creates a DAG based on this SQL…
DBT is fantastic, I very strongly recommend it.
Re: Maintainable ETL Practices at Stitchfix
#4Too large a chunk of my life was burnt the ETL from hell.
Other than SQL, I can't see any other tool that would have helped.
I found the easiest way was just dump tables of data into long varchars - even the supposedly numeric stuff, because often enough it wasn't - then scrub & filter it from there. (NB, if you take anything from this post, make it that).
There were multiple challenges, but one that was most painful was the sheer filth mixed into the data. Too often even a human couldn't work out what the heck was supposed to be in some field. Often we could, but it ate a lot of time.
It doesn't sound like you ETLs are anything like mine; you start with pretty clean data. You are lucky.
Re: Maintainable ETL Practices at Stitchfix
#5Huge +1 to leveraging SQL as much as possible during ETL. We recently moved our data warehouse to Snowflake. Along the way, we adopted an ELT paradigm instead of ETL, where you load your raw data into the warehouse and then perform any transformations in the warehouse. A tool called dbt ( https://www.getdbt.com/ ) has made this a joy. You write SQL select queries in SQL files, and dbt creates a DAG based on this SQL…
I am not a big fan for the term ELT
Just because you are using a database as your work space doesnt mean you Load your data before transformation
Most complex dw, have multiple layers of transformation, and several workspaces along the way
There is always a final step where you load the clean data, into your clean schema, used for querying the data
ETL
Extract = Fetch the data you want
T = Make all necessary change
L = Create a clean copyRe: Maintainable ETL Practices at Stitchfix
#6Huge +1 to leveraging SQL as much as possible during ETL. We recently moved our data warehouse to Snowflake. Along the way, we adopted an ELT paradigm instead of ETL, where you load your raw data into the warehouse and then perform any transformations in the warehouse. A tool called dbt ( https://www.getdbt.com/ ) has made this a joy. You write SQL select queries in SQL files, and dbt creates a DAG based on this SQL…
I am not a big fan for the term ELT Just because you are using a database as your work space doesnt mean you Load your data before transformation Most complex dw, have multiple layers of transformation, and several workspaces along the way There is always a final step where you load the clean data, into your clean schema, used for querying the data ETL Extract = Fetch the data you want T = Make all necessary change L…
Multiple layers,
You have:
1. The raw data layer where data comes directly from buisiness log
2. Common data layer where one copy of data serves multiple purposes
3. Application layer where final result is presented
Re: Maintainable ETL Practices at Stitchfix
#7Huge +1 to leveraging SQL as much as possible during ETL. We recently moved our data warehouse to Snowflake. Along the way, we adopted an ELT paradigm instead of ETL, where you load your raw data into the warehouse and then perform any transformations in the warehouse. A tool called dbt ( https://www.getdbt.com/ ) has made this a joy. You write SQL select queries in SQL files, and dbt creates a DAG based on this SQL…
I am not a big fan for the term ELT Just because you are using a database as your work space doesnt mean you Load your data before transformation Most complex dw, have multiple layers of transformation, and several workspaces along the way There is always a final step where you load the clean data, into your clean schema, used for querying the data ETL Extract = Fetch the data you want T = Make all necessary change L…
ETL can include the destruction of raw data between the T & L. ELT assumes it is retained. This is a legacy from days when storage was expensive and it often wasn't tenable to retain everything.
Re: Maintainable ETL Practices at Stitchfix
#8Huge +1 to leveraging SQL as much as possible during ETL. We recently moved our data warehouse to Snowflake. Along the way, we adopted an ELT paradigm instead of ETL, where you load your raw data into the warehouse and then perform any transformations in the warehouse. A tool called dbt ( https://www.getdbt.com/ ) has made this a joy. You write SQL select queries in SQL files, and dbt creates a DAG based on this SQL…
DBT has been a game changer for us, such an awesome tool.
Re: Maintainable ETL Practices at Stitchfix
#9In discussions like this, I often wonder how there can be enough data to require a "big data" pipeline with things like Spark and Presto.
Stitch Fix seems to be one of those online services that send you sets of clothes that they think fit your style. That seems like a really narrow, low-data kind of industry. How much data can they possibly have? And why so big on the backend? In January 2018 they had 100 engineers. Presumably they're even larger now. Just for a service that sends out clothing.
Maybe I'm lacking in imagination or insight into what takes to run a company like this. On the other hand, a single PostgreSQL instance can run complex ad-hoc queries, with CTEs and everything, on a single node involving millions, even billions, of rows.
Re: Maintainable ETL Practices at Stitchfix
#10Earlier quoted context omitted.
I am not a big fan for the term ELT Just because you are using a database as your work space doesnt mean you Load your data before transformation Most complex dw, have multiple layers of transformation, and several workspaces along the way There is always a final step where you load the clean data, into your clean schema, used for querying the data ETL Extract = Fetch the data you want T = Make all necessary change L…
ETL can include the destruction of raw data between the T & L. ELT assumes it is retained. This is a legacy from days when storage was expensive and it often wasn't tenable to retain everything.
We held onto middle data when I worked in ETL 15 years ago. It's normal to save data in the middle for unit testing and lookup tables. I agree with GP this doesn't need a new term.