Tools → Table Editor

About the Table Editor

The Table Editor allows you to define the properties of physical schema objects. The physical schema objects include the following:

• Physical schema table
• Materialized view (MV)
• Alias
• Incorta Analyzer table
• Incorta SQL table

You must have proper permissions and access rights to access the Table Editor and edit the physical schema objects. You can also access the Table Editor to view the object details.

Table Editor permissions and access rights

A user that belongs to a group with the Schema Manager or the SuperRole role can access the Table Editor for a given object in a physical schema that this user owns or has at least View access rights to.

A user that belongs to a group with the Schema Manager or the SuperRole role can access the Table Editor for a given object in a physical schema that this user owns or has View access rights to. If the Enable Super User Mode option is enabled in the Cluster Management Console (CMC) → Tenant Configurations → Security, the Super User that is a Tenant Administrator and any user with the SuperRole can access the Table Editor for a given object and edit it regardless of the access rights.

A user with only the Schema Manager role must own or have Edit access rights to the physical schema to be able to use the Table Editor to edit its objects.

You can access the Table Editor for an object in a physical schema using the:

• Schema Designer
• Schema Diagram Viewer
• Load Job Details Viewer for the physical schema (not applicable for an alias)

Access the Table Editor using the Schema Designer

There are multiple options or paths that you have to access the Table Editor for a given object using the Schema Designer for a physical schema that you own or have access rights to:

• Create a new object, by selecting either + in the Tables tab or + New and an object type in the Action bar.
• In the Tables tab, for a given object, select More Options (⋮ vertical ellipsis), and then select Table Details.
• In the Tables tab, select a given object.

Access the Table Editor using the Schema Diagram Viewer

You can access the Table Editor for a given object in a physical schema that you own or have access rights to by using the Schema Diagram Viewer of:

• the same physical schema
• another physical schema that this object has a join relationship with one of its objects

To access the Table Editor using the Schema Diagram Viewer, select an object in the diagram. In the object details drawer, select the square with arrow icon next to the object name.

Access the Table Editor using the Load Job Details Viewer

You can access the Table Editor for a given object in a physical schema that you own or have access rights to by using the Load Job Details Viewer. In the load job details list, in the Schema/Table column, select the name of an object.

Table Editor anatomy

The Table Editor consists of the following:

• Action bar
• Table Summary section
• Columns tab
• Data Sources tab
• Joins tab
• Filters tab
• Advanced Settings (available starting 2024.7.x)

Action bar

The Action bar shows the following, as applicable:

• Breadcrumbs trail for the Tables Editor as follows: schema_manager_link / physical_schema_link / current_object_name
• Name: The name of the object. You cannot change this name once saved.
• + New: select to open the New menu. The following are the available options depending on the object type.
  • Data Sources: select to add a new data source to the current physical schema table to create a multi-source table.
  • Formula Column: select to create a new formula column, available only for physical schema tables and materialized views.
  • Regular Security Filter: select to add a new regular runtime security filter
  • Formula Security Filter: select to add a new formula runtime security filter
  • Join (as Parent): select to add a new join relationship for the object as a parent
  • Join (as Child): select to add a new join relationship for the object as a child
• Done: select to save the changes you made and exit the Table Editor.
• Cancel: select to cancel the changes you made, if any, and exit the Table Editor.

Table Summary section

The following table shows the details and options available in the Table Summary section.

Message banner

The message banner shows messages as appropriate. For example, the Table Editor shows a message if the object is in a physical schema of which you already have a draft version and the Schema Designer is in the saved version mode. The message denotes that if you make new changes, you will overwrite the existing draft and lose related changes. Selecting the message opens the draft so that you can edit it and make additional changes. If the current object has related changes in the draft version, these changes are loaded.

In addition, when accessing the Table Editor for an Incorta Table, SQL Table, or MV while in draft mode, a message appears confirming that you should publish your changes if the current object is based on another object that is newly created in the current draft.

Columns tab

The Columns tab consists of two sections: the Columns section and the Formula Columns section. You can collapse and expand each section as appropriate.

Columns section

The Columns section shows the following:

• The number of data-backed columns in the physical schema object
• Manage Output: This option is available only for a physical schema table with multiple data sources. Select to manage the mapping between columns in the different datasets and select the data type for each output column in the multi-source table.
• Search box: search for object columns.
• Preview Data: select to preview sample data of the object. Available only in the case of a loaded physical schema table or materialized view.
• Columns in the physical schema object. For a loaded object, you can preview samples of the column data. You can also view the column's upstream and downstream data lineage, that is, entities that this column references (upstream lineage), and dependent entities that reference this column (downstream lineage).
• Column properties: determine how the Loader Service extracts and stores data as well as how the Analytics Service loads data. Some properties determine how Incorta stores the column data; other properties influence the column’s representation and default behavior in tools such as the Analyzer and Formula Builder. You cannot change the column properties for an alias.

