Snowflake Gen AI Functions · Topic 13 of 26
TO_FILE and PROMPT - Building File References and Prompt Objects
Use TO_FILE to create FILE values from staged files and PROMPT to build prompt objects with placeholders for AI_COMPLETE, AI_FILTER and other AI functions.
- 4 min
TO_FILEreturns aFILEfrom a stage and relative path, a file URL, or a metadata object.- Use a directory table to process many staged files.
PROMPT(template, values...)uses{0},{1}placeholders and returns an object withtemplateandargs.
What it is
Many Cortex AI functions work on files and on multi part prompts. Two helper functions prepare those inputs.
TO_FILEcreates aFILEvalue that points to a file on a stage. AI functions such asAI_PARSE_DOCUMENT,AI_EXTRACT,AI_TRANSCRIBEandAI_COMPLETEread from it.PROMPTbuilds a prompt object from a template with numbered placeholders and the values that fill them. The values can be text columns orFILEvalues.
Neither calls a model. They only shape the input. PROMPT is listed as a preview feature that is available to all accounts.
How it works
TO_FILE
TO_FILE('@stage_name', 'relative/path.pdf')
TO_FILE(<file_url>)
TO_FILE(<metadata_object>)
Only one form can be used at a time. The first form takes a stage and a relative path. The second takes a stage file URL or scoped URL. The third takes an object that has the required FILE fields. The return type is FILE.
SELECT AI_PARSE_DOCUMENT(TO_FILE('@docs', 'contract.pdf'), {'mode': 'LAYOUT'});
SELECT relative_path,
AI_EXTRACT(file => TO_FILE('@docs', relative_path),
responseFormat => ['What is the effective date?'])
FROM DIRECTORY('@docs');
A directory table is the usual way to process a whole stage. TO_FILE(FILE_URL) FROM DIRECTORY(@mystage) and TO_FILE('@mystage', relative_path) FROM DIRECTORY('@mystage') both work.
You can store FILE values in a table column and inspect them with helper functions such as FL_GET_STAGE, FL_GET_RELATIVE_PATH and FL_GET_FILE_TYPE. To process only chosen pages of a document, wrap the file in FL_FILTER_PAGES inside AI_EXTRACT or AI_PARSE_DOCUMENT.
Limitations:
TO_FILEcannot be used insideINSERT INTO <table> VALUES. UseINSERT INTO ... SELECT.FILEvalues cannot be used asGROUP BY,ORDER BYorCLUSTER BYkeys, because they cannot be compared.- The role must have privileges on the stage, otherwise an error is raised.
- The AI functions cannot read files from user stages, table stages, stages with double quoted names, or stages with
SNOWFLAKE_FULL,AWS_CSEorAZURE_CSEencryption.
A scenario that says "files on an internal stage, process each with AI functions" expects a directory table, TO_FILE and an AI function, in that order.
PROMPT
PROMPT('<template_string>', <expr_1> [, <expr_2>, ...])
The template uses placeholders {0}, {1} and so on, starting at 0. The first expression fills {0}, the second fills {1}. A placeholder with no matching expression is an error, and extra expressions that the template does not use are allowed. Values must be coercible to a string, or be a FILE.
The function returns an object with two keys: template, the template string, and args, an array of the values. PROMPT does not format the string. It only builds the object for the AI function to read.
SELECT PROMPT('Classify the sentiment of {0} for product {1}', review, product_name)
FROM reviews;
Where PROMPT is used
| Function | Use of PROMPT |
|---|---|
AI_COMPLETE | Prompt object form with text and several FILE values |
AI_FILTER | Evaluate a condition built from several columns or files |
AI_CLASSIFY | The input can be a prompt object |
SELECT * FROM reviews
WHERE AI_FILTER(PROMPT('The reviewer enjoyed the restaurant: {0}, rating {1}',
review, rating));
SELECT AI_COMPLETE('claude-sonnet-4-6',
PROMPT('Classify the item in {0} as damaged or intact.',
TO_FILE('@photos', 'item1.jpg')));
In a join, AI_FILTER(PROMPT(...)) can match rows from two tables on meaning, for example resumes to job descriptions.
Why a prompt object
A plain string works for one column. When a call needs several columns or files, a prompt object keeps the template and the data separate and tells the function which value belongs in which spot. It is what lets one call mix text and images in the AI_COMPLETE prompt object form.
Numbering starts at 0, not 1. A template that uses {1} and {2} with two values fails because {2} has no matching expression.
Common mistakes
Starting placeholders at {1}. They begin at {0}.
Using TO_FILE in INSERT ... VALUES. Use INSERT ... SELECT.
Pointing TO_FILE at a user stage. AI functions cannot read files from user or table stages.
Grouping or ordering by a FILE column. FILE values cannot be compared.
Expecting PROMPT to return a string. It returns an object with template and args.
What to remember for the exam
TO_FILEreturns aFILEfrom a stage and relative path, a file URL, or a metadata object.- Use a directory table to process many staged files.
PROMPT(template, values...)uses{0},{1}placeholders and returns an object withtemplateandargs.- Placeholders without a matching expression error; extra expressions are allowed.
FILEvalues cannot beGROUP BY,ORDER BYorCLUSTER BYkeys.PROMPTis in preview and available to all accounts.
Check yourself
Which helper creates a reference to a staged PDF for
AI_PARSE_DOCUMENT?TO_FILE, giving the stage and relative path.What does
PROMPTreturn?An object with a
templatekey and anargsarray.Which placeholder fills with the first expression in
PROMPT?{0}.
Topic 13 of 26 in Snowflake Gen AI Functions
Test yourself on Snowflake Gen AI Functions
The full Snowflake GES-C02 question bank has 585 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.