Handling of COPY INTO parameters in Snowflake dialect

[dym-ok]

Search before asking

• [X] I searched the issues and found no similar issues.

What Happened

We discovered that the following query will trigger a parsing error

COPY INTO 's3://geotags.csv.gz'
FROM
(
  SELECT DISTINCT
    ID,
    CAST(Z.VALUE AS INTEGER) AS LISTING_ADDRESS_POSTALCODE
  FROM
    ANALYTICS_PROD.SERVICE.GEO_DATA_LAYER_FLATTEN,
    LATERAL FLATTEN(ZIPS) AS Z
  WHERE
    TYPE IN ('canton', 'region', 'zip')
    AND PARENTPATHS LIKE '%geo-country-switzerland%'
) STORAGE_INTEGRATION = SI_S3_DS_ASSETS FILE_FORMAT = (
  TYPE = CSV NULL_IF = () EMPTY_FIELD_AS_NULL = FALSE COMPRESSION = GZIP
) SINGLE = TRUE OVERWRITE = TRUE HEADER = TRUE MAX_FILE_SIZE = 5368709120;

and following will work just fine

COPY INTO 's3://geotags.csv.gz'
FROM
(
  SELECT DISTINCT
    ID,
    CAST(Z.VALUE AS INTEGER) AS LISTING_ADDRESS_POSTALCODE
  FROM
    ANALYTICS_PROD.SERVICE.GEO_DATA_LAYER_FLATTEN,
    LATERAL FLATTEN(ZIPS) AS Z
  WHERE
    TYPE IN ('canton', 'region', 'zip')
    AND PARENTPATHS LIKE '%geo-country-switzerland%'
) STORAGE_INTEGRATION = SI_S3_DS_ASSETS FILE_FORMAT = (
  TYPE = CSV NULL_IF = () EMPTY_FIELD_AS_NULL = FALSE COMPRESSION = GZIP
) HEADER = TRUE SINGLE = TRUE OVERWRITE = TRUE MAX_FILE_SIZE = 5368709120;

Expected Behaviour

Linting should not reveal any errors.

Observed Behaviour

sqlfluff lint -v homegate/heatmap_v2/sql/sf_select_geotags.sql

==== sqlfluff ====
sqlfluff:                3.0.7 python:                 3.11.7
implementation:        cpython verbosity:                   1
dialect:             snowflake templater:               jinja
rules:                                all
==== readout ====

== [homegate/heatmap_v2/sql/sf_select_geotags.sql] FAIL
L:  15 | P:  48 |  PRS | Line 15, Position 48: Found unparsable section:
                       | 'MAX_FILE_SIZE = 5368709120;'
WARNING: Parsing errors found and dialect is set to 'snowflake'. Have you configured your dialect correctly?
==== summary ====
violations:        1 status:         FAIL
All Finished 📜 🎉!

How to reproduce

Just take the content from the example query and run lint command against the file.

Dialect

Snowflake

Version

sqlfluff, version 3.0.7

Configuration

[sqlfluff] templater = jinja dialect = snowflake exclude_rules = RF01,RF02,RF04,LT01,CV08,AL01

large_file_skip_byte_limit = 0 max_line_length = 0

[sqlfluff:templater:jinja] library_path = sqlfluff_libs

[sqlfluff:templater:jinja:context] var = { "value": { "s3_bucket_ds_assets": "bucket", "ds_output_database": "HG_ANALYTICS_PROD" } } dag = {"dag_id": "test_dag"} params = { "ds": "2021-01-01", "data_asset_name": "test_data_asset", "offer_type": "RENT", "look_back_days": 30, "platform": "homegate" }

[sqlfluff:layout:type:alias_expression] spacing_before = align align_within = select_clause spacing_after = touch

[sqlfluff:indentation] tab_space_size = 2 indent_unit = space indented_joins = false indented_using_on = true allow_implicit_indents = true indented_on_contents = false indented_ctes = false

[sqlfluff:rules:aliasing.table] aliasing.table = explicit

[sqlfluff:rules:aliasing.column] aliasing.column = explicit

[sqlfluff:rules:aliasing.expression] allow_scalar = True

[sqlfluff:rules:ambiguous.join] fully_qualify_join_types = inner

[sqlfluff:rules:ambiguous.column_references] group_by_and_order_by_style = consistent

[sqlfluff:rules:capitalisation.keywords] capitalisation_policy = upper

[sqlfluff:rules:capitalisation.identifiers] extended_capitalisation_policy = upper unquoted_identifiers_policy = all

[sqlfluff:rules:capitalisation.functions] extended_capitalisation_policy = upper

[sqlfluff:rules:capitalisation.literals] capitalisation_policy = upper

[sqlfluff:rules:capitalisation.types] extended_capitalisation_policy = upper

[sqlfluff:rules:jinja.padding] single_space = true

[sqlfluff:rules:layout.spacing] no_trailing_whitespace = true extra_whitespace = false

[sqlfluff:rules:layout.functions] no_space_after_function_name = true

[sqlfluff:rules:layout.select_targets] wildcard_policy = single

[sqlfluff:rules:layout.set_operators] set_operator_on_new_line = ['UNION', 'UNION ALL']

[sqlfluff:rules:structure.nested_case]

[sqlfluff:rules:structure.subquery] forbid_subquery_in = join

Are you willing to work on and submit a PR to address the issue?

• [X] Yes I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct