I tried Automating Data Pipelines to Drive Marketing Analytics with Snowflake & Fivetran from Snowflake's Quickstart
2025.09.04
I am Kawabata.
As the title indicates, I'll be trying out "Automating Data Pipelines to Drive Marketing Analytics with Snowflake & Fivetran" from Snowflake's Quickstart.
【Document】
Target audience
- Those who want to know how to connect Fivetran and Snowflake
- Those who want to experience the workflow of Fivetran, dbt, and Snowflake
Agenda
- Overview
- Snowflake configuration
- Fivetran setup
- Upload sample data with Google Spreadsheets
- Fork sample dbt project on Github
- Configure Fivetran_dbt transformation
- Snowflake setup
- Summary
Overview
In this hands-on lab, you will learn step-by-step guidance on how to use Fivetran, Snowflake, and dbt to execute marketing analytics for Google Ads data.
Quoted from the documentation.
Testing environment
- Snowflake trial account Enterprise edition
- Fivetran
- Github account
- Sample Google Ads data
Snowflake configuration
I'll proceed according to the hands-on guide.
- Log in to your Snowflake trial account.
- For this hands-on, we'll work with the
ACCOUNTADMINrole.
- From the left side of the Snowflake UI, select
Partner Connectfrom the data sharing under theHorizon Catalogsection.
- The following search screen will appear, so enter
Fivetran.
- Click Connect.
*Note: As I was already connected, mine says "Launch" instead.
- Click activate in the popup that appears.
【Stumbling point】
Be careful not to click Activate Later instead of choosing activation here, as later activation becomes troublesome.
I clicked it thinking I would set it up later.
-
A new browser tab will open and take you to the Fivetran site. Enter your login information there.
### Setting up the Destination
Here I will describe the Destination setup for those who selectedActivate Laterin step 6. -
From the
Fivetranscreen, openDestinationand selectAdd destination.
-
The screen will transition to
Select a destination, so select Snowflake.
-
The following popup will appear, so enter any name of your choice.
-
Execute the following script to create the database and other resources to be used.
begin;
-- Create variables for user / password / role / warehouse / database (need to be in uppercase for objects)
set role_name = 'FIVETRAN_ROLE';
set user_name = 'FIVETRAN_USER';
-- -- Snowflake is planning to deprecate password-based authentication, so we recommend removing the next line.
set user_password = 'password123';
set warehouse_name = 'FIVETRAN_WAREHOUSE';
set database_name = 'FIVETRAN_DATABASE';
-- For user/role steps, change role to securityadmin
use role securityadmin;
-- Create a role for fivetran
create role if not exists identifier($role_name);
grant role identifier($role_name) to role SYSADMIN;
-- Create a user for fivetran
create user if not exists identifier($user_name)
password = $user_password
default_role = $role_name
default_warehouse = $warehouse_name;
grant role identifier($role_name) to user identifier($user_name);
-- Set binary input format to BASE64
ALTER USER identifier($user_name) SET BINARY_INPUT_FORMAT = 'BASE64';
-- Change role to sysadmin for warehouse/database steps
use role sysadmin;
-- Create a warehouse for fivetran
create warehouse if not exists identifier($warehouse_name)
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 60
auto_resume = true
initially_suspended = true;
-- Create a database for fivetran
create database if not exists identifier($database_name);
-- Grant fivetran role access to the warehouse
grant USAGE
on warehouse identifier($warehouse_name)
to role identifier($role_name);
-- Grant fivetran access to the database
grant CREATE SCHEMA, MONITOR, USAGE
on database identifier($database_name)
to role identifier($role_name);
-- Change role to ACCOUNTADMIN for STORAGE INTEGRATION support (only needed for Snowflake on GCP)
use role ACCOUNTADMIN;
grant CREATE INTEGRATION on account to role identifier($role_name);
use role sysadmin;
commit;
```5. Check the hostname.
Check the URL in the browser where you are accessing Snowsight (Snowflake's Web UI). The URL is typically in the following format:
`https://<organization-name>-<account-name>.snowflakecomputing.com`
This `<organization-name>-<account-name>.snowflakecomputing.com` part is the hostname of your Snowflake account.
6. Configure with the confirmed information
For `Auth`, you can choose between key pair or password, but Snowflake plans to deprecate password-based authentication.
Once you have entered all the items, click on `Save & Test`.
When authentication is completed as shown below, you are done.
【Snowflake Connection Setup Guide】
https://fivetran.com/docs/destinations/snowflake/setup-guide
## Fivetran Configuration
The Fivetran account configuration details are described here, but since it only involves setting a password, we'll skip this section.## Upload Sample Data with Google Spreadsheet
- Create new schema and table using Google Sheets connector
- Create one Google Sheets connector per table
- Use one of the Google spreadsheet links in this document
1. Select `Connections` and click `Add connection`.
2. Navigate to `Browse our connectors` and select `Google Sheets`.
3. Navigate to `Select a destination` and select the `Destination` you created.
4. Enter the following values in the two fields:
- Destination schema: `google_ads_demo`
- Destination table: `final_url_performance`
5. Select `Authorize Service Account`, enter the spreadsheet URL in the `Sheet URL` field and click `Find sheet`.
If there are no issues, select `final_url_performance` from the `Named Range`.
Spreadsheet URL: https://docs.google.com/spreadsheets/d/1BzGIzwDvyIDEeMsGj8wC1EXxEAEPodf8U-BUXKuZp-w/edit?usp=sharing
【Stumbling point】
The official documentation says to select from the link below, but when clicking the link it had expired.
https://github.com/fivetran/snowflake_fivetran_vhol/blob/main/LAB_ASSETS/GSHEETS_LINKS.md?_fsi=uqbrIqYR&_fsi=uqbrIqYR&_fsi=uqbrIqYR&_fsi=uqbrIqYR
When clicking the link, the following screen is displayed.
I checked the changes on GitHub and was able to use a past URL that still worked.
6. The connection was established successfully as shown below.
7. Click `Continue` and you will see the following screen, then click `Start Initial Sync`.
After clicking, you will be redirected to the following screen and synchronization will begin.
8. Create `Named Range` for `criteria_performance` and `click_performance` using the same steps as 1-7.
When checking the Snowflake database, you can see that the data has been stored as shown below.
## Fork the sample dbt project on Github
This section assumes that you have created a Github account and are logged in to that account.
1. Click on the GitHub URL and then click the `Fork` button in the top right.
URL:https://github.com/fivetran/snowflake_fivetran_vhol
2. If you see the following, the `Fork` is complete.
## Set up Fivetran_dbt Transformation
This section differs from the procedure described in the documentation due to changes in the Fivetran UI.
[Note]
To obtain actual data output in this section, at least one `Fivetran Google Ads` connection needs to be syncing data to the destination.
You can test the dbt connection until this point.
1. From `Transformations`, click `Add Transformation`.
2. Select your `destination` from `Select a destination`.
3. You'll be directed to the following screen, select dbtCore.
*Note: The steps differ, but this follows the documentation
4. Copy the `Public Key`.
5. Click `Settings` in your created GitHub repository.
6. Click `Deploy keys` in the left navigation.
7. Click `Add Deploy keys` at the top right of the screen.
8. On the following screen, enter the title as `Fivetran dbt access`. Also paste the copied `Public Key` in the Key field. `Allow write access` checkbox is not required.
If you want to revoke Fivetran's access, return to this screen and delete the key.
9. From the `Code` tab, click the green `Code` button. Copy the URL from the `SSH` tab.
URL content:
git@github.com:your_username/snowflake_fivetran_vhol.git
10. Return to Fivetran and paste the copied value into the `Repository URL`.
11. I set `Default Schema Name` to `GOOGLE_ADS_DBT`.
12. Execute `Save&Test`, and when you see the following screen, the setup is complete.
13. You will be directed to the following screen, where you can create a job.
14. Since this wasn't specified in the documentation, I configured it as follows.
15. Select the created job and click `Run`.
It will output an error as shown below.
【Troubleshooting Point】
After checking the documentation, I found the following issues:
- The version in the `packages.yml` file that was `Fork`ed is outdated
packages:
-
package: fivetran/google_ads_source
version: [">=0.9.0", "<0.10.0"] -
package: fivetran/google_ads
version: [">=0.9.0", "<0.10.0"]
This needs to be changed to:
packages:
- package: fivetran/google_ads
version: 1.0.0
Regarding `google_ads_source`:
> All required sources and staging models are now bundled into this transformation package. Do not include fivetran/google_ads_source in your packages.yml since this package has been deprecated.
This indicates that the source package is deprecated and should not be included in the transformation package.
【Reference Documentation】
https://hub.getdbt.com/fivetran/google_ads/latest/
- The object specification in `dbt_project.yml` is outdated
source-paths: ["models"]
test-paths: ["tests"]
This needs to be changed to:
model-paths: ["models"]
seed-paths: ["seeds"]
After making these changes, it succeeded as shown below:
However, as mentioned earlier, without setting up the `Fivetran Google Ads` connection to send data, the conversion results will not be written to the Snowflake side by design. The destination tables were created, though.
## Snowflake Part 2
Since the data from the previous section was not created, this section has been omitted.
The content explains the typical workflow for administrators and analysts based on the data that would have been created in the previous section.
## In Conclusion
How was it?
We were able to experience Fivetran's setup and synchronization method, connection with Snowflake, and connection setup through dbt.
Since some time has passed since this Quickstart was created, the UI and specifications of Fivetran, Snowflake, and dbt that we used have changed in some areas, which made me once again realize how quickly these products are being updated.
I hope this article serves as a helpful reference.
![[新機能]Snowsightで対象のテーブルの品質に関する統計情報を確認できる「Data Quality」タブが追加されました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-37f4322e7cca0bb66380be0a31ceace4/0886455fd66594d3e7d8947c9c7c844d/eyecatch_snowflake)
