build-data-spec

name	build-data-spec
description	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.
tags	["analytics","dbt","documentation","data-spec"]
metadata	{"author":"aheden","version":"1.0"}

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:

1. What to analyze -- the feature, domain, or event category (e.g., "failed generations", "brand kit usage", "export events")
2. Which product -- LTX Studio, LTX Model, API
3. 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:

1. TL;DR block -- model purpose and key features
2. Config block -- partition_by, cluster_by, schema, tags
3. Column definitions -- all SELECT columns with their derivation logic
4. Filter conditions -- WHERE clauses that scope the data
5. Join logic -- how tables connect (especially start/end event joins)
6. 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:

1. Title + metadata -- topic, last updated date
2. Overview -- what the spec covers, key definitions
3. Primary tables -- fully-qualified BigQuery table names, partition/cluster info
4. Key columns -- organized by category (error/result, context, timing, parameters, user)
5. Filtering patterns -- ready-to-use WHERE clauses for common scenarios
6. Sample analysis queries -- 4-6 BigQuery queries answering likely questions
7. Model lineage -- ASCII diagram showing source -> base -> intermediate -> mart flow
8. Key macros -- macros involved in column derivation
9. 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:

• Every column referenced in queries exists in the column tables
• Filtering patterns cover the main use cases the user described
• Sample queries are syntactically valid BigQuery SQL
• Lineage diagram traces from source through to mart
• Important notes capture non-obvious behavior (nullability, edge cases, timing windows)
• Table names use correct project/schema from model config blocks

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.

Dashboard Handoff

After the spec is complete, if the user also wants a dashboard built, do NOT start building charts directly. Route to the Dashboard Builder agent (agents/dashboard-builder/SKILL.md) starting at Phase 2 (Plan). The completed data spec satisfies Phase 1 (Discover). The dashboard-builder will present a chart plan for user approval before building in Hex.

Checklist

• Topic scoped and confirmed with user
• Codebase explored across all layers (mart -> intermediate -> base -> macro)
• Key model SQL files read (not just YMLs)
• Spec document written with all required sections
• Queries validated for correct table names and syntax
• File saved to ~/ltx-analytics-agents/docs/{feature}_spec.md
• If user wants a dashboard → handed off to dashboard-builder (Phase 2)