Setup Validate against DB for Custom Database Targets

The new Validate Against DB functionality works in the following stages:

Create DDL

The Create DDL for the table to be validated/altered MUST use any of the following tokens to refer to the name of the table: $OBJECT$, $TABLE$ or [TABLEOWNER].[table_name].

Without these tokens, the temporary "expected" table cannot created reliably as only these tokens can be replaced with temporary table names for the temporary table creation.

Drop Table DDL

The Drop Table DDL statement MUST use any of the following tokens to refer to the name of the table: $OBJECT$, $TABLE$. Otherwise, the dropping of the temporary table after the validation process may drop the actual data warehouse table.

Table/Column information SQL Block

These are some of the characteristics that this procedure needs to be created:

• This is a SQL Block Procedure object that is configured on the target connection.
• SQL Blocks are a sub-type of the Procedure object in RED.
• The SQL Block must contain exactly two (2) separated statements by the configured end-of the statement string (<EOS>, by default).
• Each statement must return one result set.
• Each result set must contain the specified columns below in the correct order.
• The data types shown below are the maximum sizes to be processed.
• Additional information is returned in triples of extra columns at the end of the result set. This additional information is compared during validation and is provided to the template used to generate alter DDL.

Statement 1 - Table information

Where i ranges from 0 to the number of additional information keys are exclusive.

Statement 2 - Column information

Where i ranges from 0 to the number of additional information keys are exclusive.

The following example uses the standard information_schema views. It is also the default if nothing is configured

SELECT table_catalog, table_schema, table_name
        FROM information_schema.tables
       WHERE UPPER(table_catalog) = UPPER('$DATABASE$')
         AND UPPER(table_schema) = UPPER('$SCHEMA$')
         AND UPPER(table_name) = UPPER('$TABLE$')
       ORDER BY table_catalog, table_schema, table_name
<EOS>
      SELECT table_catalog, table_schema, table_name, ordinal_position, column_name,
             CONCAT(data_type, CASE WHEN COALESCE(character_maximum_length, numeric_precision, datetime_precision) IS NOT NULL
                                    THEN CONCAT('(',
                                                CONCAT(CAST(COALESCE(character_maximum_length,
                                                                     numeric_precision,
                                                                     datetime_precision) AS VARCHAR(20)),
                                                       CONCAT(CASE WHEN numeric_scale IS NOT NULL
                                                                   THEN CONCAT(', ', CAST(numeric_scale AS VARCHAR(20)))
                                                                   ELSE ''
                                                              END,
                                                              ')')))
                                    ELSE ''
                               END) data_type,
             'COLUMN_DEFAULT', 'Default Value', column_default,
             'NULLABLE', 'Nullable', is_nullable
        FROM information_schema.columns
       WHERE UPPER(table_catalog) = UPPER('$DATABASE$')
         AND UPPER(table_schema) = UPPER('$SCHEMA$')
         AND UPPER(table_name) = UPPER('$TABLE$')
       ORDER BY table_catalog, table_schema, table_name, ordinal_position

Alter DDL Template

• This is a template that is configured on each table.
• The default for new tables is taken from the default template configured on the target connection.
• The template must be set as Type: 'Alter'
• The result of the template is interpreted as a sequence of SQL Blocks separated by two newlines.
  • Each SQL Block is executed independently; the failure of one does not prevent the execution of the following.
  • Within each SQL Block there can be multiple statements separated by "<EOS>" or the configured separator.
    • A failure of a statement within an SQL Block does prevent the execution of the following statements in that block.
  • The usual variables are available, e.g. table, options, env.

In addition, the following variables are available:

'Change' object type

Change types

'TableInformation' object type

'ColumnInformation' object type

'AdditionalInformation' object type