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

#31

Post by 0x500x79 » Fri, Oct 08, 2021, 5:11 PM UTC

sails wrote: post #27

0x500x79 wrote: post #16

Some people have noted that this is a very Airbyte specific article, but I think that the lessons learned are still important. I have managed Airflow as a managed service for a company that has thousands of DAGs and one of our keys to success was splitting the compute and scheduling concepts into different components. We standardized on where our compute ran (Databricks, Spark, Lambdas, or K8s jobs) and had Airflow p…

How best to achieve this? I'm considering Astronomer or AWS hosted airflow, with a benefit to AWS having the compute components easily accessible within the AWS ecosystem. Starting out with a smaller scale and lower commitment to Airflow, so I'd like highest reliability with least hassle. https://aws.amazon.com/blogs/aws/introducing-amazon-managed-...

It depends on what other tools you are currently using for ETL (or want to use). One example is that we used Spark so we would use the Spark submit operator to submit jobs to clusters. You can also use the K8s Pod operator if you want to utilize containers for your compute.

There are a lot of options. We were adopters before AWS hosted airflow was a thing, so I don't have any experiencing running AWS hosted Airflow.

I haven't looked recently to see if some of the challenges we faced early on are solved now, but most of them stemmed from how DAG updates were handled: changing the start date on a DAG would break you DAG forever until you go update the database by hand. Things like this are(/were?) super painful and could get worse with a managed solution.

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

#32

Post by higeorge13 » Fri, Oct 08, 2021, 5:17 PM UTC

sails wrote: post #27

0x500x79 wrote: post #16

Some people have noted that this is a very Airbyte specific article, but I think that the lessons learned are still important. I have managed Airflow as a managed service for a company that has thousands of DAGs and one of our keys to success was splitting the compute and scheduling concepts into different components. We standardized on where our compute ran (Databricks, Spark, Lambdas, or K8s jobs) and had Airflow p…

How best to achieve this? I'm considering Astronomer or AWS hosted airflow, with a benefit to AWS having the compute components easily accessible within the AWS ecosystem. Starting out with a smaller scale and lower commitment to Airflow, so I'd like highest reliability with least hassle. https://aws.amazon.com/blogs/aws/introducing-amazon-managed-...

If you are on aws, then step functions could be your best option. They are responsible for the workflow definition, and the compute load is completely dependent on the underlying tasks and aws services you are going to use.

Edit: typos

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

#33

Post by verdverm » Fri, Oct 08, 2021, 5:36 PM UTC

ibains wrote: post #11

verdverm wrote: post #5

Earlier quoted context omitted.

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?

We work with many businesses that are larger (Fortune 500) and the T per pipeline is say 60 steps with 1200 columns at 10TB scale and uses multiple things not in SQL. They lookup object stores, lookup web services, use rocksdb, partitioning is important. At scale, cost becomes critical- some are even moving to their own Spark on Kubernetes. ML on done on data after ETL into Data Lake. None of them can use DBT for cor…

Have you explored Cuelang for T?

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

#34

Post by pid-1 » Fri, Oct 08, 2021, 5:40 PM UTC

0x500x79 wrote: post #16

Some people have noted that this is a very Airbyte specific article, but I think that the lessons learned are still important. I have managed Airflow as a managed service for a company that has thousands of DAGs and one of our keys to success was splitting the compute and scheduling concepts into different components. We standardized on where our compute ran (Databricks, Spark, Lambdas, or K8s jobs) and had Airflow p…

+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.

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

#35

Post by agustif » Fri, Oct 08, 2021, 5:40 PM UTC

I once made a simple ETL concept library for a coding challenge, I actually never published it, and it's not like performant or anything, but maybe I should open source it?

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

#36

Post by pid-1 » Fri, Oct 08, 2021, 5:48 PM UTC

sails wrote: post #27

0x500x79 wrote: post #16

Some people have noted that this is a very Airbyte specific article, but I think that the lessons learned are still important. I have managed Airflow as a managed service for a company that has thousands of DAGs and one of our keys to success was splitting the compute and scheduling concepts into different components. We standardized on where our compute ran (Databricks, Spark, Lambdas, or K8s jobs) and had Airflow p…

How best to achieve this? I'm considering Astronomer or AWS hosted airflow, with a benefit to AWS having the compute components easily accessible within the AWS ecosystem. Starting out with a smaller scale and lower commitment to Airflow, so I'd like highest reliability with least hassle. https://aws.amazon.com/blogs/aws/introducing-amazon-managed-...

Amazon's Airflow offering sucks really hard. As in many services, they made an awful job at designing a good UX and hiding the product's complexity from their users.

Astronomer is cool, but expensive and they won't accept monthly billing. Also, it's even more expensive if you need DAGS to access things within a VPC.

Step Functions is decent for simple use cases and very reliable. For complex stuff, you will hate their JSON based DSL with passion.

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

#37

Post by pid-1 » Fri, Oct 08, 2021, 5:50 PM UTC

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…

1 - Hide your ETL logic in REST APIs and use common microservice patterns for monitoring, logging, etc...

2 - Use PythonOperator to call your APIs.

It's working really well for us.

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

#38

Post by fredliu » Fri, Oct 08, 2021, 6:00 PM UTC

higeorge13 wrote: post #28

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?

A couple points from my end: - I miss the airflow ui to monitor the workflow execution, clear failed tasks, its pre built notifications, emails and so on. - Passing state between step functions is a bit tricky tbh. I found airflow’s way a little more straightforward. - Step functions seem to scale better, especially when you aim to use it for dynamic workflows. The recent update of step functions where you can litera…

Ya, the new Step Function service integration feature is awesome, really seals the deal for Step Function being the orchestration tool if you are in AWS.

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

#39

Post by fredliu » Fri, Oct 08, 2021, 6:07 PM UTC

rockostrich wrote: post #26

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?

Yea they look pretty similar. Not sure what the configuration language around step function is, but Airflow is nice because it's just python. There are a few gotchas you encounter, especially around templating, when you first get started but besides that it's pretty low overhead to start doing some pretty complex things since it's just writing python code.

Step Function uses JSON to describe the DAG, or if you use CDK you can define the DAG using any supported language (typescript, python, etc.).

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

#40

Post by TrealTwan » Fri, Oct 08, 2021, 6:28 PM UTC

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.