SELECT all from 1 of 2 tables, based on the table with the most rows

Question

This is a bit of a weird question, but I will explain the problem first.

Every day we import a refreshed set of data in to a table called MARKETABLE_TABLE.

Sometimes there is an error with the generated data, and it has fewer records (say 400k instead of 800k).

Currently I am having to manually re-import the previous day's data when that happens, as having all the older data is better than having 60% of new data.

What I am trying to do:

I have a built 2 copies of the main data extension, MARKETABLE_TABLE_YESTERDAY and MARKETABLE_TABLE_TODAY.

After doing the import, I copy everything to the MARKETABLE_TABLE_YESTERDAY table. The next day, a new import happens to the MARKETABLE_TABLE_TODAY table.

I then want to have a query run that looks at the rowcounts for these 2 tables.

If MARKETABLE_TABLE_YESTERDAY > MARKETABLE_TABLE_TODAY, copy everything from MARKETABLE_TABLE_YESTERDAY in to MARKETABLE_TABLE and continue with the automation.

If MARKETABLE_TABLE_YESTERDAY <= MARKETABLE_TABLE_TODAY, copy everything from MARKETABLE_TABLE_TODAY in to MARKETABLE_TABLE and continue with the automation.

Is it even possible to have 2 different SELECT statements within an IF statement comparing the size of the 2 tables, with the query itself always writing to a single final table?

Thanks

Answer 1

My recommendation for this would be to instead use a SSJS script to determine if to retain the current TODAY table or to Import YESTERDAY again into the TODAY de. You would need a Script Activity, Validation Step, and a new DE.

You would utilize the AMPscript DataExtensionRowCount() function, but as Script Activities only allow SSJS, you will have to fudge it a bit to allow AMPscript in (unless you want to just put your AMPscript in a content block then call that block in via SSJS - either works.

<script runat=server>
Platform.Load("Core","1.1.1");

var rcToday = Platform.Function.TreatAsContent('%'+'%=DataExtensionRowCount("MARKETABLE_TABLE_TODAY")=%' + '%')

var rcYesterday = Platform.Function.TreatAsContent('%'+'%=DataExtensionRowCount("MARKETABLE_TABLE_YESTERDAY")=%' + '%')

if (rcYesterday > rcToday) {
       var rows = Platform.Function.UpsertData("DE_CHECK",["ID"],["MARKETABLE_TABLE"],["ReRun"],["1"]);
} else {
        var prox = new Script.Util.WSProxy();
        var action = "ClearData";
        var props = { CustomerKey: 'DE_CHECK' };
        var data = prox.performItem("DataExtension", props, action);
}

Then you put a validation step in the auto that looks at DE_CHECK and if it has a Rowcount > 1 then you continue, otherwise stop automation. Then have your final Step be a SQL Query to move YESTERDAY back into and overwrite TODAY.

Answer 2

There may be better approaches, this one may help you.

1. Create 2 child automatons as below,

   • One with only one SQL activity to move data from MARKETABLE_TABLE_YESTERDAY in to MARKETABLE_TABLE
   • Second automation with only one SQL activity to move data from MARKETABLE_TABLE_TODAY to MARKETABLE_TABLE

2. Use the below in your Script activity to run Respective automation as per your above logic

<script runat="server">
    Platform.Load("core", "1");
    var scr = "";
        scr += "\%\%[";
        scr += "If DataExtensionRowCount('MARKETABLE_TABLE_YESTERDAY') > 
DataExtensionRowCount('MARKETABLE_TABLE_TODAY') THEN";
        scr += "]\%\%";
    Write(TreatAsContent(scr));
</script>

<script  runat="server">
    Platform.Load("core","1.1.1");
    var prox = new Script.Util.WSProxy();
    //Change the MID to suit.
    prox.setClientId({ "ID": xxxxxxxx });

    // Need to retrieve the ObjectID for the Automation, which isn't available via the SFMC interface.
    var cols = ["Name", "ProgramID", "IsActive"];
    var filter = {
        Property: "Name",
        SimpleOperator: "equals",
        Value: "xxx Name of your Automation to add MARKETABLE_TABLE_YESTERDAY xxx"
    };
    var desc = prox.retrieve("Automation", cols, filter);
    var props = { ObjectID: desc.Results[0].ObjectID};
    var action = "start";
    var opts = {};
    var res = prox.performItem("Automation", props, action, opts);
    var status = res.Status;
    var statusCode = res.Results[0].StatusCode;
    Write("status code: " + statusCode);
</script>

<script runat="server">
    Platform.Load("core", "1");
    var scr = "";
        scr += "\%\%[";
        scr += "ELSE";
        scr += "]\%\%";
    Write(TreatAsContent(scr));
</script>
<script  runat="server">
    Platform.Load("core","1.1.1");
    var prox = new Script.Util.WSProxy();

    //Change the MID to suit.
    prox.setClientId({ "ID": xxxxxxxx });

    // Need to retrieve the ObjectID for the Automation, which isn't available via the SFMC interface.
    var cols = ["Name", "ProgramID", "IsActive"];
    var filter = {
        Property: "Name",
        SimpleOperator: "equals",
        Value: "xxx Name of your Automation to add MARKETABLE_TABLE_TODAY xxx"
    };
    var desc = prox.retrieve("Automation", cols, filter);
    var props = {ObjectID: desc.Results[0].ObjectID};
    var action = "start";
    var opts = {};
    var res = prox.performItem("Automation", props, action, opts);
    var status = res.Status;
    var statusCode = res.Results[0].StatusCode;

    Write("status code: " + statusCode);
</script>       

<script runat="server">
    Platform.Load("core", "1");
    var scr = "";
        scr += "\%\%[";
        scr += "ENDIF";
        scr += "]\%\%";
    Write(TreatAsContent(scr));
</script>

Thanks to @Greg for reminding that AMPscript cant be directly used in SSJS activity. Also thanks to Ivan for this useful article https://ampscript.xyz/how-tos/use-ampscript-to-enhance-ssjs-in-sfmc/

Answer 3

I think you can do this all in a single query without the overhead of a Script Activity. Perhaps a query like this.

select
  both.id
, both.DE /* for debugging, remove this in final version */
, deCounts.todayCount /* for debugging, remove this in final version */
, deCounts.yesterdayCount /* for debugging, remove this in final version */
from (

  select 
  t.id
  , 'today' DE
  from MARKETABLE_TABLE_TODAY t

  union

  select 
  y.id
  , 'yesterday' DE
  from MARKETABLE_TABLE_YESTERDAY y

) both
outer apply (

  select 
    max(x.todayCount) todayCount
  , max(x.yesterdayCount) yesterdayCount
  from (

    select 
    count(*) todayCount
    , 0 yesterdayCount
    from MARKETABLE_TABLE_TODAY 

    union

    select 
    0 todayCount
    , count(*) yesterdayCount
    from MARKETABLE_TABLE_YESTERDAY 

  ) x

) DECounts
where 
  case
    when deCounts.todayCount >= deCounts.yesterdayCount then 'today' 
    else 'yesterday'
  end = both.DE

The outer apply combines the counts of both tables. The outermost query combines rows from today and yesterday and tags them with today or yesterday. The case statement in the where clause picks which set from the both result (based on the counts) to return.

I'd suggest experimenting with the query using this dbfiddle: https://dbfiddle.uk/w4Pl4rET