SQL Files

Bauplan supports SQL both as part of a Python function and as standalone .sql files. The latter is particularly convenient for users coming from the dbt world.

Resolving models

A model reference is of the form Model("namespace.table") or Model("table") and is used by Bauplan to identify which model produces a desired table. Bauplan resolves a model reference from one of three sources: the lake, a python function (python model), or an included .sql file (SQL model).

Case 1: from the lake

In the simplest case below, Bauplan resolves input_table as a table in the Bauplan lakehouse.

from typing import Annotated
import bauplan

class ModelOutput(bauplan.TableSchema):
    """The columns this model returns."""

    col: bauplan.Int64

@bauplan.model()
@bauplan.python("3.13")
def my_model(
    data: Annotated[pyarrow.Table, bauplan.Model("input_table")],
) -> Annotated[pyarrow.Table, ModelOutput]:
    return data.select(['col'])

Case 2: from a Python node

When a pipeline consists of multiple connected nodes, Bauplan resolves inter-node dependencies automatically. In the example below, the output of my_first_model feeds directly into my_second_model.

from typing import Annotated
import bauplan

class FirstModelOutput(bauplan.TableSchema):
    """The columns `my_first_model` returns."""

    col_first: bauplan.Int64
    col_second: bauplan.Float64

@bauplan.model()
@bauplan.python("3.13")
def my_first_model(
    data: Annotated[pyarrow.Table, bauplan.Model("input_table")],
) -> Annotated[pyarrow.Table, FirstModelOutput]:
    return data.select(['col_first', 'col_second'])

class SecondModelOutput(bauplan.TableSchema):
    """The columns `my_second_model` returns."""

    col_second: bauplan.Float64

@bauplan.model()
@bauplan.python("3.13")
def my_second_model(
    data: Annotated[pyarrow.Table, bauplan.Model("my_first_model")],
) -> Annotated[pyarrow.Table, SecondModelOutput]:
    return data.select(['col_second'])

Case 3: from a SQL file

Bauplan can also resolve a model from an included .sql file. Consider a project with the following structure:

my_pipeline/
├── bauplan_project.yaml
├── models.py
├── my_file.sql
├── pyproject.toml
└── views/
    └── my_view.sql

By default, Bauplan discovers .sql files placed directly in the project root; in this case, my_file.sql. Files in subdirectories (such as views/), however, are not auto-discovered. To include them, add an include_paths field to bauplan_project.yaml with a list of glob patterns:

project:
  id: 3a1b5af7-ad28-477c-a255-028558ec07c6
  name: my_pipeline
include_paths:
  - views/*.sql

The following rules apply to include_paths:

1. include_paths accepts only explicit .sql glob patterns. For instance, views/*.sql is acceptable but views/* is not.
2. Paths must be relative to the project root; absolute paths are rejected.
3. Upward traversal such as ../common/*.sql is not allowed.
4. git-ignored files are excluded even if they match a pattern.
5. Symlinks pointing outside the project directory are rejected.

Patterns that violate these rules cause an explicit failure at planning time.

note

Naming conflicts are resolved strictly. If a model is defined both in the lake and in a Python or SQL file, Bauplan fails early with Graph contains a cycle or graph changed during iteration. If both a Python and a SQL file define a model with the same name, Bauplan fails at parse time with an error similar to Duplicate definition of 'my_model' in ./models.py:34 and ./my_model.sql.

How to use SQL files

By default, Bauplan derives the model name from the filename. Given a file called my_titanic_view.sql:

SELECT
    Age,
    Survived
FROM bauplan.titanic

Bauplan interprets it as a model named my_titanic_view. This behavior can be overridden with a header comment:

-- bauplan: name=my_custom_name
SELECT
    Age,
    Survived
FROM bauplan.titanic

Multiple overrides can be stacked:

-- bauplan: name=my_custom_name
-- bauplan: materialization_strategy=REPLACE
-- bauplan: output_schema=ModelOutput
SELECT
    Age,
    Survived
FROM bauplan.titanic

All available options are documented in the Python SDK reference.