good night, a query when the origin is passed to the stage base in business intelligence the loading method is total or total + incremental,
I'm thinking of deleting all the data and reloading it, but if it were a very large database and many records would not be optimal. What do good practices suggest?
I will appreciate your opinions,
thank you very much,
I'm thinking of deleting all the data and reloading it, but if it were a very large database and many records would not be optimal. What do good practices suggest?
It depends.
Many companies are more comfortable with Delete-Truncate pattern because it is easy to implement and the amount data isn't a problem only if some conditions are verified (hardware, DBA..)
Incremental Loads (or Up-Sert pattern) are often used to keep data between two systems in sync with one another. They are used in cases when source data is being loaded into the destination on a repeating basis, such as every night or throughout the day.
Benefits of Incremental Data Loads : They typically run considerably faster since they touch less data. Assuming no bottlenecks, the time to move and transform data is proportional to the amount of data being touched. If you touch half as much data, the run time is often reduced at a similar scale.
Disadvantages of Incremental Data Loads : Maintainability: With a full load, if there's an error you can re-run the entire load without having to do much else in the way of cleanup / preparation. With an incremental load, the files generally need to be loaded in order. So if you have a problem with one batch, others queue up behind it till you correct it.
TRUNCATING and then INSERTING is two operations whereas UPDATEing is one, making the TRUNCATE and INSERT take (theoretically) more time.
There's also the ease-of-use factor. If you TRUNCATE then INSERT, you have to manually keep track of every column value. If you UPDATE, you just need to know what you want to change.