Overview

Prerequisites

What You'll Learn

What You'll Need

What You'll Build

Setup

Import the Notebooks

Set the Lab Context

CREATE DATABASE IF NOT EXISTS VHOL_DOCUMENT_AI_DB;
CREATE SCHEMA IF NOT EXISTS VHOL_DOCUMENT_AI_DB.DOCUMENT_AI;

USE DATABASE VHOL_DOCUMENT_AI_DB;
USE SCHEMA DOCUMENT_AI;

SELECT CURRENT_ROLE(), CURRENT_DATABASE(), CURRENT_SCHEMA(), CURRENT_WAREHOUSE();

Classify Mortgage Documents

Create the Stage

CREATE STAGE IF NOT EXISTS MORTGAGE_DOCS
    ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')
    DIRECTORY = (ENABLE = TRUE);

Upload the Sample PDFs

ALTER STAGE MORTGAGE_DOCS REFRESH;

LIST @MORTGAGE_DOCS;

Select the PDFs

CREATE OR REPLACE TEMP TABLE MORTGAGE_FILES AS
SELECT
    relative_path,
    SPLIT_PART(relative_path, '/', -1) AS filename,
    size AS file_size_bytes,
    last_modified
FROM DIRECTORY(@MORTGAGE_DOCS)
WHERE relative_path ILIKE '%.pdf';

SELECT * FROM MORTGAGE_FILES ORDER BY relative_path;

Define the Routes

CREATE OR REPLACE TEMP TABLE MORTGAGE_ROUTES (
    document_type VARCHAR,
    category_description VARCHAR,
    response_format VARIANT
);

INSERT INTO MORTGAGE_ROUTES
SELECT
    'Non taxpayer affidavit',
    'Affidavit of Non-Taxpayer with selected people, roles, years not filed, county, and notarization.',
    PARSE_JSON($${
  "county": "What county is entered near the top of the affidavit? Return an empty string if blank.",
  "affidavit_date": "What date does the notary acknowledgment in Section F give? Return YYYY-MM-DD, or an empty string if blank. Do not use the commission expiration date.",
  "commission_expiration_date": "What date is written as Commission expiration in the notary section? The date is in MM/DD/YYYY format. Return YYYY-MM-DD, or an empty string if blank."
}$$)
UNION ALL
SELECT
    'Assets and transaction details',
    'Residential loan application with Section VII assets and liabilities and Section VIII Details of Transaction, including purchase price, loan proceeds, and cash to close.',
    PARSE_JSON($${
  "total_assets": "What amount is written for TOTAL ASSETS in Section VII? Do not use Liquid Assets Subtotal. Return the written amount, or an empty string if blank.",
  "total_liabilities": "What amount is written for TOTAL LIABILITIES in Section VII, in the Unpaid balance column? Return the written amount, or an empty string if blank.",
  "purchase_price": "What amount is written for line a, Purchase price, in Section VIII, Details of Transaction? Return the written amount, or an empty string if blank.",
  "loan_proceeds": "What amount is written for line d, Loan proceeds, in Section VIII? Read the written amount; do not calculate it. Return an empty string if blank.",
  "cash_to_close": "What amount is written for line g, Cash to close, in Section VIII? Read the written amount; do not calculate it. Return an empty string if blank."
}$$)
UNION ALL
SELECT
    'Settlement charges',
    'Settlement charges worksheet listing fees by line number and line 1400 borrower and seller settlement charge totals.',
    PARSE_JSON($${
  "borrower_settlement_charges": "What amount is written on line 1400, Total Settlement Charges, in the Borrower paid column? Read the written total; do not calculate it. Return an empty string if blank.",
  "seller_settlement_charges": "What amount is written on line 1400, Total Settlement Charges, in the Seller paid column? Read the written total; do not calculate it. Return an empty string if blank.",
  "appraisal_fee": "What appraisal fee is written on line 803? Return the amount shown, or an empty string if blank.",
  "credit_report_fee": "What credit report fee is written on line 804? Return the amount shown, or an empty string if blank."
}$$)
UNION ALL
SELECT
    'Citizenship declaration',
    'Declaration of Citizenship / Household Status form with numbered blocks 1, 2, and 3 in Section II for citizenship status, followed by a signature and date.',
    PARSE_JSON($${
  "declarant_name": "What name is printed after I, at the top of the declaration? Return the printed name as written, or an empty string if blank.",
  "citizenship_status": "Which status block is checked? Return exactly one of: U.S. citizen; Noncitizen with eligible immigration status; Not contending eligible immigration status. Return an empty string if no block is checked.",
  "signature_date": "What date is written next to the signature? The date on this form is in MM/DD/YYYY format. Return YYYY-MM-DD, or an empty string if blank."
}$$)
UNION ALL
SELECT
    'Other',
    'Any other document, including notices and disclosures such as the Right to Financial Privacy Act notice or VA and HUD/FHA borrower notices, government program agreement summaries, and property insurance rating or underwriting forms. Choose Other for notice or disclosure text even when it appears alongside a loan application.',
    NULL::VARIANT;

