[KEP-8] Jinja2 templating in SQL-string datasets via load_args.template_params

[nikanton]

Q1. What are you trying to do?

Make it possible to write one SQL file and reuse it across multiple Kedro datasets that differ only in filter values — for example, the same feature-engineering query with small changes, e.g. extra filters, used in both training and inference, or the same query run over different date windows. Today this is impossible without either copy-pasting the SQL file. The proposal is to let users add a small block of variables under load_args.template_params in the catalog, and have the dataset substitute those variables into the SQL before sending it to the database.

Q2. What problem is this proposal NOT designed to solve?

• A new dataset type. No new public class. The change is additive to existing datasets via a single new load_args key.
• Templating in non-SQL datasets. The reusable helper introduced here could be applied elsewhere later, but this KEP only commits to the two SQL-string datasets listed.

Q3. How is it done today, and what are the limits of current practice?

pandas.SQLQueryDataset and polars.PolarsDatabaseDataset accept either an inline sql string or a filepath to a .sql file and pass it to the driver verbatim.

The practical workarounds in production Kedro projects today are:

1. Duplicate the SQL file per variant — events_train.sql, events_inference.sql, etc. Duplicated CTEs drift out of sync; bug fixes in one variant silently miss the others; new contributors cannot tell which file is canonical.
2. Wrap the dataset in a custom subclass — works but every team reinvents it slightly differently and the experience is not portable across projects.

Q4. What is new in your approach and why do you think it will be successful?

Three properties combined make this approach distinct from past attempts to template SQL:

1. Eliminates SQL duplication across pipelines. This is the load-bearing benefit. Teams today maintain near-identical SQL files for training vs. inference, for different date windows, or for tenant-specific variants — and those copies drift apart over time, producing training/serving skew and silently divergent bug fixes. A single .sql.j2 template parameterised via template_params collapses each family of duplicated files into one canonical source of truth, so a fix made once propagates everywhere.
2. Opt-in by data, not by config. Templating activates only when template_params is present in load_args. Absent that key, behaviour is byte-for-byte identical to today. There is no global switch, no file-extension magic, no environment variable.
3. Reusable across datasets, not bolted onto one. The rendering logic lives as a single helper function in kedro_datasets._utils. Both pandas.SQLQueryDataset and polars.PolarsDatabaseDataset consume the helper in two lines of their load() method. Future SQL-string datasets (Snowflake, Redshift, ibis-based, custom contrib datasets) can opt in identically — avoiding the same divergence at the dataset-implementation layer that the feature itself eliminates at the SQL layer.

Q5. Who cares? If you are successful, what difference will it make?

• Data scientists stop duplicating SQL files across training and inference and stop reimplementing the same feature logic in Python — eliminating an entire class of training/serving skew bugs.

Q6. What are the risks?

