Questions tagged [snowflake]
Snowflake is a cloud-based data warehouse
24
questions
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
...
- 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 ...
- 125
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 ...
- 391
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 ...
- 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 ...
- 21
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
-------- ...
- 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 ...
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"...
- 1,125
-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 ...
- 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)
...
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 ...
- 164
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 ...
- 21
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 ...
- 111
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
...
- 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 ...
- 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,
...
- 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 '...
- 9
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 ...
- 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 #...
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?
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/...
- 391
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 ...
- 391
-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 ...
- 111
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() ...
- 1,194