Generate SQL and notebooks

PySpark notebooks for Databricks and Fabric Lakehouse

Databricks and Fabric Lakehouse don’t run stored procedures the way a SQL warehouse does. When a schema targets either platform, Kenseme generates a Jupyter notebook (.ipynb) instead of a SQL script. It holds Spark SQL for the structure and Python functions for the loads. The same notebook format imports into both platforms.

[screenshot]
A generated Full Create notebook open in Databricks: the markdown header, the imports cell, and the first Object creation cells.

When you get a notebook

  • Full Script, Create Objects, and Load Script on a version page produce a notebook for Databricks and Fabric Lakehouse schemas. See Generate a script from a version.
  • Spark Schemas on a version page produces a notebook of PySpark StructType definitions.
  • Pipelines that target these platforms generate their load logic as Python functions rather than procedures. See Generate the load procedure.
  • The Star schema designer’s Download All Scripts zip packages load functions as .ipynb files for these platforms.

Migration scripts are the exception: they are always .sql files containing Spark SQL.

What is in a deployment notebook

Cells run top to bottom in this order. Sections with nothing to do are left out.

  1. Header (markdown). The schema name and notebook kind (Full Create, Create Objects, or Load Script), the version, dialect, generation time and author, and a short note on what the notebook does.
  2. Imports. import uuid, datetime and from delta.tables import DeltaTable.
  3. Drop objects (optional — uncomment to enable). Present only if you asked for drops. Every spark.sql(...) line is commented out. To use it, remove the leading # from each line, then run the cell. It drops views, then tables, then the version table.
  4. Object creation. One cell per statement: the z_blaze_version table, CREATE SCHEMA IF NOT EXISTS, then CREATE TABLE IF NOT EXISTS for each table, plus constraints where the platform allows them. Safe to re-run.
  5. Recalc views. Views are created as materialized views, each followed by a REFRESH MATERIALIZED VIEW cell so it is populated straight away. See Materialized views.
  6. Migration functions. One Python function per table load, followed by the master orchestrator that calls them in execution-group order.
  7. Run (optional — uncomment to execute). A single commented-out call to the master orchestrator, with debug=True. It stays commented so that “Run all” doesn’t start a full load by accident.
  8. Record version applied. Inserts a row into z_blaze_version.

Create Objects notebooks stop after the object-creation cells. Load Script notebooks skip the table DDL and contain the recalc views, the load functions, and the orchestrator.

The load functions

Each load function is a plain Python function named after the Pipeline mapping it implements. Its comment block names the source, the destination, and the load strategy.

The signature always starts with spark, job_id=None, executed_by=None, debug=False, parent_job_id=None. Some strategies add arguments:

Argument Added for
batch_id=None Batch-based strategies
watermark_override=None Watermark (incremental) strategies
snapshot_date=None Snapshot strategies

Inside, the function runs Delta Lake SQL (MERGE, INSERT, UPDATE, DELETE) through spark.sql(...). Pass debug=True to print progress.

If the Pipeline has a job log table, each function writes a Running row when it starts and updates it to Success or Failed (with the error message) when it ends. The orchestrator passes its own job id to each child as parent_job_id, so one run’s rows can be grouped.

Note: Spark has no multi-statement transactions. Each step is an atomic Delta operation, and the strategies are written to be safe to re-run. The rows-inserted figure a function logs is the destination row count after the load, because Spark DML doesn’t return affected-row counts.

Materialized views

A plain Spark view is recomputed on every read and exists only in Spark, so it can’t serve as a stored recalc object. Kenseme therefore creates recalc views as Delta materialized views (CREATE MATERIALIZED VIEW) and refreshes each one right after it is created, in dependency order.

Your platform must support them:

  • Fabric Lakehouse: the lakehouse must be schema-enabled.
  • Databricks: you need a Pro or Serverless SQL warehouse to create and refresh them.

The Spark Schemas notebook

Spark Schemas produces a notebook with a PySpark StructType for every table and view in the version, each assigned to a lower-snake-case variable (for example order_line). They are also collected into three dictionaries keyed by the same names, table_schemas, view_schemas, and all_schemas, so you can read files with an explicit schema, for example spark.read.schema(table_schemas["order_line"]). For one table, use Spark Schema on the table’s SQL tab instead.

Run a generated notebook

  1. Download the .ipynb from the generate dialog. For Fabric Lakehouse you can use Save to Fabric to write it straight into a workspace folder instead.
  2. Import it into your Databricks workspace or Fabric workspace.
  3. Attach it to a cluster or SQL warehouse (Databricks) or to the lakehouse (Fabric).
  4. Run the cells in order.
  5. To run the loads, uncomment the call in the Run cell, or call the orchestrator or a single load function from your own cell.

Tip: To run loads on a schedule, call the orchestrator from a Databricks job or a Fabric pipeline notebook activity. Keep the generated notebook unmodified so you can regenerate it.

What the notebook does not contain

  • Cluster or runtime configuration. You attach compute on the platform side.
  • Library installs. Add a %pip install cell if your environment needs one.
  • Credentials or connection details. The notebook runs as whoever runs it, using the catalog, lakehouse, and schema it is attached to.