Generate SQL and notebooks

Generate a migration script

A migration script contains only the changes between two versions: new tables and columns, altered columns, dropped objects, and changed views and procedures. Run it against a database that is already on the older version to bring it to the newer one. Tick one box to get the reverse script instead.

In the app: Data Model › Schemas › your schema › Versions › tick two versions › Generate Migration

[screenshot]
The Generate Migration dialog showing v1.0.0 → v1.1.0 with the green UP badge, the red "Destructive changes detected" alert, and the acknowledgement checkbox.

Before you start

  • You need two versions of the schema.
  • Both versions must use the same dialect. If they don’t, you see The two selected versions target different dialects. Migrations require both versions to share the same dialect.
  • Generating is disabled while your organization is in read-only mode.

Steps

  1. Open the schema’s Versions page.
  2. Tick the two versions. The lower number is always the starting point.
  3. Click Generate Migration. The dialog shows Analyzing changes between versions....
  4. Check the direction. By default it is UP (green), from the older version to the newer one, for example v1.0.0 → v1.1.0.
  5. To go the other way, tick Generate DOWN SQL (reverse migration). The badge turns red, reads DOWN, and the arrow flips. Kenseme re-analyzes for the new direction.
  6. Read the destructive-change alert, if one appears (see below), and tick I understand this migration will permanently drop data. Generate stays disabled until you do.
  7. If the dialog shows an Environment section, pick the target environment for each schema. It works the same way as for version scripts. See Generate a script from a version.
  8. Set the options. See Options below.
  9. Click Generate. The dialog shows Generating migration SQL..., then Validating with AI agent... if you asked for it.
  10. Review the result and click Download, or Close to discard it.

Options

Option Default What it does
Include Foreign Key Constraints On Adds and drops foreign keys along with the tables and columns they use.
Use idempotent guards (IF EXISTS / IF NOT EXISTS) On Makes the script safe to run more than once.
Wrap in transaction On Runs the script as one transaction, where the dialect supports it.
Include commented data-backup hints for DROPs On Adds a commented reminder to back up data before each drop.
Validate script with AI agent Off An AI reviewer checks the script for dialect issues and ordering problems. Counts toward your AI usage.

Destructive changes

If the migration would drop or permanently alter data, a red !!! Destructive changes detected alert lists up to eight of the operations by kind (DropTable, DropColumn, AlterColumnLossy, DropIndex, or DropConstraint) and object, for example DropColumn: [dbo].[Order].[LegacyFlag]. Longer lists end with ...and 12 more. See the full list in the generated script.

The acknowledgement is a check on you, not on the database. The script will still drop data when you run it. Back up first.

Read the result

  • A green banner confirms the direction, for example Migration script generated: v1.0.0 → v1.1.0 (Up).
  • Lines, Size, and Destructive ops. The count turns red when it is above zero, and a red list shows up to twelve of those operations.
  • Dialect limitations: changes Kenseme could not write for this dialect. Read these carefully. Common ones:
    • A view, procedure, or function was added but has no script in this dialect, so it was skipped.
    • The dialect doesn’t support stored procedures (Fabric Lakehouse), so procedures were skipped.
    • Enum changes were detected. Enum migrations are not written automatically. Review them on the comparison page and apply them by hand.
  • AI validation results, if you asked for them: errors, warnings, notes, and ordering recommendations.

If the two versions have no structural differences in the chosen direction, the dialog says No structural changes between the two versions — the generated script is a no-op (header comment only).

What the script contains

  • Table, column, index, and foreign-key changes as CREATE, ALTER, and DROP statements.
  • The new body of every added or changed view, procedure, and function, in the version’s dialect.
  • A z_blaze_version row recording the version the database is now on.

File names

  • Sales_v1.0.0_to_v1.1.0_SqlServer.sql for an UP script.
  • Sales_v1.1.0_to_v1.0.0_SqlServer_DOWN.sql for a DOWN script. The version order follows the direction.

Migration scripts are always .sql files. For Databricks and Fabric Lakehouse they contain Spark SQL, which you can run from a notebook %sql cell or a SQL editor.