xDI

Overview

This guide replaces the contents of a DEV data location with a copy of PRD data in the xDM Snowflake Native App, using a hybrid tables data location and an external PostgreSQL repository.

Applies to: xDM Snowflake Native App 2025.1.x LTS.

Note: this procedure is adapted from the classic xDM refresh guide. It is not an officially documented Native App procedure, so test it on a non-critical environment first.

• Only the data location is refreshed. Never copy the repository from PRD to DEV.

• PRD load logs, batch logs and workflow statuses are not carried over, because they live in the repository.

• The DEV data location remains a DEV data location.

Before you start

Check these four conditions, and replace the placeholders in the statements below with your own names.

• The DEV and PRD model editions have the same physical tables, columns and data types.

• No loads, batches or jobs are running or submitted in DEV.

• Your Snowflake role has SELECT on the PRD clone, and DELETE and INSERT on the DEV data location tables.

• The application keeps its grants on the DEV database and schema.

Step 1. Clone PRD and back up DEV

Snowflake backups do not cover hybrid tables, so a database CLONE is the supported way to copy them. Take the DEV backup before you delete anything.

CREATE DATABASE <PRD_CLONE> CLONE <PRD_DB>;
CREATE DATABASE <DEV_DB>_BACKUP CLONE <DEV_DB>;

Confirm both databases exist before going on.

Step 2. Record the DEV data location UUID

Save the DEV UUID before changing anything, so you can confirm it is unchanged at the end.

SELECT UUID FROM <DEV_DB>.<DEV_SCHEMA>.DL_DATA_LOC;

DL_DATA_LOC is not modified anywhere in this procedure, so the DEV UUID stays in place. If your role cannot read the table, the application may own it. In that case, ask Semarchy support how to read it.

Step 3. Empty the DEV tables

TRUNCATE is not possible on hybrid tables that have foreign keys, so use DELETE. This script repeats passes until every table succeeds, so you do not need to work out the table order. It skips DL_DATA_LOC.

EXECUTE IMMEDIATE $$
DECLARE
  tbls RESULTSET;
  failures INTEGER DEFAULT 1;
  pass INTEGER DEFAULT 0;
BEGIN
  WHILE (failures > 0 AND pass < 10) DO
    failures := 0;
    pass := pass + 1;
    tbls := (SELECT table_name FROM <DEV_DB>.INFORMATION_SCHEMA.TABLES
             WHERE table_schema = '<DEV_SCHEMA>' AND table_type = 'BASE TABLE' AND table_name <> 'DL_DATA_LOC');
    FOR r IN tbls DO
      BEGIN
        EXECUTE IMMEDIATE 'DELETE FROM <DEV_DB>.<DEV_SCHEMA>."' || r.table_name || '"';
      EXCEPTION WHEN OTHER THEN
        failures := failures + 1;
      END;
    END FOR;
  END WHILE;
  RETURN 'Passes: ' || pass || ', failures in last pass: ' || failures;
END;
$$;

Expected result: failures in last pass: 0.

Then check that DEV is empty:

CREATE OR REPLACE TEMPORARY TABLE dev_rowcount (table_name VARCHAR, dev_rows NUMBER);

EXECUTE IMMEDIATE $$
DECLARE
  tbls RESULTSET;
  tn VARCHAR;
  d NUMBER;
  dev_t VARCHAR;
BEGIN
  tbls := (SELECT table_name FROM <DEV_DB>.INFORMATION_SCHEMA.TABLES
           WHERE table_schema = '<DEV_SCHEMA>' AND table_type = 'BASE TABLE' AND table_name <> 'DL_DATA_LOC');
  FOR r IN tbls DO
    tn := r.table_name;
    dev_t := '<DEV_DB>.<DEV_SCHEMA>."' || tn || '"';
    SELECT COUNT(*) INTO :d FROM IDENTIFIER(:dev_t);
    INSERT INTO dev_rowcount VALUES (:tn, :d);
  END FOR;
  RETURN 'Done';
END;
$$;

SELECT COUNT(*) AS tables_checked, SUM(dev_rows) AS total_rows FROM dev_rowcount;

Expected: total_rows is 0, and tables_checked equals the number of tables.

Step 4. Reload DEV from the PRD clone

Each pass loads only the tables that are still empty, so a table is never loaded twice. Tables that must wait for their parent tables load on a later pass.

EXECUTE IMMEDIATE $$
DECLARE
  tbls RESULTSET;
  failures INTEGER DEFAULT 1;
  pass INTEGER DEFAULT 0;
  tn VARCHAR;
  cols VARCHAR;
  tname VARCHAR;
  cnt INTEGER;
BEGIN
  WHILE (failures > 0 AND pass < 10) DO
    failures := 0;
    pass := pass + 1;
    tbls := (SELECT table_name FROM <DEV_DB>.INFORMATION_SCHEMA.TABLES
             WHERE table_schema = '<DEV_SCHEMA>' AND table_type = 'BASE TABLE' AND table_name <> 'DL_DATA_LOC');
    FOR r IN tbls DO
      BEGIN
        tn := r.table_name;
        tname := '<DEV_DB>.<DEV_SCHEMA>."' || tn || '"';
        SELECT COUNT(*) INTO :cnt FROM IDENTIFIER(:tname);
        IF (cnt = 0) THEN
          SELECT LISTAGG('"' || column_name || '"', ', ') WITHIN GROUP (ORDER BY ordinal_position)
            INTO :cols FROM <DEV_DB>.INFORMATION_SCHEMA.COLUMNS
            WHERE table_schema = '<DEV_SCHEMA>' AND table_name = :tn;
          EXECUTE IMMEDIATE 'INSERT INTO ' || tname || ' (' || cols || ') SELECT ' || cols ||
                            ' FROM <PRD_CLONE>.<PRD_SCHEMA>."' || tn || '"';
        END IF;
      EXCEPTION WHEN OTHER THEN
        failures := failures + 1;
      END;
    END FOR;
  END WHILE;
  RETURN 'Passes: ' || pass || ', failures in last pass: ' || failures;
