Querying Data Without Loading (SELECT from Stage)
In the previous lesson, we learned how to create External Tables over data sitting in an S3 Data Lake.
But what if you don't even want to go through the trouble of creating a table definition? What if a Data Scientist just dropped a random Parquet file into an AWS S3 bucket, and you want to look at the first 10 rows right now to see what's inside it?
Snowflake provides a powerful feature: Querying a Stage directly.
Topics Covered
- The
SELECT FROM @STAGEPattern - Querying JSON and Parquet
- Filtering Files using Pattern Matching
- When to Query In-Place vs Load
1. The SELECT FROM @STAGE Pattern
Normally, an External Stage is used as the source for a COPY INTO command. However, you can write standard SQL SELECT queries directly against the stage.
Let's assume our Data Science team in Bengaluru has placed some CSV files into an external stage.
(To query an external stage, you must have USAGE privilege on it. Check your role with SELECT CURRENT_ROLE();)
USE ROLE SYSADMIN;
-- 1. Create the Stage pointing to the S3 bucket
CREATE OR REPLACE STAGE BEEMS_DS_STAGE
URL='s3://beems-data-science/experiments/'
STORAGE_INTEGRATION = AWS_S3_INT;
-- 2. List the files in the stage to see what's there
LIST @BEEMS_DS_STAGE;
-- Output might show: experiment_results.csv
-- 3. Query the stage directly!
SELECT
$1 AS USER_ID,
$2 AS MODEL_SCORE,
$3 AS PREDICTION_DATE
FROM @BEEMS_DS_STAGE/experiment_results.csv
(FILE_FORMAT => 'MY_CSV_FORMAT');
When querying a CSV stage, you refer to columns by their ordinal position: $1 for the first column, $2 for the second column, etc.
2. Querying JSON and Parquet Directly
When querying semi-structured data like JSON or Parquet, the entire record is loaded into the first column ($1) as a VARIANT object. You use Snowflake's colon : notation to extract the fields you want.
-- Assume there is a file called 'sensor_data.json' in the stage
SELECT
$1:sensor_id::STRING AS SENSOR_ID,
$1:location::STRING AS CITY,
$1:temperature::FLOAT AS TEMP_C
FROM @BEEMS_DS_STAGE/sensor_data.json
(FILE_FORMAT => 'MY_JSON_FORMAT')
WHERE $1:location::STRING = 'Bengaluru';
Notice that we can even apply a WHERE clause directly against the raw JSON file sitting in AWS S3!
3. Pattern Matching (Scanning Multiple Files)
If your S3 bucket contains thousands of files, you can use Regular Expressions (Regex) in the PATTERN clause to query a specific subset of files without moving them.
-- Query all Parquet files that begin with 'sales_2023_'
SELECT
$1:transaction_id::STRING,
$1:amount::NUMBER(10,2)
FROM @BEEMS_DS_STAGE
(FILE_FORMAT => 'MY_PARQUET_FORMAT', PATTERN => '.*sales_2023_.*\.parquet');
4. Metadata Columns
When querying a stage directly, Snowflake provides two special metadata columns:
METADATA$FILENAME: The name of the file currently being read.METADATA$FILE_ROW_NUMBER: The row number within that specific file.
These are incredibly useful for auditing and debugging data science experiments.
SELECT
METADATA$FILENAME,
METADATA$FILE_ROW_NUMBER,
$1:model_score::FLOAT
FROM @BEEMS_DS_STAGE
(PATTERN => '.*model_run_v2.*');
5. When to Query In-Place vs Load
Querying a stage directly is amazing for Exploratory Data Analysis (EDA). It allows Data Engineers to quickly inspect file schemas and Data Scientists to run ad-hoc queries without asking a DBA to create tables.
However, do NOT use Stage Queries for Production BI Dashboards.
| Operation | Querying Stage In-Place | Loading into Snowflake Table |
|---|---|---|
| Setup Time | Instant (Zero DDL) | Slower (Must define table & COPY INTO) |
| Storage Cost | $0 (Stored in S3) | Paid to Snowflake |
| Performance | Slow (Full file scans, Network latency) | Extremely Fast (Micro-partitions) |
| Use Case | Ad-hoc EDA, Debugging, Schema inspection | BI Dashboards, Daily Reporting, Data Apps |
If you find yourself running the exact same SELECT FROM @STAGE query every day, you should either load the data into a standard table using COPY INTO, or create an External Table over it to formalize the schema.
Summary
- You can execute
SELECTqueries directly against an External Stage (@stage). - For CSVs, reference columns using
$1, $2. - For JSON/Parquet, parse the
$1variant object. - Use
PATTERNto scan multiple files using regex. - Use the
METADATA$FILENAMEcolumn for tracking source files. - Excellent for exploratory data analysis, but poor performance for production workloads.