# Cross System Analysis Patterns > Reference for analyzing issues spanning multiple systems (dbt, Snowflake, Orchestra, Tableau) with agent coordination strategies Use when this capability is needed. - Skill: `tomevault-io/cross-system-analysis-patterns` (Agent Skill, multi-file: 2 files) - Install (CLI): `npx skillmds@latest add tomevault-io/cross-system-analysis-patterns` - Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/cross-system-analysis-patterns/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - Author: tomevault-io (https://skillmd.com/u/tomevault-io) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/tomevault-io/cross-system-analysis-patterns --- # 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**: 1. Start with Orchestra logs to understand what triggered 2. Trace execution through triggered systems 3. Identify failure point in the chain 4. 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**: 1. Drop everything and address immediately 2. Identify blocking compilation issues 3. Fix schema problems first 4. Validate compilation succeeds 5. Then move to data quality ### HIGH Priority **Large-scale data quality issues indicating upstream pipeline problems** **Lead Agents**: orchestra-expert + dlthub-expert **Response Pattern**: 1. Check Orchestra logs for pipeline status 2. Validate source data quality with dlthub 3. Identify if pipeline or source issue 4. Coordinate fix across systems 5. Re-run pipeline to validate ### MEDIUM Priority **Business logic and validation failures** **Lead Agents**: dbt-expert + business-context **Response Pattern**: 1. Review business requirements 2. Validate logic implementation 3. Check for edge cases 4. Test with sample data 5. Update tests if requirements changed ### LOW Priority **Warning-level issues that don't break functionality** **Response Pattern**: 1. Document for future sprint 2. Create backlog item 3. Monitor for pattern escalation 4. 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 --- > Converted and distributed by [TomeVault](https://tomevault.io/claim/dylpickledev) — claim your Tome and manage your conversions.