In the rapidly evolving landscape of Generative AI, “model lock-in” is a significant risk for data architects. A model that is state-of-the-art today might be superseded by a more efficient or accurate version next month.
Traditionally, pivoting to a new embedding model meant re-provisioning infrastructure or updating complex Python microservices. However, the integration of BigQuery ML (BQML) with Hugging Face’s Text Embeddings Inference (TEI) has changed the game. TEI provides a purpose-built toolkit for deploying and serving open-source text embeddings with high throughput and low latency, utilizing features like continuous batching and optimized kernels.
By leveraging TEI-backed models directly within BigQuery, data engineers can treat state-of-the-art LLMs as simple SQL functions. In this post, we’ll demonstrate how to build a semantic search engine on 1 million records using the Qwen3 embedding family, and how BigQuery allows you to pivot between model sizes (0.6B vs 8B parameters) without leaving your data warehouse.
The Architecture: SQL-First AI
The workflow leverages BigQuery’s REMOTE WITH CONNECTION to call models hosted via Vertex AI’s model registry or directly via Hugging Face model IDs. This removes the need to move data out of your warehouse into a separate environment for inference.
Step 1: Defining the Remote Models
The flexibility of this approach starts with the model definition. By changing a single string in the hugging_face_model_id option, you can swap between a lightweight, high-throughput model and a heavy, high-dimensional model.
-- Deploying the lightweight 0.6B model for speed
CREATE OR REPLACE MODEL `qwen_withbq.qwen_embedding_model_06B`
REMOTE WITH CONNECTION `bq-demobox.US.vertex-us`
OPTIONS (hugging_face_model_id = 'Qwen/Qwen3-Embedding-0.6B');
-- Deploying the robust 8B model for high-fidelity semantics
CREATE OR REPLACE MODEL `qwen_withbq.qwen_embedding_model_8B`
REMOTE WITH CONNECTION `bq-demobox.US.vertex-us`
OPTIONS (hugging_face_model_id = 'Qwen/Qwen3-Embedding-8B');
Tuning for Performance and Scale
While the statements above use the default endpoint configurations, BigQuery ML gives you full control to tune the underlying Vertex AI infrastructure to meet your latency and throughput needs. You can specify GPU-accelerated machine types, adjust replica counts for autoscaling, and configure idle timeouts directly in the SQL statement.
For example, to deploy the 8B model on a specific GPU machine type with a minimum of 2 replicas to handle high query volume, your statement would look like this:
CREATE OR REPLACE MODEL `bq-demobox.qwen_withbq.qwen_embedding_model_8B_tuned`
REMOTE WITH CONNECTION `bq-demobox.US.vertex-us`
OPTIONS (
hugging_face_model_id = 'Qwen/Qwen3-Embedding-8B',
machine_type = 'g2-standard-12', -- Specify GPU machine type
min_replica_count = 2, -- Ensure minimum baseline capacity
max_replica_count = 5, -- Allow autoscaling up to 5 replicas
endpoint_idle_ttl = INTERVAL 8 HOUR -- Automatically undeploys the model from the endpoint after 8 hours of inactivity
);
What happens under the hood? Executing this SQL command automatically provisions a Vertex AI Endpoint for you. If you navigate to the Vertex AI section of your Google Cloud Console, you will see the endpoints spun up and managed entirely by BigQuery.
This gives you a dedicated, production-grade inference endpoint without writing a single line of infrastructure code. You can read more about the CREATE REMOTE MODEL syntax in the official documentation.
During my experimentation the 0.6B model deployment took approx ~12 mins and 8B parameter one took approx ~35 mins.
Step 2: Generating Embeddings at Scale
Using the Hacker News public dataset (approx. 1M rows), we generate embeddings directly within a table creation statement. The ML.GENERATE_EMBEDDING function handles the batching and remote calls automatically.
CREATE OR REPLACE TABLE `your_project.qwen_embeddings.hn_embeddings` AS
SELECT * FROM ML.GENERATE_EMBEDDING(
MODEL `your_project.qwen_embeddings.model_06B`,
(SELECT id, title, text AS content FROM `bigquery-public-data.hacker_news.full` TABLESAMPLE SYSTEM (2.5 PERCENT)),
STRUCT(TRUE AS flatten_json_output)
);
Monitoring Embedding Performance in Real-Time
One of the major benefits of using BigQuery ML with Vertex AI endpoints is observability. Even though you are generating embeddings via a SQL query, you have full visibility into how the model endpoint is performing under the hood.
While your ML.GENERATE_EMBEDDING query is running, you can navigate to the Vertex AI Endpoints dashboard in the Google Cloud Console to view real-time performance metrics for your model. Key metrics to monitor include:
- Prediction Count & Latency: Track the volume of requests per second and monitor server-side computation time to ensure the endpoint isn’t bottlenecking.
- CPU and Accelerator Utilization: Verify that your chosen machine type (e.g., g2-standard-12) is effectively utilizing its GPU memory. If utilization is pegged at 100%, you may need to increase your max_replica_count.
- Prediction Error Percentage: Spot any degraded responses or bad payloads early in the batching process.
By leveraging Cloud Monitoring, you can make data-driven decisions about whether to scale up the compute instances or scale down to save costs once the embedding generation process is complete.
Step 3: Vector Indexing for Performance
Once the embeddings are stored, BigQuery’s VECTOR_SEARCH requires an index to perform efficiently at scale. We use an IVF (Inverted File) index with COSINE distance, which is standard for semantic similarity.
CREATE VECTOR INDEX hn_search_idx
ON `your_project.qwen_embeddings.hn_embeddings`(ml_generate_embedding_result)
OPTIONS(index_type='IVF', distance_type='COSINE');
Step 4: Execution & The Ability to Pivot
The table below illustrates the relative execution metrics observed during the trial. These figures represent the trade-offs engineers must consider when choosing a model size:
Model Size | Dimension | Row Count | Relative Slot Time
------------- | --------- | --------- | -----------------------------------
Qwen3-0.6B | 1024 | 500 | 1x (Baseline)
Qwen3-8B | 4096 | 500 | ~235x slower
Qwen3-0.6B | 1024 | 1,000,000 | ~2,000x baseline (scales linearly)
The 0.6B model provides a 1024-dimension vector with significantly lower slot consumption. For many RAG (Retrieval-Augmented Generation) use cases, this is the sweet spot. However, if your domain requires the nuanced semantic understanding of the 4096-dimension 8B model, be prepared for processing times that are over 200x longer. Fortunately, the pivot is as simple as updating your SQL query — you don’t need to re-engineer the pipeline, only the model reference.
Step 5: Performing Semantic Search
To find the most relevant Hacker News posts about “new programming languages,” we generate an embedding for the query on the fly and join it against our indexed table.
WITH query_embedding AS (
SELECT ml_generate_embedding_result AS q_embed
FROM ML.GENERATE_EMBEDDING(
MODEL `your_project.qwen_embeddings.model_06B`,
(SELECT "What are the most promising new programming languages?" AS content)
)
)
SELECT base.title, distance
FROM VECTOR_SEARCH(
TABLE `your_project.qwen_embeddings.hn_embeddings`,
'ml_generate_embedding_result',
TABLE query_embedding,
top_k => 10,
distance_type => 'COSINE');
Key Takeaways for Architects
- Reduced Tool Sprawl: By using BQML, you eliminate the need for a separate vector database and a separate inference engine for embeddings. Your data, your models, and your search index live in one governed environment.
- Model Agility: The use of Hugging Face IDs within BQML means you can test the latest Open Source models within minutes of their release.
- Cost/Performance Transparency: As shown in the metrics, the difference in “Slot Time” between 0.6B and 8B models is non-trivial. BigQuery gives you the visibility to make data-driven decisions on whether the increased accuracy of a larger model justifies the compute cost.
- Infrastructure as Code: Since these models are defined via SQL (DDL), they can be easily versioned and managed through your existing CI/CD and data mesh pipelines.
Additional Resources
- Hugging Face Text Embeddings Inference (TEI): Learn more about the underlying technology that powers high-throughput embedding serving.
- BigQuery ML Documentation: Official guide on running OSS models.
- VertexAI Endpoint Monitoring
Happy Querying!
Future-Proofing Your Vector Search: Swapping Open Source Models in BigQuery with Zero Friction was originally published in Google Cloud – Community on Medium, where people are continuing the conversation by highlighting and responding to this story.
Source Credit: https://medium.com/google-cloud/future-proofing-your-vector-search-swapping-open-source-models-in-bigquery-with-zero-friction-f3a804eb366e?source=rss—-e52cf94d98af—4
About the Author
