How to Send Email from BigQuery with Volanea

BigQuery is excellent at finding the rows that need attention, but it is not an email-sending application. To send email from BigQuery reliably, use a BigQuery remote function to invoke a protected Cloud Run service, then let that server-side service call Volanea’s REST API with a verified sender and a stable idempotency key.

This guide shows the production-minded route: a GoogleSQL query is the trigger, BigQuery batches matching rows into an HTTP request for Cloud Run, and Cloud Run maps those values into POST /v1/send at Volanea. There is no native Volanea app, Marketplace listing, or one-click BigQuery plugin involved.

The important constraint: BigQuery does not emit row-created email events

BigQuery is a data warehouse, not an event-driven CRM or automation platform. A new row appearing in a table does not, by itself, cause BigQuery to post a webhook or send an email. Likewise, a CREATE TABLE, INSERT, or streaming insert is not a built-in transactional-email trigger.

The concrete trigger in this integration is a GoogleSQL query job that calls a BigQuery remote function. The query can be started manually, by a scheduled query, by a workflow, or by another trusted service that submits a BigQuery job. When the query evaluates send_email(...) for a selected row, BigQuery invokes the configured Cloud Run endpoint.

That distinction matters operationally. The email is triggered by a query evaluating a row, not by a table mutation happening somewhere in the background. You therefore control sending with SQL predicates, durable event IDs, and a send-status table rather than hoping that every data change deserves a message.

A good mental model is:

1. A source system writes business events or eligible records to BigQuery.
2. A query job selects only records that are ready to notify.
3. The query invokes a remote function once for each selected record, although BigQuery can batch multiple function arguments into one HTTP request.
4. Cloud Run validates and maps each function call into a Volanea email request.
5. Cloud Run returns one reply per BigQuery call, and the query writes the outcome to an audit table.

This model is intentionally more deliberate than a generic “new row equals new email” rule. A table can receive late data, corrections, retries, duplicate records, or backfills. Transactional email should follow a business event with a durable identity, not an incidental warehouse write.

Architecture for sending email from BigQuery

The recommended path has three services with separate responsibilities:

• BigQuery chooses recipients and supplies the event data through GoogleSQL.
• Cloud Run is the private relay that receives BigQuery’s remote-function request, validates it, reads the Volanea secret, and makes the outbound API request.
• Volanea accepts the transactional message at POST /v1/send, applies sending and suppression logic, and dispatches it through your authenticated sending setup.

BigQuery remote functions are designed for this kind of boundary. BigQuery invokes an HTTP endpoint hosted on Cloud Run or Cloud Run functions through a Cloud resource connection. The remote function is called from SQL, but BigQuery does not give your SQL code a general-purpose fetch() function for calling arbitrary public APIs directly.

That is why Cloud Run is not unnecessary middleware. It is the security and protocol boundary between analytical SQL and an email provider. It is where you keep the Volanea key, reject malformed rows, create deterministic idempotency keys, format customer-facing content, and decide which provider errors deserve a retry.

The request path looks like this:

GoogleSQL query job
  -> BigQuery remote function
    -> authenticated Cloud Run service
      -> POST https://api.volanea.com/v1/send
        -> Volanea transactional email pipeline

Keep the relay small. It should not be a second marketing platform or an unbounded template engine. Its job is to turn an explicitly selected business event into one well-defined message and to produce an answer BigQuery can store.

Choose an email event table before writing the function

Do not point a remote function at an arbitrary customer table and immediately send every row. First create a small, explicit event contract. The table below represents account-renewal reminders that are ready for evaluation.

CREATE TABLE `PROJECT_ID.notifications.renewal_email_events` (
  event_id STRING NOT NULL,
  recipient_email STRING NOT NULL,
  recipient_name STRING,
  renewal_date DATE NOT NULL,
  plan_name STRING NOT NULL,
  created_at TIMESTAMP NOT NULL,
  sent_at TIMESTAMP,
  send_result STRING
);

event_id is the most important column. It should be generated by the application that owns the business event, such as renewal:subscription_8472:2026-09-30, rather than by the query run. That value gives the email a stable identity across query retries, scheduled-query reruns, and BigQuery remote-function retries.

