Snapshot, Freeze, or Lock your data

The Smart Recursive Dataflow sounds like the answer. 

Smart Recursive – What is it and How to create it in Domo

What is the Smart Recursive Data flow? -  The “Regular” recursive data flow in Domo will add data from any data base in increments rather than replace the entire data source.  Often, we do this when we do not have the ability or access to a full data base or the data base is large and for efficiency purposes we need to bring the data in segments rather than bulk.

The Smart Recursive is a way to empower the end user to lock in and choose the increments being loaded into the recursive dataset.   The process will create a snapshot of the data based on specific dimensions that want to be locked down.  This will preserve the data from being changed unless the individual with access to the control data set form authorizes the change.

To set up the Smart Recursive – Choose the dimensions you want to freeze or lock.   The example I am using in this information is month and region, but this could be as detailed as individual and day.  Note – The more detailed of rows you want to lock down the more detailed and the more columns and rows you need in your control data set or web form.

Once your data source and dimensions are chosen you can set up your control data set.  For the control data, you can use any data set that allows the ability to manually enter data, such as Excel, Google sheets, or Domo web forms. 

I recommend using Domo web forms if you do not need have a large number of columns you need to lock.  The advantage to Web forms is they will update as soon as you update them. 

Create a web form in Domo with all the dimension columns you plan to use with the smart recursive and a column to use to “Lock” the data.

Create a new data flow in MySQL.  Bring in the data source you will want to lock.  You need to create a base data source before adding the code to the recursive so that you can bring it back in to it the data flow.

Run the MySQL with one transform as follows:

SELECT

*,'Yes' as `Lock`, CURRENT_TIMESTAMP as Date

FROM

base_data_source

You will need to reference the transform in the output. For this example we are naming our data source smart_recursive_dataflow. Note that I have added two additional field into the source. One will show the lock the other will be the time stamp of when it will be locked.  These fields help select the correct data to lock or delete.

Once the data flows run, edit the current data flow and you will add the data flow source you just run back into the same data flow and at this time you will need to add your control data set to the inputs as well.

Modify the previous transform to create the adding portion of the data flow

SELECT

*,'Yes' as `Lock`, CURRENT_TIMESTAMP as Date

FROM

base_data_source

where concat(region,`Month`) in (select concat(region,`Month`) as link from control_data_set where `Lock` is not null)

and concat(region,`Month`) not in (select distinct concat(region,`Month`) as link from smart_recursive_dataflow)

UNION

SELECT

*

FROM

smart_recursive_dataflow Where `Lock` = 'Yes'

Name the transform – recursive_add – modify the fields in the concat() and separate them with a comma.  These are the fields that you will reference in all of your input fields.

Create a second transform in your data flow and use the flowing code:

SELECT

d.*

from

recursive_add d

left join

(SELECT

a.link

from

(SELECT

Concat(Region,`Month`) as link

from

smart_recursive_dataflow) a

Inner Join

(select concat(Region, `Month`) as link from control_data_set where `Lock` is null) b on a. link = b.link) c on concat(d.Region,d.Month) = c.Link

where link is null

Once again modify the concat() to the fields that you want to lock, the dimensions you used in your control data source. 

Name your new transform – delete_data

Apply the changes then update your Output data set to reference the delete_data transform. 

You are at point that you can now run your data set.  Modify the settings so that the data will automatically run with the updating of your web form and/or the source data. 

Update the control data set with a ‘Y’ or any other character in the “Lock” column to lock the data.  If you need to reset the “Locked” data.  Make sure the column is null or blank when running the data set and process will remove the data.  If you want to take a new snap shot of the data, place a ‘Y’ or any other character in the “Locked” column and run the data set and the data you selected will be added.