BigQuery Engineering

Quote

“BigQuery separates storage from compute. That single architectural decision changes everything about how you design tables, partition data, and pay for queries.”

— Jordan Tigani, founding engineer of BigQuery

Summary

BigQuery Engineering is the operational follow-on to the fundamentals note: it focuses on reusable BigQuery objects, storage-layout and cost controls, dimensional-history patterns, and write-path constraints inside a serverless warehouse where bytes scanned and DML quotas matter as much as query correctness.

Metadata and reusable query surfaces

covers INFORMATION_SCHEMA, regular and materialized views, stored procedures for multi-statement scripting, table functions for parameterized reads, and notebook-specific scripting limitations

Storage and cost optimization

covers partitioning, clustering, search indexes, materialized-view refresh trade-offs, execution-plan awareness, dry runs, and why SELECT * or missing partition filters directly raise cost

Dimensional and data-quality patterns

covers SCD Type 1 versus Type 2 modeling, LAG-based gap detection, ROW_NUMBER() deduplication, and lineage columns for medallion-style freshness and audit tracking

Write-path and transaction operations

covers snapshot-based transaction semantics, multi-statement transaction limits, batch versus streaming loads, Storage Write API usage, and cleanup of demo objects

Operations and safety

Warnings: INFORMATION_SCHEMA as primary metadata surface, DML quotas, dry-run necessity, lab-only demo objects, ADC connection behavior, view re-scan cost, missing partition filters, transaction limits, and SCD Type 1 history loss

Recommendations table: 6 defaults covering partition-plus-cluster design, DML monitoring, staging-load flow, demo dataset isolation, dry-run usage, and audit columns

Troubleshooting: 5 failure modes covering quotaExceeded, unexpectedly expensive view reads, failed partition pruning, notebook variable-scope surprises, and table-function freshness misunderstandings

Glossary

Materialized view

A BigQuery object that stores precomputed query results and can transparently satisfy future queries from the cached result instead of rescanning base tables.

It matters because repeated dashboard-style aggregations in this note become much cheaper when BigQuery can serve them from a materialized view.

Refresh is not instantaneous

Materialized views are refreshed on BigQuery’s schedule, not on every write. The speed gain comes with potential staleness between refresh events.

Table function

A BigQuery routine created with CREATE TABLE FUNCTION that returns a table result and can accept parameters.

It matters because the note treats table functions as the preferred reusable surface for parameterized read logic.

Read-only parameterization tool

A table function fills the same niche that an inline table-valued function does in SQL Server. It is usually a better fit than a stored procedure when the output should stay queryable like a table.

DML quota

BigQuery’s per-table limit on the number of INSERT, UPDATE, DELETE, and MERGE statements allowed in a day.

It matters because pipeline frequency decisions in the note are constrained by that quota, especially when MERGE is the chosen load primitive.

Quota failure stops writes

When the DML limit is exceeded, BigQuery returns quotaExceeded and the pipeline stops making progress. This is an operational capacity limit, not just a best-practice suggestion.

Storage Write API

BigQuery’s modern ingestion API for high-volume or high-frequency writes, supporting batch and committed modes.

It matters because it is the escape hatch when ordinary DML frequency would exceed per-table quotas.

Different modes solve different problems

Batch mode is aligned with cost-efficient bulk loads, while committed mode targets low-latency ingestion. Choosing the API is really choosing a write pattern.

require_partition_filter

A BigQuery table option that forces queries to include a predicate on the partition column instead of allowing silent full-partition scans.

It matters because the note treats this option as a guardrail against accidental cost blowups on partitioned production tables.

Guardrails must be enabled

Partitioning alone does not protect against an unfiltered scan. Without require_partition_filter, someone can still run an expensive full-table query by mistake.

Snapshot isolation

BigQuery’s default read-consistency model, where each statement sees a stable snapshot of data as of the statement start.

It matters because the note contrasts BigQuery’s fixed concurrency model with SQL Server’s configurable isolation levels and lock hints.

No tuning knob here

BigQuery does not expose the same isolation-level matrix as SQL Server. The engineering question is less “which isolation level?” and more “does BigQuery fit this workload at all?”

INFORMATION_SCHEMA

BigQuery’s standards-based metadata surface for tables, columns, jobs, partitions, storage, and other object details.

It matters because introspection, cost monitoring, and structural verification in this note all flow through INFORMATION_SCHEMA rather than sys.* views.

SQL Server instincts mislead here

Looking for sys.tables or sys.dm_exec_* in BigQuery is the wrong mental model. BigQuery exposes different metadata under a different naming scheme, even when the intent is similar.

Clustering

BigQuery’s within-partition storage ordering by selected columns so the engine can skip blocks whose value ranges do not match the predicate.

It matters because the note pairs clustering with partitioning to reduce scan cost for repeated symbol- or key-based queries.

Clustering is secondary pruning

Clustering works best after partition pruning has already narrowed the search space. Using it as the only optimization lever often leaves too much data in play.

Search index

A BigQuery index type designed for selective text and JSON lookups rather than for general B-tree-style query acceleration.

It matters because the note’s indexing section explains that BigQuery optimization is mostly about storage layout, with search indexes as a narrow exception.

This is not SQL Server indexing

BigQuery does not offer general-purpose nonclustered indexes for arbitrary predicates. Search indexes help specific lookup shapes, not whole-table workload design.

Demo dataset

A non-production BigQuery dataset used to isolate notebook-created objects from the real medallion datasets.

It matters because the note intentionally creates views, procedures, and temporary objects and needs those experiments to stay safe to rerun.

Cleanup is part of the pattern

Creating demo objects without cleanup leaves confusing residue that can affect later runs and readers. The isolation only works when the lifecycle is explicit.

Batch load

A bulk-ingestion pattern where data is loaded in larger chunks instead of row by row or minute by minute.

It matters because the note recommends batch loading as the default BigQuery write strategy when low-latency streaming is unnecessary.

Cheap and predictable

Batch loads align well with BigQuery’s warehouse economics. They avoid DML pressure, simplify validation steps, and make cost behavior easier to reason about.

INFORMATION_SCHEMA Is BigQuery's Primary Introspection

Unlike SQL Server’s sys.* DMVs, BigQuery exposes all metadata through INFORMATION_SCHEMA views:

INFORMATION_SCHEMA.TABLES — table metadata, row count, size

INFORMATION_SCHEMA.COLUMNS — column names, types, nullable

INFORMATION_SCHEMA.JOBS — query history, bytes scanned, cost

INFORMATION_SCHEMA.TABLE_STORAGE — storage bytes per table

