Product: Semarchy xDM Snowflake Native App
Version: 2025.1.x LTS
Platforms: Snowflake (hybrid tables data location, external PostgreSQL repository)
Author: Kim Schneider



Need

You need to replace 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. This article gives the procedure, step by step, with the expected result of each step.

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


Summarized Solution


  1. Clone PRD and back up DEV with database CLONE.
  2. Record the DEV data location UUID.
  3. Empty the DEV tables with DELETE, in repeated passes, and check that DEV is empty.
  4. Reload DEV from the PRD clone, in repeated passes.
  5. Verify that the row counts match the PRD clone.
  6. Realign the data location sequences in Snowflake.
  7. Realign the repository sequences SEQ_LOADID and SEQ_BATCHID in PostgreSQL.
  8. Run the final checks, then drop the clones after a few days.


Detailed Solution


Before you start

Check these four conditions:

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

Replace the placeholders in the statements below with your own names:

Placeholder

Meaning

<PRD_DB>.<PRD_SCHEMA>

PRD data location

<PRD_CLONE>

Database clone of PRD, used as the data source

<DEV_DB>.<DEV_SCHEMA>

DEV data location to refresh

<DEV_DB>_BACKUP

Backup clone of DEV

<repository_schema>

Schema of the external repository database

<native_app_name>

Name of the installed Native App


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

Expected: both databases exist before you go on.


2. Record the DEV data location UUID

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

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.


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



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: failures in last pass: 0.


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.


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.

Expected: every DEV sequence has a next_value at least equal to its PRD value, with the application as owner.


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);

8. Final checks

The refresh is complete when all of these checks pass. Keep the clones for a few days, then drop them once you are satisfied.


Checklist

Check

Expected

☐

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

Returns the value you saved in step 2

☐

SHOW GRANTS TO APPLICATION <native_app_name>;

USAGE on the database and warehouse; on the schema USAGE, CREATE FUNCTION, CREATE PROCEDURE, CREATE SEQUENCE, CREATE STAGE, CREATE HYBRID TABLE and CREATE VIEW

☐

Datasource Connection Initialization Query field

Empty

☐

Application and a small test load in DEV

Application resumed, test load completes

☐

New IDs

Load and batch IDs above the maximum in DL_BATCH; entity and match group IDs starting at the PRD sequence values