12,795 questions
0
votes
0
answers
38
views
How to discard changes to target folder in snowflake dbt project?
I'm working with snowflake dbt project using azure devops repo. In the repo interface in the snowflake dbt project (activated with changes button) there are only 2 actions possible: pull, and push. ...
- 609
1
vote
1
answer
54
views
Find history access of intermediate views in Snowflake
I'm using the access_history view to find out what tables and views in snowflake where last touched.
problem is that if there is a view calling a view calling a table the middle vies would not appear ...
- 3,761
Best practices
0
votes
2
replies
32
views
creating a table which excluding holidays like weekend,us holidays,bank holidays in snowflake
Is there any way to create a calendar table which stores only business day of every month excluding weekend /us holidays/bank holidays.
My requirement is to take a snapshot should be taken on 2nd ...
- 176
0
votes
0
answers
40
views
Using snowflakes externalbrowser authentification using VS Code remote tunnels
Executing this code while developing using a remote tunnel (Azure VM), the URL (token) seems not to redirect the token back to the snowflake client
Session.builder.configs(
{
"...
- 27.3k
-1
votes
1
answer
53
views
Improving performance on left join condition with CASE in Snowflake [closed]
I have a view which extracts data from two other views, joining on ID and date columns.
WITH CTE A AS
(
SELECT *
FROM TABLE A -- A UNION OF SEVERAL TABLES
),
CTE B AS
(SELECT *
FROM TABLE B
WHERE ...
- 11
1
vote
1
answer
151
views
SQL Query Resulting in Multiple Rows
The database I'm working in is Snowflake. I also have to work through an interface and can't edit the SQL query directly. I'm trying to add a JOIN to a FROM clause that is connecting two schemas with ...
- 31
Advice
0
votes
1
replies
47
views
SAS Snowflake ODBC authentication using Key pair
We are planning to switch from password-based authentication to key-pair authentication for SAS to Snowflake connection. Our current requirement is:
1. Overnight batch/service account: use key-pair ...
- 21
0
votes
0
answers
91
views
Calling stored procedure through task results in empty table if procedure contains cross-database join
I have a stored procedure in Snowflake that joins data from two different databases, in the following form:
CREATE OR REPLACE PROCEDURE database_1.schema_1.sp_test()
RETURNS varchar
AS
BEGIN
...
- 344
Tooling
1
vote
0
replies
62
views
masking secrets airflow 2.4.3
I’m using Apache Airflow 2.4.3 and trying to securely store a Snowflake connection with a private key inside the connection’s extras JSON field.
I want to mask the sensitive private_key_content field ...
2
votes
4
answers
132
views
Grouping by and making flag based on patterns
I have a table p that looks like this:
ID
Col1
AAA
kddd
AAA
13bd
AAA
14cd
AAA
15cd
BBB
15cd
BBB
23fd
BBB
4rre
BBB
tr3e
CCC
kddd
CCC
12ed
DDD
rrr4
DDD
rtt4
DDD
rrt4
I have three lists of patterns that ...
- 2,379
1
vote
1
answer
68
views
Why don't snowflake table-valued functions perform type coercion?
It seems that snowflake treats function parameters differently in scalar function versus table-valued function. i.e it performs type coercion for scalar functions, but does not for table-valued ...
- 4,801
0
votes
2
answers
87
views
How to capture delta records in Snowflake when a table is dropped and re-created every day?
I have a pipeline that drops and re-creates several Snowflake tables every day (effectively a full refresh using CREATE OR REPLACE TABLE). I want to capture daily deltas (inserts/updates/deletes) for ...
- 1
0
votes
1
answer
102
views
Snowflake: SQL equivalent of INSERT INTO table BY NAME
The goal is to simulate SQL INSERT BY NAME behavior in Snowflake
Using the BY NAME modifier, the names of the column list of the SELECT statement are matched against the column names of the table to ...
- 181k
0
votes
1
answer
99
views
Snowflake Iceberg Immutable data getting rewritten (which violates snapshot level isolation guarantee)
There appears to be a bug in Snowflakes implementation Iceberg Tables which is evident if you try to capture change data. This seems to be a result of a more fundamental problem where a single parquet ...
1
vote
1
answer
122
views
Snowflake: SQL Keyword DEFINE
While tinkering with Snowflake Workspace editor I found an entry - keyword DEFINE:
At the time of writing docs do not provide any clues. List of available commands at https://docs.snowflake.com/en/...
- 181k
0
votes
1
answer
121
views
How to give Snowflake NativeApp Permission to use SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY?
I am trying to build a snowflake nativeapp, and this is my first time trying to build one and my main objective is to get Query ID's Profile and Query/SQL Text of a Query ID that my app will get as an ...
user31671571
0
votes
1
answer
85
views
Get list of files from external snowflake stage based on where condition on timestamp column
I am trying to get the files that arrived on the snowflake external stage after that have MODIFIED_DATE > MAX(BATCH_END_TIME).
I came up with this query:
LIST @my_internal_stage;
SELECT count(...
- 59
1
vote
1
answer
86
views
dbt post_hook executes before materialization if done in macro
If DDL stated directly in the post_hook it executes after the materialization as expected, but if it is moved to a macro it executes before the materialization.
This can be illustrated by this example:...
- 699
0
votes
1
answer
125
views
Import timestamp from Rstudio to Snowflake changes timezone
I'm exporting some data from Snowflake to Rstudio and do some data processing. After that I write the data back to Snowflake. The problem is that somehow the timestamp initiale from Snowflake is ...
- 42.8k
0
votes
1
answer
107
views
Connection state pristine
I am using node 18.11 and snowflake-sdk nodejs driver v 1.15.0. Getting the below error:
Snowflake Error SF: Error [ClientError]: Unable to perform operation because a connection was never ...
- 726
1
vote
1
answer
109
views
How to EXECUTE a Triggered Task
I have not been able to EXECUTE a Snowflake Triggered Task and I would like to better understand why, and where the behavior is documented. To demonstrate my problem:
-- SETUP
USE SCHEMA EXAMPLE_DB....
- 3,026
0
votes
0
answers
75
views
Can we get access history for an Inference Service in Snowflake?
I’m currently exploring Inference Services in Snowflake and wanted to check if there’s an equivalent to the Event History column in Databricks.
So far, the closest I’ve found in Snowflake are service ...
- 1
1
vote
1
answer
107
views
Why is "SHOW WAREHOUSE" not returning anything when called with a SQL block?
I am trying to grasp the output of SHOW WAREHOUSES into a table.
If I do it within a block, it seems that SHOW WAREHOUSES returns nothing
CREATE OR REPLACE TEMPORARY TABLE APPSUPPORT.PROBLEMS2(...
- 3,795
0
votes
1
answer
130
views
Snowflake - Python Connector - MFA - MacBook Finger Scan
I have a personal Snowflake account on my MacBook Air (Tahoe 26 M4). Snowflake is requiring MFA. I added a passkey with a "nickname", which I'm not entirely sure what that means. When I ...
1
vote
0
answers
80
views
Get total connection sold by same sale person to same customer
I want to add a new column to the sales table that shows, for each row, how many connections the same salesperson sold to the same customer (by NIC).
SALE TABLE
Sale_Date Sale_Person Connection
13/...
- 191
0
votes
0
answers
39
views
Handling Schema Drift in Salesforce → Snowflake with Dynamic Mapping & CDC
We are ingesting data from Salesforce into Snowflake using Informatica. One of the challenges we’re facing is handling schema drift when Salesforce objects add or remove fields.
Our Requirements:
Need ...
1
vote
1
answer
84
views
Writing and running a stored procedure asynchronously *and* getting a return value
Suppose I have two stored procedures, foo and bar, both written using Snowflake Scripting language.
Inside foo, I want to call bar asynchronously and get a return value from bar. Is there currently a ...
- 1,729
5
votes
2
answers
107
views
Measure prescription adherence (date picking up a prescription <> date starting the prescription)
I have data that looks like this (in snowflake/SQL) (we will call the table RX):
Person ID
Rx Filled Date
Days Supply
1
January 1, 2025
10
1
January 8, 2025
10
1
January 15, 2025
10
1
January 30, 2025
...
1
vote
2
answers
61
views
Procedure to Trim all the columns in Snowflake schema tables
I want to trim all the columns in all the tables in Snowflake schema which has varchar datatype.
Below is the procedure i wrote which execute successfully but when i try to run the procedure it gives ...
- 4,021
-1
votes
2
answers
230
views
PowerApps-Snowflake Connector - Bad Gateway
Good day!
I am trying to create a PowerApp-Snowflake connector.
I followed the officials steps provided by Snowflake:
https://docs.snowflake.com/en/connectors/microsoft/powerapps/configure-oauth
...
- 13
1
vote
1
answer
47
views
Permifrost in snowflake is not able to find the roles owned by AAD_PROVISIONER
I have setup permifrost to manage roles and accesses in my snowflake environment. It is using the securityadmin role to perform it's tasks. However, I am getting the following error when I run it ...
- 285
0
votes
0
answers
59
views
How to change the name of the default app name in navigation in snowflake Streamlit?
When I created the streamlit app in snowflake, the default file is named streamlit_app.py. I am unable to change it. Because of it, when I tried creating a navigation link, the name is defaulted to &...
0
votes
1
answer
81
views
Snowflake - Hash_agg comparaison issue
I have an issue when I use the function hash_agg on snowflake.
The result is different even if the data are the same in the table.
Below a data exemple
Sales id, name, price
1, Test1, 2
1, Test1, 3
1, ...
- 420
0
votes
0
answers
37
views
How to use snowflake cli in Jenkins CI pipeline?
I am trying to setup a CI pipeline to push my java application docker image to Snowflake image registry.
Since I have to use key-pair auth therefore I tried using Snowflake-cli.
I tried creating an ...
- 153
0
votes
1
answer
115
views
Running a custom Snowflake Cortex agent in SiS
Im trying to connect a custom Snowflake Cortex Agent to my Streamlit in Snowflake(SiS) application. Here is how I am currently trying to connect my custom cortex agent:
AGENT_NAME = "A.B....
- 145
0
votes
0
answers
48
views
How can I extract email attachments in Matillion and store them in a Snowflake stage?
My goal is to:
Extract email attachments (for example: images, PDF, or CSV files).
Store those attachments into a Snowflake internal stage so that they can be processed later.
I was able to set up the ...
- 11
0
votes
0
answers
133
views
Snowflake: Secret Does not exist or Operation Not authorized
I am trying to create a stored procedure in Snowflake to access SonarQube API. I have the accountadmin create a network rule, secret and external access integration for the same. When However, when I ...
0
votes
1
answer
60
views
Is it possible to load csv file from local windows machine into TABLE STAGE in Snowflake?
Is it possible to load csv file from local windows machine into TABLE STAGE in Snowflake Snowsight ?
As I have done for Internal Named Stage and Unable to do it for TABLE STAGE ( @%) in UI/Snowsight
- 135
-3
votes
1
answer
78
views
Snowflake PK Enforcement [duplicate]
I can't get Snowflake to enforce a PK on an auto-increment column. The below code should not work, but, it does:
CREATE OR REPLACE TABLE IDENTITYTEST
(
ID INT AUTOINCREMENT START 1 INCREMENT 1,
...
0
votes
1
answer
358
views
Python snowpark print()/.show() is not working
I tried to create snowpark program in snowsight.
There's already some code example showing when I add a sheet as following:
import snowflake.snowpark as snowpark
from snowflake.snowpark.functions ...
- 623
0
votes
1
answer
73
views
Snowflake TO_TIMESTAMP not working when copying from CSV
I have CSV data loaded into a stage. The first column contains a timestamp for customer survey responses. Here is what the data looks like:
If I run this command:
SELECT TO_TIMESTAMP( $1, 'MM/DD/...
- 1,369
3
votes
1
answer
167
views
Unexpected behavior of FIRST_VALUE … IGNORE NULLS with different window frames on Snowflake
While experimenting with window functions in Snowflake, I stumbled upon a rather puzzling behavior of FIRST_VALUE in combination with IGNORE NULLS when using different window frame definitions.
...
- 457
0
votes
0
answers
89
views
Trigger DBT core jobs for Snowflake using Airflow
Airflow is installed using Docker and it is running fine. Now I am trying to add dbt-core, dbt-snowflake and astronomer-cosmos python packages to the Airflow image in order to run the DBT core jobs ...
- 1,584
2
votes
1
answer
114
views
SHOW PARAMETERS All level in single query
We can check parameters of SESSION/ACCOUNT/DATABASE level individually using SHOW command in Snowflake:
SHOW PARAMETERS IN ACCOUNT; —- shows parameters defined at the user level
SHOW PARAMETERS IN ...
- 135
1
vote
2
answers
95
views
How to add days to timestamp with daylight savings time change
When adding 90 days to a timestamp that is in PST, I expected the result to be in PDT, but I'm getting a result that I think is incorrect. In my example, I start with a timestamp that is near midnight ...
- 365
0
votes
1
answer
111
views
Use bind variable directly in snowflake SQL worksheet
I wanted to try this SQL to use bind variables as input (not in a stored procedure or anonymous block), but it's throwing an error.
LET VARIABLE VARCHAR := 'RPT';
SELECT * FROM TABLE_NAME WHERE TYPE= :...
- 135
2
votes
0
answers
65
views
Snowflake's materialized view's timestamp created_on later than refreshed_on and compacted_on of the base table's but has behind_by value > 0
Steps to reproduce:
Setup some data:
CREATE OR REPLACE TRANSIENT DATABASE ORDERS;
CREATE OR REPLACE SCHEMA ORDERS.TPCH_SF100;
CREATE OR REPLACE TABLE ORDERS.TPCH_SF100.ORDERS AS
SELECT *
...
- 65
1
vote
1
answer
55
views
Pivot with Max aggregate function with null values is not working in Snowflake
Need help below is the query , but table t3 is blank , which is giving me error in snowflake i.e unexpected 'SEP'.
select x48.SEP from
(
(
SELECT * FROM
(SELECT EID
, month11
, MIN(date_id)...
- 243
0
votes
1
answer
74
views
ASOF join snowflake using 'nearest', similar to pandas.merge_asof
Is there a way to perform ASOF join in Snowflake / DuckDB / CedarDB based on nearest time in the row in the right table whose 'on' key is closest in absolute distance to the left table.
Currently I ...
- 4,551
0
votes
0
answers
128
views
Working on Snowflake badge 3 training for Streamlit, getting snowflake connection error: No module named 'snowflake'
I am working on my 3rd Snowflake badge. I am using Streamlit. I am clearly missing something but I can't figure it out. So when I run my Streamlit app through https://streamlit.io I get this error:
...
- 77