Keep only 7 days of data: memo field not recognized?

[ogonzalesdiaz]

I've a tablet with data from 2 years ago, up to today. I've been tasked to remove all records, except for the last 7 days.

So my approache is: 

1.- Create table (toy table to store last 7 days records).
2.- Query the original table, lets call it "A", to filter all records based on the created column that were created on 21/11 and after. 
3.- Use an Update Activity to copy those records to toy table "abc", let's called it "B" .
4.- Truncate A, just to keep the headers.
5.- Copy data from B back to A, so it only has the last 7 days records.

This is the original XML schema from A:

<?xml version='1.0'?>
<schema _cs="Pipeline Event (acx)" created="2020-11-23 18:03:44.362Z" createdBy-id="1055"
        desc="Trigger data coming from Analytics through pipeline" entitySchema="xtk:schema"
        img="" implements="" label="Pipeline Event" lastModified="2023-11-14 13:48:28.230Z"
        mappingType="sql" md5="860DFE30574D2300D59CC3EC7B0FFF63" modifiedBy-id="2762680"
        name="pipelineEvent" namespace="acx" xtkschema="xtk:schema">
  <element autopk="true" desc="Trigger data coming from Analytics through pipeline"
           label="Pipeline Events" name="pipelineEvent" pkSequence="acxPipelineEventId"
           sqltable="AcxPipelineEvent">
    <compute-string expr="@id"/>
    <dbindex name="declared_id">
      <keyfield xpath="@declared_id"/>
    </dbindex>
    <dbindex name="timeGMT">
      <keyfield xpath="@timeGMT"/>
    </dbindex>
    <dbindex name="created">
      <keyfield xpath="@created"/>
    </dbindex>
    <dbindex name="id" unique="true">
      <keyfield xpath="@id"/>
    </dbindex>
    <key internal="true" name="id">
      <keyfield xpath="@id"/>
    </key>
    <dbindex name="recipientId">
      <keyfield xpath="@recipient-id"/>
    </dbindex>
    <attribute desc="Internal primary key" label="Primary key" name="id" sqlname="iPipelineEventId"
               type="long"/>
    <attribute desc="time the event was created in Campaign" label="Creation date"
               name="created" sqlDefault="GetDate()" sqlname="tsCreated" type="datetime"/>
    <attribute desc="last the event was modified in Campaign" label="Modification date"
               name="lastModified" sqlname="tsLastModified" type="datetime"/>
    <attribute desc="time of the original event, coming from Analytics, in the timeGMT attribute"
               label="timeGMT" name="timeGMT" sqlname="tsTimeGMT" type="datetime"/>
    <attribute desc="customer identifier, coming from Analytics, in a eVar" label="declared_id"
               length="32" name="declared_id" sqlname="sDeclared_id" type="string"/>
    <attribute desc="type of trigger, coming from the pipeline, in the triggerId attribute"
               length="50" name="triggerType" sqlname="sTriggerType" type="string"/>
    <attribute advanced="true" desc="full payload of the pipeline event" label="Trigger Data"
               name="data" sqlname="mData" type="memo"/>
    <element label="Recipient" name="recipient" pkgStatus="never" revCardinality="single"
             revLink="pipelineEvent" target="nms:recipient" type="link">
      <join xpath-dst="@id" xpath-src="@recipient-id"/>
    </element>
    <attribute advanced="true" label="Foreign key of the link 'Recipient' (field 'id')"
               name="recipient-id" sqlname="iRecipientId" type="long"/>
    <element advanced="true" integrity="neutral" label="Current operator" name="currentOperator"
             revLink="_NONE_" target="xtk:operator" type="link">
      <join xpath-dst="@id" xpath-src="$(loginId)"/>
    </element>
    <element integrity="neutral" label="Delivery being analyzed" name="currentDelivery"
             revLink="_NONE_" target="nms:delivery" type="link" visibleIf="NodeValue('/ignored/@currentDeliveryId')!=''">
      <join xpath-dst="@id" xpath-src="$long(@currentDeliveryId)"/>
    </element>
    <element applicableIf="HasPackage('nms:interaction')" integrity="neutral" label="Offer being processed"
             name="currentOffer" revLink="_NONE_" target="nms:offer" type="link"
             visibleIf="NodeValue('/ignored/@currentOfferId')!=''">
      <join xpath-dst="@id" xpath-src="$long(@currentOfferId)"/>
    </element>
    <element applicableIf="HasPackage('nms:centralLocal') and (@namespace!='nms' or @name!='delivery')"
             integrity="neutral" label="Current order" name="currentOrder" revLink="_NONE_"
             target="nms:localOrder" type="link" visibleIf="NodeValue('/ignored/@currentOrderId')!=''">
      <join xpath-dst="@id" xpath-src="$long(@currentOrderId)"/>
    </element>
  </element>
</schema>

Table B: XML: I just created the fields not the indexes since I don't need those... I think.