Classify Each PDF

CREATE OR REPLACE TEMP TABLE MORTGAGE_CLASSIFIED AS
WITH categories AS (
    SELECT ARRAY_AGG(
        OBJECT_CONSTRUCT('label', document_type, 'description', category_description)
    ) WITHIN GROUP (ORDER BY document_type) AS categories
    FROM MORTGAGE_ROUTES
),
classified AS (
    SELECT
        f.relative_path,
        f.filename,
        AI_CLASSIFY(
            TO_FILE('@MORTGAGE_DOCS', f.relative_path),
            c.categories,
            {
                'task_description': 'Classify the supplied document page by its visible headings and sections. Distinguish loan application pages by section content. Use Other when none of the defined form types matches. Do not use the filename as evidence.',
                'output_mode': 'single'
            },
            TRUE  -- returns error details instead of failing the query
        ) AS raw_classification
    FROM MORTGAGE_FILES AS f
    CROSS JOIN categories AS c
)
SELECT
    relative_path,
    filename,
    NULLIF(raw_classification:value:labels[0]::VARCHAR, 'null') AS document_type,
    NULLIF(NULLIF(TRIM(raw_classification:error::VARCHAR), ''), 'null') AS classification_error
FROM classified;

SELECT filename, document_type, classification_error
FROM MORTGAGE_CLASSIFIED
ORDER BY filename;

Extract Business Fields

CREATE OR REPLACE TEMP TABLE MORTGAGE_EXTRACTED AS
WITH extraction_raw AS (
    SELECT
        c.relative_path,
        c.filename,
        c.document_type,
        AI_EXTRACT(
            file => TO_FILE('@MORTGAGE_DOCS', c.relative_path),
            responseFormat => r.response_format
        ) AS raw_extraction
    FROM MORTGAGE_CLASSIFIED AS c
    JOIN MORTGAGE_ROUTES AS r ON c.document_type = r.document_type
    WHERE c.classification_error IS NULL
      AND r.response_format IS NOT NULL
)
SELECT
    relative_path,
    filename,
    document_type,
    raw_extraction:response AS extracted_data,
    NULLIF(NULLIF(TRIM(raw_extraction:error::VARCHAR), ''), 'null') AS extraction_error
FROM extraction_raw;

SELECT filename, document_type, extracted_data, extraction_error
FROM MORTGAGE_EXTRACTED
ORDER BY filename;

Review the Results

CREATE OR REPLACE TEMP TABLE MORTGAGE_RESULTS AS
WITH blank_fields AS (
    SELECT
        e.relative_path,
        LISTAGG(f.key, ', ') WITHIN GROUP (ORDER BY f.key) AS blank_fields
    FROM MORTGAGE_EXTRACTED AS e,
         LATERAL FLATTEN(INPUT => e.extracted_data) AS f
    WHERE NULLIF(TRIM(f.value::VARCHAR), '') IS NULL
       OR LOWER(TRIM(f.value::VARCHAR)) IN ('null', 'none', 'unknown', 'n/a')
    GROUP BY e.relative_path
)
SELECT
    c.filename,
    c.document_type,
    CASE
        WHEN c.classification_error IS NOT NULL THEN 'CLASSIFICATION_ERROR'
        WHEN r.response_format IS NULL THEN 'UNSUPPORTED_DOCUMENT'
        WHEN e.extraction_error IS NOT NULL OR e.extracted_data IS NULL THEN 'EXTRACTION_ERROR'
        WHEN b.blank_fields IS NOT NULL THEN 'FIELD_REVIEW'
        WHEN c.document_type = 'Citizenship declaration'
             AND NOT COALESCE(
                 (e.extracted_data:citizenship_status::VARCHAR) IN (
                     'U.S. citizen',
                     'Noncitizen with eligible immigration status',
                     'Not contending eligible immigration status'
                 ), FALSE)
            THEN 'FIELD_REVIEW'
        ELSE 'EXTRACTED'
    END AS processing_status,
    b.blank_fields,
    e.extracted_data
FROM MORTGAGE_CLASSIFIED AS c
LEFT JOIN MORTGAGE_ROUTES AS r ON c.document_type = r.document_type
LEFT JOIN MORTGAGE_EXTRACTED AS e ON c.relative_path = e.relative_path
LEFT JOIN blank_fields AS b ON c.relative_path = b.relative_path;

