← Back to Categories
Snowflake Interview Questions
Comprehensive list of 30 questions to test your knowledge.
Theory & Concepts
1
Snowflake architecture?
2
Virtual Warehouses scaling?
3
Micro-partitions?
4
Time Travel?
5
Data Sharing?
6
Snowpipe?
7
Zero-Copy Cloning?
8
Materialized Views?
9
Clustering?
10
Dynamic Data Masking?
11
Fail-safe?
12
Transient/Temp tables?
13
Semi-structured data?
14
Snowpark?
15
RBAC?
16
Billing?
17
External Tables?
18
Slow query optimization?
19
Stages?
20
Search Optimization Service?
21
Row Access Policies?
22
Sequences?
23
Query Profile?
24
Caching layers?
25
Secure Data Sharing?
Hands-on & Coding Scenarios
1
Snowpark DataFrame Operations
Context / Sample Data
Table: ANALYTICS.PUBLIC.SALES
| STORE_ID | PRODUCT | AMOUNT | SALE_DATE |
|----------|----------|---------|------------|
| S1 | Laptop | 1200.00 | 2024-01-15 |
| S1 | Phone | 800.00 | 2024-01-16 |
| S2 | Laptop | 1100.00 | 2024-01-15 |
| S2 | Tablet | 450.00 | 2024-01-17 |
| S3 | Phone | 900.00 | 2024-01-15 |Tasks:
- Write Snowpark Python to filter for amounts > 1000.
- Group by STORE_ID and sum AMOUNT.
- Collect results to a local Pandas DataFrame.
Follow-up / Optimization:
- How does Snowpark execute this?
- Why better than fetching raw data to Python?
2
JSON Ingestion via VARIANT
Context / Sample Data
Table: RAW.PUBLIC.EVENTS
Column: RAW_DATA (VARIANT)
Sample JSON payload stored in each row:
{
"event_id": "evt_991",
"user_id": "u_123",
"device_info": {
"os": "iOS",
"version": "15.4"
},
"user_actions": [
{"action": "click", "target": "login_btn"},
{"action": "scroll", "target": "homepage"}
]
}Tasks:
- Extract event_id, user_id, and os from the VARIANT column.
- Use FLATTEN to create a row for every action in user_actions.
- Materialize the flattened results into a structured table.
Follow-up / Optimization:
- Automate this for continuous streaming?
- How is VARIANT stored physically?
3
Zero-Copy Cloning & Time Travel
Context / Sample Data
Table: PRODUCTION.PUBLIC.USERS
| USER_ID | NAME | EMAIL | CREATED_AT |
|---------|-------|------------------|------------|
| 1 | Alice | alice@co.com | 2023-01-01 |
| 2 | Bob | bob@co.com | 2023-02-15 |
| 3 | Carol | carol@co.com | 2023-03-20 |
Scenario: A developer accidentally ran DELETE FROM USERS WHERE USER_ID > 1 at 2:30 PM.Tasks:
- Create a Zero-Copy Clone of USERS into a TESTING schema.
- Retrieve the table as it was exactly 2 hours ago (before the accidental delete).
- Restore the table to its state before a specific query ID ran.
Follow-up / Optimization:
- Does cloning consume storage immediately?
- Max Time Travel retention?
4
Snowpark UDFs & Stored Procedures
Context / Sample Data
Table: ML.PUBLIC.PREDICTIONS_INPUT
| CUSTOMER_ID | FEATURE_1 | FEATURE_2 | FEATURE_3 |
|-------------|-----------|-----------|-----------|\n| C001 | 0.5 | 1.2 | 3.4 |
| C002 | 0.8 | 0.9 | 2.1 |
| C003 | 1.1 | 1.5 | 4.0 |Tasks:
- Register a Python UDF in Snowpark that computes a simple score.
- Call the UDF on the DataFrame.
- List all registered UDFs.
Follow-up / Optimization:
- What is a Vectorized UDF (Batch UDF)?
- How to use 3rd party packages in Snowpark?
5
Streams, Tasks & Continuous Pipelines
Context / Sample Data
Source Table: RAW.PUBLIC.ORDERS
| ORDER_ID | CUSTOMER_ID | AMOUNT | ORDER_DATE |
|----------|-------------|--------|------------|
| 1001 | C1 | 250.00 | 2024-03-01 |
| 1002 | C2 | 100.00 | 2024-03-01 |
(New rows arrive continuously via Snowpipe)
Target Table: ANALYTICS.PUBLIC.DAILY_REVENUE (aggregated)Tasks:
- Create a Stream on the source table to capture new inserts.
- Create a Task that runs every 5 minutes.
- Start the task.
Follow-up / Optimization:
- WHEN SYSTEM$STREAM_HAS_DATA — what does it do?
- What happens to stream data after MERGE?