Switching from Fivetran to Boomi Data Integration: The Best Practices Guide

A common question we hear from prospects is how to transition from Fivetran to Boomi Data Integration. This shift is often driven by cost concerns or platform limitations. While Boomi Data Integration’s flexible pricing and ingestion capabilities are compelling reasons to switch, users often discover even more value through additional features that streamline their data management workflows.

This guide outlines how Fivetran’s processes align with Boomi Data Integration’s capabilities and highlights other beneficial functionality you can take advantage of after making the switch. Our goal is to make your migration to Boomi Data Integration as smooth as possible.

1. Setting up your Boomi Data Integration Account

When you get started with Boomi Data Integration, you’ll notice your account contains two environments by default: Production and Development.

Boomi Data Integration’s Environments give you optimal control and flexibility when organizing user permissions and data assets (connections, pipelines, etc.) within your Boomi Data Integration account.

For Fivetran Enterprise or Business Critical plans, this may sound similar to Fivetran’s Teams object; however, Boomi Data Integration’s Environments aren’t simply about ensuring that only the relevant users or groups of users have specific access to relevant assets. Beyond the simplicity of managing users and assets across business units or even external customers, Boomi Data Integration’s Environment feature enables an easy way to manage your data development lifecycle and ensure you can easily develop pipelines within one environment and then deploy your pipelines (with all of their dependencies) into another environment. These deployments can be done seamlessly using variables that match the environments you have created in your destination data warehouse or lakehouse. For example, if you plan to replicate data into Snowflake and you have a Snowflake development environment as well as a production environment, you can easily create a variable in Boomi Data Integration and automate your deployments from one environment to another.

No more manual switching or management of your Fivetran connectors when moving from development to production. 

If you want to get started quickly, we recommend you start configuring your pipelines in the Boomi Data Integration development environment – this will help you keep your options open later on. If you already know how you want to organize your data development processes, you can create additional environments or rename the default ones to match your desired structure.

2. Migrating Your First Data Connector

Terminology

While establishing a data pipeline in Fivetran and Boomi Data Integration employs similar components and processes, there are different terms used for the components and a few differences between the two. The following table maps those out:

No more having to maintain your data source connection in multiple places. 

As you can see from the table above, a data pipeline in Fivetran is named a “Connector” and that object contains both the data source connection configuration as well as the data pipeline configuration. In Boomi Data Integration data pipelines, named “Rivers”, are decoupled from the data source connection configuration. This decoupling comes in quite handy in the case where you need to set up multiple data pipelines using the same data source (i.e. because different datasets need to be replicated using different schedules or because you have a different configuration to apply for the replication or downstream process).

The Starting Point

After you choose the first connector to move to Boomi Data Integration, start by creating a connection for that data source. This step is usually very similar to the configuration you would need to do in Fivetran.

Then you can configure your Target Connection (aka Destination in Fivetran) again using similar steps as you would in Fivetran. However, unlike Fivetran, Boomi Data Integration also offers the option to move your data to your target warehouse or lakehouse via your own cloud storage. This option is easily enabled by using custom file zones configuration.

No more storing a copy of your data on the Fivetran side or working extra to create snapshots.

Configuring Your Extract and Load Pipeline

With your Source and Target Connections in place, you can start building your data pipelines using Source to Target Rivers. While the flow is somewhat similar to the Fivetran connector flow, you will notice additional configurations that will help you gain better control over your data pipelines and reduce additional work using downstream processes. If you want to move your connectors as fast as possible, in most cases using the default settings will be the closest to your existing Fivetran setup. However, if you want to take this opportunity to optimize some of your pipeline settings, you may want to use some of the following options:

• Replicated Data Structure: On top of Fivetran’s basic ability to choose your destination schema name, Boomi Data Integration offers fine grain control over your replicated data structure. While in most cases the default settings detected by Boomi Data Integration are all you need, in the one time where you do want to have that control, these abilities can save you a lot of hours building and maintaining workarounds. This includes control over: Table prefix, Table name, Table keys, Table cluster keys, Column name, Columns data type (if you want to change the default mapping), Replication mode (i.e. upsert-merge / append) at the table level, and more. Using these settings, data modelers can build their ideal data structure right from the ingestion step without having to maintain additional costly processes post replication.

• Incremental Load Mode: Boomi Data Integration offers two modes to manage your data loading. The default Upsert – Merge matches the Fivetran default Sync mode and the Append Only is somewhat similar to the Fivetran History mode.

• Calculated Columns: Boomi Data Integration gives you the option to add calculated columns along with your replication process using SQL expressions of your target warehouse or lakehouse dialect. This option can eliminate downstream transformation complexities by ensuring those columns are created during the replication process.

• Custom Scheduling: Instead of controlling just the sync frequency of your pipeline, in Boomi Data Integration you can control the exact scheduling including the time where the pipeline would run within the hour or defining a custom schedule using a cron expression.

Note: There are even more advanced orchestration options when using Logic Rivers – those are detailed later in this guide.

