Documentation by ClonePartner

Start typing to search.

Task Reference

ensure_table

View Markdown

Run idempotent DDL against a SQL Connector once per task run so later steps can stage or upsert records into the tables it creates.

ensure_table

Runs DDL statements against a SQL Connector so the tables a pipeline writes to exist before any row arrives. The step passes its input through unchanged.

When to use it

Use ensure_table at the top of a task that stages rows into a SQL database, so the schema is bootstrapped by the task itself instead of a manual setup script. It only issues DDL — to write rows, follow it with upsert_record or a call_method step.

Configuration

Key Type Required Default Description
connector string Yes — Name of a SQL Connector slot (sqlite, mssql, mysql, or postgres). Any other connector type fails with does not support SQL operations.
ddl string No — DDL statements to execute, separated by ;.
statements string No — Alias for ddl. Used only when ddl is not set.
schema_file string No — Path to a file whose contents are read and executed as the statements. Used only when neither ddl nor statements is set.

One of ddl, statements, or schema_file must resolve to a non-empty string; otherwise the step fails with ensure_table requires ddl, statements, or schema_file.

Example

name: stage_contacts
connectors:
  source:
    type: mongodb
  staging:
    type: sqlite
steps:
  - name: setup_tables
    type: ensure_table
    config:
      connector: staging
      ddl: |
        CREATE TABLE IF NOT EXISTS contacts (
          source_id TEXT PRIMARY KEY,
          name TEXT,
          email TEXT
        )
  - name: read_contacts
    type: call_method
    config:
      connector: source
      method: find
      args:
        collection: contacts
  - name: stage_contact
    type: upsert_record
    config: |
      {
        "connector": "staging",
        "table": "contacts",
        "key": "source_id",
        "data": {
          "source_id": $string(input._id),
          "name": input.name,
          "email": input.email
        }
      }

Behavior notes

  • The DDL runs once per step instance, not once per item: the first item triggers the run and every later item awaits the same promise. Because DDL such as CREATE TABLE IF NOT EXISTS is idempotent, the step is safe wherever it sits in the pipeline, including after a streaming source.
  • The statements are executed through the Connector's execute method, which splits them on ; and runs each statement.
  • Only SQL Connectors are supported. The step asserts the connector type before running and rejects MongoDB, API, and storage Connectors.
  • The input item is returned unchanged, so the step can sit inline in a pipeline without affecting downstream data.