How to load data from Salesforce to Snowflake destination

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.

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.

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.

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;

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;

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

);

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;

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.

‍

‍

‍