Spark CLI

# Resolve pipeline item ID (if user gave name)
pipelineId=$(az rest --method get --resource "$FABRIC_RESOURCE_SCOPE" \
  --url "$FABRIC_API_URL/workspaces/$workspaceId/items?type=DataPipeline" \
  --query "value[?displayName=='<PipelineName>'].id" --output tsv)

# Get pipeline run status
az rest --method get --resource "$FABRIC_RESOURCE_SCOPE" \
  --url "$FABRIC_API_URL/workspaces/$workspaceId/items/$pipelineId/jobs/instances/$jobInstanceId" \
  --output json

POST $FABRIC_API_URL/workspaces/$workspaceId/datapipelines/pipelineruns/$jobInstanceId/queryactivityruns

# BROKEN: PowerShell treats & as call operator
az rest --url "https://...?type=driver&fileName=stderr"

# WORKAROUND: Use a JSON body file or single-param endpoints when possible
# The Spark Monitoring jobs, advice, stagesSummary, and resourceUsage endpoints
# require NO query params — prefer these over log download endpoints.

# Bash — use heredoc or file
cat > /tmp/query-body.json << 'EOF'
{
  "filters": [],
  "orderBy": [{"orderBy": "ActivityRunStart", "order": "DESC"}],
  "lastUpdatedAfter": "<pipeline startTimeUtc minus 1 hour>",
  "lastUpdatedBefore": "<pipeline endTimeUtc plus 1 hour, or now if InProgress>"
}
EOF
az rest --method post --resource "$FABRIC_RESOURCE_SCOPE" \
  --url "$FABRIC_API_URL/workspaces/$workspaceId/datapipelines/pipelineruns/$jobInstanceId/queryactivityruns" \
  --body @/tmp/query-body.json --output json

