Best Practices

Reduce BigQuery costs and speed up queries with partitioning, clustering, and deduplication patterns for Streamkap's at-least-once CDC streams.

BigQuery cost and query speed depend mostly on how much data each query scans. Reference fewer columns (avoid SELECT *), partition and cluster your tables, and reference the partition/cluster keys in your queries.

Deduplicating your data

Streamkap writes to BigQuery via the Storage Write API and guarantees at-least-once delivery. Rows are appended — retries can write the same change more than once — so a table can hold duplicate rows for the same key. This is expected; you get the current state by deduplicating at query time with a view, and optionally trim stored duplicates with a scheduled cleanup.

Streamkap stamps every row with metadata columns you can order by to find the latest version of each record:

• _streamkap_source_ts_ms — when the change occurred at the source
• _streamkap_offset — always increases, so it breaks ties when two changes share the same source timestamp

Latest-record view

Expose the deduplicated, current state as a view. Replace the { ... } placeholders:

CREATE VIEW {dataset}.{view}
  OPTIONS(description="Latest version per key, excluding deleted records")
AS
SELECT * EXCEPT(_dedupe_rn)
FROM (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY {primary_key_column, ...}
      ORDER BY _streamkap_source_ts_ms DESC, _streamkap_offset DESC
    ) AS _dedupe_rn
  FROM {dataset}.{table}
)
WHERE _dedupe_rn = 1
  AND __deleted = 'false';  -- drop keys whose latest change was a delete

Scheduled cleanup (optional)

For high-volume tables, schedule a query to delete superseded rows, keeping only the latest per key:

DELETE FROM {dataset}.{table} t
WHERE STRUCT(t.{primary_key_column, ...}, t._streamkap_source_ts_ms)
  NOT IN (
    SELECT AS STRUCT {primary_key_column, ...}, MAX(_streamkap_source_ts_ms)
    FROM {dataset}.{table}
    GROUP BY {primary_key_column, ...}
  );

Datasets

Use a separate dataset

Create a dedicated dataset for Streamkap to avoid conflicts with existing data.

For the dataset location, multi-region offers better redundancy at some cost to latency/query performance; single-region is faster. With single-region datasets you can add table snapshots and scheduled exports to improve redundancy.

Tables

Set a partition key

Partition by a time unit (hour, day, month, year) rather than a number — BigQuery is built for analyzing data over time, and time-unit partitioning lets you set partition expiration to drop old data automatically.

If there’s no natural date/timestamp in your data, partition on the Streamkap change-event timestamp (_streamkap_source_ts_ms). See Choose Daily, Hourly, Monthly or Yearly Partitioning.

Expire old partitions automatically

For high-volume tables, set Partition Expiration in Days on the destination to drop partitions older than a given age — BigQuery deletes the expired partitions for you, so storage and query scans stay bounded. It applies to any time-unit partitioning (DAY, HOUR, MONTH, YEAR); fractional days are allowed for sub-day expiration with HOUR partitioning. Leave it blank to keep all partitions, and note it has no effect when Time Partitioning is NONE (a non-partitioned table has nothing to expire).

Set a cluster key

Cluster by one or more columns you commonly filter or aggregate on that have high cardinality. If unsure, your table’s primary key column(s) are a reasonable default. See Partitioning versus Clustering.

Both the partition field and clustering fields can be set directly on the destination — see BigQuery setup.