Support upsert operations for SQL datasets

[ElenaKhaustova]

Description

In the reference project kedro-agentic-workflows, we needed to create a custom dataset (SQLAlchemyEngineDataset) that returns a db_engine. This allowed us to handle writes and updates to the database within agentic workflows (e.g., creating claims, updating sessions, logging interactions).

While this approach works, it feels like a workaround. The current Kedro SQL datasets primarily focus on read-only workflows (e.g., SQLQueryDataSet) or batch inserts. They do not directly support upsert (insert + update) patterns, which are increasingly common when dealing with LLM-driven workflows, streaming data, or session management.

The goal of this ticket is to explore how Kedro can better support upserts in SQL datasets without requiring users to drop down to engine-level operations and whether it makes sense from the framework perspective.

Context

• In LLM/agent workflows, we frequently need to:
  • Create new records (e.g., new claims).
  • Update existing records (e.g., session end timestamps, claim statuses).
  • Log events incrementally rather than in bulk.
• Current datasets require manual SQLAlchemy connections or custom datasets.
• A first-class Upsert-capable SQL dataset could simplify workflows and make them more idiomatic within Kedro.

[merelcht]

Older PR with conversation around upsert: #964

[deepyaman]

Supporting upserts should be done through Ibis:

• docs: 📚 Add documentation on working with SQL in Kedro #4706 recommends Ibis as the modern way to work with SQL in Kedro (cc @datajoely)
• Support for Upsert functionality in ibis.TableDataset kedro-plugins#835 proposes to add upsert mode to ibis.TableDataset
• feat(api): implement upsert() using MERGE INTO ibis-project/ibis#11624 implements Backend.upsert() to enable the above

By the way, the reference project link appears broken (or private?).

[datajoely]

We've never been able or reconcile Kedros reproducibility mission with this requirement.

I'd be tempted to introduce a new node type that enables upserts, conditionals etc in a backwards compatible and non breaking way

[datajoely]

Also plus 1 for ibis everything

[ElenaKhaustova]

By the way, the reference project link appears broken (or private?).

Updated the link since the project was published in kedro-academy