Tables not showing on Metabase when doing Snowflake Sync

tiaradwptr · August 31, 2022, 11:21am

Hi, my Metabase has been working well with connection to Snowflake, but just few weeks ago not sure most of the table is missing from Metabase.

I can query through SQL Query but is not showing on the browse feature or the Pick your starting data.

Went looking at Logs have this line I'm not sure if this is what causes it:

WARN metabase.sync.util Error syncing _metabase_metadata table for snowflake Database 3 'Snowflake-Fivetran'
clojure.lang.ExceptionInfo: Output of db-metadata does not match schema: 
	   {:tables #{{:name (not ("Non-blank string" ""))}}}

Here's a diagnostic info:

{
  "browser-info": {
    "language": "en-US",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.0.0 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.15+10",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.15",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.15+10",
    "os.name": "Linux",
    "os.version": "5.4.0-90-generic",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "snowflake",
      "h2"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "12.11"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.3.2"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2022-06-27",
      "tag": "v0.43.4",
      "branch": "release-x.43.x",
      "hash": "61cc28e"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

flamber · August 31, 2022, 11:46am

Hi @tiaradwptr
If you don't use _metabase_metadata and don't have such table in your database, then ignore that, since it's unrelated to your problem.
But if you are using that table, then the syntax format of it is wrong. Correct the syntax or remove the table.

tiaradwptr · August 31, 2022, 12:09pm

Can you provide more detail of the syntax format of it is wrong? and how do I correct it?

Also just highlighting my issues are my tables are missing, there are only first 15 (apparently all started with the alphabet A) on them being showed on the Browse Data section while there should be almost a hundred of them. Almost seems like the sync stopped in the middle. Can you help to advise what has probably gone wrong?

I'm currently sync-ing a new database connection but there are some questions dependent to the old database connection so I would prefer if I can keep the old one and finding out what's wrong

flamber · August 31, 2022, 12:15pm

@tiaradwptr
Have you created a table called _metabase_metadata in your Snowflake database?
If yes, then delete it.
If no, then the log you have provided does not provide full context about what is going on. Post the full sync log.

tiaradwptr · September 1, 2022, 5:14pm

No, I did not create that table.

Here's a more complete snapshot of a more recent sync.

I truncated the line of the Exception Info as it has too long of a line. The table and schema I masked with Xs and Ys listed in that message seems to list all the table that is not being synced to Metabase.

While the one I masked with As and Bs has been brought correctly to Metabase.

[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T22:51:00+07:00 INFO metabase.task.sync-databases Starting sync task for Database 3.
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T22:51:00+07:00 INFO metabase.sync.util STARTING: Sync metadata for snowflake Database 3 'Snowflake'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T22:51:00+07:00 INFO metabase.sync.util STARTING: step 'sync-timezone' for snowflake Database 3 'Snowflake'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T22:51:02+07:00 INFO metabase.sync.util FINISHED: step 'sync-timezone' for snowflake Database 3 'Snowflake' (2.5 s)
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T22:51:02+07:00 INFO metabase.sync.util STARTING: step 'sync-tables' for snowflake Database 3 'Snowflake'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T22:52:18+07:00 INFO metabase.sync.util FINISHED: step 'sync-tables' for snowflake Database 3 'Snowflake' (1.3 mins)
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T22:52:18+07:00 INFO metabase.sync.util STARTING: step 'sync-fields' for snowflake Database 3 'Snowflake'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:00:00+07:00 INFO metabase.task.send-pulses Sending scheduled pulses...
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:12:22+07:00 INFO metabase.sync.util FINISHED: step 'sync-fields' for snowflake Database 3 'Snowflake' (20.1 mins)
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:12:22+07:00 INFO metabase.sync.util STARTING: step 'sync-fks' for snowflake Database 3 'Snowflake'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:12:42+07:00 INFO metabase.sync.util FINISHED: step 'sync-fks' for snowflake Database 3 'Snowflake' (20.6 s)
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:12:42+07:00 INFO metabase.sync.util STARTING: step 'sync-metabase-metadata' for snowflake Database 3 'Snowflake'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 WARN metabase.sync.util Error syncing _metabase_metadata table for snowflake Database 3 'Snowflake'
clojure.lang.ExceptionInfo: Output of db-metadata does not match schema: 
	   {:tables #{{:name (not ("Non-blank string" ""))}}}  
 {:type :schema.core/error, :schema {
    :tables #{{
        :name (constrained Str "Non-blank string"), 
        :schema (maybe (constrained Str "Non-blank string")), 
        #schema.core.OptionalKey{:k :description} (maybe (constrained Str "Non-blank string"))}}, 
        #schema.core.OptionalKey{:k :version} (maybe (constrained Str "Non-blank string"))}, 
        :value {:tables #{
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil}
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil}
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil}
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil}
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
            {:name "XXXXXX", :schema "ZZZZZZ", :description nil} 
              .... [TRUNCATED]
	at metabase.sync.fetch_metadata$fn__64168$db_metadata__64173.invoke(fetch_metadata.clj:11)
	at metabase.sync.sync_metadata.metabase_metadata$fn__65145$sync_metabase_metadata_BANG___65150$fn__65151$fn__65152.invoke(metabase_metadata.clj:100)
	at metabase.sync.util$do_with_error_handling.invokeStatic(util.clj:158)
	at metabase.sync.util$do_with_error_handling.invoke(util.clj:151)
	at metabase.sync.sync_metadata.metabase_metadata$fn__65145$sync_metabase_metadata_BANG___65150$fn__65151.invoke(metabase_metadata.clj:93)
	at metabase.sync.sync_metadata.metabase_metadata$fn__65145$sync_metabase_metadata_BANG___65150.invoke(metabase_metadata.clj:88)
	at clojure.lang.AFn.applyToHelper(AFn.java:154)
	at clojure.lang.AFn.applyTo(AFn.java:144)
	at clojure.core$apply.invokeStatic(core.clj:669)
	at clojure.core$apply.invoke(core.clj:662)
	at metabase.sync.util$fn__41548$run_step_with_metadata__41553$fn__41557$fn__41559.doInvoke(util.clj:388)
	at clojure.lang.RestFn.invoke(RestFn.java:397)
	at metabase.sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:100)
	at metabase.sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:94)
	at metabase.sync.util$with_start_and_finish_debug_logging.invokeStatic(util.clj:117)
	at metabase.sync.util$with_start_and_finish_debug_logging.invoke(util.clj:114)
	at metabase.sync.util$fn__41548$run_step_with_metadata__41553$fn__41557.invoke(util.clj:383)
	at metabase.sync.util$fn__41548$run_step_with_metadata__41553.invoke(util.clj:378)
	at metabase.sync.util$fn__41764$run_sync_operation__41769$fn__41770$fn__41778.invoke(util.clj:489)
	at metabase.sync.util$fn__41764$run_sync_operation__41769$fn__41770.invoke(util.clj:487)
	at metabase.sync.util$fn__41764$run_sync_operation__41769.invoke(util.clj:481)
	at metabase.sync.sync_metadata$fn__66501$sync_db_metadata_BANG___66506$fn__66507$fn__66508.invoke(sync_metadata.clj:50)
	at metabase.sync.util$do_with_error_handling.invokeStatic(util.clj:158)
	at metabase.sync.util$do_with_error_handling.invoke(util.clj:151)
	at clojure.core$partial$fn__5859.invoke(core.clj:2634)
	at metabase.driver$fn__32609.invokeStatic(driver.clj:592)
	at metabase.driver$fn__32609.invoke(driver.clj:592)
	at clojure.lang.MultiFn.invoke(MultiFn.java:239)
	at metabase.sync.util$sync_in_context$fn__41457.invoke(util.clj:136)
	at metabase.sync.util$with_db_logging_disabled$fn__41454.invoke(util.clj:127)
	at metabase.sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:100)
	at metabase.sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:94)
	at metabase.sync.util$with_start_and_finish_logging$fn__41443.invoke(util.clj:112)
	at metabase.sync.util$with_sync_events$fn__41438.invoke(util.clj:86)
	at metabase.sync.util$with_duplicate_ops_prevented$fn__41429.invoke(util.clj:65)
	at metabase.sync.util$do_sync_operation.invokeStatic(util.clj:179)
	at metabase.sync.util$do_sync_operation.invoke(util.clj:176)
	at metabase.sync.sync_metadata$fn__66501$sync_db_metadata_BANG___66506$fn__66507.invoke(sync_metadata.clj:49)
	at metabase.sync.sync_metadata$fn__66501$sync_db_metadata_BANG___66506.invoke(sync_metadata.clj:46)
	at metabase.task.sync_databases$sync_and_analyze_database_BANG_.invokeStatic(sync_databases.clj:68)
	at metabase.task.sync_databases$sync_and_analyze_database_BANG_.invoke(sync_databases.clj:59)
	at metabase.task.sync_databases.SyncAndAnalyzeDatabase.execute(sync_databases.clj:76)
	at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.util FINISHED: step 'sync-metabase-metadata' for snowflake Database 3 'Snowflake' (1.5 mins)
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 WARN metabase.sync.util Error in sync step Sync metadata for snowflake Database 3 'Snowflake'
java.lang.ClassCastException
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.util FINISHED: Sync metadata for snowflake Database 3 'Snowflake' (23.2 mins)
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.util STARTING: Analyze data for snowflake Database 3 'Snowflake'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.util STARTING: step 'fingerprint-fields' for snowflake Database 3 'Snowflake'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.analyze fingerprint-fields Analyzed [*****·············································] 😢   10% Table 785 'AAAA.BBBB'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.analyze fingerprint-fields Analyzed [**********········································] 😞   20% Table 962 'AAAA.BBBB'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.analyze fingerprint-fields Analyzed [***************···································] 😕   30% Table 1,691 'AAAA.BBBB'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.util FINISHED: step 'fingerprint-fields' for snowflake Database 3 'Snowflake' (215.0 ms)
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.util STARTING: step 'classify-fields' for snowflake Database 3 'Snowflake'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.analyze classify-fields Analyzed [********************······························] 😐   40% Table 794 'AAAA.BBBB'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.analyze classify-fields Analyzed [*************************·························] 😬   50% Table 974 'AAAA.BBBB'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.analyze classify-fields Analyzed [******************************····················] 😌   60% Table 966 'AAAA.BBBB'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.util FINISHED: step 'classify-fields' for snowflake Database 3 'Snowflake' (101.1 ms)
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.util STARTING: step 'classify-tables' for snowflake Database 3 'Snowflake'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.analyze classify-tables Analyzed [***********************************···············] 😏   71% Table 743 'AAAA.BBBB'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.analyze classify-tables Analyzed [****************************************··········] 😊   81% Table 845 'AAAA.BBBB'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.analyze classify-tables Analyzed [*********************************************·····] 😍   91% Table 992 'AAAA.BBBB'
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.util FINISHED: step 'classify-tables' for snowflake Database 3 'Snowflake' (121.2 ms)
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:14:12+07:00 INFO metabase.sync.util FINISHED: Analyze data for snowflake Database 3 'Snowflake' (451.4 ms)
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:23:18+07:00 INFO org.quartz.impl.jdbcjobstore.JobStoreTX Handling 1 trigger(s) that missed their scheduled fire-time.
[b6ef973b-d23d-475c-9a72-4f582c39c89c] 2022-09-01T23:41:00+07:00 INFO metabase.task.refresh-slack-channel-user-cache Slack is not configured, not refreshing slack user/channel cache.

