Snowflake Interview Questions and Answers

Snowflake is an analytic data warehouse implemented as a SaaS service. It is built on a new SQL database engine with a unique architecture built for the cloud. This cloud-based data warehouse solution was initially available on AWS as software for loading and analyzing massive volumes of data.

The most remarkable feature of Snowflake is its ability to spin up any number of virtual warehouses, allowing users to run unlimited independent workloads against the same data without risk of contention.

Three main layers make up the Snowflake architecture:

• Storage Layer: It stores data in compressed, columnar, encrypted micro-partitions. Snowflake deals with partitioning, compression, and metadata on their own
• Compute Layer (Virtual Warehouses): Where the queries live. Every warehouse is completely isolated. Your ETL won’t bog down another department’s dashboards.
• Cloud Services Layer: Handles authentication, query optimizer, transactions, access controls, and metadata. This is the brain behind the operations.

Some of the unique features of the Snowflake are listed below:

• Data Sharing
• Data Management (Zero-copy cloning and Time Travel)
• Supports Geospatial Data
• Result Caching and Dynamic Tables
• Search Optimization Service
• Security and Governance (Multi-cloud Support and HIPAA, GDPR)
• Cortex AI and Snowpark
• Iceberg Support and Streamlit.

The services layer acts as the brain of the Snowflake. In Snowflake, the Services layer authenticates user sessions, applies security functions, provides management, performs optimization, and organizes transactions.

No, Snowflake is not an ETL tool. The industry shifted to ELT, wherein raw data is sent directly to Snowflake and later transformed in the warehouse.

Dynamic Tables now natively handle declarative transformation. You define the target state, and Snowflake manages incremental refresh.

CREATE DYNAMIC TABLE daily_metrics
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
AS
  SELECT DATE_TRUNC('day', event_time) AS day,
    COUNT(*) AS events, COUNT(DISTINCT user_id) AS users
  FROM raw_events GROUP BY 1;

• Dbt has taken over the transformation layer.
• Fivetran is for any fully-managed ingestion (thousands of connectors)
• Airbyte is an increasingly common alternative for ingestion as well.

For more “traditional” ELT or enterprises looking for the last-mile integration:

• Azure Data Factory for the Microsoft-centric organizations
• AWS Glue for AWS-native organizations
• Informatica Intelligent Data Management Cloud for enterprise integration
• Snowflake OpenFlow for their NiFi-GA (native ETL/CDC) offering that dropped in 2025
• Matillion, if you're using their visual ELT capabilities.

Snowflake is a cloud-native relational database, and it is specifically designed as a data warehouse for analytics. It is built entirely on an SQL database. It’s a columnar-stored relational database that works well with Power BI, Excel, Tableau, and many other tools.

Asked by: Wipro

Snowflake stores data in multiple micropartitions, which are internally optimized and compressed. The data is stored in a columnar format in Snowflake's cloud storage. 

The data objects stored by Snowflake cannot be accessed or made visible to the users. By running SQL query operations on Snowflake, you can access them.

In Snowflake, a virtual warehouse, often known as a "warehouse," is a collection of computational resources. A virtual warehouse provides the resources required for the users, such as CPU, memory, and temporary storage, to perform multiple Snowflake operations:

• Execute the SQL SELECT statements that necessitate the use of computing resources  (e.g. retrieving rows from tables and views).
• DML operations include:
• Updating table rows (DELETE , INSERT , UPDATE).
• Data Loading into tables (COPY INTO <table>).
• Data unloading from tables (COPY INTO <location>).

Whenever we load data into Snowflake, it organizes it into a compressed, columnar, and optimized format. Snowflake deals with storing data, including compression, organization, statistics, file size, and other properties associated with data storage.

All the data objects we store in Snowflake are inaccessible and invisible. We can access the data objects by executing an SQL query in Snowflake.

CoWork (formerly known as Intelligence) is your personal AI assistant for knowledge work. It can interact with your company's data and maintain semantic understanding. Moreover, it functions according to Role-Based Access Control principles.

It actively provides valuable insights, streamlines tasks, and can generate documents (like PDFs and presentations). Access this via Snowflake's Snowsight, Slack, or the iOS app.

CoCo (formerly called Cortex Code)is the coding assistant for data experts in Snowflake. It can generate a broad range of code, including SQL, dbt models, Airflow DAGs, and ML pipelines.

Snowflake is a secure, fully managed AI service built into the Snowflake AI Data Cloud that delivers generative AI and ML directly to your SQL. No Python, no external infrastructure, no data movement.

