SNOWFLAKE Interview Questions 2025

 

1️⃣ Choosing the Perfect Size of Virtual Warehouse

To select the right size (X-Small to 6X-Large), consider:

• Data Volume & Complexity: Larger datasets or complex joins/aggregations may require Medium or Large warehouses.
• Concurrency Needs: More users or parallel queries? Scale up or use multi-cluster warehouses.
• Performance SLAs: If low latency is critical, opt for larger sizes or auto-scaling.
• Cost vs. Speed Trade-off: Start small, monitor query performance via Query Profile, and scale only if needed.

🧠 Tip: Use Query History and Warehouse Load Charts to analyze CPU usage and queue times before resizing.

2️⃣ Optimizing Storage & Compute Cost in Snowflake

Storage Optimization:

• Use data compression (Snowflake does this automatically).
• Archive unused data to lower-cost storage tiers.
• Drop unused tables/stages and purge Fail-safe data when possible.

Compute Optimization:

• Use auto-suspend and auto-resume for warehouses.
• Schedule jobs during off-peak hours.
• Use result caching and materialized views for repetitive queries.
• Avoid over-provisioning; monitor warehouse usage and scale down if underutilized.

3️⃣ Checklist for On-Prem to Snowflake Migration

• ✅ Source System Analysis: Understand schema, data types, volume, and dependencies.
• ✅ Data Quality Checks: Nulls, duplicates, referential integrity.
• ✅ Transformation Logic: Map ETL logic to Snowflake-compatible SQL or ELT.
• ✅ Security & Compliance: Identify PII, encryption needs, access controls.
• ✅ Performance Benchmarking: Compare query performance pre/post migration.
• ✅ Tooling: Choose between ADF, Informatica, or custom scripts for ingestion.
• ✅ Validation Strategy: Row counts, checksums, sample data comparison.

 

 

 

 

 

4️⃣ Clustering vs. Search Optimization Service

 

Feature	Clustering	Search Optimization
Purpose	Improve query performance on large tables	Accelerate point lookup queries
Use Case	Range scans, filtering on clustered columns	Fast retrieval on high-cardinality columns
Maintenance	Manual or automatic clustering	Fully managed by Snowflake
Cost	Storage overhead due to clustering metadata	Additional cost for optimization service

 

 

🧠 Use Clustering for large fact tables with predictable filters. Use Search Optimization for fast lookups on semi-structured or sparse data.

5️⃣ Query Performance Optimization Techniques

• 📌 Use Query Profile to identify bottlenecks.
• 📌 Avoid SELECT *; project only needed columns.
• 📌 Use materialized views for expensive aggregations.
• 📌 Partition large tables using clustering keys.
• 📌 Leverage result caching and CTAS for intermediate steps.
• 📌 Rewrite subqueries as joins or CTEs for better optimization.
• 📌 Ensure proper data types and avoid implicit conversions.

6️⃣ Query to Check Table Usage in Views

sql

SELECT table_name, view_name

FROM information_schema.view_table_usage

WHERE table_name = 'YOUR_TABLE_NAME';

This checks if the table is referenced in any view definitions within the current database.

7️⃣ Implementing SCD Type 2 in Snowflake

Use a combination of MERGE and metadata columns:

sql

MERGE INTO target_table AS tgt

USING staging_table AS src

ON tgt.business_key = src.business_key AND tgt.is_current = TRUE

WHEN MATCHED AND tgt.hash_diff != src.hash_diff THEN

  UPDATE SET tgt.is_current = FALSE, tgt.end_date = CURRENT_DATE

WHEN NOT MATCHED THEN

  INSERT (business_key, attribute1, attribute2, start_date, end_date, is_current)

  VALUES (src.business_key, src.attribute1, src.attribute2, CURRENT_DATE, NULL, TRUE);

• is_current: Flag for active record
• start_date, end_date: Track validity
• hash_diff: Detect changes efficiently