INFORMATION_SCHEMA.PARTITIONS — partition metadata

BigQuery DML Has Strict Quotas

Each table allows a maximum of 1,500 DML statements per day (INSERT, UPDATE, DELETE, MERGE combined). A pipeline running MERGE every 5 minutes = 288/day — fine. Every 1 minute = 1,440/day — dangerously close. Streaming inserts (insertAll API) have a separate quota and are not subject to the DML limit.

Safe Pattern

Keep MERGE frequency at one execution per pipeline run (e.g., daily or hourly scheduled queries). For high-frequency writes, switch to the Streaming API (insertAll) or use Storage Write API (batch mode) — both bypass the DML quota entirely. Monitor daily DML usage via INFORMATION_SCHEMA.JOBS.

Always Dry-Run Before Expensive Queries

In bq CLI: bq query --dry_run "SELECT ..." — returns estimated bytes without executing. In Python: job_config.dry_run = True. At $6.25/ TB, a ‘ SE L ECT * ‘ o na 1 TBt ab l ecos t s$ 6.25. Check before you run. See gcp-billing-and-pricing > BigQuery.

Lab Environment Note

Some sections CREATE database objects. All objects are created in a demo schema or use temp tables to avoid modifying the production stoxx schema.

Load the jupysql extension and configure display settings for notebook SQL execution.

%load_ext sql
%config SqlMagic.displaycon = False
%config SqlMagic.displaylimit = 0

Connect to BigQuery project bq-wh-nb using Application Default Credentials (no password).

%sql bigquery://bq-wh-nb

Connecting to ‘bigquery://bq-wh-nb’

BigQuery Uses ADC — No Password

The bigquery:// connection uses Application Default Credentials — no password in the connection string. See gcloud-authentication > The ADC Credential Search Order.

BigQuery uses datasets as the equivalent of SQL Server schemas. The CREATE SCHEMA statement below creates a demo dataset for lab objects — it is idempotent and safe to re-run.

Create the demo dataset if it does not already exist (idempotent).

CREATE SCHEMA IF NOT EXISTS demo
OPTIONS(location="europe-west1")

Views

BigQuery views encapsulate reusable queries as named objects in a dataset. Unlike SQL Server, BigQuery regular views do not cache results — every SELECT against a view re-executes the full underlying query and charges the bytes scanned. For repeated dashboard queries, a materialized view (CREATE MATERIALIZED VIEW) stores the pre-computed result and is automatically refreshed by BigQuery.

Regular Views | Simplify Complex Queries

A view is a saved query — it stores no data and re-executes the underlying query on every SELECT. This means each read from a view incurs the full scan cost of the base tables. Use case: wrap the “latest price per stock” pattern so downstream queries use a clean interface instead of duplicating complex logic.

Views re-scan on every read

Unlike materialized views, regular views offer no caching — BigQuery runs the full query and charges bytes scanned each time. For dashboard queries hit repeatedly throughout the day, consider a materialized view (CREATE MATERIALIZED VIEW) or a scheduled query that writes to a gold-layer table.

Create a view wrapping ROW_NUMBER deduplication logic

When multiple downstream consumers need the “latest price per stock” pattern and duplicating the ROW_NUMBER logic in each query is error-prone. It is typically triggered by identifying repeated use of the same deduplication subquery across notebooks or scheduled queries. GoogleSQL DDL (CREATE OR REPLACE VIEW) in stoxx_gold dataset. State-changing — creates or replaces a named view object. Requires bigquery.tables.create permission. The view stores no data — it re-executes the underlying query on every SELECT. Encapsulate the ROW_NUMBER deduplication pattern behind a clean interface so downstream queries can SELECT * FROM v_latest_prices without knowing the dedup logic.

Create a view that returns the most recent OHLCV row per stock using ROW_NUMBER deduplication.

CREATE OR REPLACE VIEW bq-wh-nb.stoxx_gold.v_latest_prices AS
SELECT symbol, date, `open`, high, low, `close`, volume
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY date DESC) AS rn
    FROM `bq-wh-nb.stoxx_silver.eurostoxx50_ohlcv`
) sub
WHERE rn = 1;

symbol	date	open	high	low	close	volume

The complex ROW_NUMBER pattern is now hidden behind a simple SELECT — downstream queries no longer need to know the dedup logic.

Query the view with a simple SELECT

Whenever you need the latest price per stock — the view hides the deduplication complexity. It is typically triggered by dashboard query, ad-hoc analysis, or any downstream consumer that needs the most recent OHLCV row per symbol. GoogleSQL SELECT against the view v_latest_prices. Read-only, but scans the full base table on every read (regular views are not cached). Bytes scanned = same as running the underlying ROW_NUMBER query directly. Demonstrate that the view simplifies consumption — one clean SELECT replaces the complex subquery pattern.

Field	Source	Type	Meaning
`symbol`	`eurostoxx50_ohlcv.symbol`	STRING	Ticker symbol
`date`	`eurostoxx50_ohlcv.date`	DATE	Most recent trading date for this symbol
`open`	`eurostoxx50_ohlcv.open`	FLOAT64	Opening price on the most recent trading day
`high`	`eurostoxx50_ohlcv.high`	FLOAT64	Session high
`low`	`eurostoxx50_ohlcv.low`	FLOAT64	Session low
`close`	`eurostoxx50_ohlcv.close`	FLOAT64	Closing price
`volume`	`eurostoxx50_ohlcv.volume`	INT64	Shares traded

Query the view — the complex dedup logic is now hidden behind a simple SELECT.

SELECT * FROM bq-wh-nb.stoxx_gold.v_latest_prices ORDER BY `close` DESC
LIMIT 10

10 rows affected.

symbol	date	open	high	low	close	volume
RMS.PA	2026-03-12	1900.0	1918.5	1894.0	1906.0	18681
RHM.DE	2026-03-12	1536.0	1588.0	1535.0	1551.5	158741
ASML.AS	2026-03-12	1194.8	1202.2	1187.8	1190.8	128223
ADYEN.AS	2026-03-12	920.7	933.4	917.3	925.7	27887
ARGX.BR	2026-03-12	629.0	631.6	625.6	626.6	14083

Views | Cross-Layer Dashboard View

Join multiple tables into a single business-friendly view. Dashboards query this instead of raw tables.

Create a cross-layer dashboard view

When setting up or updating the dashboard query layer — a one-time DDL operation, re-run only if the schema changes. It is typically triggered by initial dashboard setup, or after adding new columns to the scoring pipeline that should be exposed in the dashboard. GoogleSQL DDL (CREATE OR REPLACE VIEW) in stoxx_gold. State-changing. The view joins scores_daily with index_dim, exposing a business-friendly interface for dashboard tools. Create a single cross-layer view that dashboards query instead of raw tables — encapsulates the score-to-dimension join and column formatting.

