When you deploy a staging- or data warehouse table from Discovery Hub, a number of physical tables will be created.

What are the "_R" and "_V" tables?

The two primary tables are:

<TableName>_R is the Raw table, that contains the records as they where extracted from the data source, with eventual lookups added.

<TableName>_V is the Valid table, that contains the records as they are after the data cleansing and data quality process has executed.

It is possible to avoid having the Valid table if the table is not collecting history or is incrementally loaded by de-selecting "Physical Valid Table" in the Table settings screen.

On the Project Level, you can change the setting "Postfix Valid" table to ommit the _V postfix on the Valid table instances.

The reason for having data in a raw and valid instance is primarily to help test and troubleshoot transformations and secondly to avoid having to reload the raw data from the source if the data cleansing process fails for some reason.

There is a truncation setting on the tables where you can choose to truncate the raw table after data cleansing. This option will, however not free any space in the database, since the space allocated will still be reserved for the table - and the space will be needed on the next load.

What is the "_L" table and what are the deployment steps?

The List table is a many to many table, that links specific records in the Raw table with error and/or warning messages associated with the record.

One record in the Raw table can be associated with one or more messages from the Message table. The severity flag in the Message table determines if the message is considered as a warning or error.

The deployment happens in the following steps

1. Drop any user-defined functions used by the table
1. Find by timeXtender object ID and type
2. Find by name and schema
3. Find by name in default schema (dbo)
2. Drop existing table
1. Find by name and schema
2. Find by timeXtender ID and type
3. Find by name in default schema (dbo)
3. Create new table
1. Table is tagged with timeXtender object ID and type using extended properties
4. Create new functions
1. User-defined functions are tagged with timeXtender object ID and type using extended properties

What is the "_M" table and what are the deployment steps?

The Message table contains the error messages that can be displayed as result of the validation performed in the data cleansing process.

The messages are created automatically based on the implicit and explicit validation rules defined in timeXtender, such as primary key check, relations and field level validation rules. There is currently no supported way of entering custom error messages.

The severity flag in the Message table determines if the message is considered as a warning or error.

Using the List table, one record in the Raw table can be associated with one or more messages from the Message table.

The deployment happens in the following steps

1. Drop any user-defined functions used by the table
1. Find by timeXtender object ID and type
2. Find by name and schema
3. Find by name in default schema (dbo)
2. Drop existing table
1. Find by name and schema
2. Find by timeXtender ID and type
3. Find by name in default schema (dbo)
3. Create new table
1. Table is tagged with timeXtender object ID and type using extended properties
4. Create new functions
1. User-defined functions are tagged with timeXtender object ID and type using extended properties