The Table Editor shows the properties of a column as follows:

To learn about a column in a physical schema table, review Concepts → Physical Schema Table Column.

To learn about a column in a materialized view, review Concepts → Materialized View Column.

To learn about a column in an Incorta Analyzer Table, review Concepts → Incorta Analyzer Table Column.

Column upstream and downstream data lineage

The current version of the Data Lineage Viewer is available as a preview feature. This version shows both the source (upstream lineage) and destination (downstream lineage) of data. Upstream data lineage refers to entities that the current entity references and gets impacted by while downstream data lineage refers to entities where the current entity is referenced. The new version also tracks the lineage of tables, views, dashboards, insights, session variables and global variables. You can use the Data Lineage Viewer v2 to view the upstream and downstream lineage of columns and formula columns in physical schema objects.

Formula Columns section

The Formula Columns section is available only in the case of physical schema tables and materialized views to create and manage formula columns for the physical schema object. An alias inherits formula columns from the original physical schema table or materialized view; you cannot manage them for the alias itself.

This Formula Columns section shows the following:

• The number of formula columns in the physical schema object, if any
• + Add Formula Column: available in the case of a saved object when there are no existing formula columns in this object
• The formula columns in the physical schema object, if any. You can preview both the upstream and downstream data lineage of a formula column.

For each formula column, the Formula Columns section shows the following:

To learn more about formula columns in a physical schema table, review Concepts → Physical Schema Table Formula Column.

To learn more about formula columns in a materialized view, review Concepts → Materialized View Formula Column.

Formula column type inference

Based on the output of the formula expression, the Table Editor will automatically infer the data type of a formula column. You can modify the formula expression to explicitly cast the data type of the return value. For example, you can use a built-in function, such as int(), to specify that the formula expression returns an integer value.

Data Sources tab

The Data Sources section contains the following:

• The number of data sources.
• + (addition icon): select to add a new data source. This option is available only in the case of a physical schema table.
• The data source(s) of the physical schema object. There can be one or more data sources depending upon the physical schema object type.
  • Only a physical schema table can be single-source or multi-source depending upon the connector you use for the data source.
  • Besides physical schema tables, all other objects cannot be multi-source. An alias, Incorta Analyzer table, Incorta SQL table, and materialized view must have one and only one data source; they are single-source objects.
  • An alias does not actually have an external data source. It rather references another object in the same physical schema or another physical schema.
• For each data source, the list shows the following:
  • Type: this property displays different information depending on the object type.
    • In the case of a physical schema table, this property displays the type of the dataset whether File System, SQL Database, XML, Salesforce, …etc.
    • In the case of other physical schema objects, this property shows the object type: Alias, Incorta Analyzer, Incorta SQL, or Materialized View.
  • Data Source/Language: this property can display one of the following:
    • The data source in the case of physical schema tables
    • The fully qualified name of the source object in the case of aliases
    • The script language in the case of MVs
    • The type of the object: Incorta Analyzer or Incorta SQL
  • Incremental: this property shows whether the data source has incremental load enabled or not.
  • Delete (trash can icon): when there are multiple data sources, you can delete any of them. It is not available when only one data source is available.
• Select a data source to access the Table Data Source dialog to view or edit the data source properties.
  • In the case of an Incorta Analyzer table, you can view a sample of the object data in the Table Data Source dialog.
  • When you edit the data source properties of a physical schema table and select to save without discovery or when you select to save only the script of the MV in the data source properties, a note appears in the Data Source/Language column confirming that the object is not validated. This note continues to appear till you validate your updates.

Joins tab

The Joins tab shows all join relationships where the current object is the Parent table or the Child table.

Parent Joins section

The Parent Joins section shows all the join relationships, if any, where the current physical schema object is the Parent table (the object on the right side of the join). With the suitable access rights and permissions, you can add, edit, and delete join relationships in the Parent Joins section if both the Parent and Child objects exist in the same physical schema. However, you cannot edit or remove parent joins with objects in another physical schema. You can access and manage them from the Table Editor of the Child object.

To learn more about joins, review Concepts → Join and Tools → Join Editor.

The Parent Joins section shows the following:

• The number of join relationships, if any, where this object is the Parent table in the join
• + (Add New Join): select to add a new join relationship for the object as a Parent table
• The condition(s) of each existing join relationship; join filters do not show in the Table Editor. Each join condition defines the following:
  • The physical schema, object, and column of the Child side in the join (the object on the left side)
  • Join operator
  • The physical schema, object, and column of the Parent side in the join (the object on the right side). Typically, this is the current physical schema object.