END;
$$;

Expected result: failures in last pass: 0.

Step 5. Verify the row counts

This compares every DEV table with the PRD clone and lists only the tables where the counts differ.

CREATE OR REPLACE TEMPORARY TABLE rowcount_check (table_name VARCHAR, dev_rows NUMBER, prd_clone_rows NUMBER);

EXECUTE IMMEDIATE $$
DECLARE
  tbls RESULTSET;
  tn VARCHAR;
  d NUMBER;
  p NUMBER;
  dev_t VARCHAR;
  prd_t VARCHAR;
BEGIN
  tbls := (SELECT table_name FROM <DEV_DB>.INFORMATION_SCHEMA.TABLES
           WHERE table_schema = '<DEV_SCHEMA>' AND table_type = 'BASE TABLE' AND table_name <> 'DL_DATA_LOC');
  FOR r IN tbls DO
    tn := r.table_name;
    dev_t := '<DEV_DB>.<DEV_SCHEMA>."' || tn || '"';
    prd_t := '<PRD_CLONE>.<PRD_SCHEMA>."' || tn || '"';
    SELECT COUNT(*) INTO :d FROM IDENTIFIER(:dev_t);
    SELECT COUNT(*) INTO :p FROM IDENTIFIER(:prd_t);
    INSERT INTO rowcount_check VALUES (:tn, :d, :p);
  END FOR;
  RETURN 'Done';
END;
$$;

SELECT * FROM rowcount_check WHERE dev_rows <> prd_clone_rows ORDER BY table_name;

Expected: no rows returned.

Step 6. Realign the data location sequences

The reload does not copy sequences, so DEV sequences can be behind the data now in DEV (for example SEQ_<ENTITY> and SEQ_MATCHGRP). Compare them in both schemas:

SHOW SEQUENCES IN SCHEMA <PRD_CLONE>.<PRD_SCHEMA>;
SHOW SEQUENCES IN SCHEMA <DEV_DB>.<DEV_SCHEMA>;

For each DEV sequence whose next_value is lower than the PRD value, recreate it with the PRD value as its start. Snowflake has no command to set a sequence to a chosen value, and the start value cannot be changed after creation.

The application owns these sequences, so run each statement through the datasource Connection Initialization Query field with the Test button. Do not click Save. The statement then runs under the application's technical user, and the sequence keeps the correct owner.

CREATE OR REPLACE SEQUENCE <DEV_DB>.<DEV_SCHEMA>.<SEQUENCE_NAME> START = <PRD_next_value> INCREMENT = 1;

Rules for this step:

• Use CREATE OR REPLACE SEQUENCE only. ALTER SEQUENCE combined with NEXTVAL can run repeatedly and leave the value far from the target.

• Run one statement at a time, and clear the field completely before the next one. Never leave a query saved in it.

• Check the PRD sequence properties first (such as ORDER or NOORDER, and any comment), and use the same ones.

• Afterward, run SHOW SEQUENCES and check the values and the owner column.

Step 7. Realign the repository sequences

SEQ_LOADID and SEQ_BATCHID live in the external repository, not in Snowflake. Their next values must be higher than the highest load and batch IDs now in the DEV data location.

In Snowflake, find the highest IDs. Use DESCRIBE TABLE <DEV_DB>.<DEV_SCHEMA>.DL_BATCH; to find the column names.

SELECT MAX(<load_id_column>) AS max_load_id, MAX(<batch_id_column>) AS max_batch_id
FROM <DEV_DB>.<DEV_SCHEMA>.DL_BATCH;

In the repository database (PostgreSQL), read the sequences without calling NEXTVAL:

SELECT 'seq_loadid' AS seq, last_value, is_called FROM <repository_schema>.seq_loadid
UNION ALL
SELECT 'seq_batchid', last_value, is_called FROM <repository_schema>.seq_batchid;

If last_value is lower than the maximum (or equal to it with is_called = false), raise it. This statement is safe to repeat. Run it only when no loads are running in DEV.

SELECT setval('<repository_schema>.seq_loadid', <max_load_id>, true);
SELECT setval('<repository_schema>.seq_batchid', <max_batch_id>, true);

With an embedded repository these sequences are not accessible. In that case, ask Semarchy support.

Step 8. Final checks

The refresh is complete when all of these checks pass.

• ☐ The DEV UUID is unchanged: SELECT UUID FROM <DEV_DB>.<DEV_SCHEMA>.DL_DATA_LOC; returns the value you saved in Step 2.

• ☐ The application keeps its grants: SHOW GRANTS TO APPLICATION <native_app_name>; shows USAGE on the database and warehouse, and on the schema USAGE, CREATE FUNCTION, CREATE PROCEDURE, CREATE SEQUENCE, CREATE STAGE, CREATE HYBRID TABLE and CREATE VIEW.

• ☐ The Connection Initialization Query field of the datasource is empty.

• ☐ The application is resumed, and a small test load in DEV completes.

• ☐ New IDs continue after the existing ones: load and batch IDs above the maximum in DL_BATCH, and entity and match group IDs starting at the PRD sequence values.

Keep the clones for a few days, then drop them once you are satisfied.