SQL Master Data Services database has huge leaf member staging table - can I purge old records?
In short: The official answer is to use the new proc mdm.udpEntityStagingBatchTableCleanup to clean up entity staging tables, but in practice, DELETE or TRUNCATE should work just fine (or might be necessary), as long as you understand your batch detail.



I found reference to a SQL 2012 patch that adds 3 stored procedures to MDS, including the one I need:

--Cleanup entity-based staging table
EXEC mdm.udpEntityStagingBatchTableCleanup @ModelID, @CleanupOlderThanDate;


See also this article for scheduled cleanup options utilizing the same procs.

For my purpose, though, I was still concerned about blowing up the tran log with the 300+ million rows that had to be deleted, so I looked in detail at the inner workings of mdm.udpEntityStagingBatchTableCleanup. This stored proc does the following:


Populates a temp table with all completed batches that match your deletion criteria (for the given @Model_ID, older than @CleanupOlderThanDate). 
Builds dynamic DELETE statements for the corresponding Leaf, Consolidated, and Relationship tables for each DISTINCT Entity_ID in the temp table.
It executes each of those DELETE statements for each entity table, filtered via a join to the temp table.
It then deletes from mdm.tblStgBatch itself, again filtered by join to the temp table.


There is no attempt to limit the amount of rows deleted at once, and in my case,  the 300+ million rows are all for a single Entity_ID, which would likely blow up the transaction log.

The good news, though, is that if I intend to remove all Entity_IDs and all dates anyway (which I do), then that's functionally equivalent to a TRUNCATE,
 as long as I am careful to not attempt it right in the middle of an active batch.

Finally, while I couldn't find any official MS sources that explicitly recommended truncating these tables, I did find several articles where DBAs do a routine TRUNCATE at either the beginning or the end of their import process:


"Step 1:  Truncates each of the 3 staging tables."
"If you want to empty the staging table, you can either delete all records, or click Clear Batches in Integration Management."
TRUNCATE TABLE stg.TableName_Leaf


So my plan is to do an initial TRUNCATE, and then schedule mdm.udpEntityStagingBatchTableCleanup to run on a periodic basis.