# Universal Query Standards

These rules apply to ALL partner data queries. Load this file before building any query.

## Response Style

- Be concise and technical — analysts value precision over verbosity.
- Lead with the direct answer, then provide supporting context.
- Recommend the single most efficient option that meets the analyst's needs. Do not present multiple alternatives when one clearly fits.
- **Proceed confidently when context is sufficient and releveant documentation is found.** Only ask for clarification when ambiguity would lead to a materially wrong result — e.g., the wrong partner, wrong metric type, wrong entity level, or wrong country. If reasonable defaults exist (most common entity, US, national level), use them and note the assumption.
- **Do not execute the final output query.** Validate that the final query compiles (using compile-only validation), then present it to the analyst. Only execute it if the analyst explicitly asks.
- **Only run intermediate queries when the result would change the SQL you write.** Ask: *"If the answer were different, would I write different SQL?"* If no, skip the query. Examples of necessary intermediate queries: discovering the exact string format of a categorical value, verifying a column name, resolving an ambiguous entity ID. Examples of unnecessary intermediate queries: confirming a date range has data (the analyst will see that when they run the query), re-checking a value already confirmed earlier in the session, verifying a standard reference value like a country code. When in doubt, build the query with reasonable defaults and note any assumptions — do not run a query just to reduce uncertainty that doesn't affect the output.

## SQL Formatting

- **Always use fully qualified three-part names**: `DATABASE.SCHEMA.OBJECT` in every FROM, JOIN, subquery, and CTE. Never use two-part names (`SCHEMA.TABLE`).
- **Never use placeholders** in SQL output — no `[DATABASE]`, `<DATABASE>`, `YOUR_DATABASE`, `{database}`, or similar. If you do not know the correct database for an object, look it up in the partner's data overview or via the catalog before writing SQL. If it cannot be confirmed, tell the analyst rather than guessing.

## Use the Data Catalog for Column Names

Never guess column names. Always verify using the Snowflake data catalog:
- `DESCRIBE TABLE <database.schema.object>` — shows column names, types, and descriptions
- `#table` syntax — inline catalog lookup

Column descriptions often contain important context about valid values, data availability by country, and usage notes.

## Discovering Valid Filter Values

**Do NOT guess partner-specific categorical filter values** where the exact string format is non-obvious. Values are case-sensitive and exact (e.g., `'RecordingSales'` not `'recording_sales'`). Use the two-step process below **only when the format is genuinely uncertain** — see "When to Discover Values" before running anything.

### Step 1: Check Column Descriptions (Free — No Query Cost)

When inspecting a table via `DESCRIBE TABLE` or `#table`, check whether the column description lists valid values. Many categorical columns document their accepted values in the description field. This is the preferred method.

### Step 2: Scoped SELECT DISTINCT (Fallback — Small Query)

If the column description does not list values, run a scoped `SELECT DISTINCT` to discover them. **Always scope to a small data slice** to keep the query fast on large tables:

```sql
-- Scope to one recent date + one country to hit cluster keys — fast even on billion-row tables
SELECT DISTINCT <column_name>
FROM <database.schema.table>
WHERE <date_column> = CURRENT_DATE() - 1
  AND <country_column> = 'US'
ORDER BY 1;
```

Adjust the scoping columns based on what the table supports — use whatever date or partition column exists for the partner.

### When to Discover Values

Run discovery only when the **exact string format a partner uses is genuinely uncertain** — for example, whether a categorical value is `'OnDemand'`, `'on_demand'`, or `'On Demand'`.

**Do NOT run discovery for:**
- **Standard reference values** — ISO country codes (`'US'`, `'GB'`, `'AA'` for worldwide), date formats, numeric IDs. These are unambiguous; apply them directly.
- **Values already confirmed in this session** — if a value was used successfully earlier in the conversation, reuse it without re-discovering.
- **Columns already documented in the partner's `query-guide.md`** — if valid values are listed there, use them directly.
- **Analyst-provided values that map to a known standard** — e.g. "global" → `'AA'`, "US streams" → `COUNTRY_CODE = 'US'`. Apply the mapping directly.

**Run discovery when:**
- The analyst describes a filter concept in plain language and the exact partner string encoding is unknown (e.g. the analyst says "audio only" but the value could be `'Audio'`, `'AUDIO'`, or `'audio_stream'` depending on the partner)
- Using a breakout or categorical column for the first time whose valid values are not in `query-guide.md`
- The analyst explicitly asks what filter options are available for a column

## Aggregation Rules

- **Always use SUM()** for quantity/metric columns unless the partner documentation explicitly states otherwise. Many partner datasets contain adjustment records (positive and negative values for corrections, returns, etc.). Failing to SUM will produce inaccurate results.
- **Never use COUNT(*)** as a proxy for volume — it counts correction/adjustment records too.
- **Use GROUP BY ALL** in Snowflake for convenience when all non-aggregated columns should be grouped.

## Data Source Selection

When a partner provides multiple tables at different granularity levels, or your team has built pre-aggregated models on top of raw data, **always prefer the lightest source that meets the analyst's need**:

1. **Pre-aggregated / internal models** (if the partner has them) — fastest, cheapest. Use when the time frame and grain match.
2. **Primary / standard tables** — the default for most queries.
3. **Granular / detailed tables** (if the partner has them) — most expensive. Use only when the analyst explicitly needs finer granularity.

Not all partners have multiple tiers. Check the partner's `references/data-overview.md` for what's available.

### Date-Range Interpretation

When the analyst describes a time frame, interpret it against today's date to determine if a pre-aggregated model can serve it — avoiding expensive scans on raw tables.

| Analyst Says | Interpretation | Preferred Source |
|---|---|---|
| "this year", "in [current year]" | Year-to-date | Pre-aggregated model (if partner has one) |
| "this week" | Week-to-date | Pre-aggregated model (if partner has one) |
| "last week", "the week of [date]" (completed) | Completed period | Pre-aggregated / period-end model |
| "this month" or named current month | Month-to-date | Monthly model if month is complete; otherwise raw tables for partial month |
| "all time", "since release" | Full history | Pre-aggregated model (if partner has one) |
| Specific completed date range (e.g., "Jan 1 – Jan 31") | Fixed range | Check if a pre-aggregated model covers it before falling back to raw tables |
| Custom range that doesn't align to a model boundary | Custom range | Raw tables with date filter |

**Rules:**
- If the time frame maps to a pre-aggregated model, use it. Do not build a date-range query across raw tables when a model already has the answer.
- If a model covers the need, recommend it directly. Do not also mention the raw table it was built from.
- Not all partners have pre-aggregated models. Check the partner's reference docs for available model types.

## Query Performance

- **Use cluster keys in WHERE clauses** — partner-specific cluster keys are documented in each partner's query guide. Filtering on cluster key columns dramatically improves scan performance.
- **Avoid SELECT *** — only select the columns needed for the analysis.
- **Scope date ranges** — always include a date filter to avoid scanning the full table history.