How do I search video from Snowflake?
Keep the facts in Snowflake and put the video in a search index keyed the same way. A typical warehouse already has a table with one row per video: an ID, a URL to the file in S3 or GCS, and the campaign, spend and performance columns. Read the IDs and URLs out of that table, index each file scene by scene in Mixpeek with the warehouse ID attached to every scene, run your questions as searches, and write the matching scenes back into a Snowflake table. From there it is an ordinary join: scenes that show a product, next to the click-through rate of the ad they came from.
What does searching video next to warehouse data need?
A row with a URL tells you nothing about what happens on screen. To answer "which scenes show someone holding the product", something has to decode each file, cut it where the shot changes, and turn every scene into a vector that a sentence can be compared against. That index lives outside the warehouse. What makes it usable from SQL is the key: if every scene carries the same
ad_id as the row it came from, every search result can be joined to anything else you know
about that ad.| Step | Where it runs | What happens |
|---|---|---|
| 1. Read the video list | Snowflake | SELECT ad_id, video_url from your ads table |
| 2. Register each file | Mixpeek bucket | One object per video, with ad_id on it |
| 3. Index scenes | Mixpeek collection | The video is cut at scene changes and each scene is embedded, transcribed and given a thumbnail |
| 4. Ask questions | Mixpeek retriever | A sentence returns matching scenes with ad_id, start and end time, and a score |
| 5. Join | Snowflake | Matches land in a SCENE_MATCHES table and join to campaign data on ad_id |
Mixpeek runs as a separate API. It reads each file from the URL you give it and returns results over HTTP. In this pattern Snowflake makes no outbound calls and nothing is installed in your account.
Step 1: read the video list from Snowflake
# pip install snowflake-connector-python pandas requests
import os
import snowflake.connector
sf = snowflake.connector.connect(
account=os.environ["SNOWFLAKE_ACCOUNT"], user=os.environ["SNOWFLAKE_USER"],
password=os.environ["SNOWFLAKE_PASSWORD"], warehouse="ANALYTICS_WH",
database="MARKETING", schema="PUBLIC",
)
ads = sf.cursor().execute(
"SELECT ad_id, video_url FROM ads WHERE video_url IS NOT NULL"
).fetchall()
print(len(ads), "videos")boto3) or skip the URL
column and connect the bucket itself as a sync source, so Mixpeek reads the files with its own
credentials (see object storage integrations). If you
sync the bucket, put the ad ID in the file path or in object tags so it reaches every scene.Step 2: index every video scene by scene
The script creates a namespace, a bucket whose objects hold a video and an
ad_id, and a
collection that runs the multimodal extractor with scene splitting and copies ad_id onto every
scene. Then it registers one object per row. idempotency_key makes a rerun return the existing
object instead of indexing the same ad twice, so you can run it again on new rows.import json, urllib.request
API = "https://api.mixpeek.com"
def call(method, path, body=None, ns=None):
headers = {"Authorization": f"Bearer {os.environ['MIXPEEK_API_KEY']}",
"Content-Type": "application/json"}
if ns:
headers["X-Namespace"] = ns
req = urllib.request.Request(API + path, method=method, headers=headers,
data=json.dumps(body).encode() if body is not None else None)
return json.load(urllib.request.urlopen(req, timeout=300))
ns = call("POST", "/v1/namespaces", {"namespace_name": "ad-scenes",
"feature_extractors": [{"feature_extractor_name": "multimodal_extractor", "version": "v1"}]})["namespace_id"]
# A new namespace has a daily budget of $0.00, so set one before indexing.
call("PATCH", f"/v1/namespaces/{ns}", {"spend_budget": {"daily_usd": 50, "monthly_usd": 100}})
bucket = call("POST", "/v1/buckets", {"bucket_name": "ads", "bucket_schema": {"properties": {
"video": {"type": "video", "required": True},
"ad_id": {"type": "string", "required": True}}}}, ns)["bucket_id"]
col = call("POST", "/v1/collections", {"collection_name": "ad_scenes",
"source": {"type": "bucket", "bucket_ids": [bucket]},
"feature_extractor": {"feature_extractor_name": "multimodal_extractor", "version": "v1",
"input_mappings": {"video": "video"},
"field_passthrough": [{"source_path": "ad_id"}],
"parameters": {"split_method": "scene", "run_multimodal_embedding": True,
"run_transcription": True, "enable_thumbnails": True}}}, ns)["collection_id"]
for ad_id, url in ads:
call("POST", f"/v1/buckets/{bucket}/objects", {
"blobs": [{"property": "video", "type": "video", "data": url}],
"ad_id": str(ad_id),
"idempotency_key": f"ad-{ad_id}",
}, ns)
call("POST", f"/v1/collections/{col}/trigger", {}, ns)start_time, end_time, thumbnail and transcript slice. The multimodal extractor
docs list the other parameters, such as fixed-length
segments instead of scene cuts and on-screen text. The extractor is one choice of model: Mixpeek
runs any model as an extractor, so a logo detector or a brand-safety classifier can label the same
scenes in the same pipeline.Step 3: search the scenes and write matches to Snowflake
Create a retriever over the collection, run each question you care about, and keep the scenes above a score you have checked by eye.
import pandas as pd
from snowflake.connector.pandas_tools import write_pandas
ret = call("POST", "/v1/retrievers", {"retriever_name": "ad_scene_search",
"collection_identifiers": [col],
"input_schema": {"query": {"type": "text", "required": True}},
"stages": [{"stage_name": "scene_search", "stage_type": "filter", "config": {
"stage_id": "feature_search", "parameters": {
"searches": [{"feature_uri": "mixpeek://multimodal_extractor@v1/vertex_multimodal_embedding",
"query": {"input_mode": "text", "text": "{{INPUT.query}}"}, "top_k": 200}],
"final_top_k": 200}}}]}, ns)
ret = ret.get("retriever", ret)["retriever_id"]
questions = ["a person holding the product up to the camera",
"the product on a plain background",
"a person talking directly to the camera"]
rows = []
for q in questions:
out = call("POST", f"/v1/retrievers/{ret}/execute", {"inputs": {"query": q}}, ns)
for m in out["results"]:
rows.append({"QUESTION": q, "AD_ID": m["ad_id"], "DOCUMENT_ID": m["document_id"],
"START_S": m["start_time"], "END_S": m["end_time"], "SCORE": m["score"]})
write_pandas(sf, pd.DataFrame(rows), "SCENE_MATCHES", auto_create_table=True, overwrite=True)Step 4: join the scenes to campaign data
-- Do ads that show the product in the first 3 seconds get more clicks?
WITH early_product AS (
SELECT DISTINCT ad_id
FROM scene_matches
WHERE question = 'a person holding the product up to the camera'
AND start_s < 3
AND score >= 0.10 -- the cutoff you checked for this question
)
SELECT
IFF(e.ad_id IS NULL, 'no early product shot', 'product in first 3s') AS opening,
COUNT(*) AS ads,
SUM(p.clicks) / NULLIF(SUM(p.impressions), 0) AS ctr
FROM ad_performance p
LEFT JOIN early_product e ON e.ad_id = p.ad_id
GROUP BY 1;DOCUMENT_ID is kept so an analyst can go from a row back to the exact scene: fetch the document
from Mixpeek to get its thumbnail and a link that plays only that segment.What does it cost?
Indexing is the cost that scales with the library, and it is paid once per video. Video indexing is priced per minute on the pricing page: at $0.066 a minute, a library of 1,000 thirty-second ads is 500 minutes, or about $33, plus $0.012 a minute (about $6) for the transcripts. The Snowflake side is a small query to read the list and a small table of matches. New ads only cost their own minutes when you rerun step 2 on rows added since the last run.
Where do the Snowflake connector and sync fit?
There are two other ways to connect the two systems, and they solve different problems.
provider_type: "snowflake" reads a tableupdated_at-style column. Use it to
make text and metadata rows searchable. The video files themselves come from storage, as in step 2.
FAQ
Can I run the search from inside a SQL query?
Snowflake external functions call an HTTPS endpoint through an API integration, and the connector's external function module wraps the Mixpeek call for that. Most teams start with the batch pattern above because a table of matches is cheaper to query a thousand times than a thousand API calls, and it keeps a record of what each question returned on a given day.
Do I need to move my videos?
Your files stay where they are. Mixpeek fetches each one from the URL or bucket you point it at to process it, and stores what it derives (scene boundaries, embeddings, transcripts, thumbnails) in its own index.
How do I keep the index current as new ads launch?
Select only rows added since the last run (
WHERE created_at > :last_run) in step 1 and run step
2 again. Rows already indexed are skipped by their idempotency_key. Then rerun step 3 and replace
SCENE_MATCHES.Can I use my own model for the scenes?
Yes. Mixpeek runs any model as an extractor, so a model trained on your products or your brand guidelines can score every scene at ingest, and its output lands on the same documents as the embeddings and joins to Snowflake the same way.