# How the BigQuery Source Detects and Groups Table Shards into Logical Families

> Learn how the BigQuery source detects table shards, uses regex to group them into logical families, and exposes them as single wildcard concepts. Discover shard count and range.

- Repository: [Google Cloud Platform/knowledge-catalog](https://github.com/GoogleCloudPlatform/knowledge-catalog)
- Tags: internals
- Published: 2026-07-16

---

**The Knowledge Catalog BigQuery source uses a regular expression pattern `_SHARD_SUFFIX_RE` to identify table names ending with 6-8 digits, extracts a common family prefix to group them into logical families, and exposes these as single wildcard concepts with metadata including shard count and range.**

The GoogleCloudPlatform/knowledge-catalog repository provides a reference implementation for building knowledge catalogs that abstract complex data sources. When working with BigQuery datasets that use daily or hourly sharding (such as `events_20210101`, `events_20210102`), the system avoids cluttering the catalog with thousands of individual table entries by automatically detecting these patterns and consolidating them into unified logical entities.

## Shard Detection via Regex Pattern

In [`okf/src/reference_agent/sources/bigquery.py`](https://github.com/GoogleCloudPlatform/knowledge-catalog/blob/main/okf/src/reference_agent/sources/bigquery.py), the source defines a regex pattern that identifies sharded table names:

```python
_SHARD_SUFFIX_RE = r"^(?P<prefix>.+?_)(?P<shard>\d{6,8})$"

```

This pattern matches any table name ending with an underscore followed by **6 to 8 digits**. It extracts two named groups:
- **`prefix`** – The common family identifier (e.g., `events_`)
- **`shard`** – The numeric suffix (e.g., `20210101`)

Tables following this naming convention (like GA4 daily exports or legacy partitioned tables) trigger the family grouping logic, while non-matching tables are handled as standalone concepts.

## Building Logical Families

While iterating over all tables returned by `client.list_tables()`, the source maintains a dictionary keyed by the extracted prefix:

```python
families[prefix].append(table_id)

```

Tables that match the regex are appended to their respective family lists. Tables that **do not** match the pattern are stored separately as singletons. This approach ensures that sharded tables collapse into a single logical entry while uniquely named tables remain independent.

## Constructing Family Concepts

After collecting all tables, the source processes each family to create a unified `ConceptRef` object. According to the implementation in [`okf/src/reference_agent/sources/bigquery.py`](https://github.com/GoogleCloudPlatform/knowledge-catalog/blob/main/okf/src/reference_agent/sources/bigquery.py), each family concept includes:

- **`wildcard=True`** – Signals that this concept represents a family rather than a single table
- **`family_prefix`** – The shared prefix extracted from the regex
- **`shard_count`, `first_shard`, `last_shard`** – Derived from sorting the shard list

The source maintains a mapping `self._family_index[family_concept_id] = shards_sorted` to preserve the concrete table IDs for later lookup. This allows the system to track which physical tables belong to each logical family without exposing them as separate catalog entries.

## Representative Table Selection

When reading a family concept, the source designates the **last shard** (the most recent table) as the representative table ID (`_representative_table_id`). This ID drives schema introspection, row sampling, and metadata extraction operations. By using the most recent shard, the catalog ensures that schema evolution is captured based on the latest table structure while maintaining the ability to query across the entire family range.

## Practical Usage Example

The following example demonstrates how to interact with sharded table families using the BigQuery source:

```python

# Create a source for a GA4 daily-sharded dataset

source = BigQuerySource(dataset="my-project.my_dataset")

# List all concepts – sharded tables appear as a single family

concepts = source.list_concepts()
family = next(c for c in concepts if c.hint.get("wildcard"))
print(family.id)                # ('tables', 'events_')

print(family.hint["shard_count"])   # e.g. 7

print(family.hint["last_shard"])    # e.g. 'events_20211231'

# Read the family – returns metadata plus shard info

metadata = source.read_concept(family)
print(metadata["family_prefix"])   # 'events_'

print(metadata["shard_count"])     # 7

print(metadata["first_shard"])     # 'events_20210101'

print(metadata["last_shard"])      # 'events_20211231'

# Sample rows from the most recent shard

rows = source.sample_rows(family, n=5)
print(rows)

```

## Summary

- **The regex pattern `_SHARD_SUFFIX_RE`** identifies sharded tables by matching names ending with 6-8 digits after an underscore.
- **Family grouping** occurs by extracting the common prefix and aggregating matching table IDs into a dictionary structure.
- **ConceptRef objects** with `wildcard=True` represent families, carrying metadata including `family_prefix`, `shard_count`, `first_shard`, and `last_shard`.
- **The last shard** serves as the representative table for schema introspection and sampling operations.
- **Implementation** resides in [`okf/src/reference_agent/sources/bigquery.py`](https://github.com/GoogleCloudPlatform/knowledge-catalog/blob/main/okf/src/reference_agent/sources/bigquery.py) with comprehensive test coverage in [`okf/tests/test_bigquery_source.py`](https://github.com/GoogleCloudPlatform/knowledge-catalog/blob/main/okf/tests/test_bigquery_source.py).

## Frequently Asked Questions

### What naming convention does the BigQuery source use to detect shards?

The source expects table names that end with an underscore followed by 6 to 8 digits, such as `events_20210101` or `transactions_2021121501`. The regex `(?P<prefix>.+?_)(?P<shard>\d{6,8})$` captures this pattern, requiring the numeric suffix to be between 6 and 8 characters long to qualify as a shard identifier.

### How does the system handle non-sharded tables?

Tables that do not match the `_SHARD_SUFFIX_RE` pattern are treated as singletons. Each non-matching table receives its own `ConceptRef` with `wildcard=False` in the hint metadata, ensuring they remain as individual catalog entries without family grouping.

### Which shard is used for schema introspection when reading a family?

The **last shard** (the most recent table in the sorted list) is designated as the `_representative_table_id`. This shard provides the schema for sampling and metadata operations, ensuring the catalog reflects the latest table structure while maintaining the ability to reference the entire family range.

### Where is the shard detection logic implemented in the repository?

The core logic is implemented in [`okf/src/reference_agent/sources/bigquery.py`](https://github.com/GoogleCloudPlatform/knowledge-catalog/blob/main/okf/src/reference_agent/sources/bigquery.py) within the Knowledge Catalog reference agent. The corresponding test suite in [`okf/tests/test_bigquery_source.py`](https://github.com/GoogleCloudPlatform/knowledge-catalog/blob/main/okf/tests/test_bigquery_source.py) verifies that three or more shards are correctly collapsed into a single family concept with accurate shard count and naming metadata.