Isn’t pandas centric ETL much more memory intensive and less compute efficient than using SQL?
Open Source Python ETL
71–80 of 127 posts
Re: Open Source Python ETL
#72Earlier quoted context omitted.
>> Code as ETL was a huge industry shift No it’s not. Try and see what banks, retail, manufacturing, various large enterprises still use. They need scale, observability, modularity, and maintainability.
Gui Etl makes a promise that you dont need a programmer to wield it. But it is a false promise. > They need scale, observability, modularity, and maintainability. Seems orthogonal to code-vs-gui dimension.
No, unfortunately those factors are all very related.
Once you have GUI ETL tools, in my experience, you can't modularise because the ETL tool makes assumptions about where the boundaries are that are different from what suit the domain in question. Observability falls over because you're now limited to the ETL tool instead of the domain. Scale suffers because now the ETL data model needs to be preserved and high-performance tricks might need the entire tool to be worked around, etc, etc.
Code is the highest-performance environment we have for working with huge complex systems made of if statements and loops. Giving that up to go to a tool doesn't actually yield any advantages; there needs to be an abstraction with huge practical benifits and a DAG isn't it. Modeling a DAG in a true programming language isn't hard enough to justify moving away from an IDE.
An ETL pipeline in practice is still uncomfortably close to a big spaghetti of if-thens and loops, tooling and extra models create patterns that often block a lot of the useful properties you list. The real gains come from not writing a custom scheduler, but splitting out the valuable scheduler from the ETL tool means that you have a scheduler, not an ETL tool. Sometimes there is an ecosystem of adaptors that makes a big difference, but if that doesn't meet your engineering requirements then the tool is useless (because you don't have any real levers to pull on the scale/observability/modularity and maintainability front).
Re: Open Source Python ETL
#73Isn’t pandas centric ETL much more memory intensive and less compute efficient than using SQL?
That's kind of the tradeoff you make with any low-code/no-code technology. You leverage prebuilt components and string them together to achieve some kind of task. Which isn't most efficient thing in the world to do but it does work assuming you have enough compute resources to throw at it, and return what you generally achieve is an end product that's completed faster than the traditional development route.
You could just use SQL but then you'd have to develop and test the entire infrastructure to support your component-oriented architecture from scratch, and at that point you're kind of just reinventing the wheel because that's basically just pandas with less features.
Low-code is kind of just Authorware for a new generation... assuming you're old enough to remember that technology.
Re: Open Source Python ETL
#74Since there are "ETL" people here, I have a couple of naive questions, in case anybody can answer: 1) Are there any"standard"-ish (or popular-ish) file formats for node-based / low-code pipelines? 2) Is there any such format that's also reasonably human readable / writable? 3) Are there low-code ETL apps that (can) run in the browser, probably using WASM? Thanks and sorry if these are dumb questions.
They're good questions, but they are not answerable blind. The correct choices depend too much on what problems you are trying to solve, the formats and scale of the data involved, the tolerances for downtime and what other software is being used.
My advice is to avoid, in general, low code tools if you plan to have software engineers involved. And once there aren't any software engineers whatever gets built is going to be a mess by software engineering standards so just roll with it. Any tool is equally likely to hit your pain points (and generate an unmanageable mess).
Re: Open Source Python ETL
#75It's a good idea, but from the docs it looks like the high level abstractions are wrong.
If my data pipeline is "take this table, filter it, output it", I really don't want to use a "csv file input" or a "excel file output".
I want to say "anything here in the pipeline that I will define that behaves like a table, apply it this transformation", so that I can swap my storage later without touching the pipeline.
Same things for output. Personally I want to say "this goes to a file" at the pipeline level, and the details of the serialization should be changeable instantly.
That being said, can't complain about a free tool, kudos on making it available !
Re: Open Source Python ETL
#76Low code ETL tools (informatica, Appworx, talend, pentaho, ssis) were the original services for ELT/ETL. A lot of progress was made to go towards ETL-as-code starting with Airflow/Luigi. Going back to low code seems backwards as this point. (I have used all of the above tools in my 15+ yr career. Code as ETL was a huge industry shift)
I work as a Data Engineer and in my country Azure is pretty big, and as a consequence their Data Factory service has become a common choice for enterprises. It's a GUI based ETL tool, architects prefer it since it is a managed cloud service and supposedly is easy to use. In practice you lose all the benefits of abstraction, unit testing, proper CI/CD, etc. I haven't met an engineer that likes the service. Some projec…
>In practice you lose all the benefits of abstraction, unit testing, proper CI/CD, etc.
Why? We are pretty deep into the ecosystem.
Abstraction -> the only thing data factory does not allow you is to reference a previous activity as a variable, which makes sense if you don't want to let your customer blow up your product. Parametrize all you want.
Unit testing -> test all you want each activity, pipeline, flow, resume it from where it broke. Clone the entire thing into a test data factory, then deploy that once ready.
CI/CD -> the first step it nags you about is setting up CI/CD. If you want to get fancy, you setup a dev environment and deploy that to production after testing and sign-off.
Abstracting ETL only works when you remember or have the same people on staff that abstracted that ETL process. Data factory 'could' be visual but does not let you pull the same level of non-sense that SSIS would.
For example, we call data factory via API, the pipeline is fully abstracted, it does one thing, but it's inputs and outputs are controlled by the request.
Re: Open Source Python ETL
#77Earlier quoted context omitted.
Completely agree. I would also add that in my 25 years in the industry I have never actually come across an ETL workflow that was complex enough that it required people working it in code. Those opinions seem to occur before proper analysis happens.
So you have never needed to write SQL in your ETLs? I guess it depends on whether you consider that code, but at least 50% of the time I have to use SQL for one reason or another rather than just pointing the tool at a source and target.
Honestly? If you are making the transformation in SQL you've lost the T to the database server. Does it mean we don't do it? Is water wet?
The reality is that we shouldn't be making the transformation in written data, now you have staging tables, procs, views, resulting tables procs, views, a staging database (often called staging or etl), and an unhappy DBA who yells at you every time you cross-apply incorrectly.
ETL should be done before the data lands in SQL.
Re: Open Source Python ETL
#78Low code ETL tools (informatica, Appworx, talend, pentaho, ssis) were the original services for ELT/ETL. A lot of progress was made to go towards ETL-as-code starting with Airflow/Luigi. Going back to low code seems backwards as this point. (I have used all of the above tools in my 15+ yr career. Code as ETL was a huge industry shift)
Somewhat related to this discussion https://news.ycombinator.com/item?id=40646312
Tldr: as with dashboards, self-serve ETLs don't work outside very specific use cases or very simple pipeline.
Reason for that, as with bi tools, is that the complexity is not in manipulating data. There are tons of frameworks to do that efficiently.
The issue is interpreting data and it's semantics and evolving data pools to the business needs.
Re: Open Source Python ETL
#79Earlier quoted context omitted.
>> Code as ETL was a huge industry shift No it’s not. Try and see what banks, retail, manufacturing, various large enterprises still use. They need scale, observability, modularity, and maintainability.
I work in manufacturing (large industrial plant) and the data processes we have are honestly not great - mostly it is because there are a heap of legacy system and not a lot of commonality between our data sources we have a hideous mashup of Oracle, DB2, Microsoft SQL Server etc and different versions of the different databases. There's also more bespoke industry stuff like time series historians and SCADA systems/PL…
You may be interested in semantic web technologies as a means of modelling your different data sources and how they relate.
Re: Open Source Python ETL
#80Low code ETL tools (informatica, Appworx, talend, pentaho, ssis) were the original services for ELT/ETL. A lot of progress was made to go towards ETL-as-code starting with Airflow/Luigi. Going back to low code seems backwards as this point. (I have used all of the above tools in my 15+ yr career. Code as ETL was a huge industry shift)
Agreed. Well designed Airflow Operators (or taskflow) are basically the same level of effort as creating a box with a UI (honestly, easier IMHO), but the ability to go into code is important for every non-trivial pipeline.
I built a solid career replacing no-code-ETL tools with Airflow.