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/dbt_style_guide.md
View on GitHub# dbt Style Guide & Conventions
Consistent project structure and SQL style are critical for maintaining large-scale dbt architectures.
## 1. Project Layering
The dbt project must follow a strict three-layer architecture:
### Staging (`models/staging/`)
- Forms a strict 1:1 relationship with source tables.
- **Purpose**: Renaming columns, casting data types, standardizing booleans, and applying `trim/lower` logic.
- **Constraints**:
- Strictly NO JOINs or aggregations allowed!
- Must use the `source('schema', 'table')` macro.
- **Materialization**: `view`
- **Naming Prefix**: `stg_<source_name>__<table_name>.sql`
### Intermediate (`models/intermediate/`)
- Encapsulates complex transformational logic.
- **Purpose**: Consolidating multi-source data, joining lookup tables, and executing window functions.
- **Materialization**: `ephemeral` (compiled inside downstream CTEs) or `view` if referenced multiple times.
- **Naming Prefix**: `int_<entity_name>_<verb>.sql` (e.g., `int_users_enriched.sql`)
### Marts (`models/marts/`)
- The business-facing dimensional and fact models.
- **Purpose**: Serving clean, optimized data to BI tools.
- **Materialization**: `table` (or `incremental` for massive fact tables).
- **Naming Prefix**: `dim_<entity>.sql` or `fct_<event>.sql`
---
## 2. SQL Formatting and Syntax
### No Implicit Columns
- NEVER use `SELECT *` against source or ref tables in Production models (outside of simple staging passthroughs).
- The terminal `SELECT * FROM final` CTE pattern is acceptable as it references an explicitly projected CTE.
- **Why**: Implicit lists break dbt data contracts during schema drift and inflate network/memory payloads. **Always project columns explicitly in CTEs.**
### CTE Structure
- All models must use Common Table Expressions (CTEs), ending with a final `SELECT * FROM final` block.
- **Why**: Separating logic into modular CTE blocks allows for isolation and step-by-step debugging.
**Example Standard Model:**
```sql
WITH customers AS (
SELECT * FROM {{ ref('stg_salesforce__customers') }}
),
orders AS (
SELECT * FROM {{ ref('stg_stripe__orders') }}
),
customer_orders AS (
SELECT
customer_id,
MIN(order_date) AS first_order_date,
SUM(amount) AS lifetime_value
FROM orders
GROUP BY customer_id
),
final AS (
SELECT
c.customer_id,
c.customer_name,
co.first_order_date,
co.lifetime_value
FROM customers AS c
LEFT JOIN customer_orders AS co
ON c.customer_id = co.customer_id
)
SELECT * FROM final;
```
### Jinja and Macros
- Store reusable logic (e.g., converting cents to dollars, generating schema names) inside the `macros/` folder.
- Use `target.name` conditionals to limit data queried in `dev` environments to save operational costs.
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: 198k | Updated | Claude CodeCodex | Verified | Install |