Questions tagged [snowflake]
Snowflake is a cloud-based data warehouse
49 questions
1
vote
1
answer
24
views
Snowflake DB Clone?
So this may be confusing but just looking to see if anyone has any insight on how to maybe do this in a simpler fashion.
So our company utilizes Fivetran for data transport, Snowflake for warehousing ...
- 11
0
votes
0
answers
16
views
Snowflake SQL replace function - leverage a dictionary?
Looks like translate works for multi translations of single characters (abc -> xyz), but Replace in snowflake will only do 1 replacement.
I have a list of JSON objects in a Snowflake SQL database ...
- 1,709
0
votes
0
answers
12
views
How to use current user credentials in published Power BI report against Snowflake?
We are using Power BI as our reporting and dashboard tool for our Snowflake data warehouse. WE have a need for a published report to use the credentials of the person calling the report rather than ...
1
vote
1
answer
371
views
Regex help in Snowflake
I am trying to write a regex that can replace '[', ']' and '].' with '_' and in cases where ']' is the last character it should be replaced with '' but I am struggling to come up with a regex for it ...
- 11
0
votes
0
answers
461
views
In Snowflake, default value NULL on a not-nullable column?
In Snowflake, what are the implications of having a default value of null on a non-nullable column?
Also, when I describe a particular table in Snowflake, I see a default value of NULL in all columns. ...
- 65
0
votes
0
answers
171
views
Recursive CTE hiearchy snowflake -- join/ expand outward (columns) instead of rows?
I'm doing a dependency mapping explosion (like parts explosion).
I did a typical recursive CTE with a union all.
It looks like
with CTE as
( select abc from myTable where start_point = X
union all
...
- 1,709
2
votes
2
answers
320
views
Snowflake won't allow an analytic function to be renamed
In the code below, I can do the LAG and PARTITION BY with no problem as a query. But I need it as a table. If I do CTAS, I need an alias on the column. But when I try to put an alias on it, it fails....
- 131
0
votes
0
answers
33
views
Snowflake Backup strategy
I am looking to setup rolling backup strategy for snowflake databases. currently I have following setup.
Min Data retention/Time-travel at account level : min 5 days
Fail-safe default : 7 days
I am ...
- 119
0
votes
1
answer
155
views
SQL Pattern to get "root/ start" of uneven hierarchy dataset
I feel this is a common problem and I've seen it in some SQL challenges even but for the life of me, cannot think clearly about a solution.
Say you have an uneven hierarchy. Elements that belong to ...
- 1,709
1
vote
0
answers
34
views
Group each set of consecutive seats within a row_id in Snowflake
I want to group row_id and seat_number as follows so I can group by row_id and group_id (desired column) to pick the minimum seat number as start_seat and the maximum seat number as end_seat:
row_id ...
- 11
0
votes
0
answers
59
views
Generate list of tables/ views hit by a list of queries in Snowflake
Snowflake provides a list of queries against the databases in account_usage.query_history. I'd like to dive deeper into what tables/ views are being hit most often -- but often there are very ...
- 1,709
-1
votes
1
answer
48
views
Displaying values in one column as multiple columns
I have a table which millions of records like this:
The desired format of the data is this:
how do i achieve this? tried a cross join but didnt help.
pivot is taking too long to execute as the ...
-2
votes
2
answers
554
views
In Snowflake, or SQL Server, how do I order results in the same order as my query?
In Snowflake, I'm querying the Information_Schema for the number of columns in each of a series of listed tables. How do I write my query so that Snowflake will return the results in the same order ...
- 65
1
vote
1
answer
37
views
creating a CTE to filter previous CTE populaiton down. How to find new diagnosis of certain ICD 10 code?
The first CTE grabs all patients with the diagnosis codes for substance abuse, the second CTE should filter down to only NEW paitents.
Meaning of the people in the first CTE, which of thses people ...
- 21
1
vote
1
answer
1k
views
How to pass parameters to a Snowflake query in SSRS
Where SQL Server is a data source, one can simply, write:
SELECT name, age
FROM egTable
WHERE age = @age
in the dataset query part of an SSRS report.
However, if your dataset is connected via ...
- 21
0
votes
1
answer
242
views
Snowflake - extracting country/ state names - RegEx or Array?
I have a bunch of sloppy Geo text in a field.
However the country/ state names or codes are relatively clean.
It'll say Poland 23 or Illinois Remote or Frogballs, Germany or TX, AL, AK.
I have a ...
- 1,709
0
votes
0
answers
901
views
Mismatched Union in Snowflake - dynamic approach instead of dummy columns?
As far as I understand, in snowflake, the union works as follows-
Fails if there are a different number of columns.
Stitches together columns based on order alone, not names.
Well that's unfortunate.
...
- 1,709
1
vote
2
answers
2k
views
Search a text column for a string from a list, and return the found string
I'm trying to search larger text for one of multiple values, lets say "Jack", "Jill", or "Bob" (the words can be 50 characters long).
With Snowflake I have something like
...
- 1,709
0
votes
1
answer
459
views
In Snowflake, how do I include a timestamp in a result that has zero rows?
I have run a query in Snowflake that returns zero rows. How do I add a timestamp to a query that returns zero rows?
- 65
-2
votes
1
answer
423
views
How do I compare the view_sql for two views in Snowflake using an SQL query?
I've got two views that are both in Snowflake but within two different databases. I want to confirm that the view_sql for the two views are identical. I am not familiar with python. Is there a way ...
- 65
3
votes
1
answer
1k
views
In Snowflake, how do I CAST CURRENT_TIMESTAMP up to the minute only?
In Snowflake, how do I CAST CURRENT_TIMESTAMP up to the minute only. I don't want seconds, milliseconds, or timezone included in my result.
- 65
2
votes
0
answers
73
views
SnowFlake/SnowSQL: Compilation Scripts
In Oracle/Postgres it is possible to write a script that will run other scripts/files such as
compile_script.sql (contains)
@script1.sql
@script2.sql
@script3.sql
When you run compile_script.sql it ...
- 1,049
0
votes
1
answer
726
views
How can I pivot column headers into rows?
I would like to get a list of column headers for a table.
I have queried
SELECT TOP 0 * FROM TABLE_NAME.
But now I want to pivot those column names into rows. How do I do that?
- 65
-1
votes
2
answers
1k
views
XML data type cannot be compared or sorted
My source is in SQL server; my target is in Snowflake. The target table was created by extracting the XML column only from the source. I need to compare the first 20 rows in source and target to see ...
- 65
0
votes
1
answer
1k
views
Create an ERD diagram from a set of SQL CREATE TABLE statements
We have a Snowflake database that does not maintain relationships between tables. The lack of relationships is not my decision and I have no control over this. I would like to generate an ERD diagram ...
- 101
1
vote
2
answers
5k
views
Get progressive sum of column by date
I've an Employee table with StartDate and EndDate as columns. How can I get a progressive sum of employees on per daily basis?
ID
StartDate
EndDate
1
2022-02-01
2022-02-28
2
2022-02-01
2022-03-31
...
- 111
0
votes
1
answer
2k
views
Can i join output of "Describe" queries with other tables in snowflake
I trying to use the Describe table in more complex way, thing like join with other tables , or other sql manipulation.
On the other hand it's look that snowflake not accept the Describe table as ...
- 145
2
votes
1
answer
612
views
Best practices for large JOINs - Warehouse or External Compute (e.g. Spark)
I am working on a problem that requires a very large join. The JOIN itself is pretty straightforward but the amount of data I am processing is very large. I am wondering for very large JOINs, is there ...
- 543
3
votes
2
answers
630
views
Apply nth occurrence number on each distinct value in a column
I have a table with a column of values where each value occurs a variable number of times (i.e., one value may occur 1 time, and another value may occur 3 times). I need to add a column that ...
- 133
2
votes
1
answer
994
views
Dimension modelling for HR with Employee Dimension and multiple departments in a Data warehouse
What is the best way to configure a dimension model (preferably star schema) when we have the following requirements?
There is an Employees table (25 attributes) where we are required to make some of ...
- 21
0
votes
1
answer
45
views
Calculate a cumulative amount with a given time
I want to identify customers who have spent more than D dollars over a period of P hours. Suppose D=$10 and P=48. Then in the transaction data below:
CUSTOMER STAMP AMOUNT
-------- ...
- 103
0
votes
1
answer
89
views
Leaf Joins in Data Warehouse Design
Upon modeling my very first data warehouse model (for historical stock market data), I stumbled across a data relationship that I don't know how to best account for. The fact tables include multiple ...
0
votes
1
answer
218
views
Is Star/Snowflake database design good for applications? What is the alternative?
I need to solve an exercise for a pharmaceutical company, they ask me to create a database structure from scratch given a few informations.
Because I want it to be scalable and "future proof"...
- 1,629
-1
votes
1
answer
59
views
Non-procedural way of implementing ELT dependency logic
I have two tables with identical schema, sales_primary and sales_secondary:
productID
trxnWeekStartDate
dollarAmt
123
2021-02-07
100
For a particular week, these two tables can both have a ...
- 313
0
votes
1
answer
127
views
Convert query for count and updated date
I currently run the below query in Snowflake for some of my reconciliations and have never worked in PostgreSQL, which we just adopted.
Select count(1), min (LAST_UPDATED_DATE), max(LAST_UPDATED_DATE)
...
0
votes
1
answer
364
views
Snowflake: how to allow only Key Pair Authentication connections?
Is there any way to configure Snowflake to allow only key pair authenticated users' connections?
At the moment you can add RSA_PUBLIC_KEY_FP to any user. But he can connect easily without specifying ...
- 164
1
vote
1
answer
50
views
choose MAX sub-category if null
Here is some made-up data as an example:
Category
Sub-Category
COUNT
Furniture
Chairs
25
Furniture
Null
15
Technology
Null
10
Technology
Phones
35
What I'm trying to do is... Keeping CATEGORY ...
- 21
0
votes
1
answer
223
views
SQL Server 2016 compress function details - external decompression
According to SQL Server 2016 docs COMPRESS and DECOMPRESS methods are just a blackbox - you put the data in and after some magic it gets compressed or decompressed. The problem is that I need to find ...
- 111
0
votes
1
answer
472
views
Grant permissions to only specific tables in a Schema
I am having a schema where I wanted to grant permissions in the following manner. What is the right way of doing this? To keep this simple, I am putting an example.
SCHEMA - HR
TABLES:
GEO_EMPLOYEE
...
- 111
0
votes
1
answer
309
views
Snowflake/S3 Pipeline: ETL architecture Questions
I am trying to build a pipeline which is sending data from Snowflake to S3 and then from S3 back into Snowflake (after running it through a production ML model on Sagemaker). I am new to Data ...
- 101
0
votes
1
answer
566
views
Calculate start and end dates from a list of only start dates and partitioned
I'm currently working with a data set with the following table structure
create or replace TABLE FACT_TBL_PRODUCTSUPPLIER (
DATEID NUMBER(18,0) NOT NULL,
PRODUCTNUMBER NUMBER(38,0) NOT NULL,
...
- 3
-2
votes
1
answer
299
views
SQL Case statement
Need SQL query help for the logic as below
If at least 1 user under an account is "Enabled" then status = 'Active'
If all users under an account are not "Enabled" then status = 'Cancelled'
else '...
- 109
0
votes
1
answer
565
views
Merge multiple rows in table setting one column based on value in rows
I have some data that looks something like this:
key B
1 true
1 false
2 false
2 false
And I want to roll it up so that if column B has two false values then the resulting value for B is ...
- 137
1
vote
2
answers
2k
views
Window function in Update statement
Is there a work around to use window functions in update statement
You can populate sample/original table using the code
Create table #table(
key1 Char(5),
date1 Date,
date2 Date
);
Insert into #...
0
votes
1
answer
43
views
Repeatedly emptying a table, keeping the table itself [closed]
I have been using "delete from table_xyz" to delete everything in the table. Would it be better to instead use truncate? What difference does it make?
2
votes
2
answers
16k
views
How can you insert data into a ARRAY column on a Snowflake table using SQL?
I am having difficultly finding documentation on how to insert data into an ARRAY column type using SQL on a Snowflake table.
Snowflake Documentation: https://docs.snowflake.net/manuals/sql-reference/...
- 543
4
votes
1
answer
2k
views
How to do hierarchical queries without CTE tables or recusive queries in Snowflake? (parent, child relationships)
I have several tables in Postgres that we are migrating to Snowflake. Some of these table are hierarchical (a foreign key point to the same table), normally to query this data I use CTE tables and/or ...
- 543
-1
votes
1
answer
2k
views
Use Sha vs md5 or Hash in Snowflake-db
Let me preface by saying that I am not using this for storing passwords or any other sensitive info -- I simply want a row-level sha/hash that I can use later or to check for unique records. My tables ...
- 111
2
votes
2
answers
518
views
Getting timepart from timestamp
Hello my little snowflakes, how can I isolate the correct time part of a timestamp?
SELECT CURRENT_TIMESTAMP() ...
- 1,194