Data Freshness Investigation Pattern (Role-Based)

Pattern Type: Troubleshooting Workflow Confidence Level: 0.92 (based on successful concrete inspection investigation) Last Updated: 2025-10-03

When to Use This Pattern

Trigger Symptoms:

• •Dashboard shows blank or no data
• •Users report "missing recent data"
• •"No records for yesterday" complaints
• •Visualizations appear empty despite expecting data

Applicable Scenarios:

• •Any BI dashboard data availability issue
• •Data quality concerns (stale data)
• •Pipeline timing mismatches
• •Source-to-dashboard data flow problems

Quick Reference

Pattern: Trace Data Lineage Backwards

Core Principle

Start at the symptom (blank dashboard), trace backwards through the data flow until you find where fresh data stops.

Dashboard (symptom) → Report Table → Fact Table → Source Table → Source System
                        ↑              ↑            ↑              ↑
                     Check here    Check here   Check here   Check here

Key Insight

Don't assume the problem is where the symptom appears. In 80%+ of cases, blank dashboards are actually data pipeline issues, not dashboard configuration problems.

Optimal Workflow: Parallel Agent Launch

Fastest Approach (15-30 minutes)

Launch 3 agents simultaneously:

Task 1: analytics-engineer-role

Mission: Validate transformation pipeline health
Actions:
  1. Check if report table has recent data
  2. Verify dbt models ran successfully
  3. Check fact tables for data freshness
  4. Review transformation logic for date filters
Output: "Transformation healthy" or "Transformation stale at layer X"

Task 2: data-engineer-role

Mission: Validate source extraction pipeline
Actions:
  1. Check source table LOADEDON timestamp
  2. Verify business dates in source data
  3. Compare extraction time vs dbt job time
  4. Review Orchestra/Prefect workflow logs
Output: "Source fresh" or "Source timing issue detected"

Task 3: bi-developer-role

Mission: Pre-check data existence, then investigate dashboard
Actions:
  1. **FIRST**: Query underlying table for data
  2. **IF NO DATA**: STOP and wait for other agents
  3. **IF DATA EXISTS**: Investigate dashboard config
Output: "Data missing (not my issue)" or "Dashboard config problem found"

Result: All 3 analyses complete in ~10-15 minutes, root cause identified quickly

Sequential Investigation (If Preferred)

Phase 1: Data Validation (analytics-engineer-role - 5 min)

Goal: Determine if data exists at all

SELECT
  MAX(DATE(date_column)) as most_recent,
  COUNT(*) as total_records,
  COUNT(CASE WHEN DATE(date_column) = CURRENT_DATE - 1 THEN 1 END) as yesterday_count
FROM target_report_table;

Decision:

• •Data exists → Skip to Phase 3 (dashboard issue)
• •Data missing → Continue to Phase 2

Phase 2: Pipeline Trace (analytics-engineer-role → data-engineer-role - 15 min)

Goal: Find where in the pipeline data goes stale

Step 2A: Check Fact Tables

dbt show --inline "
SELECT
  MAX(DATE(date_col)) as most_recent,
  CURRENT_DATE - 1 as expected,
  DATEDIFF(day, MAX(DATE(date_col)), CURRENT_DATE - 1) as days_behind
FROM {{ ref('fact_table') }}
"

Decision:

• •Fact fresh → Transformation issue (rare, review report model logic)
• •Fact stale → Continue to Step 2B

Step 2B: Check Source Tables

dbt show --inline "
SELECT
  MAX(DATE(LOADEDON)) as last_extraction,
  MAX(DATE(business_date_column)) as latest_business_date,
  COUNT(CASE WHEN DATE(business_date_column) = CURRENT_DATE - 1 THEN 1 END) as yesterday_records
FROM {{ ref('source_table') }}
"

Decision Matrix:

Phase 3: Dashboard Investigation (bi-developer-role - 10 min)

ONLY if data exists in underlying table

1. •Connection type: Live vs Extract
2. •Filters: Date ranges, cascading filters
3. •Published data source: Stale metadata
4. •TWB file: Hardcoded dates in filters

Real Example: Concrete Pre/Post Trip Dashboard

