BigQuery guide

Turn questions into BigQuery SQL.

BigQuery uses GoogleSQL and project-qualified tables. Good prompts specify the reporting period and timestamp zone, while a careful review also considers bytes scanned.

TTSQL team Updated September 5, 2026

Try TTSQL free

Before connecting BigQuery

Configure the Google Cloud project and BigQuery dataset. TTSQL's adapter uses GoogleSQL through the BigQuery API and obtains an application-default Google credential through Goth on the TTSQL server. Your deployment operator must configure that cloud identity and its permissions; entering a dataset name alone does not supply authentication.

The identity needs permission to create query jobs in the billing project and read the target data. Set the dataset location correctly. The adapter accepts location and maximum_bytes_billed connection settings; availability of a setting in your interface depends on the deployed configuration.

Give the prompt the right dialect and context

Use backticks around fully qualified names such as `your-project.analytics.orders`. Replace this example project and dataset with yours. GoogleSQL single quotes delimit string values; PostgreSQL's ::date shorthand is not the syntax used here.

These examples use ordered_at as a BigQuery TIMESTAMP. DATE(ordered_at, 'UTC') explicitly defines the calendar day. Use the business's IANA time zone instead of UTC only when that is the agreed reporting definition.

Schema used in these examples

customers(id INTEGER PRIMARY KEY, name TEXT, country TEXT); orders(id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), ordered_at TIMESTAMP, status TEXT, total DECIMAL(12,2)). Each order has one customer. total is the order amount in a single reporting currency; paid means recognized revenue in this teaching dataset. Timestamps use UTC. These are example tables, not tables TTSQL creates.

Translate TEXT and TIMESTAMP to equivalent column types for your engine. Use your own database, schema and table qualifiers. Each example states its reporting grain and sorting rule.

Review these BigQuery details

A LIMIT is not a dependable cost control for a scan. Use appropriate partition filters, inspect estimated bytes with BigQuery's planning or dry-run facilities, and apply a bytes-billed limit where configured.

UNNEST multiplies parent rows when arrays contain several elements. Before summing an order-level total across nested items, determine whether to aggregate the items first or use item-level measures. TTSQL generation does not prove the query's cost or nested-data grain.

Generate, review, then choose whether to run

Select the matching database type and confirm your project's schema context before generating. Review the returned SQL against the schema and the metric you intended. A successful connection test does not prove that every table or operation is authorized.

TTSQL's POST /api/v1 endpoint generates SQL and returns executed: false. Query execution is a separate manual Run action in the workspace, subject to the configured connection and policies. The examples below are educational SELECT statements, not live results from your database.

Official BigQuery references

Check your engine version and account configuration when adapting a function or connection setting.

Worked SQL examples

Read the sample schema and assumptions alongside each query. Adapt names, dates, and definitions to your own database before running SQL.

01 / BigQuery GoogleSQL

Bound the scan and group UTC months

Show paid revenue by UTC month for January through March 2026, oldest first.

SELECT DATE_TRUNC(DATE(ordered_at, 'UTC'), MONTH) AS month,
       SUM(total) AS revenue
FROM `your-project.analytics.orders`
WHERE status = 'paid'
  AND ordered_at >= TIMESTAMP('2026-01-01', 'UTC')
  AND ordered_at < TIMESTAMP('2026-04-01', 'UTC')
GROUP BY month
ORDER BY month;

The explicit timestamp range bounds the requested quarter and can enable partition pruning when ordered_at is the partitioning column. A table partitioned by ingestion time or a different column needs the matching partition filter. DATE_TRUNC operates on the UTC date derived from the timestamp.

02 / BigQuery GoogleSQL

Choose a row with QUALIFY

Find each customer's latest paid order, breaking tied timestamps by greatest ID. Sort by customer ID.

SELECT customer_id, id, total
FROM `your-project.analytics.orders`
WHERE status = 'paid'
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id ORDER BY ordered_at DESC, id DESC
) = 1
ORDER BY customer_id;

QUALIFY filters the window result without an extra query block. Ranking is over all paid history here. Adding a recent date filter changes the question to 'latest within that period' and can exclude customers whose last order is older.

03 / BigQuery GoogleSQL

Compute a paid-order percentage safely

For every customer with an order, show the percentage of all orders whose status is paid. Sort by customer ID.

SELECT customer_id,
       ROUND(100 * SAFE_DIVIDE(COUNTIF(status = 'paid'), COUNT(*)), 2)
         AS paid_order_percent
FROM `your-project.analytics.orders`
GROUP BY customer_id
ORDER BY customer_id;

COUNTIF counts true conditions; COUNT(*) includes every status, including NULL. SAFE_DIVIDE returns NULL rather than an error for a zero denominator, which matters when adapting the pattern to a calendar or customer left join. This measures orders, not unique customer conversion.

Answers before you connect a database.

Does selecting BigQuery guarantee a working database connection?

No. SQL generation uses the project dialect and schema, while connection and execution also depend on deployment prerequisites, credentials, network access and database permissions. Complete the prerequisites described in this guide.

Does the TTSQL API execute BigQuery queries?

No. POST /api/v1 generates SQL and returns executed: false. Review the query; a separate manual Run action is available in the workspace when the connection and policies allow it.

Bring your next question to TTSQL.

Start with one PostgreSQL project and 20 free requests a day. Explore your data, build a report, or integrate the API.

Start free Contact sales