• More Options (⋮ vertical ellipsis): available only in the case that both the Parent and Child objects in the join relationship exist in the same physical schema. Available options are the following:
  • Join Details: select to access the Join Editor to manage the join conditions and filters
  • Remove Join: select to delete the join relationship

Child Joins section

The Child Joins section shows all the join relationships, if any, where the current physical schema object is the Child table (the object on the left side of the join). With the suitable access rights and permissions, you can add, edit, and delete join relationships in the Child Joins section whether both the Parent and Child objects exist in the same physical schema or in different physical schemas.

The Child Joins section shows the following:

• The number of join relationships, if any, where this object is the Child table in the join
• + (Add New Join): select to add a new join relationship for the object as a Parent table
• The condition(s) of each existing join relationship; join filters do not show in the Table Editor. Each join condition defines the following:
  • The physical schema, object, and column of the Child side in the join (the object on the left side). Typically, this is the current physical schema object.
  • Join operator
  • The physical schema, object, and column of the Parent side in the join (the object on the right side)
• More Options (⋮ vertical ellipsis): available options are the following:
  • Join Details: select to access the Join Editor to manage the join conditions and filters
  • Remove Join: select to delete the join relationship

Filters tab

In releases before 2024.7.x, the Filters tab has two sections: the Runtime Security Filters section and the Load Filter section. The Load Filter section is available only in the case of a physical schema table or MV. Both sections are not available for an alias. Starting 2024.7.x, the Filters tab has only the Runtime Security Filters section while the Load Filter option has been moved to the new Advanced Settings tab.

Runtime Security Filters section

A runtime security filter is a filter expression that the Analytics Service applies when a user accesses an in-memory physical schema table, materialized view, Incorta Analyzer Table, or Incorta SQL Table. As an alias only references another physical schema object, you cannot create security filters for it. There are two types of runtime security filter: Regular and Formula.

The Runtime Security Filters section shows the following:

• The number of runtime security filters specified for the physical schema object, if any
• All the runtime security filters specified for the physical schema object, whether regular filters or formula filters.
• For a formula security filter, the Runtime Security Filters section shows the filter expression for the security filter, if any. Select the text box to invoke the Formula Builder to create or edit the filter expression
• For a regular security filter, the Runtime Security Filters section shows the following:
  • Table: the physical schema object to use one of its data-backed or formula columns. This can be the object itself or another object in the same physical schema that has a join relationship as a Parent table with the current object as the Child table.
  • Column: the filterable column in the object you select for the Table. This can be a data-backed column or a formula column.
  • Operator: the operator in the filter expression. This can be one of the following:
    • <: less than
    • ≤: less than or equal to
    • =: equal to
    • ≠ or <>: not equal to
    • >: greater than
    • ≥: greater than or equal to
    • IN
  • Value: the value in the filter expression. This can be a filter expression session variable, internal session variable, external session variable, or a global variable.
• Delete (trash can icon): select to delete the runtime security filter

To learn more about runtime security filters, review Concepts → Runtime Security Filter.

Load Filter section

The Load Filter section is available only for a physical schema table or materialized view. This section shows the filter expression, if any, that the Analytics Service applies when loading the object data to the Engine memory from the extracted data on Share Storage. This filter restricts the rows available to the Analytics Service. Load filters do not affect data that the Loader Service extracts from the data source.

Select the Load Filter text box to invoke the Formula Builder to create, edit, or clear the load filter expression. To learn more about load filters, review Concepts → Load Filter.

Starting 2024.7.x, this section no longer exists on the Filters tab. A similar option is available on the new Advanced Settings tab.

Advanced Settings tab

The Advanced Settings tab is available starting 2024.7.x. This tab has multiple sections that vary according to the object properties.

• Disable Full Load
• Enforce Primary Key Constraint
• Loaded Data in Memory
• Data Retention
• Synchronizing delete operations

Options under these sections are valid for physical tables and MVs only.

Disable Full Load

This option is available only for physical schema tables and MVs enabled for incremental loads. Enabling this toggle will prevent the accidental deletion of data from a snapshot table, where the data in Incorta is more granular than the source data.

In the case of physical schema tables and MVs that have incremental load enabled and full load disabled, the Loader Service will skip them during the full load jobs. Typically, a schema developer performs a full load of an object at least once before enabling this property.

Enforce Primary Key Constraint

This option is available in the case of a physical schema table or MV with a key column, and it is disabled by default. You can enable it to enforce the calculation of the primary key index or disable it to skip this calculation to optimize data load time and performance.

• When enabled, during a full load job, the Loader Service calculates the Primary key index to enforce record uniqueness.
• When disabled, the Loader Service skips this calculation. Disable it only if you are sure your dataset has unique records. This scenario applies to full loads only. In incremental load jobs and when key columns are defined, the Loader Service must compare existing and new data to avoid data duplication.

