AI.IF
Category: Managed AI Function (Preview)
Description
Filters data based on semantic meaning using natural language conditions. Returns a BOOL value indicating whether the input satisfies the condition. Designed for use in WHERE and JOIN clauses to enable semantic filtering without exact keyword matching.
Use Cases
Content Filtering: Find articles related to specific topics without exact keyword matches
Security: Identify potential phishing emails or suspicious content
Product Search: Filter products by semantic attributes ("suitable for outdoor use")
Entity Resolution: Perform semantic joins between different datasets
Data Quality: Filter records meeting semantic criteria
Syntax
AI.IF(
('CONDITION', INPUT),
[, connection_id => 'CONNECTION' ]
[, endpoint => 'ENDPOINT' ]
)Parameters
CONDITION: Natural language description of the filtering criteria
INPUT: Text data from columns OR ObjectRefRuntime values for multimodal data
CONNECTION (optional): Connection ID format: project.location.connection_id
ENDPOINT (optional): Gemini model endpoint (auto-selected if omitted)
Code Examples
Example 1: Semantic Filtering in WHERE Clause
SELECT
title,
body
FROM news_articles
WHERE AI.IF(
('This article discusses climate change.', body),
connection_id => 'us.my_vertex_connection'
)
LIMIT 20;Example 2: Phishing Detection
SELECT
email_id,
subject,
sender,
body
FROM emails
WHERE AI.IF(
('This email is a phishing attempt.', CONCAT(subject, ' ', body)),
connection_id => 'us.my_vertex_connection'
)
ORDER BY received_date DESC;Example 3: Optimized Query with Traditional Filters First
-- BigQuery runs cheap filters first, then AI filters on the subset
SELECT
product_id,
name,
description
FROM products
WHERE category = 'outdoor' -- Traditional filter (cheap)
AND price < 100 -- Traditional filter (cheap)
AND AI.IF( -- AI filter (expensive, runs last)
('This product is suitable for camping.', description),
connection_id => 'us.my_vertex_connection'
);Example 4: Semantic JOIN
-- Find semantically matching products across two catalogs
SELECT
a.product_id AS catalog_a_id,
a.product_name AS catalog_a_name,
b.product_id AS catalog_b_id,
b.product_name AS catalog_b_name
FROM catalog_a AS a
CROSS JOIN catalog_b AS b
WHERE AI.IF(
('These two products are the same item.',
CONCAT('Product A: ', a.product_name, ' ', a.description,
' | Product B: ', b.product_name, ' ', b.description)),
connection_id => 'us.my_vertex_connection'
)
LIMIT 100;Example 5: Image Filtering
SELECT
STRING(OBJ.GET_ACCESS_URL(ref,'r').access_urls.read_url) AS image_url
FROM product_images
WHERE AI.IF(
('This image contains a person wearing red.', OBJ.GET_ACCESS_URL(ref, 'r')),
connection_id => 'us.my_vertex_connection'
)
LIMIT 10;Data Output Examples
Climate Change Articles
title | matches_condition |
|---|---|
"Global Temperatures Rising Faster Than Expected" | true |
"New Electric Vehicle Models Released" | false |
"Carbon Emissions Reach Record High" | true |
Phishing Emails
email_id | subject | is_phishing |
|---|---|---|
E001 | "Urgent: Verify your account now!" | true |
E002 | "Meeting notes from yesterday" | false |
E003 | "You've won $1,000,000!" | true |
Best Practices
Place traditional filters first: Let BigQuery optimize by running cheap filters before expensive AI filters
Be specific in conditions: Clear, specific conditions yield better results
Use for semantic matching: Ideal when exact keywords won't work
Combine with LIMIT: Control costs by limiting result sets
Test conditions first: Validate AI.IF behavior with sample data
When to Use
✅ Use AI.IF for semantic filtering beyond keyword matching
✅ Use in WHERE clauses to filter based on meaning
✅ Use in JOIN conditions for entity resolution
✅ Use when traditional pattern matching is insufficient
Alternatives
Traditional WHERE: For exact matches or pattern matching (cheaper)
LIKE/REGEXP: For pattern-based filtering
AI.CLASSIFY: When you need to categorize into multiple groups
AI.GENERATE_BOOL: When you need custom prompt control
Platform Support
Regions: All Gemini-supported regions + US/EU multi-regions
Preview Status: Currently in Preview (Pre-GA)
Cost: Charged per Vertex AI API call
Optimization: BigQuery query planner optimizes to minimize AI function calls
Returns
BOOL value: true if input satisfies the condition, false otherwise. Returns NULL if the Vertex AI call fails.
Interested to Learn More? Try Out the Free 14-Days Trial
One AI-native workspace to build, ship, and monitor dbt™ — without the tool sprawl.