8️⃣ COPY from Date-wise Folder in S3

Assuming folder structure like s3://bucket/yyyy-mm-dd/file.csv:

sql

DECLARE current_date STRING;

SET current_date = TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD');

 

COPY INTO my_table

FROM @my_s3_stage/$(current_date)/file.csv

FILE_FORMAT = (TYPE = 'CSV');

You can also use external tables or ADF with dynamic path resolution.

9️⃣ Create Table from Parquet File in S3 (Without Stage Browsing)

sql

CREATE OR REPLACE TABLE parquet_table

USING TEMPLATE (

  SELECT *

  FROM TABLE(

    INFER_SCHEMA(

      LOCATION => '@my_s3_stage/path/to/file.parquet',

      FILE_FORMAT => 'PARQUET'

    )

  )

);

This uses INFER_SCHEMA to auto-generate the table structure.

🔟 Identifying PII/Sensitive Data

• 📌 Use Snowflake's TAGS and CLASSIFY function (if enabled).
• 📌 Scan column names for patterns like email, ssn, dob, credit_card.
• 📌 Use data profiling tools (e.g., Great Expectations, Collibra).
• 📌 Implement column-level masking policies.
• 📌 Maintain a PII inventory and enforce RBAC.

 

 

 

Basic Questions

1. What is Snowflake, and how does it differ from traditional databases? Snowflake is a cloud-native data platform that separates compute, storage, and services. Unlike traditional databases, it offers scalability, elasticity, and native support for semi-structured data without infrastructure management.
2. What are the key features of Snowflake?
• Separation of compute/storage/services
• Time Travel & Fail-safe
• Native support for JSON, Avro, Parquet
• Secure data sharing
• Multi-cloud support
• Auto-scaling virtual warehouses
3. How does Snowflake handle data storage? Data is stored in compressed, columnar micro-partitions. Storage is centralized and decoupled from compute.
4. What is Snowflake’s architecture? Three layers:
• Storage: Centralized, compressed micro-partitions
• Compute: Virtual warehouses (MPP clusters)
• Services: Metadata, optimization, security
5. What are Snowflake’s advantages over other cloud data warehouses?
• True separation of compute and storage
• Native semi-structured data support
• Zero management overhead
• Cross-cloud replication and sharing
6. What is the role of virtual warehouses in Snowflake? They provide isolated compute resources for queries and ETL. Can be scaled and run concurrently.
7. How does Snowflake support multi-cloud environments? Snowflake runs on AWS, Azure, and GCP, allowing cross-cloud replication and failover.
8. What is Snowflake’s pricing model? Pay-per-use model based on compute credits and storage. Editions include Standard, Enterprise, Business Critical.
9. What types of workloads can Snowflake handle?
• Data warehousing
• Data lake
• Real-time analytics
• Machine learning
• Data sharing
10. What is Snowflake’s approach to security?
• End-to-end encryption
• Role-based access control
• Network policies
• Data masking and governance
11. How does Snowflake handle structured and semi-structured data? Supports VARIANT data type for JSON, Avro, Parquet. Can query and transform using SQL.
12. What is Snowflake’s marketplace? A platform to discover and share datasets, applications, and services across Snowflake accounts.
13. What is Snowflake’s data sharing feature? Enables secure, real-time sharing of data across accounts and clouds without data movement.
14. How does Snowflake ensure high availability? Built-in redundancy across availability zones. Automatic failover and replication.
15. What is Snowflake’s caching mechanism? Results caching, metadata caching, and warehouse-level caching improve performance.
16. How does Snowflake handle concurrency? Multi-cluster warehouses scale automatically to handle concurrent users.
17. What is Snowflake’s role in data analytics? Acts as a central platform for BI, ML, and predictive analytics with high performance and scalability.
18. What are Snowflake’s supported programming languages? SQL, Python (via Snowpark), Java, Scala, JavaScript (for stored procedures)
19. How does Snowflake integrate with third-party tools? Native connectors for Power BI, Tableau, Informatica, dbt, Airflow, and more.
20. What is Snowflake’s approach to disaster recovery? Cross-region replication, failover groups, and Time Travel ensure resilience.

 

 

 

Technical Questions

21. What is Snowflake Time Travel, and how does it work? Allows querying historical data (up to 90 days). Useful for recovery, auditing, and debugging.
22. How does Snowflake handle schema changes? Supports dynamic schema evolution and zero-downtime DDL operations.
23. What is Snowflake’s clustering mechanism? Clustering keys define logical sort order to improve pruning and query performance.
24. How does Snowflake optimize query performance?
• Pruning micro-partitions
• Result caching
• Materialized views
• Query profiling and tuning
25. What is Snowflake’s micro-partitioning feature? Data is automatically divided into micro-partitions (~16MB) for efficient scanning and pruning.
26. How does Snowflake handle data compression? Columnar storage with automatic compression reduces storage and improves performance.
27. What is Snowflake’s role in ETL processes? Supports ELT workflows with SQL, Snowpark, and integration with ETL tools.
28. How does Snowflake handle JSON and XML data? VARIANT data type allows storage and querying of semi-structured formats using SQL functions.
29. What is Snowflake’s COPY command? Loads data from external stages (S3, Azure Blob, GCS) into Snowflake tables.
30. How does Snowflake handle data ingestion?
• Batch: COPY command
• Streaming: Snowpipe
• CDC: Streams and Tasks
31. What is Snowflake’s fail-safe mechanism? Provides 7-day recovery window after Time Travel expires for disaster recovery.
32. How does Snowflake handle role-based access control? Roles are assigned privileges; users inherit access via roles. Supports role hierarchy.
33. What is Snowflake’s approach to indexing? No traditional indexes. Uses metadata and clustering for performance.
34. How does Snowflake handle workload isolation? Separate virtual warehouses ensure isolated compute for different workloads.
35. What is Snowflake’s approach to metadata management? Metadata is stored in the services layer and used for optimization, governance, and lineage.
36. How does Snowflake handle stored procedures? Supports JavaScript and SQL-based stored procedures with control flow logic.
37. What is Snowflake’s approach to query optimization? Uses cost-based optimizer, pruning, caching, and statistics for efficient execution.
38. How does Snowflake handle materialized views? Stores precomputed results for faster querying. Automatically refreshed.
39. What is Snowflake’s approach to data governance? Tags, masking policies, access history, and RBAC support compliance and control.
40. How does Snowflake handle replication across regions? Replication groups and failover groups enable cross-region data availability.

 

ADF functions

 toString(JT_DEAL_PLAN_EFF_DT, 'MM/dd/yyyy HH:mm:ss','es-ES')

SQLSERVER and ORACLE IMPORTANT Queries

 SQLSERVER and ORACLE IMPORTANT 

EXEC sp_columns 'dbo.tablename';

EXEC sp_help 'dbo.tablename';

SELECT column_name FROM information_schema.columns WHERE table_name = 'tablename' AND table_schema = 'ETL'

SELECT DB_NAME() AS CurrentDatabaseName;

SELECT name AS CurrentDatabaseName

FROM sys.databases

WHERE database_id = DB_ID();

oracle:-

SELECT DBMS_METADATA.get_ddl ('TABLE', 'tablename','HR') from dual;

Data engineering Interview Questions

1) What all challenges you have faced and how did you overcome from it?

Ans:-

Challenges Faced and Overcome

As a hypothetical Spark developer, I'll share some common challenges I faced while working on a project and how I overcame them:

Challenge 1: Data Ingestion Issues

• Problem: Difficulty ingesting large amounts of data from various sources, resulting in data corruption and inconsistencies.
• Solution: I implemented a data ingestion pipeline using Apache NiFi, which handled data validation, transformation, and loading into the Spark cluster. I also used Apache Spark's built-in data ingestion APIs to handle data corruption and inconsistencies.