flamber · September 2, 2022, 9:21am

@tiaradwptr Something on your Snowflake is returning a response to Metabase. Try running this:

SELECT * FROM "<REPLACE WITH YOU DATABASE>"."INFORMATION_SCHEMA"."TABLES" WHERE "TABLE_NAME" = '_metabase_metadata'

How many schemas, tables and columns do you have in that Snowflake database?

tiaradwptr · September 7, 2022, 2:26pm

Hello, sorry for the late response. I still couldn't troubleshoot the main cause but here's what happened:

My old connection is called Snowflake-Fivetran
I created a new connection called Snowflake to the same database, it updated once with the first few tables but stop sending more
The new connection was syncing for more than a day without bringing any new table so I decided to delete the new Snowflake connection
Somehow on the Database menu on admin, Snowflake is still there and Snowflake-Fivetran is gone
All my questions has switched from using Snowflake-Fivetran to Snowflake
The problem persisted on the new Snowflake connection, but brings in different table than Snowflake-Fivetran (The log I attached is the one from the new Snowflake connection)

I've run the SQL you provide on Snowflake but it returns 0 rows.

flamber · September 7, 2022, 3:34pm

@tiaradwptr I don't know what has happened to your system, but I have never seen this behavior.
Also, if you delete a database connection in Admin > Databases, then it will delete all questions connected to it. And Metabase cannot automatically remove or rename databases, so not sure what has happened there.