A separate event table is safer than deriving every send from a mutable subscriptions table. If a customer updates their display name, a row in the subscription table may change without meaning “send another renewal reminder.” An event table lets the upstream application write one meaningful record, while your BigQuery query determines whether that event has already been handled.

Before sending, validate that the record is truly eligible. Typical conditions include:

• the recipient has an email address and the relevant consent or contractual basis;
• the event has not already been marked as sent;
• the renewal date is within the intended notification window;
• the recipient is not a test record, internal account, or suppressed address;
• the subject, locale, currency, and dates have been prepared before they reach the relay.

BigQuery is powerful for this filtering. Use it to calculate segments, find exceptions, and enrich data. Do not use it to make an email notification look like a side effect of every raw ingestion record.

Create the Cloud Run relay and BigQuery connection

A remote function needs a Cloud resource connection. BigQuery uses the service account associated with that connection to invoke the Cloud Run endpoint. The connection must be in the same BigQuery location as the dataset containing the remote function.

The following SQL creates a Cloud resource connection in the US multi-region. Replace the project and location for your environment. If your dataset is in EU, use EU; do not mix locations casually.

CREATE CONNECTION `PROJECT_ID.US.volanea_email_connection`
CLOUD_RESOURCE;

After creating the connection, identify its generated service account and grant that principal permission to invoke your Cloud Run service. Keep the Cloud Run service authenticated rather than making it publicly invokable. The BigQuery connection is the caller identity Cloud Run should trust for this route.

Next, deploy a Cloud Run service that accepts POST requests. The service needs two ordinary configuration values:

• FROM_EMAIL, such as Billing <billing@updates.example.com> or the verified sender format your Volanea setup uses;
• VOLANEA_API_KEY, supplied from Secret Manager rather than committed source code or a plaintext deployment command.

The Volanea key belongs in Google Secret Manager and should be exposed only to the Cloud Run runtime service account. Cloud Run can make a Secret Manager value available as an environment variable or a mounted file. For an environment variable, pin a secret version during deployment so a secret change does not unexpectedly alter running instances.

Use a dedicated Volanea key for this integration where possible. That makes rotation and incident response much cleaner: you can revoke the key used by the BigQuery relay without interrupting unrelated applications.

You also need a verified sending domain before production traffic. The FROM_EMAIL domain must match a sender you have configured for Volanea. Treat the sender identity as infrastructure: authenticate it, test it with a real mailbox, and do not let a SQL query choose arbitrary From addresses.

For endpoint, authentication, and sender setup details, keep the implementation aligned with the current email API reference and setup guides.

The actual payload BigQuery sends to Cloud Run

A BigQuery remote function does not send one custom JSON object per SQL row. BigQuery sends a POST body containing a calls array. Each calls item is an array of the remote function’s positional SQL arguments.

For a remote function declared with five STRING arguments, BigQuery sends a body shaped like this:

{
  "requestId": "12345678901234567890",
  "caller": "...",
  "sessionUser": "analyst@example.com",
  "userDefinedContext": {},
  "calls": [
    [
      "renewal:subscription_8472:2026-09-30",
      "maya@example.com",
      "Maya Chen",
      "Growth",
      "2026-10-15"
    ],
    [
      "renewal:subscription_9184:2026-09-30",
      "omar@example.com",
      "Omar Diaz",
      "Starter",
      "2026-10-11"
    ]
  ]
}

Your service must return a replies array with exactly one result in the same order for every value in calls:

{
  "replies": [
    "accepted:renewal:subscription_8472:2026-09-30",
    "accepted:renewal:subscription_9184:2026-09-30"
  ]
}

This batching behavior is why the relay processes calls, not just a hypothetical single event object. BigQuery can submit multiple rows in the same invocation. The function must preserve ordering even if the Volanea requests are performed concurrently.

Remote functions accept scalar SQL types and JSON, but they do not accept STRUCT or ARRAY arguments. For this email flow, simple positional strings are easier to validate and audit. Convert dates to a stable format in SQL, such as CAST(renewal_date AS STRING), rather than asking the service to guess how a timestamp was serialized.

Working Cloud Run code: map BigQuery calls to Volanea

