القائمة
E-commerce domain knowledge: transaction lifecycle, driving tables, status filtering.
التثبيت باستخدام Codex أو Claude انسخ هذا Prompt والصقه في Codex أو Claude أو مساعد آخر ليراجع صفحة Skill ويثبّتها لك.
استنادا إلى تصنيف SOC المهني
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.
| name | domain-ecommerce |
| description | E-commerce domain knowledge: transaction lifecycle, driving tables, status filtering. |
BEFORE writing any purchase or revenue metric, check: does a separate
returns source table exist? A "separate returns table" means a distinct
raw source table (not a dbt model or ref) that records returns independently.
A downstream dbt model like lost_revenue that aggregates returns FROM the
same fact table is NOT a separate returns table.
IF YES (a raw source table for returns exists, separate from the main fact table) - the main fact table records only sales. Do NOT add a return filter. Filtering drops valid sales rows that are tracked in the other table.
IF NO (returns are rows in the same fact table via a status/flag column,
even if a dbt model aggregates those returns separately) - Exclude them
with WHERE status_col NOT IN (...) BEFORE any GROUP BY. Use WHERE, not
CASE WHEN - CASE WHEN zeroes out return rows but keeps return-only entities
in the output with fake purchase_total=0. A customer who bought 5 items
and returned 3 made 2 purchases - not 5.
When a model computes metrics by aggregating a fact table (SUM, COUNT, AVG on transactions), the fact aggregation MUST be the FROM clause - driving from the dimension table and LEFT JOINing facts produces rows for entities with zero activity, inflating row counts with NULL or zero metrics.
LEFT JOIN the dimension table onto the fact aggregation for enrichment (names, addresses). The dimension does NOT control which entities appear - the fact table does. If a customer has no qualifying rows in the fact table after status filtering, that customer has no data to report and MUST NOT appear in the output.
Exception - calendar-spine models (daily/weekly/monthly reports): When a model CROSS JOINs a date spine with a shop/entity, the date spine drives the FROM clause - NOT the fact table. Days with zero activity MUST appear in the output with metric columns COALESCE'd to 0. This is the opposite of the fact-drives rule above. The calendar ensures every date appears regardless of whether transactions occurred. Identify calendar-spine models by: CROSS JOIN with a date/calendar table, or YML description mentioning "daily", "weekly", or "per day."
An order moves through stages. Not every row in a transaction table is a completed sale:
A fact table may contain rows from ALL of these stages. Only fulfilled/delivered rows count as revenue. Returns and refunds are separate metrics. Cancelled orders are neither.
A purchase or revenue total counts ONLY completed sales. Returned, refunded, and cancelled items are NOT revenue - they are reversals or abandonments. If a transaction table has a status/flag column, revenue metrics MUST exclude these negative event types.
BEFORE writing any SUM for a revenue metric, run SELECT DISTINCT <status_col> on the table in your FROM clause - not its raw source (intermediate models rename columns). Find which values represent returns, refunds, or cancellations from sibling models or existing WHERE clauses. Then exclude them with WHERE status_col NOT IN (...). Keep ALL other values - they are valid sales regardless of what their codes mean.
When categorizing entities into health tiers (green/yellow/orange/red, good/fair/poor, A/B/C/D), use equal-width percentage bands unless the YML description specifies different thresholds - guessing custom breakpoints from data distributions produces arbitrary boundaries that vary between runs. For a 0-100% range with 4 tiers: 0-25%, 25-50%, 50-75%, 75-100%.
If a computed metric exceeds 100% (e.g., returns exceed purchases), that entity is an anomaly. Set its category to NULL - it does not belong in any defined tier.