SELECT filename, document_type, processing_status, blank_fields, extracted_data
FROM MORTGAGE_RESULTS
ORDER BY processing_status, filename;

Check Values Against the Source PDFs

SELECT
    filename,
    extracted_data:total_assets::VARCHAR AS total_assets,
    extracted_data:total_liabilities::VARCHAR AS total_liabilities,
    extracted_data:purchase_price::VARCHAR AS purchase_price,
    extracted_data:loan_proceeds::VARCHAR AS loan_proceeds,
    extracted_data:cash_to_close::VARCHAR AS cash_to_close,
    extracted_data:borrower_settlement_charges::VARCHAR AS borrower_settlement_charges,
    extracted_data:seller_settlement_charges::VARCHAR AS seller_settlement_charges
FROM MORTGAGE_RESULTS
WHERE filename IN ('03.pdf', '04.pdf');

Try it: Refine the description for the assets-and-transaction route, then re-run the classification cell and compare the results. Re-running model calls can incur additional usage.

Search Greenhouse Reports

Create the Stage

CREATE STAGE IF NOT EXISTS GHG_REPORTS
    ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')
    DIRECTORY = (ENABLE = TRUE);

Upload the PDF

ALTER STAGE GHG_REPORTS REFRESH;

LIST @GHG_REPORTS;

Register the Report

CREATE OR REPLACE TEMP TABLE GHG_FILES AS
SELECT
    relative_path,
    SPLIT_PART(relative_path, '/', -1) AS filename,
    REGEXP_SUBSTR(SPLIT_PART(relative_path, '/', -1), '(20[0-9]{2})', 1, 1, 'e', 1) AS report_year,
    size AS file_size_bytes,
    last_modified
FROM DIRECTORY(@GHG_REPORTS)
WHERE relative_path ILIKE '%.pdf';

SELECT * FROM GHG_FILES ORDER BY report_year;

Parse the Report with AGENTIC Mode

CREATE OR REPLACE TEMP TABLE GHG_PARSED AS
SELECT
    relative_path,
    filename,
    report_year,
    AI_PARSE_DOCUMENT(
        TO_FILE('@GHG_REPORTS', relative_path),
        {'mode': 'AGENTIC', 'page_split': TRUE},
        TRUE
    ) AS raw_parse
FROM GHG_FILES;

SELECT
    filename,
    report_year,
    ARRAY_SIZE(raw_parse:value:pages) AS returned_pages,
    raw_parse:error AS call_error,
    raw_parse:value:error AS document_error
FROM GHG_PARSED
ORDER BY report_year;

Split Pages, Visual Summaries, and Chart Details

CREATE OR REPLACE TEMP TABLE GHG_PAGES AS
SELECT
    r.relative_path,
    r.filename,
    r.report_year,
    p.value:index::NUMBER + 1 AS pdf_page_number,
    p.value:content::VARCHAR AS page_content,
    p.value:images AS images
FROM GHG_PARSED AS r,
     LATERAL FLATTEN(INPUT => r.raw_parse:value:pages) AS p
WHERE NULLIF(r.raw_parse:error::VARCHAR, 'null') IS NULL;

CREATE OR REPLACE TEMP TABLE GHG_VISUALS AS
SELECT
    p.relative_path,
    p.filename,
    p.report_year,
    p.pdf_page_number,
    i.index AS image_index,
    i.value:results:image_type::VARCHAR AS image_type,
    i.value:results:summary::VARCHAR AS summary,
    i.value:results:details AS details
FROM GHG_PAGES AS p,
     LATERAL FLATTEN(INPUT => p.images) AS i
WHERE NULLIF(TRIM(i.value:results:summary::VARCHAR), '') IS NOT NULL
  AND COALESCE(LOWER(i.value:results:description::VARCHAR), '') <> 'error';

SELECT report_year, COUNT(*) AS pages FROM GHG_PAGES GROUP BY report_year ORDER BY report_year;

SELECT report_year, COUNT(*) AS visuals_with_summary FROM GHG_VISUALS GROUP BY report_year ORDER BY report_year;

List All Extracted Images

SELECT
    p.pdf_page_number,
    i.index AS image_index,
    i.value:id::VARCHAR AS image_id,
    i.value:results:image_type::VARCHAR AS image_type,
    i.value:results:summary::VARCHAR AS summary,
    i.value:results:details AS details
FROM GHG_PAGES AS p,
     LATERAL FLATTEN(INPUT => p.images) AS i
ORDER BY p.pdf_page_number, i.index;

