Salesforce Snapshots: The Easy Way with DBT

Salesforce is a powerful business system, with some very obvious, and very painful shortcomings.

If you've every set up snapshots in Salesforce, you know just how clunky and manual of a process it is. What's worse, there's no great way to extract the snapshots back out for specific use cases.

There is a better way! Let's start with the basics and move on to the solution.

What is a snapshot?

Salesforce, as an operational system, has records that are constantly changing. An account record for Acme Co might change every few hours as employees enter information about sales meetings, billing details, and onboarding information.

With all these changes, businesses often find themselves wanting to observe how those records change over time.

Enter: snapshots. Snapshots are periodic (hourly, daily, weekly, etc) warehousing of the state of Salesforce objects. The output of the snapshots is a new data table storing the state of those records as of each day, week, etc. For example, the snapshot will copy the entire contents of every record on a recurring basis, so that it can be referred back to later.

Why do we need snapshots?

There are nearly limitless reasons for wanting a historical record of your business data. Some examples include:

1. Trend analysis Are there any indicators that signal an account is close to becoming a customer, before they do so? An analyst might want to identify the state of a specific field 30 days prior to signing every customer, to look for commonalities.
2. Historical reporting How much pipeline did you have every day for the last 90 days? That question is nearly impossible to answer without snapshots.
3. Data restore What happens when a new employee accidentally wipes the Industry field on every account record? If you have snapshots, it's relatively easy to reload yesterday's value with only a minor blip in operations.

Shortcomings of Salesforce snapshots

The two most obvious shortcomings of native snapshots in Salesforce (aside from the hundreds of mouse clicks to set them up) are undoubtedly:

1. Limit of 100 fields per snapshot job
2. Limit of 2000 records per snapshot

What does this mean in practice? It means you're not really running comprehensive snapshots:

1. Do you have more than 2,000 account or opportunity records? Sorry, you can't snapshot all of them!
2. If you have fewer than 2,000 records today, but more than 2,000 in the future, those extra records will be dropped from the snapshot.
3. Need to snapshot more than 100 snapshots? You'll have to run multiple snapshots in parallel, and somehow piece them back together later.

I've seen some very "creative" workarounds to the limitations above. Unfortunately, those workarounds can also cause the job to fail. Just look at all the reasons Salesforce lists in their documentation for why a job might fail:

In practice, these failures are disastrous for reporting and analytics. You likely won't know when a job fails, or to what extent. Moreover, the snapshot job might fail for multiple days on end, unknown to anybody, and that data is lost forever.

I regularly see snapshots missing many records for long periods of time. Some days the snapshot has 1,600 records, other days 1,500, and back to 1,600 again with no notes as to why or how to navigate the missing records. If a particular account is in the snapshot one day, missing tomorrow, and then back the day after: how is an analyst supposed to identify, at scale, that it happened? How do they navigate the missing records in their reporting?

As a result, Salesforce snapshots are broadly mistrusted and not used for their original use case. They just aren't very useful.

The Solution: Automated data warehouse snapshots with DBT

Surprise! The solution is a data stack, with dbt powering the snapshot automation. This method is very low cost, and infinitely scalable: there no limits on the number of fields to snapshot or volume of records, or dependencies on the setup of your Salesforce instance.

With a few lines of SQL-driven code, you can snapshot an unlimited number of fields on an unlimited number of records:

Efficient Storage

dbt's built-in snapshot feature stores snapshots much more efficiently than Salesforce does natively. In Salesforce, if you snapshot an object every day that has 2,000 records, after 1 year you will have 2,000 x 365 = 730,000 records. 🤯

However, dbt only creates a new snapshot record if the underlying record has changed. If you have a daily snapshot, and only 5 records have changed in the last 24 hours, then dbt only creates 5 new records -- 99.75% fewer records than Salesforce did natively.

How? dbt snapshots are a representation of the state of a record between timestamps:

Summary

Leveraging dbt for snapshots overcomes all the challenges of using native Salesforce snapshots. With dbt you can:

1. Snapshot an unlimited number of record
2. Snapshot an unlimited number of fields per record
3. Transform the data, and add new columns during the snapshot
4. Store as much as 99% less data thanks to dbt's efficient methodology

Let Us Help!

At Modern Ops, setting up and managing a dbt environment is our bread and butter! We've set up dbt snapshots for all major CRMs, and can have you up and running in less than 24 hours.