News for hackers. Stuff that matters.
Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by Arimbr on Friday October 08, 2021 @10:40AM (#28799821)
[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.

Another concern about using Airflow for the T part is that you need to code the dependencies between models both in your SQL files and your Airflow DAG. Other open-source projects like dbt create a DAG from the model dependencies in the SQL files.

So I advocate for integrating Airflow scheduler with Airbyte and dbt.

Curious to know how other use Airflow for ETL/ELT pipelines?

Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by rockostrich on Friday October 08, 2021 @10:59AM (#28800069)
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 complain much about 10 minute intervals (less than that and I'd be a bit worried). One workaround we're considering to having 144 dag runs/dag/day for 10 minute intervals is to just define it as a daily dag and periodically clear it throughout the day.

I have heard of newer companies who don't need to deal with legacy ETL going the ELT route which is interesting to me. I'm curious who owns the transformation in that case. Do you just hire more data scientists or maybe a bunch of junior devs/analysts/DSs to own data cleaning and transformation? The way our transformation works is that the engineering team that owns the application data owns the transformation so they can document exactly what the data going into the BI tool is. It adds overhead on the engineering side, but not nearly as much as throwing the data in a lake and hoping someone downstream knows what to do with it.

Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by Grimm1 on Friday October 08, 2021 @11:10AM (#28800176)
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.
Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by rockostrich on Friday October 08, 2021 @11:32AM (#28800388)
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 by KptMarchewa on Friday October 08, 2021 @12:04PM (#28800794)
In the end, analysts are cheaper than engineers.
Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by rockostrich on Friday October 08, 2021 @12:25PM (#28801078)
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 them good data.
Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by Grimm1 on Tuesday October 12, 2021 @12:31PM (#28841691)
I think we've worked in very different jobs, in my case the analysts had a good idea of the underlying application data and often worked closely with both data engineering and regular engineering to understand it so they can make better analyses. They were quite competent in their own right, otherwise I wouldn't have given them control over the T which only made a net benefit to my life as reduced work.
Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by dominotw on Friday October 08, 2021 @11:31PM (#28806796)
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 by Arimbr on Friday October 08, 2021 @11:11AM (#28800189)
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 (data analysts) who know best the data to create and maintain the models. That's easier said than done, and most often turns out into a worst situation than when is only data engineers who can model data... I've only heard of Zalando who has successfully distributed data ownership within the company.

Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by rockostrich on Friday October 08, 2021 @11:29AM (#28800344)
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 by dominotw on Friday October 08, 2021 @11:34PM (#28806813)
>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.

Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by Arimbr on Friday October 08, 2021 @11:44AM (#28800513)
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 by rockostrich on Friday October 08, 2021 @12:27PM (#28801097)
Agreed. I see a lot of folks coming up with one off solutions for pulling data out of 3rd party sources like Kustomer or Lever. Giving a centralized UI for setting that up would be a great service.

Seems like I have a fun weekend project.

Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by dominotw on Friday October 08, 2021 @11:35PM (#28806818)
what about meltano/singer
Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by contravariant on Friday October 08, 2021 @03:05PM (#28802859)
I mostly don't bother writing separate Operators. The only part I write are the so called Hooks (which are basically just airflow's way of defining a standard way of grabbing credentials and instantiating a session object).

After that you just write a short python function that grabs the data from one hook and pushes it to another. Which is basically the (M + N) solution you mention (I think the factor 2 is unnecessary if you've already split sources and sinks).

This approach works with anything you can connect to python. Though for particularly large datasets you want to be careful that you don't accidentally store all data in memory at once. And sure you can sometimes specialize an operation for a particular use case (e.g. if in your example can instruct BigQuery to connect to the Postgres application natively), but usually it works just fine to use a python script in-between.

Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by smarx007 on Friday October 08, 2021 @11:01AM (#28800083)
I am curious if Airflow is the most appropriate tool for such tasks. I would imagine Apache Camel to be a good starting point for building a library of connectors and then plugging them together using EIP [1] patterns?

[1]: https://martinfowler.com/books/eip.html

Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by DevKoala on Friday October 08, 2021 @03:58PM (#28803489)
My team is currently evaluating Debezium for a PG to S3 CDC solution, and is very advanced with the PoC.

What would be the argument for trying AirByte? It is the first time we hear about it.

Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by Arimbr on Friday October 08, 2021 @04:40PM (#28803930)
Airbyte CDC is based on Debezium, but Airbyte abstracts it away and make it easier to CDC from Postgres, MySQL, MSSQL to any supported destination (included S3). Here is the doc for CDC: https://docs.airbyte.io/understanding-airbyte/cdc

I guess one benefit is that you can use Airbyte for all your data syncs, CDC and non-CDC. You can give it a try with your own data, and see if it's easier for your team. You can run Airbyte locally with Docker Compose: https://docs.airbyte.io/quickstart/deploy-airbyte

Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by gunnarmorling on Friday October 08, 2021 @05:11PM (#28804259)
Can you elaborate on how Airbyte makes things easier for a user? Would love to pick up any potential improvements in Debezium itself, so that all its users get to benefit from them, rather than only users of a specific integrator like Airbyte.

Disclaimer: I work on Debezium

Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by pid-1 on Friday October 08, 2021 @01:50PM (#28802021)
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 by ibishvintilli on Saturday October 09, 2021 @08:36PM (#28814703)
This is a very bad idea. You cannot hide ETL in a Rest API, at most you can hide the T part and it's a lot more work. you need to build an extra service that reads the data from a database and does some padgination. It's way more complex than a one-liner to get data from a jdbc connection and way much slower.
Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by kdazzle on Friday October 08, 2021 @08:16PM (#28805729)
I used to do that, but having to deal with timeouts in long running jobs was frustrating because i never knew if something was successful. Could bump up the timeouts, but theyre there for a reason.
Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by verdverm on Friday October 08, 2021 @10:57AM (#28800045)
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?

Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by ibains on Friday October 08, 2021 @11:18AM (#28800238)
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 by verdverm on Friday October 08, 2021 @01:36PM (#28801826)
Have you explored Cuelang for T?
Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by verdverm on Monday October 11, 2021 @01:08PM (#28830516)
I got inspired and started this over the weekend to demonstrate what is possible.

https://github.com/hofstadter-io/cuetils

Re:ETL Pipelines with Airflow: The Good, the Bad and the Ugly by rpedela on Friday October 08, 2021 @11:31PM (#28806797)
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 by Arimbr on Friday October 08, 2021 @11:03AM (#28800109)
My understanding of dbt is that it builds a DAG based on the interdepencies between models. The interdepencies are parsed from 'ref' functions on the SQL files. The thing with dbt is that you transform the data within a single data warehouse.

So, you would normally first load all data to the data warehouse. Then dependencies between SQL models are easier to map.