Attributes in Code Generation

How active polymorphic versions become SQL columns in Dynamic Tables, including transformation application and array table generation.

Dynamic Table Column Generation

For each active polymorphic version, DataPancake generates a column in the Dynamic Table following this process:

1

Column Name Determination

• Uses alias name if provided, otherwise polymorphic attribute name

• Applies case sensitivity settings

• Combines into final CODE_GENERATED_COLUMN_NAME

2

Type Casting

• Determines appropriate Snowflake data type

• Applies precision and scale for numeric types

• Uses datetime formats if USE_DATETIME_FORMAT = TRUE

• Handles array types appropriately

3

Value Extraction

• Extracts value from source using attribute path

• Handles nested structures and arrays

• Applies null value expression if transformation type is not SQL Expression

4

Transformation Application

• If transformation type is 'SQL Expression', applies transformation expression

• Replaces {attribute_name} placeholder with actual path reference

• Applies semantic layer expressions if configured

5

Column Inclusion

• Only includes if INCLUDE_IN_CODE_GEN = TRUE

• Only includes if PARENT_INCLUDE_IN_CODE_GEN = TRUE

• Excludes if version status is inactive

---

Example: Column Generation

For attribute customer.email with polymorphic version email_string:

Configuration:

• Data Platform Data Type: VARCHAR(255)

• Transformation Type: No Transformation

• Null Value Expression: COALESCE({attribute_name}, '[email protected]')

• Alias Name: customer_email

Generated SQL:

Generated SQL

COALESCE(json_data:"customer"."email"::VARCHAR(255), '[email protected]') AS customer_email

With Transformation Expression:

If transformation type is 'SQL Expression' with:

• Transformation Expression: UPPER({attribute_name})

Generated SQL:

Generated SQL with Transformation

UPPER(json_data:"customer"."email"::VARCHAR(255)) AS customer_email

---

Array Table Generation

For array attributes, DataPancake generates separate tables.

Object Arrays

1

Root Table

• Contains reference to array or flattened array data

• Includes foreign key columns if configured

2

Array Table

• Separate Dynamic Table created for array contents

• Named as {root_table_name}_array_object

• Contains all attributes from array elements

3

Foreign Key Relationship

• Links array table to root table

• Based on configured foreign key columns

• Supports many-to-one or one-to-one relationships

Primitive Arrays

• May be stored as VARIANT or array types in root table

• Or flattened into separate table depending on configuration

• Array primitive type determines element handling

---

ITDCs (Immutable Typed Derived Columns)

DataPancake uses ITDCs (Immutable Typed Derived Columns) in generated SQL to prevent technical debt.

Characteristics:

• Type-safe column definitions

• Explicit type casting

• No implicit conversions

• Prevents future breaking changes

Example:

ITDC example

-- ITDC approach (DataPancake)
json_data:"customer"."email"::VARCHAR(255) AS customer_email

-- vs. implicit approach (technical debt)
json_data:"customer"."email" AS customer_email

---

Code Generation Process Flow

1

Collect Active Attributes

• Query all active polymorphic versions

• Filter by INCLUDE_IN_CODE_GEN = TRUE

• Respect parent include flags

2

Generate Column Statements

• For each active version, generate column SQL

• Apply transformations

• Handle null values

3

Generate Array Tables

• Identify array attributes

• Generate separate table DDL

• Configure foreign key relationships

4

Generate Root Table

• Combine all root-level columns

• Apply table-level settings

• Include metadata columns if configured

5

Generate Views

• Create semantic layer views

• Apply security policies

• Include additional transformations

---

Metadata Tracking

DataPancake can generate metadata tracking objects:

Streams:

• Tracks changes to Dynamic Tables

• Enables change data capture

Tasks:

• Scheduled jobs for metadata synchronization

• Updates metadata tables

Metadata Tables:

• Track insert and last updated datetime

• Monitor Dynamic Table changes

• Support audit and compliance