Fivetran

Fivetran might be tricky to integrate with SnowDDL since Fivetran wants to create and manage its own schemas.

How to configure Fivetran with SnowDDL?

1) Manually create WRITE role

While going through Snowflake Setup Guide, use word WRITE in the name of the role created for Fivetran technical user.

For example: FIVETRAN_WRITE_ROLE.

2) Manually create READ role

Manually create an additional READ role to provide read-only access to tables created by Fivetran.

For example: FIVETRAN_READ_ROLE.

CREATE ROLE "FIVETRAN_READ_ROLE";

GRANT USAGE ON FUTURE SCHEMAS IN DATABASE "FIVETRAN_DATABASE";
GRANT SELECT, REFERENCES ON FUTURE TABLES IN DATABASE "FIVETRAN_DATABASE";

3) Grant READ role to business roles in SnowDDL config

Grant READ role for Fivetran data to specific business roles using global_roles property.

How does it work with env prefix?

This approach is compatible with env prefix.

You will have one Fivetran database shared by any number of prefixed "environments" created by SnowDDDL.

You may create views referencing existing tables in Fivetran database, but you should NOT use ${{ env_prefix }} placeholder.

How does it work with dev account?

This approach can be used when you have separate Snowflake accounts for "development" and "production".

1. Configure Fivetran for prod account first.

2. Create OUTBOUND SHARE on prod account.

3. Grant Fivetran database, schemas and objects to OUTBOUND SHARE.

4. Add dev account to OUTBOUND SHARE.

5. Create INBOUND SHARE in dev account with name FIVETRAN_DATABASE.

6. Create FIVETRAN_READ_ROLE in dev account.

7. Grant IMPORTED PRIVILEGES for FIVETRAN_DATABASE INBOUND SHARE to FIVETRAN_READ_ROLE in dev account.

With this approach will get an exact copy of data ingested by Fivetran both into prod and dev accounts, but you will have to pay Fivetran only once.

The only real downside is lack of "future grants" to OUTBOUND SHARES. You will have to grant new Fivetran schemas and tables to OUTBOUND SHARE from time to time to make it available in dev account.

What if I need more fine-grained access to Fivetran schemas?

The easiest way to achieve it is to create multiple Snowflake "destinations" in Fivetran, each destination pointing to a separate database. For example: FIVETRAN_REGULAR and FIVETRAN_FINANCE. You may reuse write role and warehouse, but you should create separate READ roles.