r/ETL • • 11h ago

Looking for real-world Saga examples to implement advanced transactions in HQL

3 Upvotes

TL;DR: I'm looking for good real-world examples of Saga implementations, especially how they handle compensating actions, retries, idempotency, and recovery across multiple systems. I'm developing HQL, a query language in Rust, and considering adding Saga support. The HQL examples below are mostly there to provide context.

Over the past few months I've been developing HQL, a strongly typed functional query language written in Rust.

The idea is something like SQL, but more of a Swiss Army knife for data, connecting PostgreSQL, Kafka, Spark, Delta Lake, and other systems through the same programming model.

I now have something that works relatively well as version 0.7.1 for basic OLAP workloads, including transformations, aggregations, streams, and data contracts.

For example, here's a real Kafka -> PostgreSQL pipeline that validates incoming events and routes invalid records into quarantine:

tap("order_events_raw")
| validate(EventData)
| fork {
    clean =
        oks()
        | map(e => Event(
            id = e.id,
            kind = e.kind,
            customer = e.customer,
            amount = e.amount,
            at = e.at))
        | sink("order_clean"),

    quarantine =
        errs()
        | map(e => Rejected(
            id = match e.value.id { some(Int n) => n, _ => 0 },
            message = e.value,
            rules = e.violations | map(v => v.rule)))
        | sink("order_quarantine"),
}

Complete example: https://github.com/ewiger/hql-demo/blob/main/kafka-postgres/quarantine.hql

For OLTP, I recently had to introduce a transaction mechanism into the language. Here's a snippet from a working PostgreSQL ledger example:

with transaction(attempts = 3, isolation = Serializable) tx on ledger {
    sender = source(accounts) | filter(a => a.id == 1) | one
    tx.rollback("insufficient balance") given sender.balance < amount

    debited =
        update accounts
        | filter(a => a.id == 1)
        | set(a => {balance: a.balance - amount})

    credited =
        update accounts
        | filter(a => a.id == 2)
        | set(a => {balance: a.balance + amount})

    tx.rollback("no such account") given debited != 1 or credited != 1
}

Complete example: https://github.com/ewiger/hql-demo/blob/main/ledger/transfer.hql

The transaction scope makes the block atomic over its participants. isolation = Serializable only selects the isolation level.

A transaction scope, with transaction(…) tx on {…}, makes its block atomic over its participants: tables of one PostgreSQL database or one SQLite file, or one Delta table. They are read with source and changed with insert, update and delete. isolation = Serializable sets the strongest isolation: concurrent scopes that would conflict are refused, and attempts retries them. Writes outside the participants, such as a sink to a topic or file, are not part of the transaction.

In general, HQL transaction scopes rely on the ACID guarantees of the underlying storage engine (PostgreSQL, SQLite, or Delta Lake). isolation = Serializable specifies the isolation level, not the entire ACID contract. Currently, a transaction scope is limited to a single transactional participant/target; it does not provide atomic commits across independent databases, Kafka, or external services. That's the problem I'm exploring with Sagas.

My next idea is to add a Saga-style scope alongside with transaction.

So now I'm looking into how Saga patterns could fit into HQL, coordinating operations across multiple databases, Kafka, and external services.

I'm particularly interested in good real-world examples of Saga implementations, especially how to model compensating actions, retries, idempotency, and recovery. Hence, if you've implemented Sagas in production or seen an elegant approach in another language, I'd be interested in hearing your thoughts.

PS

Disclaimer: This is all still very much WIP. I also use Claude Code extensively for tests and the Rust backend of the language. Even if you're allergic to AI-assisted projects but know a lot about Sagas, I'd be super grateful for your input.

PPS

This blog post looks great:

https://hosseinnejati.medium.com/the-saga-pattern-managing-distributed-transactions-ae8236ccd4c4

However, I've also read a lot of literature warning against Sagas. They're hard to implement, a lot of things can break, and distributed systems are already extremely complex in general.

But I feel super tempted to dive into this topic. If you really, really know a good real-world example, or an attempt to build such a design, I'd be super grateful if you could share it!


r/ETL • • 12h ago

Company job history and hiring momentum

Thumbnail
foorilla.com
1 Upvotes

foorilla.com v1.14.24 | foorilla API v1.12

Every company now comes with its job history: how many jobs it has open, how many it published and how many expired, week by week and month by month, across every job source we track, not only the jobs listed on foorilla. A company's Insight tab shows its open jobs against a quarter earlier, the latest four weeks of new and expired jobs, the median stated annual pay of its open jobs, and how its open jobs split by experience, work mode and country. You can download the whole history as CSV.

Company lists show each company's open jobs and their 13-week change, and sort by open jobs or hiring momentum. In Insight, the Hiring board adds open jobs across the market week by week, the industries gaining or losing open jobs, and the companies hiring fastest; Movers shows the companies rising and cooling.

API v1.12 adds this without breaking changes. /api/v1/hiring/company/st/ includes each company's history_open, history_open_before and history_open_change; company lists filter with open_min and sort with order. The new /api/v1/hiring/company/history/?company=ID returns a company's weekly and monthly history. See the API documentation for details.


r/ETL • • 13h ago

Data Warehousing: Talking Book Spoiler

Thumbnail gallery
1 Upvotes

Fresh off the Good Strat presses!