Data Model — Kenseme docs

Schemas

Schemas overview What a schema is in Kenseme, what it holds, how it relates to Pipelines, versions and SQL generation, and where to go next. Create a schema Use the Create Schema wizard to start blank, import a script, read a live database, duplicate a schema into another dialect, or load the sample. Browse a schema A tour of the schema workspace: the header, every tab, and the bulk actions shared by the object lists. Work with tables and columns Create tables from scratch, SQL, a file, a template, another table or a live database, then add and edit columns and get AI naming help. Work with views, procedures, functions and scripts Create views, stored procedures, functions and scripts, write their SQL in one or more dialects, and read their outputs, parameters and dependencies. Define relationships and indexes Set primary keys, create and edit foreign-key relationships, let Kenseme suggest relationships from column names, and define indexes. Work with enums Define named lists of allowed values, constrain string columns to them, and choose whether they’re generated as native ENUM types or CHECK constraints. Schema settings Set a schema’s default dialect, naming conventions, per-environment database names and attribute groups, or delete the schema. Attach labels and attributes to schemas and their objects Tag schemas with coloured labels, and record structured custom attributes on schemas, tables, columns and SQL objects. Read a schema as an ER diagram The Diagram tab draws a schema as an interactive entity-relationship diagram. Filter it, trace keys and dependencies, focus on one table, and export it. Work with the Schema Agent Chat with an AI advisor that knows your schema. Ask for reviews and SQL, then add its proposed objects and column changes in one click. Generate a query with AI The Query Agent tab writes SQL from a plain-English question, using your schema’s definition, for you to copy and run yourself. Clone a schema to another dialect Duplicate a schema into a different SQL dialect. Structure copies directly; AI converts view, procedure and function SQL, which you then review. Generate a schema from an ontology Turn an ontology into a relational schema, then keep the two in step with updates that only touch the objects the generator created. Expose a schema over MCP Let AI assistants read a schema’s metadata through the Model Context Protocol: turn the server on, copy its URL, and choose exactly what it shares. AI in schema editing Every place AI helps while you edit a schema: naming suggestions, bulk column parsing, descriptions, dialect conversion and the Schema Agent. AI in SQL object analysis How Kenseme analyzes view, procedure, function and script SQL to find issues, output columns, parameters and dependencies, and flags broken objects.

Import a schema

What schema import does Load an existing database design from a DDL script instead of retyping it. Kenseme parses tables, keys, indexes, views, procedures and functions. Supported import file formats Script imports accept SQL DDL, YAML, JSON and plain text up to 5 MB, uploaded as a file or pasted. Start an import Upload or paste a DDL script into an existing schema and start the background import. Import objects from a database connection Read a live database through a saved connection, review every proposed addition and update, and apply only what you tick. Nothing is ever deleted. Watch import progress The schema import page shows what the import is doing now, progress by stage, every item’s status, and lets you cancel or resume. Fix failed import items Open the Fix & Retry dialog on a failed import item, read the plain-English explanation, edit the DDL, and retry. After the import finishes What a finished import gives you, a short checklist to run before you rely on it, and what to do if many items failed. Schema import as a background job Script imports run in the background. Where to follow them, what happens if you close the browser, and what else runs alongside. AI in schema import Where AI helps during a script import (splitting, parsing, descriptions, failure explanations) and what it never does.

Versions

How schema versioning works What a schema version captures, when you create one, how version numbers work, and how versions relate to the database you deploy to. Browse the version history Open a schema’s Versions page to see every version newest first, read change summaries, and pick two versions to compare or migrate between. Inspect a past version Open a read-only snapshot of a schema version and drill into its tables, views, procedures, functions, and enums exactly as they were. Compare two versions See every added, modified, and removed table, column, index, view, procedure, function, and enum between two schema versions, with line-level SQL diffs. Roll back to an earlier version Restore a whole schema, or one table, column, view, procedure, function, or script, to a past version with preflight checks and type-to-confirm safety.

