All tutorials

Copy Salesforce data to Snowflake

Learn how to use Airbyte Cloud to sync CRM data in Salesforce to Snowflake.

No items found.

Salesforce is a cloud-based CRM platform that has become the go-to solution for businesses to understand and manage their customers. Its low-code admin tools, powerful data integration capabilities, and dynamic dashboards have made it the market leader in the CRM space. 

By moving your Salesforce data to a cloud-based data warehouse like Snowflake you can combine sales data with other business sources to build a 360-degree customer view and build custom reporting.

With Airbyte Cloud, you can seamlessly export, backup and replicate data between any supported business application, database and data warehouse. In this tutorial, we'll take you through the steps to set up Airbyte Cloud, and copy over your Salesforce data into Snowflake.

Prerequisites

1. An Airbyte Cloud account to perform data replication. You can create an Airbyte Cloud account here.

2. A Salesforce account to export the data. You can create a Salesforce account here. You will need at least a developer account for Airbyte to be able to access Salesforce REST APIs.

3. A Snowflake account to load the Salesforce data. You can create a Snowflake account here.

Step 1: Set up a Salesforce account

The first step is to configure your Salesforce account to allow external applications to connect and access data from the account. Login to Salesforce, go to Setup > Create > Apps, and select New in the Connected Apps section.

Fill in the required fields and click on Save to add the new app.

Next, we will generate and import sample data into Salesforce. For this example, we will create Leads data. Go to https://www.mockaroo.com/ and create the Leads data set with the following fields:

The Mockaroo tool will generate a CSV with sample data.

Login to Salesforce and go to Setup > Data > Data Import Wizard. Choose the Leads object, upload the CSV and follow the Wizard steps to upload the data.

Once complete, you can view the data by going to the Salesforce homepage > Leads.

Step 2: Set up a Snowflake account

When creating a Snowflake account, you’ll need to pick a Snowflake edition and a cloud provider as part of the account creation process. Next, you’ll receive an email containing your login URL. Make sure to bookmark this URL for future logins.

Once your account is successfully created, you'll be redirected to the Snowflake dashboard. The worksheet area will be the primary place you’ll run scripts for creating and modifying resources. You will need to set up the destination database, user, role, and schema on Snowflake for the sync.

The good news is that Airbyte provides a script in the Snowflake destination documentation for automating this process. Paste the snippet below into your worksheet area on Snowflake.  Select ‘All queries’ and run the script by clicking on the run button.

Once executed, you should see the following message:

Step 3: Set up a Salesforce connector as a source

Next, log in to Airbyte Cloud and create a new source. Give the source a name and choose Salesforce as the Source type. You can find more information about the Salesforce connector from the Airbyte documentation here.

Next, click on the Authenticate your account button to give Airbyte Cloud the required permissions to copy your data. 

Clicking on the button will launch a pop-up window where you will be prompted to sign in with your Salesforce credentials. 

Once authenticated, you will be redirected back to Airbyte Cloud. Then enter the ‘Start Date’ from when Airbyte should copy data and click on Set up source.

Step 4: Set up a Snowflake connector as the destination

The next step is to use the Snowflake connector. Select Snowflake as the destination type and give it a name. You can find more information about the Snowflake connector in the Airbyte docs here.

Enter the values for the fields based on the values set in the script in Step 2. For the host, enter the URL you received when signing up for Snowflake. If you had updated the password in your script, enter the new password. Once ready, click on the Set up destination button.

Step 5: Set up a Salesforce to Snowflake connection

Once the source and destination are configured, you can access your connection settings. Here you can set the replication frequency, the destination namespace, and also set a destination stream prefix if required. You can learn more about this configuration settings in the Airbyte Cloud getting started guide.

You can also see the various source data streams that are available to be copied. In this example, we will select the Lead stream. Depending on the features you use on Salesforce, you can select different objects to copy. Each objects will replicated into a table in Snowflake

You can set the sync mode for each data stream individually. In this case, we will set the Lead stream to use the Incremental | Append sync mode. If you want to save each version of your Salesforce data you can instead select the Full Refresh | Append sync mode that will append all your data to each table. In this case, you will need to deduplicate the data yourself when querying the data.

By selecting Normalized tabular data Airbyte will automatically create tables with one column for each field in the source data.

Once configured, save the connection and select Sync now.

Once the sync is complete, you can go to the Database section in the Snowflake UI to see the tables that have been created. The copied data also contains the raw data in a separate table with the name _AIRBYTE_RAW_{TABLE_NAME}

You can view the structure of the table as well as the data types for each of the fields. The Lead data has the following structure:

To test the incremental sync, you can add more data to the Salesforce and run another sync to copy the new data.

Once the sync has been completed, you can go back to Snowflake to see the updated counts for the appended rows.

Conclusion

To summarize how we can move data between Salesforce and Snowflake with Airbyte Cloud:

  1. Set up a Salesforce source in Airbyte Cloud. 
  2. Set up a Snowflake destination in Airbyte Cloud. 
  3. Set up a connection between Salesforce and Snowflake.
  4. Run a sync to move your sales data.

We know that development and operations teams working on fast-moving projects with tight timelines need quick answers to their questions from developers who are actively developing Airbyte. They also want to share their learnings with experienced community members who have “been there and done that.”

Join the conversation at Airbyte’s community Slack Channel to share your ideas with over 4000 data engineers and help make everyone’s project a success. With Airbyte, the integration possibilities are endless, and we can't wait to see what you're going to build!

Open-source data integration

Get all your ELT data pipelines running in minutes with Airbyte.

Similar use cases

Replicate Salesforce data to BigQuery

Learn how to configure your Salesforce account to replicate leads data to BigQuery.