Bird
Raised Fist0
SnowflakeConceptBeginner · 3 min read

What is Array in Snowflake: Definition and Usage

In Snowflake, an ARRAY is a data type that stores an ordered list of elements, which can be of any supported data type. It allows you to group multiple values into a single column, making it easy to handle collections of data within a table.
⚙️

How It Works

Think of an ARRAY in Snowflake like a row of mailboxes, where each mailbox holds a value and the order matters. You can store multiple values together in one place, and access them by their position, just like checking the first or second mailbox.

Under the hood, Snowflake stores arrays as a single column but keeps track of each element's position. This lets you easily add, remove, or query individual elements without needing separate columns for each value.

💻

Example

This example shows how to create a table with an ARRAY column, insert data, and query elements from the array.

sql
CREATE OR REPLACE TABLE fruits_table (
  id INT,
  fruits ARRAY
);

INSERT INTO fruits_table (id, fruits) VALUES
  (1, ARRAY_CONSTRUCT('apple', 'banana', 'cherry')),
  (2, ARRAY_CONSTRUCT('orange', 'grape'));

SELECT id, fruits, fruits[0] AS first_fruit FROM fruits_table;
Output
ID | FRUITS | FIRST_FRUIT ---|-----------------------------|------------ 1 | ["apple", "banana", "cherry"] | apple 2 | ["orange", "grape"] | orange
🎯

When to Use

Use ARRAY when you want to store multiple related values in one column without creating many separate columns. This is helpful for data like tags, lists of items, or multiple phone numbers.

For example, an e-commerce site might store a list of product colors or sizes in an array. It simplifies queries and keeps your table structure clean.

✅

Key Points

  • ARRAY stores ordered collections of values in one column.
  • Elements can be accessed by their index starting at 0.
  • Supports any Snowflake data type inside the array.
  • Useful for grouping related data without extra columns.
✅

Key Takeaways

An ARRAY in Snowflake holds an ordered list of values in a single column.
You can access array elements by their zero-based index.
Arrays help organize related data without adding many columns.
Use ARRAY for lists like tags, multiple contacts, or product options.