How to create a view in BigQuery: CREATE VIEW, cost and limits

Create a view with CREATE VIEW

The examples use the public thelook_ecommerce dataset, so they run in any project. Replace my_project.reporting with a dataset of yours, in the same location as the tables the view reads (here, US).

CREATE OR REPLACE VIEW `my_project.reporting.daily_orders`
OPTIONS (description = 'Orders per day and status, thelook_ecommerce')
AS
SELECT
  DATE(created_at) AS order_date,
  status,
  COUNT(*) AS orders,
  COUNT(DISTINCT user_id) AS customers
FROM `bigquery-public-data.thelook_ecommerce.orders`
GROUP BY order_date, status;

The dry run returned the view’s schema and 0 bytes processed: creating a view reads no data.

Three variants, and how they behave:

• CREATE VIEW fails if a view with that name already exists.
• CREATE OR REPLACE VIEW overwrites it. This is also how you edit a view in SQL: rewrite the whole statement with the new query.
• CREATE VIEW IF NOT EXISTS does nothing if the view exists. You cannot combine the two: the dry run of CREATE OR REPLACE VIEW IF NOT EXISTS returned “CREATE VIEW cannot have both OR REPLACE and IF NOT EXISTS”.

Two other ways to create the same view, per Google’s creating views page (read in October 2026):

• Console: run the query, then Save > Save view, and pick the project, dataset and name. A view saved this way cannot get a description, label or expiration at creation; add them afterwards.
• bq CLI: bq mk --use_legacy_sql=false --view 'SELECT ...' reporting.daily_orders. Without --use_legacy_sql=false the query is read as legacy SQL.

Query a view like a table

SELECT order_date, status, orders, customers
FROM `my_project.reporting.daily_orders`
WHERE order_date = '2024-01-15'
ORDER BY orders DESC;

The result above is the view’s body run with the same filter, which is what BigQuery executes when you read the view. thelook_ecommerce is generated data, so your counts may differ slightly on a later date.

ORDER BY is allowed inside a view (Google’s own bq mk example uses one), but put the sort in the query that reads the view: that is the result the reader sees.

A view stores the query, so every read re-runs it

Google’s wording: “the query that defines the view is run each time the view is queried”, and queries are billed on the data “referenced directly or indirectly by the top-level query” (introduction to views, read in October 2026). A view adds no storage cost and no extra charge. It does hide how much data a short-looking query reads.

To show that, we dry-ran queries against a real view in our project, SearchPerformanceViews.AllSearchQueries, which groups Search Console data by month and keyword:

What the numbers say:

1. Reading the view costs exactly what running its SQL costs.
2. Selecting fewer columns helps: BigQuery dropped the columns the outer query does not need.
3. LIMIT saves nothing.
4. The month filter barely helped, because the view turns data_date into a text Month, and a filter on that text cannot skip partitions. Asking the same question on the date column read 55 times less data.

The trap: a view that hides the partition column makes every filtered query a full scan. Keep the raw date column in the view’s output so readers can filter on it.

Read a view’s SQL: there is no SHOW CREATE VIEW

SHOW CREATE VIEW is a MySQL statement. In BigQuery the dry run returned “Syntax error: Unexpected keyword CREATE”. The SQL is in INFORMATION_SCHEMA, per dataset:

SELECT table_name, table_type, ddl
FROM `owox-d-ikrasovytskyi-001.SearchPerformanceViews.INFORMATION_SCHEMA.TABLES`
WHERE table_type = 'VIEW';

The ddl column is a ready-to-run CREATE VIEW statement: copy it, change CREATE to CREATE OR REPLACE, edit the query, run it. For the query alone, use INFORMATION_SCHEMA.VIEWS:

SELECT table_name, use_standard_sql, LENGTH(view_definition) AS sql_length
FROM `owox-d-ikrasovytskyi-001.SearchPerformanceViews.INFORMATION_SCHEMA.VIEWS`;

use_standard_sql = YES means the view is GoogleSQL, not legacy SQL. Replace the project and dataset with yours. The region-wide form, region-us.INFORMATION_SCHEMA.VIEWS, needs permission to list tables in every dataset of the project; our account lacked it and got “Access Denied”, so the per-dataset form is the safer default.

Authorized views: share the result, not the table

A normal view does not grant access to anything. Someone who queries it needs read access to the view and to every table it reads. An authorized view removes the second requirement: the source dataset trusts the view itself, so users “can’t access the source dataset directly” yet can query the view (authorized views, read in October 2026). Typical use: give the marketing team daily totals without the customer-level table behind them.

Google’s steps:

1. Keep the source table in its own dataset.
2. Create a second dataset for the view, in the same region as the source.
3. Create the view there, selecting only the columns and rows to share.
4. Give users roles/bigquery.jobUser on the project where they run queries.
5. Give users roles/bigquery.dataViewer on the view’s dataset, not the source.
6. On the source dataset, open Sharing > Authorize views and add the view as PROJECT_ID.DATASET_ID.VIEW_NAME.

Limits from the same page: a dataset’s access list holds up to 2,500 authorized resources (views, datasets and routines together), and only a data owner can change an authorized view’s SQL. The roles are explained in BigQuery IAM roles and permissions.

Views, materialized views and tables

On refresh, Google’s manage page (read in October 2026) says BigQuery “attempts to start a refresh within 5 minutes of a change in the base table”, and that by default materialized views “are refreshed no more often than every 30 minutes”. You can also refresh one manually at any time. When a materialized view fits, how to build one is in BigQuery materialized views.

Rule of thumb: start with a view. Turn it into a materialized view when the same heavy aggregation is read many times a day, and into a scheduled table when the SQL is too complex for a materialized view.

View limits that return errors

Each rule below is from Google’s introduction to views and quotas page (read in October 2026). Where the error text is quoted, it came from a dry run.

A wildcard table inside a view is allowed. This dry run passed, and the body returned real rows:

CREATE OR REPLACE VIEW `my_project.reporting.gsod_1990s` AS
SELECT CONCAT('199', _TABLE_SUFFIX) AS year, COUNT(*) AS readings
FROM `bigquery-public-data.noaa_gsod.gsod199*`
GROUP BY year;

(The last three years shown, from the body run with ORDER BY year DESC LIMIT 3.) What is not allowed is the reverse: a wildcard table pattern that matches a view’s name.

The parameter rule is the one that hurts most in reporting. The usual workaround is to leave the filter out of the view, expose the column, and let the reading query add WHERE order_date >= @start_date.

Where views go wrong in reports

A view gives one definition of “orders per day” a single home. The failure is quiet: someone replaces it with CREATE OR REPLACE VIEW, a column is renamed or a filter changes, and every report that reads it changes on its next refresh, with no error. The job history keeps who ran the CREATE OR REPLACE for 180 days, but nothing tells the reports’ readers that the definition changed.

In OWOX Data Marts the same SQL is published as a Data Mart with a description for each column, and Sheets, Data Studio and AI assistants read that one data model. OWOX Data Marts runs your SQL as written, in your own BigQuery project, and does not transform the data.

Google BigQueryBigQuery window functions: OVER, PARTITION BY, frames and LAG, with examplesOctober 16, 2026→Google BigQueryBigQuery Partitioned Tables: How to Create and Query ThemOctober 6, 2026→Data AnalyticsComparing BigQuery, Redshift, Snowflake & More in 2025July 11, 2024→

See all articles →