Questions tagged [snowflake]
Snowflake is a cloud-based data warehouse
22
questions
3
votes
2answers
72 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 ...
2
votes
0answers
33 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
1answer
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
-------- ...
0
votes
1answer
25 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
1answer
72 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
1answer
48 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 ...
0
votes
1answer
21 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
1answer
51 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
1answer
45 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
0answers
100 views
Snowflake: Creating a table with a column name which is ANSI reserved fails, but adding a column(alter table) same ANSI reserved column name succeeds
CREATE TABLE test_table (LOCALTIMESTAMP varchar(10))
This query fails with .invalid column definition name 'LOCALTIMESTAMP' (ANSI reserved)
ALTER TABLE test_table ADD COLUMN LOCALTIMESTAMP varchar(...
0
votes
1answer
55 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
1answer
27 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
...
0
votes
1answer
108 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 ...
0
votes
1answer
62 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,
...
-2
votes
1answer
54 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 '...
0
votes
1answer
127 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 ...
0
votes
2answers
462 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
1answer
36 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?
1
vote
1answer
11k 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
1answer
1k 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
1answer
918 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
2answers
129 views
Getting timepart from timestamp
Hello my little snowflakes, how can I isolate the correct time part of a timestamp?
SELECT CURRENT_TIMESTAMP() ...
