How to Configure Output Object & Dynamic Table Settings

Configure Output Object Settings, Dynamic Table Settings, and Dynamic Table Metadata Settings for a data source.

Output Object Settings

These settings are used to configure the type of output objects generated, whether to use case sensitivity in the code generation process, whether to include stream message metadata (if applicable), and the deployment location.

1. Ensure the Output Object Type is set to "Dynamic Table"

If this is for a one-time materilization process, you can choose "Table" instead.

2. Enter the Root Output Object Name

Required only if you plan to generate dynamic tables for this data source.

This name will be used as the root dynamic table name and as a prefix for all other nested Dynamic Tables created for array attributes.

You do not need to include an underscore at the end of this name. An underscore will be added automatically for all nested Dynamic Tables.

3. (Optional) Enter the Root Output Object Select Prefix Expression

This is a SQL expression added to the beginning of the select clause of the root dynamic table.

This expression is used to include one or more columns (not the source column to be flattened) from the source database object by adding the expression to the beginning of the root level dynamic table select clause.

For multiple columns use a comma separated list. Alias all columns in this list using the 'as' keyword.

4. (Optional) Enter the name of the database the Dynamic Tables will be deployed to.

If left blank, dynamic tables will be deployed to the same database the source database object resides in.

5. (Optional) Enter the name of the schema the Dynamic Tables will be deployed to.

If left blank, dynamic tables will be deployed to the same schema the source database object resides in.

6. (Optional) Enable "Include Stream Message Metadata"

If enabled, the core Kafka message metadata will be extracted and included in the root and all nested dynamic table select clauses.

7. (Optional) Enable "Use Case Sensitivity"

If enabled, all output column names will be enclosed with double quotes allowing for the accomodation of special characters in the attribute names.

---

Dynamic Table Settings

Dynamic tables are a new type of object in Snowflake that can be used to automatically materialize data based on a schedule similar to how a materialization table, stream (on the source table), and a task with a cron schedule (used to merge CDC events from the stream into the materialized table) would work. For more information see https://docs.snowflake.com/en/user-guide/dynamic-tables-intro.html

1. Enter the Dynamic Table Warehouse Name

The name of warehouse that will be assigned to the dynamic tables which is a part of the CREATE dynamic table statement. This is the warehouse used to manage all of the data refreshes once the dynamic tables are deployed.

2. Enter the Dynamic Table Target Lag Unit Type

The frequency interval for the dynamic table refresh. This value will be included in the CREATE dynamic table statement.

3. Enter the Dynamic Table Target Lag Quantity

The frequency interval quantity for the dynamic table refresh. This value will be included in the CREATE dynamic table statement. The minimum is one minute.

4. (Optional) Enter the Root Dynamic Table Optional Parameters

This value will be included in the CREATE dynamic table statement. See https://docs.snowflake.com/en/sql-reference/sql/create-dynamic-table for available options.

5. (Optional) Enter the Nested Dynamic Table Optional Parameters

Optional parameters for the flattened dynamic tables created from nested arrays. This value will be included in the CREATE dynamic table statement. See https://docs.snowflake.com/en/sql-reference/sql/create-dynamic-table for available options.

---

Dynamic Table Metadata Settings

Metadata tables are used to track changes to dynamic table rows by storing the primary key, a unique UUID, the created at, last action (INSERT or UPDATE), and last updated date for each row in the root dynamic table.

1. (Optional) Toggle the Create Metadata for Dynamic Table Inserts

If enabled, SQL DDL code will be generated to create a new table to store the metadadta for each row in the root dynamic table.

2. (Optional) Toggle the Create Metadata for Dynamic Table Updates

If enabled, the metadata will be created for row updates in additon to inserts.

3. (Optional) Toggle the Create Metadata for Dynamic Table Deletes

4. (Optional) Enter the Task Deployment Database Name

The name of the database the metadata table sync task will be deployed to.

5. (Optional) Enter the Task Deployment Schema Name

The name of the schema the metadata table sync task will be deployed to.

6. (Optional) Enter the Metadata Table Task Warehouse Name

The name of warehouse that will be included in the CREATE Task statement used to create metadata based on changes to the root Dynamic Table.

7. (Optional) Enter the Metadata Table Task Cron Schedule

The metadata table task cron schedule used to schedule the metadata updates. The schedule should coincide with the target lag of the dynamic table to be as frequent or more frequent. Examples:

Hourly at 30 minutes after the hour

30 * * * *

Daily at 3:00 am

0 3 * * *

Weekly at 3:00 am every Monday

0 3 * * MON

Monthly at 3:00 am on the 1st of each month

0 3 1 * *

8. (Optional) Enter the Metadata Table Task Schedule Time Zone

A cron timezone is required in addition to the cron schedule.

---

Next Steps

See How to Configure the Secure Semantic View Layer