Questions tagged [snowflake]

Snowflake is a cloud-based data warehouse

Filter by
Sorted by
Tagged with
1 vote
2 answers
441 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 ...
user avatar
  • 111
-1 votes
1 answer
44 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 ...
user avatar
1 vote
1 answer
44 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 ...
user avatar
3 votes
2 answers
147 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 ...
user avatar
  • 133
2 votes
0 answers
197 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 ...
user avatar
0 votes
1 answer
32 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 -------- ...
user avatar
  • 103
0 votes
1 answer
43 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 ...
user avatar
0 votes
1 answer
98 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"...
user avatar
-1 votes
1 answer
52 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 ...
user avatar
  • 313
0 votes
1 answer
39 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) ...
user avatar
0 votes
1 answer
89 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 ...
user avatar
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 ...
user avatar
0 votes
1 answer
92 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 ...
user avatar
0 votes
1 answer
74 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 ...
user avatar
  • 111
0 votes
1 answer
146 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 ...
user avatar
  • 101
0 votes
1 answer
130 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, ...
user avatar
  • 3
-2 votes
1 answer
58 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 '...
user avatar
0 votes
1 answer
202 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 ...
user avatar
  • 107
1 vote
2 answers
740 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 #...
user avatar
0 votes
1 answer
38 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?
user avatar
2 votes
2 answers
12k 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/...
user avatar
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 ...
user avatar
-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 ...
user avatar
2 votes
2 answers
147 views

Getting timepart from timestamp

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