Loading table. delta loading instead of full loading

[Nasser_DRMCP]

data source ;

length project_name $ 9;

input num_client project_name $ project_amount date_load ;

informat date_load ddmmyy10.;

format date_load ddmmyy10.;

datalines;

280001 home 15000 01/04/2019

280002 car 3000 01/05/2019

280003 personnal 2250 30/06/2019

280004 home 25000 31/07/2020

280005 car 12000 21/01/2021

280006 personnal 1500 20/02/2021

280007 home 75500 12/04/2021

280008 car 42000 27/05/2021

280009 personnal 2000 05/09/2021

280010 home 2000 10/10/2021

;

run;

sorry,

this is a new code with only one column "date_load"

And my objectif is to detect the last (max) date_load of the target wich named  "max_date_load_target"

And then, I should extract from source only rows where  date_load is greater then" max_date_load_target"

so, I would like to know the best (optimized) way to detecte the last (max) "date_load target" 

[Kurt_Bremser · Accepted solution]

So you only need

proc sql noprint;
select max(load_date) into :max_load
from target;
quit;

data target_new;
set
  target
  source (where=(load_date gt &max_load))
;
run;

[Patrick]

A column update_date indicates that a delta load would need to be update and insert at least.

If there could also be deletes (observations deleted in source) then you would also need to derive the deletions by comparing source to target.

To really have an opinion of what's "best" we would need to understand your data model better and also how much change there is (like 1% or 50% per run).

Let's say your source data model is a star. In such a case all new rows in a fact table would become inserts and pre-existing rows in the fact table that link to a dimension that has changed would become updates. 

Fact records don't get deleted but there is of course always the possibility that old records get archived (=removed from the fact table) or that a roll-back is required after a load of wrong data. And you're then on the receiving end of this and must design and build for such cases as well.

If the dimension tables are not too big then re-creating the target data mart table using a single data step with hash lookups to the dimension tables could perform reasonably well and is much easier to implement and maintain.

Your use case might be something very different. Above only to demonstrate why one needs to understand the "have" details for making the right call.