Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [snowflake]

Snowflake is a cloud-based data warehouse

51 questions
4
votes
1 answer

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 recursive SQL statements. As far as I can tell…
Arthur Putnam
  • 553
  • 2
  • 6
  • 12
3
votes
1 answer

In Snowflake, how do I CAST CURRENT_TIMESTAMP up to the minute only?

In Snowflake, how do I CAST CURRENT_TIMESTAMP up to the minute only. I don't want seconds, milliseconds, or timezone included in my result.
Jimbo
  • 65
  • 1
  • 1
  • 9
3
votes
2 answers

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 identifies the occurrence sequence # of its corresponding…
Declan
  • 133
  • 3
2
votes
1 answer

setup snowflake task to run every 2nd Monday of the month

I setup task with following schedule to run every 2nd Monday of the month but seems like it setup task to run every Monday. How can I setup cron schedule to run every 2nd Monday of the month? USING CRON 0 11 1 * 1-1 UTC
SqlDBA
  • 171
  • 2
  • 12
2
votes
2 answers

Snowflake won't allow an analytic function to be renamed

In the code below, I can do the LAG and PARTITION BY with no problem as a query. But I need it as a table. If I do CTAS, I need an alias on the column. But when I try to put an alias on it, it fails. I think this is valid SQL. But I'm using…
James Madison
  • 131
  • 4
2
votes
0 answers

SnowFlake/SnowSQL: Compilation Scripts

In Oracle/Postgres it is possible to write a script that will run other scripts/files such as compile_script.sql (contains) @script1.sql @script2.sql @script3.sql When you run compile_script.sql it runs all three of those scripts. In snowSQL…
Joe W
  • 1,058
  • 9
  • 20
2
votes
1 answer

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 a preferred type of technology. For example, is…
Arthur Putnam
  • 553
  • 2
  • 6
  • 12
2
votes
1 answer

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 the attributes to SCD2. For e.g. Salary,…
Irfan Gowani
  • 21
  • 2
2
votes
2 answers

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/data-types-semistructured.html#array // example…
Arthur Putnam
  • 553
  • 2
  • 6
  • 12
2
votes
2 answers

Getting timepart from timestamp

Hello my little snowflakes, how can I isolate the correct time part of a timestamp? SELECT CURRENT_TIMESTAMP() AS a, …
Kermit
  • 1,194
  • 13
  • 27
1
vote
1 answer

Effective way to grant limited access to 3rd party service account in Snowflake?

I'm green as a dba somewhat. Not really a DBA actually. So we have a bunch of databases/ datatables ... we need to grant a small subsection of access (say 3 tables, show half the fields for PPI reasons). For some reason the project lead initially…
user45867
  • 1,739
  • 5
  • 24
  • 41
1
vote
1 answer

Snowflake DB Clone?

So this may be confusing but just looking to see if anyone has any insight on how to maybe do this in a simpler fashion. So our company utilizes Fivetran for data transport, Snowflake for warehousing and Tableau for BI. Unfortunately, we weren't…
AKBirite
  • 11
  • 1
1
vote
1 answer

Regex help in Snowflake

I am trying to write a regex that can replace '[', ']' and '].' with '_' and in cases where ']' is the last character it should be replaced with '' but I am struggling to come up with a regex for it that works in all cases because Snowflake does not…
Pepe Silvia
  • 11
  • 2
1
vote
0 answers

Group each set of consecutive seats within a row_id in Snowflake

I want to group row_id and seat_number as follows so I can group by row_id and group_id (desired column) to pick the minimum seat number as start_seat and the maximum seat number as end_seat: row_id seat_number group_id a 1 0 a 2…
kapashh
  • 11
  • 2
1
vote
1 answer

creating a CTE to filter previous CTE populaiton down. How to find new diagnosis of certain ICD 10 code?

The first CTE grabs all patients with the diagnosis codes for substance abuse, the second CTE should filter down to only NEW paitents. Meaning of the people in the first CTE, which of thses people have noy been diagnosised with the 2 codes before.…
Michelle
  • 21
  • 2
1
2 3 4