It’s always the same story. Before modifying a dimension or a fact table, I really do not want to lose any data or have to recreate everything from scratch. There is always the danger that this could happen just because I missed a step or irredeemably modified a column’s data wrongly for instance.
Sometimes, when working on critical objects, it is great to know that whatever happens, you can go back. You do not want to deploy last week’s full backup of the database just because you missed something in a single table. The method I use to prevent this is fairly simple:
1) An old but efficient SQL Statement to create a copy of the table:
Oracle: “CREATE TABLE clone_table AS SELECT * FROM table;”
SQL Server: “SELECT * INTO clone_table FROM table;”
It could be a good idea to adopt a common naming convention for this kind of table. For instance you can prefix it by bkp_ + original_table_name, so it’s easy to find them for a further purge.
When the volume is big, such as with a fact table, I obviously try to tune the bkp table creation query as much as possible.
2) A new version of the Object’s Metadata Definition with WhereScape RED:
Right Click on the table in the left pane then select “New Version”. Give a name to your version and thick the checkbox “include associated procedure / script”.
From there, whatever happens, I’m fine. If necessary, I can recreate the original meta-data by using the “create object from version” option (right click on a project/table type). I can easily repopulate the table by using the following statement:
“INSERT INTO table
SELECT * FROM clone_table;”
These mechanisms are not only useful for backup / restore purpose. They can be very handy for comparisons between tables after any processing or creating data subsets for instance.
Object versioning in WhereScape RED is used for several other reasons such as tracking development changes over the time or creating new tables close to the versioned one, for instance in case of table merges.
Obviously for this kind of approach, everyone implements using their own recipe. I guess the only important thing is to always have a plan B.