Creating Snapshot tables with Incorta

A snapshot table holds the same transactional data as its source system, with additional fields for tracking the snapshot date. Snapshot tables are populated with those rows of data that are considered effective (i.e., current) in the source table at the time of the extract. We can capture a specific point in time – say inventory or how much they are owed in Accounts Receivable – so that they can refer back to it later. Then you can trend back and compare today vs. last week or last month. This information is very basic in business intelligence because we want to compare today vs. something.

17replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Nice article

    Like 2
    • Anurag Malik Nice article Thanks! Will you please let me clear little bit more.

      1. Create a new table and in the select clause add a new column called snapshot_dt with value of current date (sysdate for oracle) and create keys on snapshot_dt and the id field.
      for eg - select sysdate snapshot_dt, * from <table> 

      ==>> Here in this option where do I create new table in DB or in Incorta, and where do I add new column called snapshot_dt with the value of current date (sysdate for oracle)?

      2. Turn on the incremental option and copy the same sql into that.
      for eg - select B.week_end_date snapshot_dt, A.* from<table> A, Dates B where b.date = sysdate

      ==>> Please elaborate this.

      ** I want to store my per day's dashboard snapshot for a month, so in the next month I can easily get the no of count of student present per day in a class. 

      Please guide, your's suggestions were very much helpfull for me.

      Kind Regards


    • Like 1
  • Anurag Malik  thanks for your reply, it seems to be helpful. But here we are facing one issue when we gave  query  "select sysdate Snapshot_date, class_id,class_name from school_data;" and tried to save then we got the attached warning. Should we define a key in our query? It also says that on incremental load the new data will be added to existing data without any updates.

    Does it mean snap_shot data will be overridden? Could you please help us on this point.

    Thanks in advance.

  • Can we do this for data file also? Like I want to put all days/date updated data in Incorta as a Snapshot.  I tried it but Snapshot Load is not working at schema level. Please suggest.



    • Rudra How are you adding the sysdate ? Is it in the file column and that is defined as a key field ?

      Like 1
    • Amit Kothari yes, I created sysdate in file column and defined it as a key in Incorta

    • Amit Kothari  some more questions, please help.

      1. when I'm trying to load my schema with snapshot load, its not getting loaded. Will you please explain.? Should we make any changes to load snapshot data? 

      2. As snapshot load is not working I did incremental load and after that when I'm trying to make report in schema its showing only one data against every snapshot_date. Its not getting previous as well as todays data, only showing one data against every column in report. 

      Please help. 



    • Rudra Can we do a quick zoom? PM me your email address.

      Like 1
  • Make sure that the scheduled job for this is set to type Incremental

    Like 1
  • Amit Kothari  Thanks for helping us last week. We are able to do an incremental load of data and show the daily snapshot properly. The load is happening fine for last 1 week. Thanks again.

    Also wanted to know few things:

    1. Because of the daily incremental load , data keeps growing. What all would be the impacts of that. Will this impact the performance of the dashboard ?
    2. Is there any way to remove older data ? Suppose we decide a data retention period of 30 days, is there a way to remove data older than 30 days. Also how do we automate that so that after everyday incremental load, it still keeps only last 30 days data.

    • Rudra  Any chance the steps to accomplish this could be posted? We are trying to snapshot a data file as well with an Incremental Load and are running into problems.

      Like 1
    • Mark Samuels  - Can you send me a mail with the issue and we can have a zoom meeting?

    • Make sure that the keys are setup correctly.

      Like 1
    • Hi Mark Samuels  make snapshotdate column and the primary column in the table as a KEY function, and load type will be incremental. It will work.

      For more please please follow this video:


  • Thanks Rudra, Amit also called me and walked me through it!

    Like 1
  • Hi, I followed the video link provided above, but still I get and message "This table does not have a key Defined. If you run an incremental load the new data will be added to existing data". 

    From the table which I am creating the snapshot is provided by the IT team as the source data in Incorta and I see that table/view do not have Primary key. But in the query window I used "SELECT SYSDATE ........" 

    Do I need to have primary key on the base table , like SYSDATE should be my Primary Key column? Is SYSDATE return system datetime or it is user defined column?

Like3 Follow
  • 11 mths agoLast active
  • 17Replies
  • 787Views
  • 6 Following