Introducing Flights: agent-native data pipelines in MotherDuckJoin the livestream

Skip to main content

SQLMesh

SQLMesh is a data transformation tool for building and managing data pipelines. It integrates with MotherDuck for running data transformation projects against MotherDuck.

How it works with MotherDuck​

SQLMesh can use MotherDuck as its execution engine for transformation projects.

Prerequisites​

  • SQLMesh installed with DuckDB support, for example sqlmesh[duckdb].
  • A SQLMesh project.
  • A MotherDuck access token. For shared projects, use a service account that owns SQLMesh-managed objects.

Setup​

  1. Install SQLMesh with DuckDB support:

    pip install "sqlmesh[duckdb]"

  2. Create a MotherDuck token and store it in MOTHERDUCK_TOKEN.

  3. Add a MotherDuck gateway to config.yaml:

    gateways:
      motherduck:
        connection:
          type: motherduck
          token: {{ env_var('MOTHERDUCK_TOKEN') }}

    default_gateway: motherduck

  4. Validate the connection:

    sqlmesh info

  5. Run a plan when the connection succeeds:

    sqlmesh plan

Authentication and configuration​

  • Load the token from an environment variable instead of committing it in config.yaml.
  • SQLMesh supports persistent and ephemeral catalogs for MotherDuck projects.
  • The built-in scheduler uses the motherduck engine adapter type.

Important notes​

  • SQLMesh needs permission to create and access the databases, schemas, and objects it manages.
  • Use a service account for shared or automated SQLMesh projects so ownership is stable.
  • The SQLMesh documentation also covers advanced connection options such as extensions, connector config, and external-source secrets.

Use cases​

  • Run SQLMesh transformation plans against MotherDuck.
  • Manage model state and environments with MotherDuck as the execution engine.
  • Use service-account credentials for scheduled SQLMesh runs.

Related content​