Transformations: REST Api Improvement for DBT Transformations start and Stop
I am working on DBT snapshots versions automated swapping in Snowflake.
In order to swap the snapshot table(s) we need to stop Transformation Jobs till the swapping is over and re-activate them after. All other steps I expect to be executed using DBT and SF Stored procedures. DBT commands to be executed in ADO pipeline.
In Rest Api documentation I don’t see any information related to the Transformation Jobs from REST API perspective.
Could you help us with the Fivetran Rest API call, and the instructions on how to run it in Azure DevOps pipeline?
This way we could have a fully automated pipeline for snapshot swapping.
This feature could be useful in other scenarios when we need to synchronize DBT Transformation activities with some other tasks.
-
Official comment
Hey Vadim, thank you for reaching out and for taking the time to write out your use case. API instantiated transformations is something that we're looking at for exactly your reason: fully automated pipelines. I'm adding your comments here to our backlog, but would love for you to go into a bit more detail about how you'd like this to work via our API.
-
Hi,
I am also looking for this feature to run these DBT transformations via API after our Airflow pipeline finishes.
We do run these DBTs are particular time intervals but we also need to run after certain data is available via different pipelines. We need to reflect this data on UI ASAP so running them via API will be very very helpful.
Looking forward to get this feature soon.
-
Hello Jimmy
WE have an Azure DevOps (ADO) pipeline, which has many tasks including Create/update the branch in Git repo for DBT code. The branch Fivetran is getting DBT Transformations jobs. In order to swap the snapshot and migrate the historical records from the old snapshot version to the new one I need:
- Update DBT script in the branch,
- stop dbt job that is running snapshot
- rename the current version of snapshot to preserve the historical records and force DBT to recompile the snapshot. In many cases I saw dbt continue running compiled old version of snapshot disregarding the changes, and only the need to create a new table forces the recompilation
- start the DBT job, so let Fivetran DBT creates a new version of the snapshot table, to make sure on the cloud the compiled snapshot sql code got recompiled
- merge historical records from old table top the new one
ADO pipeline has the task for each step other than Fivetran DBT Transformation start and stop. That prevents me from executing all operation in a single run. As a temp solution I stop DBT transformations manually first. Deploy the new version of snapshot using ADO task that runs dbt snapshot -s <new_snap> on build server. Executing tasks for all other operations: hist data merge, target model --full-refresh, old table delete, metadata cleanup, etc.
Then I turn on Fivetran DBT Transformations, hoping that Git sync would occur before the job runs(my other feature request). This allows me to have uninterrupted ADO pipeline exception.
However, it's important to create a new snapshot with Fivetran DBT, so the compiled code got refreshed for sure in Fivetran cloud. That's what I need API calls that ADO can execute in the sequence with the rest of the tasks to start and stop DBT Transformations
Please sign in to leave a comment.
Comments
3 comments