Check the Regional Figure on Page 2

SELECT
    pdf_page_number,
    image_index,
    image_type,
    summary,
    LEFT(TO_JSON(details), 1200) AS details_preview
FROM GHG_VISUALS
WHERE report_year = '2024'
  AND pdf_page_number = 2
ORDER BY image_index;

Build the Search Content

CREATE OR REPLACE TEMP TABLE GHG_SEARCH_UNITS AS
SELECT relative_path, filename, report_year, pdf_page_number,
       'text'::VARCHAR AS content_type,
       page_content AS body
FROM GHG_PAGES
WHERE NULLIF(TRIM(page_content), '') IS NOT NULL
UNION ALL
SELECT relative_path, filename, report_year, pdf_page_number,
       'visual_summary'::VARCHAR AS content_type,
       summary AS body
FROM GHG_VISUALS
UNION ALL
SELECT
    v.relative_path,
    v.filename,
    v.report_year,
    v.pdf_page_number,
    'visual_details'::VARCHAR AS content_type,
    LISTAGG(d.path || ': ' || d.value::VARCHAR, '\n') WITHIN GROUP (ORDER BY d.path) AS body
FROM GHG_VISUALS AS v,
     LATERAL FLATTEN(INPUT => v.details, RECURSIVE => TRUE) AS d
WHERE TYPEOF(d.value) NOT IN ('OBJECT', 'ARRAY', 'NULL_VALUE')
  AND d.path NOT LIKE '%value_range%'
  AND d.path NOT LIKE '%value_precision%'
GROUP BY v.relative_path, v.filename, v.report_year, v.pdf_page_number, v.image_index;

CREATE OR REPLACE TABLE GHG_SEARCH_CHUNKS
    DATA_RETENTION_TIME_IN_DAYS = 1 AS
SELECT
    MD5(u.relative_path || '|' || u.pdf_page_number::VARCHAR || '|' ||
        u.content_type || '|' || c.index::VARCHAR) AS chunk_id,
    u.relative_path,
    u.filename,
    u.report_year,
    u.pdf_page_number,
    u.content_type,
    'Report: Greenhouse Gas Report ' || u.report_year ||
    '. PDF page: ' || u.pdf_page_number::VARCHAR ||
    '. Content: ' || u.content_type || CHR(10) || c.value::VARCHAR AS search_text
FROM GHG_SEARCH_UNITS AS u,
     LATERAL FLATTEN(INPUT => SNOWFLAKE.CORTEX.SPLIT_TEXT_RECURSIVE_CHARACTER(
         u.body, 'markdown', 1400, 150
     )) AS c
WHERE NULLIF(TRIM(c.value::VARCHAR), '') IS NOT NULL;

ALTER TABLE GHG_SEARCH_CHUNKS SET CHANGE_TRACKING = TRUE;

SELECT report_year, content_type, COUNT(*) AS chunks
FROM GHG_SEARCH_CHUNKS
GROUP BY report_year, content_type
ORDER BY report_year, content_type;

Create the Cortex Search Service

-- Replaces only the Search service created by this notebook.
DROP CORTEX SEARCH SERVICE IF EXISTS GHG_REPORT_SEARCH;

EXECUTE IMMEDIATE $$
DECLARE
    wh VARCHAR DEFAULT CURRENT_WAREHOUSE();
    ddl VARCHAR;
    missing_warehouse EXCEPTION (-20001, 'Select a lab warehouse before creating Search.');
BEGIN
    IF (wh IS NULL) THEN
        RAISE missing_warehouse;
    END IF;
    ddl := 'CREATE OR REPLACE CORTEX SEARCH SERVICE GHG_REPORT_SEARCH '
        || 'ON search_text ATTRIBUTES report_year, content_type, filename '
        || 'WAREHOUSE = "' || REPLACE(wh, '"', '""') || '" '
        || 'TARGET_LAG = ''1 hour'' INITIALIZE = ON_CREATE '
        || 'AS SELECT chunk_id, search_text, relative_path, filename, '
        || 'report_year, pdf_page_number, content_type FROM GHG_SEARCH_CHUNKS';
    EXECUTE IMMEDIATE :ddl;
    RETURN 'Created GHG_REPORT_SEARCH.';
END;
$$;

DESCRIBE CORTEX SEARCH SERVICE GHG_REPORT_SEARCH;

Retrieve Passages for the Question

