Snowflake interview questions

What a strong answer covers

• Define a Virtual Warehouse as a cluster of compute resources used for query execution.
• Explain its role in separating compute from storage, allowing independent scaling.
• Discuss how different warehouse sizes (e.g., XS, S, M) impact query performance and concurrency.
• Detail how warehouses are billed per second of usage, contributing to cost management by suspending when idle.

Where people lose the point

• ×Confusing a Virtual Warehouse with a traditional database server or a storage unit.
• ×Not mentioning the independent scaling of compute and storage.
• ×Failing to explain the cost implications of warehouse size and auto-suspend features.

What a strong answer covers

• Define stages as locations where data files are stored before loading into or after unloading from Snowflake tables.
• Describe internal stages as Snowflake-managed storage, suitable for temporary or small-scale data transfers.
• Explain external stages as references to external cloud storage locations (e.g., S3, Azure Blob), ideal for integrating with existing data lakes.
• Discuss the use cases for each type, emphasizing security and accessibility considerations.

Where people lose the point

• ×Confusing stages with actual Snowflake tables or schemas.
• ×Not clearly distinguishing between the management and accessibility of internal vs. external stages.
• ×Overlooking the role of stages in both data loading and unloading.

What a strong answer covers

• Explain `COPY INTO` as the primary SQL command for bulk data loading from a stage into a table.
• Provide a basic SQL example: `COPY INTO my_table FROM @my_stage/path/to/file.csv FILE_FORMAT = (TYPE = CSV);`
• Discuss key considerations such as specifying file format options (TYPE, FIELD_DELIMITER, SKIP_HEADER), error handling (`ON_ERROR`), and data transformation during load.
• Mention the ability to load from multiple files and pattern matching.

Where people lose the point

• ×Forgetting to specify the `FROM` clause with the stage location.
• ×Incorrectly defining file format options or omitting them entirely.
• ×Not considering error handling strategies for failed loads.

What a strong answer covers

• Define Time Travel as the ability to access historical data (data that has been changed or deleted) at any point within a defined retention period.
• Explain that it's enabled by default for all tables and doesn't require explicit backups.
• Describe common use cases: querying data as it existed at a specific timestamp (`AT` clause), recovering dropped tables or rows (`UNDROP`), and analyzing data changes.
• Mention the `DATA_RETENTION_TIME_IN_DAYS` parameter and its impact on storage costs.

Where people lose the point

• ×Believing Time Travel requires manual backups or complex configuration.
• ×Not understanding that Time Travel works for both data changes and object drops.
• ×Confusing Time Travel with standard version control systems.

What a strong answer covers

• Define Zero-Copy Cloning as the ability to create instant, writable copies of databases, schemas, or tables without physically duplicating the underlying data.
• Explain that clones initially point to the same micro-partitions as the original object, making the operation metadata-only.
• Detail the benefits: significant storage cost savings, rapid provisioning of development/test environments, simplified data recovery, and enabling data governance.
• Describe how changes to a clone are stored separately, ensuring the original data remains immutable.

Where people lose the point

• ×Assuming that cloning duplicates all the data, leading to high storage costs.
• ×Not understanding that clones are writable and independent of the original after creation.
• ×Failing to highlight the speed and efficiency benefits for development workflows.

What a strong answer covers

• Explain that Snowflake natively supports semi-structured data types like JSON, XML, Avro, and Parquet without requiring a predefined schema.
• Introduce the `VARIANT` data type as the primary way to store semi-structured data, allowing it to hold values of any other type.
• Describe how Snowflake automatically parses and optimizes semi-structured data stored in `VARIANT` columns for querying.
• Demonstrate how to query `VARIANT` data using dot notation (`:`) and array indexing (`[]`), and how to flatten nested structures using `LATERAL FLATTEN`.

Where people lose the point

• ×Believing that semi-structured data must be flattened into relational tables before loading.
• ×Not understanding the `VARIANT` data type's role or how to query it effectively.
• ×Failing to mention the performance implications of querying deeply nested `VARIANT` data without proper indexing or flattening.

What a strong answer covers

• Explain RBAC as a security model where privileges are granted to roles, and roles are granted to users or other roles.
• Describe the concept of a role hierarchy, where roles can inherit privileges from other roles, simplifying management.
• Outline a simple hierarchy: `SYSADMIN` (top-level), `SECURITYADMIN` (manage users/roles), `ACCOUNTADMIN` (ultimate control), `CUSTOM_ANALYST_ROLE` (read-only access to specific data), `CUSTOM_ETL_ROLE` (write access to specific staging tables).
• Discuss the principle of least privilege and how RBAC helps enforce it.

Where people lose the point

• ×Confusing users with roles or granting privileges directly to users instead of roles.
• ×Creating overly complex or flat role hierarchies that are difficult to manage.
• ×Not understanding the inheritance mechanism of roles.

What a strong answer covers

• Explain that Snowflake employs multiple caching layers to optimize query performance and reduce latency.
• Describe the Result Cache: stores query results for 24 hours, returning instantly if the same query is run and data hasn't changed.
• Detail the Warehouse Cache (Data Cache): stores data retrieved from storage in the local SSDs of a virtual warehouse, accelerating subsequent queries on the same data.
• Mention the Metadata Cache (part of Cloud Services): used for query optimization, pruning micro-partitions, and access control checks.

Where people lose the point

• ×Assuming only one type of cache exists in Snowflake.
• ×Not understanding the conditions under which the Result Cache is invalidated.
• ×Overlooking the role of the Warehouse Cache in reducing I/O from remote storage.

What a strong answer covers

