SQL Pipelines

Purpose#

SQL Pipelines is where you write transformations by hand. A task reads from a source, applies SQL you control, and writes into a destination table with an explicit load mode.

Unlike CDC (which mirrors) or streams (which pull an API), SQL Pipelines makes no assumptions about your model. You supply the query; Abrq DIP supplies the loading, the incremental bookkeeping, the retries, the drift handling, and the run history.

The surface lives at /etl.

How the surface is organised#

Projects group tasks. A project is the unit of ownership and access control: membership, roles, and environments are all per-project.

When you create a project two things happen automatically:

• You become its Owner. The Owner role carries project administration — updating the project, deleting it, and managing its members.
• A default dev environment is created. It is unprotected, and it is the only environment created for you. Everything else you add deliberately.

Inside a project, tasks are defined once and run in an environment. The environment supplies the connectors. This split is what makes the same task runnable against a scratch database and a production database — see Environments and promotion.

Before you begin#

• Data connectors for the databases you will read from and write to. Create them first — see Data connectors.
• Connector bindings on the environment you intend to run in. A task names a role (source, destination); the environment maps that role to a real connector. An unbound role fails the run loudly.
• A destination you own. Abrq DIP writes rows into your table. It never runs ALTER against an operator-owned table — see Schema drift.

Required role#

task.create, task.update, task.delete, task.run, connection.bind and schedule.manage are environment-gated: a Developer holds them in unprotected environments only, and is refused in a protected one with the reason protected_environment. Maintainers and Owners hold them everywhere. The global admin role is an implicit Owner on every project.

Note. The builder's validation probes (below) are gated on the global role ladder, not on project capabilities: editor or higher for preview, render-preview, drift, quality and contract checks; any authenticated user for SQL validation. They are read-only and never write to your data.

Create a project#

1. Go to /etl and choose New project.
2. Give it a name and description.
3. Save.

You are now its Owner and it has a dev environment. Bind connectors to dev before you build your first task — see Environments and promotion.

Create a task#

1. Open the project and choose New task.
2. Pick the task type (see Task types).
3. Set the source role and destination role — the names the environment resolves to connectors.
4. Supply the logic: SQL for the SQL types, or a source table for table_copy.
5. Set the destination schema and table.
6. Pick the load mode and fill in whatever that mode requires (see Load modes).
7. Set operational behaviour: timeout, retries, on_row_error, on_schema_drift.
8. Validate with the probes, then save.

Task types#

Load modes#

For the full semantics of each mode — including exactly what "newer than the watermark" means and how the SCD2 hash is computed — see Load modes.

History columns (historical_upsert only)

historical_upsert needs four bookkeeping columns. The history columns mode decides who owns them:

Default names in auto mode:

Pre- and post-SQL#

Two optional hooks run around the load, against the destination:

• Pre-SQL runs before the load. Typical uses: truncate a staging table, create an index-free landing area, set a session parameter.
• Post-SQL runs after a successful load. Typical uses: rebuild an index, refresh a materialized view, insert an audit row.

Keep both idempotent. A retried run runs them again.

Schema drift#

Drift is the source projection producing columns the destination table does not have.

The ETL vocabulary has exactly two values, and this is deliberate:

Warning. ETL never ALTERs an operator-owned table. There is no auto_add option here, unlike CDC and file ingestion, which manage their own destination tables. If you want a new column loaded, add it to the destination yourself and the next run will pick it up.

Columns present at the destination but absent from the source are tolerated: the writer simply omits them. See Schema drift policies.

Row errors and quarantine#

on_row_error decides what happens when the destination rejects an individual row — a constraint violation, a type conflict, an over-long value.

Quarantine is the right default for messy upstream data where partial progress beats a nightly failure. It is the wrong choice when the load must be all-or- nothing.

Quarantined rows are read back per run:

curl -s \
  -H "Authorization: Bearer <API_TOKEN>" \
  "https://<ABRQ_DIP_HOST>/api/v1/etl/runs/<RUN_ID>/quarantine?limit=200"

limit defaults to 200 and caps at 1000; rows come back oldest-first. Each record carries the row payload, the destination's error_text, a replay_status (pending, replayed, failed), and timestamps.

Once you have fixed the cause — widened a column, added a missing lookup row — replay the quarantined rows. Replay re-attempts either a list of specific rows or every row still pending, and reports how many were replayed, failed or skipped. Replaying requires task.run.

Data contracts#

A data contract is a list of columns the task promises the destination will have. It is enforcement, not documentation.

• expected_columns — the promised column names.
• enforce — defaults to on. When on, the contract gates the run.

The gate runs before any data is loaded or copied. If the destination table exists and any promised column is missing, the run is refused with a data- contract-breach error naming the destination table and the missing columns. It does not warn and continue.

Columns present at the destination but not declared in the contract are reported as extra and do not breach it. A contract asserts a floor, not an exact shape. Turn enforce off to keep the contract as a check you run by hand without gating runs.

Validate before you save#

The builder has six read-only probes. None of them writes to your data, and all of them can be run as often as you like.

Notes worth acting on:

• Preview honours data masking. Columns your masking policy hides for you are listed in masked_columns and their raw values are never returned. A preview is not a way around masking.
• Preview is bounded. The limit defaults to 50 rows and caps at 200; truncated tells you whether more rows existed.
• unresolved in render preview is normal. Job parameters are supplied at job-run time, so they legitimately remain unsubstituted in the builder. What should worry you is a placeholder you expected to be a built-in showing up there — that is a typo.
• Validate SQL always answers 200. Invalid SQL comes back as valid: false with a message, not as an HTTP error.
• missing_at_dest is the drift that matters. extra_at_dest is tolerated.

Data-quality checks come in four types — not_null, unique, row_count, accepted_values — each at severity warn or error. In a check-quality result, errored counts failing checks at severity error.

Run a task and read the result#

1. Open the task and choose Run.
2. Pick the environment.
3. Optionally switch on Sample run to bound the read — see Environments and promotion.
4. Watch the run detail.

The run detail shows three things:

• Logs — the ordered narrative of the run, including which connector each role resolved to, the rendered SQL, drift decisions, quarantine counts, and the failure with its cause when there is one.
• Metrics — rows read, rows written, duration, and per-phase timings.
• Quarantined rows — present when on_row_error = quarantine and the destination rejected rows. Each row shows its payload and the error text, and can be replayed from here.

Runs also appear in the cross-family monitor at Executions, alongside CDC, streams, file ingestions and exports.

Retries and timeouts#

• Timeout bounds a single run. A run that exceeds it is stopped and marked failed.
• Retry count and retry delay control automatic re-attempts after a failure. Retries repeat pre-SQL and post-SQL, which is why both should be idempotent.

Field reference#

Failure modes#

See also#

• Load modes
• Schema drift policies
• Environments and promotion
• Jobs — orchestrating tasks into a DAG
• Schedules
• Executions
• RBAC