# Snowflake DSAR: hash bucket coverage planner

## Scope and inputs

- `x`: number of logical hash buckets (1–1,000,000,000).
- `y`: number of distinct account IDs in the request list (0–1,000,000), not request rows.
- `days`: selected period length.
- `P`: micro-partition count entered as a daily average or an entire-period total.
- `D`: data volume entered in GiB or TiB, using the same input basis as P.
- `t`: maximum target model coverage, entered as a percentage (0.1–100%).
- `k`: optional occupied logical bucket count from SQL using the same account set and bucket expression.

Distinct account IDs are assumed to map independently and uniformly to buckets. Multiple IDs can collide in the same bucket. The browser does not reproduce Snowflake HASH.

## Expected logical coverage

`f(x, y) = 1 − (1 − 1/x)^y`

This is both the probability that a preselected bucket is occupied and the expected fraction of occupied buckets. Expected occupied buckets are `x × f`.

For `y = 0`, coverage is zero. For `x = 1` and `y > 0`, coverage is 100%.

The ID list selects candidate buckets. Some IDs may have no events in the selected period. Logical request-list coverage is not a measurement of actual event presence, partitions scanned or partitions rewritten.

## Inverse planning

For `y > 0` and target fraction `0 < t < 1`:

`minimum_x = ceil(1 / (1 − (1 − t)^(1/y)))`

The planner verifies the adjacent integer boundary against the forward formula. For zero accounts or a 100% target, the minimum is one bucket. The result is a logical target, not a physical clustering recommendation or performance guarantee.

## Period and physical projections

For daily-average inputs:

- `P_period = P × days`
- `D_period = D × days`

For entire-period inputs, use P and D once. Switching the input basis converts the entered values while preserving period totals. Changing GiB/TiB preserves the underlying data volume.

Ideal projections:

- Partitions scanned: `P_period × f`
- Data scanned: `D_period × f`
- Scan reduction relative to the same unpruned period: `1 / f`

Zero coverage has no finite scan reduction ratio.

These projections require effective date/bucket grouping, reasonably balanced data volume and successful pruning. Multi-day projections assume the same unique account list and hash expression for each day, comparable physical layouts and additive daily volumes. Summed daily partition counts require day-disjoint micro-partitions; otherwise enter the distinct entire-period count. The planner does not infer which IDs have events on each day.

A bucket is not a physical micro-partition. If the average number of partitions per day is below x, fully isolating each day's buckets is impossible under the model. Poor historical date clustering may also cause scans outside the requested period.

## Observed SQL mode

Enter k from SQL and select **Observed buckets**. Main projections and the current comparison scenario then use `k / x` instead of f. The panel shows the difference from the model in percentage points.

Changing x or y clears the observation because its original definition no longer matches. For a nonempty request list, a valid observed count is an integer from one to `min(x, y)`. With zero accounts, the only valid count is zero. Enter the request-list result, not a count over matching event rows; those answer different questions.

The target solver, growth chart and bucket-count alternatives always use the mathematical model. Observed logical coverage does not remove the assumptions behind physical projections.

## SQL template

Replace the source table and ID column. Match the event table's exact identifier type and exact bucket expression; hashing numeric and string representations can differ. Do not add an arbitrary cast. The modulus must equal the selected x.

```sql
-- Template only: replace this source with your real request list.
WITH accounts AS (
  SELECT DISTINCT ACCT_ID
  FROM YOUR_DATABASE.YOUR_SCHEMA.DSAR_ACCOUNTS
  WHERE ACCT_ID IS NOT NULL
)
SELECT
  COUNT(*) AS unique_accounts,
  COUNT(DISTINCT ABS(MOD(HASH(ACCT_ID), 20))) AS occupied_buckets
FROM accounts;
```

This query counts candidate logical buckets from the request list without scanning the event table. Counting over matching event rows instead measures buckets with events in that scope; do not substitute that count for request-list coverage in this mode. Neither count is the fraction of physical micro-partitions read.

## Scenario comparison

Pin current settings as A, then edit B. A preserves its account count, bucket count, period, volumes, coverage source and observed bucket count. The table shows both model and applied coverage, full-period volumes, ideal scanned volumes and B − A differences. Different periods or coverage sources are flagged because they are not directly comparable.

The baseline and current inputs persist in the browser and are included in calculation links. Old calculation links containing x, y, p and d still load with one-day defaults.

## Collision simulation

For x up to 256, the grid assigns the current y IDs independently and uniformly at random. Each cell displays its bucket index and assigned ID count. It shows expected occupied buckets separately from this draw's actual occupied count.

Extra IDs in occupied buckets are `y − occupied_buckets`; this is not the number of colliding pairs. Buckets with at least two IDs have a separate color. A random draw never changes model or observed estimates. Changing x or y invalidates the previous draw. Random draws are not saved or shared.

## Interpretation

Use the model to compare logical layouts, SQL to measure a particular ID list, and query-profile evidence to evaluate actual execution. Partitions scanned, partitions containing modified rows and partitions rewritten are distinct metrics. Neither bucket coverage nor the ideal scan reduction factor predicts UPDATE duration.

## Local files and subdirectory hosting

All asset and document links are relative to `index.html`. Serve `dist/` from a domain root or a subdirectory, or open `dist/index.html` directly for local calculations. No build or server is needed for the calculation engine.

For `file:` URLs, **Copy local link** stores the inputs and pinned baseline in a URL fragment. Such links require the same files at the same path on the same device; they are not portable web links. Clipboard or browser storage may be unavailable, in which case a selectable link is shown and calculations continue without persistent storage. Reset also resets the local fragment so reloading does not restore an old calculation.

Use an HTTP server for normal web links and web hosting behavior. Existing query-based calculation links remain supported.