# PowerShell — escape inner double quotes with backslash
az rest --method post --resource $FABRIC_RESOURCE_SCOPE `
  --url "$FABRIC_API_URL/workspaces/$workspaceId/datapipelines/pipelineruns/$jobInstanceId/queryactivityruns" `
  --body '{\"filters\":[],\"orderBy\":[{\"orderBy\":\"ActivityRunStart\",\"order\":\"DESC\"}],\"lastUpdatedAfter\":\"<startTimeUtc minus 1h>\",\"lastUpdatedBefore\":\"<endTimeUtc plus 1h>\"}' `
  --output json

# Group activities by activityName
# For each group:
#   If all have same iterationHash → single run (no loop)
#   If different iterationHash values → ForEach loop
#     → Diagnose each iteration
#     → In report, show: "Activity: LoadRawData [iteration 1/5] (Failed)"

# Group activities by activityName + iterationHash
# For each group:
#   Keep only the run with max(retryAttempt)
#   Note: "Activity retried N times; analyzing final attempt"

# From the queryActivityRuns response, for each Notebook/SJD activity:
livyId="<activity.output.result.sessionId>"
notebookId="<activity.input.notebookId>"
workspaceId="<activity.input.workspaceId>"  # may differ from pipeline workspace

# Get the Spark application ID for this session
az rest --method get --resource "$FABRIC_RESOURCE_SCOPE" \
  --url "$FABRIC_API_URL/workspaces/$workspaceId/notebooks/$notebookId/livySessions/$livyId" \
  --query "sparkApplicationId" --output tsv

# When output.result.sessionId is null, Spark never launched.
# Check output.result.error for the Python traceback:
#   output.result.error.ename   — exception class name
#   output.result.error.evalue  — exception message
#   output.result.error.traceback — full stack trace lines
# Also check the pipeline-level error:
#   error.errorCode, error.message, error.failureType
# Common causes:
#   - Library/environment setup failure
#   - Invalid Spark pool configuration
#   - Capacity exhausted (could not allocate executors)
#   - Notebook syntax error caught before submission

Cell In[<cell_number>], line <line_number>

  File "Cell In[14], line 19"  
    raise Exception(f"Expected key '{key}' not found in URL: {url}")
Exception: Expected key 'cluster_id' not found in URL: https://...

https://app.powerbi.com/workloads/de-ds/sparkmonitor/{notebookId}/{livyId}?trident=1&experience=power-bi&ctid={tenantId}&tab=related

#### Activity: <activityName> [iteration <N>/<total>] — FAILED
- **Notebook**: <notebookId> (resolve display name via Items API)
- **Cell**: Cell In[14], line 19
- **Exception**: <ename>: <evalue>
- **Notebook Snapshot**: [Open in Fabric UI](<constructed snapshot URL>)
- **Parameters**: <key pipeline parameters for this iteration>
- **Traceback** (last 5 frames):
  <paste relevant traceback lines>

# Get all Spark jobs for the session
az rest --method get --resource "$FABRIC_RESOURCE_SCOPE" \
  --url "$FABRIC_API_URL/workspaces/$workspaceId/notebooks/$notebookId/livySessions/$livyId/applications/$sparkApplicationId/jobs" \
  --output json

# Extract child pipeline run info from activity output
childJobInstanceId="<from activity output>"

# Recurse: run the full Pipeline Run Diagnosis (Steps P1–P6) on the child pipeline
# Use the child pipeline's workspaceId and pipelineId from typeProperties

## Pipeline Diagnostic Summary

**Pipeline**: <pipeline name> | **Run ID**: <jobInstanceId> | **Status**: <status>
**Duration**: <startTimeUtc> → <endTimeUtc>

### Activity Overview

| # | Activity | Type | Status | Duration | Spark Issues | Notes |
|---|----------|------|--------|----------|--------------|-------|
| 1 | LoadRawData | TridentNotebook | Failed | 5m 23s | 1 HIGH, 1 MEDIUM | |
| 2 | LoadRawData [iter 2] | TridentNotebook | Succeeded | 4m 10s | 0 HIGH, 1 MEDIUM | ForEach iteration 2 |
| 3 | TransformSilver | TridentNotebook | Succeeded | 12m 45s | 0 HIGH, 2 MEDIUM | Retried 1× |
| 4 | PublishGold | SparkJob | Cancelled | — | — | Upstream failure |
| 5 | RunChildPipeline | ExecutePipeline | Failed | 8m 30s | See child report | Nested pipeline |
| 6 | SetupEnv | TridentNotebook | Failed | 0s | — | Spark never started (library error) |

### Detailed Findings

#### Activity: LoadRawData (Failed)
| # | Severity | Finding | Fix |
|---|----------|---------|-----|
| 1 | HIGH | Executor OOM in stage 4 (skewed partition) | Enable AQE skew join |
| 2 | MEDIUM | Disk spill 1.3 GB in stage 3 | Increase shuffle partitions to 400 |

#### Activity: TransformSilver (Succeeded — performance issues)
| # | Severity | Finding | Fix |
|---|----------|---------|-----|
| 1 | MEDIUM | Data skew ratio 4.1× in stage 7 | Salt the join key |
| 2 | MEDIUM | Core efficiency 28% | Reduce executor count |

#### Activity: SetupEnv (Failed — no Spark session)
**Error**: library packaging error — `pip install failed for package xyz==2.0`
**Fix**: Verify package version compatibility with Fabric runtime Python version.

#### Activity: ValidateData [31/36 iterations FAILED — user-code error]
**Notebook**: Check load_ virtualization (8e28e1fd-...)
**Cell**: Cell In[14], line 19
**Exception**: `Exception: Expected key 'cluster_id' not found in URL`
**Spark Jobs**: 13/13 SUCCEEDED — failure is in Python code, not Spark infrastructure
**Pattern**: 31 iterations fail at the same cell with the same exception class; 5 succeed
**Sample failing parameters**: `cluster_url=https://...`, `batch_key=...`
**Root Cause**: Notebook business logic expects a key in the URL that is missing for certain cluster URLs
**Fix**: Update notebook validation logic to handle URLs without the expected key, or fix upstream data

#### Child Pipeline: IngestPipeline (Failed)
[Recursive report from child pipeline diagnosis]