Best Practices - Streamkap Docs

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

The view scans the whole table each time. For large, high-churn tables, also run the scheduled cleanup below so queries (and the view) scan fewer rows.

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, ...}
  );

BigQuery restricts UPDATE/DELETE on rows still in the streaming write buffer. Scope cleanup to older rows (e.g. add AND TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), t._streamkap_source_ts_ms, MINUTE) > 90) to avoid errors on very recently streamed data.

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.

For database sources, the change-event timestamp is the snapshot time for the initial backfill, and the actual change time thereafter. Keep this in mind when partitioning on it.

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.

The partition field and clustering fields are destination-wide — one setting applied to every table the destination creates. They can’t be set per table.Each field you specify must exist in every table the destination writes. If a field is missing from a table (or the partition field isn’t a date/time column), BigQuery rejects that table’s creation and the destination goes into an error state — it does not skip the setting or fall back to a default.

Partition field: for mixed-schema pipelines, the Streamkap metadata column _streamkap_source_ts_ms is a safe universal choice — it’s a timestamp present on every table.
Clustering fields: cluster on the columns you actually filter or join on (usually the primary key). There’s rarely a single good clustering key across different tables, so for mixed-schema pipelines either leave clustering unset or give each table (or group of same-shaped tables) its own destination clustered on its own key. Don’t reach for _streamkap_offset just because it’s present everywhere — it’s never a query filter, so clustering on it gives no benefit.

Partitioning and partition expiration are fixed when a table is first created — changing either later only applies to newly created tables, not existing ones.

​Deduplicating your data

​Latest-record view

​Scheduled cleanup (optional)

​Datasets

​Use a separate dataset

​Tables

​Set a partition key

​Expire old partitions automatically

​Set a cluster key

Deduplicating your data

Latest-record view

Scheduled cleanup (optional)

Datasets

Use a separate dataset

Tables

Set a partition key

Expire old partitions automatically

Set a cluster key