3 ms·

I've seen some "data engineering" scripts that were complete messes and beyond crazy. Some examples: Massively over engineered "pipelines" that process a few hu

I've seen some "data engineering" scripts that were complete messes and beyond crazy. Some examples: Massively over engineered "pipelines" that process a few hundred rows a day, but somehow manage to take forever to run. Developers that didn't know SQL beyond "select * from table", so they do all their summarization in Python. Or, worse, I've seen a Python script calling a shell script calling R calling something else, several more layers deep, when the same result could've all been done in SQL with a few temporary tables. Oh, then I'm asked to "give this a code review before so-and-so does a deployment tomorrow." Uh, it's a little late to address any of the fundamentals, but there are hard coded paths everywhere...

bob1029 2y ago
I recently got a bit of a shocked reaction when I proposed to directly load daily files into temporary SQL tables and then use merge commands within the database to load the final tables. My use of code is essentially a shim between an SFTP client and SQL Server in this scenario. Maybe ~200 lines to connect, locate the files, run the bulk load operation, and then invoke the merge commands. Most of the fun bits are in the actual merge scripts. Once your data is safely inside the database (temporary load tables or otherwise), there really isn't a good excuse for pulling it out and playing a bunch of circus tricks on it. Moving and transforming data within the RDBMS is infinitely more reliable than doing it with external tooling. Your ETL code should be entirely about getting the data safely into the RDBMS. It shouldn't even be responsible for testing new/deleted/modified records. You really want to use SQL for that. You'll also be able to recruit more help if everything is neatly contained within the SQL tooling. In my scenario, business analysts can look at the merge commands and quickly iterate on the data pipeline if certain customers have weird quirks. They cannot do the same with some elaborate set of codebases, microservices, etc. One specific thing that really sold me on this path was seeing how CTEs and views can make the T part of ETL 10000000x easier than even the fanciest code helpers like LINQ.
- snidane 2y ago
  The architecture is sound - typically called ELT these days. Dump contents of upstream straight into a database and apply stateless and deterministic operations to achieve the final result tables. SQL server is where this breaks though. You'll get yelled by DBAs for bad db practices like storing wide text fields without casting them to varchar(32) or varchar(12), primary keys on strings or no indexes at all, and most importantly taking majority of storage on the db host for tbese raw dumps. SQL Server and any traditional database scales by adding machines, so you end up paying compute costs for your storage. If you use a shared disk system with decoupled compute scaling from storage, then your system is the way to go. Ideally these days dump your files into a file storage like s3 and slap a table abstraction over it with some catalog and now you have 100x less storage costs and about 5-10x increased compute power with things like duckdb. Happy data engineering!
  - jamesblonde 2y ago
    You're basically describing the Lakehouse Tables architecture. Store your data as tabular data in Iceberg/Hudi/Delta on S3. Save a bucket on storage. Query with whatever engine you like (Snowflake, Redshift, BQ, DuckDB, etc).
    - aoeusnth1 2y ago
      Yes, this is the vast majority of my data work at Google as well. Spanner + Files on disk (Placer) + distributed query engine (F1) which can read anything and everything (even google sheets) and join it all. It’s amazingly productive and incredibly cheap to operate.
  - sevensor 2y ago
    My experience with delta was that the catalog, being stored in s3 itself, was unacceptably slow, and for our data volume, Airflow was prohibitively expensive. We spent a lot of engineering time working around both problems. Which is funny because the consultants who advised us to do this told us it was the best possible solution; tailor made for our application, foolproof in every way. After that we proceeded to pay for their “data” “science” “services,” which went about as well as my scare quotes would suggest.
- hinkley 2y ago
  Except everyone wants microservices each with its own database.
  - 392 2y ago
    If they can't explain why, they don't get the fun internet interview stuff
    - icedchai 2y ago
      It's so much simpler to have a shared "integration" database. Until you have actual, real need for separate data sources, don't.