The following Node.js service accepts BigQuery’s remote-function payload, validates its five arguments, maps each call to Volanea’s single-message endpoint, and returns a matching reply. It uses the business event_id as the Idempotency-Key, which makes the logical send stable even if BigQuery repeats an HTTP call.

import express from "express";

const app = express();
app.use(express.json({ limit: "1mb" }));

const port = process.env.PORT || 8080;
const volaneaApiKey = process.env.VOLANEA_API_KEY;
const from = process.env.FROM_EMAIL;

function requiredString(value, name) {
  if (typeof value !== "string" || value.trim() === "") {
    throw new Error(`${name} must be a non-empty string`);
  }
  return value.trim();
}

function escapeHtml(value) {
  return value
    .replaceAll("&", "&")
    .replaceAll("<", "&lt;")
    .replaceAll(">", "&gt;")
    .replaceAll('"', "&quot;")
    .replaceAll("'", "&#039;");
}

async function sendRenewalEmail(call) {
  const [eventId, recipientEmail, recipientName, planName, renewalDate] = call;

  const id = requiredString(eventId, "event_id");
  const to = requiredString(recipientEmail, "recipient_email");
  const name = requiredString(recipientName, "recipient_name");
  const plan = requiredString(planName, "plan_name");
  const renewal = requiredString(renewalDate, "renewal_date");

  if (!to.includes("@")) {
    throw new Error("recipient_email is not email-shaped");
  }

  const safeName = escapeHtml(name);
  const safePlan = escapeHtml(plan);
  const safeRenewal = escapeHtml(renewal);

  // BigQuery positional arguments become this Volanea send payload.
  const payload = {
    from,
    to: [to],
    subject: `Your ${plan} plan renews on ${renewal}`,
    html: `<p>Hi ${safeName},</p><p>Your <strong>${safePlan}</strong> plan renews on <strong>${safeRenewal}</strong>.</p>`,
    text: `Hi ${name},\n\nYour ${plan} plan renews on ${renewal}.`,
    tags: ["bigquery", "renewal-reminder"]
  };

  const response = await fetch("https://api.volanea.com/v1/send", {
    method: "POST",
    headers: {
      "Authorization": `Bearer ${volaneaApiKey}`,
      "Content-Type": "application/json",
      "Idempotency-Key": id
    },
    body: JSON.stringify(payload)
  });

  const responseText = await response.text();

  if (!response.ok) {
    throw new Error(`Volanea returned ${response.status}: ${responseText}`);
  }

  return `accepted:${id}`;
}

app.post("/bigquery-send-email", async (req, res) => {
  if (!volaneaApiKey || !from) {
    console.error("Missing VOLANEA_API_KEY or FROM_EMAIL configuration");
    return res.status(500).json({ errorMessage: "Server is not configured" });
  }

  const { calls } = req.body ?? {};
  if (!Array.isArray(calls)) {
    return res.status(400).json({ errorMessage: "Expected BigQuery calls array" });
  }

  try {
    const replies = await Promise.all(calls.map(sendRenewalEmail));
    return res.status(200).json({ replies });
  } catch (error) {
    console.error("Email relay failed", error);
    return res.status(500).json({ errorMessage: error.message });
  }
});

app.listen(port, () => {
  console.log(`Listening on port ${port}`);
});

The essential field mapping is visible in one place:

The relay escapes interpolated HTML because analytics data is still data from a security perspective. A display name, plan label, or other field can contain markup characters accidentally or maliciously. Better yet, move stable production content to a Volanea template and supply only reviewed variables.

Create and invoke the BigQuery remote function

Once the Cloud Run endpoint is deployed and the connection service account can invoke it, create the BigQuery routine. This example returns a STRING reply for each send attempt.

CREATE OR REPLACE FUNCTION `PROJECT_ID.notifications.send_renewal_email`(
  event_id STRING,
  recipient_email STRING,
  recipient_name STRING,
  plan_name STRING,
  renewal_date STRING
)
RETURNS STRING
REMOTE WITH CONNECTION `PROJECT_ID.US.volanea_email_connection`
OPTIONS (
  endpoint = 'https://YOUR_CLOUD_RUN_SERVICE_URL/bigquery-send-email'
);

The trigger is now a query that calls this function. Start with a narrow test predicate and a real mailbox that your team controls.