Investigation Timeline

2025-10-03 Morning Investigation:

Key Insight: Source data arrived AFTER transformation ran. This is a timing/orchestration issue, not a data quality issue.

What We Learned

PATTERN markers for memory extraction:

PATTERN: Blank dashboard = trace data lineage backwards (report → fact → source)

SOLUTION: Check data EXISTS before investigating dashboard configuration

ERROR-FIX: Source timing issue (extraction after dbt) → Manual dbt trigger + adjust Orchestra dependencies

ARCHITECTURE: Role separation (analytics-engineer owns transforms, data-engineer owns ingestion, bi-developer owns dashboards)

INTEGRATION: dbt show for quick pipeline validation without full Snowflake access

Efficiency Gains

Manual Investigation vs Pattern

Time Saved: 60-90 minutes per investigation

Common Pitfalls to Avoid

❌ Don't Start with Dashboard

Common mistake: User reports blank dashboard, immediately investigate Tableau configuration

Why it fails: 80% of the time, issue is upstream (data pipeline), not dashboard

Better approach: Check if data exists FIRST, then investigate dashboard

❌ Don't Confuse Load Time vs Business Time

Common mistake: Seeing fresh LOADEDON and assuming data is current

Why it fails: LOADEDON = extraction timestamp, not business transaction date

Better approach: Always check BOTH timestamps:

• •LOADEDON: Did extraction run?
• •Business date columns: Does source have yesterday's transactions?

❌ Don't Skip Agent Coordination

Common mistake: One person manually checks everything sequentially

Why it fails: Wastes time, misses expertise, takes 2+ hours

Better approach: Launch parallel agents with domain expertise

Escalation Decision Tree

User Reports Blank Dashboard
    ↓
Check if data exists (SQL query - 30 sec)
    ↓
├─ Data Missing
│   ↓
│   Trace to fact table
│   ↓
│   ├─ Fact stale → Check source table
│   │   ↓
│   │   ├─ Source fresh → dbt job needs to run (analytics-engineer-role)
│   │   └─ Source stale → Extraction issue (data-engineer-role)
│   │
│   └─ Fact fresh → Transformation logic issue (analytics-engineer-role)
│
└─ Data Exists
    ↓
    Dashboard investigation (bi-developer-role)
    ↓
    ├─ Extract stale → Refresh extract
    ├─ Filter incorrect → Update filters
    └─ Data source stale → Refresh metadata

Success Criteria

Pattern Application is Successful When:

• •✅ Root cause identified within 30 minutes
• •✅ Correct agent assigned to resolution
• •✅ No time wasted on wrong troubleshooting path
• •✅ User communicated with accurate timeline
• •✅ Long-term fix identified (not just workaround)

Pattern Application Needs Improvement When:

• •❌ Takes >1 hour to identify root cause
• •❌ Wrong agent assigned (e.g., BI dev troubleshooting data pipeline)
• •❌ Multiple false starts or backtracking
• •❌ User given inaccurate information
• •❌ Issue recurs without prevention plan

Prevention Strategies

Proactive Monitoring

Implement data freshness checks:

-- Alert if data >24 hours stale
SELECT
  table_name,
  MAX(business_date) as most_recent,
  DATEDIFF(hour, MAX(business_date), CURRENT_TIMESTAMP) as hours_stale
FROM monitoring.data_freshness
WHERE hours_stale > 24;

Orchestra Dependency Management

Ensure proper job sequencing:

# Source extraction must complete before dbt
dbt_job:
  dependencies:
    - source_extraction.complete

User Communication

Set expectations:

"Data typically available by 8:00 AM. If viewing earlier, yesterday's data may not be processed yet."

Related Patterns

• •Pipeline Timing Pattern: Orchestration dependencies and job sequencing
• •Data Quality Pattern: Source validation and testing strategies
• •Escalation Pattern: Role-based responsibility and communication

Pattern Confidence: 0.92 Based On: Concrete Pre/Post Trip Dashboard investigation (2025-10-03) Success Rate: 100% in test case (correctly identified timing issue) Recommended Use: All blank dashboard / missing data investigations