Create a cross-layer dashboard view joining gold scores with silver dimension metadata.

CREATE OR REPLACE VIEW bq-wh-nb.stoxx_gold.v_stock_dashboard AS
SELECT
    s.composite_rank AS `rank`,
    s.symbol,
    d.short_name,
    d.sector,
    d.country,
    s.current_price,
    ROUND(s.composite_score, 4) AS composite_score,
    ROUND(s.relative_value_score, 3) AS value_score,
    ROUND(s.momentum_score, 3) AS momentum_score,
    ROUND(s.index_weight * 100, 2) AS weight_pct,
    s._index,
    s.score_date
FROM `bq-wh-nb.stoxx_gold.scores_daily` s
JOIN `bq-wh-nb.stoxx_silver.index_dim` d ON s.symbol = d.symbol AND d._index = s._index AND d.is_current = TRUE;

rank	symbol	short_name	sector	country	current_price	composite_score	value_score	momentum_score	weight_pct	_index	score_date

Query the dashboard view for the latest rankings

When refreshing the stock ranking dashboard or validating that the scoring pipeline produced expected results. It is typically triggered by daily dashboard refresh, portfolio review, or post-scoring-run verification. GoogleSQL SELECT against the v_stock_dashboard view. Read-only, but the underlying view re-scans base tables on every read. Filtered to euro_stoxx_50 and the latest score_date. Retrieve the latest ranked stock dashboard with composite scores, sub-scores, and index weights — the primary output consumed by portfolio managers.

Field	Source / Computation	Type	Meaning
`rank`	`scores_daily.composite_rank`	INT64	Overall rank (1 = best composite score)
`symbol`	`scores_daily.symbol`	STRING	Ticker symbol
`short_name`	`index_dim.short_name`	STRING	Company short name
`sector`	`index_dim.sector`	STRING	GICS sector
`country`	`index_dim.country`	STRING	Country of primary listing
`current_price`	`scores_daily.current_price`	FLOAT64	Price at time of scoring
`composite_score`	`ROUND(composite_score, 4)`	FLOAT64	Weighted composite score
`value_score`	`ROUND(relative_value_score, 3)`	FLOAT64	Relative value component
`momentum_score`	`ROUND(momentum_score, 3)`	FLOAT64	Momentum component
`weight_pct`	`index_weight * 100`	FLOAT64 (%)	Index weight as a percentage
`_index`	`scores_daily._index`	STRING	Index key
`score_date`	`scores_daily.score_date`	DATE	Date of the scoring run

Query the dashboard view for the latest Euro Stoxx 50 rankings.

SELECT * FROM bq-wh-nb.stoxx_gold.v_stock_dashboard
WHERE _index = 'euro_stoxx_50'
  AND score_date = (SELECT MAX(score_date) FROM `bq-wh-nb.stoxx_gold.scores_daily` WHERE _index = 'euro_stoxx_50')
ORDER BY `rank`
LIMIT 10

10 rows affected.

rank	symbol	short_name	sector	country	current_price	composite_score	value_score	momentum_score	weight_pct	_index	score_date
1	BNP.PA	BNP PARIBAS ACT.A	Financial Services	France	87.44	0.6796	1.497	0.46	1.94	euro_stoxx_50	2026-03-12
2	VOW.DE	VOLKSWAGEN AG	Consumer Cyclical	Germany	92.85	0.5756	1.028	-0.382	0.93	euro_stoxx_50	2026-03-12
3	DTE.DE	DEUTSCHE TELEKOM AG	Communication Services	Germany	32.55	0.487	0.226	0.706	3.13	euro_stoxx_50	2026-03-12
4	TTE.PA	TOTALENERGIES	Energy	France	69.8	0.3913	0.585	1.307	2.95	euro_stoxx_50	2026-03-12
5	ABI.BR	AB INBEV	Consumer Defensive	Belgium	62.76	0.3852	0.251	0.537	2.43	euro_stoxx_50	2026-03-12

Stored Procedures

BigQuery supports stored procedures via CREATE OR REPLACE PROCEDURE with CALL invocation. Unlike SQL Server, BigQuery procedures offer no execution plan caching — they simply run statements sequentially. They are meant for multi-statement scripting blocks with control flow (IF, LOOP, BEGIN...EXCEPTION), not for parameterized reads, which are better expressed as table functions or CTE-with-params patterns.

Related pattern

Tools like dbt’s BigQuery adapter generate many of the parameterized query and view patterns shown below, removing the need to hand-write stored procedures for routine transforms.

Stored Procedures | Parameterized Queries

A stored procedure is precompiled SQL that lives in the database. BigQuery supports CREATE OR REPLACE PROCEDURE with CALL, but jupysql magic cannot execute CALL statements. BigQuery procedures also use EXECUTE IMMEDIATE for dynamic SQL.

The idiomatic BigQuery pattern for reusable parameterized logic is a CTE with a params row or a table function — not a stored procedure. Stored procedures are reserved for multi-statement scripting blocks with control flow (IF, LOOP, BEGIN...EXCEPTION).

Cross-engine comparison

SQL Server stored procedures compile and cache execution plans — a major performance feature. BigQuery procedures offer no plan caching; they simply execute statements sequentially. For parameterized reads, prefer table functions (CREATE TABLE FUNCTION) over procedures.

Parameterized top-N query with a CTE-based params row

When you need a reusable, parameterized read pattern without creating a stored procedure or table function. It is typically triggered by building a notebook cell or scheduled query that fetches top-N stocks by score for a given index — parameters are defined in the CTE rather than passed as procedure arguments. GoogleSQL CTE with a params row joined via CROSS JOIN (implicit comma syntax). Read-only. The params CTE acts as a single-row configuration table. This pattern avoids the jupysql limitation where DECLARE variables are not visible across cells. Demonstrate BigQuery’s idiomatic alternative to stored procedures for parameterized reads — change the params CTE values to reuse the same query for different indices or top-N limits.

Field	Source / Computation	Type	Meaning
`symbol`	`scores_daily.symbol`	STRING	Ticker symbol
`score`	`scores_daily.composite_score`	FLOAT64	Raw composite score (not rounded — full precision)
`composite_rank`	`scores_daily.composite_rank`	INT64	Rank within the index
`current_price`	`scores_daily.current_price`	FLOAT64	Price at time of scoring

Use a CTE with a params row to simulate a parameterized query — BigQuery’s idiomatic alternative to stored procedures for reads.

