BigQuery for Historical IoT Analytics

Where BigQuery fits in an IoT architecture, and who this is for

An IoT pipeline has three separate jobs. Messaging (MQTT brokers, Kafka, Pub/Sub) moves readings from devices and buffers them. An operational time-series database such as TimescaleDB or InfluxDB holds recent readings for dashboards and alarms. A warehouse such as BigQuery holds the full history for analysis that spans sites, years and other business data.

BigQuery suits the third job because it is serverless (no instances to size), stores data in columns, and bills queries by the bytes they read. A question such as "which chillers in the portfolio used more kWh per cooling degree day this summer than last" scans a few columns over many months, which matches the columnar, scan-billed design the documentation describes.

This page is for data engineers and analysts in facilities, energy or industrial teams who already collect sensor data and want a design for its long-term home in BigQuery. It does not compare BigQuery with other warehouses: for that see BigQuery vs PostgreSQL, ClickHouse vs BigQuery and the BigQuery alternatives guide.

Designing the readings table: partitioning and clustering

Partition by the reading timestamp, not by ingestion time, so late readings land in the day they belong to and partition filters match how people ask questions. For TIMESTAMP columns BigQuery supports hourly, daily, monthly or yearly partitions, with boundaries in UTC. Daily is the default and fits continuous sensor data; Google suggests hourly only for high volumes over short date ranges, and monthly or yearly when each day holds little data.

Two limits shape the choice. A partitioned table can have up to 10,000 partitions, which is about 27 years of daily partitions but only about 14 months of hourly ones. And Google advises against partitions much smaller than about 10 GB, recommending clustering instead when partitions would be tiny. For a small estate, monthly partitions clustered by timestamp can be the better layout.

Then cluster by the columns most queries filter on, in order of importance: typically site or building first, then point. Clustering sorts storage blocks by those columns so a filter on one building skips the other buildings' blocks within each partition. Column order matters: a filter on the second clustering column alone benefits less.

Two table options are worth setting from the start: require_partition_filter, which rejects queries with no filter on the partitioning column, and partition_expiration_days, which deletes each partition once it reaches the age you set. Do not use date-sharded tables such as readings_20261027; Google recommends partitioning over sharding.

CREATE TABLE iot.bms_reading (
  ts           TIMESTAMP NOT NULL,   -- reading time from the device, UTC
  building_id  INT64     NOT NULL,
  point_id     INT64     NOT NULL,   -- from the asset/point register
  value        FLOAT64,
  quality      STRING                -- e.g. 'good', 'stale', 'fault'
)
PARTITION BY TIMESTAMP_TRUNC(ts, DAY)
CLUSTER BY building_id, point_id
OPTIONS (
  partition_expiration_days = 1825,  -- illustrative: five years of raw readings
  require_partition_filter  = TRUE
);

Getting readings in: streaming vs batch loading

BigQuery offers three practical ingestion routes for sensor data:

• Storage Write API (gRPC). Google's recommended API for new projects. Its default stream makes rows queryable as soon as they are written and is at-least-once; for application-created streams the documentation describes exactly-once write semantics through stream offsets (that covers writes into BigQuery, not duplicates your producers send). Send rows in batches per AppendRows call, not one at a time. The older tabledata.insertAll method is now called the Storage Write API (REST) and remains supported, at a higher ingestion price.
• Pub/Sub BigQuery subscription. If devices or gateways already publish to Pub/Sub, a BigQuery subscription writes each message to an existing table without a subscriber service of your own. It uses the Storage Write API and delivers at least once. Use a Dataflow pipeline instead when you need to window, aggregate or deduplicate across messages before they land.
• Batch loads. Gateways or an edge service write files (Parquet, Avro or CSV) to Cloud Storage and a load job appends them, for example every 15 minutes or nightly. Loading from Cloud Storage is free by default using a shared slot pool with no throughput guarantee.

