How to build a Periodic Snapshot Fact Table for Salesforce Data

Periodic snapshot fact tables capture and store the state of a business process at regular intervals, such as daily, weekly, or monthly. In contrast to standard transactional fact tables -  that simply log discrete events - periodic snapshots aggregate and record data at a fixed frequency, offering a time series perspective.

With Salesforce data this technique is particularly useful for numeric scores and journey stages. Time series analysis using a periodic snapshot fact table can be crucial for understanding trends and forecasting.

In this blog, we will explore how to record snapshots of your Salesforce data daily, using the Matillion Data Productivity Cloud.

Use Cases for Snapshots

Before diving into the technical steps, let's consider some potential use cases for daily snapshots:

• Tracking Fluctuating Values: Preserve daily values such as opportunity in-pipeline value or lead scoring, to monitor trends
• Monitoring Opportunity Close Dates: Understand which opportunities are being delayed.
• Analysing Performance Metrics: Snapshot metrics like leads per sales rep, sales activities per sales rep, MQLs per region, etc.

Other ideas for use cases could include:

• Snapshot daily values that fluctuate such as opportunity in-pipeline ARR or lead scoring
• Snapshot opportunity close dates to understand which opportunities are being pushed out
• Snapshot # x per y (e.g.,: leads per SDR; activities per SDR; MQLs per region, … )

Creating a periodic snapshot from a type 1 dimension table

Create a table, account_dim, that is up-to-date with Salesforce. Some values on the account change over time, including 6Sense numerical scores and journey stages. I want preserve these scores on a daily basis, for historical tracking.

The jobs

0. Set-Up: Creating the Final Snapshot Table

To begin, we need to set up the final table using a SQL script in an orchestration job. 

In the script, include a unique ID/key and the fields you want to preserve. Also include snapshot_date and snapshot_datetime.

Name it something like account_snapshots_final; we will reference this later. This job will only be run once.

1. Master Orchestration

This orchestration job is made so that we can snapshot lots of different tables, not just the account table. My job just calls the account table job it for now. It also sends an email alert if it succeeds or fails.

 2. Table-Level Orchestration

This orchestration job calls two transformation jobs:

(1) Saving the current account_dim table to a staging table

(2) Appending the rows in the staging table to the final one

3. Staging Table

This transformation job reads the account_dim table, adds our snapshot date columns, and then writes it to a staging table. Name it something like staging_snapshot_accounts.

 Calculator:

4. Append Staging Rows to Final Table

This transformation job appends the new rows in the staging table to our final snapshot table. It uses a table input component and a table output component. The final output table name is the same as the one we created in set-up/step 0, account_snapshots_final.

Make sure to select ‘Append’ in the truncate option of the table output component, and make sure to add in all columns:

5. Schedule Job

I schedule this job to run daily.

‘Manage Schedules’ in the project dropdown

Choose the master orchestration job when setting up the schedule:

Obtaining business insights from a Periodic Snapshot table

Now that data is accumulating in the periodic snapshot table, it opens the ability to generate new insights that were previously unavailable. For example:

• Which opportunity stage has the most dropoff?
• Which opportunity stage is taking the longest amount of time to pass?
• What causes pipeline fluctuations?

A periodic snapshot table can also help trace and debug changes in summary or aggregate reporting. For example, finding out if a value changed or a record was deleted.

Conclusion

With Matillion, setting up periodic snapshot fact tables for your Salesforce data is straightforward and powerful. By capturing and storing data at regular intervals, you can gain deeper insights into your business processes. Track fluctuating values, monitor delayed opportunities, and analyze performance metrics to make informed decisions.

Implementing these steps ensures that you can generate new insights and trace changes in your data, providing a robust framework for time series analysis and forecasting. Start leveraging the power of periodic snapshots to enhance your data strategy and drive better business outcomes.

Ready to unlock the potential of your Salesforce data? Try Matillion for free today and start gaining deeper business insights!