topic Re: Best Practices for DEV -> PROD migration in Data & Schema Discussions

Best Practices for DEV -> PROD migration

RADSr — Thu, 13 Oct 2022 16:10:43 GMT

I'm about to embark on a change which will impact a whole bunch ( tech term ) of measures from different tables and views in both physical and business schemae. I have a DEV environment - huzzah! - so I intend to make changes there. That brings up a question of the best way to move changes from DEV to PROD.

Just for the record I did search first and found this: https://community.incorta.com/t5/administration-knowledgebase/best-practices-article-index/ta-p/1731

and this: https://community.incorta.com/t5/administration-knowledgebase/migrating-content-between-environments/ta-p/1865

but am still left w/ a few questions - I think an important note is that I'm all cloud so no fiddling about w/ copying files and whatnot directly in the OS.

1) In DEV I may be working on changes to several tables and be ready to promote only one of them - is there a way to do this other than to re-key the changes in PROD?

1a) I know I can hack around in the XML exports to swap out table definitions and whatnot, although I assume that's not a supported technique ? What is the best way to move table/view changes from DEV to PROD?

2) Is there a way to move schema/table/view changes from DEV to PROD w/out wiping out existing stored data? If I make changes on a half-dozen tables of a 50 table schema do I need to do a full load of all 50 after a schema import ( I think ) ?

3) Objects ( schemas and dashboards ) are numbered in the XML - is this meaningful? Is there a risk of collision if I'm not actively avoiding conflicts?

4) It looks like importing a schema of the same name ( and number? ) retains the version history in the metadata db so I can still rollback if need be w/ out importing a backup - is that correct? Perhaps more importantly is that the design intent and can I rely on it as part of the migration process?

I'm sure there are more questions and considerations and I'm hopeful I'm not the only person thinkgin about them 😉

Re: Best Practices for DEV -> PROD migration

RADSr — Thu, 13 Oct 2022 16:26:32 GMT

Adding - I just deleted a physical schema from DEV and imported the PROD copy. Just for kicks I tried to load the schema from stage and ... it worked.

So a new question is when do staging tables ( files ) go away and how can I take advantage of this apparent persistence?

I hadn't made any changes to data sources ( SQL ) so there weren't any inconsistencies that way - but if there were I'd expect an error on the table load - but the schema I think should work w/ the other tables.

Re: Best Practices for DEV -> PROD migration

dylanwan — Fri, 14 Oct 2022 17:22:29 GMT

1) In DEV I may be working on changes to several tables and be ready to promote only one of them - is there a way to do this other than to re-key the changes in PROD?

No, it is not possible today. We need to consider other dependent objects as well, such as aliases and joins. Currently export and import are done at the schema level. We do have a relative new feature that allows you to export a notebook of a MV and you can import the notebook only to another new or old MV that can be any place, either another env or the same env.

The practice is to migrate the entire schema when all objects in the schema are ready.

When multiple changes need to be done to the same schema, I will made the change in a sequential order for migrating to the new env. Development work for a bigger change may be done in a different schema first and reimplement in the DEV env when it is ready.

Adding columns and adding tables should have minimum impact to the downstream process. I may just go ahead to release it without the new business view or new dashboards that uptake the new schema change.

I will not manually update the schema for the individual table change in PROD. As the migration and the development work may be done by different people and the change prompted should be fully tested first in DEV. However, some changes may have to be done in this way are scheduled job, MV properties, adding DB hints to SQL, sharing to different groups.

A good practice is to perform a schema comparison between the target schema in the PROD and the to-be-prompted schema from DEV and see the difference before migrating. It will be important if you start having the practice of applying individual changes to the PROD.