11 ms·

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

147 points by Arimbr 5y ago

TrealTwan 5y ago
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.
- CRConrad 5y ago
  > 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 more descriptive?". So I don't quite get the "starting to notice this at ELT becomes more popula[r]" bit -- this feels quite old to me. Before most of the meteoric rise of Python.
  - mr_toad 5y ago
    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.
- slotrans 5y ago
  Been doing it this way since 2005. I don't know why anyone thinks it's new.
- geoduck14 5y ago
  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
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.
ltbarcly3 5y ago
This is just advertising copy. It isn't giving unbiased advice, there is an obvious conflict of interest here. I was hoping to learn some things to help me avoid common airflow problems, but it only talks about database sync jobs for the most part and jumps into pitching Airbyte over and over, the last 2/3 of the article being a sales pitch, right out of a marketing class. Hi to the author and other Airbyte employees pushing this to the frontpage! I hope you didn't have to get up too early to coordinate your voting. Make sure to give this comment a downvote so we know you are out there!
willvarfar 5y ago
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 combination of tools could fill it, that would be the new LAMP stack for data.
- Arimbr 5y ago
  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.
- chrisjc 5y ago
  Are you perhaps talking about something like https://materialize.com/ 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/ 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 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-models-with-just-dbt-sql/1457 https://discourse.getdbt.com/t/how-to-create-near-real-time-...
  - dominotw 5y ago
    >2. Run dbt in micro-batches >Just don’t do it. Because dbt is primarily designed for batch-based data processing, you should not schedule your dbt jobs to run continuously. This can open the door to unforeseeable bugs. why not though. you can inplement incremental models and run them continously. sure its more work but what bugs does this cause?
    - 5y ago
psychometry 5y ago
If your ETL code is written in Python, just use Prefect and thank me later: https://docs.prefect.io/ https://docs.prefect.io/
- yamrzou 5y ago
  Could you elaborate?
0x500x79 5y ago
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.
- sails 5y ago
  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-workflows-for-apache-airflow-mwaa/ https://aws.amazon.com/blogs/aws/introducing-amazon-managed-...
  - numlocked 5y ago
    At Grove we make extensive use of KubernetesPodOperator[0]. This allows to both encapsulate the job in containers (k8s pods), and also specify the resources we need for each DAG. [0] https://airflow.apache.org/docs/apache-airflow-providers-cncf-kubernetes/stable/operators.html https://airflow.apache.org/docs/apache-airflow-providers-cnc...
  - 0x500x79 5y ago
    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.
mkw5053 5y ago
Having always been on AWS and using Glue Spark Jobs for my jobs, I've never felt any benefit of using Airflow for orchestration over Glue Workflows. I can understand some people not wanting to deal with vendor lock-in. I'm curious what others opinions are.
- higeorge13 5y ago
  For glue jobs you probably don’t need something more. However step functions is the norm for creating workflows in aws nowadays.
fredliu 5y ago
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?
- rockostrich 5y ago
  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.
  - fredliu 5y ago
    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.).
- higeorge13 5y ago
  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 literally use any aws service api (compared to a couple weeks ago where you were restricted to around 10 and then you only had lambdas) minimises its weaknesses imo.
  - fredliu 5y ago
    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.
- 5y ago
zepmck 5y ago
If you have GPUs, NVTabular outperforms most of the frameworks out there: https://github.com/NVIDIA/NVTabular https://github.com/NVIDIA/NVTabular
agustif 5y ago
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?
yamrzou 5y ago
I have worked with Airflow during the past three years, but recently we adopted Dagster and I have been using it for the past 3 months. I have found it quite joyful to use and the experience has been very positive. Its main advantages compared to Airlfow (IMO): - A great UI - It forces you to clearly define inputs, outputs and types. - Separation of concerns: Between configuration and data, between processing and IO, and between code and deployment options. - It allows you to define flexible dags which you can configure at runtime, which makes it easiy to run locally or in k8s, or to switch the storage backend depending on the environment. This blog post by the founder outlines the differences between the two in much more detail: https://dagster.io/blog/dagster-airflow https://dagster.io/blog/dagster-airflow
- StreamBright 5y ago
  I was looking for a replacement for Airflow right now. This is really helpful.