WITH params AS (
    SELECT 'euro_stoxx_50' AS index_key, 5 AS top_n
)
SELECT s.symbol, s.composite_score AS score,
       s.composite_rank, s.current_price
FROM `bq-wh-nb.stoxx_gold.scores_daily` s, params p
WHERE s._index = p.index_key
  AND s.score_date = (
      SELECT MAX(score_date) FROM `bq-wh-nb.stoxx_gold.scores_daily` WHERE _index = p.index_key
  )
ORDER BY s.composite_rank
LIMIT 5

5 rows affected.

symbol	score	composite_rank	current_price
BNP.PA	0.6795985859619491	1	87.44
VOW.DE	0.5756100520413311	2	92.85
DTE.DE	0.4870486370039222	3	32.55
TTE.PA	0.3912872052761238	4	69.8
ABI.BR	0.38521031359211527	5	62.76

Stored Procedures | Error Handling with BEGIN…EXCEPTION

Production scripts wrap logic in BEGIN...EXCEPTION...END with explicit transactions. If anything fails inside the block, execution jumps to the EXCEPTION handler where you can roll back and log the error. This is BigQuery’s equivalent of SQL Server’s TRY/CATCH.

BEGIN...EXCEPTION...END Pattern

BigQuery uses BEGIN...EXCEPTION...END for error handling (not TRY/CATCH like SQL Server). Transactions wrap the DML so failures roll back the entire operation — no partial loads.

BEGIN…EXCEPTION error handling with explicit transaction

When writing production pipeline scripts that perform multi-statement DML and need atomic rollback on failure. It is typically triggered by building a load script that must either succeed entirely or roll back — partial loads are unacceptable. GoogleSQL scripting block with DECLARE, BEGIN TRANSACTION, COMMIT, ROLLBACK, and EXCEPTION WHEN ERROR. State-changing — the DML inside the block modifies data. Requires bigquery.jobs.create permission. Each DML statement counts against the 1,500/day quota. Demonstrate BigQuery’s error handling pattern — wrap DML in a transaction so failures trigger ROLLBACK and the error message is captured via @@error.message.

Wrap DML inside BEGIN...EXCEPTION...END with an explicit transaction and an error handler.

DECLARE index_key STRING DEFAULT 'euro_stoxx_50';
DECLARE rows_loaded INT64 DEFAULT 0;
 
BEGIN
    BEGIN TRANSACTION;
    SET rows_loaded = (
        SELECT COUNT(*)
        FROM `bq-wh-nb.stoxx_gold.scores_daily`
        WHERE _index = index_key);
    COMMIT TRANSACTION;
    SELECT CONCAT('Load completed: ',
        CAST(rows_loaded AS STRING), ' rows') AS status;
EXCEPTION WHEN ERROR THEN
    ROLLBACK TRANSACTION;
    SELECT @@error.message AS error_message;
END

1 rows affected.

error_message
Undeclared variable: rows_loaded

jupysql limitation — multi-statement scripts

The Undeclared variable: rows_loaded error occurs because jupysql sends each `{init: {‘theme’: ‘dark’, ‘themeVariables’: { ‘primaryColor’: ‘#292e42’, ‘primaryTextColor’: ‘#c0caf5’, ‘primaryBorderColor’: ‘#565f89’, ‘lineColor’: ‘#565f89’, ‘secondaryColor’: ‘#1a1b26’, ‘tertiaryColor’: ‘#24283b’, ‘noteTextColor’: ‘#c0caf5’, ‘noteBkgColor’: ‘#292e42’, ‘textColor’: ‘#c0caf5’, ‘fontSize’: ‘14px’ }}}%% flowchart TD A[Dimension attribute changed] ⇒ B{Does history matter
for calculations?} B ⇒ NO1[NO] B ⇒ YES1[YES] NO1 ⇒|typo fix,
display name| C[SCD Type 1
Overwrite in place] YES1 ⇒|sector change,
index membership| D[SCD Type 2
Expire old + insert new] C ⇒ E[UPDATE row directly] D ⇒ F[Set is_current = FALSE,
valid_to = NOW on old row] F ⇒ G[INSERT new row with
is_current = TRUE,
valid_from = NOW] style YES1 fill:#1f3b2d,stroke:#73d13d,color:#c0caf5 style NO1 fill:#4a1f24,stroke:#db4b4b,color:#c0caf5


> [!info] Cross-engine comparison
> 
>
> SCD patterns are engine-agnostic SQL — the same Type 1/Type 2 logic works in BigQuery and SQL Server. Firestore handles versioning differently: store historical snapshots as subcollections (`/company/{id}/history/{timestamp}`) or use a `versions` array field within the document.

> [!danger] SCD Type 1 Destroys History
> 
>
> SCD Type 1 Destroys History Permanently.
> SCD Type 1 overwrites in place -- once the old value is gone, it is unrecoverable unless you have a backup or the source system retains history. In financial pipelines, always default to SCD Type 2 for dimension attributes that affect calculations (sector, index membership, weighting). A sector change can retroactively alter historical portfolio returns if the dimension is Type 1.

> [!success] Safe Pattern
> 
>
> Use **SCD Type 2** for any attribute that affects historical calculations: expire the old row (`is_current = FALSE`, `valid_to = NOW()`) and insert a new row (`is_current = TRUE`, `valid_from = NOW()`). Reserve SCD Type 1 only for non-analytical corrections such as fixing a typo in a display name.

### Slowly Changing Dimensions | SCD Type 1 Overwrite

Simply UPDATE the row. History is lost. Use when you don't care about old values.
Example: fix a typo in a company name.

This simulation shows the before/after of an SCD Type 1 overwrite: ASML's sector changes from its current value to "Information Technology". In production, this would be a direct `UPDATE` statement.

#### Simulate an SCD Type 1 overwrite on dimension rows

When a non-analytical attribute needs correcting (e.g., fixing a typo in a company name) and history preservation is not required. It is typically triggered by A dimension attribute that does not affect historical calculations needs updating — a display name correction, a metadata fix, or a classification change that should apply retroactively. GoogleSQL SELECT with CASE expression against `stoxx_silver.index_dim`. Read-only simulation — in production, this would be an `UPDATE` statement. This query shows the before/after without modifying data. Demonstrate the SCD Type 1 pattern — in-place overwrite with no history. The `scd_action` column flags which rows would be modified.

| Field | Source / Computation | Type | Meaning |
|---|---|---|---|
| `symbol` | `index_dim.symbol` | STRING | Ticker symbol |
| `short_name` | `index_dim.short_name` | STRING | Company short name |
| `original_sector` | `index_dim.sector` | STRING | Current sector value before the overwrite |
| `updated_sector` | `CASE WHEN symbol = 'ASML.AS' THEN 'Information Technology' ELSE sector END` | STRING | Sector value after the overwrite — only ASML changes |
| `scd_action` | `CASE WHEN symbol = 'ASML.AS' THEN 'OVERWRITTEN' ELSE 'unchanged' END` | STRING | `OVERWRITTEN` = this row would be modified. `unchanged` = no change |

*Simulate an SCD Type 1 overwrite by flagging which rows would be updated in place and which remain unchanged.*

```sql
WITH original AS (
    SELECT symbol, short_name, sector, is_current
    FROM `bq-wh-nb.stoxx_silver.index_dim`
    WHERE _index = 'euro_stoxx_50' AND is_current = TRUE
)
SELECT symbol, short_name,
    sector AS original_sector,
    CASE WHEN symbol = 'ASML.AS' THEN 'Information Technology'
         ELSE sector END AS updated_sector,
    CASE WHEN symbol = 'ASML.AS' THEN 'OVERWRITTEN'
         ELSE 'unchanged' END AS scd_action
