BigQuery table partitioning for Daton pipelines

Need help with something?

Talk to data expert

BigQuery table partitioning for Daton pipelines

Purpose: Step-by-step workflow to partition Daton-loaded tables safely (pause → rebuild → swap → resume), plus field suggestions and operational notes.

1. BigQuery behavior and constraints

Partitioning is defined when the table is created. Time-unit and other column-based partitioned tables use CREATE TABLE with a PARTITION BY clause; see Google’s guide on creating partitioned tables.
 
You cannot change the partitioning “kind” or expression in place with CREATE OR REPLACE. The same DDL page states (under Limitations): “It is not possible to use the OR REPLACE modifier to replace a table with a different kind of partitioning. Instead, DROP the table, and then use a CREATE TABLE ... AS SELECT ... statement to recreate it.” That is the supported pattern when you need a different partition layout—including migrating from an unpartitioned table to a time-unit column–partitioned table.
 
What you can change later on an already-partitioned table (without redefining PARTITION BY) includes options such as partition expiration and require partition filter, via ALTER TABLE ... SET OPTIONS—see Managing partitioned tables (partition expiration and partition filter sections).
 
Practical takeaway for Daton tables: Build a new partitioned table (for example CREATE TABLE ... PARTITION BY ... AS SELECT * FROM ...), validate, then swap names or repoint the pipeline—consistent with Google’s stated DROP + CREATE TABLE ... AS SELECT ... approach when partitioning must change.

Steps to be followed in brief are -

  1. Pick partition column
  2. Pause Daton for that source.
  3. CREATE TABLE ... PARTITION BY ... AS SELECT into a new table; validate counts and sample data.
  4. Swap to the original table name (or update Daton destination per product capability).
  5. Resume Daton and verify the next sync.
  6. Update downstream queries/dashboards to always filter on the partition column where possible.

Follow the below section for detailed steps and guide:

Step 1 — Plan

  1. Identify the table(s) and the partition column (see Section 4).
  2. Confirm the column type is compatible with BigQuery partitioning:
  • Prefer DATE for PARTITION BY DATE(col).
  • TIMESTAMP / DATETIME are supported with the appropriate PARTITION BY expression.
  1. Estimate size and downtime: large tables take longer to copy; Daton must stay paused until the swap is complete.

Step 2 — Pause the Daton source (connector)

  1. In Daton, pause the connector (or the specific table) that writes to the target table(s).
  2. Wait for in-flight loads to finish so you do not have partial writes during the migration.
Pausing prevents Daton from writing while you replace or rename tables, which avoids schema drift, duplicate loads, or failed jobs mid-migration.

Step 3 — Create a partitioned copy of the table

Run a DDL job in BigQuery (Console, scheduled query, or API). Pattern:
Option 1 — Same dataset, temporary name then swap
-- 1) Create partitioned clone (example: daily partition on date_start)CREATE TABLE `project.dataset.table_name__partitioned`
PARTITION BY date_start
ASSELECT *FROM `project.dataset.table_name`;
Adjust:
  • PARTITION BY date_start if date_start is already DATE.
  • If date_start is STRING, cast in the partition expression, e.g. PARTITION BY DATE(PARSE_DATE('%Y-%m-%d', date_start)) — only if the string is consistently parseable; fix bad values first or exclude them in a controlled SELECT.
Option 2 — Partition on ingestion-time (only if you do not have a reliable business date column)
PARTITION BY DATE(_PARTITIONTIME)
Ingestion-time partitioning is rarely ideal for Daton marketing tables where date_start (or similar) matches how users query.

Step 4 — Validate the new table

  1. Row count (should match source unless you filtered):
 SELECT COUNT(*) FROM `project.dataset.table_name`;
 SELECT COUNT(*) FROM `project.dataset.table_name__partitioned`;
  1. Spot-check a few partitions and min/max dates:
 SELECT date_start, COUNT(*) 
 FROM `project.dataset.table_name__partitioned`
 GROUP BY 1ORDER BY 1 DESC
 LIMIT 20;
  1. Run a test query with a WHERE date_start BETWEEN ... and confirm bytes processed drops versus the old table (query info in BigQuery UI).

Step 5 — Swap names (replace the table Daton expects)