- sails 5y ago
  Did you consider Prefect?
swyx 5y ago
> The main issue with Airflow transfer operators is that if you want to support transfers from M sources to N destinations, the community would need to code N x M Airflow operators. I'm biased but this is a nonissue with workflow-as-code solutions like temporal.io (which Airbyte uses). N activities pulling data from sources, M activities sending data to destinations, write whatever translation layers you want in your workflows. links to examples https://temporal.io/usecases#Pipelines https://temporal.io/usecases#Pipelines and our community meetup where Airbyte spoke about their needs https://www.youtube.com/watch?v=K25Bt5asd8I https://www.youtube.com/watch?v=K25Bt5asd8I
SPascareli13 5y ago
Something I've been thinking is, like the article says, SQL is a very good way to transform data, and it recommends dbt for it, but how to you test this transformation? I know dbt has tests, but on a superficial look they seem pretty trivial stuff like "check if this field is null" and things like that, but what about tests which I setup scenarios and see if the end result of my transformation is what I expect? Is there any good tools for this?
- msinxi 5y ago
  Checkout great expectations (https://greatexpectations.io/ https://greatexpectations.io/). You basically run assertions on your data. It auto-generates documentation for you and you can also store results of your validations in a flat file or database. I think there are dbt modules that try to mimic great expectations' validation but I like the docs that come with great expectations
  - boxysean 5y ago
    [dbt Labs employee here] Check out dbt-expectations package[1]. It's a port of the Great Expectations checks to dbt as tests. The advantage of this is you don't need another tool for these pretty standard tests, and can be early incorporated into dbt workflows. [1] https://github.com/calogica/dbt-expectations https://github.com/calogica/dbt-expectations
- snidane 5y ago
  You can only run superficial tests like is not null, is unique, to prevent row duplication in sql join for example, but not much else. You simply don't have enough information about what to test at the stage of the initial ETL/ELT that data engineering is responsible for. You maintain pipes that can route water, oil or gasoline. You don't want to test for water purity, because next day you are asked to route sewage or oil through your system. You can at best test volume, pressure or velocity in the pipeline because these actually have an impact on your system. The actual test that business is asking you to put at the "raw data" stage has to come at the application level later on. Eg. when you extract a metric out of the data you can do all kinds of time series tests on it, which will test the pipeline thoroughly. Testing is what makes data engineering different from software engineering. You don't control the input data and don't want to. You have to make your organization work in such a way that downstream users communicate data issues back to you. You only test the data thoroughly by using it. If you are a data vendor and your downstream user is a paying customer that you don't want to embarass yourself in front of, you need to invent a use for the data, that you sell, within your org.
41b696ef1113 5y ago
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.
- FridgeSeal 5y ago
  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.
  - mwadhera 5y ago
    looking at Argo for exactly these reasons. open to chat? (didn’t see your email in profile)
iroddis 5y ago
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/daggy https://gitlab.com/iroddis/daggy
- slotrans 5y ago
  (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.
  - thundergolfer 5y ago
    I know this thing you want by the name “incremental” processing. GrailBio’s pipeline tech is incremental, as is Lyft’s Flyte I think. Build systems such as Bazel are build on the same incremental DAG processing foundation. I agree that it’s the most important feature, as it dramatically improves correctness and performance (when at scale). IAM policy trouble is a good example of a bug that crashes a pipeline but has no influence on precomputed outputs of succeeded nodes.
slotrans 5y ago
Blows my mind that they recommend keeping Airflow just for the scheduler, which is basically the WORST part of an overall bad tool. Use Jenkins. You'll be happy you did.
- geoduck14 5y ago
  >Use Jenkins. If I'm not mistaken, Jenkins directly tells you not to use it as a scheduler
raj_singh2021 5y ago
Airflow is really nice and awesome solutions for simple workflow and tasks. But when we use Complex DAGs with too many subDAGs then real pain starts. Real issue with airflow is - 1. scalability 2. Scheduler delays. 3. security ( Open source airflow ) - A ) Airflow uses single super role that has access to resources for all its orchestration jobs which is potential compliance risks. B ) Lack of granular roles and security groups which leads to rely on trust that no airflow users mistakenly make any changes through UI I feel there is some undocumented dependency between scheduler, celery and web server which always hit performance issue of ETL job. Also We see more reliability issues on the platform as more workloads are added.

