Array Foreign Key - Bulk Update

Select and export the array foreign key metadata to a .csv file, edit in a spreadsheet like Google Sheets or Excel, then perform a bulk update in DataPancake.

---

Array Foreign Key - Bulk Update

Ensure you have completed the Array Foreign Key - Initialization guide.

This guide explains how to export foreign key metadata, edit the metadata in a spreadsheet, and apply the changes through a CSV bulk update.

The examples use the sample Real Estate data source. The exact data source ID and available child tables may vary by environment.

Open a new SQL worksheet in Snowflake

Open Snowsight and create a new SQL worksheet.

Select a role, warehouse, database, and schema with access to the DataPancake application and the Snowflake stage used for bulk-update files.

Export the foreign key metadata

Run the following query to retrieve foreign key metadata from DATAPANCAKE.CORE.VW_DATASOURCE_FOREIGN_KEYS.

SELECT
-- REQUIRED COLUMNS FOR BULK UPDATE
FOREIGN_KEY_ID, -- Unique ID for upsert operations - use {new_id} for new records
ATTRIBUTE_ID_CHILD_TABLE,
-- VIEW ONLY COLUMNS
DATASOURCE_ID,
DATASOURCE_NAME,
PARENT_TABLE_WITHOUT_PREFIX,
CHILD_TABLE_WITHOUT_PREFIX,
CHILD_TABLE_PATH,
CHILD_TABLE_LEVEL,
-- EDITABLE COLUMNS FOR BULK UPDATE
PARENT_ATTRIBUTE_NAME,
CHILD_ATTRIBUTE_NAME,
INCLUDE_COLUMN_IN_ROW_ACCESS_POLICY,
ROW_ACCESS_POLICY_COLUMN_ORDER
FROM datapancake.core.vw_datasource_foreign_keys
--where datasource_id = '<your datasource id>'
ORDER BY DATASOURCE_NAME, CHILD_TABLE_LEVEL, CHILD_TABLE_WITHOUT_PREFIX, PARENT_ATTRIBUTE_NAME;

Uncomment the WHERE clause and enter the target data source ID to limit the export to a single data source. Filtering the export reduces the possibility of changing foreign keys for an unrelated data source.

For the Real Estate demonstration, filter the query to the sample Real Estate data source in your environment.

Run the query and review the results

Execute the query.

Confirm that the results contain the Real Estate data source and its child tables.

Use the following columns to identify each child table:

• CHILD_TABLE_WITHOUT_PREFIX

• CHILD_TABLE_PATH

• CHILD_TABLE_LEVEL

• ATTRIBUTE_ID_CHILD_TABLE

Download the results as a CSV file

Download the query results as a .csv file.

Open the CSV file

Open the downloaded file in Excel, Google Sheets, or another spreadsheet editor that preserves CSV formatting.

Confirm that the header row contains all exported columns.

Review the CSV columns

Column	Usage
FOREIGN_KEY_ID	Preserve the existing identifier when updating a record. Enter {new_id} when creating a new foreign key.
ATTRIBUTE_ID_CHILD_TABLE	Required identifier for the child table attribute. Preserve the exported value.
DATASOURCE_ID	View-only data source identifier. Do not edit.
DATASOURCE_NAME	View-only data source name. Do not edit.
PARENT_TABLE_WITHOUT_PREFIX	View-only parent table name. Do not edit.
CHILD_TABLE_WITHOUT_PREFIX	View-only child table name. Do not edit.
CHILD_TABLE_PATH	View-only path that identifies the child table within the source structure. Do not edit.
CHILD_TABLE_LEVEL	View-only nesting level of the child table. Do not edit.
PARENT_ATTRIBUTE_NAME	Parent attribute whose value should be added to the child table.
CHILD_ATTRIBUTE_NAME	Name of the foreign key column created in the child table.
INCLUDE_COLUMN_IN_ROW_ACCESS_POLICY	Enter TRUE or FALSE to control whether the generated child-table column is included in the row access policy.
ROW_ACCESS_POLICY_COLUMN_ORDER	Enter the column's position in the row access policy when applicable. Leave blank when the column is not included in the policy.

Do not change ATTRIBUTE_ID_CHILD_TABLE or any of the view-only columns. When updating an existing foreign key, preserve its current FOREIGN_KEY_ID. Use {new_id} only when creating a new foreign key record.

Add property_id_pk to selected Real Estate child tables

For this demonstration, select a few of the Real Estate child tables that should receive the parent property's identifier.

For each selected child table:

1. Enter {new_id} in FOREIGN_KEY_ID.

2. Preserve the exported ATTRIBUTE_ID_CHILD_TABLE.

3. Enter property_id_pk in PARENT_ATTRIBUTE_NAME.

4. Enter property_id_pk in CHILD_ATTRIBUTE_NAME.

5. Enter FALSE in INCLUDE_COLUMN_IN_ROW_ACCESS_POLICY.

6. Leave ROW_ACCESS_POLICY_COLUMN_ORDER blank.

Example:

FOREIGN_KEY_ID	ATTRIBUTE_ID_CHILD_TABLE	CHILD_TABLE_WITHOUT_PREFIX	PARENT_ATTRIBUTE_NAME	CHILD_ATTRIBUTE_NAME	INCLUDE_COLUMN_IN_ROW_ACCESS_POLICY	ROW_ACCESS_POLICY_COLUMN_ORDER
{new_id}	Preserve exported value	First selected child table	property_id_pk	property_id_pk	FALSE
{new_id}	Preserve exported value	Second selected child table	property_id_pk	property_id_pk	FALSE
{new_id}	Preserve exported value	Third selected child table	property_id_pk	property_id_pk	FALSE

