> ## Documentation Index
> Fetch the complete documentation index at: https://docs.streamkap.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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:

<CodeGroup>
  ```SQL SQL theme={null}
  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
  ```
</CodeGroup>

<Info>
  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.
</Info>

### Scheduled cleanup (optional)

For high-volume tables, [schedule a query](https://cloud.google.com/bigquery/docs/scheduling-queries) to delete superseded rows, keeping only the latest per key:

<CodeGroup>
  ```SQL SQL theme={null}
  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, ...}
    );
  ```
</CodeGroup>

<Warning>
  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.
</Warning>

## 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](https://cloud.google.com/bigquery/docs/partitioned-tables#choose_daily_hourly_monthly_or_yearly_partitioning).

<Info>
  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.
</Info>

### 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](https://cloud.google.com/bigquery/docs/partitioned-tables#partitioning_versus_clustering).

Both the partition field and clustering fields can be set directly on the destination — see [BigQuery setup](/google-bigquery).

<Warning>
  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.
</Warning>