ETL Pipelines with Airflow: The Good, the Bad and the Ugly • Hacker News Board

Re: ETL Pipelines with Airflow: The Good, the Bad and the Ugly

#61

Post by geoduck14 » Sat, Oct 09, 2021, 1:19 AM UTC

TrealTwan wrote: post #40

The article says that "SQL is taking over Python to transform and analyze data in the modern data stack". Are other people starting to notice this at ELT becomes more populate than traditional ETL? Haven't used Airflow before but use Azure Data Factory in my org to load the raw the data into the data warehouse and then transform into data models using SQL.

In a past role, we used Airflow to do the transformations. Airflow accomplished a couple of things that you won't get from a "barebone run my SQL transform":

Retrieve credentials from X

Rewrite SQL (maybe QA is repointed to PROD)

Run, test for performance, then promote/notify/whatever

That being said, my last org was an early adopter of Airflow, and we deployed it before there were "best practices" - and it was an unpleasant experience. In my current role, we are investigating schedulers and Airflow is in the "I really hope I don't have to do this" list of tools. ADF is in the "this will work until we find the real solution" list

Re: ETL Pipelines with Airflow: The Good, the Bad and the Ugly

#62

Post by xkcd-sucks » Sat, Oct 09, 2021, 1:26 AM UTC

fredliu wrote: post #20

I've never used Airflow, but used Step Function in AWS to pretty much achieved the same things this article described. I wonder if anybody has used both and what are the pros and cons between them? Besides the obvious reason of Step Function in AWS so it would work better within AWS ecosystem and Airflow is open source and service/provider agnostic?

Airflow: Much has been written about good and bad. My bads included poor support for event driven workflows; goods included ease of extensibility. A lot has happened since I stopped using it.

AWS stepfunctions: Goods include okay event driven workflows, integration with AWS products, offloading lower level infrastructure to AWS-only people. Bads are everything else - It gives you a set of remarkably and weirdly limited primitives from which you may assemble something like Airflow. If you have any important use case, avoid it like the plague because it is half-baked and limited to a degree that only amazon can accomplish, yet it is capable enough for an initial POC to seem impressive.

Re: ETL Pipelines with Airflow: The Good, the Bad and the Ugly

#63

Post by yashasolutions » Sat, Oct 09, 2021, 1:27 AM UTC

psychometry wrote: post #56

pid-1 wrote: post #34

Earlier quoted context omitted.

+1 In my org we never use Airflow to compute anything. We only use a single operator: PythonOperator. All business and data access logic is encapsuled in REST APIs. Our DAGs are responsible for calling those APIs in the correct order, in the correct time, retrying when needed. I really dislike using Airflow for anything else, as it generally becomes a huge mess.

If it's all Python, why not use Prefect?