CREATE OR REPLACE TEMP TABLE GHG_RETRIEVED_PASSAGES AS
WITH hits AS (
    SELECT
        r.value:FILENAME::VARCHAR AS filename,
        r.value:REPORT_YEAR::VARCHAR AS report_year,
        r.value:PDF_PAGE_NUMBER::NUMBER AS pdf_page_number,
        r.value:CONTENT_TYPE::VARCHAR AS content_type,
        r.value:SEARCH_TEXT::VARCHAR AS passage
    FROM (
        SELECT PARSE_JSON(SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
            'GHG_REPORT_SEARCH',
            '{"query": "Which year had the highest and which had the lowest annual regional total CO2 emissions between 2020 and 2023", "columns": ["SEARCH_TEXT", "FILENAME", "REPORT_YEAR", "PDF_PAGE_NUMBER", "CONTENT_TYPE"], "filter": {"@eq": {"REPORT_YEAR": "2024"}}, "limit": 6}'
        )) AS response
    ) AS s,
    LATERAL FLATTEN(INPUT => s.response:results) AS r
),
details AS (
    SELECT report_year, pdf_page_number, LISTAGG(search_text, '\n') WITHIN GROUP (ORDER BY chunk_id) AS chart_details
    FROM GHG_SEARCH_CHUNKS
    WHERE content_type = 'visual_details'
    GROUP BY report_year, pdf_page_number
)
SELECT h.filename, h.report_year, h.pdf_page_number, h.content_type, h.passage, d.chart_details
FROM hits AS h
LEFT JOIN details AS d
  ON d.report_year = h.report_year
 AND d.pdf_page_number = h.pdf_page_number
ORDER BY h.pdf_page_number;

SELECT * FROM GHG_RETRIEVED_PASSAGES ORDER BY pdf_page_number;

Answer the Question with AI_COMPLETE

WITH context AS (
    SELECT
        LISTAGG(
            'Report ' || report_year || ', PDF page ' || pdf_page_number::VARCHAR || ' (' || content_type || '):\n' || passage ||
            IFF(chart_details IS NULL, '', '\nChart details for page ' || pdf_page_number::VARCHAR || ':\n' || chart_details),
            '\n---\n'
        ) WITHIN GROUP (ORDER BY pdf_page_number) AS passages
    FROM GHG_RETRIEVED_PASSAGES
)
SELECT AI_COMPLETE(
    'claude-sonnet-5-5',
    'You answer questions about a 2024 greenhouse gas report using only the passages below. Question: which year had the highest and which had the lowest annual regional total CO2 emissions between 2020 and 2023? Cite each claim as [Report 2024, PDF page P, source], where source is the content label of the passage you used: text, visual_summary, or visual_details. If the passages do not name a region or the fire series, say so rather than guessing. If the passages do not contain the answer, say what is missing.\n\nPassages:\n' || passages
) AS answer
FROM context;

Highest: 2020. Lowest: 2023. I read "between 2020 and 2023" as inclusive of both years.

Annual regional total CO₂ (GtC yr⁻¹), from the dark/"Black Bars" series in Figure 1:

• 2020: 0.57 [Report 2024, PDF page 2, visual_details]
• 2021: 0.36 [Report 2024, PDF page 2, visual_details]
• 2022: 0.33 [Report 2024, PDF page 2, visual_details]
• 2023: 0.21 [Report 2024, PDF page 2, visual_details]

The 2023 total of 0.21 is also given in Table 3 (fire 0.47, NBE −0.26) [Report 2024, PDF page 2, text].

• Series identification: The chart legend is illegible in the extracted details, so the series are labeled only by appearance. The Figure 1 caption states that dark bars show total emissions [Report 2024, PDF page 2, text]. I therefore took the "Black Bars" series as the total.
• Consistency check: For each of these years, fire plus NBE matches the total, as the report's formula requires [Report 2024, PDF page 1, text]. For 2020, grey bars (0.43) plus striped bars (0.14) give 0.57 [Report 2024, PDF page 2, visual_details].
• Estimated values: The chart details are flagged values_estimated: true [Report 2024, PDF page 2, visual_details]. Only the 2023 figure is confirmed by an exact table value. The ranking is still clear, because the gaps between years are large relative to rounding.
• Region: The passages do not name a region. They describe only "one illustrative land area" [Report 2024, PDF page 1, text] and an "Illustrative region" [Report 2024, PDF page 2, text]. The totals exclude fossil-fuel and ocean fluxes, so they are not a global inventory [Report 2024, PDF page 1, text].
• Exclusive reading: If you meant only the years strictly between 2020 and 2023 (2021 and 2022), the highest would be 2021 (0.36) and the lowest 2022 (0.33).

Cleanup

DROP DATABASE IF EXISTS VHOL_DOCUMENT_AI_DB;

Conclusion And Resources

What You Learned

Dataset Sources

Related Resources