For building data, one possible split (a design choice, not a Google recommendation) is: alarms and live trends from the operational store, plus hourly or nightly batch loads of the same readings into BigQuery. Streaming earns its cost when analysts need the warehouse to be minutes behind rather than hours. Whichever you choose, at-least-once delivery means duplicates are possible, so deduplicate on (point_id, ts) in a scheduled query or a downstream table rather than assuming each reading arrives once. The Kafka IoT pipeline design covers the upstream side.

Time-series SQL in BigQuery

GoogleSQL has dedicated time series functions: TIMESTAMP_BUCKET and DATE_BUCKET return the lower bound of a fixed-width bucket, and GAP_FILL fills missing buckets with the last observed value (locf), linear interpolation or nulls. That matters for meters and BMS points that report on change or drop out for a few minutes.

For repeated roll-ups, incremental materialized views are refreshed in the background from base-table changes; an hourly summary per point can serve most dashboards and reports at a fraction of the bytes. For anomaly work, BigQuery ML's ML.DETECT_ANOMALIES works with time series models such as ARIMA_PLUS, which keeps simple per-point anomaly checks inside SQL; see time-series anomaly detection for the methods.

SELECT *
FROM GAP_FILL(
  (
    SELECT point_id,
           TIMESTAMP_BUCKET(ts, INTERVAL 1 HOUR) AS hour,
           AVG(value) AS avg_kw
    FROM iot.bms_reading
    WHERE ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)   -- partition filter
      AND building_id = 12                                            -- clustering filter
      AND point_id IN (4101, 4102)                                    -- chiller kW points
    GROUP BY point_id, hour
  ),
  ts_column => 'hour',
  bucket_width => INTERVAL 1 HOUR,
  partitioning_columns => ['point_id'],
  value_columns => [('avg_kw', 'linear')]
)
ORDER BY point_id, hour;

Sizing the data: a building portfolio example

BigQuery bills storage and on-demand queries on logical bytes, and documents a size for each data type: 8 bytes for TIMESTAMP, INT64 and FLOAT64, and 2 bytes plus the UTF-8 length for STRING. As an illustrative assumption, take a portfolio of 40 buildings with 2,500 BMS points each (100,000 points) reporting once a minute, stored in the table above; substitute your own point count, reporting interval and columns. The arithmetic below ran on PostgreSQL; it shows about 5.1 GiB of new data a day and 1.82 TiB a year, and how much a typical query reads with and without a partition filter.

Only the columns a query references are counted. A query reading ts, point_id and value for a full year would process about 1.15 TiB; the same query limited to the last 7 days processes about 22.5 GiB before clustering prunes it further. That difference is multiplied by every dashboard refresh. Our editorial view: under on-demand billing, the partition filter and clustering are the design choices with the largest effect on query cost.

-- Logical size of one reading row using BigQuery's documented data type sizes:
-- TIMESTAMP 8, INT64 8, INT64 8, FLOAT64 8, STRING 2 + UTF-8 length ('good' = 4)
WITH assumptions AS (
    SELECT 100000::numeric   AS points,          -- 40 buildings x 2,500 BMS points
           60::numeric       AS interval_s,      -- one reading per point per minute
           (8 + 8 + 8 + 8 + (2 + 4))::numeric AS row_bytes,
           (8 + 8 + 8)::numeric AS scan_bytes     -- ts, point_id, value read by a typical query
), daily AS (
    SELECT (points * 86400 / interval_s)::bigint AS rows_per_day, row_bytes, scan_bytes FROM assumptions
)
SELECT rows_per_day,
       round(rows_per_day * row_bytes / 1073741824, 2)               AS gib_per_day,
       round(rows_per_day * row_bytes * 365 / 1099511627776, 2)         AS tib_per_year,
       round(rows_per_day * scan_bytes * 365 / 1099511627776, 3)        AS tib_scanned_full_year_3_cols,
       round(rows_per_day * scan_bytes * 7 / 1073741824, 2)          AS gib_scanned_last_7_days_3_cols
FROM daily;

 rows_per_day | gib_per_day | tib_per_year | tib_scanned_full_year_3_cols | gib_scanned_last_7_days_3_cols
