cross-system-analysis-patterns - SKILL.md Agent Skill

name: cross-system-analysis-patterns description: Reference for analyzing issues spanning multiple systems (dbt, Snowflake, Orchestra, Tableau) with agent coordination strategies user-invocable: false

Cross-System Issue Analysis & Coordination Patterns

Common Issue Categories (Multi-Tool)

1. Schema/Column Reference Errors

Symptom: Tests referencing incorrect column names vs actual model schemas

Analysis Pattern:

Check dbt model schemas against test definitions
Verify column name case sensitivity
Look for renamed columns in recent changes
Cross-reference with source system schemas

Priority: CRITICAL if blocking compilation

2. Data Quality Issues

Symptom: Uniqueness constraint violations, null constraint failures, massive duplications

Analysis Pattern:

Check upstream data sources for quality
Review recent ETL/ELT pipeline changes
Validate data freshness and completeness
Look for source system changes

Priority: HIGH if indicating upstream pipeline problems

3. Cross-System Validation Failures

Symptom: Mismatches between source systems and dbt model expectations

Analysis Pattern:

Compare source schema with dbt expectations
Check for API/integration changes
Validate data type mismatches
Review ingestion pipeline logs

Priority: MEDIUM to HIGH depending on impact

4. Business Logic Validation

Symptom: Failed reconciliation tests, metric validation errors

Analysis Pattern:

Review business rules implementation
Validate calculation logic
Check for edge cases in data
Consult stakeholders on expectations

Priority: MEDIUM unless affecting critical reports

Architecture-Aware Analysis Approach

Data Flow Context

Issues often span multiple layers in the data stack:

Orchestra (Orchestrator)
    ↓
[Prefect, dbt, Airbyte, Snowflake] (Execution Layer)
    ↓
Snowflake (Data Warehouse)
    ↓
Semantic Layer (Business Logic)
    ↓
Tableau (Visualization)

Orchestra-Centric Thinking

PATTERN: Orchestra kicks off everything

Prefect flows
dbt jobs
Airbyte syncs
Direct Snowflake operations

Analysis Strategy:

Start with Orchestra logs to understand what triggered
Trace execution through triggered systems
Identify failure point in the chain
Work backwards to root cause

Model Layer Impact

PATTERN: Problems cascade through layers

Source System Issue
    ↓
stg_* (Staging Models) - First failure point
    ↓
dm_* (Data Marts) - Downstream failures
    ↓
rpt_* (Reports) - User-facing impact

Analysis Strategy:

Start at earliest failure point (usually staging)
Understand cascade effects downstream
Fix root cause, not symptoms
Validate entire chain after fix

Source System Dependencies

PATTERN: Different source systems create different data patterns

ERP Systems:

Structured, transactional data
Strong referential integrity expected
Frequent schema changes with updates

Customer Systems:

Variable data quality
Missing/inconsistent data common
Requires robust null handling

Operations Systems:

Real-time data with lag considerations
High volume, time-series patterns
Deduplication often needed

Safety Systems:

Regulatory compliance requirements
Strict data retention rules
Audit trail critical

Tableau Data Pipeline:

Parse TFL flows for published extracts
Parse TWB workbooks to validate connections
Trace data flow issues through XML/JSON analysis
Validate extract refresh schedules

Cross-Tool Prioritization Framework

CRITICAL Priority

Schema compilation errors that block other work

Lead Agent: dbt-expert

Response Pattern:

Drop everything and address immediately
Identify blocking compilation issues
Fix schema problems first
Validate compilation succeeds
Then move to data quality

HIGH Priority

Large-scale data quality issues indicating upstream pipeline problems

Lead Agents: orchestra-expert + dlthub-expert

Response Pattern:

Check Orchestra logs for pipeline status
Validate source data quality with dlthub
Identify if pipeline or source issue
Coordinate fix across systems
Re-run pipeline to validate

MEDIUM Priority

Business logic and validation failures

Lead Agents: dbt-expert + business-context

Response Pattern:

Review business requirements
Validate logic implementation
Check for edge cases
Test with sample data
Update tests if requirements changed

LOW Priority

Warning-level issues that don't break functionality

Response Pattern:

