What is the diff between Full table vs Delta table vs Incremental in dwh oracle

Question

I am trying to understand the concept Full Table vs Incremental Table vs Delta table and in principle in order to simplify (faster loading process) the daily ETL loads is it a good practice to use Incremental table

FULL TABLE

INCREMENTAL TABLE

DELTA TABLE

i have read some where

Using incremental loads to move data can shorten the run times of your ETL processes and reduce the risk when something goes wrong

can some one please help me understanding the concept ?

Answer 1

• full, as its name says, loads everything, the whole source data file
• incremental - or delta (those are synonyms, not two different types) - mean that you load only data which you haven't loaded yet. It usually means that time of the last loading has been recorded. The next loading session loads data created after the last successful loading timestamp

As of

• "shortening run times": obviously, if you don't have to load everything but just what's missing, it takes less time
• "reducing the risk": you don't mess up with data already loaded, it stays in the database. If something goes wrong, it goes wrong with current loading session so you can discard changes you've made and start over

Answer 2

Well you did not provide the reference for your quote, but in my experience it is only 50% correct.

I'd read it:

Using incremental loads to move data can shorten the run times of your ETL processes but increase the risk that something goes wrong.

The problem is in the error accumulation. If you get corrupt or incomplete data in full load you through them away on the next load and there is a good chance that the new load is valid.

On contrary with delta load the errors remain and can accumulate within the time.

Therefor is a good practice while implementing a delta load is to perform periodical check (daily, monthly etc.) that the complete snashot in the source and target are identical.

My rule of thumb is - choose delta load only if the full load is not feasible (i.e. for transaction tables and large dimensions).