> For the complete documentation index, see [llms.txt](https://docs.datapancake.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.datapancake.com/guides/how-to-guides-bulk-upsert-via-csv/usage/array-foreign-key-bulk-update.md).

# Array Foreign Key - Bulk Update

***

## Array Foreign Key - Bulk Update

{% hint style="info" %}

#### Ensure you have completed the [Array Foreign Key - Initialization](app://-/database-object-initialization/array-foreign-key-initialization) guide.

{% endhint %}

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.

![](https://colony-recorder.s3.amazonaws.com/files/2026-08-05/68632df4-c5d3-4fff-a936-35d9da4ad590/action-d2e23de045f24392ad681c14f052b650_257db268e1564d32ac6691bd16cc10af_text_export.jpeg)

#### Export the foreign key metadata

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

```sql
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;
```

{% hint style="info" %}
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.
{% endhint %}

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

![](https://colony-recorder.s3.amazonaws.com/files/2026-08-05/bd623a2a-bfec-4296-8fde-47763b4932c5/action-28eb42ab405a4b54b19ee7150af6f765_56eed7ea1437478c8e0e89fb156c420f_text_export.jpeg)

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

![](https://colony-recorder.s3.amazonaws.com/files/2026-08-05/a48679c1-8249-433e-b0fe-70d440dae979/action-fec9a831b92c459ca485368a7d7ac21b_8a163a021f3040028444862733da9d5e_text_export.jpeg)

#### Download the results as a CSV file

Download the query results as a `.csv` file.

![](https://colony-recorder.s3.amazonaws.com/files/2026-08-05/fe6e449b-9975-4dff-86ea-31ee2b320709/action-efa2449906ea4481a66997db40645ae8_df74d1a3f62545889d3caa86e68c1841_text_export.jpeg)

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

![](https://colony-recorder.s3.amazonaws.com/files/2026-08-05/1173e8f9-078f-4b0b-b5fc-c593a0c71d92/action-9c61b88b7a944a7483c99c2467f065d3_6a29e9c73604451bbc3c11b3b0c335c9_text_export.jpeg)

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

{% hint style="info" %}
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.
{% endhint %}

#### 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`                                  | <p><br></p>                        |
| `{new_id}`       | Preserve exported value     | Second selected child table   | `property_id_pk`        | `property_id_pk`       | `FALSE`                                  | <p><br></p>                        |
| `{new_id}`       | Preserve exported value     | Third selected child table    | `property_id_pk`        | `property_id_pk`       | `FALSE`                                  | <p><br></p>                        |

{% hint style="info" %}
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.
{% endhint %}

![](https://colony-recorder.s3.amazonaws.com/files/2026-08-05/4881fbee-23d3-4b96-b20e-9414412149d8/action-b18102fb9a2449f2b9d226f7e8cb4ba3_e62a39a769dd4210a50ba0e2b26e242b_text_export.jpeg)

![](https://colony-recorder.s3.amazonaws.com/files/2026-08-05/765a64ed-c822-4f69-b5fa-0a10cf930bc5/action-d11c5ded37344ae7b980b2335eb86eb9_eb25e71ab86a4dcca0ab6b659bd9f6e6_text_export.jpeg)

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

{% hint style="info" %}
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.
{% endhint %}

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

![](https://colony-recorder.s3.amazonaws.com/files/2026-08-05/04f770cd-ef5c-42ca-959e-5f6e0cf08c37/action-e3099445604440659639f432623368e2_9ec697f6709c4126bb4adf03710557aa_text_export.jpeg)

![](https://colony-recorder.s3.amazonaws.com/files/2026-08-05/370c9681-a644-4bd6-978a-ed0c6a7ac50c/action-b968f5c91771411c892bf9d75da2888d_a82b263e1c8444a08e7fa8f71f691b3c_text_export.jpeg)

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

![](https://colony-recorder.s3.amazonaws.com/files/2026-08-05/b1340cfe-e093-427d-8277-adc3d8831f5a/action-9317a45cb49a4dcf9360751498b0930c_7f7777094bc84dfeb27129fd343fb843_text_export.jpeg)

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

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

Example structure:

```sql
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'
);
```

{% hint style="warning" %}
The stage path and filename must exactly match the uploaded file.<br>

Check the following values before running the procedure:

* Database
* Schema
* Stage
* Optional folder
* Filename
* File extension
* Snowflake username
  {% endhint %}

![](https://colony-recorder.s3.amazonaws.com/files/2026-08-05/90b7e618-58ca-4d28-a4cb-7f1ce8d04617/action-affec076b2cc42f2b9f05ebf3d6cd05f_cc037fde78d5407f9a3ad6471883d077_text_export.jpeg)

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

{% hint style="success" %}
The returned processing message is the primary confirmation that the file was accepted and processed. Review the entire message before continuing to verification.
{% endhint %}

![](https://colony-recorder.s3.amazonaws.com/files/2026-08-05/486318d3-e24d-4a89-81b1-1c8e43a75059/action-ae72c6bc6f2b46e682b29ca1e621d8aa_22324a3b737247b3b7948e48a4629a73_text_export.jpeg)

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

```sql
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.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## 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, and the optional `goal` query parameter:

```
GET https://docs.datapancake.com/guides/how-to-guides-bulk-upsert-via-csv/usage/array-foreign-key-bulk-update.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

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.