Skip to main content

Capstone: Document Processing Pipeline

Project Overview​

Build an automated pipeline that processes invoices and contracts from a Snowflake stage. The pipeline uses Cortex AI functions to parse, extract, and classify documents, stores structured results in tables, and refreshes automatically as new files arrive.

Architecture​

 Stage (PDFs/images land here)
│
▼
Stream (detects new files)
│
▼
Task (triggers processing)
│
├──► AI_PARSE_DOCUMENT → raw text extraction
├──► AI_EXTRACT → structured field extraction
└──► AI_CLASSIFY → document type classification
│
▼
Results Table (structured output)

Stage Setup​

-- Create a stage for incoming documents
CREATE OR REPLACE STAGE doc_db.public.incoming_docs
ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE');

-- Create directory table to track files
ALTER STAGE doc_db.public.incoming_docs SET DIRECTORY = (ENABLE = TRUE);
ALTER STAGE doc_db.public.incoming_docs REFRESH;

Step 1: Parse Documents​

Use AI_PARSE_DOCUMENT to extract raw text from PDFs and images:

-- Create a table for parsed document text
CREATE OR REPLACE TABLE doc_db.public.parsed_documents AS
SELECT
RELATIVE_PATH AS file_path,
BUILD_SCOPED_FILE_URL(@doc_db.public.incoming_docs, RELATIVE_PATH) AS file_url,
SNOWFLAKE.CORTEX.AI_PARSE_DOCUMENT(
BUILD_SCOPED_FILE_URL(@doc_db.public.incoming_docs, RELATIVE_PATH),
{'mode': 'LAYOUT'}
):content::VARCHAR AS parsed_text,
CURRENT_TIMESTAMP() AS processed_at
FROM DIRECTORY(@doc_db.public.incoming_docs)
WHERE RELATIVE_PATH LIKE '%.pdf' OR RELATIVE_PATH LIKE '%.png';

Step 2: Classify Documents​

Use AI_CLASSIFY to categorize each document:

-- Classify document types
CREATE OR REPLACE TABLE doc_db.public.classified_documents AS
SELECT
file_path,
parsed_text,
SNOWFLAKE.CORTEX.AI_CLASSIFY(
parsed_text,
['invoice', 'contract', 'purchase_order', 'receipt', 'other']
):label::VARCHAR AS document_type,
SNOWFLAKE.CORTEX.AI_CLASSIFY(
parsed_text,
['invoice', 'contract', 'purchase_order', 'receipt', 'other']
):score::FLOAT AS classification_confidence,
processed_at
FROM doc_db.public.parsed_documents;

Step 3: Extract Structured Fields​

Use AI_EXTRACT to pull specific fields based on document type:

-- Extract fields from invoices
CREATE OR REPLACE TABLE doc_db.public.extracted_invoices AS
SELECT
file_path,
document_type,
SNOWFLAKE.CORTEX.AI_EXTRACT(
parsed_text,
{
'vendor_name': 'The name of the vendor or supplier',
'invoice_number': 'The invoice number or ID',
'invoice_date': 'The date of the invoice in YYYY-MM-DD format',
'total_amount': 'The total amount due as a number',
'currency': 'The currency code (e.g., USD, EUR)',
'line_items': 'A summary of the line items'
}
) AS extracted_fields,
extracted_fields:vendor_name::VARCHAR AS vendor_name,
extracted_fields:invoice_number::VARCHAR AS invoice_number,
TRY_TO_DATE(extracted_fields:invoice_date::VARCHAR) AS invoice_date,
TRY_TO_DECIMAL(extracted_fields:total_amount::VARCHAR, 12, 2) AS total_amount,
extracted_fields:currency::VARCHAR AS currency,
processed_at
FROM doc_db.public.classified_documents
WHERE document_type = 'invoice';

-- Extract fields from contracts
CREATE OR REPLACE TABLE doc_db.public.extracted_contracts AS
SELECT
file_path,
document_type,
SNOWFLAKE.CORTEX.AI_EXTRACT(
parsed_text,
{
'party_a': 'The first party in the contract',
'party_b': 'The second party in the contract',
'effective_date': 'The contract start date in YYYY-MM-DD format',
'expiration_date': 'The contract end date in YYYY-MM-DD format',
'contract_value': 'The total contract value as a number',
'key_terms': 'Summary of key terms and conditions'
}
) AS extracted_fields,
extracted_fields:party_a::VARCHAR AS party_a,
extracted_fields:party_b::VARCHAR AS party_b,
TRY_TO_DATE(extracted_fields:effective_date::VARCHAR) AS effective_date,
TRY_TO_DATE(extracted_fields:expiration_date::VARCHAR) AS expiration_date,
processed_at
FROM doc_db.public.classified_documents
WHERE document_type = 'contract';

Automating with Streams and Tasks​

Create a Stream on the Directory Table​

-- Stream detects new files added to the stage
CREATE OR REPLACE STREAM doc_db.public.new_docs_stream
ON STAGE doc_db.public.incoming_docs;

Create the Processing Task​

-- Task runs when new files are detected
CREATE OR REPLACE TASK doc_db.public.process_new_documents
WAREHOUSE = compute_wh
SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('doc_db.public.new_docs_stream')
AS
BEGIN
-- Parse new documents
INSERT INTO doc_db.public.parsed_documents
SELECT
RELATIVE_PATH,
BUILD_SCOPED_FILE_URL(@doc_db.public.incoming_docs, RELATIVE_PATH),
SNOWFLAKE.CORTEX.AI_PARSE_DOCUMENT(
BUILD_SCOPED_FILE_URL(@doc_db.public.incoming_docs, RELATIVE_PATH),
{'mode': 'LAYOUT'}
):content::VARCHAR,
CURRENT_TIMESTAMP()
FROM doc_db.public.new_docs_stream
WHERE RELATIVE_PATH LIKE '%.pdf' OR RELATIVE_PATH LIKE '%.png';

-- Classify and extract (process newly parsed docs)
-- Additional INSERT statements for classification and extraction
END;

ALTER TASK doc_db.public.process_new_documents RESUME;

Monitoring Pipeline Health​

-- Check processing status
SELECT
DATE_TRUNC('hour', processed_at) AS hour,
COUNT(*) AS documents_processed,
COUNT_IF(classification_confidence > 0.8) AS high_confidence,
AVG(classification_confidence) AS avg_confidence
FROM doc_db.public.classified_documents
GROUP BY 1
ORDER BY 1 DESC
LIMIT 24;

-- Find failed or low-confidence extractions
SELECT file_path, document_type, classification_confidence
FROM doc_db.public.classified_documents
WHERE classification_confidence < 0.5
ORDER BY processed_at DESC;

-- Task history
SELECT name, state, scheduled_time, completed_time, error_message
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
TASK_NAME => 'PROCESS_NEW_DOCUMENTS',
RESULT_LIMIT => 20
));

Summary​

This capstone demonstrated building an automated document processing pipeline using Cortex AI functions (AI_PARSE_DOCUMENT, AI_EXTRACT, AI_CLASSIFY), Streams for change detection, and Tasks for scheduled execution. The pipeline handles multiple document types and stores structured results for downstream analytics.