# Schema Transformation / Consolidation - Bulk Update

{% hint style="info" %}

### Ensure you have completed [Initialization Guide](https://docs.datapancake.com/guides/how-to-guides-bulk-upsert-via-csv/database-object-initialization/schema-transformation-consolidation-intialization)

{% endhint %}

### Open a new SQL file Snowflake workspace

![](https://colony-recorder.s3.amazonaws.com/files/2026-01-23/4b391681-1194-493e-87ae-5b5e550c0857/ascreenshot_294d708fbe9e4bff8033a5f4c50a35c2_text_export.jpeg)

### Run a select statement for DATAPANCAKE.CORE.VW\_DATASOURCE\_SCHEMA\_CONSOLIDATION

```sql
SELECT
-- REQUIRED COLUMNS FOR BULK UPDATE
TRANSFORMATION_ID,  -- Unique ID for upsert operations - use {new_id} for new records
DATASOURCE_ID,
-- VIEW ONLY COLUMNS
DATASOURCE_NAME,
DATASOURCE_TAG,
-- EDITABLE COLUMNS FOR BULK UPDATE
TRANSFORMATION_NAME,
TRANSFORMATION_TYPE,
SCHEMA_ATTRIBUTE_PATH_SEARCH,
SCHEMA_REGULAR_EXPRESSION_SEARCH,
SCHEMA_REGULAR_EXPRESSION_REPLACE,
STRING_DELIMITER_VALUE,
STRING_KEY_VALUE_SEPARATOR,
STRING_WRAPPER_VALUE,
STRING_FIELD_WIDTHS
FROM datapancake.core.vw_datasource_schema_transformation
ORDER BY DATASOURCE_NAME, TRANSFORMATION_NAME;
```

{% hint style="info" %}
You can choose which editable fields you wish to include in your select statement.
{% endhint %}

![](https://colony-recorder.s3.amazonaws.com/files/2026-01-23/b327ab4a-e541-4bb0-8fff-0fdbd1da96c5/ascreenshot_0c40b3c393534615b71e22a54f94ee76_text_export.jpeg)

### Verify the query successfully returns a list of attribute metadata

![](https://colony-recorder.s3.amazonaws.com/files/2026-01-23/6854ca13-27aa-47e2-9088-b96f64feed73/ascreenshot_fce689aaf5be41f1838592dbaa8545ac_text_export.jpeg)

### Download the results as a .csv file

![](https://colony-recorder.s3.amazonaws.com/files/2026-01-23/f70b1a0a-d38a-43dd-ab18-8cf961d1f7db/ascreenshot_dc8fef5b5b7840b799fee9afd14c08fa_text_export.jpeg)

### Open the .csv file in either Excel or Google Sheets

![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-24/166d997b-67c7-44df-a522-984aec4ab6e7/ascreenshot.jpeg?tl_px=109,0\&br_px=1486,769\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=524,129)

### Edit any editable columns as needed

![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-24/3dcc3276-5b44-4d03-bc5e-d511fb39a13c/ascreenshot.jpeg?tl_px=164,0\&br_px=1541,769\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=643,123)

### Save (Excel) or download (Google Sheets) the file

![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-24/1d878217-47d1-4e0b-9beb-5b32f76fd18a/ascreenshot.jpeg?tl_px=0,46\&br_px=1376,815\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=459,277)

### Load the .csv into your stage

![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-25/b95a896b-a5e6-41e4-bf02-8f6ebeca882f/ascreenshot.jpeg?tl_px=0,8\&br_px=1376,777\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=239,276)

![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-25/e03c17ff-fde1-4bd3-b412-c33e3fe39724/ascreenshot.jpeg?tl_px=164,0\&br_px=1541,769\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=1005,7)

![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-25/ff23955f-4c43-4781-885a-58c1cd46cc6b/ascreenshot.jpeg?tl_px=68,0\&br_px=1445,769\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=524,257)

![](https://ajeuwbhvhr.cloudimg.io/https://colony-recorder.s3.amazonaws.com/files/2025-06-25/f34d74ef-8a5b-413a-a39a-20aa0849aeab/ascreenshot.jpeg?tl_px=164,223\&br_px=1541,993\&force_format=jpeg\&q=100\&width=1120.0\&wat=1\&wat_opacity=1\&wat_gravity=northwest\&wat_url=https://colony-recorder.s3.amazonaws.com/images/watermarks/FB923C_standard.png\&wat_pad=664,372)

### Run the bulk update stored procedure

{% hint style="info" %}
Modify the \<placeholders> with same database and schema you uploaded the stored procedure to.
{% endhint %}

When the stored procedure completes, the attribute metadata file will be moved to the archive folder.

```sql
call <database>.<schema>.sp_update_datapancake_datasource_schema_transformation_metadata_core(
    '@<stage_name>/<staged_filename>.csv',
    '@<stage_name>/<archive_folder>/',
    '<snowflake_username>'
);
```

### Verify the integration completed successfully by running the following query

```sql
select * from datapancake.core.vw_integration_process
where user_name = 'BWOLFF'
order by integration_process_date desc;
```