Integration & API

Programmatic access to attributes via system views and stored procedures for automation and external system integration.

Views for Attributes

vw_datasource_active_attributes

Purpose: Comprehensive view of all active attributes

Contents:

• Both attribute and polymorphic version data

• All active polymorphic versions

• Complete metadata for code generation

Use Cases:

• UI display

• Programmatic access to active attributes

• Code generation input

• Attribute configuration

Example Query:

SELECT * 
FROM vw_datasource_active_attributes 
WHERE datasource_id = 'your-datasource-id'
  AND INCLUDE_IN_CODE_GEN = TRUE
ORDER BY ATTRIBUTE_PATH;

vw_datasource_all_attributes

Purpose: Includes all polymorphic versions regardless of status

Contents:

• All polymorphic versions (active and inactive)

• Complete version history

• Schema evolution tracking

Use Cases:

• Schema analysis

• Version history review

• Understanding all possible variations

• Schema drift analysis

Example Query:

SELECT 
  ATTRIBUTE_PATH,
  POLYMORPHIC_ATTRIBUTE_NAME,
  VERSION_STATUS,
  VERSION_STATUS_DATE
FROM vw_datasource_all_attributes 
WHERE datasource_id = 'your-datasource-id'
ORDER BY ATTRIBUTE_PATH, POLYMORPHIC_ATTRIBUTE_NAME;

vw_datasource_version_all_attributes

Purpose: Includes version information for schema evolution tracking

Contents:

• Version numbers

• Version creation dates

• Schema change tracking

• Polymorphic version history

Use Cases:

• Monitoring schema drift

• Tracking schema evolution

• Version comparison

• Change detection

Example Query:

SELECT 
  DATASOURCE_NAME,
  VERSION_NUMBER,
  ATTRIBUTE_PATH,
  POLYMORPHIC_ATTRIBUTE_NAME,
  VERSION_STATUS,
  VERSION_STATUS_DATE
FROM vw_datasource_version_all_attributes 
WHERE datasource_id = 'your-datasource-id'
  AND VERSION_STATUS = 'active'
ORDER BY VERSION_NUMBER DESC, ATTRIBUTE_PATH;

Stored Procedures

sp_upsert_attribute

Purpose: Creates or updates attributes from scan results

Usage:

• Used internally during scanning process

• Not typically called directly by users

• Handles attribute creation from scan data

Parameters:

• DATASOURCE_ID - The data source identifier

• DATASOURCE_VERSION_ID - The version identifier

• ATTRIBUTE_DATA - JSON string containing attribute data

---

sp_upsert_virtual_datasource_attribute

Purpose: Creates or updates virtual attributes

Usage:

• Create custom virtual attributes

• Update existing virtual attributes

• Programmatic attribute creation

Parameters:

• DATASOURCE_ID - The data source identifier

• ATTRIBUTE_NAME - Name of the virtual attribute

• SOURCE_DATA_TYPE - Source data type

• PLATFORM_DATA_TYPE - Snowflake data type

• TRANSFORMATION_EXPRESSION - SQL transformation expression

• W_QUESTION_CATEGORY - Cortex Analyst category (optional)

• ATTRIBUTE_DESCRIPTION - Description (optional)

• ATTRIBUTE_ID - For updates (optional)

Example:

CALL core.sp_upsert_virtual_datasource_attribute(
  'datasource-id',
  'full_name',
  'string',
  'VARCHAR',
  '{first_name} || '' '' || {last_name}',
  '',
  'Full name calculated from first and last name',
  NULL
);

---

sp_update_attribute_polymorphic_versions

Purpose: Updates polymorphic version metadata

Usage:

• Bulk updates to attribute metadata

• Programmatic configuration

• Integration workflows

Parameters:

• UPDATED_VERSION_ROWS_SOURCE_SCHEMA - JSON with source schema updates

• UPDATED_VERSION_ROWS_DESTINATION_SCHEMA - JSON with destination schema updates