Risk	Severity	Mitigation
Users accidentally enable templating and a literal {{ in their SQL breaks rendering.	Low	Templating only activates when template_params is set. A SQL string with no template_params is never sent through Jinja.
SQL injection via template_params values.	Medium	Document clearly: template_params controls structure, not user-supplied values. Values that originate from end users must still go through SQLAlchemy bind parameters (load_args.params). Mirrors dbt and Airflow guidance.
Jinja2 becomes a hard dependency of the SQL extras.	Low	Already an indirect Kedro dependency via cookiecutter and a direct dependency of kedro-airflow. Lazy-imported inside the helper, so other datasets pay zero cost.

Q7. How long will it take?

• Shared helper + tests + docs: ~1–2 days of focused work.
• pandas.SQLQueryDataset integration: ~0.5 day (two-line call site + tests + docstring update).
• polars.PolarsDatabaseDataset integration: ~0.5 day (mirrors pandas).

Estimated total: under two weeks of part-time effort.

Q8. What are the mid-term and final "exams" to check for success?

Mid-term:

• The new render_sql helper has dedicated unit tests covering: no-op when template_params is absent, inline string rendering, file-based rendering with sibling macro import, StrictUndefined raising on a missing variable, and composability with load_args.params bind parameters.
• The existing SQLQueryDataset test suite passes unchanged (backwards compatibility).
• A documentation example shows the training/inference one-file pattern from issue Release kedro #1385.

Final:

• No regression issue filed against the new code path within one release cycle.

Appendix A. Proposed API Changes

New load_args key on pandas.SQLQueryDataset and polars.PolarsDatabaseDataset:

events_train:
  type: pandas.SQLQueryDataset
  filepath: sql/events.sql.j2
  credentials: warehouse
  load_args:
    template_params:               # NEW — opt-in templating trigger
      mode: train
      start_date: "${globals:train_start_date}"
      end_date:   "${globals:train_end_date}"
    params:                        # existing — SQLAlchemy bind params
      tenant_id: 42

Semantics:

• template_params is a mapping of variable name → value. Values may be any JSON-serialisable scalar or list (whatever Jinja2 will accept).
• When template_params is present, the SQL (inline or file-loaded) is rendered through Jinja2 with StrictUndefined before being passed to the driver.
• When absent (or None, or {}), behaviour is unchanged.
• template_params is not forwarded to the underlying driver. It is consumed and removed during load().
• All other existing load_args (params, parse_dates, index_col, chunksize, etc.) are unaffected.

Backwards compatibility: fully additive. No existing catalog, dataset, or pipeline behaves differently. The only observable change for users not opting in is a new entry in the dataset's documented load_args reference.

Forwards compatibility: the template_params key is namespaced inside load_args and so cannot collide with future positional/keyword arguments of pd.read_sql_query or pl.read_database.

Appendix B. Design Sketch

Reusable helper

A new module kedro_datasets/_utils/sql_templating.py:

from __future__ import annotations

import os
from collections.abc import Mapping
from typing import Any


def render_sql(
    sql: str,
    template_params: Mapping[str, Any] | None,
    search_path: str | os.PathLike[str] | None = None,
) -> str:
    """Render `sql` through Jinja2 when `template_params` is provided.

    - Returns `sql` unchanged when `template_params` is None or empty.
    - Uses StrictUndefined so missing variables raise at render time.
    - When `search_path` is a local directory, a FileSystemLoader is configured
      so the template can import sibling macros via `{% from '...' import ... %}`.
    - Remote (fsspec) paths fall back to inline rendering only; cross-filesystem
      macro support is deferred to a future iteration.
    """
    if not template_params:
        return sql

    # Lazy import so datasets that don't use templating pay zero import cost.
    from jinja2 import Environment, FileSystemLoader, StrictUndefined

    loader = FileSystemLoader(str(search_path)) if search_path else None
    env = Environment(loader=loader, undefined=StrictUndefined, autoescape=False)
    template = env.from_string(sql)
    return template.render(**template_params)

Re-exported from kedro_datasets/_utils/__init__.py:

from .sql_templating import render_sql  # noqa: F401

This mirrors the existing _utils conventions (ConnectionMixin, validate_sub_path, spark_utils.get_spark, databricks_utils.split_filepath) and is trivially adoptable by any future SQL-string dataset.

Integration in pandas.SQLQueryDataset

Two-line change inside load() (current code at kedro-datasets/kedro_datasets/pandas/sql_dataset.py:553-563):

def load(self) -> pd.DataFrame:
    load_args = copy.deepcopy(self._load_args)

    if self._filepath:
        load_path = get_filepath_str(PurePosixPath(self._filepath), self._protocol)
        with self._fs.open(load_path, mode="r", encoding=self.encoding) as fs_file:
            load_args["sql"] = fs_file.read()

    # NEW: apply Jinja2 templating if requested
    template_params = load_args.pop("template_params", None)
    search_path = (
        Path(self._filepath).parent
        if self._filepath and self._protocol == "file"
        else None
    )
    load_args["sql"] = render_sql(load_args["sql"], template_params, search_path)

    return pd.read_sql_query(
        con=self.engine.execution_options(**self._execution_options), **load_args
    )

Integration in polars.PolarsDatabaseDataset

Symmetric change at kedro-datasets/kedro_datasets_experimental/polars/polars_database_dataset.py:322-338:

def load(self) -> pl.DataFrame:
    load_args = copy.deepcopy(self._load_args)

    if self._filepath:
        load_path = get_filepath_str(PurePosixPath(self._filepath), self._protocol)
        with self._fs.open(load_path, mode="r") as fs_file:
            query = fs_file.read()
    elif "sql" in load_args:
        query = load_args.pop("sql")
    else:
        query = f"SELECT * FROM {self.table_name}"  # nosec B608

    # NEW: apply Jinja2 templating if requested
    template_params = load_args.pop("template_params", None)
    search_path = (
        Path(self._filepath).parent
        if self._filepath and self._protocol == "file"
        else None
    )
    query = render_sql(query, template_params, search_path)

    return pl.read_database(query=query, connection=self.engine, **load_args)

Macro example

{# sql/_macros.sql.j2 #}
{%- macro date_window(mode, start_date=None, end_date=None) -%}
  {%- if mode == "train" %}
    AND event_time >= DATE '{{ start_date }}'
    AND event_time <  DATE '{{ end_date }}'
  {%- elif mode == "inference" %}
    AND event_time >= SYSDATE - INTERVAL '24' HOUR
  {%- endif -%}
{%- endmacro -%}

{# sql/events.sql.j2 #}
{%- from '_macros.sql.j2' import date_window -%}
SELECT ...
FROM events
WHERE 1=1
  {{ date_window(mode, start_date=start_date, end_date=end_date) }}

Dependency declaration

In kedro-datasets/pyproject.toml:

pandas-sqlquerydataset = [
  "kedro-datasets[pandas-base]",
  "SQLAlchemy>=1.4, <3.0",
  "pyodbc>=4.0",
  "Jinja2>=3.0",          # NEW
]

And a corresponding new extra for the experimental Polars dataset (or whichever extra currently gates PolarsDatabaseDataset).

Testing strategy

A new file kedro-datasets/tests/_utils/test_sql_templating.py covers the helper in isolation:

• Returns input unchanged when template_params is None.
• Returns input unchanged when template_params is {}.
• Substitutes a simple {{ var }} from an inline string.
• Raises jinja2.UndefinedError when a referenced variable is missing.
• Imports a sibling macro when search_path is provided.
• Does not import macros when search_path is None.

The existing test_sql_dataset.py and test_polars_database_dataset.py suites gain a small set of integration tests that confirm:

• A dataset with template_params set renders the SQL before the driver sees it (verifiable via a stub engine that captures the executed string).
• A dataset without template_params works exactly as before (byte-for-byte SQL).
• template_params and params (bind parameters) coexist.

Appendix C. Rejected Designs

I didn't explore possibility of using OmegaConf instead of Jinja2

[deepyaman]

Have you explored using ibis.TableDataset to load a table reference, transform the data in a node, and stream the data into pandas or Polars? I personally don't like the idea of writing Jinja-templated SQL in YAML files; feels like dbt (but worse). With the transformation logic in a node, you could also leverage parameters more directly, passing them to nodes where the transformations are performed.

[Galileo-Galilei]

First of all, thank you for the very well explained KEP.

On the functional need of such a mechanism

Like @deepyaman ,I tend to nudge users into using more pythonic ways of transforming data with libraries like Ibis. That said, there are no really ibis equivalent to "write in python and delegate the compute to SQL" that I am aware of, and despite it beeing reasonably popular, we cannot expect most of our users to switch their codebase, especially since it has less private fundings that it used to have. I sometimes suggest to "load from SQL with minimal filters so the table is easy to handle and then worj in python", but for big tables that don't fit in memory, I don't have better suggestion than "use sql files" for most users.

Given this, I've seen a lot of users doing some SQL transformations with kedro, and I've seen a fair share of them implementing exactly this feature, most of the times with suspicious design to say the least (including a data analysis team that used to create their own parser with reserved character and regex 😨). I've also seen a lot of reimplementation with subtle variations of this so I feel we need to support it in a way; without encouraging it too much.

On the technical suggestions

• Maybe the external function can be a parent method of the AbstractDataset class, and renamed render_jinja because it's not specific to SQL? It may be breaking the Liskov principle of SOLID though
• the other design choices seem sound to me :
  • the reserved key in load_args seem to be the path of less breaking changes ; I'd be more in favor to a new entry to keep load_args "pure" *args passed to the underlying reading function of the package associated to the dataset, but this would be much more breaking
  • OmegaConfigLoader is for config and more limited, I think jinja support is better suited here.
  • The jinja dependency does not seem a big deal and is dataset specific but we've made huge efforts to reduce the number of dependencies so I'd like others to double check

Conclusion

I can commit to shepherd this KEP if other validate traction, especially my presonal observations that some kedro users ARE doing this currently

[deepyaman]

That said, there are no really ibis equivalent to "write in python and delegate the compute to SQL" that I am aware of

What do you mean by this?

Given this, I've seen a lot of users doing some SQL transformations with kedro

My issue with the proposed approach is that these are transformations, and thus should happen in nodes. This would better enable users to do their transformations on the catalog, which I see as an antipattern.

Even in dbt, SQLMesh, etc., transformations (defined in SQL) are separated from I/O.

I've seen a fair share of them implementing exactly this feature

Agree we don't want people continuing to roll their own implementations of this; really need that "working with SQL" section in the docs sometime. :)

All said, I do have issues with the proposal as is, but would be interested in having a deeper discussion on the right and lowest-friction ways to enable users, since it's definitely important to solve.