FROM original
ORDER BY symbol
LIMIT 10

10 rows affected.

symbol	short_name	original_sector	updated_sector	scd_action
ABI.BR	AB INBEV	Consumer Defensive	Consumer Defensive	unchanged
AD.AS	KONINKLIJKE AHOLD DELHAIZE N.V.	Consumer Defensive	Consumer Defensive	unchanged
ADS.DE	adidas AG	Consumer Cyclical	Consumer Cyclical	unchanged
ADYEN.AS	ADYEN	Technology	Technology	unchanged
AI.PA	AIR LIQUIDE	Basic Materials	Basic Materials	unchanged

Slowly Changing Dimensions | SCD Type 2 History Tracking

Expire the old row (is_current=0, valid_to=NOW) and insert a new row (is_current=1). This is how silver.index_dim works — it has valid_from, valid_to, is_current columns.

The stoxx_silver.index_dim table already implements SCD Type 2 with valid_from, valid_to, and is_current columns. Rows with is_current = TRUE and valid_to = NULL represent the current state.

Query SCD Type 2 validity ranges

When inspecting the history of dimension changes, or verifying that the SCD Type 2 mechanism is correctly expiring old rows and inserting new ones. It is typically triggered after a dimension load, to confirm that changed attributes produced the expected (expired old row + new current row) pair. Also useful for auditing historical membership. GoogleSQL SELECT against stoxx_silver.index_dim. Read-only. The table implements SCD Type 2 with valid_from (TIMESTAMP), valid_to (TIMESTAMP, NULL for current rows), and is_current (BOOL). Show the validity ranges for dimension rows — current rows have valid_to = NULL and is_current = TRUE. Historical rows show the period during which each attribute value was active.

Field	Source / Computation	Type	Meaning
`symbol`	`index_dim.symbol`	STRING	Ticker symbol
`short_name`	`index_dim.short_name`	STRING	Company name during the validity period
`sector`	`index_dim.sector`	STRING	GICS sector during the validity period
`is_current`	`index_dim.is_current`	BOOL	`True` = this is the active row. `False` = expired historical row
`valid_from`	`CAST(valid_from AS DATE)`	DATE	Date from which this row’s attributes were effective
`valid_to`	`CAST(valid_to AS DATE)`	DATE / NULL	Date when this row was expired. `None` (NULL) = currently active

Query SCD Type 2 history: show valid_from / valid_to ranges for Euro Stoxx 50 dimension rows.

SELECT
    symbol, short_name, sector,
    is_current,
    CAST(valid_from AS DATE) AS valid_from,
    CAST(valid_to AS DATE) AS valid_to
FROM `bq-wh-nb.stoxx_silver.index_dim`
WHERE _index = 'euro_stoxx_50'
ORDER BY symbol, valid_from
LIMIT 10

10 rows affected.

symbol	short_name	sector	is_current	valid_from	valid_to
ABI.BR	AB INBEV	Consumer Defensive	True	2026-03-04	None
AD.AS	KONINKLIJKE AHOLD DELHAIZE N.V.	Consumer Defensive	True	2026-03-04	None
ADS.DE	adidas AG	Consumer Cyclical	True	2026-03-04	None
ADYEN.AS	ADYEN	Technology	True	2026-03-04	None
AI.PA	AIR LIQUIDE	Basic Materials	True	2026-03-04	None

Gap Detection & Gap Filling

Time-series data in financial pipelines frequently contains gaps — missing trading days due to market holidays, exchange closures, or ingestion failures. Detecting and classifying these gaps is a prerequisite for accurate signal computation, since undetected gaps distort rolling averages and return calculations. BigQuery’s LAG() function paired with DATE_DIFF() is the standard tool for gap detection.

Gap Detection & Gap Filling | Islands and Gaps

The classic SQL pattern: identify contiguous groups (islands) and missing periods (gaps) in a time series. Uses the difference between ROW_NUMBER and the date to group consecutive days.

LAG compares each date to its predecessor within the same symbol’s time series. A gap of more than 3 calendar days is flagged as unusual — normal weekends produce a 3-day gap (Friday → Monday), so anything larger indicates a holiday, data issue, or delisting event.

Detect calendar gaps with LAG and DATE_DIFF

After loading new data, or when investigating anomalies in rolling calculations (moving averages, volatility) that could be caused by hidden gaps. It is typically triggered by post-load validation, or debugging unexpected results in time-series analytics. GoogleSQL window functions LAG() and DATE_DIFF() against stoxx_silver.eurostoxx50_ohlcv. Read-only. Partitioned by symbol, ordered by date. Detect unusual gaps in the time series — any gap > 3 calendar days exceeds a normal weekend and may indicate a holiday, data issue, or delisting event that requires investigation.

Field	Source / Computation	Type	Meaning
`symbol`	`eurostoxx50_ohlcv.symbol`	STRING	Ticker symbol
`date`	`eurostoxx50_ohlcv.date`	DATE	Current trading date
`prev_date`	`LAG(date) OVER (PARTITION BY symbol ORDER BY date)`	DATE	Previous trading date (NULL for first row)
`gap_days`	`DATE_DIFF(date, LAG(date), DAY)`	INT64	Calendar days between current and previous trading date. 1 = consecutive weekday. 3 = Friday→Monday (normal weekend). > 3 = holiday or data gap
`status`	`CASE WHEN gap_days > 3 THEN 'UNUSUAL GAP' ELSE 'normal' END`	STRING	`UNUSUAL GAP` = gap exceeds normal weekend — investigate. `normal` = expected trading pattern

