Generate SQL and notebooks

Dialect-specific output notes

Kenseme adapts the DDL it generates to each target: quoting, identity syntax, keys, indexes, defaults, and enums. Some targets can’t express everything a schema describes. This page tells you what changes, so you know before you run a script.

Quick comparison

Dialect Output Quoting Identity columns Indexes Column defaults Enums
SQL Server .sql [name] IDENTITY(seed,increment) Yes Yes Check constraint
Fabric Warehouse .sql [name] IDENTITY on BIGINT only Not emitted Not emitted Check constraint
PostgreSQL .sql "name" GENERATED ALWAYS AS IDENTITY Yes Yes Native CREATE TYPE … AS ENUM or check constraint
MySQL .sql `name` AUTO_INCREMENT Yes Yes Inline ENUM(...) or check constraint
Redshift .sql "name" IDENTITY(seed,increment) Not emitted Yes Check constraint
Snowflake .sql "name" AUTOINCREMENT START … INCREMENT … Not emitted Yes Check constraint
Databricks .ipynb `name` GENERATED ALWAYS AS IDENTITY Not emitted Yes Check constraint
Fabric Lakehouse .ipynb `name` Not supported Not emitted With Delta opt-in Check constraint

An enum uses the native form only where the dialect has one and the enum’s strategy is ENUM. Otherwise it becomes a check constraint on the columns that use it.

What stays the same everywhere

  • Tables and columns, mapped to each dialect’s data types.
  • Dependency order: schemas, then tables, then keys and constraints, then views, procedures, and functions.
  • The z_blaze_version tracking table and its version row.
  • Your own view, procedure, and function bodies. Kenseme emits the script you stored for the target dialect, as written. It doesn’t translate them at generation time.

SQL Server

The most complete target. Scripts use GO batch separators and IF NOT EXISTS (SELECT * FROM sys.schemas …) guards for CREATE SCHEMA. Procedures are T-SQL.

Fabric Warehouse

Close to SQL Server, with engine limits Kenseme works around:

  • Primary keys are added after the table with ALTER TABLE … ADD CONSTRAINT … PRIMARY KEY NONCLUSTERED (…) NOT ENFORCED. Fabric treats them as hints and accepts duplicate keys.
  • Identity columns must be BIGINT.
  • Column defaults aren’t supported, so they are left out. Supply values in your inserts.
  • CREATE INDEX isn’t supported, so indexes are left out.
  • Explicit transactions aren’t supported for DDL. Wrap in transaction has no effect in migration scripts.
  • Computed columns aren’t supported.

PostgreSQL

  • Enums with the ENUM strategy become native types, created before the tables that use them.
  • Identity uses GENERATED ALWAYS AS IDENTITY.
  • CREATE SCHEMA IF NOT EXISTS and CREATE INDEX IF NOT EXISTS are used directly.
  • Computed columns aren’t generated yet.

MySQL

  • Identity uses AUTO_INCREMENT. Custom seed and increment values aren’t carried over; set them at table or server level if you need them.
  • Enums with the ENUM strategy become inline ENUM(...) column types.
  • MERGE isn’t available, which affects Pipeline load procedures.
  • Computed columns aren’t generated yet.

Redshift

  • Redshift has no CREATE INDEX. Indexes are skipped; tune with sort and distribution keys yourself.
  • MERGE isn’t used, which affects Pipeline load procedures.
  • Computed columns aren’t generated yet.

Snowflake

  • Identity uses AUTOINCREMENT START … INCREMENT ….
  • Snowflake has no user-defined indexes, so they are skipped.
  • Computed columns aren’t generated yet.

Databricks and Fabric Lakehouse

  • Whole-schema scripts are notebooks. See PySpark notebooks.
  • There are no indexes and no multi-statement transactions.
  • Recalc views become Delta materialized views with an explicit refresh.
  • Databricks supports identity columns; Fabric Lakehouse doesn’t.

Fabric Lakehouse has extra limits:

  • Primary and foreign keys are not emitted. Delta tables in Fabric reject them.
  • Column defaults need the Delta allowColumnDefaults feature. Kenseme adds TBLPROPERTIES ('delta.feature.allowColumnDefaults' = 'enabled') to any table that declares a default. A Delta default applies only to SQL inserts that leave the column out; a DataFrame write doesn’t fill it.
  • Stored procedures aren’t supported. Migration scripts skip them and list them under Dialect limitations.
  • Tables and columns can’t be renamed in place.