Use ALTER TABLE ... RENAME TO (no second full copy)
BigQuery supports renaming a table in place with DDL (Rename a table). The new table name is unqualified and stays in the same dataset as the original. Google documents that this operation recreates the table while preserving the original creation time (watch dataset-level table expiration if enabled). 
Limitations include: no external tables, no concurrent DML on the table during rename.
Typical swap after table_name__partitioned is validated:
ALTER TABLE `project.dataset.table_name` RENAME TO table_name__old;
ALTER TABLE `project.dataset.table_name__partitioned` RENAME TO table_name;
-- After Daton resumes successfully and you no longer need the backup:-- DROP TABLE `project.dataset.table_name__old`;
You do not need to drop the old table first if you do the two-step rename above. The first rename frees table_name, then the second rename takes that name.

Step 6 — Resume Daton

  1. Resume the connector.
  2. Watch the next sync for errors (schema mismatch, partition column nulls, etc.).
  3. Re-run the bytes processed test query after new data lands.

3. Best practices

Topic
Recommendation
Partition column
Use a stable reporting date present on almost every row (e.g. date_start, date_stop, event date). Avoid high-cardinality IDs as the partition key.
Column type
Prefer native DATE / TIMESTAMP for the partition field; avoid partitioning on uncasted messy strings.
NULLs
Rows with NULL partition keys go into a NULL partition; heavy NULLs hurt pruning. Clean or coalesce where appropriate.
Partition granularity
Default DAY is appropriate for most marketing/ads daily reports. Use MONTH only with good reason.
Clustering
Optional: add CLUSTER BY common filter columns (e.g. campaign_id, region) after you partition, for extra pruning on equality filters.
Incremental models downstream
If dbt or other tools read these tables, ensure merge keys and incremental filters align with the partition column to maximize pruning.

4. Choosing a partition column

1. Which date column do analysts put in the WHERE clause when filtering by date range? That column is almost always the right choice. Partitioning only saves bytes when queries filter on the partition column — so pick the one that is already being used to scope time.
2. Does that column have a clean DATE or TIMESTAMP type with very few NULLs? If yes, you are ready to go. If it is stored as a STRING, check whether the values are consistently formatted (e.g. '2024-01-15') — a reliable cast like DATE(PARSE_DATE('%Y-%m-%d', col)) still works. Rows with a NULL partition value land in a separate __NULL__ partition and do not benefit from pruning.
3. If more than one column qualifies, which one represents when the event happened rather than when it loaded? Prefer the business event date (e.g. the ad reporting date, the order placement date) over the pipeline ingestion timestamp (_daton_batch_runtime). Analysts query by event date, not load time.

Shortcut: Open the table in BigQuery Console, look at the schema, and ask ”what would I put in a WHERE clause if I only wanted last month's data?” — that column is your partition key.

Quick-pick by platform

Always confirm the exact column name and type in your dataset before running the DDL — connector versions and client configurations can differ.
Platform / Table type
Suggested partition column
Notes
Meta / Facebook Ads (insights, breakdown tables)
date_start
Reporting window start date; what analysts filter by for date ranges
Google Ads (campaign, ad group, keyword stats)
segments_date or date
The calendar day the metrics belong to
Amazon Ads (sponsored products, brands, display)
date
Daily performance date
Shopify Orders
DATE(created_at)
Cast TIMESTAMP → DATE; use order placement date, not updated_at
Shopify other events (refunds, fulfillments)
DATE(created_at)
Same pattern — when the event occurred
Amazon Seller (orders, traffic, sales)
date or DATE(purchase_date)
Depends on the specific report type
TikTok Ads
stat_time_day or date
Daily stats date
No reliable business date (lookup/dimension tables)
DATE(_PARTITIONTIME) — ingestion time
Last resort only; useful for tables that are rarely queried with a date filter

5. Troubleshooting

Symptom
Likely cause
What to check
CREATE TABLE ... PARTITION BY fails
Wrong type, NULL partition expression, or invalid cast
Inspect schema; try SAFE.PARSE_DATE; sample bad rows.
Queries still scan full table
Filter not on partition column, or dynamic SQL without date filter
Ensure WHERE date_start ... (same column as PARTITION BY).
Daton job fails after resume
Table replaced with different schema / permissions
Compare schema; grants on new table; Daton destination config.
Duplicate data after migration
Overlap between manual copy and Daton reload
Keep Daton paused until swap is done; use full replace strategy once.
segmentation sharding

Related Articles

Table of Contents

1. BigQuery behavior and constraints 2. Recommended workflow (pause → partition → resume) Step 1 — Plan Step 2 — Pause the Daton source (connector) Step 3 — Create a partitioned copy of the table Step 4 — Validate the new table Step 5 — Swap names (replace the table Daton expects) Step 6 — Resume Daton 3. Best practices 4. Choosing a partition column Quick-pick by platform 5. Troubleshooting