• COMPLETE (LLM text generation - Anthropic, Meta, Mistral, Google models)
• SUMMARIZE (Automatic text summarization)
• CLASSIFY (Text classification)
• SENTIMENT (Sentiment analysis)
• AI_EXTRACT (GA Oct 2025 - Extract structured data from documents)
• AI_TRANSCRIBE (Audio to text; cut pricing 60% Jun 2026)
• Cortex Analyst (Natural language to SQL)
• Cortex Search (RAG retrieval)
• Cortex AISQL (Multimodal analysis via SQL)

Asked by: Cognizant, Infosys

Snowpark is a DataFrame API for Python, Java, or Scala that runs inside a Snowflake compute. Data never leaves the platform. Everything operates under the same RBAC as SQL.

You write data transformations in Python, Java, or Scala using a DataFrame API instead of SQL. That code runs inside Snowflake's compute layer, so your data never leaves the platform. Everything executes under the same security and governance policies as regular SQL queries.

from snowflake.snowpark import Session
from snowflake.snowpark.functions import col, sum as sum_

session = Session.builder.configs(params).create()
orders = session.table("raw_orders")

report = (orders.filter(col("status") == "completed")
  .group_by("region", "category")
  .agg(sum_("amount").alias("revenue"))
  .sort(col("revenue").desc()))

report.write.mode("overwrite").save_as_table("revenue_report")

Snowflake Notebooks give you a Jupyter-like environment right inside Snowsight. You can mix SQL, Python, and Markdown in a single document, run cells interactively, and share everything with your team.

The reason they matter is that you used to need an external Jupyter setup to do any Python-based data exploration or ML prototyping with Snowflake data. Now all of that happens inside the platform. Your compute runs on your virtual warehouse, your data stays in Snowflake, and your access is governed by the same roles and policies as everything else.

They went GA on all three clouds in 2025 and now support custom container runtimes via Snowpark Container Services, so you can run heavier ML workloads without spinning up external infrastructure. Git integration is built in for version control, and multiple people can collaborate on the same notebook with role-based access.

Iceberg tables let you store data in an open format that is not locked into Snowflake. Under the hood, your data sits in standard Parquet files using the Apache Iceberg table format.

You can query it in Snowflake with full performance, but the same data is also readable by Spark, Flink, Trino, or any other engine that supports Iceberg.

Why this matters in practice: say your data engineering team uses Snowflake, but your machine learning team prefers Spark. With Iceberg tables, both teams work on the same data without copying it anywhere. No duplicate storage, no sync pipelines, no vendor lock-in.

CREATE ICEBERG TABLE events (
  event_id STRING,
  event_type STRING,
  created_at TIMESTAMP
)
  CATALOG = 'SNOWFLAKE'
  EXTERNAL_VOLUME = 'my_s3_volume'
  BASE_LOCATION = 'events/';

Regular Snowpipe is file-based. A file lands in a stage; Snowpipe picks it up and loads it. Takes a few minutes. Fine for most batch use cases.

Snowpipe Streaming skips files entirely. Data flows row by row into Snowflake through an SDK with sub-second latency. No staging, no COPY INTO.

The simple rule: if your data arrives as periodic files, use Snowpipe. If you have a Kafka stream or an application emitting thousands of events per second, use Snowpipe Streaming.

Think of the Trust Center as Snowflake's built-in security health check. It continuously scans your account configuration and tells you where you have gaps: roles with excessive privileges, columns missing masking policies, users without MFA, unusual access patterns, and unexpected cost spikes.

Before the Trust Center existed, you had to audit all of this manually. Now Snowflake does it for you and gives you a security posture score. If something looks off, it flags it.

Here is what it actually does. You write your transformations as SQL SELECT statements. dbt calls these "models." You do not write INSERT or MERGE logic. You just write the SELECT that describes what the final table should look like, and dbt handles the materialization, whether that is a table, a view, or an incremental load.

