Unstructured Data in Snowflake: Directory Tables & REST API

Modern data pipelines often involve more than just structured tables. Images, PDFs, logs, and other unstructured data are critical for analytics, machine learning, and compliance. Snowflake extends its cloud data platform to handle unstructured data through directory tables and REST APIs. Directory tables allow you to query file metadata (name, size, last modified) using standard SQL, while the REST API enables programmatic file upload, download, and deletion. Combined with Snowflake's compute power, you can process files using Python UDFs, extract text from PDFs, resize images, or parse custom logs. This tutorial walks you through setting up directory tables, using the REST API, and processing files in production. You'll learn how to avoid common pitfalls like stale metadata and permission issues, and how to debug when things go wrong. By the end, you'll be able to integrate unstructured data into your Snowflake workflows seamlessly.

What Are Directory Tables?

Directory tables are Snowflake's way of exposing file metadata in external or internal stages as a queryable table. They provide columns like RELATIVE_PATH, FILE_SIZE, LAST_MODIFIED, and FILE_URL. To use a directory table, you must first enable it on a stage using the DIRECTORY = (ENABLE = TRUE) option. Once enabled, you can query it like any other table: SELECT * FROM DIRECTORY(@my_stage). Directory tables are especially useful for discovering files, filtering by name or date, and generating file URLs for downstream processing. However, they are not automatically updated; you must refresh them after adding or removing files. This is a common source of confusion in production.

-- Create an internal stage with directory table enabled
CREATE OR REPLACE STAGE my_unstructured_stage
  DIRECTORY = (ENABLE = TRUE)
  FILE_FORMAT = (TYPE = CSV);

-- Query the directory table
SELECT relative_path, file_size, last_modified
FROM DIRECTORY(@my_unstructured_stage)
WHERE file_size > 1000000
ORDER BY last_modified DESC;

Using the REST API for File Operations

Snowflake's REST API (SQL API) allows you to upload, download, and delete files in stages using HTTP requests. This is essential for integrating with external systems or automating file management. The endpoint is /api/v2/statements. To upload a file, you first issue a PUT request to the stage URL. For example, using curl: curl -X PUT -H 'Authorization: Bearer <token>' -F 'file=@data.csv' 'https://account.snowflakecomputing.com/api/v2/statements/content?stage=my_stage&path=data.csv'. Downloading uses GET. You can also list files via the directory table. The REST API requires a valid session token or OAuth token. For production, use key-pair authentication or OAuth for automation. Note that the API has rate limits and payload size limits (e.g., 128 MB per request).

# Upload a file using Snowflake REST API
curl -X PUT \
  -H 'Authorization: Bearer <your_token>' \
  -F 'file=@/path/to/local/file.csv' \
  'https://account.snowflakecomputing.com/api/v2/statements/content?stage=my_unstructured_stage&path=uploads/file.csv'

Processing Files with Python UDFs

Snowflake supports Python UDFs (user-defined functions) that can read files from stages using the IMPORTS clause. This allows you to process unstructured data directly in SQL. For example, you can write a UDF that reads a CSV file and returns the row count, or extracts text from a PDF. The UDF receives the file URL as input and can use libraries like pandas, PyPDF2, or PIL. To use a file, you must first create a stage and enable directory table. Then, create a Python UDF that imports the file via the @stage/path syntax. The UDF runs on Snowflake's compute, so be mindful of memory and time limits (e.g., 60 seconds default). You can also use Snowpark for more complex processing.

-- Create a Python UDF to count rows in a CSV file
CREATE OR REPLACE FUNCTION count_csv_rows(file_url STRING)
RETURNS INTEGER
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
HANDLER = 'count_rows'
AS
$$
import csv
import urllib.request

def count_rows(file_url):
    # file_url is like @my_stage/path/file.csv
    # Snowflake provides a way to read files via the stage
    # In practice, use the IMPORTS clause or SnowflakeFile
    # This is a simplified example
    response = urllib.request.urlopen(file_url)
    reader = csv.reader(response.read().decode('utf-8').splitlines())
    return sum(1 for row in reader) - 1  # exclude header
$$;

-- Use the UDF
SELECT count_csv_rows('@my_unstructured_stage/uploads/file.csv') AS row_count;

Best Practices for Directory Tables

1. Enable directory tables only when needed – they incur storage costs for metadata. 2. Refresh after every bulk file operation – use ALTER STAGE REFRESH. 3. Use file URLs wisely – they are time-limited and tied to the stage. 4. Partition by file path – if you have many files, consider using a naming convention that groups files by date or category. 5. Monitor directory table size – large directories can slow down queries. 6. Secure stages – use network policies and grants to control access. 7. Automate refreshes – use Snowflake tasks or external schedulers. 8. Leverage metadata columns – filter by LAST_MODIFIED to process only new files.

-- Create a task to refresh directory table every hour
CREATE OR REPLACE TASK refresh_directory_table
  WAREHOUSE = my_wh
  SCHEDULE = '60 MINUTE'
AS
  ALTER STAGE my_unstructured_stage REFRESH;

-- Start the task
ALTER TASK refresh_directory_table RESUME;

Handling File Formats and Large Files

Snowflake's directory tables and REST API support any file type, but processing large files (e.g., >100 MB) requires careful design. For CSV/JSON, consider using Snowflake's native file format support with COPY INTO. For binary files (images, PDFs), use Python UDFs with streaming. The REST API has a 128 MB limit per request; for larger files, use the PUT command in SnowSQL or Snowpipe. When processing many small files, batch them into a single UDF call to reduce overhead. Also, consider using external stages (S3, Azure) for cost savings and scalability.

-- Load CSV files from stage into a table
CREATE OR REPLACE TABLE raw_data (
  id INTEGER,
  name STRING,
  value FLOAT
);

COPY INTO raw_data
FROM @my_unstructured_stage
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1)
PATTERN = '.*/data/.*\.csv'
ON_ERROR = 'CONTINUE';

Security and Access Control

Access to directory tables and stages is controlled via Snowflake's RBAC. To query a directory table, a role needs USAGE on the stage and the stage's database/schema. To upload/download via REST API, the role needs WRITE/READ privileges. Additionally, stages can be secured with network policies and encryption. For sensitive data, use Snowflake's end-to-end encryption. Always use HTTPS for REST API calls. Rotate tokens regularly. Consider using OAuth for third-party integrations. Monitor access logs for unusual activity.

-- Grant necessary privileges to a role
GRANT USAGE ON DATABASE my_db TO ROLE analyst;
GRANT USAGE ON SCHEMA my_db.public TO ROLE analyst;
GRANT USAGE ON STAGE my_unstructured_stage TO ROLE analyst;
GRANT READ ON STAGE my_unstructured_stage TO ROLE analyst;

-- Verify grants
SHOW GRANTS TO ROLE analyst;