Skip to main content

Migrations FAQs

Schema migrations for materialized views​

Question

note

How are migrations for materialized views handled by Depot?

Answer

Depot supports materialized view migrations, but they are disabled by default. It can be enabled only on Dataset level with a migrateMaterializedViews flag, that is in CDK it should look something like:

new depot.Dataset(this, 'SampleSourceDataset', {
    environment: depotEnvironment,
    name: 'sample.source',
    location: myLocation,
    package: myPackage,
    migrateMaterializedViews: true
});

When enabled, during migration, the data in the view will be preserved and explicit migrations can be provided to modify the data (analogous to object migrations). This also means that materialized views can be renamed without losing the data.

Once enabled, there is no way to force view recreation without disabling the feature. It could be worked around by truncating the backing table manually.

Without this feature enabled, if a materialized view gets any structural change, for example: adding a (nullable) column, deleting a column, changing column type, etc... the underlying Snowflake table is re-created.

The old table is renamed to MY_TABLE_xxx where xxx is the migration ID, and a new table with the correct structure is created.

It is then expected that a REFRESH transaction without any query restrictions will be run manually on the materialized view to re-populate the data.

Without the manual REFRESH transaction, the materialized view table will stay empty after migration.

How does Depot schema migration work when rolling back changes?​

Question

note

How does depot schema migration work when rolling back changes?

  • Environment has un-versioned (no version property) schema A.
  • A new version of schema A (version: 2 ) is deployed.
  • Rollback changes to schema A back to original version (step 1).

For #3 - do I need to change schema version to 3 to rollback?

Example:

  • A has property x and is deployed
  • A ’s property x is renamed to y and is deployed. Version set to 2.
  • I want to rename A’s property y back to x . Should I set version to 3?

Answer

For #3, if the rollback is a conscious rollback, then yes, you need to change the schema version back to version 3, otherwise it doesn't actually rollback.

You could think that this is not a rollback, but a normal change (which happens to be back to the old structure).

If rolling back is manually reverting the schema to a previous state, after a successful deployment that changes from 1 -> 2 you will need to create a new version 3 that undoes the changes.

If the change from 1 -> 2 fails and it rolls back (in the sense that we consider a rollback) then you won't ever see v2 deployed.