Loaded Data in Memory

Use the Loaded Data in Memory section to specify the object data that you want to load into the Engine memory.

• Select All (Performance Optimized) to load all data into the Engine memory, providing the fastest query performance.
• Select Partial to partially load data into memory by specifying time-window configurations or custom formula conditions (the same as using a load filter). Since this feature is focused on loading data into memory, the data will persist fully in storage. Therefore, Incorta functions that read from Parquet, like MVs and SQLi, will continue to see the full dataset. However, the SQLi Engine port will honor the conditions set for partial loading.
• Select None (Disk-only) to not load any data into the Engine memory, providing the best memory usage. In such a case, queries against the object can be done using MVs or other tools like Spark.

Data Retention

Use the Data Retention section to specify data to keep on disk based on time-window configurations or custom conditions, limiting disk space usage. After configuring the data retention condition, you can use the new Data Purge option in the Schema Designer to permanently remove data that does not satisfy the specified condition.

Synchronizing delete operations

Use this section to prepare for deleting records identified by an exclusion set, allowing for synchronizing delete operations with operational data sources to remove inconsistencies in data. Additionally, this capability can also be leveraged for privacy reasons, like GDPR Article 17 (The right to be forgotten). The exclusion set must contain a column that matches a column in the table data source. After setting the configurations, you can use the new Data Purge option in the Schema Designer to permanently remove matching records.

Table Editor actions for a physical schema object

With the Table Editor, you can perform the following actions for a physical schema object, as applicable:

• Define a physical schema object
• Update a physical schema object
• Enable or disable enforcing primary key index calculation
• Enable or disable full load
• Manage data sources
  • Create a multi-source physical schema table
  • View data source details
  • Edit a data source
  • Manage output columns for a multi-source physical schema table
  • Remove a data source
  • Preview object sample data
• Manage columns
  • Search for columns
  • Select columns to show in Analyzer
  • Preview column sample data
  • View column data lineage
  • Specify column properties
  • Lock the data type of one or more output columns in a multi-source table
• Manage formula columns
  • Create a formula column
  • Edit a formula column
  • Select formula columns to show in Analyzer
  • View formula column data lineage
  • Delete a formula column
• Manage joins
  • Create a join as parent
  • Create a join as child
  • Edit a join relationship
  • Remove a join relationship
• Manage runtime security filters
  • Add a regular runtime security filter
  • Add a formula runtime security filter
  • Edit a runtime security filter
  • Remove a runtime security filter
• Manage the load filter
  • Add a load filter
  • Edit the load filter
  • Remove the load filter
• Manage the object data
  • Enable or disable performance optimization
  • Manage data loaded into memory via a time window
  • Manage data loaded into memory via a custom condition
  • Manage data stored on disk via a time window
  • Manage data stored on disk via a custom condition
  • Manage data deletion via an exclusion set

Updates that require data load

Some updates you make to the physical schema objects require loading data, either load from source (full load) or load from staging.

The following are the updates that require load from staging:

• Adding, updating, or removing the load filter
• Enabling performance optimization for non-optimized objects

The following are the updates that require full load:

• Adding a new physical schema table or materialized view
• Changing the data type of a physical schema table column or MV column
• Changing the source of a materialized view or physical schema table, whether by selecting another source file in the Data Source properties dialog or editing the query
• Adding or changing a key column (changing the column function from key to dimension or measure and vice versa) in a physical schema table or MV
• Adding a new physical schema table column (before 2024.7.x)
• Adding a new MV column (before 2024.7.x)
• Changing the object type, for example, changing a physical schema table to an Incorta Analyzer table or MV
• Removing a physical schema table column or a materialized view column that functions as a key
• Changing the encryption status of one or more columns in a physical schema table or materialized view

In this case, when you select Done in the Table Editor, and then select Save Changes in the Schema Designer, the following occurs:

• A warning dialog appears denoting that you have to perform data load. The dialog lists the objects and the required load type for each.
• The message banner in the Schema Designer will keep showing the objects that require data load till you or another user performs the required load or a scheduled load job runs the required load for the related object(s).
• The Model Status for this object in the Schema Manager and the object record in the Schema Designer will show a triangle with an exclamation mark.

Table Editor access rights matrix

The availability of sections and options in the Table Editor is subject to both the user access rights to the physical schema and the type of the physical schema object.

The following table shows the options and sections available for each object type and the least access rights required to the related physical schema to perform the action:

Define a physical schema object

To start creating a physical scheme object, you need to use the + New menu in the Schema Designer or the + option in the Tables tab in the Schema Designer. Then, you specify the appropriate properties of the data source of the new object. After completing the data source definition, the Table Editor is shown to manage other properties as applicable and define the object name.

