Data Snapshot

damen
damen Contributor
edited May 2023 in Magic ETL

Ok so we have a dataset in one of our databases that periodically gets updated.

Every so often, we have new data that gets added AND some of the old data gets modified. (Sometimes we have loans that get modified and we need to keep the original 'value')

I am trying to create a snapshot of the original data - on a quarterly basis and feed that into the dataflow that is used to create visualizations

Am I supposed to be doing a recursive dataflow?

An example:

image.png

In this case loan_id 125 isn't new but it was modified and for the purposes of this dataflow, we don't want the new value ($126) but the original ($125)

What I am hoping to do is create a table that can keep track of all the original 'value' entries- on a quarterly basis.

I keep looking back towards a recursive data flow but the other problem is that when a loan gets modified, there aren't two entries of 'loan_ids' that match and the original record is lost.

What is the easiest way to capture an original record, store it in a database, and make sure the dataflow knows to use the "master file" and to never modify the old records?

If this helps, feel free to agree, accept or awesome it!

Tagged:

Best Answers

  • trafalger
    trafalger Coach
    Answer ✓

    Use the "Dataset copy" connector, take a copy of the original dataset and append it to your snapshot dataset on a quarterly basis. From there you can make an ETL that marks the most recent version of the data as the most recent and use the snapshot date to view history of the date.

  • MichelleH
    MichelleH Coach
    Answer ✓

    @damen Yes, you can use a recursive dataflow to create your master "snapshot" data. The way you would do this is to add a column to your input data with the current date, then append to the existing output dataset. Here is a KB article about how to set up a recursive dataflow: https://domo-support.domo.com/s/article/360057087393?language=en_US

    As for your question about duplicate entries, you can create a second dataflow that runs the snapshot data through a Group By tile to show the original value. Do not do this in the original recursive dataflow since you will lose all the detail data.

Answers

  • trafalger
    trafalger Coach
    Answer ✓

    Use the "Dataset copy" connector, take a copy of the original dataset and append it to your snapshot dataset on a quarterly basis. From there you can make an ETL that marks the most recent version of the data as the most recent and use the snapshot date to view history of the date.

  • MichelleH
    MichelleH Coach
    Answer ✓

    @damen Yes, you can use a recursive dataflow to create your master "snapshot" data. The way you would do this is to add a column to your input data with the current date, then append to the existing output dataset. Here is a KB article about how to set up a recursive dataflow: https://domo-support.domo.com/s/article/360057087393?language=en_US

    As for your question about duplicate entries, you can create a second dataflow that runs the snapshot data through a Group By tile to show the original value. Do not do this in the original recursive dataflow since you will lose all the detail data.

  • damen
    damen Contributor

    @trafalger @MichelleH

    Thank you both for the suggestions. I will take a look at both and let you know what I found.

    If this helps, feel free to agree, accept or awesome it!

Categories