Integration database tables, ETL, and partitioning

The Campaign integration with Engage populates database tables that Campaign uses for auditing and tracking. Consult with your database administrator to discuss how long you need to keep the data for querying. Depending on the volume of activity for your account, the tables can grow large over time.

Each integration table shares some characteristics.

  • The primary key is an identity or sequence column. The IDs in the primary keys reflect the order in which rows were inserted.
  • The tables have a datetime/timestamp column to indicate the time at which a particular event happened.
  • The rows in each table are inserted once and the integration does not update them after the initial insert.
  • There are no predefined indexes, foreign keys, or check constraints other than the primary key.

If you are not using recipient email address as the audience level in Unica Campaign, you can add one or more columns to the tracking tables. However, your data must include a way to look up audience level for any contact. You must configure the integration to download the values for those columns from your Engage database. When you add columns, do not use unique indexes or constraints because you might prevent data from being inserted.

The integration does not automatically purge or archive the tables. Your administrator can schedule archiving or purging of the data. A typical purging scheme might set up range partitioning on the datetime/timestamp field, with partitions for each month or quarter. The purging plan can drop partitions when they become outdated. However, different database capabilities and performance characteristics can affect your strategy for partitioning and purging of data. How you query the data might also affect your strategy.

As the Engage ETL runs on the Unica Campaign web application, a cluster user can control the Engage ETL execution on individual nodes by specifying the JVM option -Dengage.etl.disabled=true. If -Dengage.etl.disabled=true, Engage ETL does not work on the specified cluster node.