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 data from Salesforce to 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.
‍
‍
{{COMPONENT_CTA}}

Methods to Move Data From Salesforce to Snowflake

• Method 1: Connecting Salesforce to Snowflake using Airbyte.
• Method 2: Connecting Salesforce to Snowflake manually.

Method 1: Connecting Salesforce to Snowflake using Airbyte

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.

‍

Method 2: Connecting Salesforce to Snowflake manually.

‍
Moving data from Salesforce to Snowflake without using third-party connectors can be a bit challenging, but it's certainly doable by leveraging the capabilities of both platforms. Here's a step-by-step guide to help you accomplish this task:

Step 1: Extract Data from Salesforce

a. Query Data

1. Log in to your Salesforce account.
2. Use Salesforce's SOQL (Salesforce Object Query Language) to query the data you want to export. You can do this through the Developer Console, Workbench, or any tool that allows you to run SOQL queries.

b. Export Data

1. Once you have the SOQL query, you can export the data. If you're using the Developer Console or Workbench, you can usually export the results as a CSV file directly.
2. If you need to automate this process, you could use Salesforce's Data Loader command-line interface (CLI) to export the data to CSV. You can schedule a cron job (on Unix-like systems) or a scheduled task (on Windows) to run the Data Loader CLI with the appropriate SOQL query and export parameters.

Step 2: Prepare Data for Snowflake

a. Clean Data

1. Open the exported CSV file and ensure the data types and formats align with what Snowflake expects. For example, dates may need to be formatted appropriately, and strings sanitized to escape special characters.

b. Split Large Files

1. If your CSV file is very large, consider splitting it into smaller files to make the upload process more manageable and to avoid timeouts or memory issues.

Step 3: Stage Data for Snowflake

a. Choose a Staging Area

1. Snowflake supports various staging options such as internal (Snowflake) stages, Amazon S3, Google Cloud Storage, or Microsoft Azure. Choose the one that best suits your needs.

b. Upload Data to the Staging Area

1. Use the command-line tools provided by your chosen cloud storage provider to upload the CSV files to your staging area.You can use aws s3 cp for Amazon S3, gsutil cp for Google Cloud Storage, or azcopy for Azure Blob Storage.

Step 4: Create File Format in Snowflake

1. Log in to your Snowflake account.
2. Use the Snowflake web interface or SnowSQL to create a file format that matches the CSV file structure. This will ensure Snowflake can correctly parse the data.

CREATE OR REPLACE FILE FORMAT my_csv_format

  TYPE = 'CSV'

  FIELD_DELIMITER = ','

  SKIP_HEADER = 1

  NULL_IF = ('NULL', 'null')

  EMPTY_FIELD_AS_NULL = TRUE

  TRIM_SPACE = TRUE;

‍

Step 5: Create a Database and Schema in Snowflake

1. Create a database and schema in Snowflake if you haven't already.

CREATE DATABASE IF NOT EXISTS salesforce_data;

CREATE SCHEMA IF NOT EXISTS salesforce_data_schema;

‍

Step 6: Create a Table in Snowflake

1. Create a table in Snowflake that matches the structure of the Salesforce data you exported.

CREATE TABLE salesforce_data_schema.my_table (

  Column1 DataType,

  Column2 DataType,

  -- Add all columns as per the CSV file

);

‍

Step 7: Copy Data into Snowflake

a. Copy Command

1. Use the COPY INTO command to load data from the staged files into the Snowflake table.

COPY INTO salesforce_data_schema.my_table

FROM '@my_stage/path_to_files/'

FILE_FORMAT = (FORMAT_NAME = my_csv_format)

ON_ERROR = 'CONTINUE';

‍

b. Verify Data

1. After the COPY INTO command completes, verify that the data was loaded correctly by querying the table.

SELECT * FROM salesforce_data_schema.my_table LIMIT 10;

‍

Step 8: Automate and Schedule

1. To automate this process, you can create a script that combines the data extraction, preparation, and upload steps.
2. Schedule the script to run at regular intervals using cron jobs, scheduled tasks, or Snowflake tasks, depending on your preference and the tools at your disposal.

Remember to handle any security considerations, such as encrypting data during transfer and storing credentials securely. Also, monitor the process for any failures or issues, and set up alerts to notify you if something goes wrong.

By following these steps, you can move data from Salesforce to Snowflake without third-party connectors. It requires some setup and maintenance, but it gives you complete control over the data transfer process.

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.

‍

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!

‍