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 with INCLUDE_IN_CODE_GEN = TRUE, DataPancake generates a column:

1. Column name: Uses ALIAS_NAME if provided, otherwise POLYMORPHIC_ATTRIBUTE_NAME → CODE_GENERATED_COLUMN_NAME

2. Type casting: Uses DATA_PLATFORM_DATA_TYPE with precision/scale; applies datetime formats if USE_DATETIME_FORMAT = TRUE

3. Value extraction: Extracts from source using ATTRIBUTE_PATH; handles nested structures and arrays

4. Transformation:

   • If TRANSFORMATION_TYPE = 'No Transformation': Applies NULL_VALUE_EXPRESSION (if provided)

   • If TRANSFORMATION_TYPE = 'SQL Expression': Applies TRANSFORMATION_EXPRESSION (replaces {attribute_name} placeholder)

5. Inclusion: Only if INCLUDE_IN_CODE_GEN = TRUE AND PARENT_INCLUDE_IN_CODE_GEN = TRUE AND VERSION_STATUS = 'active'

---

Example: Column Generation

Attribute: customer.email with polymorphic version email_str

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:

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

With transformation:

• TRANSFORMATION_TYPE = 'SQL Expression'

• TRANSFORMATION_EXPRESSION = 'UPPER({attribute_name})'

Generated SQL:

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

---

Array Table Generation

Object arrays (ARRAY_TYPE = 'object'):

• Separate Dynamic Table created for array contents

• Named using CREATE_OBJECT_NAME_ALIAS_NAME or default {root_table_name}_array_object

• Foreign key columns link array table to parent (configured in Foreign Keys section)

Primitive arrays (ARRAY_TYPE = 'primitive'):

• 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 explicit type casting (::DATA_TYPE) in generated SQL to prevent technical debt from implicit conversions.

Example:

-- DataPancake (explicit casting)
json_data:"customer"."email"::VARCHAR(255) AS customer_email

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

---

Code Generation Process

1. Collect active attributes: Query vw_datasource_active_attributes where INCLUDE_IN_CODE_GEN = TRUE and PARENT_INCLUDE_IN_CODE_GEN = TRUE

2. Generate column statements: For each active version, generate SQL with type casting and transformations

3. Generate array tables: Separate Dynamic Tables for object arrays with foreign key relationships

4. Generate root table: Combine all root-level columns with table-level settings

5. Generate views: Semantic layer views with SEMANTIC_LAYER_EXPRESSION and security policies

Metadata Tracking

DataPancake can generate:

• Streams: Track changes to Dynamic Tables (change data capture)

• Tasks: Scheduled metadata synchronization

• Metadata tables: Track insert/update timestamps for audit and compliance