147 points by Arimbr 5y ago

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.

CRConrad 5y ago
> 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 more descriptive?". So I don't quite get the "starting to notice this at ELT becomes more popula[r]" bit -- this feels quite old to me. Before most of the meteoric rise of Python.
- mr_toad 5y ago
  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.
slotrans 5y ago
Been doing it this way since 2005. I don't know why anyone thinks it's new.
geoduck14 5y ago
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

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.

ltbarcly3 5y ago

This is just advertising copy. It isn't giving unbiased advice, there is an obvious conflict of interest here. I was hoping to learn some things to help me avoid common airflow problems, but it only talks about database sync jobs for the most part and jumps into pitching Airbyte over and over, the last 2/3 of the article being a sales pitch, right out of a marketing class. Hi to the author and other Airbyte employees pushing this to the frontpage! I hope you didn't have to get up too early to coordinate your voting. Make sure to give this comment a downvote so we know you are out there!

willvarfar 5y ago

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 combination of tools could fill it, that would be the new LAMP stack for data.

Arimbr 5y ago
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.
chrisjc 5y ago
Are you perhaps talking about something like https://materialize.com/ 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/ 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 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-models-with-just-dbt-sql/1457 https://discourse.getdbt.com/t/how-to-create-near-real-time-...
- dominotw 5y ago
  >2. Run dbt in micro-batches >Just don’t do it. Because dbt is primarily designed for batch-based data processing, you should not schedule your dbt jobs to run continuously. This can open the door to unforeseeable bugs. why not though. you can inplement incremental models and run them continously. sure its more work but what bugs does this cause?
  - 5y ago

psychometry 5y ago

If your ETL code is written in Python, just use Prefect and thank me later: https://docs.prefect.io/ https://docs.prefect.io/

yamrzou 5y ago
Could you elaborate?

0x500x79 5y ago

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.

sails 5y ago
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-workflows-for-apache-airflow-mwaa/ https://aws.amazon.com/blogs/aws/introducing-amazon-managed-...
- numlocked 5y ago
  At Grove we make extensive use of KubernetesPodOperator[0]. This allows to both encapsulate the job in containers (k8s pods), and also specify the resources we need for each DAG. [0] https://airflow.apache.org/docs/apache-airflow-providers-cncf-kubernetes/stable/operators.html https://airflow.apache.org/docs/apache-airflow-providers-cnc...
- 0x500x79 5y ago
  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.

mkw5053 5y ago

Having always been on AWS and using Glue Spark Jobs for my jobs, I've never felt any benefit of using Airflow for orchestration over Glue Workflows. I can understand some people not wanting to deal with vendor lock-in. I'm curious what others opinions are.

higeorge13 5y ago
For glue jobs you probably don’t need something more. However step functions is the norm for creating workflows in aws nowadays.

fredliu 5y ago

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?

rockostrich 5y ago
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.
- fredliu 5y ago
  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.).
higeorge13 5y ago
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 literally use any aws service api (compared to a couple weeks ago where you were restricted to around 10 and then you only had lambdas) minimises its weaknesses imo.
- fredliu 5y ago
  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.
5y ago

zepmck 5y ago

If you have GPUs, NVTabular outperforms most of the frameworks out there: https://github.com/NVIDIA/NVTabular https://github.com/NVIDIA/NVTabular

agustif 5y ago

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?

yamrzou 5y ago

I have worked with Airflow during the past three years, but recently we adopted Dagster and I have been using it for the past 3 months. I have found it quite joyful to use and the experience has been very positive. Its main advantages compared to Airlfow (IMO): - A great UI - It forces you to clearly define inputs, outputs and types. - Separation of concerns: Between configuration and data, between processing and IO, and between code and deployment options. - It allows you to define flexible dags which you can configure at runtime, which makes it easiy to run locally or in k8s, or to switch the storage backend depending on the environment. This blog post by the founder outlines the differences between the two in much more detail: https://dagster.io/blog/dagster-airflow https://dagster.io/blog/dagster-airflow

StreamBright 5y ago
I was looking for a replacement for Airflow right now. This is really helpful.
sails 5y ago
Did you consider Prefect?

swyx 5y ago