• Enforce Snowflake Masking Policy: If your destination is Snowflake, Boomi Data Integration allows you to respect any masking policy you have configured in Snowflake while loading data into it. That means you can define your data governance masking rules once in Snowflake and make sure your data pipelines don’t override those.
• Custom Query for Database Replication: Similar to Fivetran, Boomi Data Integration offers a Change Data Capture (CDC) replication mode for common databases. In cases where CDC isn’t possible or desired, Fivetran would direct users to use the Teleport mode which isn’t very efficient. In Boomi Data Integration, you can replicate your data using a standard SQL extract where the SQL queries are either generated for you or where you insert your own custom queries to define the specific data to extract. This can be very useful in cases where you want to filter some data prior to the replication or when you need to create a custom dataset to replicate using your own SQL logic.

• Predefined and Custom Reports for Applications (API) Replication: For most applications, Fivetran has predefined a normalized output schema to be created on the destination. Boomi Data Integration, on the other hand, provides users with the options to choose between replicating applications data by choosing from a set of predefined reports or creating their own custom reports that includes their ideal selection of data to extract from the source application. Those reports tend to resemble the expected data structure to be extracted from the source and typically require less data transformation before they could be used for analytics. In addition, this output structure makes it easier to validate the extracted data with business users shortening acceptance test cycles.

No more losing control over your ingestion pipeline definitions and working harder to fix it downstream.

Backfilling Historical Data

Trying to avoid a full extraction of historical data by reusing the existing data already replicated with Fivetran is possible but not always very easy. While setting this up could potentially help you save time and money, in most cases the savings will be greater if you simply replicate the history again with Boomi Data Integration and let it keep on managing incremental loads.

If you still want to try and avoid that initial first sync, you can try to adapt the Boomi Data Integration output to a certain step in your downstream processes (ideally the staging step in your warehouse or lakehouse) where data is already picked up today to serve the rest of your transformations. The process will be as follows:

• Set Boomi Data Integration Source to Target River to start replicating raw data from a certain point in time
• Build transformations that will incrementally load the replicated raw data into a staging model. To avoid mixing with any running Fivetran connectors, it is recommended to do so into a new agnostic materialized view. To build those transformations you can either use Boomi Data Integration’s Logic rivers to run SQL transformations, use dbt, or other solutions.

3. Adjusting Downstream Processes

For the most part, moving a connector is a relatively straightforward step. The part of the process that requires a bit more planning is your downstream data transformation processes.

Using Boomi Data Integration’s Logic River, you have the ability to control downstream orchestration in several ways after your ingestion job is completed. You can orchestrate SQL and Python transformations, trigger a dbt job, or even trigger a Databricks transformation job. This can all be done within Data Integration to streamline your data delivery.

Whether you plan on keeping your current transformation tool or use Boomi Data Integration for it, it is assumed that in most cases, you would want to generate a data model that aligns with the existing data model your analytics tools (i.e. Tableau, Sigma), so you don’t have to work to adjust those as well.

We briefly touched on this above but essentially that means you would need to map the output of Boomi Data Integration’s ingestion pipelines to the desired data structures you previously created.

For databases and files, this process is typically straightforward as the output structure created is very similar to the one you have used to date (with or without a few metadata columns generated by Fivetran or Boomi Data Integration).

For applications APIs, the Fivetran normalized schema and potentially any dbt quickstart models you have used will likely differ from the Boomi Data Integration Predefined and Custom Reports output. For common applications (i.e. Salesforce, HubSpot, others) you may find a Boomi Data Integration Kit that generates a similar data model. Using such a kit can greatly simplify the alignments with any downstream process. For other applications, you will need to map the Boomi Data Integration output to a certain step in the downstream process and adjust the SQL logic accordingly.

4. Moving Forward

Once you migrate your first connector, you simply repeat the same steps across all other connectors until you finalize your move. You will notice that monitoring your pipelines in Boomi Data Integration can be easily done from within your platform dashboard and activities report so you no longer have to depend on raw tables generated in your warehouse.

At this point, you can start thinking about what else Boomi Data Integration can help you optimize. Here are a few possibilities to consider:

• Automate Your Deployments: With Boomi Data Integration’s environment variables and deployments moving pipelines from development to production can be a very simple process.
• Connect to Niche Data Sources: Using Boomi Data Integration’s custom connections and Data Connector Agent, you can easily integrate data sources you didn’t have a connector for.
• Activate Your Analytics Layer: Boomi Data Integration can help you trigger downstream processes to make sure everyone’s data is as fresh as it is in your warehouse. For example, you can trigger a Tableau Extract refresh or a Sigma Workbook materialization to boost your dashboards’ performance using the freshest data and reduce your warehouse computing costs.
• Operationalize Your Data with Reverse ETL: Boomi Data Integration orchestration capabilities along with its Action Rivers enable you to build Reverse ETL workflows to push enriched data from your warehouse back into business applications.

With agentic transformation being top of mind, switching from Fivetran to Boomi Data Integration could be worth exploring.

Boomi not only offers the opportunity to consolidate costs with modern data pipelines using Data Integration, but also to create agentic workflows with a purpose-built AI activation platform using Agentstudio. Don’t just stop at moving your data, use your data to fuel agentic workflows and get on the AI fast track with Boomi.