--------------+-------------+--------------+------------------------------+--------------------------------
    144000000 |        5.10 |         1.82 |                        1.147 |                          22.53

Controlling query cost

• Filter on the partition column. Enforce it with require_partition_filter. Compare the ts column itself with a constant expression; a filter that compares it with values from another table or computes it from other columns does not prune partitions.
• Select only the columns you need. SELECT * reads every column; on non-clustered tables LIMIT does not reduce the bytes billed.
• Set maximum bytes billed on scheduled and ad hoc queries, and project or user daily quotas for on-demand billing. A query whose estimate exceeds the limit fails without charge.
• Expect estimates, not exact figures, on clustered tables. Google notes that the dry-run estimate for a clustered table is an upper bound, because the blocks pruned are only known at run time.
• Serve dashboards from roll-ups. Point BI tools at hourly or daily materialized views, not at raw readings.
• Consider capacity pricing (slots) when query volume is steady and high; it bills compute time instead of bytes scanned.

Security and operational considerations

• Access. Use IAM roles at dataset level: the ingestion service account needs data editor rights on one dataset only, analysts get viewer rights. Keep raw readings and curated tables in separate datasets.
• Location. Choose the dataset region before loading data; it determines where readings are stored and which price list applies. Cross-region loads are billed for network transfer.
• Late and corrected data. Writing into old partitions resets their long-term storage clock and costs DML. Prefer appending corrections with a version column and resolving them in views.
• Quotas. Load jobs and partition modifications have daily quotas; check them before designing many small loads per day.
• Retention. Partition expiration deletes raw readings automatically. Keep the long-lived roll-ups in a separate table with a longer or no expiration.

Limitations and when not to use BigQuery for IoT data

• Live operations. Alarm screens that refresh every few seconds and need the latest value per point want an operational store with low-latency lookups, not a warehouse billed per scan.
• Frequent row updates. Our editorial view: BigQuery suits appends and large scans better than frequent changes to individual readings, which are charged as DML and, in old partitions, reset the long-term storage clock.
• Small estates. A few hundred meters at 15-minute intervals is a few million rows a year. Our editorial view: PostgreSQL is likely to cover that at lower cost and complexity; see How to Store IoT Sensor Data.
• Not on Google Cloud. If the rest of the platform is on AWS or Azure, moving every reading across clouds adds transfer cost and another security boundary. Compare Databricks vs BigQuery and Snowflake vs BigQuery.
• Unpredictable ad hoc scanning. Without partition filters and byte limits, on-demand billing can surprise you.

Pricing

Prices from the provider's official pricing pages, checked 27 October 2026, region us-central1 (Iowa), in USD, excluding tax. List prices only; discounts, commitments and your actual usage change the bill.

List prices from the BigQuery pricing page for us-central1 on 27 October 2026. Storage is shown on that page per GiB-hour; a month is billed at 730 hours (the page's own example gives 23.552 USD for 1 TiB of active logical storage for a full month). Long-term storage applies to a table or partition not modified for 90 consecutive days, which is why daily partitions keep most sensor history at the lower rate. Other regions and multi-regions have different prices.

Worked examples are estimates calculated from the list prices above; they are not quotes or measured bills.

Frequently asked questions

Sources

• BigQuery docs: Introduction to partitioned tables
• BigQuery docs: Introduction to clustered tables
• BigQuery docs: Manage partitioned tables
• BigQuery docs: Query partitioned tables
• BigQuery quotas and limits
• BigQuery docs: Storage Write API (gRPC)
• BigQuery docs: Storage Write API (REST)
• Pub/Sub docs: BigQuery subscriptions
• BigQuery pricing
• GoogleSQL data types (data type sizes)
• GoogleSQL time series functions
• BigQuery docs: Materialized views
• BigQuery ML: ML.DETECT_ANOMALIES
• BigQuery docs: Best practices for controlling costs

Checked 27 October 2026.

How we research these articles: our editorial method. Research-based from official documentation; SQL examples are run on a local database where the page says so. CodeWithSQL earns nothing from the vendors mentioned.