Detect time-series gaps: compare each date to the previous date using LAG and flag gaps > 3 days.

SELECT
    symbol, date,
    LAG(date) OVER (PARTITION BY symbol ORDER BY date) AS prev_date,
    DATE_DIFF(date, LAG(date) OVER (PARTITION BY symbol ORDER BY date), DAY) AS gap_days,
    CASE WHEN DATE_DIFF(date, LAG(date) OVER (PARTITION BY symbol ORDER BY date), DAY) > 3
         THEN 'UNUSUAL GAP' ELSE 'normal' END AS status
FROM `bq-wh-nb.stoxx_silver.eurostoxx50_ohlcv`
WHERE symbol = 'ASML.AS' AND date >= '2025-01-01'
ORDER BY date DESC
LIMIT 10

10 rows affected.

symbol	date	prev_date	gap_days	status
ASML.AS	2026-03-12	2026-03-11	1	normal
ASML.AS	2026-03-11	2026-03-10	1	normal
ASML.AS	2026-03-10	2026-03-09	1	normal
ASML.AS	2026-03-09	2026-03-06	3	normal
ASML.AS	2026-03-06	2026-03-05	1	normal

Deduplication Strategies

Duplicate rows in source data are one of the most common data quality issues in financial pipelines — broker feeds retry failed deliveries, ETL jobs re-run after failures, and UNION operations occasionally double-count rows. BigQuery’s ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) window function is the standard deduplication tool: assign rank 1 to the row to keep within each duplicate group, then filter or delete the rest.

Deduplication Strategies | ROW_NUMBER Pattern

The standard approach: assign ROW_NUMBER() within each duplicate group, keep rn = 1, delete the rest.

The simulation below uses UNION ALL to create an artificial duplicate, then applies ROW_NUMBER() partitioned by the natural key (symbol, date) to assign rn = 1 to the row to keep (highest volume wins). In production, filter to rn = 1 and write the deduplicated result to the target table.

Identify duplicates with ROW_NUMBER and tie-breaking

When deduplicating a table after detecting duplicate rows, or as part of a load validation step. It is typically triggered by quality check reveals copies > 1 for some (symbol, date) combinations, or a re-run of the ETL job may have double-loaded data. GoogleSQL CTEs with UNION ALL (to simulate a duplicate) and ROW_NUMBER() OVER (PARTITION BY symbol, date ORDER BY volume DESC). Read-only simulation. In production, filter to rn = 1 and write the deduplicated result to the target table using CREATE OR REPLACE TABLE ... AS SELECT. Identify duplicate rows using ROW_NUMBER with volume-based tie-breaking — the row with the highest volume is kept (rn = 1), others are flagged for removal.

Field	Source / Computation	Type	Meaning
`symbol`	CTE `raw_data`	STRING	Ticker symbol
`date`	CTE `raw_data`	DATE	Trading date — part of the natural key for deduplication
`close`	CTE `raw_data`	FLOAT64	Closing price (may differ between duplicate rows)
`volume`	CTE `raw_data`	INT64	Trading volume — used as the tie-breaker (highest volume wins)
`source`	String literal in UNION ALL	STRING	`original` = real data. `duplicate` = simulated duplicate row
`rn`	`ROW_NUMBER() OVER (PARTITION BY symbol, date ORDER BY volume DESC)`	INT64	1 = row to keep. > 1 = duplicate to remove
`copies`	`COUNT(*) OVER (PARTITION BY symbol, date)`	INT64	Total rows sharing the same (symbol, date) key. 1 = unique. > 1 = duplicated

Simulate a duplicate row and identify it using ROW_NUMBER with volume-based tie-breaking.

WITH raw_data AS (
    SELECT symbol, date, `close`, volume, 'original' AS source
    FROM `bq-wh-nb.stoxx_silver.eurostoxx50_ohlcv`
    WHERE symbol = 'ASML.AS' AND date >= '2026-03-10'
    UNION ALL
    SELECT symbol, date, `close` + 0.5, volume + 999, 'duplicate'
    FROM `bq-wh-nb.stoxx_silver.eurostoxx50_ohlcv`
    WHERE symbol = 'ASML.AS' AND date = (SELECT MAX(date) FROM `bq-wh-nb.stoxx_silver.eurostoxx50_ohlcv` WHERE symbol = 'ASML.AS')
),
numbered AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY symbol, date ORDER BY volume DESC) AS rn,
           COUNT(*) OVER (PARTITION BY symbol, date) AS copies
    FROM raw_data
)
SELECT symbol, date, ROUND(`close`, 2) AS `close`, volume, source, rn, copies
FROM numbered
WHERE copies > 1  -- only show the duplicated date
ORDER BY date DESC, rn
LIMIT 10

2 rows affected.

symbol	date	close	volume	source	rn	copies
ASML.AS	2026-03-12	1191.3	129222	duplicate	1	2
ASML.AS	2026-03-12	1190.8	128223	original	2	2

Execution Plans & Query Optimization

BigQuery’s query optimizer and execution engine are fully managed — there is no index selection or plan hint grammar for engineers to tune. The levers that matter are column selection (to reduce bytes scanned), partition pruning (to skip irrelevant data), clustering (to skip blocks within partitions), and avoidance of anti-patterns that defeat these optimizations.

BigQuery SELECT * Is Expensive

BigQuery SELECT * Scans All Columns and Bills Accordingly. BigQuery is columnar — you pay per column scanned, not per row. SELECT * on a 1 TB table costs the full 1 TB price even if you only need two columns. Always select specific columns. Use the query validator in the BigQuery console (top-right of the editor) to preview bytes scanned before running.

Safe Pattern

Always name the columns you need: SELECT symbol, date, close FROM table. Use bq query --dry_run or job_config.dry_run = True in Python to verify bytes scanned before execution. For exploratory work, filter on a partition column first (WHERE date = '2026-03-12') to limit the scan window.

For a broader look at controlling BigQuery spend through slot management and reservation strategies, see querying-and-cost-optimization.

Execution Plans & Query Optimization | Common Anti-Patterns

Anti-Pattern	Problem	Fix
`WHERE EXTRACT(YEAR FROM date) = 2025`	Function on partition column prevents partition pruning	`WHERE date >= '2025-01-01' AND date < '2026-01-01'`
`SELECT *`	Reads all columns — BigQuery is columnar, so more columns = more bytes scanned = higher cost	Select only needed columns
`WHERE col = NULL`	Always FALSE (NULL != NULL)	`WHERE col IS NULL`
No partition filter	Scans all partitions on a partitioned table	Always filter on partition column; use `require_partition_filter`
`ORDER BY` without `LIMIT`	Full sort across all slots — expensive on large result sets	Always pair `ORDER BY` with `LIMIT`
Cross-join with large tables	Cartesian product multiplies bytes scanned	Ensure at least one side is small; use JOIN instead