• UPDATED_VERSION_ROWS_TRANSFORMATION - JSON with transformation updates

• UPDATED_VERSION_ROWS_CONSOLIDATION - JSON with consolidation updates

• UPDATED_VERSION_ROWS_GOVERNANCE - JSON with governance updates

• UPDATED_VERSION_ROWS_SEMANTIC_MODEL - JSON with semantic model updates

Supports:

• Bulk updates via JSON input

• Multiple attribute updates in single call

• Different update categories

---

update_attribute_metadata_core

Purpose: Bulk update via CSV integration

Usage:

• External integration workflows

• Spreadsheet-based updates

• Bulk configuration changes

Parameters:

• USER_NAME - User performing the update

• ATTRIBUTE_DATA - JSON string with attribute data

• FILE_PATH - Path to CSV file in Snowflake stage

Process:

1

Upload CSV to Snowflake stage

1. Upload your CSV file to the configured Snowflake stage.

2

Call procedure with file path

1. Invoke update_attribute_metadata_core with the stage file path and other parameters.

3

Procedure processes CSV and updates attributes

1. The procedure reads the CSV and applies updates to attributes.

4

Review results

1. Procedure returns update results; review for success/failure and details.

Supported Fields (examples):

• Data types, precision, scale

• DateTime formats

• Transformation expressions

• Null value expressions

• Alias names

• Include in code gen flags

• Semantic layer configurations

• Security policy settings

• And many more metadata fields

Bulk Operations

CSV Upload Integration

Process:

1

Prepare CSV

1. Prepare a CSV file containing attribute updates. Must include _ID or POLYMORPHIC_VERSION_ID column for identification.

2

Upload to Snowflake stage

1. Upload the CSV to your Snowflake stage.

3

Call update procedure

1. Call update_attribute_metadata_core with the file path or use the equivalent API.

4

Review update results

1. Inspect the returned results for success/failure details and act accordingly.

CSV Format:

• Must include _ID or POLYMORPHIC_VERSION_ID column

• Include columns for fields to update

• Follow DataPancake column naming conventions

Example CSV:

POLYMORPHIC_VERSION_ID,ALIAS_NAME,INCLUDE_IN_CODE_GEN,TRANSFORMATION_EXPRESSION
version-id-1,customer_email,TRUE,
version-id-2,order_total,TRUE,ROUND({attribute_name}, 2)

Programmatic Integration

Python Example:

from snowflake.snowpark import Session

# Update attribute metadata
session.call(
    'core.update_attribute_metadata_core',
    user_name='your-user',
    attribute_data=json.dumps(attribute_updates),
    file_path='@stage/path/to/file.csv'
)

SQL Example:

-- Update via stored procedure
CALL core.sp_update_attribute_polymorphic_versions(
  '{"updates": [...]}',
  '{"updates": [...]}',
  '{"updates": [...]}',
  '{"updates": [...]}',
  '{"updates": [...]}',
  '{"updates": [...]}'
);

Integration Patterns

1

Bulk Configuration Update

• Export current attribute configuration to CSV

• Modify CSV with desired changes

• Upload CSV to Snowflake stage

• Call update_attribute_metadata_core

• Verify updates

2

Programmatic Virtual Attribute Creation

• Identify attributes to create

• Call sp_upsert_virtual_datasource_attribute for each

• Verify creation

• Generate code

3

Schema Drift Response

• Monitor vw_datasource_version_all_attributes for changes

• Identify newly activated versions

• Configure new versions programmatically

• Regenerate code

Best Practices for Integration

Error Handling

• Always check return values from stored procedures

• Handle errors gracefully

• Log integration operations

• Validate input data before calling procedures

Performance

• Batch updates when possible

• Use bulk operations for multiple attributes

• Avoid updating attributes one-by-one

• Monitor procedure execution time

Security

• Use appropriate roles and permissions

• Validate user input

• Audit integration operations

• Follow least privilege principles