1

I have an Alteryx workflow which selects everything from a snowflake table, adds a RecordId column to the table, then uploads the result back to snowflake with a new name.

The problem is: Snowflake does not recognize the RecordID column.

If i use a SELECT * FROM NUMBERED_TABLE it successfully reads the entire table, displaying every field, including the RecordID.

But if I try to select with SELECT RECORDID FROM NUMBERED_TABLE I get the message SQL compilation error: error line 1 at position 7 invalid identifier 'RECORDID'

From the UI, Snowflake says the column is indeed called RECORDID.

Trying to rename the column doesn't work either. It simply says the RECORDID column doesn't exist.

I already tried recreating the table by creating a new one based on itself, it's still not recognizing it.

ThePorcius
  • 123
  • 8
  • It probably won't matter, but can you try `SELECT RecordId` instead of `SELECT RECORDID` ? Just want to rule out any weird quirks arising from Alteryx being involved in column creation – Radagast Feb 12 '21 at 19:01

1 Answers1

1

The query is SELECT RECORDID FROM NUMBERED_TABLE but you note a couple of times the name is RecordID.

Snowflake is case sensitive for object names so RECORDID and RecordID can be two different columns in the same table. Snowflake implicitly uppercases object names, so if you run query SELECT RecordID FROM NUMBERED_TABLE Snowflake will implicitly convert that to SELECT RECORDID FROM NUMBERED_TABLE. If the column is in fact RecordID, it will report that it can't find RECORDID. If you have a mixed-case column name, the way to prevent the implicit uppercasing is to wrap the object name in double quotes:

select "RecordID" from NUMBERED_TABLE;

Most ETL partners will wrap object names in quotes for Snowflake. I've seen Alteryx do that for Snowflake, so that's probably what's happening here if it's RecordID in the source database.

Greg Pavlik
  • 10,089
  • 2
  • 12
  • 29