Watermark for an Integration Wizard data source query

Products

• Information Centric Analytics
• Data Loss Prevention Core Package

Issue/Introduction

What is a Watermark for an Integration Wizard data source query?  What are the best practices when defining a watermark? 

Environment

CAUTION: These commands assume a basic understanding of SQL. Any changes to the Database pose the potential to affect the operation of the product. ALWAYS ENSURE YOU HAVE A BACKUP OF YOUR DATABASE BEFORE MAKING CHANGES.

In order for Symantec to provide full support of the schema and the DB environment, you have to be running the DB in the version specific to the used version as outlined in the System Requirements Guide. Please see the limitations section at http://www.symantec.com/docs/TECH220433

Resolution

1. A Watermark within Information Centric Analytics (ICA) will determine how far back in time a data source query will look back when it executes against the Source System.  The user will be allowed to select a query column as defined in the Query Details.  When defining the watermark column, the column should either be a date-based column, or it should be an Integer based column.  When loading data through ICA’s integration wizard it will track the latest value defined for the column using the SQL Server Max() function.  The next time the data source query executes ICA will use the Watermark Value to determine where the previous query left off and it will only bring back records greater than the last known watermarked date or integer field.  Since it is performing this comparison it is much more effective to use either an integer or a date field.  

 

_{Figure 1 This figure depicts where one would define the watermark for a data source query}

2. When no watermark column is defined for an SQL based data source query ICA will truncate all data in the staging table, it will pull back all data defined within the SQL statement and it will reload the staging table with the results from the query.  It differs from a data source query using a watermark in that the data source query that leverages a watermark does not get truncated and reloaded on a nightly basis, it will instead keep appending to the staging table on a nightly basis. 

 

_{Figure 2 This statement depicts a sample process with no Watermark defined, data will be truncated and reloaded nightly}

_{Figure 3 This statement depicts a sample process with a Watermark defined, data will not be truncated from stage, ICA will only add data to the staging table}