Both queries return the same count, but the sargable version enables partition pruning. The EXTRACT version wraps the column in a function, preventing BigQuery from using partition metadata to skip irrelevant partitions. The range filter version allows direct partition elimination.

Compare non-pruning (EXTRACT) vs pruning (range) predicates

When optimizing query cost on partitioned tables, or when teaching the difference between SARGable and non-SARGable predicates in BigQuery. It is typically triggered by observing unexpectedly high bytes scanned on a partitioned table — the most common cause is a function wrapping the partition column. GoogleSQL subqueries comparing two COUNT queries against stoxx_silver.eurostoxx50_ohlcv. Read-only. Both return the same count, but the EXTRACT version prevents partition pruning while the range predicate enables it. Demonstrate that wrapping the partition column in a function (EXTRACT(YEAR FROM date)) defeats partition pruning — the range predicate WHERE date >= '2025-01-01' AND date < '2026-01-01' produces the same result at lower scan cost.

Field	Source / Computation	Type	Meaning
`bad_function_on_column`	`COUNT(*)` with `EXTRACT(YEAR FROM date) = 2025`	INT64	Row count using the non-SARGable predicate — scans all partitions
`good_sargable`	`COUNT(*)` with `date >= '2025-01-01' AND date < '2026-01-01'`	INT64	Same row count using a SARGable range predicate — enables partition pruning

Compare non-pruning (EXTRACT on column) vs pruning (range predicate) filters — same result, different scan cost.

SELECT
    (SELECT COUNT(*) FROM `bq-wh-nb.stoxx_silver.eurostoxx50_ohlcv`
     WHERE EXTRACT(YEAR FROM date) = 2025) AS bad_function_on_column,
    (SELECT COUNT(*) FROM `bq-wh-nb.stoxx_silver.eurostoxx50_ohlcv`
     WHERE date >= '2025-01-01' AND date < '2026-01-01') AS good_sargable

1 rows affected.

bad_function_on_column	good_sargable
12698	12698

Transaction Model

BigQuery’s transaction model is substantially simpler than SQL Server’s — every query runs under snapshot isolation automatically, and there are no configurable isolation levels or lock-based concurrency. Multi-statement transactions exist for atomic multi-DML operations but come with strict regional and time limits.

Transaction Model | BigQuery Snapshot Isolation

BigQuery does not expose configurable isolation levels like SQL Server. Every query runs under snapshot isolation automatically — each statement sees a consistent snapshot of the data as of the statement’s start time. There is no risk of dirty reads, non-repeatable reads, or phantom reads.

Feature	BigQuery	SQL Server
Default isolation	Snapshot (automatic)	READ COMMITTED
Configurable levels	No	Yes (5 levels)
Multi-statement transactions	`BEGIN TRANSACTION ... COMMIT` (scripting only)	`BEGIN TRAN ... COMMIT`
Concurrent writers	Last-writer-wins per row	Lock-based concurrency
Deadlock risk	None (no row-level locks)	Yes (lock escalation)

Multi-statement transactions have strict limits

BigQuery multi-statement transactions (using BEGIN TRANSACTION) are limited to tables in a single region, cannot span datasets in different locations, and must complete within 10 minutes. Each transaction counts toward the 1,500 DML/day quota per table. They are designed for short atomic operations — not long-running ETL pipelines.

Safe Pattern

For most pipeline patterns, single DML statements (INSERT, MERGE, DELETE) are already atomic — no explicit transaction needed. Use BEGIN TRANSACTION ... COMMIT only when you need multiple DML statements to succeed or fail as a unit (e.g., delete old partition + insert new data). Keep transactions short and targeted.

Cross-engine comparison

SQL Server offers five isolation levels (READ UNCOMMITTED through SERIALIZABLE) plus SNAPSHOT. BigQuery offers only snapshot isolation with no configuration. Firestore offers serializable transactions with a 500-document-per-transaction limit and optimistic concurrency (transaction retries on conflict).

Bulk Loading Patterns

Bulk ingestion is the performance-critical path for bronze-layer loads and silver/gold materializations. BigQuery offers several mechanisms with very different quotas, costs, and latency characteristics — from free batch loads via bq load to sub-second Storage Write API streaming.

Bulk Loading Strategies

BigQuery offers several ingestion mechanisms, each with different quotas, costs, and latency characteristics. The choice depends on data volume, frequency, and whether you need exactly-once semantics.

Strategy	Latency	Cost	When
`bq load` (CLI)	Seconds–minutes	Free (batch)	CSV/JSON/Parquet files from GCS or local disk
`LOAD DATA` (SQL)	Seconds–minutes	Free (batch)	Same as `bq load` but executed as a SQL statement
`INSERT INTO ... SELECT`	Seconds	Bytes scanned	Small-medium loads from other BQ tables or CTEs
Storage Write API (batch)	Seconds	Free (batch)	Programmatic loads from Python/Java with exactly-once semantics
Storage Write API (committed)	Sub-second	Streaming pricing	High-frequency inserts with exactly-once guarantee
`insertAll` (legacy streaming)	Sub-second	Streaming pricing ($0.05/GB)	Real-time inserts — simpler API but at-least-once delivery

Batch loads are free

BigQuery does not charge for batch loading (bq load, LOAD DATA, Storage Write API in batch mode). You pay only for storage after the data lands. Streaming inserts (insertAll, Storage Write API in committed mode) are charged at $0.05/GB. For cost-sensitive pipelines, prefer batch loading on a schedule over streaming.

Pipeline pattern: load to staging table (batch) → validate with quality checks → MERGE to target → truncate staging. See data-loading-and-export for implementation details.

Data Lineage & Audit Columns

The stoxx datasets implement audit columns on every table to support data lineage tracking — when each row was ingested, computed, and last modified. These columns enable freshness checks, replay detection, and pipeline debugging across the medallion layers.

Data Lineage & Audit | Standard Audit Columns

Every table in the stoxx database has audit columns:

Column	Type	Purpose
`_ingested_at`	TIMESTAMP	When the row was loaded (bronze)
`_scored_at`	TIMESTAMP	When the score was computed (gold)
`_computed_at`	TIMESTAMP	When the performance was calculated
`is_filled`	BOOL	Whether the row was gap-filled (silver)
`is_current`	BOOL	SCD Type 2 current flag (dimension)

