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


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 refreshed to 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


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.

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

ACCESS_TOKEN=$(curl -s -X POST \
  -H "Content-Type: application/json" \
  -d '{
    "grant_type":"client_credentials",
    "scope":"session:role:SYSADMIN"
  }' \
  https://<account>.snowflakecomputing.com/oauth/token \
  | jq -r .access_token)


curl -X POST \
  -H "Authorization: Bearer ${ACCESS_TOKEN}" \
  -H "Content-Type: application/json" \
  https://<account>.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