Integration & API

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

Views

vw_datasource_active_attributes

Purpose: All active polymorphic versions with complete metadata for code generation.

Use cases: UI display, programmatic access, code generation input.

Example:

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: All polymorphic versions (active and inactive) for schema analysis and version history.

Use cases: Schema drift analysis, understanding all possible variations.

Example:

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: Version information for schema evolution tracking.

Use cases: Monitoring schema drift, tracking schema evolution.

Example:

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 (internal use during scanning).

Parameters:

• DATASOURCE_ID (varchar)

• DATASOURCE_VERSION_ID (varchar)

• ATTRIBUTE_DATA (varchar) - JSON string containing attribute data

Note: Typically not called directly by users; used internally during scan process.

sp_upsert_virtual_datasource_attribute

Purpose: Creates or updates virtual attributes.

Parameters:

• DATASOURCE_ID (varchar)

• ATTRIBUTE_NAME (varchar) - No spaces allowed

• SOURCE_DATA_TYPE (varchar)

• PLATFORM_DATA_TYPE (varchar) - Snowflake data type

• DATASOURCE_ATTRIBUTE_POLYMORPHIC_VERSION_ID_PARENT_ARRAY (varchar) - Optional, for array-level virtual attributes

• TRANSFORMATION_EXPRESSION (varchar) - Required SQL expression

• W_QUESTION_CATEGORY (varchar) - Optional, for Cortex Analyst

• ATTRIBUTE_DESCRIPTION (varchar) - Optional

• ATTRIBUTE_ID (varchar) - Optional, for updates

Example:

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

sp_update_attribute_polymorphic_versions

Purpose: Bulk updates to polymorphic version metadata.

Parameters: All JSON strings containing updates:

• UPDATED_VERSION_ROWS_SOURCE_SCHEMA - Source schema updates

• UPDATED_VERSION_ROWS_DESTINATION_SCHEMA - Destination schema updates

• UPDATED_VERSION_ROWS_TRANSFORMATION - Transformation updates

• UPDATED_VERSION_ROWS_CONSOLIDATION - Consolidation updates

• UPDATED_VERSION_ROWS_GOVERNANCE - Governance updates

• UPDATED_VERSION_ROWS_SEMANTIC_MODEL - Semantic model updates

Usage: Bulk updates via JSON input; multiple attributes in single call.

---

update_attribute_metadata_core

Purpose: Bulk update via CSV integration.

Parameters:

• USER_NAME (varchar) - User performing the update

• ATTRIBUTE_DATA (varchar) - JSON string with attribute data

• FILE_PATH (varchar) - Path to CSV file in Snowflake stage (e.g., @stage/path/to/file.csv)

Process:

1. Upload CSV to Snowflake stage

2. Call core.update_attribute_metadata_core with file path

3. Procedure reads CSV and applies updates

4. Review returned results for success/failure

CSV requirements:

• Must include _ID or POLYMORPHIC_VERSION_ID column for identification

• Include columns for fields to update

• Follow DataPancake column naming conventions

Supported fields: All metadata fields (data types, transformations, security policies, etc.). See DataPancake Metadata Fields.

Integration Patterns

Bulk configuration update:

1. Export current attribute configuration to CSV

2. Modify CSV with desired changes

3. Upload CSV to Snowflake stage

4. Call core.update_attribute_metadata_core

5. Verify updates

Programmatic virtual attribute creation:

1. Call core.sp_upsert_virtual_datasource_attribute for each attribute

2. Verify creation

3. Generate code

Schema drift response:

1. Monitor vw_datasource_version_all_attributes for changes

2. Identify newly activated versions (VERSION_STATUS_DATE changes)

3. Configure new versions programmatically

4. Regenerate code

Best Practices

Error handling:

• Check return values from stored procedures

• Validate input data before calling procedures

Performance:

• Use bulk operations (update_attribute_metadata_core or sp_update_attribute_polymorphic_versions) for multiple attributes

• Avoid updating attributes one-by-one

Security:

• Use appropriate roles and permissions

• Audit integration operations