How to do incremental loading without date filters

Question

How to do incremental loading without date filters

upendar.dilytics

Mr or Mrs. 500

Points: 529
More actions
August 22, 2017 at 6:39 am

#345879

We need to load the data which is inserted or updated in last four days in a source to staging and then to fact by incremental loading by using SSIS packages, but the problem is we don't have any date columns in source or fact tables.
Source to stage is full load and stage to fact is an incremental load. How can we achieve this?
Any suggestions regarding this issue will be greatly appreciated.
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

upendar.dilytics - Tuesday, August 22, 2017 6:39 AM
We need to load the data which is inserted or updated in last four days in a source to staging and then to fact by incremental loading by using SSIS packages, but the problem is we don't have any date columns in source or fact tables.
Source to stage is full load and stage to fact is an incremental load. How can we achieve this?
Any suggestions regarding this issue will be greatly appreciated.
Thanks in advance.

do you have an incrementing identity key?...

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Ant-Green SSC Guru Points: 113445 More actions · Answer 2

Will Fact once loaded from stage using the 4 day method be a full load of data as like stage. So once the process is complete both stage and fact would be identical?

If so you could look at a MERGE statement to only load in non matched items.

Otherwise you will need to build in some form of date routines into your source data so that you know its a new record.

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 3

What is the main problem here? Not able to identify what to pull from the source or incrementally loading from source or both?

If you can identify the records that have changed and just need a way to incrementally load them from staging to fact, then just add a row number when exporting from source to staging and load the data incrementally from there? You can also export the source data across multiple files or export to one big file and do another process that partitions that one file into multiple files. Then load from source to staging incrementally as opposed to a full load.

Plenty of ways I think.

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 4

upendar.dilytics - Tuesday, August 22, 2017 6:39 AM
We need to load the data which is inserted or updated in last four days in a source to staging and then to fact by incremental loading by using SSIS packages, but the problem is we don't have any date columns in source or fact tables.
Source to stage is full load and stage to fact is an incremental load. How can we achieve this?
Any suggestions regarding this issue will be greatly appreciated.
Thanks in advance.

What is/are the PK column(s) of the Fact table and does that information appear in the source tables?

Also, if the data doesn't have some date/time column and the source tables aren't named with some sort of temporal indicator and there's no ever increasing integer value in all 4 tables, there might still be a way to pull this off.. IF the source tables where created in the correct temporal order for the incremental data, then you could use the table creation date to drive the correct order of the incremental load.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100950 More actions · Answer 5

First I'd suggest looking into "Change Tracking". That would accurately identity any/all rows that have been modified since a certain date, and it wouldn't limit you to 4 days, you could use any other time period in the future if you wanted/needed to.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.