sync push collapses transformation script[] array → runtime fails

[frantisekrehor]

Summary

sync pull → sync push roundtrip collapses parameters.blocks[].codes[].script from N elements to 1 whenever the original config has more than one element per code block. KBC API accepts the flattened config silently; runtime fails at job execution because each script[] element is supposed to map 1:1 to a separate executable unit.

Not specific to any particular SQL shape — triggers for any code containing multiple script elements (CREATE + INSERT + UPDATE, TRUNCATE + MERGE, multiple Python blocks, etc.).

Impact

• SQL transformations (Snowflake / BigQuery / Redshift / Synapse): runtime error Actual statement count N did not match the desired statement count 1, SQL state 0A000 — driver default MULTI_STATEMENT_COUNT=1.
• Python / R transformations: named sub-blocks merge into one element; runtime may still succeed but UI structure is lost.
• No warning at push time. Failure only at job execution.
• sync push is effectively unusable as the bulk-edit path for any transformation with multi-statement code blocks.

Reproduction (~2 minutes)

Needs any transformation with len(script) > 1 in at least one code block.

# 1. Create test branch
kbagent --json branch create --project {alias} --name "repro"
# → e.g. branch_id=466659

# 2. Verify starting state
kbagent --json config detail --project {alias} --branch 466659 \
  --component-id keboola.snowflake-transformation --config-id {id} \
  | jq '.data.configuration.parameters.blocks[0].codes[0].script | length'
# → 6

# 3. Clean sync pull
mkdir /tmp/repro && cd /tmp/repro && git init -q && git commit --allow-empty -m init -q
kbagent sync init --project {alias} --directory /tmp/repro --git-branching
kbagent sync branch-link --project {alias} --branch-id 466659 --directory /tmp/repro
kbagent sync pull --project {alias} --directory /tmp/repro

# 4. Any edit (or none — even no-op push reproduces)
echo "-- test" >> /tmp/repro/{branch}/transformation/{component}/{config}/transform.sql

# 5. Push
kbagent sync push --project {alias} --directory /tmp/repro

# 6. Array has collapsed
kbagent --json config detail --project {alias} --branch 466659 \
  --component-id keboola.snowflake-transformation --config-id {id} \
  | jq '.data.configuration.parameters.blocks[0].codes[0].script | length'
# → 1   ← BUG

# 7. Run job → error
kbagent --json tool call run_job --project {alias} --branch 466659 \
  --input '{"component_id":"keboola.snowflake-transformation","configuration_id":"{id}"}'

Reproduced on real production config (6 CTAS in one code block). Full error text, job ID, and KBC UI diff screenshot available on request.

Root cause

src/keboola_agent_cli/sync/code_extraction.py

_extract_sql_transformation (pull side, lines 139–145)

scripts = code.get("script") or []
for script in scripts:                         # N elements in
    if isinstance(script, str) and "\n" in script:
        lines.extend(script.split("\n"))
    else:
        lines.append(script)
lines.append("")                               # blank line AFTER loop, not BETWEEN elements

Loop appends content of each script element directly back-to-back. Element boundaries are not recorded anywhere in the output file.

_lines_to_script (helper, lines 21–32)

def _lines_to_script(lines: list[str]) -> list[str]:
    """The Keboola transformation runner treats each element of the ``script``
    array as a separate executable statement.  Joining all lines of a CODE
    block into one string ensures multi-line SQL/Python is executed as a
    single unit.
    """
    stripped = _strip_trailing_empty(lines)
    if not stripped:
        return []
    return ["\n".join(stripped)]               # hardcoded: always returns exactly 1 element

Docstring explicitly acknowledges each script[] element is a separate executable unit, yet the function always returns a single-element list.

_parse_sql_blocks (push parser, lines 173–227)

Tracks only BLOCK: and CODE: markers. No concept of script-level boundaries. All lines under one CODE marker flow into a flat list and collapse through _lines_to_script.

Missing test case

tests/test_sync_code_extraction.py::test_sql_round_trip covers only script = [<single string>]. A multi-element case exposes the bug:

def test_sql_roundtrip_multi_script():
    original = {
        "parameters": {"blocks": [{"name": "Block 1", "codes": [{
            "name": "code",
            "script": [
                "CREATE OR REPLACE TABLE a AS SELECT 1;",
                "INSERT INTO a VALUES (2);",
                "UPDATE a SET x = 3 WHERE x = 2;",
            ],
        }]}]},
    }
    with tempfile.TemporaryDirectory() as tmp:
        _extract_sql_transformation(original, Path(tmp))
        restored = _merge_sql_transformation({}, Path(tmp))
        scripts = restored["parameters"]["blocks"][0]["codes"][0]["script"]
        assert len(scripts) == 3  # currently returns 1

Environment

• kbagent CLI: v0.17.4
• keboola-mcp-server: v1.49.1
• Affected components (anything with parameters.blocks[].codes[].script[] shape):
  • keboola.snowflake-transformation
  • keboola.bigquery-transformation
  • keboola.redshift-transformation
  • keboola.synapse-transformation
  • keboola.python-transformation-v2
  • keboola.r-transformation

Workaround

Use update_sql_transformation MCP tool with str_replace — patches individual script elements via JSONPath and preserves array structure.

[padak]

Thanks for the excellent bug report @frantisekrehor -- the root cause analysis and reproduction steps were spot on.

Fixed in #120. The approach: instead of injecting artificial markers into the SQL file, we now use a state-machine SQL splitter that splits on semicolons (respecting strings, comments, and dollar-quoted blocks) -- matching the behavior of the old keboola-as-code CLI and the Keboola UI.

Verified on a real production transformation with 374 script elements across 30 code blocks (518KB of SQL) -- content-identical roundtrip.