Questions tagged [snowflake]

Snowflake is a cloud-based data warehouse

Filter by
Sorted by
Tagged with
0 votes
0 answers
10 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: ...
  • 1
0 votes
1 answer
61 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?
  • 41
0 votes
0 answers
26 views

Which is faster between regex and string length function, in searching string of different length?

I have a table named table1 having 2 columns named as user_id and col1. The table contains 5 million rows. This column col1 contains 3 types of values: first ending with char 'x' and string length 13; ...
-1 votes
2 answers
140 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 ...
  • 41
0 votes
1 answer
61 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
0 votes
0 answers
21 views

Getting different Hierarchy levels and Path of level in Snowflake SQL

I am trying to replicate the result of a qlik function called Hierarchy. It creates the Hierarchy with different Levels and also gives the Hierarchy Path. The Code that i am using so far is giving me ...
  • 1
1 vote
2 answers
3k 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
355 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 ...
2 votes
1 answer
203 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 ...
3 votes
2 answers
344 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
0 answers
428 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 ...
0 votes
1 answer
39 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
59 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
130 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 votes
1 answer
57 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
52 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
159 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 ...
1 vote
1 answer
46 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 ...
0 votes
1 answer
145 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 ...
0 votes
1 answer
192 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
225 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
251 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
96 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
319 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 ...
  • 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 #...
0 votes
1 answer
40 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
13k 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/...
3 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 ...
-1 votes
1 answer
1k 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 ...
2 votes
2 answers
215 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