Skip to content

RECURSIVE CTE no longer compiles correctly for INCREMENTAL insert #2083

Open
Open
RECURSIVE CTE no longer compiles correctly for INCREMENTAL insert#2083

Description

@bartekelm
bartekelm
opened on Feb 5, 2026

Recursive CTE like the example below now compiles to non-working code due to proxy-procedure being used. Worked before on dataform/core=2.4.2.

Code:

EXECUTE IMMEDIATE  """
CREATE OR REPLACE PROCEDURE project.schema.df_09289b69b6be2dab9e8ed4e01adaf8caa53b57ceca86ea247a2494e585e21160() OPTIONS(strict_mode=false)
BEGIN 
    INSERT INTO project.schema.table
    (""" || dataform_columns_list || """)
    SELECT """ || dataform_columns_list || """
    FROM (
     
    WITH RECURSIVE -- materializes output of non-deterministic functions

    cte AS (
        SELECT *
        FROM project.schema.some_table
    )
    ...
)

Error:

Query error: WITH RECURSIVE is only allowed at the top level of the SELECT, CREATE TABLE AS SELECT, CREATE VIEW, INSERT, EXPORT DATA statements.

Issue didn't occur in "non-procedural inserts" of versions 2.x.

Activity

  1. kolina commented on Feb 6, 2026

    @kolina
    kolina
    on Feb 6, 2026
    Contributor

    Can you show your config block? Do you use GCP Dataform?

  2. bartekelm commented on Feb 23, 2026

    @bartekelm
    bartekelm
    on Feb 23, 2026
    Author

    Yes, it's GCP DF.

    type: "incremental",
    database: dataform.projectConfig.vars.someDatabase,
    schema: "some_staging",
    tags: [some_tag_${system_name}],
    dependencies: [stg_other_${system_name}],
    bigquery: {partitionBy: "partition_date", requirePartitionFilter: true}

  3. kolina commented on Mar 2, 2026

    @kolina
    kolina
    on Mar 2, 2026
    Contributor

    I think the reason is not in a generated procedure, but in additional SELECT query wrapping your SELECT with recursive CTE.

    To fix it, the most feasible way is to create an intermediate temporary table with results of the incremental query and generate the insert statement separately. I've created an internal bug to track it. Because this is not related to open-source, can you please file an issue in our Public Tracker?

    As a workaround (somewhat), you can enable onSchemaChange value different from IGNORE. In this case we're already generating SQL with putting incremental results into a temporary table, so you can check if this works for your case (obviously if you're fine with enabling automatic incremental table changes).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions