I started using Fivetran for transformations back in 2019, and it was a nice feature – a basic SQL editor, and several orchestration options.
Earlier this year, however, I got the notice:
The alternative to Fivetran’s Basic SQL Transformation feature was dbt Core. This gave me a chuckle because I had recently started testing out dbt Cloud. But the obvious next question was whether I should continue using dbt Cloud, or if I should switch to dbt Core.
In the end, I decided to migrate some of my transformations into dbt Cloud – the ones that can run as cron jobs. The other transactions are triggered by upserted records, and dbt Cloud doesn’t support that option yet. So today I sat down to migrate the rest of my transformations into dbt Core.
Well, can I just tell you, I think I stumbled onto something very convenient! Instead of running some transformations through dbt Cloud and the others through Core, I can run them all through Cloud. Fivetran won’t tell you this is possible. So here’s how you can do it.
First, you’re going to need to set up your dbt Cloud environment. dbt offers a variety of resources to help you get started. This includes creating a repo for your dbt project – I use GitHub.
Once you have dbt Cloud set up (I know this is jumping ahead pretty far), then you can follow these steps to connect Fivetran.
In Fivetran’s new Quickstart Transformations wizard for dbt, you’re going to be prompted to enter some information. You’ll have to create a public key in your repo and drop that into the Public Key field in Fivetran. You’re also going to need your repo URL – if you’re using GitHub, you can find that in your repo by clicking the Code button and then choosing SSH. You can enter a default schema name; in setting up dbt Cloud, you’re probably going to be prompted to create a default dbt schema called “analytics”.
Finally, you’ll be prompted to choose a dbt Core Version. I thought I was going to be stuck here.
Fortunately, dbt Cloud tells you what version of dbt Core you’re using! In dbt Cloud, under Deploy -> Environments, you can open up your Deployment environment and get the dbt Version as shown below. Even though you’re on dbt Cloud, you are still running a dbt Core version.
Plug that into the Fivetran wizard and give it a test – if you’ve entered all of the required information correctly, Fivetran will connect to your repository *and* you’ll be able to continue using dbt Cloud’s sweet IDE to manage your transformations *and* all of your other jobs.
This is a much better outcome for me; rather than managing some code in dbt Cloud and some other code in dbt Core, I can develop all of it in one place.
Today Doug Learned 
Leave a Reply