SQL query in Metabase different result than in Snowflake

1

Hi,

I'm running into a rather urgent bug - I'm running the exact same query in Metabase and then in Snowflake and am getting different results. Can you help me figure out why? We're presenting these numbers this week and need this to be accurate.

In Metabase:

image
image2864×1246 149 KB

In Snowflake:

image
image2260×1274 227 KB

Query:
SELECT count(*) AS "count" FROM "ANALYTICS_DATA"."DBT"."F_RESERVATIONS" WHERE ("ANALYTICS_DATA"."DBT"."F_RESERVATIONS"."IS_EPHEMERAL_EMAIL" = FALSE AND "ANALYTICS_DATA"."DBT"."F_RESERVATIONS"."IS_REFUNDED" = FALSE AND "ANALYTICS_DATA"."DBT"."F_RESERVATIONS"."RESERVATION_CREATED_AT_ET" >= timestamp '2021-01-01 00:00 -05:00' AND "ANALYTICS_DATA"."DBT"."F_RESERVATIONS"."RESERVATION_CREATED_AT_ET" < timestamp '2021-04-01 00:00 -04:00')

Thanks,
Meera

2

Hi @mvtemple
Post "Diagnostic Info" from Admin > Troubleshooting.
Metabase sends alter session set timezone <report-timezone> before your query, so that's likely what is causing the problem.
You might be seeing this issue:
https://github.com/metabase/metabase/issues/16381 - upvote by clicking :+1: on the first post

3

Hi @flamber - How can I change my Metabase or Snowflake settings to prevent this from happening? This is sort of urgent on my end, so anything you can do to help me would be great.

4

@mvtemple

  1. Post "Diagnostic Info" from Admin > Troubleshooting.
  2. Did you read the issue, where someone has commented with a workaround that worked for him?
5

Hi @flamber,

Thanks for the speedy response!

  1. { "browser-info": { "language": "en-US", "platform": "MacIntel", "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.114 Safari/537.36", "vendor": "Google Inc." }, "system-info": { "file.encoding": "UTF-8", "java.runtime.name": "OpenJDK Runtime Environment", "java.runtime.version": "11.0.11+9", "java.vendor": "AdoptOpenJDK", "java.vendor.url": "https://adoptopenjdk.net/", "java.version": "11.0.11", "java.vm.name": "OpenJDK 64-Bit Server VM", "java.vm.version": "11.0.11+9", "os.name": "Linux", "os.version": "4.14.219-164.354.amzn2.x86_64", "user.language": "en", "user.timezone": "GMT" }, "metabase-info": { "databases": [ "h2", "snowflake" ], "hosting-env": "unknown", "application-database": "postgres", "application-database-details": { "database": { "name": "PostgreSQL", "version": "11.9" }, "jdbc-driver": { "name": "PostgreSQL JDBC Driver", "version": "42.2.18" } }, "run-mode": "prod", "version": { "date": "2021-06-15", "tag": "v1.39.4", "branch": "release-x.39.x", "hash": "f538050" }, "settings": { "report-timezone": "America/New_York" } } }

  2. I did changing the account setting 'JBDC_USE_SESSION_TIMEZONE' to false, but that didn't prevent the Metabase user from still altering the timezone.

6

@mvtemple So what's the timezone of Snowflake data? Have you tried to change the Report Timezone in Metabase to "GMT"?
Metabase will still send the session timezone, since Metabase doesn't know that configuration, which is applied directly on Snowflake.

7

@flamber Ok, I think Metabase is actually doing what I want and I was getting the wrong result in Snowflake!

I was comparing two values: my column, and a date. My column is in ET, so I assumed Snowflake would read the date as ET as well, but it was reading it as UTC because that's what my account is set to.

So, yes, I think changing the Metabase reporting time would align my answers with Snowflake, but now I'm thinking I'll change my Snowflake reporting time to ET :slight_smile:

1 Like