# How to Configure Materialization & Dynamic Table Settings ## 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. ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-13/434267fd-def9-41ed-a1f3-612af143465f/ascreenshot.jpeg?tl_px=0,0\&br_px=2698,1507\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=487,114) ### 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. ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-13/135cff45-319d-4ae1-a5b0-f3cb9ba91c57/ascreenshot.jpeg?tl_px=0,0\&br_px=2698,1507\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=486,185) ### 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. ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-13/bd2212fa-2661-45bd-a551-3974cc4c012b/ascreenshot.jpeg?tl_px=0,0\&br_px=2698,1507\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=794,115) ### 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. ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-13/86ecacdf-ccfe-4f75-9d54-67e1e98d080b/ascreenshot.jpeg?tl_px=0,0\&br_px=2698,1507\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=736,187) ### 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. ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-13/cb6eccb0-8503-4f59-a22e-3707255f2bfa/ascreenshot.jpeg?tl_px=0,0\&br_px=2698,1507\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=899,257) ### 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. ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-13/d17562a5-76ad-4b07-be23-275237707de7/ascreenshot.jpeg?tl_px=0,0\&br_px=2698,1507\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=416,230) ### 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. ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-13/e01923ec-8ca3-48cb-b060-159ac8b83906/ascreenshot.jpeg?tl_px=0,0\&br_px=2698,1507\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=404,262) *** ## 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 ### 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. ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-11/4b09b28d-253c-43ec-9472-a64443106d4e/ascreenshot.jpeg?tl_px=0,15\&br_px=1376,784\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=398,277) ### 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. ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-11/ce603188-b50f-4f57-b310-6bc27ca2cf73/ascreenshot.jpeg?tl_px=0,108\&br_px=1376,877\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=454,277) ### 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. ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-11/cfb44144-5b5f-4893-9311-9dc5d81a3d85/ascreenshot.jpeg?tl_px=0,179\&br_px=1376,948\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=372,277) ### 4. (Optional) Enter the Root Dynamic Table Optional Parameters This value will be included in the CREATE dynamic table statement. See for available options. ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-11/801c9976-9c73-49f5-973f-949748901e65/ascreenshot.jpeg?tl_px=9,223\&br_px=1385,993\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=524,322) ### 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 for available options. ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-11/c7cd52e0-7581-4732-94c8-a1fbb5085cff/ascreenshot.jpeg?tl_px=14,223\&br_px=1390,993\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=524,442) *** ## 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 metadata for each row in the root dynamic table. ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-11/5d385f63-9348-474b-b0f5-f2f30a754ba6/ascreenshot.jpeg?tl_px=164,28\&br_px=1541,797\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=627,277) ### 2. (Optional) Toggle the Create Metadata for Dynamic Table Updates If enabled, the metadata will be created for row updates in additon to inserts. ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-11/1cbc2b25-c287-4de2-a1b3-2861337f6be6/ascreenshot.jpeg?tl_px=164,96\&br_px=1541,865\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=628,277) ### 3. (Optional) Toggle the Create Metadata for Dynamic Table Deletes If enabled, the metadata will be created for row deletes. ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-11/6e6890ef-4300-4e9d-9e5a-070343193b67/ascreenshot.jpeg?tl_px=164,165\&br_px=1541,934\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=626,276) ### 4. (Optional) Enter the Task Deployment Database Name The name of the database the metadata table sync task will be deployed to. {% hint style="info" %} If any of the 3 ‘Create Metadata’ toggles are toggled on and this is left blank, it will assume the same database name as the source table. {% endhint %} ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-11/c612e6f2-a3b9-42a9-8f99-3cc42a39070f/ascreenshot.jpeg?tl_px=164,0\&br_px=1541,769\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=633,270) ### 5. (Optional) Enter the Task Deployment Schema Name The name of the schema the metadata table sync task will be deployed to. {% hint style="info" %} If any of the 3 ‘Create Metadata’ toggles are toggled on and this is left blank, it will assume the same schema name as the source table. {% endhint %} ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-11/d82acbfd-5404-456c-9c76-2e2c57a3e7f4/ascreenshot.jpeg?tl_px=164,74\&br_px=1541,843\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=632,277) ### 6. Enter the Metadata Table Task Warehouse Name {% hint style="warning" %} If any of the 3 ‘Create Metadata’ toggles are enabled, this is **required**. {% endhint %} 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. ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-11/25dc782b-6483-4cba-90ff-1e72e2f6f506/ascreenshot.jpeg?tl_px=164,160\&br_px=1541,929\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=643,277) ### 7. Enter the Metadata Table Task Cron Schedule {% hint style="warning" %} If any of the 3 ‘Create Metadata’ toggles are enabled, this is **required**. {% endhint %} 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 ```python 30 * * * * ``` Daily at 3:00 am ```python 0 3 * * * ``` Weekly at 3:00 am every Monday ```python 0 3 * * MON ``` Monthly at 3:00 am on the 1st of each month ```python 0 3 1 * * ``` ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-11/d4714201-8c4e-467c-ace8-b7a6d131ca56/ascreenshot.jpeg?tl_px=164,223\&br_px=1541,993\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=645,296) ### 8. Enter the Metadata Table Task Schedule Time Zone {% hint style="warning" %} If any of the 3 ‘Create Metadata’ toggles are enabled, this is **required**. {% endhint %} A cron timezone is required in addition to the cron schedule. ![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-11/71ffa2e5-6d43-4b44-a0ce-2652a7c5b0f9/ascreenshot.jpeg?tl_px=164,223\&br_px=1541,993\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=642,362) *** ## Next Steps See [How to Configure the Secure Semantic View Layer](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-the-secure-semantic-view-layer.md) --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.datapancake.com/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-output-object-and-dynamic-table-settings.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.