Transactional idempotent in-database ETL on SAP HANA with a row-count check

This blueprint runs a transactional, idempotent in-database ETL on SAP HANA that fully reloads a single date partition on every execution. It stages raw orders for the load date, performs a delete-then-insert on the fact partition, cleans up scoped staging, and then verifies that source and target row counts agree before the run is declared successful. Pushing the multi-statement transform into HANA keeps heavy joins next to the in-memory column store, while Kestra owns atomicity, scheduling, retries, alerting, and a data-quality gate. The delete-then-insert per partition is what makes the load idempotent: re-runs and backfills produce identical results, and rows deleted at source for a date are removed from the fact table too. It solves the classic SAP HANA ETL problem of partial writes and silent drift when a multi-step load is run by a bare scheduler with no transaction boundary or verification.

How it works

1. The nightly_load io.kestra.plugin.core.trigger.Schedule trigger fires at 0 2 * * * in Etc/UTC. The load_date input defaults to {{ (trigger.date ?? execution.startDate) }}, so a manual run or backfill reloads exactly that day's partition.
2. The transform_etl task (io.kestra.plugin.jdbc.hana.Queries) runs five statements in one atomic transaction (transaction: true): clear staging for the load date, stage raw rows, delete the fact partition, re-insert it from staging, then clear staging again. Any statement failure rolls back the whole transaction. A constant retry (PT1M, maxAttempt: 3) absorbs transient HANA blips.
3. The verify_row_counts task (io.kestra.plugin.jdbc.hana.Query, fetchType: FETCH_ONE) returns the raw ingest count and the fact count for the load date in a single row.
4. The assert_counts_match task (io.kestra.plugin.core.flow.If) compares RAW_COUNT to FACT_COUNT: a match logs success via io.kestra.plugin.core.log.Log, a mismatch fails the run with io.kestra.plugin.core.execution.Fail and an explicit message.
5. On any failure, the flow-level errors block posts a Slack alert through io.kestra.plugin.slack.notifications.SlackIncomingWebhook with the execution id.

What you get

• Atomic, all-or-nothing partition reloads with no partial writes.
• Idempotent loads: safe re-runs and backfills, with source deletes propagated to the fact table.
• A meaningful row-count gate that measures what this run actually reloaded, not a cumulative total.
• Automatic Slack alerting on failure and built-in retries for flaky connections.
• concurrency: { limit: 1 } so two nightly loads never overlap or interleave staging.

Who it's for

• Data engineers running scheduled SAP HANA warehouse loads who need correctness guarantees.
• Analytics and BI teams who depend on a fact table that is trustworthy after every load.
• Platform teams consolidating ad hoc HANA SQL jobs into observable, alerting pipelines.

Why orchestrate this with Kestra

A database scheduler can run SQL on a cron, but it cannot gate the run on a data-quality check, fail the pipeline on count drift, retry only the transient failures, alert Slack with execution context, or give you per-execution lineage and replayable history. Kestra wraps the in-database transform in declarative YAML with event and schedule triggers, retries, conditional branching, and end-to-end observability, the exact gap SAP HANA's own SQL scheduler cannot fill.

Prerequisites

• A reachable SAP HANA instance (the JDBC URL uses port 39015; adjust the instance number and databaseName for your tenant). The HANA JDBC driver (ngdbc) ships with the Kestra JDBC plugin.
• Tables RAW.ORDERS_INGEST, STAGING.ORDERS_STG, and WAREHOUSE.FACT_ORDERS (or your equivalents), each with an ORDER_DATE column.
• A database user with INSERT, DELETE, and SELECT on those schemas.

Secrets

• HANA_HOST: hostname or IP of the SAP HANA server.
• HANA_USERNAME: SAP HANA database user with read and write access to the ETL schemas.
• HANA_PASSWORD: password for that database user.
• SLACK_WEBHOOK: Slack Incoming Webhook URL for failure alerts.

Quick start

1. Add the four secrets above to your Kestra instance.
2. Adjust the schema and table names in the SQL to match your warehouse model.
3. Trigger the flow manually with a specific load_date to validate the reload and the count gate.
4. Let the nightly 0 2 * * * schedule run it, and confirm Slack alerts route correctly.

How to extend

• Swap the fact delete-then-insert for MERGE INTO WAREHOUSE.FACT_ORDERS ... ON target.ORDER_ID = source.ORDER_ID when you must preserve fact rows absent from the current raw batch, and verify with a checksum or EXCEPT instead of a count.
• Parameterize the schema and table names as inputs to reuse the flow across multiple fact tables.
• Replace the schedule with a flow or webhook trigger to chain this load after an upstream ingestion.
• Add more verification queries (sum of amounts, null checks) before the assert_counts_match gate.

Links

• SAP HANA plugin docs: https://kestra.io/plugins/plugin-jdbc-hana