DVRF™: Moving Beyond the Medallion Architecture in the Age of GenAI -
Let's Go

Dynamic Table Settings

Configuration for Snowflake Dynamic Tables generated from semi-structured data sources.

Overview

Dynamic Table Settings configure the refresh behavior, warehouse assignment, and optional parameters for Dynamic Tables generated by DataPancake.

Feature Requirements:

  • Table Type must be set to "Dynamic Table"

  • SQL Code Generation feature must be enabled

Dynamic Table Warehouse Name

The Snowflake warehouse assigned to the Dynamic Table for refresh operations.

Purpose:

  • Warehouse used to execute Dynamic Table refreshes

  • Determines compute resources for refresh

  • Warehouse size affects refresh cost and performance

Requirements:

  • Required for Dynamic Table type

  • Warehouse must exist before code generation

  • User must have USAGE privilege on warehouse

Note: This is the warehouse assigned to the Dynamic Table (in the CREATE DYNAMIC TABLE statement), not the warehouse used to execute the CREATE statement.

Target Lag Configuration

The maximum acceptable delay between source data changes and Dynamic Table updates.

Target Lag Unit Type:

  • seconds — For near-real-time updates

  • minutes — For frequent updates (most common)

  • hours — For periodic updates

  • days — For daily or less frequent updates

Target Lag Quantity:

  • Number of time units for target lag (must be greater than 0)

  • Combined with unit type defines refresh schedule

Examples:

  • 1 minute — Updates within 1 minute of source changes

  • 5 minutes — Updates within 5 minutes

  • 1 hour — Updates within 1 hour

  • 1 day — Daily updates

How Target Lag Works:

  • Snowflake automatically refreshes Dynamic Table when target lag is exceeded

  • Refresh frequency depends on source data change rate

  • More frequent changes trigger more frequent refreshes

Optional Parameters

Additional parameters included in the CREATE DYNAMIC TABLE statement.

Root Dynamic Table Optional Parameters:

  • Parameters for the root-level Dynamic Table

  • Included directly in CREATE DYNAMIC TABLE statement

Nested Dynamic Table Optional Parameters:

  • Parameters for nested Dynamic Tables created from array attributes

  • Applied to all nested/flattened Dynamic Tables

Common Parameters:

  • REFRESH_MODE=INCREMENTAL — Only refresh changed data (default, recommended)

  • REFRESH_MODE=FULL — Refresh all data each time

Parameter Format:

  • Key-value pairs separated by commas

  • Format: KEY1=VALUE1, KEY2=VALUE2

  • Example: REFRESH_MODE=INCREMENTAL, INITIALIZE=ON_CREATE

Documentation:

Metadata Table Configuration

Optional configuration for creating metadata tables to track Dynamic Table changes.

Create Metadata for Dynamic Table Inserts:

  • Toggle: Creates a metadata table tracking inserts to root Dynamic Table

  • Stores primary key, UUID, created timestamp, last updated timestamp

  • Enables change tracking and auditing

  • Metadata table and sync task created automatically when enabled

Create Metadata for Dynamic Table Updates:

  • Toggle: Extends metadata tracking to include updates

  • Tracks when rows are updated in Dynamic Table

  • Requires Create Metadata for Inserts to be enabled

Create Metadata for Dynamic Table Deletes:

  • Toggle: Extends metadata tracking to include deletes

  • Tracks when rows are deleted from Dynamic Table

  • Requires Create Metadata for Inserts to be enabled

Metadata Table Task Configuration

Required when metadata tracking enabled:

  • Metadata Table Task Warehouse Name - Warehouse for executing metadata sync task (can be different from Dynamic Table warehouse)

  • Metadata Table Task Cron Schedule - Cron expression for scheduling metadata updates

  • Metadata Table Task Schedule Time Zone - Timezone for cron schedule

Optional:

  • Task Deployment Database - Database where metadata sync task will be deployed (defaults to Dynamic Table deployment database)

  • Task Deployment Schema - Schema where metadata sync task will be deployed

Cron Schedule Examples:

  • 30 * * * * — Hourly at 30 minutes past hour

  • 0 3 * * * — Daily at 3:00 AM

  • 0 3 * * MON — Weekly on Monday at 3:00 AM

  • 0 3 1 * * — Monthly on 1st at 3:00 AM

Available Timezones:

  • America/Los_Angeles, America/New_York, America/Chicago, America/Denver, Etc/UTC (recommended), and others

Use Cases:

  • Change tracking and audit trails

  • Data quality monitoring and lineage

  • Integration with downstream systems for change detection

Configuration Requirements

Required Settings for Dynamic Tables:

  • Dynamic Table Warehouse Name

  • Dynamic Table Target Lag Unit Type

  • Dynamic Table Target Lag Quantity

Required Settings for Metadata Tables (if enabled):

  • Metadata Table Task Warehouse Name

  • Metadata Table Task Cron Schedule

  • Metadata Table Task Schedule Time Zone

Optional Settings for Metadata Tables:

  • Task Deployment Database (defaults to Dynamic Table deployment database)

  • Task Deployment Schema

Validation: The system validates warehouse exists and is accessible, target lag quantity is greater than 0, cron schedule is valid format, timezone is valid, and all required fields are provided when metadata enabled.

Summary

Core Settings (required):

  • Warehouse Name

  • Target Lag Unit Type

  • Target Lag Quantity

Optional Parameters:

  • Root Dynamic Table parameters

  • Nested Dynamic Table parameters

Metadata Configuration (optional):

  • Enable metadata tracking (Create Metadata for Inserts/Updates/Deletes)

  • Configure metadata task (warehouse, cron schedule, timezone)

For materialization settings, see Materialization Settings. For secure view configuration, see Secure View Settings.

PreviousData Source TypesNextMaterialization Settings

Last updated

Was this helpful?