Using Insights to take historical snapshots of your Copper data

In this article, I’ll demonstrate how you can use a simple Insight to record an audit trail of any of your endpoints.

The problem

For this example, we're going to use data from the Opportunity table in our Copper connector. Extracting data from this table is pretty simple...

select Name, CompanyName, MonetaryValue, PipelineStageID
from Opportunity

The problem is that this data is in a relational database. If a change is made to any of these records, your prior value - by design - is overwritten because relational databases prefer to have a single current copy of your records:

What you really need is a snapshot of your Opportunity records as they change over time, so that you can reference items as they were at any arbitrary point in the past.

Unfortunately, Copper doesn't record historical data like this. But fortunately for you, SyncHub does.

The solution

SyncHub lets you easily resolve this problem by creating an Insight which detects changes to your data and records them in a timestamped table that you can reference in your reports.

The solution consists of two parts - a self-referencing Insight which searches for changes, and a Parameter which caches your data, ensuring excellent performance for your Insight. Let’s start with the Parameter itself, as it is the easiest. Copy this SQL into your Query Editor (I have documented the code inline, so read it carefully):

-- Create a temp table to store the latest value of each record
declare @Keys table (
    SnapshotKey nvarchar(50)
)

-- Include all existing snapshots by querying the existing records from your Insight
-- Remember, your Insight may not have been created yet, so you need to check
-- if the table exists first.  You can call your table whatever you like, but make a note of it
-- as you'll be using it in your other Insight later too. In this case,
-- I have chosen to call it 'copper_opportunity_snapshot'
if exists (select 1 from information_schema.tables where table_schema='sh_report_cache' and table_name='copper_opportunity_snapshot')
begin
    insert into @Keys
    select distinct SnapshotKey from sh_report_cache.copper_opportunity_snapshot
end

-- We also add a "current" key, which our calling function uses to populate the current snapshot
-- The snapshot key is simply a representation of the date time, in an *orderable* format, which means yyyyMMddHHmmss
-- It must be orderable because get the LATEST version from our snapshot table. It also must be a string, because we have to account
-- for the '0' placeholders here and there
declare @SnapshotKey nvarchar(50) = FORMAT(GETUTCDATE(), 'yyyyMMddHHmmss')
insert into @Keys values (@SnapshotKey)

-- Return our list
select * from @Keys

So far so good. Now, you need to make it a Parameter, so that we can use it in our other Insight:

Great. Now, create a second file in your Query Editor and pop in the following SQL:

-- By pulling the key from our parameters, we enable Insights to cache
-- Note that my parameter name (copper_opportunity_snapshot) may differ from the one you created in the earlier step
declare @SnapshotKey nvarchar(50)
set @SnapshotKey = '[PARAMETERS.copper_opportunity_snapshot]'

-- Create a temp table to store the latest value of each record
-- The columns here must match what you eventually want to snapshot for your Opportunity data
declare @MostRecentSnapshot table (
    RemoteID nvarchar(200), -- Always grab the RemoteID - we use this later to detect changes
    Name nvarchar,
    CompanyName nvarchar,
    MonetaryValue decimal,
    PipelineStageID bigint
)

-- Load our latest snapshots? Remember, our Insight may not exist yet, so we need to check
-- first.  And note that the table name must match that in your prior Parameter query
if exists (select 1 from information_schema.tables where table_schema='sh_report_cache' and table_name='copper_opportunity_snapshot')
begin
    ;with latest_records as (
        select RemoteID, max(SnapshotKey) as LatestSnapshotKey
        from sh_report_cache.copper_opportunity_snapshot
        group by RemoteID
    )
    insert into @MostRecentSnapshot
    select s.RemoteID, s.Name, s.CompanyName, s.MonetaryValue, s.PipelineStageID, s.SnapshotKey
    from sh_report_cache.harvest_client_snapshot s
    inner join latest_records l on (s.RemoteID = l.RemoteID and s.SnapshotKey = l.LatestSnapshotKey)
end

-- Now just select the records that are in your current realtime table, but whose data differs from that in the snapshot table
select
    c.RemoteID, c.Name, c.CompanyName, c.MonetaryValue, c.PipelineStageID, @SnapshotKey as SnapshotKey
from [CONNECTIONS.copper].Opportunity c

-- Compare to their latest snapshot?
left join @MostRecentSnapshot snapshot on (c.RemoteID = snapshot.RemoteID)

-- Get records where either the snapshot is different, or there is no prior snapshot
where (
    snapshot.RemoteID is null
    or (
        c.Name <> snapshot.Name
        or c.Name <> snapshot.Name
        or c.CompanyName <> snapshot.CompanyName
        or c.MonetaryValue <> snapshot.MonetaryValue
        or c.PipelineStageID <> snapshot.PipelineStageID
    )
)

And that’s it for the SQL - not too bad, right? Next, you just need to create an Insight from this second query:

Important - set your parameter cache

Open the newly created Insight and observe its settings. It is crucial that you configure your Insight to cache your prior records, otherwise it will just continuously overwrite your snapshots with the latest data. To do this, select the Partitions section from your Insight…

And set your cache set zero, which means “immediate”:

Let’s see it in action…

Here is my Insight after it has run for the first couple of times. You can see that the first time (1) it took all of my Opportunity records, but the second time it ran (2) it took no records at all, because no changes were made to the data in the interim:

However, a few days later I might make a change in Copper. Let's say for example that I've changed the Name value from 8 New Copy Machines to Cross-Sector Collaboration. Our snapshot will detect the difference and...voila:

And our audit table has recorded the change history with date snapshots:

Daaaaamn…! That’s cool.