# Integration & API

## 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:**

```sql
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:**

```sql
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:**

```sql
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:**

```sql
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](/core-concepts/attribute-metadata/datapancake-metadata-fields.md).

## 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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.datapancake.com/core-concepts/attribute-metadata/integration-and-api.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.