Generate SQL and notebooks

SQL generation overview Where Kenseme generates SQL and PySpark notebooks, which dialect each script targets, and how to pick the right kind of script for the job. Generate a script from a version Generate a full, create-objects, load, or Spark schemas script from a schema version, check it with AI, then download it or save the notebook to Fabric. Generate SQL for one object Copy CREATE, DROP, SELECT, INSERT, UPDATE, and DELETE statements for one table in any dialect, and copy a view, procedure, or function’s SQL. Generate a migration script Generate the SQL that moves a database from one schema version to another, forward (UP) or back (DOWN), with guards for destructive changes. PySpark notebooks for Databricks and Fabric Lakehouse What is inside the Jupyter notebooks Kenseme generates for Databricks and Fabric Lakehouse, and how to import, run, and schedule them. Supported database targets The eight database dialects Kenseme generates for, what each one produces, how to choose a schema’s dialect, and which targets each feature supports. Dialect-specific output notes How generated DDL differs across the eight dialects: quoting, identity, keys, indexes, defaults, enums, and what each target leaves out.

Star schema

Star schema designer overview Design a Kimball star schema on a cleansed schema. AI proposes facts and dimensions, you review, and Kenseme builds the tables and load Pipeline. Set up a star schema plan Create a star schema plan: pick the destination schema and target platform, set naming conventions, give the AI context, and start the analysis. Review the AI's star schema design Read the AI’s proposed bus matrix, keep or drop dimensions and facts, refine items with extra guidance, and create the plan and its Pipeline. Build each dimension and fact Walk each star schema item through source, table, and procedure steps, build the date dimension, or build the whole plan in the background in one click. Finish and deploy a star schema plan Validate a star schema plan, generate the master procedure, regenerate load procedures, and download every script in dependency order to deploy. Keep a star schema plan in step with its source Find where a star schema plan’s source tables have changed, heal the safe differences automatically, and resolve or dismiss the rest. AI in the Star schema designer Where AI proposes the star schema design, drafts source views, and refreshes prompts in the Star schema designer, and what stays under your control.

Pipelines

Pipelines overview A Pipeline maps one or more source schemas into a new destination schema and generates the load procedures or PySpark functions that move the data. Pipelines list Find your Pipelines, check their status and drift, and create, edit, open, or delete them from one page. Create a Pipeline Pick source schemas, name the destination schema, set naming conventions, choose tables, and optionally ask AI to suggest column names. Map source columns to target columns Use the mapping editor to include or exclude tables and columns, rename destinations, set keys, and accept or reject AI name suggestions. Override a mapping with an expression Decide how calculated columns are loaded, and supply your own SQL expression when a destination column needs a derived value. Choose a load strategy Pick how each destination table is loaded, configure watermark, batch, or hash settings, and let Analyze All suggest strategies. Validate a Pipeline Run the Pipeline checks, read errors, warnings, and notes, and fix what blocks finalizing. Finalize a Pipeline Build the destination schema’s tables, columns, surrogate keys, and audit columns from your mappings. Nothing runs against your database. Generate the load procedures Generate, preview, and regenerate each table’s load procedure or PySpark function, then build the master that runs them all. Run the generated load Kenseme generates the load code but never runs it. Export the scripts or notebook, deploy them to your database or lakehouse, and run the master. Keep a Pipeline in sync with its source Pick up source tables and columns that were added, renamed, or removed, clear stale mappings, and re-finalize in one click. Re-run or roll back a Pipeline Change a Pipeline’s name, conventions, sources, or table selection with Re-run, and undo changes using destination-schema versions. Pipeline load strategies Reference for every Pipeline load strategy: behavior, required settings, key requirements, audit columns, and per-dialect output. AI in Pipelines Where Pipelines use AI: suggesting readable destination column names and checking view-based expressions for the target dialect.