I there a way to update values for top-n/limit amount of records in Snowflake? Sample data, top rows are the ones that need to flaged: The logic must combine SELECT n FROM limit 200 with SET FLAG = 1 Below syntax is not working: Answer If you need to update only a limited number of rows, you can try this
Tag: snowflake-cloud-data-platform
Return only last statement from Snowflake SQL query to R
I’ve got a Snowflake SQL query I’m trying to execute in R via ODBC connection that looks like this Querying Microsoft SQL Server from R I’d normally include set nocount no; at the top of the query to ensure only the last step is returned to R to avoid the error Actual statement count 6 did not match the desired
How to update table with summarized view from a config table?
I have a table A with data associated for an account for each month and there could be multiple entries in each month with following data as shown in snippet. Expected transformed table I want to generate table something like below as shown in below snippet. Idea is that for each distinct account_id in table A (configuration) , would like
SQL – return rows in partition based on max value
I have below dataset with comments which row must be returned. Logic is: return value for max kg_to within partition of each country. Current working code: Question: Shorter code would be better, any ideas on how to improve it? Answer Use distinct on: Or window functions: Note: I also don’t see how your code could be retrieving duplicates, unless you
Nested JSON parsing using Snowflake SQL
I have a problem parsing a certain nested JSON structure in Snowflake. The problem here is the usage of special characters like @ and # for example for some of the elements. Those characters prevents me from using simple dot notation when trying to access some of the elements without resourcing to a really complicated queries utilizing joins and where
How to update only first NULL column with a value in Snowflake sql?
How to update a table first Null column with value and other Null columns with the text ‘Available’? My attempt: I tried using Case statements but it is affecting the performance of a query. Is there any efficient way to update a table as mentioned below the expected format? Expected Output: Answer Try using a combination of nvl2 and coalesce
Tasks in snowflake – multiple SQLs
Can a Task execute multiple SQLs at a time in single transaction? I know stored procedure does but wanted to see if we can start a transaction in task and execute multiple SQLs
dbt macro – using *args/**kwargs
Occasionally, our tables need to append multiple columns together to make a unique key. This can be done by doing something like: select *, col1 || “_” || col2 as unique_key from …
Snowflake Failed to cast variant value “{TIMESTAMP VALUE}” to TIMESTAMP_NTZ
Above is the copy statement that I have made. But I keep getting the following error “Failed to cast variant value “2020-09-265T08:03:42.358Z” to TIMESTAMP_NTZ” Answer There’s a problem with the input data: September 265 is not a date. If you want to parse that date to null, you can use TRY_CAST instead. If you want to attempt to fix the
Convert YYYYMMDD to MM/DD/YYYY in Snowflake
I need help in figuring out the date conversion logic in Snowflake. The documentation isn’t clear enough on this. In SQL Server, I would try SELECT CONVERT(DATE, ‘20200730’, 101) and it gives me ’07/…