Loading database schema from Snowflake fails in some cases

[brettwatkins-Built]

Describe the bug
After successfully authenticating to Snowflake and running the initial information schema query, Cube returns 'Undefined' for our tables in the Data Model section. I can see initial information schema query successful runs, but I never get the results back into Cube.

To Reproduce
Steps to reproduce the behavior:

1. Connect to a Snowflake instance
2. Wait for initial response
3. See error in Data Model

Expected behavior
I would expect to see our Snowflake tables

Screenshots

Version:
v0.34.23

[zbakkernomi]

having the same issue

snowflake version: 8.21.1
cube version: v0.35.45

[christianallred-nomi]

Do we have a solution or an update on this? its been almost 10 months now and no resposne?

[igorlukanin]

Hi @christianallred-nomi 👋 Are you experiencing the same issue? Which Snowflake and Cube versions do you use?

[christianallred-nomi]

@igorlukanin yes i ran into the same issue.

I was using the off the shelf playground,
assuming every tag of cubjs/cube gets pushed to latest it would have been
cube: cubejs/cube:latest (0.35.70) <- assuming this.
snowflake: 8.31.1

from the cube container

{
  "name": "@cubejs-backend/docker",
  "version": "0.35.66",
  "description": "Cube.js In Docker (virtual package)",
  "author": "Cube Dev, Inc.",
  "license": "Apache-2.0",
  "private": true,
  "engines": {
    "node": "^14.0.0 || ^16.0.0 || >=17.0.0"
  },
  "dependencies": {
    "@cubejs-backend/athena-driver": "^0.35.66",
    "@cubejs-backend/bigquery-driver": "^0.35.66",
    "@cubejs-backend/clickhouse-driver": "^0.35.66",
    "@cubejs-backend/crate-driver": "^0.35.66",
    "@cubejs-backend/databricks-jdbc-driver": "^0.35.66",
    "@cubejs-backend/dbt-schema-extension": "^0.35.66",
    "@cubejs-backend/dremio-driver": "^0.35.66",
    "@cubejs-backend/druid-driver": "^0.35.66",
    "@cubejs-backend/duckdb-driver": "^0.35.66",
    "@cubejs-backend/elasticsearch-driver": "^0.35.64",
    "@cubejs-backend/firebolt-driver": "^0.35.66",
    "@cubejs-backend/hive-driver": "^0.35.63",
    "@cubejs-backend/ksql-driver": "^0.35.66",
    "@cubejs-backend/materialize-driver": "^0.35.66",
    "@cubejs-backend/mongobi-driver": "^0.35.64",
    "@cubejs-backend/mssql-driver": "^0.35.63",
    "@cubejs-backend/mysql-driver": "^0.35.66",
    "@cubejs-backend/oracle-driver": "^0.35.63",
    "@cubejs-backend/postgres-driver": "^0.35.66",
    "@cubejs-backend/prestodb-driver": "^0.35.64",
    "@cubejs-backend/questdb-driver": "^0.35.66",
    "@cubejs-backend/redshift-driver": "^0.35.66",
    "@cubejs-backend/server": "^0.35.66",
    "@cubejs-backend/snowflake-driver": "^0.35.63",
    "@cubejs-backend/sqlite-driver": "^0.35.63",
    "@cubejs-backend/trino-driver": "^0.35.66",
    "cubejs-cli": "^0.35.66",
    "typescript": "~5.2.2"
  },
  "resolutions": {
    "colors": "1.4.0"
  }
}

[christianallred-nomi]

it looks like you have some type of mapping problem. it looks to be getting data from something but the processing appears to fail? im going to dig in more in a bit

[christianallred-nomi]

i found it.

this code:

cube/packages/cubejs-snowflake-driver/src/SnowflakeDriver.ts

Lines 820 to 829 in 861f13e

	SELECT COLUMNS.COLUMN_NAME as "column_name",
	COLUMNS.TABLE_NAME as "table_name",
	COLUMNS.TABLE_SCHEMA as "table_schema",
	CASE WHEN COLUMNS.NUMERIC_SCALE = 0 AND COLUMNS.DATA_TYPE = 'NUMBER' THEN 'int' ELSE COLUMNS.DATA_TYPE END as "data_type"
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE COLUMNS.TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA')
	`;
	}
	public async release(): Promise<void> {

doesn't do what you're hoping, those as "column_name" and such dont actually lowercase the response objects. snowflake headers are coerced the capitols.

this is the response rows that come out of the this.query() in baseDriver

{
  COLUMN_NAME: 'OFFICE_CBSA',
  TABLE_NAME: 'RX_CLAIM_DENORMALIZED',
  TABLE_SCHEMA: 'ARTEMIS_CHILD',
  DATA_TYPE: 'TEXT'
}

so when you got to reduce them in informationColumnsSchemaReducer

and use these let schema = (result[i.table_schema] || {}); it fails

[christianallred-nomi]

This code change confirmed it. Not sure if this is an option of the snowflake api or not, but its this code that is failing and returning the undefined's

I've also confirmed this works in the playground but can't share that screenshot because i was testing on our dataset.

[christianallred-nomi]

i think this was a change that snowflake made recently(ish)

https://docs.snowflake.com/en/release-notes/bcr-bundles/2023_07/bcr-881

it looks like snowflake wants you to set a value to control this in your instance. As i dont think the fix for this should be imposing snowflake settings. probably the right route is to override the informationColumnsSchemaReducer inside of the snowflake driver?

looks like the sortBykeys is a private method inside of the base driver though. not sure you'd want to extract that to a util or just copy paste, given two different packages, probably just copy paste.

this does introduce a coupled dependency though.

could update the base driver func to have an option to use captial identifiers, but that feels gross as well.

I'll let yall decide on your patch, but theres your error.

[christianallred-nomi]

mgiht be able to set this CLIENT_RESULT_COLUMN_CASE_INSENSITIVE on the session possibly.