SELECT
  event_id,
  `PROJECT_ID.notifications.send_renewal_email`(
    event_id,
    recipient_email,
    COALESCE(recipient_name, 'there'),
    plan_name,
    CAST(renewal_date AS STRING)
  ) AS send_result
FROM `PROJECT_ID.notifications.renewal_email_events`
WHERE sent_at IS NULL
  AND event_id = 'renewal:subscription_8472:2026-09-30';

For production, do not rely on a plain SELECT as your whole audit system. Write outcomes into a send ledger, or use an orchestration pattern that stores selected event IDs and results. You want an operator to be able to answer three questions later: which business event produced the message, which query run selected it, and what response did the relay receive?

One approach is to first insert eligible event IDs into a run-specific queue table. A second controlled query invokes the remote function for those rows, and a follow-up job records successful outcomes. This separation provides a reviewable set of messages before you create external side effects.

Keep the Volanea API key out of BigQuery and the browser

The Volanea secret key must live on the Cloud Run side, not in a SQL literal, routine definition, scheduled-query configuration, frontend application, or a client-visible automation tool field.

Putting the key in SQL is risky for several reasons. Query text can be visible in job history, copied into tickets, exported by audit tooling, or exposed to users who can inspect saved queries. A remote function definition is also not a secret vault. It is infrastructure configuration that more people may need to read than should ever be able to send mail.

The right ownership boundary is:

• BigQuery has permission to invoke the Cloud Run relay through the Cloud resource connection.
• Cloud Run has permission to read only the necessary secret from Secret Manager.
• Cloud Run uses the key in an HTTPS Authorization: Bearer header when it calls Volanea.
• No browser, SQL analyst, or recipient ever receives the Volanea key.

Use least privilege on both sides. Grant the BigQuery connection service account only Cloud Run invocation permission for this relay, not broad project access. Grant the Cloud Run runtime service account access only to the secret it needs. Log message identifiers and event IDs, but never log the Authorization header or raw secret values.

Also verify recipients before sending important operational mail. A lightweight syntax and deliverability check can prevent avoidable bounces; Volanea provides a free email address verification tool for checking addresses before they become part of an automated workflow.

When this breaks: retries, timeouts, and incomplete payloads

Email integrations should be designed around the fact that “request succeeded” and “message was accepted” can become ambiguous during failures. BigQuery remote functions can repeat requests even after a successful response because of transient network or internal errors. That makes duplicate sends a real risk if every call gets a newly generated identifier.

BigQuery retries can create duplicate sends

A remote function is not an exactly-once messaging system. BigQuery may resend the same call if it cannot safely determine whether the previous request completed. If your relay generates a random idempotency key every time, Volanea sees each retry as a new message and recipients can receive duplicates.

Use the stable business event_id as the Idempotency-Key exactly as the example does. Do not base it on requestId, the current timestamp, a Cloud Run request ID, or a random UUID created inside the handler. Those values identify delivery attempts, not the email event itself.

There is a second layer of protection worth adding for critical notifications: a durable send ledger with a uniqueness constraint on event_id. The ledger lets your own system detect that a send was accepted, even if a query is re-run later. The Volanea idempotency key protects the provider call; the ledger protects your workflow design.

A webhook-style timeout leaves an uncertain result

The remote-function request is synchronous. If Cloud Run takes too long, returns a non-success response, or loses the connection after Volanea accepted the request, BigQuery can treat the invocation as failed. Retrying blindly is safe only when the idempotency key remains stable.

Keep the relay fast. It should validate inputs, call Volanea, return the reply, and avoid slow secondary work such as expensive joins, reporting writes, or third-party enrichment. If you need a complex workflow, have Cloud Run enqueue a durable job and make the worker responsible for final delivery. That changes the SQL result from “email accepted” to “job accepted,” but it gives you a more controllable failure boundary.

Alert on repeated 5xx responses from the relay and inspect Cloud Run logs by event_id. Do not respond to a timeout by rerunning a wide historical query without first confirming the idempotency and send-ledger behavior.

Fields can be missing or unexpectedly null

BigQuery source data is often less complete than an email template expects. A customer may have no display name, a plan value may be null, or a query change may rename a column while leaving the remote function call behind.

