Restoring databases from one MT environment to another and the implications on Data Lake

If the schema has been sent to the data catalog and data to the data lake in one environment, there are tables in PLM for Process that reflect this, FSDATALAKESENTLOG, FSDATALAKETABLESCHEMA and FSDATALAKEPAYLOAD. These tables match up with the data catalog and data lake in ION Desk for that environment.

If a backup of the PLM for Process database for this environment is used to populate a new environment that is moving test data into production, then the PLM for Process tables contain information from the old environment that do not reflect what is in the data catalog and data lake for the new environment. To correct this, schema and data must be sent again from this new environment.

If a backup is used to populate a new, clean environment, three things need to happen to set things up correctly.

  1. New ION API credentials must be generated and entered using the IMPORT ION CONFIG wizard. This will set the correct the profile values for this environment.
  2. Run a ResetDataLake.sql script that will clear out the PLM for Process data lake tables and reset the fields on the symbol related to data lake.
    --RESET SYMBOLS TO ZERO AND CLEAR OUT SCHEMA (Execute these in update/deletes below)
    UPDATE FSOBJECTSYMBOL SET DATALAKE_SCHEMA_SENT_IND=0,DATALAKE_SCHEMA_SENT_DATE=NULL,DATALAKE_DATA_SENT_IND=0, DATALAKE_DATA_SENT_DATE= NULL
    FROM FSOBJECTSYMBOL WHERE DATALAKE_IND=1;
    
    DELETE FROM FSDATALAKETABLESCHEMA;
    DELETE FROM FSDATALAKEPAYLOAD;
    DELETE FROM FSDATALAKEOUTBOX;
    DELETE FROM FSDATALAKESENTLOG;
    
  3. Use the Symbol page to setup data lake and then send data to data lake for the new environment for each symbol.
    These steps are important and if they are not followed will lead to inconsistent data in the data lake.