Is there any means of querying Snowflake for all the roles (and/or users) where a specific privilege has been granted? I know how to show grants to <role> but I’m not so sure of how to find the roles that have a grant. This is not something that needs to happen regularly–i’m just looki…
Tag: snowflake-cloud-data-platform
How to select a column from the output of ‘SHOW COLUMNS’ sql
I am using the sql command SHOW COLUMNS in this way It outputs multiple columns, specifically one called ‘COLUMN_NAME’ that I would like to select. I tried doing which gives an error, is there another way I can just show one of the columns of the output? Is this because the output is not a table s…
Why is this query not able to execute?
The query goes on without executing, any ideas on how I can help it resolve? Using the Economy Data Atlas dataset from Knoema within Snowflake. Answer This might be a case where temporary tables will help your what I assume is a massive table be pruned (aka one of the reasons to avoid complex CTE’s) I w…
How to get the 2nd record for a customer purchase?
I’m working on a customers database and I want to get all data for their second purchase (for all of our customer weather they have 2 or more purchases). For example: I want to display the second order which is: I’m facing some difficulties in finding the right logic, any idea how I can achieve my…
Pythonic way to optimize SQL VIEW count to extract information schema metadata from Snowflake
I have 12 VIEW tables in Snowflake and I would like to extract TABLE_NAME,CREATED,LAST_ALTERED from Snowflakes INFORMATION Schema for View tables, and also want to get row count for each 12 VIEW tables, along with metadata for Base table mentioned in below code.I was wondering if there is way get row count us…
Save the output of a procedure in snowflake in a variable
I have following procedure: Now I want to do two things: First I would like to save the output of the procedure in the variable todays_amount So I tried this: But this does not work. And second: Instead of where TARGET_KEY = ’20’ i would like to do where TARGET_KEY = targetkey_variable But this do…
How to convert 1900-01-01 to null in snowflake
My SQL query output looks like this. ID UPC 1 2021-05-01 2 1900-01-01 UPC is a date column. How do I convert 1900-01-01 to null? I tried following instructions from this, but I am getting SQL compilation error: error line 1 at position 22 invalid identifier ‘DATE’ Answer Using NULLIF: Returns NULL…
How to structure DBT tables with cyclical dependencies
I have one table containing my members. customer_id name age 1 John 74 2 Sarah 87 Everyday, I get a new table containing the current members. If a new member has joined, I want to add them. If a member has left, I want to nullify their name/id If a current member is still a member then I want to
Snowflake/SQL: create a time-series table such that every ID is visible, and if ID is null, it uses the previous value? (similar to shift)
Suppose I have the following table: Day ID Value 2022-11-05 0 A 2022-11-06 1 B 2022-11-07 0 C Now given a time window of 1 day, I want to create a time-series table that: The Day column granular unit is 1 day Each Day row displays every ID in the table (like cross-join) Moreover, if for that day, the ID
why empty values are allowed but not null values in composite primary keys
I am working on an App connecting to the Snowflake database. I stumbled upon an issue while loading CSV files. I was using the NULL_IF condition in copy command to change column value to null if any empty value is encountered during the load On investigation, I came to know that one of the columns is part of …