Generate SQL and notebooks

SQL generation overview

Kenseme writes deployment SQL for you, so you don’t hand-write DDL. You design the schema, cut a version, and generate a script for your target database. For Databricks and Fabric Lakehouse, the output is a runnable PySpark notebook instead of a SQL file.

Where you generate from

You want Use Page
Everything needed to build a schema version in a database Generate Script on a version page Generate a script from a version
SQL for one table, to copy and paste The table’s SQL tab Generate SQL for one object
The changes between two versions, as SQL Generate Migration on the Versions page Generate a migration script
Load procedures that move data between schemas A Pipeline Generate the load procedure
A Kimball star schema with its load procedures The Star schema designer Star schema designer overview

Whole-schema and migration scripts are always generated from versions, never from the live schema. That way the script you run always matches a version you can open again later. If you want a script that includes your latest edits, create a version first.

Script types on a version

The picker next to Generate Script on a version page offers:

  • Full Script: every table, index, constraint, view, procedure, and function in the version.
  • Create Objects: the tables and everything you wrote yourself. It leaves out the Generated Objects (the load views, procedures, and scripts a Pipeline produced).
  • Load Script: only the Generated Objects. Run it after Create Objects; it assumes the tables already exist.
  • Spark Schemas: Databricks and Fabric Lakehouse only. A notebook of PySpark StructType definitions for every table and view.

Splitting Create Objects and Load Script lets you deploy structure and load logic separately, for example when a different team owns each.

Which dialect a script targets

  • Whole-schema and migration scripts use the dialect the schema had when the version was created. The dialect shows as a badge in the generate dialog and can’t be changed there.
  • To generate for a different dialect, change Default Database Dialect on the schema’s Settings tab, make sure your views and procedures have a script in that dialect, then create a new version.
  • A table’s SQL tab lets you switch dialects freely, because table DDL is generated from the column definitions.
  • Views, procedures, and functions are written by you (or by a Pipeline) and stored per dialect. Kenseme doesn’t translate them at generation time. An object with no script in the target dialect is skipped, and the dialog warns you before you generate.

Kenseme supports eight dialects. See Supported database targets and Dialect-specific output notes.

SQL files and notebooks

  • SQL Server, PostgreSQL, MySQL, Redshift, Snowflake, and Fabric Warehouse produce .sql files.
  • Databricks and Fabric Lakehouse produce Jupyter notebooks (.ipynb) for whole-schema scripts, because those platforms run PySpark rather than stored procedures. See PySpark notebooks.
  • Migration scripts are always .sql files, including for Databricks and Fabric Lakehouse, where they contain Spark SQL.

Checks before and after you generate

Before you generate, Kenseme checks the version for problems in the target dialect and lists them as errors and warnings. Examples: a NVARCHAR column with no length on SQL Server, a table with no columns (skipped), or a view with no script in the target dialect (skipped). Errors don’t stop you from generating, but the script will likely fail when you run it.

After generation, you can have an AI reviewer check the script. Tick Validate script with AI agent before you click Generate. The results list errors, warnings, and notes per object, with a suggested fix where it has one, plus any ordering recommendations. This counts toward your organization’s AI usage, so it is off by default. See AI token allowance and usage.

Version tracking in the target

Every whole-schema and migration script creates a small table named z_blaze_version in the target (if it doesn’t exist) and inserts a row with the version number, schema name, script type, and the person who generated it. Query that table to see which version a database is on.