MCQs on Advanced Querying and Optimization | Snowflake

Master advanced querying and optimization techniques in Snowflake to unlock the full potential of your data. This chapter covers window functions and complex SQL queries, Snowpark for efficient data processing, managing query performance with query plans, and leveraging external functions and stored procedures for robust data operations.

MCQs

Topic 1: Window Functions and Advanced SQL Queries

1. Window functions in SQL are used for:
   a) Aggregating data across multiple tables
   b) Performing row-by-row operations
   c) Calculating values over a defined set of rows
   d) Creating tables dynamically
2. What keyword is mandatory for defining a window function?
   a) PARTITION BY
   b) ROWS BETWEEN
   c) OVER
   d) GROUP BY
3. Which of these is an example of a window function in Snowflake?
   a) SUM
   b) ROW_NUMBER
   c) MIN
   d) DISTINCT
4. What is the main difference between GROUP BY and window functions?
   a) GROUP BY is faster than window functions
   b) Window functions do not aggregate data into groups
   c) GROUP BY can only be used with joins
   d) Window functions require temporary tables
5. The ROW_NUMBER function:
   a) Counts the number of rows in a table
   b) Assigns a unique number to each row within a partition
   c) Calculates the sum of row values
   d) Filters rows based on conditions
6. Which clause in a window function determines how rows are partitioned?
   a) ROWS BETWEEN
   b) PARTITION BY
   c) ORDER BY
   d) WHERE
7. What does the RANK() function do in SQL?
   a) Returns a sequential number for each row
   b) Assigns a rank to rows based on their order
   c) Counts distinct values in a table
   d) Summarizes data across partitions

Topic 2: Using Snowpark for Data Processing

8. Snowpark is a feature of Snowflake designed for:
   a) Data warehousing
   b) Real-time analytics
   c) Programmatic data processing
   d) Query optimization
9. Which programming languages does Snowpark support?
   a) Python, Java, Scala
   b) Ruby, C++, JavaScript
   c) Go, R, Julia
   d) PHP, Perl, Swift
10. Snowpark enables developers to:
    a) Write data pipelines in familiar programming languages
    b) Design Snowflake dashboards
    c) Monitor warehouse activity
    d) Perform ad-hoc queries
11. The core component of Snowpark’s functionality is:
    a) Query plans
    b) DataFrames
    c) Stored procedures
    d) Caching
12. Snowpark operations are executed:
    a) Directly on Snowflake’s compute resources
    b) On a local developer’s machine
    c) In third-party cloud environments
    d) As batch processes only
13. Snowpark is most beneficial for:
    a) Real-time streaming analytics
    b) Building complex ETL pipelines programmatically
    c) Basic SQL querying
    d) Designing virtual warehouses
14. What makes Snowpark different from traditional SQL?
    a) Its focus on user interface design
    b) Its use of programming language APIs for data processing
    c) Its reliance on external compute engines
    d) Its ability to automatically optimize queries

Topic 3: Managing Query Performance with Query Plans

15. A query plan in Snowflake provides:
    a) A list of queries executed in the past
    b) An execution roadmap for a SQL query
    c) User activity reports
    d) Schema design recommendations
16. What tool in Snowflake is used to analyze query performance?
    a) Query Profiler
    b) Query Optimizer
    c) Query Execution Plan
    d) Performance Analyzer
17. Which of the following impacts query performance the most?
    a) Size of the database
    b) Query complexity and design
    c) User roles
    d) Number of warehouses
18. The EXPLAIN command in Snowflake is used to:
    a) Debug SQL syntax errors
    b) Describe a query’s execution plan
    c) Execute a query in stages
    d) Export query results
19. Query pruning in Snowflake refers to:
    a) Reducing query result size
    b) Avoiding unnecessary table partitions during execution
    c) Simplifying the SQL syntax
    d) Combining multiple queries into one
20. Which warehouse configuration helps improve query performance?
    a) Setting warehouses to auto-suspend
    b) Increasing warehouse size for complex queries
    c) Using a single warehouse for all queries
    d) Restricting warehouse concurrency

Topic 4: External Functions and Stored Procedures

21. External functions in Snowflake enable:
    a) Queries across different schemas
    b) Integration with external APIs or services
    c) Real-time data replication
    d) Automatic query optimization
22. To create an external function in Snowflake, you need:
    a) A valid SQL script
    b) An external API endpoint and credentials
    c) A Python script
    d) A virtual warehouse
23. Stored procedures in Snowflake are written in:
    a) SQL and JavaScript
    b) Python and Scala
    c) C++ and Rust
    d) PHP and Perl
24. The main purpose of stored procedures in Snowflake is to:
    a) Automate repetitive database tasks
    b) Design user interfaces
    c) Host external applications
    d) Perform network configuration
25. Which statement is used to create a stored procedure in Snowflake?
    a) CREATE FUNCTION
    b) CREATE PROCEDURE
    c) CREATE SCRIPT
    d) CREATE TASK
26. External functions are executed:
    a) Within Snowflake’s environment
    b) On third-party services or APIs
    c) On a local developer’s machine
    d) In virtual warehouses
27. A key use case for stored procedures in Snowflake is:
    a) Data cleaning and validation
    b) Real-time machine learning predictions
    c) Designing ETL pipelines
    d) Performing distributed computing
28. Which of these is NOT a characteristic of external functions?
    a) Requires an API integration
    b) Runs SQL logic outside of Snowflake
    c) Executes entirely on Snowflake resources
    d) Supports real-time integration with external systems
29. Stored procedures are triggered using:
    a) A query statement
    b) A CALL statement
    c) A RUN statement
    d) A SCRIPT statement
30. External functions in Snowflake require permissions from:
    a) ACCOUNTADMIN role only
    b) The database owner
    c) Both database owner and external API owner
    d) SYSADMIN role

Answer Key