-2

Is there a benefit from doing a periodic full table refresh when you regularly insert/update/delete incrementally?

To clarify, this question is in regards to ETL processes.

James
  • 117
  • 2
  • 11
  • What do you mean by a "full refresh"? Like a truncate/insert of the full data set? – Xedni Apr 03 '18 at 02:26
  • I realize that I could have been a little more clear. Fortunately Eric caught on to what I meant. – James Apr 04 '18 at 04:56

2 Answers2

1

If you are 100% certain that your incremental updates are capturing all CRUD operations, there is no reason to flush and fill. If your incrementals have room for error beyond the tolerance of the business rules governing the process, then you should consider period flush and fills.

It all depends on your source system, your target system, your ETL process, and your tolerance for error.

Eric Brandt
  • 7,886
  • 3
  • 18
  • 35
0

I'm not sure what you mean by 'data refresh', so I will take some liberties in assuming that you mean rebuilding indexes. Good maintenance involves rebuilding indexes periodically over time in order to eliminate fragmentation of any indexes on tables that are the result of INSERT/UPDATE/DELETE.

For more information, read: https://dba.stackexchange.com/questions/4283/when-should-i-rebuild-indexes

If you mean to say a full backup, then that is to truncate the transaction log and create a more recent database backup that you can fully restore from without having to restore the last full backup plus all incremental partial database backups and the transaction log backup.

For more information, read this: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/full-database-backups-sql-server

and this: https://technet.microsoft.com/en-us/library/2009.07.sqlbackup.aspx

J Weezy
  • 3,507
  • 3
  • 32
  • 88