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

#81

Post by sails » Sun, Oct 10, 2021, 5:06 PM UTC

pid-1 wrote: post #36

sails wrote: post #27

Earlier quoted context omitted.

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 thei…

Thanks, glad to hear that before I bother trying it.

Have heard astronomer is too expensive.

Step Functions doesn't look great either.

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

#82

Post by sails » Sun, Oct 10, 2021, 5:08 PM UTC

0x500x79 wrote: post #31

sails wrote: post #27

Earlier quoted context omitted.

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

Thanks. Requirement at this stage is to coordinate the running of a few singer.io or airbyte taps.

I may consider Prefect over Airflow, generally my experience so far resonates with this blog post: https://towardsdatascience.com/tired-of-airflow-try-this-c51...

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

#83

Post by chrisjc » Mon, Oct 11, 2021, 11:27 AM UTC

dominotw wrote: post #72

chrisjc wrote: post #18

Earlier quoted context omitted.

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. h…

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

Totally agree. While not using DBT specifically, I've done this on tables with billions of rows and it works perfectly. And even this can be combined with a Lambda view giving you the best of both worlds. Combining overcomes any latency from the incremental process since it can take time.

But I did end up questioning why I needed to continuously microbatch when the lambda views are able to bridge the gap. It turned out that the lambda views were good enough that we could reduce the microbatching back to ever 24hrs, and that was just being overly cautious. 48hrs or more might have been good enough, maybe more.

It turned out that the costly part of the microbatching was really merging (inserts and update, not append only) the delta data back into the prepared table. Selecting, combining and consolidating new and historic data is extremely fast.

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

#84

Post by verdverm » Mon, Oct 11, 2021, 5:08 PM UTC

verdverm wrote: post #33

ibains wrote: post #11

Earlier quoted context omitted.

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?

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

#85

Post by sails » Mon, Oct 11, 2021, 7:29 PM UTC

yamrzou wrote: post #42

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,…

Did you consider Prefect?

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

#86

Post by sails » Mon, Oct 11, 2021, 7:32 PM UTC

snidane wrote: post #75

SPascareli13 wrote: post #51

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 th…

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 o…

I like this analogy, I often use the term plumbing in relation to Data Engineering, and this extends it perfectly!

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

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

#87

Post by Grimm1 » Tue, Oct 12, 2021, 4:31 PM UTC

rockostrich wrote: post #23

KptMarchewa wrote: post #21

Earlier quoted context omitted.

In the end, analysts are cheaper than engineers.

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 t…

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.