> The main issue with Airflow transfer operators is that if you want to support transfers from M sources to N destinations, the community would need to code N x M Airflow operators. I'm biased but this is a nonissue with workflow-as-code solutions like temporal.io (which Airbyte uses). N activities pulling data from sources, M activities sending data to destinations, write whatever translation layers you want in your workflows. links to examples https://temporal.io/usecases#Pipelines https://temporal.io/usecases#Pipelines and our community meetup where Airbyte spoke about their needs https://www.youtube.com/watch?v=K25Bt5asd8I https://www.youtube.com/watch?v=K25Bt5asd8I

SPascareli13 5y ago

Something I've been thinking is, like the article says, SQL is a very good way to transform data, and it recommends dbt for it, but how to you test this transformation? I know dbt has tests, but on a superficial look they seem pretty trivial stuff like "check if this field is null" and things like that, but what about tests which I setup scenarios and see if the end result of my transformation is what I expect? Is there any good tools for this?

msinxi 5y ago
Checkout great expectations (https://greatexpectations.io/ https://greatexpectations.io/). You basically run assertions on your data. It auto-generates documentation for you and you can also store results of your validations in a flat file or database. I think there are dbt modules that try to mimic great expectations' validation but I like the docs that come with great expectations
- boxysean 5y ago
  [dbt Labs employee here] Check out dbt-expectations package[1]. It's a port of the Great Expectations checks to dbt as tests. The advantage of this is you don't need another tool for these pretty standard tests, and can be early incorporated into dbt workflows. [1] https://github.com/calogica/dbt-expectations https://github.com/calogica/dbt-expectations
snidane 5y ago
You can only run superficial tests like is not null, is unique, to prevent row duplication in sql join for example, but not much else. You simply don't have enough information about what to test at the stage of the initial ETL/ELT that data engineering is responsible for. You maintain pipes that can route water, oil or gasoline. You don't want to test for water purity, because next day you are asked to route sewage or oil through your system. You can at best test volume, pressure or velocity in the pipeline because these actually have an impact on your system. The actual test that business is asking you to put at the "raw data" stage has to come at the application level later on. Eg. when you extract a metric out of the data you can do all kinds of time series tests on it, which will test the pipeline thoroughly. Testing is what makes data engineering different from software engineering. You don't control the input data and don't want to. You have to make your organization work in such a way that downstream users communicate data issues back to you. You only test the data thoroughly by using it. If you are a data vendor and your downstream user is a paying customer that you don't want to embarass yourself in front of, you need to invent a use for the data, that you sell, within your org.

41b696ef1113 5y ago

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.

FridgeSeal 5y ago
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.
- mwadhera 5y ago
  looking at Argo for exactly these reasons. open to chat? (didn’t see your email in profile)

iroddis 5y ago

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/daggy https://gitlab.com/iroddis/daggy

slotrans 5y ago
(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.
- thundergolfer 5y ago
  I know this thing you want by the name “incremental” processing. GrailBio’s pipeline tech is incremental, as is Lyft’s Flyte I think. Build systems such as Bazel are build on the same incremental DAG processing foundation. I agree that it’s the most important feature, as it dramatically improves correctness and performance (when at scale). IAM policy trouble is a good example of a bug that crashes a pipeline but has no influence on precomputed outputs of succeeded nodes.

slotrans 5y ago

Blows my mind that they recommend keeping Airflow just for the scheduler, which is basically the WORST part of an overall bad tool. Use Jenkins. You'll be happy you did.

geoduck14 5y ago
>Use Jenkins. If I'm not mistaken, Jenkins directly tells you not to use it as a scheduler

raj_singh2021 5y ago

Airflow is really nice and awesome solutions for simple workflow and tasks. But when we use Complex DAGs with too many subDAGs then real pain starts. Real issue with airflow is - 1. scalability 2. Scheduler delays. 3. security ( Open source airflow ) - A ) Airflow uses single super role that has access to resources for all its orchestration jobs which is potential compliance risks. B ) Lack of granular roles and security groups which leads to rely on trust that no airflow users mistakenly make any changes through UI I feel there is some undocumented dependency between scheduler, celery and web server which always hit performance issue of ETL job. Also We see more reliability issues on the platform as more workloads are added.