DEV Community: Chamindu Perera

Snowflake SQL Interview Questions – Quick Notes

Chamindu Perera — Tue, 07 Jul 2026 15:04:54 +0000

SNOWFLAKE SQL INTERVIEW QUESTIONS – QUICK NOTES

What is Snowflake? Snowflake is a cloud-based data warehouse that separates compute and storage, allowing both to scale independently.

Key Features:
• Cloud-native architecture
• Separation of Compute & Storage
• Virtual Warehouses
• Time Travel
• Zero-Copy Cloning
• Secure Data Sharing
• Automatic Scaling

Explain Snowflake Architecture.

Snowflake has three layers:

Database Storage Layer
- Stores compressed data.
Compute Layer
- Virtual Warehouses execute SQL queries.
Cloud Services Layer
- Handles authentication, metadata, query optimization, and security.

What is a Virtual Warehouse?

A Virtual Warehouse is a compute cluster that executes SQL queries. Multiple warehouses can access the same data simultaneously.

Difference between CTE and Subquery

CTE

Easier to read
Can be reused
Supports recursive queries

Subquery

Less readable for complex queries
Usually used once
Cannot be reused easily

Types of Joins

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
• CROSS JOIN
• SELF JOIN

Window Functions

Common functions:
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LAG()
• LEAD()
• SUM() OVER()
• AVG() OVER()

Example:

SELECT employee_id,
salary,
ROW_NUMBER() OVER(ORDER BY salary DESC) AS rn
FROM employees;

QUALIFY Clause (Snowflake Specific)

Filters rows after applying window functions.

Example:

SELECT *
FROM employees
QUALIFY ROW_NUMBER() OVER
(PARTITION BY department ORDER BY salary DESC)=1;

Difference between ROW_NUMBER(), RANK(), and DENSE_RANK()

ROW_NUMBER()

Assigns unique numbers.
No duplicates.

RANK()

Same rank for ties.
Skips rank numbers.

DENSE_RANK()

Same rank for ties.
Does not skip numbers.

Find Second Highest Salary

SELECT salary
FROM employees
QUALIFY DENSE_RANK() OVER(ORDER BY salary DESC)=2;

What is Time Travel?

Allows recovery of deleted or modified data within the configured retention period.

What is Zero Copy Cloning?

Creates an instant copy of tables, schemas, or databases without duplicating physical storage.

Temporary vs Transient vs Permanent Tables

Temporary Table

Exists only for the current session.

Transient Table

No Fail-safe.
Lower storage cost.

Permanent Table

Supports Time Travel and Fail-safe.

Common SQL Interview Questions
Find duplicate records.
Remove duplicate records.
Find the second highest salary.
Find the top 3 salaries in each department.
Calculate running totals.
Calculate rolling averages.
Find employees earning above department average.
Get the latest record for each customer.
Count customers by month.
Find missing dates.

Performance Tips

• Avoid SELECT *.
• Filter data as early as possible.
• Use appropriate warehouse size.
• Use clustering keys when necessary.
• Use QUALIFY instead of nested subqueries where applicable.

Frequently Asked Snowflake Questions

• What are Micro-partitions?
• Explain Snowpipe.
• What are Streams and Tasks?
• What is Fail-safe?
• Difference between COPY INTO and Snowpipe.
• What is VARIANT data type?
• Explain FLATTEN().
• Explain RBAC in Snowflake.
• How do you optimize slow queries?
• What is Query Profile?

Interview Tips

✔ Master Joins, CTEs, Window Functions, and QUALIFY.

✔ Practice writing SQL without an editor.

✔ Learn Snowflake-specific features:

Time Travel
Zero-Copy Cloning
Streams
Tasks
Snowpipe
VARIANT
FLATTEN
Virtual Warehouses

✔ Explain your SQL logic clearly during the interview.

KNIME Analytics Platform for Data Science-1

Chamindu Perera — Sat, 02 Sep 2023 19:44:08 +0000

KNIME

KNIME Analytics is a Java-based data science tool. It is simpler to construct data mining applications with this software since it supports visual programming in the form of a workflow with numerous nodes, which eliminates the need for highly developed coding abilities, but also allows if you need it. It has a huge and varied plugin center and is commonly used in academic settings. It is an extensible data science platform that supports user-created scripts and codes in addition to visual programming. KNIME is a cross-platform software package that may be set up on several operating systems. Early versions of KNIME works with JAVA 8 but Updated KNIME requires JAVA 11 and higher versions. So, you can install one of the openjdk-11 or openjdk-latest packages before install the KNIME platform. After that You can start surfing with start downloading KNIME by this Link:)

KNIME Workbench

The main tab in the KNIME interface is the Workbench. This Workbench is the place where you will be building your workflows. It is also where you'll find all the resources you need to help you build your workflows.

Other Tabs are,

KNIME Explorer
Explorer is what you use to manage your workflows, workflow groups, and server connections and also you can access example workflows that have been built to demonstrate different functionalities in KNIME.
Workflow Coach
This tab will recommend you the node that you can use as the next step after where you are now. These node recommendations are based on KNIME community usage statistics. You can add nodes from the workflow coach to the editor by dragging and dropping them, or by double clicking.
Node repository
Currently installed nodes are available from the node repository. You build your workflow by dragging or double clicking nodes from the node repository to the workflow editor as in the workflow coach. There are two types of searches in the repository as crisp search and fuzzy search. The crisp search is the default mode and returns all nodes that either have the search term in the name or that are in a subcategory whose name includes the search term. The fuzzy search returns all nodes that are related to the search term.
Description
This gives you information about the currently active workflow, or about an individual node selected either in the node repository or the workflow editor and tells you the purpose of the workflow, what it does, what you might need to run it plus links to blog articles, for example, or other web pages related to the workflow.
Console
The console is where any warnings and error messages are shown that relate to your workflow, indicating what is going on under the hood.

Nodes, Data & Workflows

The following image illustrates a node and explains the different parts of a node.

Data access

The start of any project is to access the required data. So, refer below different nodes which we can access data in KNIME. You can access and blend data from different local end remote file systems: CSV files and other formatted text files, Excel workbooks, proprietary file formats of other tools, and more.

Reader Nodes

The common file types for which there are dedicated nodes are CSV, Tabular, and Excel files. For a more exhaustive list and description of all KNIME nodes for data access, download the free e-book "Will they blend?", a collection of blog posts centered around data access & blending.

Related Nodes:

1.CSV Reader
2.Table Reader
3.Excel Reader

Accessing Databases

In KNIME, you can connect to any JDBC compliant database and manipulate data directly on the database. At any point, you can read the data into a local KNIME data table, and vice versa.

Related Nodes:

1.DB Connector
2.DB Table Selector
3.DB Reader

Data Cleaning

After accessed the data from one or more sources, you need to clean and transform it. One purpose of these steps is to reduce the irrelevant and retain the essential information. The possible operations include row and column filtering and transformations on numbers, strings, and rules.

Another purpose of cleaning data is to reduce bias and enhance clarity in the next steps, such as model training, statistics calculation, and creating dashboards. Missing value handling and outlier detection are helpful operations there.

Row & Column Filtering

A good start to transforming data is to exclude unnecessary information. Rows can be removed, for example, if they are duplicates, have missing values, or if they just don't belong to the subset of interest.

Related Nodes:

1.Row Filter
2.Column Filter

That's it for the Basic introduction and all data accessing, cleaning parts in KNIME for beginners and keep in touch for upcoming Data Standardization part with numbers, Strings and Rules.

Thank you!