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.

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.

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

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

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

1. Pause your Artie pipeline

   In the Artie dashboard, open your pipeline, click Pause pipeline, and choose Pause delivery only. Artie keeps reading changes and holds them until you resume, so nothing is merged while you restructure the table.

2. 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:

   SQLCopyCopied

   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:

   SQLCopyCopied

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

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

4. Deploy and verify

   Click Save and Deploy. In the deploy dialog, leave Resume pipeline after deployment on so the pipeline resumes. 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

Related

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