Questions tagged [snowflake]

Snowflake is a cloud-based data warehouse

Filter by
Sorted by
Tagged with
2 votes
2 answers
189 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....
James Madison's user avatar
0 votes
0 answers
10 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 ...
SqlDBA's user avatar
  • 117
0 votes
1 answer
51 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 ...
user45867's user avatar
  • 1,683
1 vote
0 answers
19 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 ...
kapashh's user avatar
  • 11
0 votes
0 answers
9 views

Why does GUI view details and SELECT COUNT(*) return different numbers of rows for snowflake?

When I right click on a table and click view details in the GUI I get a window below which mentions the number of rows. However this number of rows differents from when I do SELECT COUNT (*) as ...
user1821961's user avatar
0 votes
0 answers
18 views

Snowflake For i in 1 to 10 - can't figure out variable scope

I can't figure out Snowflake variables. I have: Declare max_count integer default 100; product varchar default null; BEGIN for i in 1 to max_count do let product = (select product from mytable where ...
user45867's user avatar
  • 1,683
0 votes
0 answers
29 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 ...
user45867's user avatar
  • 1,683
-1 votes
1 answer
36 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 ...
Dipanjana's user avatar
-2 votes
2 answers
140 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 ...
Jimbo's user avatar
  • 65
0 votes
0 answers
25 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 ...
Michelle's user avatar
0 votes
0 answers
191 views

In Snowflake, and in SQL Server, how do I unpivot 83 columns into 83 rows?

I need to compare the SUMs of 83 columns in my source table (in SQL Server) to the SUMs of 83 columns in my target table (in Snowflake) to see if the SUMs of the source and target table columns match. ...
Jimbo's user avatar
  • 65
1 vote
1 answer
430 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 ...
Koketso M's user avatar
0 votes
1 answer
148 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 ...
user45867's user avatar
  • 1,683
0 votes
0 answers
588 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. ...
user45867's user avatar
  • 1,683
1 vote
1 answer
1k 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 ...
user45867's user avatar
  • 1,683
0 votes
1 answer
331 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?
Jimbo's user avatar
  • 65
-2 votes
1 answer
194 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 ...
Jimbo's user avatar
  • 65
3 votes
1 answer
680 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.
Jimbo's user avatar
  • 65
1 vote
0 answers
59 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 ...
Joe W's user avatar
  • 1,029
0 votes
0 answers
14 views

Counting touchpoints to conversion in Snowflake

I have created a table structure that gives me when an ID enters site, the source it comes through, the dates associated with visit, as well as transaction date and transaction id. See sample below: ...
mnm7's user avatar
  • 1
0 votes
1 answer
491 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?
Jimbo's user avatar
  • 65
-1 votes
2 answers
597 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 ...
Jimbo's user avatar
  • 65
0 votes
1 answer
751 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 ...
Calab's user avatar
  • 101
1 vote
2 answers
4k 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 ...
Aniruddha's user avatar
  • 111
0 votes
1 answer
1k 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 ...
Eyal leshem's user avatar
2 votes
1 answer
488 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 ...
Arthur Putnam's user avatar
3 votes
2 answers
534 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 ...
Declan's user avatar
  • 133
2 votes
1 answer
709 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 ...
Irfan Gowani's user avatar
0 votes
1 answer
43 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 -------- ...
jsf80238's user avatar
  • 103
0 votes
1 answer
77 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 ...
Pit Wegner's user avatar
0 votes
1 answer
193 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"...
Francesco Mantovani's user avatar
-1 votes
1 answer
58 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 ...
Ryan's user avatar
  • 313
0 votes
1 answer
89 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) ...
pynoobatbest's user avatar
0 votes
1 answer
242 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 ...
Vasilii Chernov's user avatar
1 vote
1 answer
48 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 ...
techfan4506's user avatar
0 votes
1 answer
193 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 ...
Paweł Sopel's user avatar
0 votes
1 answer
377 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 ...
Julaayi's user avatar
  • 111
0 votes
1 answer
289 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 ...
cocoo84hh's user avatar
  • 101
0 votes
1 answer
494 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, ...
VGJ's user avatar
  • 3
-2 votes
1 answer
227 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 '...
Karthik's user avatar
  • 109
0 votes
1 answer
507 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 ...
fraxture's user avatar
  • 127
1 vote
2 answers
1k 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 #...
Sunil Babu Vudimudi's user avatar
0 votes
1 answer
42 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?
Niklas Rosencrantz's user avatar
2 votes
2 answers
15k 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/...
Arthur Putnam's user avatar
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 ...
Arthur Putnam's user avatar
-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 ...
Serban Tanasa's user avatar
2 votes
2 answers
384 views

Getting timepart from timestamp

Hello my little snowflakes, how can I isolate the correct time part of a timestamp? SELECT CURRENT_TIMESTAMP() ...
Kermit's user avatar
  • 1,194