dbt organizes all your models into a DAG, determines their dependencies, and runs them in the correct order. It also lets you write tests (is this column unique? are there null values where there shouldn't be?) and automatically generates documentation from your code.

How it works with Snowflake specifically: dbt connects to Snowflake as its execution engine. Your SQL runs on your Snowflake virtual warehouse. dbt sends the queries; Snowflake does the compute. Data never leaves the platform.

-- A simple dbt model: models/daily_revenue.sql
SELECT
  DATE_TRUNC('day', order_date) AS day,
  SUM(amount) AS total_revenue
FROM {{ ref('stg_orders') }}
WHERE status = 'completed'
GROUP BY 1

Both rank rows within partitions. The tie handling differs.

RANK() skips after ties: positions 1, 1, 3. DENSE_RANK() does not skip: positions 1, 1, 2.

SELECT name, dept, salary,
  RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS d_rnk
FROM employees;

We can access through:

• Snowsight is the primary web UI. The old "Classic UI" is fully deprecated.
• Snowflake CLI (snow) To assist with operations via command line, the Snowball CLI 'snow' has replaced the old SnowSQL.
• Multiple Snowflake Connectors are supported by languages like Python, Java (JDBC) & C#. 
• A complete Snowflake Notebook for SQL & Python-based queries.
• Snowpark API for DataFrame access in Python, Java, and Scala.
• REST API for programmatic account management.

# Modern CLI (recommended)
snow sql -q "SELECT CURRENT_WAREHOUSE();"

# Legacy SnowSQL (works but no new features)
snowsql -a myaccount -u myuser -q "SELECT CURRENT_WAREHOUSE();"

Asked by: TCS, Wipro, Cognizant, Infosys

I would secure data by following these steps:

1. Mandatory MFA for all human users (SSO with Okta, Azure AD, etc. included), password only is on the deprecation path.
2. Key Pair Authentication for machine identity/service account/pipelines.
3. NetworkPolicies for restricting IP address ranges and VPN.
4. Private link for Private connections from VNET to the service.
5. Tri Secret Secure to enable customer-managed encryption keys.
6. Dynamic Data masking, based on the roles column, will be applied.
7. RowAccess policies, row security based on user roles.
8. Column Level Security, very granular control of which user can view which columns.
9. Trust Center (2025 GA), based on posture scoring.

CREATE MASKING POLICY mask_ssn AS (val STRING)
  RETURNS STRING ->
  CASE WHEN CURRENT_ROLE() IN ('HR_ADMIN') THEN val
    ELSE 'XXX-XX-' || RIGHT(val, 4) END;

ALTER TABLE employees MODIFY COLUMN ssn
  SET MASKING POLICY mask_ssn;

Asked by: Deloitte, IBM, Infosys

A star schema denormalizes dimensions to reduce joins and improve query performance. A Snowflake schema normalizes dimensions to reduce storage redundancy.

In Snowflake, the platform, star schemas generally perform better because columnar compression handles wide tables efficiently.

Yes. External tables query S3/ADLS/GCS directly. Iceberg tables provide full read/write in open format. Storage integrations give secure, credential-free access.

Step 1: Open your query in Snowsight and see for any spilling, full scans, missing pruning, etc.

Step 2: Verify your partition pruning; are almost all partitions scanned? Create new ones and/or modify with clustering:

SELECT SYSTEM$CLUSTERING_DEPTH('big_table', '(order_date)');
ALTER TABLE big_table CLUSTER BY (order_date);

Step 3: Check for spills. Spill to a local or remote disk means the warehouse is too small. Scale up.

Step 4: Right-size the warehouse. 60 seconds on SMALL often becomes 8 seconds on LARGE at the same per-query cost.

Step 5: Enable search optimization for point lookups on high-cardinality columns.

ALTER TABLE customers
  ADD SEARCH OPTIMIZATION ON EQUALITY(customer_id, email);

Step 6: Materialize expensive repeated subqueries as Dynamic Tables or materialized views.

We can load using multiple methods. Knowing when to use each matters.

Using Bulk Load:

-- Bulk load from stage
COPY INTO raw_events FROM @my_stage/events/
FILE_FORMAT = (TYPE = 'JSON', STRIP_OUTER_ARRAY = TRUE)
ON_ERROR = 'CONTINUE';

Using Snowpipe:

-- Snowpipe (continuous auto load)
CREATE PIPE auto_pipe AUTO_INGEST = TRUE AS
  COPY INTO raw_logs FROM @my_stage/logs/
  FILE_FORMAT = (TYPE = 'CSV', SKIP_HEADER = 1);

Step 1: The first thing I do is open the Query Profile in Snowsight. It shows you exactly where time is being spent. Is data spilling to disk? Is it scanning every partition? Is there a bad join exploding row counts? The profile tells you before you start guessing.

Step 2: Check partition pruning. If the query scans millions of micro-partitions when it only needs a fraction, your WHERE clause is misaligned with how the data is physically organized. Adding a clustering key on the columns you filter by most often fixes this. I once cut a 45-second query down to 3 seconds on a 2TB table just by clustering on the date column.

Step 3: Look for spilling. If the profile shows data spilling to local or remote disk, the warehouse is too small for that workload. Scale up one size. A query that takes 60 seconds on Small often takes 8 seconds on Large, and the cost per query stays roughly the same because it finishes faster.

Step 4: Use result caching. If the same query runs repeatedly with no data changes, Snowflake returns cached results instantly. No warehouse needed. No cost. This is a free performance that many teams overlook.

Step 5: Enable search optimization for point lookups. If your query filters on high-cardinality columns such as customer_id or email, the search optimization service dramatically speeds up those selective lookups.

Step 6: Materialize expensive repeated work. If multiple dashboards or reports run the same heavy aggregation, put it in a materialized view or a Dynamic Table instead of recalculating it every time.

Step 7: Right-size your warehouses by workload. Do not run ETL and BI queries on the same warehouse. Give heavy transformations their own dedicated warehouse and keep analytics in a smaller one. They scale independently, so you are not overpaying.

• Clustering keys on a 2TB orders table decreased scan time drastically from 45 seconds to 3 seconds
• Warehouse right-sizing by analyzing profiles. Moved ETL to XL, kept BI on Small
• Search optimization on customer_id cut point lookup time by 90%
• Query tagging with QUERY_TAG to attribute costs to specific teams.

ALTER SESSION SET QUERY_TAG = 'team=marketing;pipeline=daily';

Three separate commands run one after another. They are not a script you paste and execute all at once.

First, you run the QUERY_HISTORY query to find exactly when the DELETE happened and confirm it was the accidental one.

Then you run the CLONE with the time offset to create a copy of the table from before the incident.

Then you run the two RENAME statements to swap the restored table into place.

Each step depends on the previous one. You need to confirm the DELETE details before you clone. You need the clone to exist before you swap.

Running them blindly as one block without checking results between steps would be reckless in a production incident.

Step 1: Find the DELETE

SELECT query_id, query_text, start_time
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE query_text ILIKE '%DELETE%critical_table%'
ORDER BY start_time DESC LIMIT 5;

Step 2: Clone from before the incident

CREATE TABLE critical_table_restored
  CLONE critical_table AT(OFFSET => -10800);

Step 3: Swap tables

ALTER TABLE critical_table RENAME TO critical_table_damaged;
ALTER TABLE critical_table_restored RENAME TO critical_table;

Asked by: Cognizant, Deloitte

CREATE TABLE raw_json (payload VARIANT,
  loaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP());

CREATE PIPE json_pipe AUTO_INGEST = TRUE AS
  COPY INTO raw_json (payload) FROM @json_stage
  FILE_FORMAT = (TYPE = 'JSON');

Snowpipe auto-ingest on S3 event notifications. Files are typically queryable within 1 to 2 minutes. Well within the SLA.

For transformations, chain a Dynamic Table.

CREATE DYNAMIC TABLE parsed_events
  TARGET_LAG = '5 minutes' WAREHOUSE = transform_wh
AS SELECT payload:event_id::STRING AS id,
  payload:type::STRING AS type,
  payload:ts::TIMESTAMP AS event_time
FROM raw_json;

Asked by: Infosys, TCS, Wipro

Every 10 seconds: Snowpipe Streaming via Ingest SDK. Thousands of tiny staged files per hour is wasteful. Streaming accepts rows directly. For Kafka sources, use Connector 4.0.

Every 4 hours: Standard Snowpipe with auto ingest. Low-frequency, file-based ingestion is efficient and cost-effective here.

Asked by: Deloitte

Clone captures a snapshot at the instant the CLONE command starts. Updates during the clone do not appear in it. After creation, source and clone are fully independent. Copy-on-write handles divergence.

SELECT SYSTEM$STREAM_HAS_DATA('my_stream');
-- Returns TRUE or FALSE

-- In a task:
CREATE TASK process WAREHOUSE = wh SCHEDULE = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('my_stream')
AS INSERT INTO target SELECT * FROM my_stream;

The WHEN clause prevents unnecessary warehouse starts.

Layer 1: Access Control: Establish a role hierarchy with RBAC, and absolutely NO ONE has ACCOUNTADMIN for daily duties! Create “functional” roles (i.e., data engineer, analyst, finance viewer) and assign the minimum necessary privileges to them.

CREATE ROLE finance_viewer;
GRANT USAGE ON DATABASE finance_db TO ROLE finance_viewer;
GRANT SELECT ON ALL TABLES IN SCHEMA finance_db.reports
  TO ROLE finance_viewer;

Layer 2: Data Protection: Implement Dynamic Data Masking for all PII columns. Add a Row Access Policy to restrict row access by region.

CREATE MASKING POLICY mask_email AS (val STRING)
  RETURNS STRING ->
  CASE WHEN CURRENT_ROLE() IN ('ADMIN') THEN val
    ELSE REGEXP_REPLACE(val, '.+@', '***@') END;

Layer 3: Classification: Add Object tags to the columns containing the sensitive data. Leverage Snowflake’s auto classification for PII identification.

ALTER TABLE customers MODIFY COLUMN ssn
  SET TAG pii_classification = 'SENSITIVE';

Layer 4: Monitoring: Monitor and audit access via Snowsight’s Trust Center to assess security posture and via Horizon to audit data lineage and movement. Configure alerts for unauthorized access attempts.

Layer 5: AI Governance: If using Cortex AI or agents, configure AI Guardrails to detect and block PII in AI-generated output, and enforce data access policies for AI agents like human users under the same RBAC.