Staging table is piling up with data

[Sunny26]

Hi Developers,

We have done integration between Snow an Salesforce(SF), This is 2 way integration for incident module. we are using a staging table to capture transaction between SF and SNow. Transaction capture include incident creation, incident update, attachment, work note and comments update from Snow to SF and SF to Snow. This integration was done 6 months back but the data in the staging became huge due to number of transactions. How can we handle this data? Even if we want to check any transaction with respective incident number, it is taking so much of time to load. 

Any suggestions how we can handle this data?

Thanks,

Sunny

[Tony Chatfield1]

Hi, best practice for a data staging table is to clean out or archive old records on a regular basis, and this is normally automated if utilizing OOB import sets.

https://docs.servicenow.com/bundle/tokyo-platform-administration/page/administer/import-sets/task/de....

You should be able to create a simple scheduled job that runs daily (or weekly) and delete records older than X days,  as once your staging data is processed it has no real value other than auditing.

If the data is critical to your business you might want to wait a little longer than 7 days so that audit information is available for reference should any anomalies with final (target table) data need review\investigation.

[AshishKM]

Hi Sunny26,

Are you using single staging table for recording all transaction, similar to system audit tables. 

The OOTB audit tables are using distributed method to record all changes. Seems like your system has custom audit log for this integration.

You can save the staging table URL in filter mode and apply the filter data for result.

You can start the staging table cleanup ,( weekly or monthly ) to reduce the record count.

You can apply column level indexing for better search result.

[Mathieu Lepoutr]

Hi Sunny

It's my understanding that you may want to review your indexing strategy and ensure that the appropriate indexes are in place for the queries you are running. It sounds like you are facing a challenge with the amount of data being stored in your staging table.

I have been using Exalate for a SF to Snow integration and honestly it makes it way smoother.