• Start by analyzing the Query Profile to identify bottlenecks (e.g., large scans, joins, sorts, I/O).
• Check for appropriate Virtual Warehouse size and scaling policy; consider if the warehouse is undersized or if concurrency issues exist.
• Evaluate table design: check for effective micro-partition pruning, consider adding clustering keys for large tables with high selectivity.
• Review query logic: optimize `JOIN` conditions, filter early, avoid full table scans where possible, and consider using materialized views for complex aggregations.

Where people lose the point

• ×Immediately jumping to increasing warehouse size without proper diagnosis.
• ×Ignoring the Query Profile and relying on guesswork for optimization.
• ×Failing to consider data distribution and table design (e.g., lack of clustering) as potential causes.

What a strong answer covers

• Define Automatic Clustering: reorders data in micro-partitions based on specified clustering keys to improve pruning for range scans and equality filters.
• Define Search Optimization Service: creates a persistent search access path to accelerate point lookups and highly selective range scans on specific columns.
• Contrast their mechanisms: Clustering physically reorders data; Search Optimization creates an index-like structure.
• Discuss use cases: Clustering for large tables with frequent range queries on multiple columns; Search Optimization for very selective queries, especially point lookups, where clustering might not be sufficient.

Where people lose the point

• ×Confusing the two services or using them interchangeably.
• ×Not understanding that clustering physically reorders data while search optimization creates a separate access path.
• ×Failing to identify scenarios where one is more appropriate than the other (e.g., point lookups vs. broad range scans).

What a strong answer covers

• Explain `COPY INTO` as a batch loading mechanism, typically initiated manually or via scheduled tasks, suitable for periodic or large-volume loads.
• Describe Snowpipe as a serverless, continuous data ingestion service that loads data automatically as soon as new files arrive in a stage.
• Detail the key differentiators: `COPY INTO` is pull-based and batch-oriented; Snowpipe is push-based (via notifications) and continuous/near real-time.
• Discuss use cases: Snowpipe for streaming data, frequently updated datasets, or when low latency is critical; `COPY INTO` for historical loads, large one-time transfers, or less frequent batch updates.

Where people lose the point

• ×Assuming Snowpipe is always the better option for all data ingestion scenarios.
• ×Not understanding the 'serverless' and 'continuous' nature of Snowpipe.
• ×Failing to consider the cost implications and operational overhead differences between the two.

What a strong answer covers

• Explain dynamic data masking: creating a masking policy (SQL UDF) that transforms column data based on the querying role or user, without altering the underlying data.
• Provide an example of a masking policy that shows full data to `ANALYST_ADMIN` and masked data (e.g., last 4 digits) to `ANALYST_READ`.
• Explain row access policies: creating a policy (SQL UDF) that filters rows returned by a query based on the querying role or user, or other conditions.
• Provide an example of a row access policy that restricts users to only see rows from their assigned region or department.

Where people lose the point

• ×Confusing data masking with data encryption or physical data alteration.
• ×Not understanding that policies are applied at query time and are transparent to the user.
• ×Failing to consider the performance implications of complex masking or row access policies.

What a strong answer covers

• Define micro-partitions as immutable, compressed data units (typically 50-500 MB) that Snowflake automatically creates for tables.
• Explain that each micro-partition stores metadata about its contents, including value ranges, distinct values, and null counts for all columns.
• Describe how this metadata enables 'query pruning,' where Snowflake's query optimizer can skip scanning micro-partitions that do not contain data relevant to the query's filters.
• Discuss how effective pruning significantly reduces the amount of data scanned, leading to faster query execution and lower compute costs.

Where people lose the point

• ×Confusing micro-partitions with traditional fixed-size partitions or blocks.
• ×Not understanding the role of metadata in enabling query pruning.
• ×Failing to connect micro-partitions directly to performance benefits like reduced I/O and faster query times.

What a strong answer covers

• **External Stages:** Reference data in external cloud storage (e.g., S3, Azure Blob). Pros: leverages existing data lakes, no data duplication, direct integration with cloud services. Cons: requires external cloud account management, potential network latency, security configuration across platforms.
• **Internal Stages:** Snowflake-managed storage. Pros: simpler setup, fully managed by Snowflake, often faster for smaller/temporary loads, integrated security. Cons: data duplication if already in cloud storage, limited storage management options compared to external cloud providers.
• **Use Cases:** External for large-scale data lake integration, continuous ingestion (Snowpipe), or when data needs to be accessible by other tools. Internal for ad-hoc loads, temporary files, or when data is generated within Snowflake.
• **Security:** External requires managing IAM roles/credentials in the cloud provider; Internal leverages Snowflake's native security model.

Where people lose the point

• ×Assuming one type of stage is universally superior without considering specific use cases.
• ×Overlooking the security implications and management overhead of external stages.
• ×Not considering data residency or network latency as factors in the choice.

What a strong answer covers

• **Provider Account Setup:** Identify the data (e.g., `SALES_DB.PUBLIC.SALES_TRANSACTIONS`) to be shared and ensure it's in a dedicated database/schema.
• **Create a Share:** Use `CREATE SHARE` to define the share object. Grant usage on the database, schema, and select privileges on the specific tables/views to the share.
• **Add Consumer Accounts:** Add the partner's Snowflake account identifiers to the share using `ALTER SHARE ... ADD ACCOUNTS = ...`.
• **Consumer Account Access:** Explain that partners (consumers) will create a database from the share (`CREATE DATABASE ... FROM SHARE ...`) and can then query the shared data as if it were local, without data movement.

Where people lose the point

• ×Attempting to share data by physically copying it between accounts.
• ×Not understanding that data sharing is a live, secure link, not a one-time transfer.
• ×Failing to grant appropriate privileges (USAGE, SELECT) to the share, leading to access issues for consumers.