Table Type

Table Type defines the dataset's update capability, versioning, change tracking, storage method, and persistence.

The default update is UPDATABLE, which updates as deltas.

Syntax
table_type ::= UPDATEABLE | VERSIONED | ENTITYCHANGETRACKING | INLINE | VIEW| AGGREGATIONONLY
The table types are:
  • UPDATEABLE: Handles modifications (insert and update) as deltas. For delete, refer to Delete Handling
  • VERSIONED: Maintains the latest version of data changes.
  • ENTITYCHANGETRACKING: Tracks changes at the entity level for auditing or synchronization. (Internal)
  • INLINE: Provides structure and data in the definition. See Inline Dataset.
  • VIEW: Represents a read only, computed dataset derived from other datasets. See VIEW Dataset.
  • AGGREGATIONONLY: Contains only summarized data in a single row. See AGGREGATION ONLY Dataset.
Example: In the following code, the versioned dataset CUSTOMER_DIM is refreshed entirely with each source load and available in the target data warehouse:
DEFINE PUBLIC VERSIONED DATASET CUSTOMER_DIM FROM CUSTOMERS END

VIEW Dataset

Example:
IMPORT SOURCE PRODUCTS
DEFINE DATASET PROD_DIM
 ROWSOURCE PROD_DIM;
 THIS = PRODUCTS;
END
DEFINE VIEW DATASET CURRENT_PRODUCT_D
 ROWSOURCE PROD_DIM WHERE DATEDIFF(PROD_DIM.PROD_EFF_TO , DATE '2020-01-01') > 0;
 THIS = PROD_DIM;
END

Note:

  • View is always accessible as PUBLIC. For this reason, you can't specify export_type as PRIVATE or PROTECTED.
  • You can input only data sets, not sources.
  • You're not required to enter the primary key.
  • You can also define views using SQL. See VIEW QUERY.