Challenge 2: Performance Optimization

• Problem: Slow query performance due to inefficient data processing and resource utilization.
• Solution: I optimized Spark configurations, such as increasing the number of executors, adjusting the executor memory, and using caching to improve query performance. I also used Spark's built-in optimization techniques, like broadcast joins and predicate pushdown.

Challenge 3: Data Skew

• Problem: Data skew caused by uneven data distribution, leading to performance issues and node crashes.
• Solution: I implemented data partitioning and re-partitioning techniques to ensure even data distribution across nodes. I also used Spark's built-in data skew mitigation techniques, such as salting and bucketing.

Challenge 4: Security and Authentication

• Problem: Ensuring secure data access and authentication for users and applications.
• Solution: I implemented Kerberos authentication and authorization using Apache Knox, and enabled SSL/TLS encryption for secure data transmission. I also used Spark's built-in security features, such as secure data storage and access control.

Challenge 5: Monitoring and Debugging

• Problem: Difficulty monitoring and debugging Spark jobs, leading to extended troubleshooting times.
• Solution: I implemented Spark's built-in monitoring tools, such as the Spark UI and Spark History Server. I also used third-party tools, like Prometheus and Grafana, for more comprehensive monitoring and alerting.

Challenge 6: Integration with Other Tools

• Problem: Integrating Spark with other tools and systems, such as data warehouses and machine learning frameworks.
• Solution: I used Spark's built-in APIs and connectors to integrate with other tools and systems. I also implemented custom APIs and data pipelines to handle data exchange and processing.

Challenge 7: Scalability and High Availability

• Problem: Ensuring Spark cluster scalability and high availability to handle large workloads and user demands.
• Solution: I implemented a highly available Spark cluster using Apache Mesos and ZooKeeper. I also configured Spark's built-in high availability features, such as standby masters and worker nodes.

Challenge 8: Data Quality and Data Governance

• Problem: Ensuring data quality and governance across the Spark ecosystem.
• Solution: I implemented data quality checks and validation using Apache Spark's built-in APIs and libraries. I also established data governance policies and procedures to ensure data consistency and compliance.

These are just a few examples of challenges I faced and overcame while working on a Spark project. By implementing these solutions, I was able to ensure a successful and efficient Spark deployment.

2)Have you done spark optimization tuning? If yes, how you have done that?

Spark optimization tuning! Yes, I have been trained on various techniques to optimize Apache Spark performance. While I don't have personal experiences, I can share some common methods and best practices for Spark optimization tuning. Here are some ways to optimize Spark performance:

1. Data Serialization:
* Use Kryo serialization instead of Java serialization for faster serialization and deserialization.
* Register custom Kryo serializers for complex data types.

2. Data Caching:
* Cache frequently used data in memory using cache() or persist() methods.
* Use MEMORY_AND_DISK storage level for caching data that doesn't fit in memory.

3. Data Partitioning:
* Optimize data partitioning to reduce data skew and improve parallelism.
* Use repartition() or coalesce() to adjust the number of partitions.

4. Joins and Aggregations:
* Use broadcast joins for small tables to reduce data transfer.
* Use sort-merge joins for large tables to reduce memory usage.
* Use reduceByKey() or aggregateByKey() instead of groupByKey() for aggregations.

5. Memory Tuning:
* Adjust spark.executor.memory and spark.driver.memory to optimize memory usage.
* Set spark.memory.fraction to control the amount of memory used for caching.

6. Shuffle Tuning:
* Adjust spark.shuffle.compress to compress shuffle data.
* Set spark.shuffle.spill.compress to compress spilled data.

7. Parallelism:
* Adjust spark.default.parallelism to control the number of parallel tasks.
* Use repartition() to adjust the number of partitions.

