{
    "table_name": "Website_Analytics",
    "primary_key": "WEBSITE, YEAR, PAGE PATH",
    "candidates": [
        {
            "rank": 0,
            "partition_key": "YEAR",
            "partition_key_label": "T",
            "partition_key_reasoning": "YEAR is the time axis of the yearly page-level metric snapshots. It has 11 distinct values (2014-2024), RPUV ~5681, and top-3 coverage of only 37%. Partitioning by YEAR gives a complete snapshot of all pages for each period.",
            "clustering_key": "PAGE PATH",
            "clustering_key_label": "X",
            "clustering_key_reasoning": "PAGE PATH is the measured entity (page) within each year, with 37,410 unique values and RPUV 1.67. It is not unique on its own, since the same path appears under several websites and years, so it is combined with WEBSITE to identify an entity. WEBSITE has only 11 values, with its top 3 covering 90.5%, so it works poorly as the lone clustering key. PAGE PATH is better for sorting, and the WEBSITE+PAGE PATH pair identifies the page.",
            "world_line": "PAGE PATH",
            "session_column": null,
            "confidence": "MEDIUM",
            "notes": "Each row is the aggregated metrics (views, entrances, bounce and exit rates) for a page on a website in a given year, so this is a periodic snapshot. The composite key WEBSITE+YEAR+PAGE PATH is assumed unique. PAGE URL is effectively redundant, being WEBSITE plus PAGE PATH. The _ingestion_timestamp, _batch_id, _source_file and _source_system columns are constants and unusable as keys."
        },
        {
            "rank": 1,
            "partition_key": "WEBSITE",
            "partition_key_label": "T",
            "partition_key_reasoning": "WEBSITE has 11 values and RPUV ~5681, which is within a workable range. However, its top 3 coverage is 90.5%, so partitions would be heavily skewed. It is a coarse grouping dimension rather than a time axis.",
            "clustering_key": "YEAR",
            "clustering_key_label": "X",
            "clustering_key_reasoning": "YEAR orders each site's yearly history, but within a site it has only 11 values. Combined with PAGE PATH it would give a page's history over time.",
            "world_line": "WEBSITE",
            "session_column": null,
            "confidence": "LOW",
            "notes": "This alternative serves per-site analysis. It is weaker because of the skew toward www.brla.gov, brgov.com and data.brla.gov, and because it does not follow the T/X convention of partitioning by time."
        }
    ]
}