FileFormat resource always shows changes and fails on update #174

ixti · 2023-01-12T15:43:24Z

What happened?

Everytime I run pulumi up FileFormat reports changes and tries to update. Update fails with:

Do you want to perform this update? details
  pulumi:pulumi:Stack: (same)
    [urn=urn:pulumi:production::tatari::pulumi:pulumi:Stack::tatari-production]
    ~ snowflake:index/fileFormat:FileFormat: (update)
        [id=SNOWPIPE_DB|PUBLIC|TATARI_CSV_REPORTS]
        [urn=urn:pulumi:production::tatari::snowflake:index/fileFormat:FileFormat::snowflake-tatari-file-format]
        [provider=urn:pulumi:production::tatari::pulumi:providers:snowflake::default_0_15_0::8f540542-2be1-4fb2-b1e1-43af06ce902d]
      - binaryFormat             : "HEX"
      - dateFormat               : "AUTO"
      - encoding                 : "UTF8"
      - escape                   : "NONE"
      - escapeUnenclosedField    : "\\"
      - fieldDelimiter           : ","
      - fieldOptionallyEnclosedBy: "NONE"
      - recordDelimiter          : "\n"
      - timeFormat               : "AUTO"
      - timestampFormat          : "AUTO"

Do you want to perform this update? yes
Updating (production):
     Type                           Name                          Status                  Info
     pulumi:pulumi:Stack            tatari-production             **failed**              1 error; 1 message
 ~   └─ snowflake:index:FileFormat  snowflake-tatari-file-format  **updating failed**     [diff: -binaryFormat,dateFormat,encoding,escape,escapeUnenclosedField,fieldDelimiter,fieldOptionallyEnclosedBy,recordDelimiter,timeFor


Diagnostics:
  pulumi:pulumi:Stack (tatari-production):
    &{TATARI_CSV_REPORTS SNOWPIPE_DB PUBLIC      0 false       false  [] false false false false false  false false false false false false false false false false }

    error: update failed

  snowflake:index:FileFormat (snowflake-tatari-file-format):
    error: 1 error occurred:
    	* updating urn:pulumi:production::tatari::snowflake:index/fileFormat:FileFormat::snowflake-tatari-file-format: 1 error occurred:
    	* error updating file format record delimiter on SNOWPIPE_DB|PUBLIC|TATARI_CSV_REPORTS err = 001008 (22023): SQL compilation error:
    invalid value [''] for parameter 'RECORD_DELIMITER'

Steps to reproduce

Create index.ts

new FileFormat("my-csv", {
  formatType: "CSV",
  skipHeader: 1,
  trimSpace: true,
  emptyFieldAsNull: true,
  skipBlankLines: true,
  compression: "GZIP",
  database: snowflakeDatabase,
  schema: snowflakeSchema
});

Run deploy twice

pulumi up # succeeds
pulumi up # shows changes and fails on update

Expected Behavior

there should be no changes to deploy
deployment should succeed

Actual Behavior

changes are reported even when there were no changes
deployment fails

Output of `pulumi about`

CLI
Version 3.51.0
Go Version go1.19.4
Go Compiler gc

Plugins
NAME VERSION
aws 5.26.0
aws-native 0.45.0
nodejs unknown
snowflake 0.15.0

Host
OS gentoo
Version 2.9
Arch x86_64

This project is written in nodejs: executable='/run/user/1000/fnm_multishells/6948_1673536151552/bin/node' version='v18.6.0'

Current Stack: production

TYPE pulumi:pulumi:Stack aws:cloudwatch:EventRuleEve pulumi:providers:snowflake snowflake:index/table:Table snowflake:index/table:Table snowflake:index/table:Table pulumi:providers:aws snowflake:index/table:Table pulumi:providers:aws-native aws:iam/role:Role aws-native:s3:Bucket snowflake:index/table:Table snowflake:index/table:Table aws:cloudwatch/eventRule:EventRule snowflake:index/storageInte aws:s3/bucketPolicy:BucketPolicy aws:lambda/function:Function snowflake:index/stage:Stage aws:cloudwatch/eventTarget:EventTarget aws:lambda/permission:Permission snowflake:index/fileFormat:FileFormat URN
urn:pulumi:production::tatari::pulumi:pulumi:Stack::tatari-production
ntSubscription urn:pulumi:production::tatari::aws:cloudwatch:EventRuleEventSubscription::tatari-reports-transfer-cron
urn:pulumi:production::tatari::pulumi:providers:snowflake::default_0_15_0
urn:pulumi:production::tatari::snowflake:index/table:Table::linear-spot-level-metrics-table
urn:pulumi:production::tatari::snowflake:index/table:Table::linear-spend-and-impressions-table
urn:pulumi:production::tatari::snowflake:index/table:Table::streaming-user-attribution-apps-table
urn:pulumi:production::tatari::pulumi:providers:aws::default_5_26_0
urn:pulumi:production::tatari::snowflake:index/table:Table::streaming-user-attribution-websites-table
urn:pulumi:production::tatari::pulumi:providers:aws-native::default_0_45_0
urn:pulumi:production::tatari::aws:iam/role:Role::tatari-reports-ingestion-role
urn:pulumi:production::tatari::aws-native:s3:Bucket::tatari-reports
urn:pulumi:production::tatari::snowflake:index/table:Table::streaming-spend-and-impressions-table
urn:pulumi:production::tatari::snowflake:index/table:Table::streaming-publisher-level-metrics-table
urn:pulumi:production::tatari::aws:cloudwatch:EventRuleEventSubscription$aws:cloudwatch/eventRule:EventRule::tatari-reports-transfer-cron
gration:StorageIntegration urn:pulumi:production::tatari::snowflake:index/storageIntegration:StorageIntegration::snowflake-storage-integration
urn:pulumi:production::tatari::aws:s3/bucketPolicy:BucketPolicy::tatari-reports-bucket-policy
urn:pulumi:production::tatari::aws:lambda/function:Function::tatari-reports-transfer-lambda
urn:pulumi:production::tatari::snowflake:index/stage:Stage::snowflake-stage
urn:pulumi:production::tatari::aws:cloudwatch:EventRuleEventSubscription$aws:cloudwatch/eventTarget:EventTarget::tatari-reports-transfer-cron
urn:pulumi:production::tatari::aws:cloudwatch:EventRuleEventSubscription$aws:lambda/permission:Permission::tatari-reports-transfer-cron
urn:pulumi:production::tatari::snowflake:index/fileFormat:FileFormat::snowflake-tatari-file-format

Found no pending operations associated with production

Backend
Name dominant-dobermann
URL s3://***
User ixti
Organizations

Dependencies:
NAME VERSION
@pulumi/aws-native 0.45.0
@pulumi/aws 5.26.0
@pulumi/pulumi 3.51.0
@pulumi/snowflake 0.15.0
@types/node 18.11.18

Pulumi locates its logs in /tmp by default

Additional context

No response

Contributing

Vote on this issue by adding a 👍 reaction.
To contribute a fix for this issue, leave a comment (and link to your pull request, if you've opened one already).

The text was updated successfully, but these errors were encountered:

guineveresaenger · 2023-01-14T01:13:24Z

Hi @ixti - thank you for filing this bug.

I have successfully duplicated this behavior. We will take a look when we can.

guineveresaenger · 2023-01-30T19:19:52Z

Hi @ixti - I have looked into this and it seems to be a manifestation of this upstream bug, as well as this one.

It turns out that when the fileformat is created, it is created with default values for a set of optional fields - using your code as an example, the file format full object looks like this after creation:

Outputs:
  + fileformat: {
      + allowDuplicate            : false
      + binaryAsText              : false
      + binaryFormat              : "HEX"
      + compression               : "GZIP"
      + database                  : "DEBUG_DB"
      + dateFormat                : "AUTO"
      + disableAutoConvert        : false
      + disableSnowflakeData      : false
      + emptyFieldAsNull          : true
      + enableOctal               : false
      + encoding                  : "UTF8"
      + errorOnColumnCountMismatch: false
      + escape                    : "NONE"
      + escapeUnenclosedField     : "\\"
      + fieldDelimiter            : ","
      + fieldOptionallyEnclosedBy : "NONE"
      + formatType                : "CSV"
      + id                        : "DEBUG_DB|schema-7634151|my-csv"
      + ignoreUtf8Errors          : false
      + name                      : "my-csv"
      + preserveSpace             : false
      + recordDelimiter           : "\n"
      + replaceInvalidCharacters  : false
      + schema                    : "schema"
      + skipBlankLines            : true
      + skipByteOrderMark         : false
      + skipHeader                : 1
      + stripNullValues           : false
      + stripOuterArray           : false
      + stripOuterElement         : false
      + timeFormat                : "AUTO"
      + timestampFormat           : "AUTO"
      + trimSpace                 : true
      + urn                       : "urn:pulumi:dev::snowflake-174::snowflake:index/fileFormat:FileFormat::my-csv"
    }

and then on the second pulumi up, these defaults get deleted, causing the values to no longer be in accepted format for Snowflake.

...The really funny part here is that a third update to the stack with no code changes succeeds as you would expect.

Workaround

The good news is there's a workaround - if you set the following "optional" fields to the expected default, your stack updates will always detect the expected value and avoid this issue.

   recordDelimiter: "\n",
    fieldDelimiter: ",",
    binaryFormat: "HEX",
    dateFormat: "AUTO",
    timestampFormat: "AUTO",
    timeFormat: "AUTO",
    encoding: "UTF8",
    escape: "NONE",
    escapeUnenclosedField: "\\",
    fieldOptionallyEnclosedBy: "NONE",

ixti added kind/bug Some behavior is incorrect or out of spec needs-triage Needs attention from the triage team labels Jan 12, 2023

guineveresaenger removed the needs-triage Needs attention from the triage team label Jan 14, 2023

AaronFriel added the p1 A bug severe enough to be the next item assigned to an engineer label Jan 23, 2023

AaronFriel assigned guineveresaenger Jan 23, 2023

guineveresaenger added awaiting-upstream The issue cannot be resolved without action in another repository (may be owned by Pulumi). and removed p1 A bug severe enough to be the next item assigned to an engineer labels Jan 30, 2023

AaronFriel unassigned guineveresaenger Feb 13, 2023

AaronFriel assigned aq17 Mar 13, 2023

mjeffryes added the blocked The issue cannot be resolved without 3rd party action. label Sep 26, 2024

mjeffryes unassigned aq17 Sep 26, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

FileFormat resource always shows changes and fails on update #174

FileFormat resource always shows changes and fails on update #174

ixti commented Jan 12, 2023

guineveresaenger commented Jan 14, 2023

guineveresaenger commented Jan 30, 2023

FileFormat resource always shows changes and fails on update #174

FileFormat resource always shows changes and fails on update #174

Comments

ixti commented Jan 12, 2023

What happened?

Steps to reproduce

Create index.ts

Run deploy twice

Expected Behavior

Actual Behavior

Output of pulumi about

Additional context

Contributing

guineveresaenger commented Jan 14, 2023

guineveresaenger commented Jan 30, 2023

Workaround

Output of `pulumi about`