Defining one or more physical schema tables using the Schema Wizard does not require defining the object names in the Table Editor as the new objects inherit the data source name, whether this data source is a file, sheet in a spreadsheet file, a table or view in a database, or any other source. However, you can change or specify the table name in the Schema Wizard only when customizing the SQL query or creating a custom SQL table using a query-based data source.

For information about the different types of objects that you can create, refer to Tools → Schema Designer → Tables.

To discover more about the properties of different data sources, see Connectors → All, and refer to the suitable connector.

Update a physical schema object

• In the Table Editor, make the necessary updates.
• To save your changes, in the Action bar, select Done.

Enable or disable enforcing primary key index calculation

The primary key (PK) index calculation is disabled by default for all new physical schema tables and materialized views with key columns.

Here are the steps to enable it or disable it before 2024.7.x:

• Access the Table Editor for a given physical schema object.
• In the Table Editor, in the Table Summary section, turn the Enforce Primary Key Constraint toggle on to enable the PK index calculation, or turn the toggle off to disable the PK index calculation.
• In the Action bar, select Done.

Here are the steps to disable it or enable it starting 2024.7.x:

• Access the Table Editor for a given physical schema object.
• In the Table Editor, select the Advanced Settings tab.
• Turn the Enforce Primary Key Constraint toggle on to enable the PK index calculation, or turn the toggle off to disable the PK index calculation.
• In the Action bar, select Done.

Enable or disable full load

You can disable full load for physical tables and MVs with incremental load enabled:

Here are the steps to enable it or disable it before 2024.7.x:

• Access the Table Editor for a given physical schema object with incremental load enabled.
• In the Table Editor, in the Table Summary section, turn on the Disable Full Load toggle to disable full load, or turn it off to enable full load for the physical schema object.
• In the Action bar, select Done.

Here are the steps to disable it or enable it starting 2024.7.x:

• Access the Table Editor for a given physical schema object with incremental load enabled.
• In the Table Editor, select the Advanced Settings tab.
• Turn the Disable Full Load toggle on to disable full load, or turn it off to enable full load for the physical schema object.
• In the Action bar, select Done.

Manage data sources

Only a physical schema table can have one or more data sources. Creating a multi-source table automatically results in validating the data sources and inferring the data types of the output columns. Incorta will automatically perform data type casting to avoid type conflicts. You can also perform manual type-casting but you need to ensure that data can be cast into the new data type.

All of the following actions also result in inferring the output column data types and casting these data types if required. However, you can lock the data type of one or more output columns in a multi-source physical schema table to avoid the data type being automatically cast in the following cases:

• Accessing the respective multi-source table in the Table Editor
• Adding a new data source to a multi-source table
• Editing one of the data sources in the Table Data Source dialog and validating all data sources
• Deleting one data source from a multi-source table with more than two data sources

Create a multi-source physical schema table

You can add a data source to physical schema tables only. Adding more than one data source creates a multi-source table.

• In the Table Editor, do one of the following:
  • In the Data Source(s) tab, select + (addition icon).
  • In the Action bar, Select + New → Data Sources.
• In the Table Data Source dialog, for Type, select the type of the data source.
• For Data Source, select the data source.
• Specify the required data source configuration according to the type and data source you select.
• Select Add to check the data source properties and save the new data source.
• In the Action bar, select Done.

View data source details

• In the Table Editor, in the Data Sources tab, select a data source.
• In the Table Data Source dialog, review the data source properties.
• Select x in the upper-right corner of the dialog or select Cancel to exit it.

Edit a data source

• Access the Table Editor for a given physical schema object.
• In the Table Editor, in the Data Sources tab, select a data source.
• In the Table Data Source dialog, make the necessary configuration updates as applicable.
• Select Validate to check the data source properties and save the updates.
• In the Action bar, select Done.

Manage output columns for a multi-source physical schema table

• In the Table Editor, in the Columns tab, select Manage Output.
• In the Manage Output dialog, for each output column, do the following as required:
  • For Output, define the output column data type.
  • For Auto-detect data type, select the checkbox to enable the auto-update of the output column data type or clear the checkbox to lock the output column data type.
• Select Ok.
• Optionally, in the Columns section, update the column properties as required.
• In the Action bar, select Done.

Remove a data source

You can remove a data source only from a multi-source table.

• Access the Table Editor for a given physical schema table with multiple data sources.
• In the Table Editor, in the Data Sources tab, for a given data source, select Delete (trash can icon).
• In the dialog, select Delete.
• In the Action bar, select Done.

Preview object sample data

You can preview sample data for loaded objects only.

• In the Table Editor, in the Columns tab, select Preview Data.
• In the Preview dialog, review the sample data. Select x in the upper-right corner of the dialog or select any other place in the Table Editor to exit the dialog.

Manage columns

Search for columns

• In the Table Editor, in the Columns tab, in the Search box, enter a search term.
• Review the list of columns.

Select columns to show in Analyzer

By default, all columns in a physical schema object appear in the Data panel in the Analyzer. However, you can specify which columns to show.

Here are the steps to specify which columns to show or hide in the Analyzer:

• In the Table Editor, in the Columns tab, clear the checkbox for each column you want to hide in the Data panel; select the checkbox for the columns you want to show.
• In the Action bar, select Done.

Preview column sample data

you can preview sample data for a column that is loaded.

• In the Table Editor, in the Columns tab, in the Columns section, hover over the column you want.
• Select the eye icon.
• Review the column fully qualified name and sample data.
• Select any other place in the Table Editor to exit the column data preview.

View column data lineage

• In the Table Editor, on the Columns tab, in the Columns section, for the column you want, select Lineage.
• Use the Data Lineage Viewer v2 to review the upstream and downstream data lineage of the column.

Specify column properties

• In the Table Editor, in the Columns tab, edit the properties of the column you want as applicable depending upon the object type.
  • You cannot change the column name in the Table Editor.
  • For Label, enter a user-friendly name for the column. This is not available for an Incorta Analyzer table nor an Incorta SQL table. However, you can use the Analyzer to specify the column label for an Incorta Analyzer table column, and use the Query Editor to specify another name and label for an Incorta SQL table column.
  • For Type, select the data type of the column.
  • For Function, specify the function of the column in Incorta.
  • In Encrypt, specify whether or not you want to encrypt the column data in Shared Storage.
  • For Auto-detect data type, select the checkbox to enable the auto-update of the output column data type or clear the checkbox to lock the data type of the respective output column.
• In the Action bar, select Done.

Lock the data type of one or more output columns in a multi-source table

You can lock the data type of one or more output columns in a multi-source table either in the Columns tab or the Manage Output dialog.

Here are the steps to lock the output column data type in the the Columns tab:

• Access the Table Editor for a given physical schema table with multiple data sources.
• In the Table Editor, in the Columns tab, optionally, change the data type of the output columns.
• For each output column that you want to lock its data type, clear the Auto-detect data type checkbox.
• In the Action bar, select Done.

Here are the steps to lock the output column data type in the Manage Output dialog:

• Access the Table Editor for a given physical schema table with multiple data sources.
• In the Table Editor, in the Columns tab, in the Columns section, select Manage Output.
• In the Manage Output dialog, optionally, define the data type for the output columns.
• For each output column that you want to lock its selected data type, clear the Auto-detect data type checkbox.
• Select Ok.
• In the Action bar, select Done.

Manage formula columns

Create a formula column

You can add a formula column for a saved physical schema table or materialized view.

• Access the Table Editor for a given physical schema table or materialized view.
• In the Table Editor, do one of the following:
  • In the Columns tab, in the Formula Columns section, select +.
  • In the Action bar, Select + New → Formula Column.
• For the new formula column, specify the following:
  • For Name, enter the name of the formula column used in the formula column fully qualified name.
  • For Label, enter a friendly-user name for the formula column.
  • The Type box shows the Incorta Data Type of the formula column that the formula output determines automatically.
  • For Function, specify the Incorta function of the formula column: dimension or measure.
  • Select the Column Formula box to show the Formula Builder to create the formula expression for the formula column. In the Formula Builder, select Validate & Save.
  • Select the Show checkbox to show the formula column as a selectable column in the Data Panel in the Analyzer.
• In the Table Editor, in the Action bar, select Done.

Edit a formula column

• In the Table Editor, in the Columns tab, for a formula column in the Formula Columns section, make the necessary updates.
• In the Action bar, select Done.

Select formula columns to show in Analyzer

By default, all formula columns in a physical schema object appear in the Data panel in the Analyzer. However, you can specify which columns to show.

Here are the steps to specify which formula columns to show or hide in the Analyzer:

• In the Table Editor, in the Columns tab, in the Formula Columns section, clear the Show checkbox for each formula column you want to hide in the Data panel; select the Show checkbox for the formula columns you want to show.
• In the Action bar, select Done.

View formula column data lineage

• In the Table Editor, on the Columns tab, in the Formula Columns section, for the formula column you want, select Lineage.
• Use the Data Lineage Viewer v2 to review the upstream and downstream data lineage of the formula column.

Delete a formula column

• In the Table Editor, in the Columns tab, in the Formula Columns section, for the formula column you want to delete, select Delete (trash can icon).
• In the dialog, select Delete.

Manage joins

You can manage the joins related to an object when both the Parent table and the Child table are in the same physical schema or when this object is the Child table in a cross-schema join relationship.

Create a join as parent

You can add a join for saved physical schema objects only.

• In the Table Editor, do one of the following:
  • Select the Joins tab, and then in the Parent Joins section, select +.
  • In the Action bar, Select + New → Join (as Parent).
• In the Join Editor, for the new join relationship, specify the join properties for the Child and Parent sides.
• In the Join Editor, in the Action bar, select Done.

Create a join as child

You can add a join for saved physical schema objects only.

• In the Table Editor, do one of the following:
  • Select the Joins tab, and then in the Child Joins section, select +.
  • In the Action bar, Select + New → Join (as Child).
• In the Join Editor, for the new join relationship, specify the join properties for the Child and Parent sides.
• In the Join Editor, in the Action bar, select Done.

View join relationship properties

• In the Table Editor, select the Joins tab, and then for a join relationship in the Parent Joins or Child Joins sections, do one of the following (if applicable):
  • Select the join relationship.
  • Select More Options (⋮ vertical ellipsis), and then select Join Details.
• In the Join Editor, review the join conditions and filters, if any.
• To exit the Join Editor, in the Action bar, select Cancel.

Edit a join relationship

• In the Table Editor, select the Joins tab, and then for a join relationship in the Parent Joins or Child Joins sections, do one of the following (if applicable):
  • Select the join relationship.
  • Select More Options (⋮ vertical ellipsis), and then select Join Details.
• In the Join Editor, make the necessary updates.
• In the Join Editor, in the Action bar, select Done.

Remove a join relationship

• In the Table Editor, select the Joins tab.
• For a join relationship in the Parent Joins or Child Joins sections, if applicable, select More Options (⋮ vertical ellipsis), and then select Remove Join.
• In the dialog, select Delete.
• In the Action bar, select Done.

Manage runtime security filters

You can add and manage runtime security filters for all physical schema objects, except for an alias as it inherits the runtime security filters of the original object.

Add a regular runtime security filter

• In the Table Editor, do one of the following:
  • Select the Filters tab, and then in the Runtime Security Filters section, select + → Regular Security Filter.
  • In the Action bar, Select + New → Regular Security Filter.
• For the new runtime security filter, specify the following:
  • For Table, select the physical schema object to use one of its data-backed or formula columns for the filter expression. This can be the object itself or another object in the same physical schema that has a join relationship (as a Parent table) with the current object (as the Child table).
  • For Column, select the filterable column in the object you selected for the Table. This can be a data-backed column or a formula column.
  • For Operator, select the operator in the filter expression.
  • For Value, select the value in the filter expression. This can be a filter expression session variable, internal session variable, external session variable, or a global variable.
• In the Action bar, select Done.

Add a formula runtime security filter

• In the Table Editor, do one of the following:
  • Select the Filters tab, and then in the Runtime Security Filters section, select + → Formula Security Filter.
  • In the Action bar, Select + New → Formula Security Filter.
• For the new runtime security filter, select the text box to invoke the Formula Builder and create the filter expression.
• In the Formula Builder, select Validate & Save.
• In the Table Editor, in the Action bar, select Done.

Edit a runtime security filter

• In the Table Editor, select the Filters tab, and then in the Runtime Security Filters section, do one of the following depending upon the security filter type:
  • For a regular security filter, make the necessary configuration updates.
  • In the case of a formula security filter, select the formula text box to access the Formula Builder and edit the filter expression. In the Formula Builder, select Validate & Save.
• In the Table Editor, in the Action bar, select Done.

Remove a runtime security filter

• In the Table Editor, select the Filters tab.
• In the Runtime Security Filters section, for a security filter, select Delete (trash can icon).
• In the dialog, select Delete.
• In the Action bar, select Done.

Manage the load filter

You can create load filters only for physical schema tables and materialized views. As a load filter determines which rows of an object to be loaded into the Engine memory, load filters will be effective only when the object has performance optimization enabled. Adding, editing, or removing a load filter requires loading the object data from staging.

The following steps apply to releases before 2024.7.x. For details about managing data loaded into memory in releases starting 2024.7.x, refer to Manage the object data.

Add a load filter

• Access the Table Editor for a given physical schema table or materialized view.
• In the Table Editor, select Filters, and then in the Load Filter section, select the text box to invoke the Formula Builder to create the load filter expression.
• In the Formula Builder, select Validate & Save.
• In the Table Editor, in the Action bar, select Done.

Edit the load filter

• Access the Table Editor for a given physical schema table or materialized view.
• In the Table Editor, select Filters, and then in the Load Filter section, select the text box to invoke the Formula Builder and update the load filter expression.
• In the Formula Builder, select Validate & Save.
• In the Table Editor, in the Action bar, select Done.