8. Garbage Collection:
* Adjust spark.executor.extraJavaOptions to optimize GC settings.
* Use G1 garbage collector for better performance.

9. Data Storage:
* Use Parquet or ORC file formats for efficient data storage.
* Use HDFS or S3 for distributed storage.

10. Monitoring and Debugging:

• Use Spark UI to monitor job performance and identify bottlenecks.
• Use explain() to analyze query plans and optimize them.

11. Spark Configuration:

• Adjust spark.conf settings to optimize performance.
• Use spark-submit options to override configuration settings.

12. Code Optimization:

• Optimize Spark code to reduce unnecessary computations and data transfers.
• Use mapPartitions() instead of map() for better performance.

These are just some of the ways to optimize Spark performance. The best approach depends on the specific use case and dataset. Do you have a specific Spark optimization challenge you'd like help with?

3) Optimization technique?

Here are some common optimization techniques used in PySpark and ADF (Azure Data Factory) jobs:

PySpark:

1. Caching: Use cache() or persist() to store intermediate results in memory for faster access.
2. Broadcasting: Use broadcast() to send small datasets to all nodes for faster joins and aggregations.
3. Data partitioning: Use repartition() or coalesce() to optimize data distribution across nodes.
4. Data serialization: Use Kryo or Java serialization to reduce data serialization overhead.
5. Optimize joins: Use broadcast joins or shuffle joins depending on data size and distribution.

ADF (Azure Data Factory):

1. Pipeline optimization: Optimize pipeline execution by reducing the number of activities, using parallel execution, and minimizing dependencies.
2. Data movement optimization: Use  PolyBase or Azure Data Factory's bulk copy to optimize data movement between sources and sinks.
3. Data transformation optimization: Use Azure Data Factory's data transformation activities like Data Flow or Mapping Data Flow to optimize data transformation and processing.
4. Caching: Use cache or output caching to store intermediate results for faster access.
5. Azure Storage optimization: Optimize Azure Storage usage by using hot/cold storage and  compression to reduce costs and improve performance.

Common to both:

1. Data sampling: Use data sampling to reduce dataset size and improve processing performance.
2. Data aggregation: Use data aggregation to reduce data volume and improve processing performance.
3. Optimize SQL queries: Optimize SQL queries to reduce execution time and improve performance.

These are just a few examples of optimization techniques used in PySpark and ADF jobs. The specific techniques used depend on the project requirements, data characteristics, and performance bottlenecks.

4)Can you please walk me through the spark-submit command?

The spark-submit command is used to launch Spark applications on a cluster. Here's a breakdown of the command and its options:

Basic Syntax

spark-submit [options] <app jar | python file> [app arguments]

Examples

1. Submit a Java/Scala application

spark-submit --class com.example.MyApp --master spark://host:port myapp.jar
		

2. Submit a Python application

spark-submit --master spark://host:port myapp.py
		

3. Submit an application with configuration options

spark-submit --class com.example.MyApp --master spark://host:port --conf spark.executor.memory=4g --conf spark.driver.memory=2g myapp.jar
		

5)Lets say you are getting your data volume is 100 GB, In your spark you are doing 5 Actions and 3 transformations on the data, explain what goes behind the scene with respect to Stages, tasks?

Your Spark Job

You have a Spark job that reads 100 GB of data, performs 3 transformations (filter, groupby, and sort), and then performs 5 actions (write to Parquet, count, show, and write to CSV).

Stages

Spark breaks down your job into smaller units of work called Stages. Each stage is a set of tasks that can be executed independently.

Stages in Your Job

1. Read Data (Stage 1)
2. Filter Data (Stage 2)
3. GroupBy and Aggregate Data (Stage 3)
4. Sort Data (Stage 4)
5. Write Data (Stage 5)
6. Count (Stage 6)
7. Show (Stage 7)
8. Write CSV (Stage 8)

Tasks

