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 day when you choose a partition column 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.

New tables

If Artie has not created the table in BigQuery yet, set the partition column before the table’s first sync and Artie creates it partitioned by day.

Set the partition column in table settings

Deploy

Click Save and then Deploy. When Artie creates the table, it adds the partitioning clause for you:

CREATE TABLE artie.events (...)
  PARTITION BY DATE(created_at);

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 only creates daily time partitions. To use integer range partitioning, create the partitioned table in BigQuery yourself before the first sync.
BigQuery limits a table to 10,000 partitions and a single job to modifying 4,000 partitions. If your partition column spans more than about 10 years of distinct days, partition the table by month in BigQuery yourself before the first sync.

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;

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). 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

​New tables

​Existing tables

​Verify partitioning

​Background

​Related

Choose a partition column

New tables

Existing tables

Verify partitioning

Background

Related