Do not silently turn required fields into the string null. Validate each required argument in Cloud Run. In SQL, use intentional defaults only for fields where a default is genuinely safe, such as COALESCE(recipient_name, 'there'). For a missing email address, event ID, renewal date, or sender configuration, fail the call and route the record to an exception process rather than mailing an incomplete message.

If your source data comes from a connector, export, or limited product tier, confirm which fields are actually available before designing a template around them. The integration contract should document required fields, optional fields, defaults, and the expected format for each value.

Authentication and sender failures need different treatment

A 401 or 403 response from Volanea is not a transient email failure. It usually means the key is missing, revoked, malformed, or unauthorized. Retrying the same request will not help until you fix configuration.

A sender or domain configuration error is similarly a deployment problem, not a recipient problem. Test a verified FROM_EMAIL in a staging environment before enabling a scheduled query. Keep sender setup separate from row-level error handling so a broad bad configuration does not create thousands of failed attempts.

Operational practices that make this maintainable

Treat the SQL function call as an external side effect. That means query reviews, production change control, and backfill planning should apply just as they would to a payment or account-change job.

Start with a low-volume canary. Select a handful of internal records, confirm that Cloud Run receives the expected calls format, check the Volanea acceptance response, and inspect the rendered HTML and plain-text message in real clients. Then enable a limited scheduled run before moving to the full population.

Use tags such as bigquery, renewal-reminder, and a workflow-specific label to make investigation easier later. Avoid putting email addresses, account numbers, or sensitive personal data in tags. The stable event ID belongs in your controlled logs and send ledger; tags should remain short operational categories.

For high-volume sends, think carefully about query shape. A remote function may batch calls, but an email request is still an external side effect for every selected row. Do not accidentally scan and invoke the function over an entire historical table because a WHERE sent_at IS NULL condition was omitted. Stage eligible IDs, apply a limit during tests, and require an explicit run date or batch identifier.

Finally, distinguish transactional email from campaigns. A renewal reminder tied to one customer’s known subscription state fits this pattern. A broad announcement to a marketing segment may require different consent handling, unsubscribe behavior, scheduling, and approval processes. BigQuery can calculate either audience, but the messaging workflow should match the purpose.

Alternatives when a remote function is not the right fit

Use the Cloud Run remote-function route when the decision naturally happens in GoogleSQL and you need controlled per-row transactional sends. It is particularly useful for exception alerts, account notices, data-quality escalations, renewal reminders, and operational reports with a known recipient.

For low-code orchestration, a middleware workflow can also work: a scheduler or application queries BigQuery, sends selected rows to Zapier or Make, and that workflow calls Volanea’s REST endpoint. This can be appropriate for modest volume and simple business rules, but it still needs server-side secret handling, an event ID, and duplicate protection.

For large batches, a better architecture is often BigQuery query -> queue or table export -> worker service -> Volanea batch or single-message sends. The worker can apply rate control, durable retries, dead-letter handling, and richer auditing without holding a BigQuery remote-function request open. The key principle remains the same: BigQuery selects data; a trusted backend owns email delivery.

FAQ

Can BigQuery send email directly with SQL?

Not as a direct arbitrary HTTP call from standard GoogleSQL. The supported native extension point is a remote function, which invokes a Cloud Run or Cloud Run functions endpoint through a Cloud resource connection. That endpoint then calls Volanea.

What exactly triggers the email?

A GoogleSQL query job that evaluates the remote function for an eligible row triggers the send. A table row being created or updated does not automatically invoke Volanea.

Why do I need an idempotency key?

BigQuery can repeat remote-function requests after network or internal errors. Using the stable business event ID as Volanea’s Idempotency-Key prevents a retry from becoming a second logical email send.

Where should the Volanea API key be stored?

Store it in Google Secret Manager and expose it only to the Cloud Run service at runtime. Never put it in SQL, a browser application, a public repository, or a client-visible automation configuration.

Can I use this for campaign email?

You can use BigQuery to build campaign audiences, but a per-row remote function is best reserved for targeted transactional or operational mail. For a larger campaign, use a queue or worker workflow with explicit consent, batching, scheduling, and audit controls.