Enable partitioning

Partitioning your BigQuery tables reduces the amount of data scanned during Artie’s merge operations, which lowers merge latency and can significantly reduce your BigQuery bill. How you enable it depends on whether the table already exists in BigQuery:

New tables - Artie creates the table partitioned by hour, day, month or year when you choose a partition column and granularity in table settings.
Existing tables - BigQuery cannot partition a table after it has been created, so you recreate the table and then update table settings.

The examples in this guide use this source table in Postgres:

CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Choose a partition column

Pick a TIMESTAMP or DATE column that your queries frequently filter on. Good candidates are columns like created_at, updated_at, or event_date. For most CDC workloads, daily time partitioning on a created_at-style column provides the best balance between partition pruning and manageability.

Choose a partition granularity

Pick how much time each partition covers. Artie defaults to Daily.

Granularity	Use it when	Column types
Hourly	High-volume tables that only keep a few months of data	`TIMESTAMP`, `DATETIME`
Daily	Most CDC workloads	`TIMESTAMP`, `DATETIME`, `DATE`
Monthly	Tables with many years of history, or low volume per day	`TIMESTAMP`, `DATETIME`, `DATE`
Yearly	Very long history with low volume per month	`TIMESTAMP`, `DATETIME`, `DATE`

BigQuery limits a single job to modifying 4,000 partitions, and a table to 10,000 partitions. A backfill merge can touch rows from across the whole table, so pick a granularity where your partition column spans fewer than 4,000 partitions: about 5 months of hours, or about 10 years of days.

New tables

If Artie has not created the table in BigQuery yet, set the partition column and granularity before the table’s first sync and Artie creates the partitioned table for you.

Set the partition column and granularity in table settings

In the Artie dashboard, go to your pipeline > Edit > Tables > find the events table > Table settings.Turn on Use a merge predicate and select the partition column (e.g. created_at). Then pick a Partition granularity. It defaults to Daily, and Hourly is only offered for timestamp columns.See Table settings for more details on table-level configuration.

Deploy

Click Save and then Deploy. When Artie creates the table, it adds the partitioning clause for you. For a TIMESTAMP column like created_at:

Granularity	Partitioning clause
Hourly	`PARTITION BY TIMESTAMP_TRUNC(created_at, HOUR)`
Daily	`PARTITION BY DATE(created_at)`
Monthly	`PARTITION BY TIMESTAMP_TRUNC(created_at, MONTH)`
Yearly	`PARTITION BY TIMESTAMP_TRUNC(created_at, YEAR)`

A DATETIME column uses DATETIME_TRUNC, and a DATE column uses DATE_TRUNC for monthly and yearly partitions.Artie also includes the partition column in merge predicates, which enables BigQuery to prune partitions during merges.

Verify

Follow Verify partitioning once the table has data.

Things to know:

The partition column must be a date or timestamp column. If it is not, or if the column is hashed or encrypted, Artie stops with an error instead of creating an unpartitioned table.
Artie creates time partitions only. To use integer range partitioning, create the partitioned table in BigQuery yourself before the first sync.
Changing the granularity later doesn’t change an existing table, because BigQuery can’t change a table’s partitioning after it’s created. To switch, recreate the table.
Keep the partition column’s value fixed after a row is inserted. Artie uses the incoming row’s value to find the partition to merge into, so a row that moves to a different partition can be duplicated. This matters most with hourly partitions on a column like updated_at.
If your partition column spans more than 4,000 partitions at the granularity you picked, pick a coarser one (see Choose a partition granularity).

Existing tables

If the table already exists in BigQuery and is not partitioned, recreate it as a partitioned table while your pipeline is paused. You need permissions to run DDL statements (CREATE TABLE, DROP TABLE, ALTER TABLE) in your BigQuery dataset.

Pause your Artie pipeline

In the Artie dashboard, navigate to your pipeline and click Pause. This prevents new data from being merged while you restructure the table.

Recreate the table with partitioning in BigQuery

Run the following SQL in the BigQuery console to create a partitioned copy, drop the original, and rename the copy:

This operation drops the original table. Verify that the partitioned copy contains all expected data before running the DROP TABLE statement.

CREATE TABLE artie.events_copy
  PARTITION BY DATE(created_at)
  AS SELECT * FROM artie.events;

For another granularity, swap in its partitioning clause, for example PARTITION BY TIMESTAMP_TRUNC(created_at, MONTH).After verifying the data in events_copy looks correct:

DROP TABLE artie.events;
ALTER TABLE artie.events_copy RENAME TO events;

Update table settings in Artie

In the Artie dashboard, go to your pipeline > Edit > Tables > find the events table > Table settings.Turn on Use a merge predicate and select the partition column (e.g. created_at). Set Partition granularity to match how you partitioned the table. This tells Artie to include the partition column in merge predicates, which enables BigQuery to prune partitions during merges.See Table settings for more details on table-level configuration.

Deploy and verify

Click Save and then Deploy to resume your pipeline, then follow Verify partitioning.

Verify partitioning

To confirm partitioning is active, run the following query in BigQuery:

SELECT table_name, partition_id, total_rows
FROM `artie.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = 'events'
ORDER BY partition_id;

You should see multiple rows, one per partition, instead of a single NULL partition.

Background

What is a partitioned table?

A partitioned table is divided into segments called partitions, making it easier to manage and query your data. By dividing a large table into smaller partitions, you can improve query performance and control costs by reducing the number of bytes read by a query. You partition tables by specifying a partition column which is used to segment the table.

Why use table partitions?

Faster merges - Artie’s merge operations scan only the relevant partitions instead of the entire table.
Lower costs - Fewer bytes scanned means lower BigQuery compute charges, especially under on-demand pricing.
Higher quotas - Partitioned tables have higher DML quotas than unpartitioned tables.
Partition-level management - You can write to, delete, or expire individual partitions without affecting the rest of the table.

What are the different partitioning strategies?

Partitioning type	Description	Artie recommendation
Time partitioning	Partitions based on a `TIMESTAMP`, `DATE`, or `DATETIME` column. Supports hourly, daily, monthly, and yearly granularity.	Recommended. Works well with CDC workloads where data naturally has a time dimension.
Integer range	Partitions based on value ranges of an integer column (e.g. `customer_id` 0–9 in one partition, 10–19 in the next).	Useful when your data has no natural time column but has a well-distributed integer key.
Ingestion time	Partitions based on when rows were inserted into BigQuery.	Not recommended. Artie’s merge operations update existing rows, which can create duplicates across partitions since the ingestion timestamp changes on each merge.

BigQuery destination setup - Configure BigQuery as a destination in Artie, including queue priority settings.
Capacity pricing - Understand BigQuery’s pricing models and avoid unexpected costs.

​Choose a partition column

​Choose a partition granularity

​New tables

​Existing tables

​Verify partitioning

​Background

​Related

Choose a partition column

Choose a partition granularity

New tables

Existing tables

Verify partitioning

Background

Related