Menu
Product analytics rules: calendar spine cross-joins, date boundary caps, event type pivoting, first-run NULL behavior.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
Based on SOC occupation classification
| name | domain-product |
| description | Product analytics rules: calendar spine cross-joins, date boundary caps, event type pivoting, first-run NULL behavior. |
When aggregating usage or event metrics, drive FROM the fact/event table (events, sessions, pageviews), not the dimension table (users, features, pages). Users with zero activity MUST NOT appear in activity reports - they have no data to aggregate.
Daily metrics models that cross-join a calendar spine with entities (users, features, pages) MUST cap the join at current_date - generating rows beyond today produces phantom future rows with zero metrics that inflate row counts.
If the YML description says "from creation date to current date," add AND spine.date_day >= entity.created_at::date AND spine.date_day <= current_date to the join condition - both bounds are required.
NEVER cross-join without a date cap - unbounded spines grow indefinitely and break row count expectations.
When an entity has a created_at or equivalent timestamp, the spine MUST start from that date, not from the earliest date in the spine - starting earlier produces rows before the entity existed.
When an events table has a type or event_name column, pivot distinct values into separate metric columns - each event type becomes a count_<type> column in the output.
Do NOT filter the events table to a single type before pivoting - all types must appear in the output as separate columns, using conditional aggregation (COUNT(CASE WHEN type = 'x' THEN 1 END)).
Read the YML or a sibling model to confirm the exact set of event types expected - inventing column names from partial data causes schema mismatches.
On first build with no prior state, rolling window columns (7-day avg, 30-day sum) MUST be NULL for the first N-1 periods - this is correct behavior, not a bug.
Do NOT substitute 0 for NULL in rolling window columns - NULL means "insufficient history," 0 means "activity was zero."
Period-over-period columns follow the same rule: NULL on first build. See dbt-workflow "Incremental Models and Period-Over-Period Columns" for the full rule.
Load when working with Xata Postgres branches: forking a branch, building or testing dbt models on a branch, wiring dbt to a branch's credentials, diffing two branches, or the pre-merge impact report. Covers create_xata_branch, delete_xata_branch, get_dbt_profile, xata_branch_diff, schema_diff_branches, and pgroll migrations.
Load at Step 1 before exploring the project. Covers output shape inference, incremental model handling, and what to trust in YML.
Use this skill before writing any SQL query. Covers: output shape inference (cardinality clues from the question), efficient schema exploration, iterative CTE-based query building, structured verification loop (row count, NULL audit, fan-out check, sample inspection), error recovery protocol, saving output to result.sql and result.csv, turn budget management, and common benchmark traps.
Populate the knowledge base from dbt project research. Proposes entries across all 6 categories at org, project, and connection scopes.
Load FIRST before any dbt project work. Covers the full 8-step dbt workflow: project scanning, skill loading, validation, macro discovery, research, technical spec, SQL writing, and verification. Also covers output shape inference, incremental model handling, and what to trust in YML.
Loaded at Step 2 for the full workflow. Covers column naming, type preservation, JOIN defaults, lookup joins, sibling models, materialization, packages, and filtering rules.
