SQLite3 and Other Critical Tools for Salesforce Data Work

SQLite? More like SQLightning.

The problem with spreadsheet-driven migrations

Most Salesforce data work starts the same way: export to CSV, open it in Excel, VLOOKUP the parent IDs onto the child records, save, upload with Data Loader. It works once. Then the client sends a corrected file, and you redo every lookup by hand, and you have no record of which of the four versions on your desktop produced the load you already ran.

The three things I want out of a data process are:

Repeatability: I can run it again from scratch and get the same result, in an idempotent manner.
Accuracy: the transformations are written down as code I can read and test, not as cell formulas.
Speed: no manual step between extract and load.

SQLite3 as a staging area

SQLite3 is a serverless SQL engine that stores an entire database in one file. There is no server to install and no connection string to configure; you open a path and start writing tables. For a migration that means the whole staging environment is a file you can zip up and hand to a colleague, or delete and rebuild in thirty seconds.

The pattern is: extract from the source system into SQLite tables, run transformations and validations there, then load the results into Salesforce. Every step is SQL, so every step is diffable and re-runnable.

Also, a table JOIN beats the pants off of a VLOOKUP operation any day of the week.

Python, Pandas, and Simple-Salesforce

Beyond SQLite, we need a Salesforce client and something to move rows around. I use Python here, but Ruby (with the Restforce gem) or Node.js (with the jsforce library) would work the same way.

Simple-Salesforce wraps the REST, Bulk, and Bulk 2.0 APIs, so a describe call or a bulk query is one method instead of a hand-rolled HTTP request and a polling loop.
Pandas reads a CSV into a DataFrame and writes a DataFrame to a SQL table in one call each, which covers most of the shuttling between files and SQLite.

Friendly reminder: never store your Salesforce credentials in your code. Use environment variables or a secure configuration file.

Setting up your Python environment

To get started, you’ll need to install the required Python libraries. You can do this using pip:

pip install simple-salesforce pandas

Install into a virtual environment rather than your system Python; the venv docs cover the setup.

Putting it all together

Let’s walk through a simple example to illustrate how these tools can be used together.

Step 1: create the connected app

Create a new Connected App in Salesforce and obtain the client ID and client secret. You’ll also need your user’s security token. You’ll need to enable OAuth settings for the app, and the scope for the connected app should include full and refresh_token.

Step 2: store the credentials

Store your credentials in a secure manner. I recommend using environment variables, but we’ll use a configuration file for this example:

{
    "username":       "",
    "password":       "",
    "security_token": "",
    "client_id":      "",
    "client_secret":  "",
    "api_version":    "61.0",
    "host":           "test"
}

Step 3: authenticate

Authenticate with Salesforce using Simple-Salesforce. Here’s a simple function to build the connection using the configuration file:

from simple_salesforce import Salesforce
import json

env_file = open("./myconfigurationfile.json", 'r')
sf_creds = json.loads(env_file.read())

un = sf_creds['username']
pw = sf_creds['password']
st = sf_creds['security_token']
ck = sf_creds['client_id']
cs = sf_creds['client_secret']
en = sf_creds['host']

sf = Salesforce(username=un, password=pw, security_token=st, consumer_key=ck, consumer_secret=cs, domain=en)

(Yes, it’s really that straightforward.)

Step 4: extract from Salesforce

Extract data from Salesforce. In this example, we’ll extract data from the Account SObject and dynamically retrieve all fields:

sobject_type = "Account"
sobject = getattr(sf, sobject_type)
description = sobject.describe()
field_names = []
compound_fields = set()
for field in description["fields"]:
    field_names.append(field["name"])
    if field["compoundFieldName"] is not None and field["name"] != "Name":
        # compound fields should not be queried directly
        compound_fields.add(field["compoundFieldName"])

# remove compound fields
field_names = [field for field in field_names if field not in compound_fields]

# dynamically define bulk2 type
bulk_sobject = getattr(sf.bulk2, sobject_type)
query = f"SELECT {', '.join(field_names)} FROM {sobject_type}"
bulk_sobject.download(query, path='./temp_data')

Step 5: load the CSVs into SQLite

Now that we have our data locally as CSV files, we can load them into a SQLite3 table:

import sqlite3
import pandas as pd
import os
import shutil
from pathlib import Path

# create a new SQLite3 database
db = sqlite3.connect('salesforce_db.sqlite3')

# for each CSV file in the data directory, load it into a SQLite3 table
for csv in Path('./temp_data').glob("*.csv"):
    df = pd.read_csv(csv)
    df.to_sql("Account", db, if_exists='append')
    
# then, we can clean up the CSV files
for filename in os.listdir('./temp_data'):
    file_path = os.path.join('./temp_data', filename)
    try:
        if os.path.isfile(file_path) or os.path.islink(file_path):
            os.unlink(file_path)
        elif os.path.isdir(file_path):
            shutil.rmtree(file_path)
    except Exception as e:
        print('Failed to delete %s. Reason: %s' % (file_path, e))

Step 6: transform and validate

Now that the data is in SQLite3, we can run our transformations and validate the outputs. You could do this in more Python, but plain ol’ SQL is shorter:

Here we create a new table Account_Clean holding only the records that have both a Website and an Industry:

CREATE TABLE Account_Clean AS
SELECT * FROM Account
WHERE Website IS NOT NULL AND Industry IS NOT NULL;

Because the transformation is a statement rather than a spreadsheet action, re-running it after the client sends a corrected extract is one command, and the definition of “clean” is right there for anyone who asks.

Step 7: load back into Salesforce

Finally, if we had modified data, we can load the records into Salesforce via the Bulk API:

pd.read_sql_query("SELECT * FROM Account_Clean", db).to_csv("temp-data.csv", sep=",", index=False)
bulk_sobject = getattr(sf.bulk2, "Account")
results = bulk_sobject.upsert("temp-data.csv", external_id_field="Id")

Closing thoughts

The seven steps above are a script. When the client sends a revised file on Friday afternoon, you run the script again instead of redoing an afternoon of lookups, and the SQL in step 6 is the answer to “what did you change?”. That is the whole argument for doing this locally: not that SQLite is fast, but that the work survives being questioned.