When using a stored procedure in Snowflake, how many threads can the Snowflake API run concurrently?
Answer(s): A
In Snowflake, stored procedures written in JavaScript execute synchronously within a single thread. The Snowflake API executes stored procedures in a single-threaded manner. Stored procedures do not support parallel execution or multi-threading.Even if a virtual warehouse has multiple clusters, the stored procedure itself will always run on one thread at a time.
Given the following cron expression "0 1 * * * America/Los_Angeles", how will the task behave when changing from Daylight Savings Time to Standard Time, moving the clock one hour back?
Answer(s): B
The cron expression "0 1 * * * America/Los_Angeles" schedules the task at 1:00 AM PST/PDT.When Daylight Savings Time (DST) ends, clocks move back from 2:00 AM PDT to 1:00 AM PST, causing 1:00 AM to occur twice. Snowflake executes the task in both instances, running it twice.
A company has many nested JSON files in cloud storage. The company would like to use these files in complex JOIN operations.Which method would provide the BEST performance?
Answer(s): C
Loading JSON files into a permanent table as a VARIANT column provides the best performance for complex JOIN operations because:Snowflake optimizes queries on VARIANT columns.Indexed micro-partitions improve query performance.JSON functions (FLATTEN, LATERAL) allow efficient querying and joining.Unlike external tables, permanent tables support Snowflake's query optimization features.
Data is loaded into a staging table every day. From there, several departments perform transformations on the data and load it into different production tables.How should the staging table be created and used to MINIMIZE storage costs and MAXIMIZE performance?
Transient tables do not incur Fail-Safe storage costs and do not retain historical data, reducing storage costs.Setting DATA_RETENTION_TIME_IN_DAYS = 0 ensures that no additional storage is used for Time Travel, maximizing performance and cost efficiency.Unlike temporary tables, transient tables are available across sessions, allowing multiple departments to access the data.
A Data Engineer is reviewing Python data pipeline code that is dynamically scaling warehouse HOL_WH:What effect is the WAIT_FOR_COMPLETION clause having on the pipeline?
The WAIT_FOR_COMPLETION = TRUE clause in ALTER WAREHOUSE ensures that the command does not return until the warehouse resize operation is fully completed.This prevents subsequent operations from executing until all compute resources are provisioned at the new size. Without this clause, the command would return immediately and resizing would occur in the background.
Post your Comments and Discuss Snowflake SnowPro Advanced Data Engineer exam prep with other Community members:
Our website is free, but we have to fight against AI bots and content theft. We're sorry for the inconvenience caused by these security measures. You can access the rest of the SnowPro Advanced Data Engineer content, but please register or login to continue.