A data freshness check across all medallion layers — if any table’s last_update is more than 1 day behind the current date, the pipeline may have stalled.

Check data freshness across all medallion layers

As part of the daily pipeline monitoring routine, or when investigating why downstream dashboards show stale data. It is typically triggered by scheduled monitoring check, or a user reports that dashboard data appears outdated. GoogleSQL UNION ALL of four MAX-timestamp queries, one per medallion-layer table. Read-only. Each query scans only the timestamp/date column used. If any last_update is more than 1 day behind the current date on a business day, the pipeline may have stalled. Provide a single-query freshness overview across all medallion layers — bronze ingestion, silver signals, gold scores, and gold index performance — to detect pipeline stalls at a glance.

Field	Source / Computation	Type	Meaning
`table`	String literal	STRING	Fully qualified table name identifying which medallion-layer table the timestamp comes from
`last_update`	`MAX(_ingested_at)` or `MAX(signal_date)` or `MAX(score_date)` or `MAX(perf_date)`	TIMESTAMP / DATE	Most recent timestamp or date in the table. The column name varies by layer — `_ingested_at` for bronze, domain-specific date columns for silver/gold

Check data freshness across all four medallion layers — the latest timestamp per table.

SELECT '`bq-wh-nb.stoxx_bronze.eurostoxx50_ohlcv`' AS `table`, MAX(_ingested_at) AS last_update
FROM `bq-wh-nb.stoxx_bronze.eurostoxx50_ohlcv`
UNION ALL
SELECT '`bq-wh-nb.stoxx_silver.signals_daily`', MAX(signal_date) FROM `bq-wh-nb.stoxx_silver.signals_daily`
WHERE _index = 'euro_stoxx_50'
UNION ALL
SELECT '`bq-wh-nb.stoxx_gold.scores_daily`', MAX(score_date) FROM `bq-wh-nb.stoxx_gold.scores_daily`
WHERE _index = 'euro_stoxx_50'
UNION ALL
SELECT '`bq-wh-nb.stoxx_gold.index_performance`', MAX(perf_date) FROM `bq-wh-nb.stoxx_gold.index_performance`
WHERE _index = 'euro_stoxx_50'
ORDER BY last_update DESC

4 rows affected.

table	last_update
`bq-wh-nb.stoxx_bronze.eurostoxx50_ohlcv`	2026-03-12 12:45:00.021478
`bq-wh-nb.stoxx_silver.signals_daily`	2026-03-12 00:00:00
`bq-wh-nb.stoxx_gold.scores_daily`	2026-03-12 00:00:00
`bq-wh-nb.stoxx_gold.index_performance`	2026-03-12 00:00:00

Partitioning Strategies

Table partitioning divides a BigQuery table into physically separate segments based on a column value (typically a date). Partition elimination allows the query optimizer to skip entire partitions that cannot satisfy the WHERE clause predicate, cutting scan cost by orders of magnitude on time-series data. Combined with clustering, partitioning is BigQuery’s primary performance lever.

Partitioning Strategies | When to Partition

Partition large tables (millions of rows) by a date column for:

Faster queries: partition elimination skips irrelevant months/years
Easier maintenance: rebuild one partition, not the whole table
Instant archival: SWITCH old partitions to archive table

The OHLCV tables (~65K rows each) are too small to benefit. In production with 100M+ rows, partition by year or month.

Querying without a partition filter scans ALL partitions

If a table is partitioned by date but your query has no WHERE date = ... or WHERE date BETWEEN ... filter, BigQuery scans every partition — negating the cost benefit entirely. You pay for the full table scan.

Safe Pattern

Always filter on the partition column in WHERE clauses. Enable require_partition_filter when creating the table to enforce this at the schema level: queries without a partition filter will fail with an error instead of silently scanning everything.

Create a partitioned and clustered table with require_partition_filter

When creating a new production table that will store time-series data, or migrating an existing table to a partitioned layout. It is typically triggered by table design phase for a new pipeline, or after observing that queries against an unpartitioned table are scanning excessive bytes. GoogleSQL DDL (CREATE TABLE IF NOT EXISTS) in the demo dataset. State-changing — creates a new table. PARTITION BY DATE_TRUNC(date, MONTH) splits storage by month. CLUSTER BY symbol sorts data within each partition. require_partition_filter = TRUE forces all queries to include a partition predicate. Create the recommended table layout for time-series OHLCV data — monthly partitioning for coarse scan elimination, clustering by symbol for fine-grained block pruning, and mandatory partition filter to prevent accidental full-table scans.

Create a partitioned and clustered OHLCV table with require_partition_filter enabled to enforce scan-cost discipline.

CREATE TABLE IF NOT EXISTS demo.ohlcv_partitioned (
    symbol STRING,
    date DATE,
    open FLOAT64,
    high FLOAT64,
    low FLOAT64,
    close FLOAT64,
    volume INT64
)
PARTITION BY DATE_TRUNC(date, MONTH)
CLUSTER BY symbol
OPTIONS(
    require_partition_filter = TRUE,
    description = 'Partitioned by month, clustered by symbol'
)

For partition pruning cost details and slot management, see querying-and-cost-optimization.

Cleanup

Drop all objects created in the demo dataset by this notebook. Running the cleanup leaves the project in its original state and makes the notebook safe to re-run from a clean slate.

Demo object cleanup

Every view, table function, and dataset created earlier must be dropped in reverse dependency order so the project is left in its original state.

Drop all demo objects and the demo dataset

At the end of a lab session, or before re-running the notebook from a clean slate. It is typically triggered by notebook execution complete — clean up demo objects to leave the project in its original state. Python client library (bigquery.Client). State-changing — drops table functions, views, and the demo dataset. Uses not_found_ok=True for idempotent re-runs. delete_contents=True removes all remaining objects inside the dataset before dropping it. Clean up all objects created by this notebook so the project is left in its original state and the notebook is safe to re-run from scratch.

Drop all demo objects and the demo dataset using the BigQuery Python client.

from google.cloud import bigquery
bq = bigquery.Client(project="bq-wh-nb")
 
for obj in [
    "bq-wh-nb.demo.fn_price_history",
    "bq-wh-nb.demo.v_latest_prices",
    "bq-wh-nb.demo.v_stock_dashboard",
]:
    bq.query(f"DROP TABLE FUNCTION IF EXISTS `{obj}`").result()
 
bq.delete_dataset("demo", delete_contents=True, not_found_ok=True)
print("Demo objects cleaned up")

Demo objects cleaned up