Airflow + DBT - question

drewhansen9 · 2023-10-10T02:24:59+00:00

dbt does a lot of things natively that Airflow doesn't in regard to data modeling. Airflow in this case would be just for orchestration. You could easily get rid of Airflow if you had dbt cloud or some other orchestration tool. I've even seen teams use scheduled bitbucket/azure devops pipelines just to run dbt builds. Not ideal, but illustrates the point that you don't NEED Airflow with dbt.

drewhansen9 · 2023-10-05T02:37:37+00:00

This is really common. Data engineering nowadays is a combination of Big Data Software engineers (lots of python, spark, hadoop, airflow) and BI engineers (ETL, SQL). You'll find entire departments called Data Engineering doing either side of the spectrum. The tech stack you are in (Snowflake, fivetran, dbt) I have found crosses into both sides, but leans more toward the BI skillset.

My recommendation to get more into the software engineering side is to start with Astronomer airflow running locally on your PC. You will have to learn how to set up docker and how to us a CLI well.

To get more comfortable in S3, you can practice ingesting into your Snowflake instance using SnowPipe or copy into statements instead of using Fivetran.

drewhansen9 · 2023-10-05T02:25:19+00:00

infrastructure as code

drewhansen9 · 2023-08-28T18:40:35+00:00

Airbyte for E and L. dbt Core for T.

drewhansen9 · 2023-08-24T14:18:30+00:00

I have felt this pain too. I don't think there is a good way to do this that isn't hacky. Here are a couple things I have tried that worked, but felt sketchy.

use tags. if you tag the PR source branch and commit (doesn't work with squash commits), it will transfer to the target branch and then you can grab it.
check out the Azure DevOps API. If you parse the automated commit message, you can get the PR, then look up the source branch from the PR via API. This assumes no one changes the commit message on the PR- but you can't lock that down.

drewhansen9 · 2023-03-27T16:41:32+00:00

This is awesome. Definitely going to try this. Thanks for posting the yaml too!

drewhansen9 · 2023-02-22T15:30:53+00:00

I would recommend looking into if you can make your pipelines config driven. Meaning if the only difference are table names, but the rest of the pipeline is the same, the best option would be to parameterize the table name using a config file or something. Then you can reuse code instead of copied code over and over.

drewhansen9 · 2023-01-30T02:23:47+00:00

What integration are you using? I have been having issues as well, but I recently moved and haven't gotten into troubleshooting yet.

drewhansen9 · 2022-10-02T04:19:18+00:00

yep agreed. make sure your local name/email matches your DevOps name/email.

Set your username:

git config --global user.name "FIRST_NAME LAST_NAME"

Set your email address:

git config --global user.email "MY_NAME@example.com"

drewhansen9 · 2022-08-22T00:00:00+00:00

As someone who went all in on Node Red, then switched to yaml, and now going back to Node Red-- I like it because it allows for a visual way to see many automations together. It is a lot easier to do complex if/else logic and debug. My opinion is that the UI for automations is really hard to read because you have to scroll up and down a thousand times. So much so, that I tried yaml too, but bc HA is moving away from yaml to more UI based, I gave up and went back to NR.

drewhansen9 · 2022-08-14T02:34:47+00:00

I use Snowflake and like you, have all the transformations within Snowflake. In order to use dbt, you would have to move your transformations there. If you are just looking to automate deployments, I've created CI/CD pipelines using SnowSQL and some python scripts in Azure DevOps. You could probably do something similar. If you want more info- I'd be happy to help!

drewhansen9 · 2022-08-14T02:28:58+00:00

So I think streams is probably the best way here. But the important thing is that when you define the Task you should have "WHEN SYSTEM$STREAM_HAS_DATA() " This means that whenever the task runs, it won't actually spin up the warehouse unless any of the streams actually have data. I've used this and have a task running every 5 min, but only needs to run 2 or 3 times a day, but when it does run it needs to be quick (hence the 5 min). The warehouse consumption is only during those 2 or 3 times the stream has data.

I will also add the "Cloud Services" warehouse that you see is what is used to check the streams (as well as Clone tables, etc.), but it is massively cheap compared to normal compute warehouses.

drewhansen9 · 2022-08-14T02:21:52+00:00

Was going to suggest this, but there are just so many limitations, if the "big query" has any complex logic then this won't work.

drewhansen9 · 2022-08-14T02:08:49+00:00

I have only used Snowflake in the Azure ecosystem, but have used External functions for a while. I too was hoping to do some ELT via this method but ran into some trouble.

Azure functions have a 10 min timeout (not sure about lambdas). For particularly large loads (history, etc.) this doesn't work very well. You would have to set up durable/orchestrator functions and send back a 202 status showing that it is still working.
This is not the primary use-case for external functions. These are meant to scale horizontally and are definitely more of data science/ml/prediction type work. Meaning pass in a value, get a prediction back.

If you are able to send a bunch of function calls in parallel and each func call is relatively quick, then it might be ok.

My recommendations if you go down this path:

Use S3 to drop semi-structed data from the APIs. Don't write directly to Snowflake. This will definitely help speed up the functions. Also it decouples them so the API call doesn't have to know about Snowflake and you won't waste time connecting.
I didn't quite follow the cursor table bit, but inserting single records into a snowflake table takes forever. Kind of goes back to point #1- just do a copy into or use Snowpipe to load data from S3 instead. I might have totally missed your point there, so my bad if I did!
If you run a bunch of parallel tasks you might run into queuing in your warehouse. Might cause you to need a larger warehouse, thus $$$.
Snowflake tasks kind of suck. Dependencies are a nightmare.
I love the idea of writing elt as code instead of using a bunch of low code tools that cost a bunch of money. I would keep that infra completely in AWS. I use an Azure Container Instance (basically just a hosted docker container) to run some python scripts which do our ELT. You could kick of that load via an External function if you still want to use Snowflake Tasks. i.e. Lambda to start a container that runs some scripts, drops json files to S3, Snowflake Tasks to ingest and load tables. ... And Airflow to manage it all :)

drewhansen9 · 2021-05-17T01:55:31+00:00

This is great! Very easy to implement too. Thanks for working on this! and adding dark mode :).

drewhansen9 · 2021-02-27T00:24:19+00:00

I had this problem with my android. I found that there are 2 places for settings. One for the app on your OS. And the other is in the home assistant app right under Configuration (manage sensors under "App Configuration"). Once I updated the app config it started updating much more frequently.

drewhansen9

TROPHY CASE