tgv 2y ago
Some of my colleagues use Microsoft PowerBI, and indeed, they upload a few hundred rows of data (and a few hundred columns, which get unpivoted in powerbi to a say 40k rows). When they upload it, the powerbi instance overloads, and people get timeouts and such. That can last up to 20 minutes. I stay away from that as far as I can.
- Pxtl 2y ago
  This is what bothers me with MS SQL related tools - they all seem horrendously brittle. Everything seems prone to deadlocks, has weird edge-cases, and incomplete coverage of the API of the next tool they're talking to so you keep having to break open the abstraction and manually tinker in the next level.

bob1029 2y ago

I recently got a bit of a shocked reaction when I proposed to directly load daily files into temporary SQL tables and then use merge commands within the database to load the final tables. My use of code is essentially a shim between an SFTP client and SQL Server in this scenario. Maybe ~200 lines to connect, locate the files, run the bulk load operation, and then invoke the merge commands. Most of the fun bits are in the actual merge scripts. Once your data is safely inside the database (temporary load tables or otherwise), there really isn't a good excuse for pulling it out and playing a bunch of circus tricks on it. Moving and transforming data within the RDBMS is infinitely more reliable than doing it with external tooling. Your ETL code should be entirely about getting the data safely into the RDBMS. It shouldn't even be responsible for testing new/deleted/modified records. You really want to use SQL for that. You'll also be able to recruit more help if everything is neatly contained within the SQL tooling. In my scenario, business analysts can look at the merge commands and quickly iterate on the data pipeline if certain customers have weird quirks. They cannot do the same with some elaborate set of codebases, microservices, etc. One specific thing that really sold me on this path was seeing how CTEs and views can make the T part of ETL 10000000x easier than even the fanciest code helpers like LINQ.

snidane 2y ago
The architecture is sound - typically called ELT these days. Dump contents of upstream straight into a database and apply stateless and deterministic operations to achieve the final result tables. SQL server is where this breaks though. You'll get yelled by DBAs for bad db practices like storing wide text fields without casting them to varchar(32) or varchar(12), primary keys on strings or no indexes at all, and most importantly taking majority of storage on the db host for tbese raw dumps. SQL Server and any traditional database scales by adding machines, so you end up paying compute costs for your storage. If you use a shared disk system with decoupled compute scaling from storage, then your system is the way to go. Ideally these days dump your files into a file storage like s3 and slap a table abstraction over it with some catalog and now you have 100x less storage costs and about 5-10x increased compute power with things like duckdb. Happy data engineering!
- jamesblonde 2y ago
  You're basically describing the Lakehouse Tables architecture. Store your data as tabular data in Iceberg/Hudi/Delta on S3. Save a bucket on storage. Query with whatever engine you like (Snowflake, Redshift, BQ, DuckDB, etc).
  - aoeusnth1 2y ago
    Yes, this is the vast majority of my data work at Google as well. Spanner + Files on disk (Placer) + distributed query engine (F1) which can read anything and everything (even google sheets) and join it all. It’s amazingly productive and incredibly cheap to operate.
- sevensor 2y ago
  My experience with delta was that the catalog, being stored in s3 itself, was unacceptably slow, and for our data volume, Airflow was prohibitively expensive. We spent a lot of engineering time working around both problems. Which is funny because the consultants who advised us to do this told us it was the best possible solution; tailor made for our application, foolproof in every way. After that we proceeded to pay for their “data” “science” “services,” which went about as well as my scare quotes would suggest.
hinkley 2y ago
Except everyone wants microservices each with its own database.
- 392 2y ago
  If they can't explain why, they don't get the fun internet interview stuff
  - icedchai 2y ago
    It's so much simpler to have a shared "integration" database. Until you have actual, real need for separate data sources, don't.

tgv 2y ago

Some of my colleagues use Microsoft PowerBI, and indeed, they upload a few hundred rows of data (and a few hundred columns, which get unpivoted in powerbi to a say 40k rows). When they upload it, the powerbi instance overloads, and people get timeouts and such. That can last up to 20 minutes. I stay away from that as far as I can.

Pxtl 2y ago
This is what bothers me with MS SQL related tools - they all seem horrendously brittle. Everything seems prone to deadlocks, has weird edge-cases, and incomplete coverage of the API of the next tool they're talking to so you keep having to break open the abstraction and manually tinker in the next level.