1 Reply Latest reply on Dec 4, 2018 7:14 AM by Chainalytics FMIC

    Extract Incremental refresh based on data not rows

    Dwayne West

      I'm looking for a way to have an extract do an incremental refresh if a value changes in the results, not just rows.

       

      Basically, my extract is a custom SQL which Counts records with the same transaction_date.

      SELECT transaction_date, count(*) as my_count

           FROM my_data

           GROUP BY transaction_date

       

      It seems, when the incremental refresh runs Daily at 3:00am, I have a new 'row' for that day with 5 records. However, by the end of the day, I should have 10+ records for that row.

      Since the 'rows' have not increased, the incremental refresh is missing updates to my count.

       

      My results look like this;

      2018-10-15 10

      2018-10-16 11

      2018-10-17 12

      2018-10-18 5

      2018-10-19 5

      2018-10-20 5

       

      When they should look like this:

      2018-10-15 10

      2018-10-16 11

      2018-10-17 12

      2018-10-18 13

      2018-10-19 14

      2018-10-20 15

       

      There are actually billions of records in my database each day, so a Full Refresh takes a very long time.
      The data is also a rolling 30 days, so if I do Full Refresh, I lose anything from the extract older than 30 days, which I prefer to keep the counts in tableau.

       

       

      Thanks,

      Allen