Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

airbyte.io

Post reply on HN
Print view View original

11–20 of 87 posts

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

#11

Post by ibains »

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?

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 core ETL, but DBT might be good later for views, some dimensional modeling. They have done a good job here.

Think of it as the modern small-scale data stack.

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

#12

Post by rockostrich »

Arimbr wrote: post #10
rockostrich wrote: post #6

Earlier quoted context omitted.

Is that really that bad of a problem? In the worst case, you use the underlying hooks to create a custom operator and in the worst worst case you do something like have a Spark/Dataflow/k8s pod/whatever other single threaded or distributed task runner act as that custom operator. I'm running into the "incremental load" problem now trying to batch intervals of Avro files into BigQuery, but Airflow doesn't seem to comp…

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 mesh is certainly something in-between a lake and a warehouse... Something super simple that I've gotten good feedback on so far from DSs is just documenting the transformed data in place. It was really difficult to do this in our old ETL stack (data pulled from HBase, transformed to parquet + Hive in HDFS) but we've moved a lot of it over to Avro files loaded into BigQuery where we can just put decorators on our Scala transformation code that's writing the Avro files and that updates the schema with descriptions in BigQuery. Gives a nice bit of ownership to the engineering team and lets the DS using the data be a lot more autonomous. That boundary has to exist somewhere (or I guess in many places for a "mesh") so having it distinctly at data getting loaded in feels right to me.

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

#13

Post by rockostrich »

Grimm1 wrote: post #9
rockostrich wrote: post #6

Earlier quoted context omitted.

Is that really that bad of a problem? In the worst case, you use the underlying hooks to create a custom operator and in the worst worst case you do something like have a Spark/Dataflow/k8s pod/whatever other single threaded or distributed task runner act as that custom operator. I'm running into the "incremental load" problem now trying to batch intervals of Avro files into BigQuery, but Airflow doesn't seem to comp…

In a previous job we/I trained the analysts to be more technical and write the T part of ELT in DBT. They effectively became what is known as "Analytics Engineers" so they owned the T and then wrote their analysis on top of the models they had created.

That works for ELT, especially if you have documentation around the raw data being loaded in but sounds like it adds a bit of overhead to the analysts' jobs which may or may not be more than just having the engineering team own it and document it well (something they already have to do for the analysts to write transformation code). I'm curious how you handle the upstream data schema changing. Loading in raw data means handling compatibility in another place outside the application.

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

#14

Post by Arimbr »

willvarfar wrote: post #4

Very solid article. Even with where it’s published, it’s jolly sensible. I would like to jump in and say use Beam instead of DBT, but tbh that’s bad advice. What the world needs is something open source with the incremental model of beam, a fast incremental backend (thinking htap storage that mixes columns and rows automagically) and the ease and maintainability of DBT. There is just this massive hole. If some combin…

Oh, you should check Materialize. I feel Materialize is like dbt but with an ingestion layer and real-time materialized views.

To deliver that you need to centralize data on their the Materialize database, which is may main caveat. With dbt you can use any data warehouse.

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

#16

Post by 0x500x79 »

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 purely as a scheduler/orchestration tool.

Scaling your Airflow worker nodes to handle big-data scale transformations/extractions is a pain. Especially attempting to support customers who want to run larger and larger jobs. Splitting these concepts allowed for us to prevent noisy neighbor issues, Airflow as a component had high reliability for all of our customers, and we prevented the need for M * N operators.

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

#17

Post by Arimbr »

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…

Nice work there! I also think that the next challenge for data teams is all this data documentation and discovery work.

I still think that Airflow is great for power data engineers. Airbyte and dbt are positioned to empower data analysts (or lazy data engineers like me) to own the ELTs.

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

#18

Post by chrisjc »

willvarfar wrote: post #4

Very solid article. Even with where it’s published, it’s jolly sensible. I would like to jump in and say use Beam instead of DBT, but tbh that’s bad advice. What the world needs is something open source with the incremental model of beam, a fast incremental backend (thinking htap storage that mixes columns and rows automagically) and the ease and maintainability of DBT. There is just this massive hole. If some combin…

Are you perhaps talking about something like https://materialize.com/ ? (btw, dbt now has some materialize compatibility)

Maybe Pravega and Beam working together? https://pravega.io/docs/v0.6.0/key-features/

Another option is something like Snowflake with tasks and streams. https://docs.snowflake.com/en/user-guide/tasks-intro.html

Or Snowflake with change streams, dbt and scheduler in combination with lambda views. https://discourse.getdbt.com/t/how-to-create-near-real-time-...

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

#20

Post by fredliu »

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?
Post reply on HN