Fivetran and Coalesce are two of the best solutions in data today. Fivetran for its ability to move data between so many different sources and targets easily, and Coalesce for its powerful yet simple-to-use platform for transforming data within the Snowflake AI Data Cloud.
These two organizations came together and thought, “How beneficial would it be to our customers to integrate them?”
This vision has become a reality with Fivetrans’s new Coalesce integration.
In this blog, we’ll explain a bit about Fivetran and Coalesce, what this integration does for you, and how to connect your accounts so you can start using it today.
What Is Fivetran and Coalesce?
Fivetran
The E and L in Extract Load and Transform (ELT), Fivetran is a leading automated data integration service. It provides businesses with an efficient way to move and centralize data from all their sources. Boasting over 500 pre-built data connectors, Fivetran simplifies transferring data to, from, and within any cloud data platform available today.
Coalesce
The T in ELT, Coalesce is a data transformation platform specifically designed for Snowflake. It is a hybrid development environment that combines code-first and GUI capabilities, allowing users to build complex transformations visually or write code directly. With Coalesce, users can extend and scale their projects using customizable templates for frequently used transformations and automatically generate standardized, best-practice SQL.
What Does Fivetran’s Coalesce Orchestration Integration Do?
Simply put, this integration allows you to start Coalesce pipelines directly from Fivetran. This means you can have transformations begin the instant the data is available in your Snowflake account. With this integration, you can essentially link Fivetran and Coalesce into one continuous ELT tool.
What Are the Benefits of Fivetran’s Coalesce Orchestration Integration?
Complete ELT Pipeline
There are many benefits of using separate tools to load data into your data warehouse and then transform it. However, scheduling jobs between the two to transform your data as soon as it is loaded can be challenging. With this new connection between Fivetran and Coalesce, your transformations can be kicked off as soon as new data arrives, every time it arrives. This creates a complete ELT pipeline, ensuring end users have the data they need as soon as it is available.
Single View
With these tools connected, users can utilize Fivetran’s logging capabilities to monitor their entire end-to-end pipeline. Simplifying monitoring to one tool increases the chances users catch issues early instead of having to watch two separate tools as they run.
How to Orchestrate Coalesce from Fivetran
As with most things in Fivetran, creating the integration between Coalesce and Fivetran is very simple. Follow along as we walk you through each step with some examples:
Create the Transformation Connection
Note: You must already have established the Snowflake account on which your Coalesce pipeline transforms data as a destination in Fivetran.
There are two pieces of information needed from your Coalesce account in order to connect it to Fivetran:
BaseURL
This is the portion of the URL of your Coalesce account that comes before app.coalescesoftware.io
An access token
The access token needed can be generated by navigating to the Deploy pane in Coalesce and clicking the Generate Access Token link
Once those two pieces of information are acquired, create a transformation in Fivetran with your Snowflake account as the destination. Fivetran will give options for either a Fivetran built-in transformation, a dbt connection, or a connection for Coalesce. Choose the Coalesce connection.
The access URL should be input exactly as seen in this screenshot, replacing <baseURL> with the base URL of your account we saved earlier and the access token with the Coalesce access token. Hit Authenticate, and you should have your connection established, it’s just that simple!
Once authenticated, it will have you choose a project you want to kick off from Fivetran:
Scheduling
There are two ways to schedule your Coalesce pipelines to run from within Fivetran:
Integrated
The associated Coalesce pipeline will run immediately after every connection sync
Choose this option for a full integration of the tools
Custom
This allows you to run the pipeline on a custom schedule, anywhere from every minute to every week
Limitations
As with any kind of new feature, there are some limitations out of the gate. Here are the current limitations according to Fivetran:
You can only set up a single project per destination. If you want to set up an already selected project on another destination, you must first remove it from the previous one.
Integrated scheduling requires that all connectors that are triggering a Coalesce job be associated with the same destination.
Connector syncs trigger Coalesce runs regardless of whether new data lands with a sync.
Closing
The integration of Fivetran and Coalesce is a great solution for users of both platforms. With this integration, you can take advantage of combining Fivetran’s data ingestion capabilities with Coalesce’s powerful transformation features, to streamline data pipelines while achieving insights from your data as soon as it’s available.
Looking to better leverage Fivetran and Coalesce? phData can help! As an Elite Coalesce Partner and Fivetran’s Partner of the Year 2024, phData is uniquely positioned to help organizations of all sizes succeed with both platforms. Contact us today for guidance, automation, and support.