<srcSchema _cs="abc (cus)" created="2023-11-29 04:36:16.768Z" createdBy-id="0" entitySchema="xtk:srcSchema"
           img="xtk:schema.png" label="abc" lastModified="2023-11-29 04:52:48.015Z"
           mappingType="sql" md5="E3213537CADFEE06FE5232BE532B2A35" modifiedBy-id="0"
           name="abc" namespace="cus" xtkschema="xtk:srcSchema">
  <createdBy _cs="Omar Gonzales (ogonzales)"/>
  <modifiedBy _cs="Omar Gonzales (ogonzales)"/>
  <element autopk="true" label="abc" name="abc" pkSequence="cusAbcId">
    <attribute desc="time the event was created in Campaign" label="Creation date"
               name="created" sqlDefault="GetDate()" type="datetime"/>
    <attribute desc="last the event was modified in Campaign" label="Modification date"
               name="lastModified" type="datetime"/>
    <attribute desc="time of the original event, coming from Analytics, in the timeGMT attribute"
               label="timeGMT" name="timeGMT" type="datetime"/>
    <attribute desc="customer identifier, coming from Analytics, in a eVar" label="declared_id"
               length="32" name="declared_id" type="string"/>
    <attribute desc="type of trigger, coming from the pipeline, in the triggerId attribute"
               length="50" name="triggerType" type="string"/>
    <attribute advanced="true" desc="full payload of the pipeline event" label="Trigger Data"
               name="data" type="memo"/>
  </element>
</srcSchema>

However, in my Update activity for the workflow that will query A and keep only 7 days results, then update those to B... I cannot use the memo type column called: "Trigger Data @data)"... even when I see it in the structure of B (and of course A), I don't see it in the Update activity, not in the Destination field or the Source field. 

Why?

[_Manoj_Kumar_ · Accepted solution]

Hello @ogonzalesdiaz 

Here is a simple way of doing this:

In the query use the condition (this will give you all the records created before 7 days)

Created on or before Current date -7 days

Then use the update data activity and select the operation Delete.

Make sure the Targeting Dimesion is same in both activities.

[ogonzalesdiaz]

Hi @_Manoj_Kumar_  I did do this becayse there are 140 M of records to delete. And according to DB best practices, deleting that amount of records can cause other issues, like problems with indexing, etc. 

Is this not the case with Adobe Campaign?

[_Manoj_Kumar_]

Hello @ogonzalesdiaz  No, there won't be any issue.

Also, check the "Pipeline triggers purge" which does the same thing.

[ParthaSarathy]

Hi @ogonzalesdiaz ,

For your use case, Instead of creating new schema and updating records, you can try and consider the below approach which will be a best solution,

Create a workflow:

Scheduler > Query > update data

Scheduler: schedule the workflow to run daily once

Query: Select Targeting and filtering dimension as table_A (where you want to delete older records and keep only 7 days record)

Filtering condition:

DateOnly(@created) on or before DateOnly(DaysAgo(7))

Update data:

Select operation as delete

Schema: Table_A

Reconciliation: Reconcile with Primary key

Now when the workflow runs daily, it will delete older records and keep only last 7 days record in it.

[ogonzalesdiaz]

Hi @ParthaSarathy I did do this becayse there are 140 M of records to delete. And according to DB best practices, deleting that amount of records can cause other issues, like problems with indexing, etc. 

Is this not the case with Adobe Campaign?

[ParthaSarathy]

@ogonzalesdiaz , There won't be a problem. And however you have planned to truncate all records from Table_A. So, with the suggested approach, only for the first run, there will be huge data deletion will happen. And from the next scheduled run, only few data will get deleted.

[LakshmiPravallika]

Hi @ogonzalesdiaz ,

If you see an issue with DB Size after deleting this huge Data, please try dropping and  recreating the index using SQL Code activity or when you modify the Schema you can get the Script in the Update Database Structure, so that you will not be facing any issue.

DROP INDEX {DBIndexSQLName};

CREATE INDEX {newIndexName} ON {sqlTableName}(sqlColnames);

Regards,

Pravallika.

[ogonzalesdiaz]

Hi @LakshmiPravallika ,

so according to the XML, this SQL would be:

DROP INDEX created; // or DROP INDEX AcxpipelineEvent created;

CREATE INDEX created ON pipelineEvent(sqlColnames);

What would be the specific command to targed the field created in pipelineEvent table?

[LakshmiPravallika]

Hi @ogonzalesdiaz ,

You can try to get the Script by removing the Index in the Edit Tab first and then Update the Database structure, in this Tab the Script for deletion of index gets automatically generated.

After executing it, Please add the index again in the Edit Tab of the Schema and then Update the Database Structure so that it will be recreated.

Regards,

Pravallika.

[Craig_Thonis]

Hi ogonzalesdiaz,

Can you check that your advanced fields are enabled in the update activity (click the button with the three dots in the lower right-hand corner of the window in your screenshot)? Specific areas will be hidden unless that has been checked, and it appears to be contained in one screenshot but not the other.

[Sukrity_Wadhwa]

Hi @ogonzalesdiaz,

Were you able to resolve this query with the help of the given solutions or do you still need more help here? Do let us know. In case the given solutions were helpful, then kindly choose the one that helped you the most as the 'Correct Reply'.
Thanks!