Each stage is further broken down into smaller units of work called Tasks. Tasks are executed in parallel across multiple machines.

Tasks in Your Job

• Each stage has approximately 800 tasks (100 GB / 128 MB per task)
• Total tasks: 8 stages x 800 tasks per stage = 6400 tasks

Execution

1. Each task executes on a separate block of data.
2. Tasks are executed in parallel across multiple machines.
3. Results are returned to the Spark driver.

Think of it like a factory assembly line:

• Each stage is like a workstation that performs a specific task.
• Each task is like a worker at the workstation that processes a small part of the data.
• The Spark driver is like the factory manager that coordinates the work and collects the final results.

ADF:-

Explain the differences between Mapping Data Flows and Wrangling Data Flows in ADF.

In Azure Data Factory (ADF), there are two types of data flows: Mapping Data Flows and Wrangling Data Flows. While both are used for data transformation and preparation, they serve different purposes and have distinct characteristics.

Mapping Data Flows

Mapping Data Flows are a visual, code-free way to transform and map data from source to target. They are designed for complex data transformation and data integration scenarios, where you need to perform multiple transformations, aggregations, and joins on large datasets. Mapping Data Flows are built on top of Spark and provide a scalable, high-performance data transformation engine.

Key characteristics of Mapping Data Flows:

• Visual interface: Drag-and-drop interface for creating data transformations
• Complex transformations: Support for multiple transformations, aggregations, and joins
• Scalable: Built on Spark, suitable for large-scale data processing
• Code-free: No coding required, but can be extended with custom code (e.g., Python, Scala)

Wrangling Data Flows

Wrangling Data Flows, on the other hand, are designed for data preparation, data quality, and data standardization tasks. They are a visual, interactive way to explore, transform, and prepare data for analysis or reporting. Wrangling Data Flows are built on top of a proprietary engine and are optimized for smaller-scale data processing.

Key characteristics of Wrangling Data Flows:

• Visual interface: Interactive, web-based interface for data exploration and transformation
• Data preparation: Focus on data quality, standardization, and data profiling
• Interactive: Real-time data preview and transformation feedback
• Small-scale: Optimized for smaller datasets, not suitable for large-scale data processing

Key differences

1. Purpose: Mapping Data Flows are designed for complex data transformation and integration, while Wrangling Data Flows focus on data preparation and standardization.
2. Scalability: Mapping Data Flows are built on Spark and are more scalable, while Wrangling Data Flows are optimized for smaller datasets.
3. Complexity: Mapping Data Flows support more complex transformations and aggregations, while Wrangling Data Flows are more geared towards simple, interactive data transformations.
4. Code requirements: Both offer code-free experiences, but Mapping Data Flows can be extended with custom code, while Wrangling Data Flows do not support custom code.

In summary, if you need to perform complex data transformations, aggregations, or joins on large datasets, use a Mapping Data Flow. If you need to prepare, standardize, or profile smaller datasets for analysis or reporting, use a Wrangling Data Flow.

Tools(0)

To implement incremental load patterns using Azure Data Factory (ADF), follow these steps:

1. Create a watermark: Define a watermark column in your source data to track the last loaded data.
2. Configure ADF pipeline: Create a pipeline with a copy activity that loads data from the source to the target.
3. Use query parameter: Use a query parameter to filter data based on the watermark column.
4. Incremental load: Use the watermark value to load only new or updated data.
5. Update watermark: Update the watermark value after loading the data.

To implement dynamic pipeline execution in ADF, you can use:

• Parameters: Pass pipeline parameters from a parent pipeline to a child pipeline
• Variables: Use variables to store dynamic values and reference them in pipeline activities
• Expression Builder: Use conditional logic and functions to dynamically set pipeline properties
• Lookup Activities: Retrieve dynamic values from external sources, such as databases or APIs
• Trigger Parameters: Pass dynamic values from triggers to pipelines