If you have configured a cache to capture the results from a Java enricher, you may want to migrate the cache from DEV to QA and PROD when you deploy your model towards Production. This allows you to retain the results and potentially save processing time and costs as you won't need to call out to the external service again in QA and PROD.

There are multiple ways to perform this migration. This article covers the most common use cases.

NOTES:

  • If your Production environment is on a different database server, these SQL queries won't work because you won't be able to easily copy over the data that is stored in different database servers. In this case, you'll need to use an ETL job to perform the migration.
  • This article is using PostgreSQL as the example database. You may need to update the queries for other database technologies.

Background on Cache Tables

When you configure your model to use an enricher cache, the results of the enrichment process are stored in a cache table in your data location schema (or database for SQL Server). The cache table has the prefix ct and the name that you assigned the cache table in your model.

For example, in the Melissa Personator enricher, my cache table is called ct_mel_personator . Let's use this example table going forward.

Grant the QA/PROD environment read access to the DEV table

We will be performing a simple copy process where we will create a new table in the QA/PROD schema by copying the existing cached results from DEV.

However, to allow the QA/PROD schema to access the data in DEV, we need to grant it permissions.

Here's a sample grant statement you can use. You'll need to run this grant as the DBA user.

grant select on dev_mdm.ct_mel_personator to qa_mdm;

Now, you should have access to the DEV cache table when you connect as the QA data location user.

Copy the Cache Table

As the QA data location user, you can run the following query to copy the DEV cache table into the QA data location.

create table qa_mdm.ct_mel_personator as
 select * from dev_mdm.ct_mel_personator;

This should be sufficient. Your QA environment should now have the cached results. Of course, you will need to repeat this process every time you want to sync the cache tables from DEV to QA and PROD. Or you can use an ETL process to automate this synchronization.