Delete Processing

Inactive records need to be deleted from offline devices. This allows storing only active data sets in the tables and views of the MyInsights layer.

There are special tables in Nitro to handle these deleted records.

Creating a Job for Deleted Records

A metadata package needs to be created to store the deleted records in a table.

  1. Create a task sequence in the taskSequences directory of the package to invoke the tasks to create the tables named <tableName>_active_delete to store the deleted records.
  2. Add the tables to active the logic to the task sequence:
  • name - set to tk_crm_rs_recreate_delete_obj__v.sql
  • <context section>
  • objectName- name of the object defined in HTML(MyInsights) .yml.
  • tableName - name of the table defined in HTML(MyInsights) . yml.
  • partitionKey - name of the partition key defined in HTML(MyInsights) . yml.

Tables with crm_user_id__v are supported in the logic.

Example

Copy 
ts_crm_recreate_delete_object__c.yml
- name : tk_crm_rs_recreate_delete_obj__v.sql
context :
objectName : fact_trx_trend_html__ctable
Name : fact_trx_trend__c
partitionKey : crm_user_id__v
  1. Create a job in the jobs directory of the package to invoke the task sequence from the previous step.
  2. Zip the task, tasksequences, and the jobs sub-directories. This package is used to deploy the connector.

Creating a Job to Load Data to Nitro Cache Server

Creating a metadata package involves the jobs to store the deleted records in the special tables. When loading the data to the Nitro cache server, the enhanced logic merges the records from the special table into the table. The enhanced logic also alters the delete_datetime__v column in the table in the cache server automatically. The active records always have a null value in the column. The inactive(deleted) records always have a deleted date time in the column.

  1. Create a task sequence in the taskSequences directory of the package to invoke the enhanced tasks to store the deleted records in the tables.
  2. Change the context parameters but do not change the tasks section:
  • objectName - name of the object defined in HTML(MyInsights) .yml
  • tableName - name of the table defined in HTML(MyInsights) . yml
  • partitionKey - name of the partition key defined in HTML(MyInsights) . yml

Example

Copy 
ts_fact_trx_trend_rs_hive_enhanced_delete__c.yml
context:
objectName : fact_trx_trend_html__c
tableName : fact_trx_trend__c
partitionKey : crm_user_id__v
tasks:
- tk_crm_sysdate.sqlv
- tk_crm_purgedate.sqlv
- tk_crm_cleanup_s3_redshift.script
- tk_crm_presto_delete_obj_ins__v.sql
- name : tk_crm_cache_ddl_presto_delete_obj__v.sql
platform : CACHEDB
- name : tk_crm_cache_ddl_redshift.sql
platform : CACHEDB
- tk_crm_rs_to_s3_unld_delete_obj__v.sql
- name : tk_crm_s3_to_hive_ins_delete_obj__v.sql
platform : CACHEDB
- name : tk_crm_presto_rename.sql
platform : CACHEDB
- tk_crm_cleanup_s3_hive.script
  1. Create a job to invoke the task sequence in the previous step.
  2. Zip the tasks, tasksequences, and the jobs sub-directories. This is used to deploy the connector.

Configuring the Sync Profile

  1. Configure a .yml file in the syncProfiles directory of the package to filter sync data from the table.

Example

Copy 
name: fact_trx_trend_HTML__c
tableName: fact_trx_trend__c
profileId: 1
profileName: Rep
incrementalFilter: "Where crm_user_id__v = @@VOD_SF_USER_ID@@ and from_iso8601_timestamp(effective_last_modified_date__v) > from_iso8601_timestamp(@@LAST_MODIFIED_DATE@@) and NULLIF(delete_datetime__v,'') is Null"
activeIncrementalFilter: "Where crm_user_id__v = @@VOD_SF_USER_ID@@ and from_iso8601_timestamp(effective_last_modified_date__v) > from_iso8601_timestamp(@@LAST_MODIFIED_DATE@@) and NULLIF(effective_end_date__v, '') is Null and NULLIF(delete_datetime__v,'') is Null"
inactiveIncrementalFilter: "Where crm_user_id__v = @@VOD_SF_USER_ID@@ and NULLIF(delete_datetime__v,'') is not Null and from_iso8601_timestamp(delete_datetime__v) > from_iso8601_timestamp(@@LAST_MODIFIED_DATE@@) "
fullRefreshFilter: "Where crm_user_id__v = @@VOD_SF_USER_ID@@ and NULLIF(effective_end_date__v, '') is Null and NULLIF(delete_datetime__v,'') is Null"
  1. Zip the syncProfiles sub-directory. This is used to deploy the connector.

Uploading the Metadata Package

Deploying the metadata package involves uploading the zipped file created to your connector. This is done using the CLI client.