tiaradwptr · September 9, 2022, 2:55pm

Yeah, the 5th step I mentioned was done automatically, it's like Snowflake-Fivetran was renamed to Snowflake as all the questions are still intact.

Is there anyway I can provide more detail to find out what's wrong for this one? A pointer of some sort of where I can take a look. I am still unable to bring all the schemas from Snowflake to Metabase. I have pressed the Sync Database Schema and Re-scan field values button on Admin several times now but still no changes.

flamber · September 9, 2022, 3:48pm

@tiaradwptr Okay, so you renamed the database? It is impossible for Metabase to do this by itself.

The Snowflake driver was upgraded in 0.44.0, so try the latest release: https://github.com/metabase/metabase/releases/latest

It's only "Sync database schema now", which would do anything. Nothing will happen with "Re-scan field values now".
Read this: https://www.metabase.com/docs/latest/troubleshooting-guide/sync-fingerprint-scan

But I've never seen a problem like this, so I cannot tell you what the problem is.

tiaradwptr · September 12, 2022, 10:52am

Okay, got it... I was expecting it to retrieve all schemas and tables when I do "Sync database schema now" but it didn't so I thought if this is known issue let me try to update the version and see what happened

Thank you!

flamber · September 12, 2022, 11:01am

@tiaradwptr It will retrieve all schemas and tables, which you have defined Metabase should sync.

If it still doesn't work, then setup a new Metabase and try there. If that doesn't work either, then I don't know what the problem is and you'll need to provide a full DDL, so the issue can be reproduced otherwise it will be impossible to fix.