Build Data Spec
Target Repository
~/dwh-data-model-transforms
Remote: origin -> github.com/Lightricks/dwh-data-model-transforms.git
Default branch: develop
All file reads, searches, and explorations must target this directory, regardless of which repo this skill is invoked from. Use absolute paths (e.g., ~/dwh-data-model-transforms/models/) or set working directory before running commands.
When to Use
- User wants to analyze a feature, domain, or event category
- User needs a reference document for an agent or analyst
- User says "pull all events for X", "create a data spec", "document events for Y"
- Starting an analysis that requires understanding which tables, columns, and filters to use
Output
A markdown file saved to ~/ltx-analytics-agents/docs/{feature}_spec.md.
Naming convention: Use the feature name in snake_case (e.g., brand_kits_spec.md, gen_space_spec.md, failed_generations_spec.md). This filename must match what other agents (e.g., dashboard-builder) use to look up the spec.
Workflow
Phase 1: Scope the Topic
Clarify with the user:
- What to analyze -- the feature, domain, or event category (e.g., "failed generations", "brand kit usage", "export events")
- Which product -- LTX Studio, LTX Model, API
- Breadth -- single feature deep-dive or cross-feature overview
If the user's request is broad, propose a focused scope before proceeding.
Phase 2: Explore the Codebase
Search systematically across all layers in ~/dwh-data-model-transforms. Use parallel exploration agents for speed.
Search targets (in priority order):
All paths below are relative to ~/dwh-data-model-transforms/.
| Layer |
Where to Look |
What to Extract |
| Event registry |
docs/event-registry.yaml |
Canonical event names, key properties, status |
| Mart models |
models/**/marts/ |
Final columns, filters, action_name/action_category mapping |
| Intermediate models |
models/**/intermediate/ |
Business logic, joins, derived columns |
| Base models |
models/base/ |
Raw source columns, process_started/ended pairs |
| Macros |
macros/ |
Extraction logic, parsing, field derivation |
| Source definitions |
models/sources.yml |
Raw event table names |
| Existing specs |
docs/*_spec.md |
Related specs to cross-reference |
Search strategies:
- Filename search:
Glob for model names containing the topic keyword
- Content search:
Grep for column names, event names, action categories
- Semantic search: "How does X work?" scoped to relevant directories
- YAML search: Look at
.yml files alongside .sql for column descriptions and tests
Read priority: Always read the SQL model files, not just YMLs. The SQL reveals:
- Actual column derivation logic (CASE statements, COALESCEs, joins)
- Filter conditions that define the event scope
- Macro calls that generate columns
- Incremental predicates and partition fields
Phase 3: Read Key Models
For each relevant model found in Phase 2, read the full .sql file to extract:
- TL;DR block -- model purpose and key features
- Config block -- partition_by, cluster_by, schema, tags
- Column definitions -- all SELECT columns with their derivation logic
- Filter conditions -- WHERE clauses that scope the data
- Join logic -- how tables connect (especially start/end event joins)
- Macro calls -- which macros generate columns (read the macro too)
Also read the .yml file for:
- Column descriptions (especially "In this table:" context)
- Accepted values tests (reveal valid column values)
- Data quality tests (reveal important constraints)
Phase 4: Compile the Spec Document
Write ~/ltx-analytics-agents/docs/{feature}_spec.md following the structure in references/spec-template.md.
Required sections:
- Title + metadata -- topic, last updated date
- Overview -- what the spec covers, key definitions
- Primary tables -- fully-qualified BigQuery table names, partition/cluster info
- Key columns -- organized by category (error/result, context, timing, parameters, user)
- Filtering patterns -- ready-to-use WHERE clauses for common scenarios
- Sample analysis queries -- 4-6 BigQuery queries answering likely questions
- Model lineage -- ASCII diagram showing source -> base -> intermediate -> mart flow
- Key macros -- macros involved in column derivation
- Important notes -- gotchas, caveats, edge cases
Writing guidelines:
- Use fully-qualified BigQuery table names (
`project.schema.table`)
- Include column types (STRING, BOOLEAN, INT64, TIMESTAMP, FLOAT64)
- Show accepted values inline when known from tests
- Always include
NOT is_lt_team in example queries
- Use partition column in WHERE for cost efficiency
- Provide both simple filters and full analysis queries
Phase 5: Validate Completeness
Before finalizing, check:
Existing Specs as Reference
Current data spec documents in the project:
| File |
Topic |
Good Example Of |
docs/gen_space_events_spec.md |
Gen Space activity |
Filtering patterns, page_workspace breakdown |
docs/brand_kits_events_spec.md |
Brand Kit events |
Event-to-column mapping, action_category usage |
docs/gen_space_lightbox_actions_spec.md |
Lightbox/asset actions |
UI-to-event mapping, cross-feature coverage |
docs/ltxstudio_failed_generations_spec.md |
Failed generations |
Error analysis, multi-layer column tracking |
Read these for style and depth calibration when creating a new spec.
Checklist
1---2name: build-data-spec3description: Build a structured data spec document for analytics topics by exploring the dbt codebase, identifying relevant events/models/columns, and producing a ready-to-use markdown reference. Use when: (1) starting a new data analysis, (2) documenting events for a feature or domain, (3) creating a reference for an agent or analyst.4---5
6# Build Data Spec
7
8## Target Repository
9
10```
11~/dwh-data-model-transforms
12```
13
14Remote: `origin` -> `github.com/Lightricks/dwh-data-model-transforms.git`
15Default branch: `develop`
16
17**All file reads, searches, and explorations must target this directory**, regardless of which repo this skill is invoked from. Use absolute paths (e.g., `~/dwh-data-model-transforms/models/`) or set working directory before running commands.
18
19## When to Use
20
21- User wants to analyze a feature, domain, or event category
22- User needs a reference document for an agent or analyst
23- User says "pull all events for X", "create a data spec", "document events for Y"
24- Starting an analysis that requires understanding which tables, columns, and filters to use
25
26
27## Output
28
29A markdown file saved to `~/ltx-analytics-agents/docs/{feature}_spec.md`.
30
31**Naming convention:** Use the feature name in snake_case (e.g., `brand_kits_spec.md`, `gen_space_spec.md`, `failed_generations_spec.md`). This filename must match what other agents (e.g., dashboard-builder) use to look up the spec.
32
33## Workflow
34
35### Phase 1: Scope the Topic
36
37Clarify with the user:
38
391. **What to analyze** -- the feature, domain, or event category (e.g., "failed generations", "brand kit usage", "export events")
402. **Which product** -- LTX Studio, LTX Model, API
413. **Breadth** -- single feature deep-dive or cross-feature overview
42
43If the user's request is broad, propose a focused scope before proceeding.
44
45### Phase 2: Explore the Codebase
46
47Search systematically across all layers in `~/dwh-data-model-transforms`. Use parallel exploration agents for speed.
48
49**Search targets (in priority order):**
50
51All paths below are relative to `~/dwh-data-model-transforms/`.
52
53| Layer | Where to Look | What to Extract |
54|-------|--------------|-----------------|
55| **Event registry** | `docs/event-registry.yaml` | Canonical event names, key properties, status |
56| **Mart models** | `models/**/marts/` | Final columns, filters, action_name/action_category mapping |
57| **Intermediate models** | `models/**/intermediate/` | Business logic, joins, derived columns |
58| **Base models** | `models/base/` | Raw source columns, process_started/ended pairs |
59| **Macros** | `macros/` | Extraction logic, parsing, field derivation |
60| **Source definitions** | `models/sources.yml` | Raw event table names |
61| **Existing specs** | `docs/*_spec.md` | Related specs to cross-reference |
62
63**Search strategies:**
64
65- Filename search: `Glob` for model names containing the topic keyword
66- Content search: `Grep` for column names, event names, action categories
67- Semantic search: "How does X work?" scoped to relevant directories
68- YAML search: Look at `.yml` files alongside `.sql` for column descriptions and tests
69
70**Read priority:** Always read the SQL model files, not just YMLs. The SQL reveals:
71- Actual column derivation logic (CASE statements, COALESCEs, joins)
72- Filter conditions that define the event scope
73- Macro calls that generate columns
74- Incremental predicates and partition fields
75
76### Phase 3: Read Key Models
77
78For each relevant model found in Phase 2, read the full `.sql` file to extract:
79
801. **TL;DR block** -- model purpose and key features
812. **Config block** -- partition_by, cluster_by, schema, tags
823. **Column definitions** -- all SELECT columns with their derivation logic
834. **Filter conditions** -- WHERE clauses that scope the data
845. **Join logic** -- how tables connect (especially start/end event joins)
856. **Macro calls** -- which macros generate columns (read the macro too)
86
87Also read the `.yml` file for:
88- Column descriptions (especially "In this table:" context)
89- Accepted values tests (reveal valid column values)
90- Data quality tests (reveal important constraints)
91
92### Phase 4: Compile the Spec Document
93
94Write `~/ltx-analytics-agents/docs/{feature}_spec.md` following the structure in [references/spec-template.md](references/spec-template.md).
95
96**Required sections:**
97
981. **Title + metadata** -- topic, last updated date
992. **Overview** -- what the spec covers, key definitions
1003. **Primary tables** -- fully-qualified BigQuery table names, partition/cluster info
1014. **Key columns** -- organized by category (error/result, context, timing, parameters, user)
1025. **Filtering patterns** -- ready-to-use WHERE clauses for common scenarios
1036. **Sample analysis queries** -- 4-6 BigQuery queries answering likely questions
1047. **Model lineage** -- ASCII diagram showing source -> base -> intermediate -> mart flow
1058. **Key macros** -- macros involved in column derivation
1069. **Important notes** -- gotchas, caveats, edge cases
107
108**Writing guidelines:**
109
110- Use fully-qualified BigQuery table names (`` `project.schema.table` ``)
111- Include column types (STRING, BOOLEAN, INT64, TIMESTAMP, FLOAT64)
112- Show accepted values inline when known from tests
113- Always include `NOT is_lt_team` in example queries
114- Use partition column in WHERE for cost efficiency
115- Provide both simple filters and full analysis queries
116
117### Phase 5: Validate Completeness
118
119Before finalizing, check:
120
121- [ ] Every column referenced in queries exists in the column tables
122- [ ] Filtering patterns cover the main use cases the user described
123- [ ] Sample queries are syntactically valid BigQuery SQL
124- [ ] Lineage diagram traces from source through to mart
125- [ ] Important notes capture non-obvious behavior (nullability, edge cases, timing windows)
126- [ ] Table names use correct project/schema from model config blocks
127
128## Existing Specs as Reference
129
130Current data spec documents in the project:
131
132| File | Topic | Good Example Of |
133|------|-------|-----------------|
134| `docs/gen_space_events_spec.md` | Gen Space activity | Filtering patterns, page_workspace breakdown |
135| `docs/brand_kits_events_spec.md` | Brand Kit events | Event-to-column mapping, action_category usage |
136| `docs/gen_space_lightbox_actions_spec.md` | Lightbox/asset actions | UI-to-event mapping, cross-feature coverage |
137| `docs/ltxstudio_failed_generations_spec.md` | Failed generations | Error analysis, multi-layer column tracking |
138
139Read these for style and depth calibration when creating a new spec.
140
141## Checklist
142
143- [ ] Topic scoped and confirmed with user
144- [ ] Codebase explored across all layers (mart -> intermediate -> base -> macro)
145- [ ] Key model SQL files read (not just YMLs)
146- [ ] Spec document written with all required sections
147- [ ] Queries validated for correct table names and syntax
148- [ ] File saved to `~/ltx-analytics-agents/docs/{feature}_spec.md`
majiayu000