borghei/Claude-Skills / data-analytics/analytics-engineer
analytics-engineer
VerifiedAnalytics engineering across data modeling, dbt, transformation, and semantic layers. Use when building dbt models, designing star schemas, writing staging or mart SQL, configuring data tests, or optimizing warehouse queries.
| File | Kind | Size |
|---|---|---|
| REFERENCE.md | markdown | 4.2 KB |
| SKILL.md | skill md | 13.4 KB |
| assets/dbt_project.yml | config | 1.1 KB |
| assets/macros.sql | other | 1.5 KB |
| assets/schema.yml | config | 1.4 KB |
| assets/source.yml | config | 1.0 KB |
| references/dbt_style_guide.md | markdown | 2.7 KB |
| references/modeling_patterns.md | markdown | 3.2 KB |
| references/optimization.md | markdown | 3.0 KB |
| references/testing_guide.md | markdown | 2.4 KB |
| scripts/doc_generator.pyexecutable | script | 3.2 KB |
| scripts/impact_analyzer.pyexecutable | script | 3.5 KB |
| scripts/quality_scorer.pyexecutable | script | 3.9 KB |
| scripts/schema_diff.pyexecutable | script | 4.0 KB |
references/modeling_patterns.md
View on GitHub# Data Modeling Patterns
This guide formalizes the dimensional data modeling conventions expected within the analytics engineering workflow.
## 1. Dimensional Architectures
### The Star Schema
The **Star Schema** is the universally preferred architecture for analytics. It relies on a single **Fact Table** surrounded by highly denormalized **Dimension Tables**.
- **Performance**: Minimizes complex multi-table joins.
- **Understandability**: Business users can natively grok the "nouns" (dimensions) and "verbs" (facts) of the business.
- **Rule**: Never use a Snowflake schema unless explicitly required by a BI tool limitation. A dimension table should contain all attributes required to slice the fact table, regardless of normalization redundancy.
### One Big Table (OBT)
- **Warning**: Do not use OBT approaches. While columnar databases handle them well, they force disparate business processes into monolithic structures, resulting in extreme matrix sparsity and complex workarounds.
---
## 2. Dimension Tables
Dimensions (the "who, what, where, when") provide the text attributes used to filter and group fact data.
### Surrogate Keys
- **Rule**: Every dimension table MUST have a meaningless, auto-incrementing integer (or hashed integer) as its primary key.
- **Why**: Protects the warehouse from upstream source system changes (e.g., if a natural key is recycled) and enables Slowly Changing Dimension (SCD) capabilities.
- **Implementation**: Use `dbt_utils.generate_surrogate_key(['natural_key'])`.
### Hierarchies
- Do not normalize hierarchies. The `Dim_Product` table should contain `Category`, `Sub_Category`, `Brand`, and `SKU` directly on the single row.
### Slowly Changing Dimensions (SCDs)
- **SCD Type 1 (Overwrite)**: Use when history is irrelevant. Edits overwrite the old data.
- **SCD Type 2 (Versioning)**: Required for compliance and historical accuracy. Create a new row when an attribute changes. Requires `is_active` boolean, `effective_date`, and `expiration_date` columns.
- *For implementing SCD Type 2 in dbt, utilize the `snapshots` feature rather than manual logic.*
---
## 3. Fact Tables
Fact tables capture the measurable, quantitative metrics of a business event.
### Target Atomic Grain
- Define the lowest possible granularity. The fact table should represent the most atomic level of an event (e.g., `one row per order line item`, not `one row per order`).
### Additive Facts Only
- **Rule**: Store fully additive measures (e.g., `dollars_sold`, `quantity_ordered`).
- **Rule**: Non-additive measures (e.g., `gross_margin_percentage`, `conversion_rate`) must NOT be stored in the database. Instead, store the additive components (`revenue`, `cost`) and perform the ratio division dynamically in the BI tool.
### Degenerate Dimensions
- **Rule**: For extremely high-cardinality values with no descriptive attributes (e.g., `transaction_id`, `invoice_number`), store them directly on the fact table. Do not create a dimension table just to hold an ID.
### No NULL Foreign Keys
- **Rule**: A fact table must *never* contain a `NULL` foreign key. Inner joins drop records with `NULL` keys, silently obliterating revenue metrics.
- **Fix**: Direct `NULL` foreign keys to an established placeholder dimension row (e.g., ID `-1` mapped to "Unknown" or "N/A").
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| finishing-a-development-branchobra/superpowersUse when implementation is complete, all tests pass, and you need to decide how to integrate the work | Stars: 322k | Updated | Claude CodeCodex | Verified | Install |
| systematic-debuggingobra/superpowersUse when encountering any bug, test failure, or unexpected behavior, before proposing fixes | Stars: 322k | Updated | Claude CodeCodex | Verified | Install |
| test-driven-developmentobra/superpowersUse when implementing any feature or bugfix, before writing implementation code | Stars: 322k | Updated | Claude CodeCodex | Verified | Install |
| skill-creatoranthropics/skillsCreate new skills, modify and improve existing skills, and measure skill performance. Use when users want to create a skill from scratch, edit, or optimize an existing skill, run evals to test a skill, benchmark skill performance with variance analysis, or optimize a skill's description for better triggering accuracy. | Stars: 197k | Updated | Claude CodeCodex | Verified | Install |