Remove the load filter

• Access the Table Editor for a given physical schema table or materialized view.
• In the Table Editor, select Filters, and then in the Load Filter section, select the text box to invoke the Formula Builder and clear the load filter expression.
• In the Formula Builder, select Validate & Save.
• In the Table Editor, in the Action bar, select Done.

Manage the object data

In releases before 2024.7.x, you can only enable or disable performance optimization or apply a load filter to manage data loaded into the memory based on a condition. Starting 2024.7.x, you can load data into memory partially based on a time window or a condition, manage data stored on disk based on a time window or condition, or delete records from Parquet files based on an exclusion set.

Specifying a load filter or disabling performance optimization affects only data loaded into memory and does not affect data on disk; data will persist fully in storage. Therefore, Incorta functions that read from Parquet, like MVs and SQLi, will continue to see the full dataset. However, the SQLi Engine port will honor the conditions set for partial loading. Additionally, the Preview Data feature will preview the full data, not the partially loaded data. The incremental load does not apply the partial loading condition on the incremental data. It is only applied during a full load or a load from staging.

Enable or disable performance optimization

The performance optimization is enabled by default for all the physical schema objects. When disabled, the object data will be cleared from memory, if exists.

Here are the steps to disable or enable it in releases before 2024.7.x:

• Access the Table Editor for a given physical schema object.
• In the Table Editor, in the Table Summary section, turn the Performance Optimized toggle on to disable the Performance optimization, or turn the toggle off to enable performance optimization.
• In the Action bar, select Done.

Here are the steps to disable or enable it starting 2024.7.x:

• Access the Table Editor for a given physical schema object.
• In the Table Editor, select the Advanced Settings tab.
• In the Loaded Data in Memory section, do one of the following as appropriate:
  • To disable the performance optimization, select None (Disk-only), and then in the Confirm Changes dialog, select Confirm Changes
  • To enable it, select All (Performance Optimized).
• In the Action bar, select Done.

Manage data loaded into memory via a time window

• Access the Table Editor for a given physical schema table or materialized view.
• In the Table Editor, select the Advanced Settings tab, and then in the Loaded Data in Memory section, select Partial.
• Under Filtering Criteria, select Time Window.
  • For the Include data for the last option, specify the number of days, weeks, months, quarters, or years to load its data into memory.
  • For the Select column option, select the date or timestamp column that you want to use to specify data you want to load into memory.
• In the Table Editor, in the Action bar, select Done.

Manage data loaded into memory via a custom condition

This is the same as adding a load filter in releases before 2024.7.x.

• Access the Table Editor for a given physical schema table or materialized view.
• In the Table Editor, select the Advanced Settings tab, and then in the Loaded Data in Memory section, select Partial.
• Under Filtering Criteria, select Custom Condition, and then select the Include data that satisfies the below condition text box to invoke the Formula Builder.
• In the Formula Builder, create the condition to load data into memory, and then select Validate & Save.
• In the Table Editor, in the Action bar, select Done.

Manage data stored on disk via a time window

• Access the Table Editor for a given physical schema table or materialized view.
• In the Table Editor, select the Advanced Settings tab, and then turn on the Data Retention toggle.
• Under Filtering Criteria, select Time Window.
  • For the Keep data for the last option, specify the number of days, weeks, months, quarters, or years to keep its data on disk.
  • For the Select column option, select the date or timestamp column that you want to use to specify data you want to keep on disk.
• In the Table Editor, in the Action bar, select Done.

Manage data stored on disk via a custom condition

• Access the Table Editor for a given physical schema table or materialized view.
• In the Table Editor, select the Advanced Settings tab, and then turn on the Data Retention toggle.
• Under Filtering Criteria, select Custom Condition, and then select the Keep data that satisfies the below condition text box to invoke the Formula Builder.
• In the Formula Builder, create the condition to keep data on disk, and then select Validate & Save.
• In the Table Editor, in the Action bar, select Done.

Manage data deletion via an exclusion set

You can synchronize delete operations between a data source and Incorta by identifying an exclusion set that specifies the records to be deleted and without the need for a full load.

• Access the Table Editor for a given physical schema table or materialized view.
• In the Table Editor, select the Advanced Settings tab, and then turn on the Synchronizing delete operations toggle.
• Under Select Exclusion Set, select the schema and the physical table or MV that contains the identifiers of records to be deleted.
• Under Define the column mappings between the target table and the exclusion set, specify the columns in the source object and the exclusion set object to compare to each other to decide which records to delete. Columns in the exclusion set object are filtered according to the data type of the selected source object column.
• In the Table Editor, in the Action bar, select Done.