Document for future sprint
Create backlog item
Monitor for pattern escalation
Address during refactoring

Agent Coordination Strategy

Role-Based Primary Agents

data-engineer-role: Pipeline & Orchestration Lead

Role: LEADS all workflow and pipeline analysis Scope: Orchestra, Prefect, dbt pipelines, Airbyte, source integrations Consolidates: orchestra-expert + dlthub-expert + prefect-expert

When to Invoke:

Pipeline failures or timing issues
Multi-system coordination problems
Workflow dependency analysis
Scheduling and orchestration questions
Source system integration issues

analytics-engineer-role: Transformation Layer Owner

Role: Owns all data modeling and transformation work Scope: dbt models, SQL optimization, business logic, semantic layer Consolidates: dbt-expert + snowflake-expert (SQL) + tableau-expert (data layer)

When to Invoke:

Model compilation errors
Performance optimization
Business logic implementation
Data quality testing
Metric definitions

bi-developer-role: Consumption Layer & Documentation

Role: Dashboard development and end-user documentation Scope: Tableau visualizations, UX design, user guides Consolidates: tableau-expert (viz) + ui-ux-expert + documentation-expert (end-user)

When to Invoke:

Dashboard development
Performance optimization for BI
User training materials
Visualization best practices

qa-engineer-role: Comprehensive Testing

Role: Enterprise-grade testing and validation Scope: All user-facing changes, data quality validation

When to Invoke:

After UI/UX changes
Before marking work complete
API/backend changes
Data quality validation

Tool Specialists (Consultation Layer - 20% of cases)

Available for complex edge cases requiring deep tool expertise:

dbt-expert, snowflake-expert, tableau-expert
dlthub-expert, orchestra-expert, prefect-expert
documentation-expert (platform-wide standards)

When to consult: Role agents automatically invoke for complex scenarios

Specialist Consultation Examples

Example 1: Complex dbt Macro Development

analytics-engineer-role handles most transformations
→ Consults dbt-expert for advanced macro patterns
→ Implements solution with expert guidance

Example 2: Advanced Prefect Flow Patterns

data-engineer-role sets up most pipelines
→ Consults prefect-expert for complex flow patterns
→ Implements with specialist input

Example 3: Deep Snowflake Cost Optimization

analytics-engineer-role handles query optimization
→ Consults snowflake-expert for warehouse-level cost analysis
→ Applies recommendations

business-analyst-role: Requirements & Stakeholder Alignment

Role: Business logic validation, stakeholder communication Scope: Requirements gathering, metric definitions, business rules

When to Invoke:

Unclear business requirements
Metric definition questions
Stakeholder alignment needed
Business rule validation

data-architect-role: System Design & Strategy

Role: System design, data flow analysis, strategic platform decisions Scope: Entire data stack architecture

When to Invoke:

Cross-system design decisions
Architecture pattern questions
Strategic platform choices
Complex integration design

Multi-Agent Coordination Patterns

Sequential Coordination

Pattern: One agent's output feeds next agent's analysis

orchestra-expert (identify workflow issue)
    ↓
dlthub-expert (check source data quality)
    ↓
dbt-expert (fix model logic)
    ↓
qa-coordinator (validate fix)

Parallel Investigation

Pattern: Multiple agents investigate different aspects simultaneously

                    Issue Detected
                          |
        +-----------------+-----------------+
        |                 |                 |
    dbt-expert      snowflake-expert   tableau-expert
    (check models)  (check queries)    (check dashboards)
        |                 |                 |
        +-----------------+-----------------+
                          |
                   Synthesize Findings

Iterative Refinement

Pattern: Agent collaboration with feedback loops

business-context (gather requirements)
    ↓
dbt-expert (implement logic)
    ↓
qa-coordinator (test functionality)
    ↓
business-context (validate with stakeholders)
    ↓ (if changes needed)
dbt-expert (refine implementation)

Pattern Markers for Memory Extraction

When documenting cross-system discoveries:

PATTERN: Reusable analysis approaches
SOLUTION: Specific multi-system fixes
ERROR-FIX: Cross-system errors and resolutions
ARCHITECTURE: System integration patterns
INTEGRATION: Cross-system coordination approaches