Historical Database I inherited

user084398

Hi all.

 

I'm dealing with CRM data, and have a snapshot of all of our data that is appended to a full historical daily database. This is done in a SQL transform, but I am completely unable to figure out why the input data is being appended to the output instead of clearing it. I am completely dumbfounded. I'm attaching screenshots of the SQL transform, but basically I have an input, a simple transform that selects a bunch of columns and adds a date stamp, and the output. The input data is around 53k rows daily, and the historical dataset is around 18m at this time. I'm also attaching a screenshot of the run history of the dataflow, where it shows clearly that the input is 53k and the output is also 53k.

 

I inherited this dataflow from someone that left my company. I would greatly appreciate the help

 

Thanks

 

HistoryInput and OutputOutputTransform2Transform1SQL transform

DDalt

Hi @user084398 

 

If your input data always displays the most up-to-date or "current" view of the data then the benefit of the data flow that you inherited is that you are able to see how certain measurements are changing over time.

 

Let's say you want to show how "Total Forecast Amount" has changed over time. By snapshotting/appending the data each day you will be able to create a card that uses SUM(`Total Forecast Amount`) graphed over time using the `Date Stamp` date. Because the `Date Stamp` coumn displays the "snapshot date" you will be able show the Total Forecast Amount for every single day that the dataflow ran.

 

So while your input data set will allow you to answer the question "What is our Total Forecast Amount?" your output data set will allow you to answer questions like "What was our Total Forecast Amount yesterday? What about the day before?"

 

 

DDalt

Re-reading your post, I realize that you might also be wondering how, from a technical standpoint, the data flow is working. Is that what you were actually wondering about?

 

If so, I think I would also be confused. If the transform for "shapshot_table" is a simple SELECT statement and there is no more code after the FROM line in line 53 then I would also be confused how this data flow is producing an output that is larger than the input.

 

I feel like at one point there was some kind of "Append" method feature that DOMO implemented on data flows... for example this link: https://knowledge.domo.com/Prepare/Magic_Transforms/SQL_DataFlows/01Creating_an_SQL_DataFlow discusses some feature about appending data using the Configure button under 5 C) (2.). If no one else is able to help you, I'm sure DOMO support can help figure out how that simple transform is growing the Output data set

 

 

 

user084398

Hi @DDalt! Yes, that's exactly it, I need to understand why the flow is APPENDING instead of REPLACING! I understand that having all the historical information is important, but I want to know how the thing works! I see the link you sent me and it sounds exactly like what I'm looking for, some obscure configuration outside of the actual transformation code. The problem is that I can't find those config settings in my instance. Could it be that this particular dataflow was created with those settings back then, and they have been phased out since, but the old dataflow still works?

DDalt

@user084398 I really think it might be that it was created with settings that have been phased out. That concept of being able to append easily inside of a data flow seems so familiar, but I can't find a way to replicate it. 

 

I think you can feel comfortable reaching out to DOMO Support with a link to the data flow and ask if there is an old Append setting that is allowing that data flow to run. You can also ask if they foresee any risk of that data flow not running as expected in the future so that you can figure out if you need to take action.