Questions tagged [snowflake]
Snowflake is a cloud-based data warehouse
30
questions
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; ...
- 21
-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 ...
- 135
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 ...
- 441
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 ...
- 21
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,314
-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 ...
- 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
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 ...
- 111
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/...
- 441
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 ...
- 441
-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
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