Notebook
SQLMesh supports Jupyter and Databricks Notebooks. Magics are loaded automatically when sqlmesh or one of its modules is imported.
NOTE: VSCode notebooks may not properly render widgets with ipywidgets library version >=8 (VSCode Github issue). You can work around this by downgrading the ipywidgets version with pip install "ipywidgets<8".
SQLMesh project setup
Notebooks locate a SQLMesh project by setting a context with either the Python API or a notebook magic.
Set the context with the Python Context function as follows:
Alternatively, set the context with a notebook magic:
Quickstart project
If desired, you can create the quickstart example project with the Python init_example_project function. The function requires a default SQL dialect for the project's models; this example uses snowflake:
from sqlmesh.cli.example_project import init_example_project
init_example_project("path_to_project_directory", dialect="snowflake")
Alternatively, create the project with a notebook magic:
Databricks notebooks
To use your Databricks cluster, update the default_connection and test_connection in the config.yaml file.
See the Execution Engines page for information on configuring a Databricks connection.
Magic Commands
All available magic commands can be listed with %lsmagic and then the docstring for any given magic can be displayed with %magic_name? for line magics and %%magic_name? for call magics.
context
%context [--config CONFIG] [--gateway GATEWAY] [--ignore-warnings]
[--debug]
paths [paths ...]
Sets the context in the user namespace.
positional arguments:
paths The path(s) to the SQLMesh project(s).
options:
--config CONFIG Name of the config object. Only applicable to
configuration defined using Python script.
--gateway GATEWAY The name of the gateway.
--ignore-warnings Ignore warnings.
--debug Enable debug mode.
init
%init [--template TEMPLATE] [--dlt-pipeline PIPELINE] path sql_dialect
Creates a SQLMesh project scaffold with a default SQL dialect.
positional arguments:
path The path where the new SQLMesh project should be
created.
sql_dialect Default model SQL dialect. Supported values: '',
'bigquery', 'clickhouse', 'databricks', 'doris',
'drill', 'duckdb', 'hive', 'mysql', 'oracle',
'postgres', 'presto', 'redshift', 'snowflake',
'spark', 'spark2', 'sqlite', 'starrocks', 'tableau',
'teradata', 'trino', 'tsql'.
options:
--template TEMPLATE, -t TEMPLATE
Project template. Supported values: airflow, dbt,
dlt, default, empty.
--dlt-pipeline PIPELINE
DLT pipeline for which to generate a SQLMesh project.
This option is supported if the template is dlt.
plan
%plan [--start START] [--end END] [--execution-time EXECUTION_TIME]
[--create-from CREATE_FROM] [--skip-tests]
[--restate-model [RESTATE_MODEL ...]] [--no-gaps]
[--skip-backfill, --dry-run] [--forward-only]
[--effective-from EFFECTIVE_FROM] [--no-prompts] [--auto-apply]
[--no-auto-categorization] [--include-unmodified]
[--select-model [SELECT_MODEL ...]]
[--backfill-model [BACKFILL_MODEL ...]] [--no-diff] [--run]
[environment] [--diff-rendered]
Goes through a set of prompts to both establish a plan and apply it
positional arguments:
environment The environment to run the plan against
options:
--start START, -s START
Start date to backfill.
--end END, -e END End date to backfill.
--execution-time EXECUTION_TIME
Execution time.
--create-from CREATE_FROM
The environment to create the target environment from
if it doesn't exist. Default: prod.
--skip-tests, -t Skip the unit tests defined for the model.
--restate-model <[RESTATE_MODEL ...]>, -r <[RESTATE_MODEL ...]>
Restate data for specified models (and models
downstream from the one specified). For production
environment, all related model versions will have
their intervals wiped, but only the current versions
will be backfilled. For development environment, only
the current model versions will be affected.
--no-gaps, -g Ensure that new snapshots have no data gaps when
comparing to existing snapshots for matching models in
the target environment.
--skip-backfill, --dry-run
Skip the backfill step and only create a virtual update for the plan.
--forward-only Create a plan for forward-only changes.
--effective-from EFFECTIVE_FROM
The effective date from which to apply forward-only
changes on production.
--no-prompts Disables interactive prompts for the backfill time
range. Please note that if this flag is set and there
are uncategorized changes, plan creation will fail.
--auto-apply Automatically applies the new plan after creation.
--no-auto-categorization
Disable automatic change categorization.
--include-unmodified Include unmodified models in the target environment.
--select-model <[SELECT_MODEL ...]>
Select specific model changes that should be included
in the plan.
--backfill-model <[BACKFILL_MODEL ...]>
Backfill only the models whose names match the
expression. This is supported only when targeting a
development environment.
--no-diff Hide text differences for changed models.
--run Run latest intervals as part of the plan application
(prod environment only).
--diff-rendered Output text differences for the rendered versions of models and standalone audits
run_dag
%run_dag [--start START] [--end END] [--skip-janitor] [--ignore-cron]
[environment]
Evaluate the DAG of models using the built-in scheduler.
positional arguments:
environment The environment to run against
options:
--start START, -s START
Start date to evaluate.
--end END, -e END End date to evaluate.
--skip-janitor Skip the janitor task.
--ignore-cron Run for all missing intervals, ignoring individual
cron schedules.
evaluate
%evaluate [--start START] [--end END] [--execution-time EXECUTION_TIME]
[--limit LIMIT]
model
Evaluate a model query and fetches a dataframe.
positional arguments:
model The model.
options:
--start START, -s START
Start date to render.
--end END, -e END End date to render.
--execution-time EXECUTION_TIME
Execution time.
--limit LIMIT The number of rows which the query should be limited
to.
render
%render [--start START] [--end END] [--execution-time EXECUTION_TIME]
[--expand EXPAND] [--dialect DIALECT] [--no-format]
model
Renders a model's query, optionally expanding referenced models.
positional arguments:
model The model.
options:
--start START, -s START
Start date to render.
--end END, -e END End date to render.
--execution-time EXECUTION_TIME
Execution time.
--expand EXPAND Whether or not to use expand materialized models,
defaults to False. If True, all referenced models are
expanded as raw queries. If a list, only referenced
models are expanded as raw queries.
--dialect DIALECT SQL dialect to render.
--no-format Disable fancy formatting of the query.
dag
%dag [--file FILE]
Displays the HTML DAG.
options:
--file FILE, -f FILE An optional file path to write the HTML output to.
dlt_refresh
%dlt_refresh PIPELINE [--table] TABLE [--force]
Attaches to a DLT pipeline with the option to update specific or all models of the SQLMesh project.
options:
--table TABLE, -t TABLE The DLT tables to generate SQLMesh models from. When none specified, all new missing tables will be generated.
--force, -f If set it will overwrite existing models with the new generated models from the DLT tables.
environments
fetchdf
%%fetchdf [df_var]
Fetches a dataframe from sql, optionally storing it in a variable.
positional arguments:
df_var An optional variable name to store the resulting dataframe.
test
%test [--ls] model [test_name]
Allow the user to list tests for a model, output a specific test, and then write their changes back
positional arguments:
model The model.
test_name The test name to display
options:
--ls List tests associated with a model
migrate
create_external_models
table_diff
%table_diff [--on [ON ...]] [--skip-columns [SKIP_COLUMNS ...]]
[--model MODEL] [--where WHERE] [--limit LIMIT]
[--show-sample] [--decimals DECIMALS] [--skip-grain-check]
[--temp-schema SCHEMA]
SOURCE:TARGET
Show the diff between two tables.
Can either be two tables or two environments and a model.
positional arguments:
SOURCE:TARGET Source and target in `SOURCE:TARGET` format
options:
--on <[ON ...]> The column to join on. Can be specified multiple
times. The model grain will be used if not specified.
--skip-columns <[SKIP_COLUMNS ...]>
The column(s) to skip when comparing the source and
target table.
--model MODEL The model to diff against when source and target are
environments and not tables.
--where WHERE An optional where statement to filter results.
--limit LIMIT The limit of the sample dataframe.
--show-sample Show a sample of the rows that differ. With many
columns, the output can be very wide.
--decimals DECIMALS The number of decimal places to keep when comparing
floating point columns. Default: 3
--skip-grain-check Disable the check for a primary key (grain) that is
missing or is not unique.
--temp-schema SCHEMA The schema to use for temporary tables.
model
%model [--start START] [--end END] [--execution-time EXECUTION_TIME]
[--dialect DIALECT]
model
Renders the model and automatically fills in an editable cell with the model definition.
positional arguments:
model The model.
options:
--start START, -s START
Start date to render.
--end END, -e END End date to render.
--execution-time EXECUTION_TIME
Execution time.
--dialect DIALECT, -d DIALECT
The rendered dialect.
diff
%diff environment
Show the diff between the local state and the target environment.
positional arguments:
environment The environment to diff local state against.
invalidate
%invalidate environment
Invalidate the target environment, forcing its removal during the next run of the janitor process.
positional arguments:
environment The environment to invalidate.
janitor
%janitor
Run the janitor process to clean up old environments and expired snapshots.
options:
--ignore-ttl Cleanup snapshots that are not referenced in any environment, regardless of when they're set to expire
create_test
%create_test [--query QUERY [QUERY ...]] [--overwrite]
[--var VAR [VAR ...]] [--path PATH] [--name NAME]
[--include-ctes]
model
Generate a unit test fixture for a given model.
positional arguments:
model
options:
--query <QUERY [QUERY ...]>, -q <QUERY [QUERY ...]>
Queries that will be used to generate data for the
model's dependencies.
--overwrite, -o When true, the fixture file will be overwritten in
case it already exists.
--var <VAR [VAR ...]>, -v <VAR [VAR ...]>
Key-value pairs that will define variables needed by
the model.
--path PATH, -p PATH The file path corresponding to the fixture, relative
to the test directory. By default, the fixture will be
created under the test directory and the file name
will be inferred based on the test's name.
--name NAME, -n NAME The name of the test that will be created. By default,
it's inferred based on the model's name.
--include-ctes When true, CTE fixtures will also be generated.
run_test
%run_test [--pattern [PATTERN ...]] [--verbose] [--preserve-fixtures] [tests ...]
Run unit test(s).
positional arguments:
tests
options:
--pattern <[PATTERN ...]>, -k <[PATTERN ...]>
Only run tests that match the pattern of substring.
--verbose, -v Verbose output.
--preserve-fixtures Preserve the fixture tables in the testing database,
useful for debugging.
audit
%audit [--start START] [--end END] [--execution-time EXECUTION_TIME]
[models ...]
Run audit(s)
positional arguments:
models A model to audit. Multiple models can be audited.
options:
--start START, -s START
Start date to audit.
--end END, -e END End date to audit.
--execution-time EXECUTION_TIME
Execution time.
rollback
clean
rewrite
%rewrite [--read READ] [--write WRITE]
Rewrite a sql expression with semantic references into an executable query.
https://sqlmesh.readthedocs.io/en/latest/concepts/metrics/overview/
options:
--read READ The input dialect of the sql string.
--write WRITE The output dialect of the sql string.
format
%format [--transpile TRANSPILE] [--append-newline] [--no-rewrite-casts]
[--normalize] [--pad PAD] [--indent INDENT]
[--normalize-functions NORMALIZE_FUNCTIONS] [--leading-comma]
[--max-text-width MAX_TEXT_WIDTH] [--check]
Format all SQL models and audits.
options:
--transpile TRANSPILE, -t TRANSPILE
Transpile project models to the specified dialect.
--append-newline Whether or not to append a newline to the end of the
file.
--no-rewrite-casts Preserve the existing casts, without rewriting them
to use the :: syntax.
--normalize Whether or not to normalize identifiers to lowercase.
--pad PAD Determines the pad size in a formatted string.
--indent INDENT Determines the indentation size in a formatted string.
--normalize-functions NORMALIZE_FUNCTIONS
Whether or not to normalize all function names.
Possible values are: 'upper', 'lower'
--leading-comma Determines whether or not the comma is leading or
trailing in select expressions. Default is trailing.
--max-text-width MAX_TEXT_WIDTH
The max number of characters in a segment before
creating new lines in pretty mode.
--check Whether or not to check formatting (but not actually
format anything).