Ensuring Data Share Persistence During Table Recreation in Snowflake

Ensuring Data Share Persistence During Table Recreation in Snowflake

Problem:

Infoworks pipelines in overwrite mode that write to Snowflake will drop and recreate the target table. If the target table is dropped ,the table's Object ID is deleted causing broken shares or views.

Recreating the table with the same name does not restore downstream dependencies.

Solution:

Use a Snowflake Dynamic Table as a Stable Proxy, dynamic tables have a stable Object ID.

Views/Shares will be created based on the dynamic table instead of Infoworks landing table

Base Dynamic Table Definition example

xxxxxxxxxx

CREATE OR ALTER DYNAMIC TABLE SHARED_DB.SHARED_SCHEMA.T_STABLE_SHARED_DATA

  TARGET_LAG = DOWNSTREAM

  WAREHOUSE = WH_XS

  REFRESH_MODE = FULL

AS

SELECT * FROM LANDING_DB.LANDING_SCHEMA.INFOWORKS_LANDING_TABLE;

Replace dynamic table db, schema and table name along with Infoworks landing table name and warehouse.

Consumers must now reference only the Dynamic Table, never the Infoworks landing table.

The dynamic table must be refreshedto update data from Infoworks landing table.

Below are the options to refresh the table automatically based on usecase and required refresh frequency.

Option 1: Automatic Refresh Using TARGET_LAG

xxxxxxxxxx

ALTER DYNAMIC TABLE SHARED_DB.SHARED_SCHEMA.T_STABLE_SHARED_DATA

SET TARGET_LAG = '12 hours';

The dynamic table is refreshed every 12 hours

Option 2: Cron‑Based Snowflake Task

A Snowflake Task periodically triggers a refresh.

xxxxxxxxxx

CREATE OR REPLACE TASK SHARED_DB.SHARED_SCHEMA.REFRESH_DYNAMIC_TABLE

  WAREHOUSE = WH_XS

  SCHEDULE = 'USING CRON 0 14 * * * UTC'

AS

  ALTER DYNAMIC TABLE SHARED_DB.SHARED_SCHEMA.T_STABLE_SHARED_DATA REFRESH;

ALTER TASK SHARED_DB.SHARED_SCHEMA.REFRESH_DYNAMIC_TABLE RESUME;

Recommended when batch‑aligned refresh is acceptable

Option 3: Event‑Driven Refresh via Infoworks Bash Node (Snowflake API)

Infoworks workflows can include a Bash node that runs after the Snowflake export job and explicitly triggers a Dynamic Table refresh using the Snowflake REST API. An alternate option is to use pipeline post hook job to call the snowflake rest api.

This provides true event‑driven behavior.

Get Snowflake OAuth Token and call Snowflake API to Refresh Dynamic Table

xxxxxxxxxx

ACCESS_TOKEN=$(curl -s -X POST \

  -H "Content-Type: application/json" \

  -d '{

    "grant_type":"client_credentials",

    "scope":"session:role:SYSADMIN"

  }' \

  https://.snowflakecomputing.com/oauth/token \

  | jq -r .access_token)

curl -X POST \

  -H "Authorization: Bearer ${ACCESS_TOKEN}" \

  -H "Content-Type: application/json" \

  https://.snowflakecomputing.com/api/v2/statements \

  -d '{

    "statement": "ALTER DYNAMIC TABLE SHARED_DB.SHARED_SCHEMA.T_STABLE_SHARED_DATA REFRESH",

    "warehouse": "WH_XS",

    "role": "SYSADMIN"

  }'

Env : Infoworks 5.5.3