SELECT with Snowflake functions - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Understanding the output of a SELECT with DATE_TRUNC

What is the output of this query when run on 2024-06-15 14:35:20?

SELECT DATE_TRUNC('month', '2024-06-15 14:35:20'::TIMESTAMP) AS truncated_date;

Snowflake

SELECT DATE_TRUNC('month', '2024-06-15 14:35:20'::TIMESTAMP) AS truncated_date;

A 2024-06-01 00:00:00.000

B 2024-06-15 00:00:00.000

C 2024-01-01 00:00:00.000

D 2024-06-15 14:35:20.000

💡 Hint

DATE_TRUNC cuts off smaller parts of the date to the start of the specified part.

DATE_TRUNC('month', timestamp) returns the first day of the month at midnight for the given timestamp.

🧠 Conceptual

intermediate

Effect of TRY_CAST on invalid data

What will be the result of this query?

SELECT TRY_CAST('abc' AS INTEGER) AS result;

Snowflake

SELECT TRY_CAST('abc' AS INTEGER) AS result;

A Error: invalid input syntax for integer

B 0

C abc

D NULL

💡 Hint

TRY_CAST returns NULL instead of error when conversion fails.

TRY_CAST returns NULL when the cast fails instead of raising an error.

❓ Configuration

advanced

Using LATERAL FLATTEN to extract array elements

Given a table with a VARIANT column named data containing JSON arrays, which query correctly extracts each element as a separate row?

Assume table name is events and column is data.

Options:

Snowflake

SELECT value FROM events, LATERAL FLATTEN(input => data);

A SELECT FLATTEN(data) FROM events;

B SELECT data.value FROM events FLATTEN(data);

C SELECT value FROM events, LATERAL FLATTEN(input => data);

D SELECT value FROM events FLATTEN(input => data);

💡 Hint

LATERAL FLATTEN is used with FROM clause and input parameter.

Option C uses correct syntax: LATERAL FLATTEN with input => data in FROM clause to extract array elements.

❓ security

advanced

Preventing SQL injection with Snowflake functions

Which Snowflake function helps safely handle user input to prevent SQL injection when constructing dynamic SQL queries?

A IDENTIFIER()

B TO_VARCHAR()

C PARSE_JSON()

D TRY_CAST()

💡 Hint

This function safely treats input as an object name, not code.

IDENTIFIER() safely treats input as an object identifier, preventing injection in dynamic SQL.

❓ Architecture

expert

Optimizing query performance with clustering keys

You have a large Snowflake table frequently queried by a date column. Which approach best improves query performance for date range filters?

A Create a materialized view without clustering

B Define a clustering key on the date column

C Use a multi-cluster warehouse without clustering keys

D Partition the table by date using standard SQL partition syntax

💡 Hint

Clustering keys physically organize data to speed up range queries.

Clustering keys organize data on disk by specified columns, improving performance on range queries like date filters.