Materialization Settings

Configuration for generating materialized objects (Dynamic Tables or Tables) from semi-structured data sources.

Overview

Materialization Settings configure how DataPancake generates SQL code to create materialized objects from your semi-structured data. These settings control the output object type, naming, deployment location, case sensitivity, and stream metadata inclusion.

Feature Requirements:

• Semi-structured data sources only

• SQL Code Generation feature must be enabled

---

Table Type

The type of materialized object to generate.

Options:

• Dynamic Table (Recommended) - Automatically refreshed based on target lag, requires Dynamic Table Settings configuration

• Table - Static tables requiring manual refresh, simpler configuration

Selection:

• Use Dynamic Table for continuously updated data requiring automatic refresh

• Use Table for static or rarely updated data requiring manual control

---

Root Table Name

The base name used for all generated materialized objects.

Purpose:

• Name for the root-level materialized object

• Prefix for all nested array objects (system automatically adds underscores for nested objects)

Requirements:

• Required when SQL Code Generation is enabled

• Should be unique within deployment database/schema

• Must be valid Snowflake identifier

Naming Pattern:

• Root Object: Uses the exact name provided (e.g., CUSTOMER_EVENTS)

• Nested Array Objects: Uses prefix with underscore and array attribute name (e.g., CUSTOMER_EVENTS_ITEMS for items array)

---

Deployment Location

The database and schema where materialized objects will be created.

Table Deployment Database:

• Optional field (if empty, uses source database)

• Should exist before code generation

Table Deployment Schema:

• Optional field (if empty, uses source schema)

• Should exist before code generation

Note: If deployment database/schema are not specified, objects are created in the same database/schema as the source object.

---

Case Sensitivity

Controls whether object names preserve case and allow special characters.

Toggle: Use Case Sensitivity

When Enabled:

• All output object names enclosed in double quotes

• Preserves exact case of names

• Allows special characters in names

• Example: "CustomerEvents", "customer_events"

When Disabled:

• Object names not quoted

• Snowflake converts to uppercase

• No special characters allowed

• Example: CUSTOMER_EVENTS (always uppercase)

---

Include Stream Message Metadata

Controls whether streaming platform message metadata is included in the root dynamic table.

When Enabled:

• Includes metadata fields: message_key, message_offset, message_timestamp, and platform-specific metadata fields

• Useful for tracking message order, deduplication, and auditing

When Disabled:

• No message metadata included

• Only data payload attributes in dynamic table

Requirements:

• Source Platform/Application must be configured (Kafka, DynamoDB, etc.)

• Only applies to streaming platform data sources

• Metadata fields automatically added to root table

For information on deduplication, see Source Object Settings.

---

Configuration Requirements

Minimum Requirements (when SQL Code Generation enabled):

• Table Type (Dynamic Table or Table)

• Root Table Name

For Dynamic Tables (additionally required):

• Dynamic Table Warehouse Name

• Dynamic Table Target Lag Unit Type

• Dynamic Table Target Lag Quantity

For Metadata Tables (if enabled):

• Metadata Table Task Warehouse Name

• Metadata Table Task Cron Schedule

• Metadata Table Task Schedule Time Zone

Optional Settings:

• Table Deployment Database (defaults to source database)

• Table Deployment Schema (defaults to source schema)

• Case Sensitivity (defaults to disabled)

• Include Stream Message Metadata (defaults to disabled)

---

---

Summary

Core Settings:

• Table Type (Dynamic Table or Table)

• Root Table Name (required)

• Deployment Location (optional, defaults to source location)

Advanced Settings:

• Case Sensitivity (optional, defaults to disabled)

• Include Stream Message Metadata (optional, streaming only, defaults to disabled)

Requirements:

• SQL Code Generation feature must be enabled (semi-structured only)

• Additional Dynamic Table settings required for Dynamic Tables

For detailed Dynamic Table configuration, see Dynamic Table Settings. For secure view configuration, see Secure View Settings.