SnowPro Associate: Platform Question 16
Single answer● Snowflake layersA data engineering team is troubleshooting why a nightly ELT pipeline in Snowflake is running slower than expected after source files are loaded. The team confirms that data has already been staged and loaded into Snowflake tables, and they want to identify which Snowflake layer is primarily responsible for executing the SQL transformations and scaling compute resources independently from data storage. Which Snowflake layer should they focus on?
- A
Cloud services layer
- B
Query processing layer
- C
Database storage layer
- D
External stage layer
Show answer and explanation
Correct answer: B
Explanation
Snowflake architecture is commonly described in three main layers: the database storage layer, the query processing layer, and the cloud services layer. In this scenario, the workload bottleneck occurs during SQL transformations after data is already loaded into Snowflake tables. That points to the query processing layer, where virtual warehouses provide the compute resources used to run queries, DML, and ELT transformations. One of Snowflake's key design principles is the separation of compute and storage, allowing the query processing layer to scale independently from the database storage layer. By contrast, the cloud services layer handles coordination functions such as authentication, metadata, optimization support, and transaction management, while the storage layer persists the data itself. This aligns with Snowflake documentation on architecture and virtual warehouses, which emphasizes independent scaling of compute and storage.
- A. Incorrect.
Incorrect. The cloud services layer coordinates activities such as authentication, infrastructure management, metadata management, query parsing, and access control. Although it supports overall query lifecycle operations, it is not the primary layer that performs the actual SQL execution work or provides the virtual warehouses used for compute scaling.
- B. Correct.
Correct. The query processing layer is the compute layer in Snowflake. It uses virtual warehouses to execute SQL statements, including transformations in ELT pipelines. This layer can scale independently of storage, which is why performance tuning for slow transformations typically starts with warehouse sizing, scaling, concurrency, or workload isolation in this layer.
- C. Incorrect.
Incorrect. The database storage layer is responsible for storing data in Snowflake's optimized, compressed, columnar format. It manages persisted table data, but it does not execute transformation queries. A common misconception is that slow SQL transformations are caused by the storage layer simply because the data resides there.
- D. Incorrect.
Incorrect. An external stage is not one of Snowflake's core architectural layers. It is an integration object used to reference data files in cloud storage such as Amazon S3, Azure Blob Storage, or Google Cloud Storage. Since the data has already been loaded into Snowflake tables, the external stage is not the layer responsible for executing the transformations.