The table names shown in the CSV depend on the current Real Estate data source schema. Select child tables that do not already contain the intended property_id_pk foreign key relationship.

Save the CSV file

Save or download the edited spreadsheet as a CSV file.

Use a descriptive filename, such as:

real_estate_foreign_keys_YYYYMMDD.csv

Confirm that the saved filename contains exactly one .csv extension.

The stage path used in the stored procedure call must exactly match the uploaded filename.

Upload the CSV to a Snowflake stage

In Snowflake, navigate to the database, schema, and stage used for DataPancake bulk-update files.

Select + Files or the equivalent upload action and upload the edited CSV.

Verify the staged filename

Confirm that the uploaded CSV appears in the stage.

Note the exact stage path and filename. You will use both values in the stored procedure call.

Run the bulk update stored procedure

Run the same array foreign key bulk-update procedure used for existing foreign key files.

Replace the placeholders with values for your environment.

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

Example structure:

call MY_DATABASE.MY_SCHEMA.sp_update_datapancake_array_foreign_key_metadata_core(
    '@MY_DATABASE.MY_SCHEMA.DATAPANCAKE_FILES/real_estate_foreign_keys_YYYYMMDD.csv',
    '@MY_DATABASE.MY_SCHEMA.DATAPANCAKE_FILES/ARCHIVED_FILES/',
    'MY_USERNAME'
);

The stage path and filename must exactly match the uploaded file.

Check the following values before running the procedure:

• Database

• Schema

• Stage

• Optional folder

• Filename

• File extension

• Snowflake username

Review the returned processing message

Execute the stored procedure call.

The procedure returns a processing message after it evaluates the uploaded file. Review the complete returned message and confirm that the file was processed successfully.

The screenshot for this step should capture the full message returned by the current version of the stored procedure.

The returned processing message is the primary confirmation that the file was accepted and processed. Review the entire message before continuing to verification.

Confirm that the file was archived

After a successful update, confirm that the processed CSV was moved to the configured archive folder.

The file should:

• Appear in the archive folder.

• No longer appear in its original stage location.

Verify the new foreign keys

Rerun the export query using the same Real Estate data source filter.

SELECT
-- REQUIRED COLUMNS FOR BULK UPDATE
FOREIGN_KEY_ID,
ATTRIBUTE_ID_CHILD_TABLE,
-- VIEW ONLY COLUMNS
DATASOURCE_ID,
DATASOURCE_NAME,
PARENT_TABLE_WITHOUT_PREFIX,
CHILD_TABLE_WITHOUT_PREFIX,
CHILD_TABLE_PATH,
CHILD_TABLE_LEVEL,
-- EDITABLE COLUMNS FOR BULK UPDATE
PARENT_ATTRIBUTE_NAME,
CHILD_ATTRIBUTE_NAME,
INCLUDE_COLUMN_IN_ROW_ACCESS_POLICY,
ROW_ACCESS_POLICY_COLUMN_ORDER
FROM datapancake.core.vw_datasource_foreign_keys
WHERE datasource_id = '<your datasource id>'
ORDER BY DATASOURCE_NAME, CHILD_TABLE_LEVEL, CHILD_TABLE_WITHOUT_PREFIX, PARENT_ATTRIBUTE_NAME;

For each child table updated in the CSV, confirm that:

• FOREIGN_KEY_ID now contains a generated identifier instead of {new_id}.

• ATTRIBUTE_ID_CHILD_TABLE identifies the expected child table attribute.

• PARENT_ATTRIBUTE_NAME is property_id_pk.

• CHILD_ATTRIBUTE_NAME is property_id_pk.

• INCLUDE_COLUMN_IN_ROW_ACCESS_POLICY is FALSE.

• The correct child table is shown in CHILD_TABLE_WITHOUT_PREFIX and CHILD_TABLE_PATH.

Optional: Review the integration process

You can also review the DataPancake integration process log.

Replace <snowflake_username> with the username passed to the bulk-update procedure.

SELECT *
FROM datapancake.core.vw_integration_process
WHERE user_name = '<snowflake_username>'
ORDER BY integration_process_date DESC;

Review the latest entry associated with the uploaded CSV.

---

Troubleshooting

The staged file could not be found

Confirm that:

• The file was uploaded successfully.

• The stage path is correct.

• The filename in the procedure call exactly matches the staged filename.

• The filename does not contain a duplicated .csv extension.

• The active Snowflake role has access to the stage.

You can list the staged files to confirm the exact filename:

LIST @<database>.<schema>.<stage>;

A new foreign key was not created

Confirm that:

• FOREIGN_KEY_ID contains exactly {new_id}.

• ATTRIBUTE_ID_CHILD_TABLE contains the original exported identifier.

• PARENT_ATTRIBUTE_NAME contains property_id_pk.

• CHILD_ATTRIBUTE_NAME contains property_id_pk.

• The required column headers were not renamed.

• The intended child table row was edited.

An existing foreign key was duplicated

Use {new_id} only when creating a new foreign key.

When updating an existing relationship, preserve the generated value already present in FOREIGN_KEY_ID.

The wrong child table was updated

Use the following view-only fields together to identify the intended table before editing:

• CHILD_TABLE_WITHOUT_PREFIX

• CHILD_TABLE_PATH

• CHILD_TABLE_LEVEL

• ATTRIBUTE_ID_CHILD_TABLE

Do not rely on the child table name alone when similarly named tables exist at different nesting levels.

The row access policy configuration was not applied

Confirm that:

• INCLUDE_COLUMN_IN_ROW_ACCESS_POLICY contains TRUE or FALSE.

• ROW_ACCESS_POLICY_COLUMN_ORDER is populated when required.

• The data source has the required security policy features enabled.

• The configured policy supports the selected child-table column.