First time I read about Prefect, can you briefly share why you would use prefect over airflow if using only python? (that's what I am about to be doing soon...)

Re: ETL Pipelines with Airflow: The Good, the Bad and the Ugly

#64

Post by FridgeSeal » Sat, Oct 09, 2021, 2:03 AM UTC

41b696ef1113 wrote: post #53

Has anyone kicked the tires on Airflow, Prefect, and Dagster and care to give their thoughts? My initial foray into Airflow 1 met a lot of complexity that both Prefect and Dagster claim to minimize.

Tried some of them, threw them away for various reasons and used Argo Workflows instead.

Airflow mixes orchestration and the actual transformation, and I wasn’t a fan of being ao intimately tied to Python. Argo let’s us run arbitrary containers (a mix of Spark, Rust, etc) and played nicer with the tools we were already using (Kafka, K8s etc), didn’t require us to write Python and cleanly separated orchestration and actual ETL/ELT work. It’s working fantastically for us.

Re: ETL Pipelines with Airflow: The Good, the Bad and the Ugly

#65

Post by jonahbenton » Sat, Oct 09, 2021, 2:32 AM UTC

yashasolutions wrote: post #63

psychometry wrote: post #56

Earlier quoted context omitted.

If it's all Python, why not use Prefect?

First time I read about Prefect, can you briefly share why you would use prefect over airflow if using only python? (that's what I am about to be doing soon...)

Have been a Prefect user since their first release. In a nutshell, it's a native pythonic DAG tool, where Airflow is a DAG tool that happens to be written in Python.

Re: ETL Pipelines with Airflow: The Good, the Bad and the Ugly

#66

Post by mr_toad » Sat, Oct 09, 2021, 2:52 AM UTC

CRConrad wrote: post #50

TrealTwan wrote: post #40

The article says that "SQL is taking over Python to transform and analyze data in the modern data stack". Are other people starting to notice this at ELT becomes more populate than traditional ETL? Haven't used Airflow before but use Azure Data Factory in my org to load the raw the data into the data warehouse and then transform into data models using SQL.

> Are other people starting to notice this at ELT becomes more populate than traditional ETL? I invented ELT. No, duh, of course not... But: I and my then-colleagues, other DW consultants, started noticing some time in the early-to-mid-00s that what we were doing didn't actually follow the order of the "ETL" acronym, and occasionally commented to each other along the lines of "shouldn't this be called 'ELT' to be mor…

All the warehouses I’ve worked on have used ELT more than ETL, going back 20 years or more. I think that at the coalface it’s always been the more common paradigm. It’s just that the people doing it are too busy writing code to write blogs.

Re: ETL Pipelines with Airflow: The Good, the Bad and the Ugly

#67

Post by slotrans » Sat, Oct 09, 2021, 3:12 AM UTC

iroddis wrote: post #55

I recently started working on my own DAG execution framework, after failing to get some patches into Airflow to make the scheduling easier to reason about. My typical use case was orchestrating DAGs with thousands of vertices, and airflow would silently wedge itself and fail to report errors. Daggy is just starting out, but I’m hoping it’ll become more robust and scalable as time goes on. https://gitlab.com/iroddis/d…

(workflow/DAG systems are an area of special interest for me...)

Happy to see you're supporting runtime-dynamic DAG shape. That's one of the critical things Airflow is missing.

But, if I'm inferring correctly, it looks like you're recording task state in a similar way to Airflow, i.e. in some kind of data store. I suggest you look at how Luigi models task state: using "targets" which are a task's materialized outputs (e.g. a file, or anything whose existence can be tested for). Luigi's model isn't perfect but it's very valuable for tasks to be able to recognize the condition "my output exists, therefore I don't need to run".

...which leads me to the most important feature you need to check off, that most competitors are getting wrong: resuming a partially failed build. It's often the case that one node of a DAG will fail with a non-recoverable error that can't be solved with retries, and so the DAG as a whole will fail. But if we can fix that error in a way that's compatible with the work that was already done (e.g. fix a network ACL, IAM policy, etc), we should be able to start the DAG again and have it resume at the point of failure, and not do any work over again.

Luigi does this very well, Airflow does it adequately, and I think all the other tools in this space (Prefect, Dagster, Nextflow... tell me about others!) don't do it at all.

Re: ETL Pipelines with Airflow: The Good, the Bad and the Ugly

#68

Post by dominotw » Sat, Oct 09, 2021, 3:31 AM UTC

rockostrich wrote: post #23

KptMarchewa wrote: post #21

Earlier quoted context omitted.

In the end, analysts are cheaper than engineers.

Not if it's just a part of those engineers' jobs. They're already familiar with the underlying application data so owning the transformation is just understanding what the data needs to look like and documenting it. They're going to need to document the raw data anyway to avoid those analysts asking them a million questions. Might as well avoid hiring analysts who can also learn the transformation bit and just give t…

exactly. analysts are always a step behind engineers when it comes to really understand what data really means and what changes are coming down the line. this always results in delays, broken pipelines ect. modern tools like dbt make it easy for data producing teams to also own T part.

Re: ETL Pipelines with Airflow: The Good, the Bad and the Ugly

#69

Post by rpedela » Sat, Oct 09, 2021, 3:31 AM UTC

verdverm wrote: post #5

Arimbr wrote: post #2

[author of the article] My main concern about using Airflow for the EL parts is that sources and destinations are highly coupled with Airflow transfer operators (e.g. PostgresToBigQueryOperator). The community needs to provide M * N operators to cover all possible transfers. Other open-source projects like Airbyte, decouple sources from destinations, so the community only needs to contribute 2 * (M + N) connectors. A…

Is there really a 1-1 mapping between SQL and T? What about use cases where the data lives in an object store? How does dbt deal with that?

If you can write a SQL query or a set of SQL queries to do your transformation, then you can use DBT. DBT doesn't do transformation itself rather it helps you manage all the dependencies between your SQL models. Whether you can use SQL depends on your data and database/warehouse functionality. For example, JSON parsing support is pretty good now in many databases and warehouses. If your objects can be represented as JSON, then you could write SQL via DBT to parse the objects into columns and tables.

Re: ETL Pipelines with Airflow: The Good, the Bad and the Ugly

#70

Post by dominotw » Sat, Oct 09, 2021, 3:34 AM UTC

rockostrich wrote: post #12

Arimbr wrote: post #10

Earlier quoted context omitted.

Really good points! I don't think that Airflow is necessarily a problem if your data engineering team knows how to best use Airflow Operators, Hooks and DAGs for incremental loads. But because Airflow is not an opinionated ETL/ELT tool, most often I see a lot of custom code that could be improved... You know there is this "data mesh" hype now. I think the idea behind is to empower data consumers within the company (d…

Yea, I wasn't familiar with Airbyte before writing that comment so now I'm seeing the value in it. We have tons of teams asking "how do I get this data into BigQuery" and the answer is usually "use this airflow operator to dump it into GCS and then use this airflow operator to load it into BigQuery" which isn't super useful for a non-technical person or even really any technical person not familiar with Airflow. A me…

>the answer is usually "use this airflow operator to dump it into GCS and then use this airflow operator to load it into BigQuery"

why not hide these steps behind a yaml file and construct dags from those. devs already are used to writing yaml files for ci, kubernetes ect.