Handling Unstructured Data in Snowflake
Unstructured data includes files that do not have a predefined schema or data model, such as PDFs, images, audio, video, and raw log files.
Historically, data warehouses could only handle structured (rows/columns) or semi-structured (JSON/XML) data. Unstructured files had to be processed by external data lakes and external compute pipelines (like AWS Lambda).
Today, Snowflake provides native, end-to-end capabilities to store, manage, and extract value from unstructured data directly within its platform.
️ 1. Storing Unstructured Data (Stages)
You do not insert a raw PDF into a Snowflake VARCHAR column. Instead, unstructured files are stored in Stages.
Download the sample invoice
Use the attached invoice_123.pdf throughout this lesson. It is a fictional training invoice with the following extraction targets:
| Field | Sample value |
|---|---|
| Invoice Number | INV-123 |
| Vendor Name | Beems Technologies Pvt Ltd |
| Customer Name | Acme Analytics Pvt Ltd |
| Issue Date | 20 Jun 2026 |
| GST | INR 3,600.00 |
| Total Amount | INR 23,600.00 |
Download the PDF, save it as /tmp/invoice_123.pdf, and then upload it to Snowflake using SnowSQL or Snowsight.
- External Stages: You keep your files in your own cloud buckets (AWS S3, Google Cloud Storage, or Azure Blob), and Snowflake securely references them without copying them.
- Internal Stages: You upload the files directly into Snowflake’s managed internal storage.
-- Creating an internal stage for storing PDFs
CREATE STAGE INVOICE_STAGE
DIRECTORY = (ENABLE = TRUE);
-- Upload the downloaded sample via SnowSQL
PUT 'file:///tmp/invoice_123.pdf'
@INVOICE_STAGE
AUTO_COMPRESS = FALSE;
️ 2. File Metadata (Directory Tables)
How do you query millions of files? You enable a Directory Table on your stage.
A directory table provides a standard, tabular SQL view of all your unstructured files, allowing you to use SELECT statements to query file metadata.
-- Refresh the directory to pick up newly uploaded files
ALTER STAGE INVOICE_STAGE REFRESH;
-- Query the files like a normal table
SELECT
RELATIVE_PATH,
SIZE AS FILE_SIZE_BYTES,
LAST_MODIFIED
FROM DIRECTORY(@INVOICE_STAGE)
WHERE RELATIVE_PATH = 'invoice_123.pdf';
3. Secure File Access (Scoped URLs)
To view or download a file securely, Snowflake provides SQL functions to generate URLs. You can pass these URLs to front-end applications, BI tools, or Snowpark functions.
BUILD_SCOPED_FILE_URL: Creates a temporary, secure URL that is only valid for the current user's session.GET_PRESIGNED_URL: Creates a URL that can be shared externally for a set period of time (e.g., 3600 seconds).
SELECT
RELATIVE_PATH,
BUILD_SCOPED_FILE_URL(@INVOICE_STAGE, RELATIVE_PATH) AS SECURE_URL
FROM DIRECTORY(@INVOICE_STAGE)
WHERE RELATIVE_PATH = 'invoice_123.pdf';
4. Extracting Data with Snowpark Python
You no longer need external tools to parse PDFs or images. You can write a Snowpark Python User-Defined Function (UDF) directly inside Snowflake to read the file using standard Python libraries (like PyPDF2).
Snowflake provides a SnowflakeFile class that allows your Python code to open the staged file dynamically.
-- 1. Create a Python UDF to read text from a PDF
CREATE OR REPLACE FUNCTION extract_pdf_text(file_url STRING)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('snowflake-snowpark-python', 'pypdf2')
HANDLER = 'process_pdf'
AS
$$
import PyPDF2
from snowflake.snowpark.files import SnowflakeFile
def process_pdf(file_url):
# Open the file dynamically using its Snowflake URL
with SnowflakeFile.open(file_url, 'rb') as f:
reader = PyPDF2.PdfReader(f)
text = ""
for page in reader.pages:
text += page.extract_text()
return text
$$;
-- 2. Query the directory table and extract text on the fly!
SELECT
RELATIVE_PATH,
extract_pdf_text(BUILD_SCOPED_FILE_URL(@INVOICE_STAGE, RELATIVE_PATH)) AS PARSED_TEXT
FROM DIRECTORY(@INVOICE_STAGE)
WHERE RELATIVE_PATH = 'invoice_123.pdf';
5. Zero-Code Extraction with Document AI
If you don't want to write custom Python OCR scripts, Snowflake offers Document AI (powered by the Arctic-TILT multimodal LLM).
Document AI allows you to visually select what data points you want to extract from a PDF or image (e.g., "Vendor Name", "Total Amount"), and Snowflake generates a model specifically for your documents.
You can then query the model using SQL to pull structured columns out of raw images instantly.
-- Using a pre-trained Document AI model to extract invoice fields
SELECT
RELATIVE_PATH,
-- Extracting specific fields as defined in the sample
PREDICT_DOCUMENT(@MY_INVOICE_MODEL, BUILD_SCOPED_FILE_URL(@INVOICE_STAGE, RELATIVE_PATH)):"Invoice Number"::STRING AS INVOICE_NUMBER,
PREDICT_DOCUMENT(@MY_INVOICE_MODEL, BUILD_SCOPED_FILE_URL(@INVOICE_STAGE, RELATIVE_PATH)):"Vendor Name"::STRING AS VENDOR_NAME,
PREDICT_DOCUMENT(@MY_INVOICE_MODEL, BUILD_SCOPED_FILE_URL(@INVOICE_STAGE, RELATIVE_PATH)):"Issue Date"::DATE AS ISSUE_DATE,
PREDICT_DOCUMENT(@MY_INVOICE_MODEL, BUILD_SCOPED_FILE_URL(@INVOICE_STAGE, RELATIVE_PATH)):"Total Amount"::FLOAT AS TOTAL_AMOUNT
FROM DIRECTORY(@INVOICE_STAGE)
WHERE RELATIVE_PATH = 'invoice_123.pdf';
6. Generative AI with Snowflake Cortex
Once you have extracted the raw text from your unstructured files (using Snowpark or Document AI), you can feed that text directly into Snowflake Cortex to run Large Language Models natively in SQL.
-- Summarize the text extracted from the PDF using Llama 3
SELECT
RELATIVE_PATH,
SNOWFLAKE.CORTEX.SUMMARIZE(
extract_pdf_text(BUILD_SCOPED_FILE_URL(@INVOICE_STAGE, RELATIVE_PATH))
) AS DOCUMENT_SUMMARY
FROM DIRECTORY(@INVOICE_STAGE)
WHERE RELATIVE_PATH = 'invoice_123.pdf';
Summary
Snowflake provides a complete ecosystem for unstructured data:
- Store it in Stages.
- Track it with Directory Tables.
- Access it with Scoped URLs.
- Process it with Snowpark Python.
- Extract it with Document AI.
- Analyze it with Snowflake Cortex.