All code for this post can be found here: Publishing and Subscribing Salesforce Reports

Working in Marketing Operations for a few years prior to coming into Tabsdata, I have quite a bit of experience working with Salesforce. Because it sits at the intersection of so many different teams: Sales, Marketing, Customer Success, Sales Eng, Finance, etc., one thing you notice is that even though we all share the same database, the ways people use that data diverge massively.  

Data Eng prefers to do some sort of ELT into a data warehouse or datalake, while Sales manages everything within Salesforce Reports.  It’s not a problem per se, however it gets difficult when data in your BI tool starts diverging from the data that Sales or Marketing has in their Salesforce reports. Then you have to rebuild the salesforce report manually with SOQL and constantly be monitoring if the report filters have changed.

Also, Salesforce is not great at tracking changes to your data on a macro scale. Sure, you can see data value changes on an individual record, but you need to set field history tracking on a limited set of fields; so answering business level questions like “how many mqls did I have in this report 6 days ago” is difficult unless you’re explicitly snapshotting your data every day. 

With Tabsdata’s 1.3.0 release, we introduce new connectors for Salesforce reports that allow you to easily Publish Salesforce Report Data. All you need is your salesforce report  name and you can easily propagate salesforce report data while automatically snapshotting and versioning the data as well.

Overview

The pipeline consists of two parts:

1. Publisher: Reads Salesforce Report Data and Publishes it into Tabsdata.
   
   
2. Subscriber: Writes Tabsdata Table Data into Snowflake
   

Publishing from Salesforce

I start by registering a publisher that queries the my Salesforce Report and publishes it into a Tabsdata Table called sf_snapshot

@td.publisher(
    source=td.SalesforceReportSource(
        report=SALESFORCE_REPORT,
        find_report_by="name",
        column_name_strategy="columnName",
        credentials=td.SalesforceTokenCredentials(
            SALESFORCE_USER,
            SALESFORCE_PASSWORD,
            SALESFORCE_TOKEN,
        ),
    ),
    tables="sf_snapshot",
)
def salesforce_pub(
    tf: td.TableFrame,
):
    return tf
  

Subscribing to Snowflake

I then subscribe my sf_snapshot into Snowflake

CONNECTION_PARAMETERS = {
    "database": SNOWFLAKE_DATABASE,
    "schema": SNOWFLAKE_SCHEMA,
    "warehouse": SNOWFLAKE_WAREHOUSE,
    "account": SNOWFLAKE_ACCOUNT,
    "user": SNOWFLAKE_USER,
    "password": SNOWFLAKE_PAT,
    "role": SNOWFLAKE_ROLE,
}


@td.subscriber(
    tables="sf_snapshot",
    destination=td.SnowflakeDestination(
        CONNECTION_PARAMETERS,
        destination_table="sf_snapshot",
        if_table_exists="replace",
    ),
)
def snowflake_sub(tf):
    return tf
  

Results

With two functions, I was able to build a basic ELT workflow between Salesforce and Snowflake. After I invoked my publisher a few times, I dove into the UI to see the different versions of my data. 

Closing Thoughts

Could I have done something like this without Tabsdata? Yeah probably. However, I would’ve needed to manually build out the code to utilize the Salesforce API, designated a storage location to store my data versions, build something to catalog that data and allow me to meaningfully query and access those versions of data, and manually build the code for exporting that data into Snowflake.

Tabsdata's declarative architecture helped abstract all that away so as long as I have the report name I want to subscribe to, I can be confident my data will be handled cleanly and reliably.