6 ms·

[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

by Arimbr 5y ago

[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?

verdverm 5y ago
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?
- Arimbr 5y ago
  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.
- ibains 5y ago
  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.
  - verdverm 5y ago
    Have you explored Cuelang for T?
    - verdverm 5y ago
      I got inspired and started this over the weekend to demonstrate what is possible. https://github.com/hofstadter-io/cuetils https://github.com/hofstadter-io/cuetils
- rpedela 5y ago
  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.
rockostrich 5y ago
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.
- Grimm1 5y ago
  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.
  - rockostrich 5y ago
    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.
    - KptMarchewa 5y ago
smarx007 5y ago
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 https://martinfowler.com/books/eip.html
pid-1 5y ago
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.
- kdazzle 5y ago
  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.
- ibishvintilli 5y ago
  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.
contravariant 5y ago
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.
DevKoala 5y ago
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.
- Arimbr 5y ago
  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 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 https://docs.airbyte.io/quickstart/deploy-airbyte
  - gunnarmorling 5y ago
    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

by Arimbr 5y ago

verdverm 5y ago

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?

Arimbr 5y ago
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.
ibains 5y ago
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.
- verdverm 5y ago
  Have you explored Cuelang for T?
  - verdverm 5y ago
    I got inspired and started this over the weekend to demonstrate what is possible. https://github.com/hofstadter-io/cuetils https://github.com/hofstadter-io/cuetils
rpedela 5y ago
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.

rockostrich 5y ago

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.

Grimm1 5y ago
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.
- rockostrich 5y ago
  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.
  - KptMarchewa 5y ago

smarx007 5y ago

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 https://martinfowler.com/books/eip.html

pid-1 5y ago

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.

kdazzle 5y ago
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.
ibishvintilli 5y ago
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.

contravariant 5y ago

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.

DevKoala 5y ago

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.

Arimbr 5y ago
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 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 https://docs.airbyte.io/quickstart/deploy-airbyte
- gunnarmorling 5y ago
  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