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/optimization.md
View on GitHub# SQL Query Optimization Patterns
This guide outlines antipatterns that cripple data warehouse performance and details the strategies required to remediate them.
## 1. Eliminate Spaghetti Queries
- **The Antipattern**: Writing a monolithic, multi-thousand-line `SELECT` statement attempting to aggregate, filter, and join a dozen tables at once to fulfill an entire reporting requirement.
- **The Issue**: Massive queries confuse the warehouse optimizer. The engine often allocates incorrect memory logic, resulting in unintentional Cartesian products (cross-joins) that spin compute resources into infinity.
- **The Fix**: Adopt a **Divide and Conquer** methodology. Break complex operations down into distinct sequential dbt Intermediate models (`int_` layer) or distinct, linearly progressive Common Table Expressions (CTEs) representing granular steps.
## 2. Eliminate EAV and Jaywalking Joins
- **Jaywalking**: Storing comma-separated delimited IDs inside a single column and using `JOIN ON a LIKE '%' || b || '%'` to connect them.
- **EAV (Entity-Attribute-Value)**: Storing schema-less data in three vertical columns (`entity_id`, `attribute_name`, `value`).
- **The Issue**: Both patterns completely bypass the relational database indexing structures, forcing horizontal table scans that are mathematically exponential.
- **The Fix**: Abstract many-to-many elements into an explicit Intersection Table natively supporting primary-to-foreign key bridging.
## 3. Ambiguous Aggregations
- **The Antipattern**: Selecting raw columns alongside aggregate measures without explicitly grouping them, or adding extraneous descriptive fields to a `GROUP BY` clause just to pass syntax.
- **The Issue**: It results in pseudo-random value selection for the non-grouped rows or inadvertently alters the grain of the calculation, resulting in duplicated or dropped financial measures.
- **The Fix**: Follow the single-value rule. Only group on the absolute core dimension keys. For descriptive descriptors, wrap them in deterministic functions like `MAX()`, or calculate the core aggregates in a nested CTE and `JOIN` the descriptors afterward.
## 4. Unoptimized Randomization
- **The Antipattern**: `SELECT * FROM massive_table ORDER BY RAND() LIMIT 10`
- **The Issue**: The database must assign a random floating-point integer to *every single row in the millions-row facts table*, execute a global sort on the entire table in memory, select the first 10, and discard the rest.
- **The Fix**: Avoid `RAND()`. Instead use windowing sequencing operations:
```sql
-- More optimal approach for sampling:
WITH CTE AS (
SELECT *, ROW_NUMBER() OVER(ORDER BY event_timestamp) AS rn
FROM massive_table
)
SELECT * FROM CTE WHERE rn % 100 = 0; -- takes 1% roughly
```
## 5. Incremental Processing
For facts exceeding ten million records, daily rebuilds consume massive resources.
Configure fact models as `materialized='incremental'` inside the configuration block.
- Read only the last 1-3 days of data utilizing the `{% if is_incremental() %}` Jinja macro condition.
- Update matching records utilizing `unique_key`.
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 |