Skill: Jira Flow Extract

Capability Domain: Data Engineering
Technology Stack: Jira REST, JQL, Changelog

When to use

Scope Contract sources includes jira. Skip entirely when it does not. This skill is the extract contract for issue lead time and cycle time. Webhook-only or snapshot-only pulls are not enough.

What to persist (minimum)

1. Issue snapshot per key: issue type, current status, assignee, created, updated, and resolutiondate stored as closed_at. Unresolved issues have a null closed_at.
2. Status timeline: one row per contiguous visit to a status (status, entered_at, exited_at). The last segment stays open (exited_at null).
3. Watermark: last updatedDate (or equivalent cursor) so incremental pulls refresh both snapshot and timeline.

Optional fields (team, sprint, story points) may land in the same snapshot. They are not the clock for lead or cycle.

Suggested contract views when jira is scoped: jira_issues, jira_status_segments. Derive names as {source}_{entity} per the Scope Contract. Do not invent a third clock table.

Extraction recipe

1. Scope JQL to a parameterized project key (or keys listed in the contract). Never unbounded JQL — Cloud rejects it. When no watermark exists, default a bounded updatedDate window (for example 365 days), then page forward.
2. Incremental: project = {KEY} AND updatedDate >= '{watermark}' ORDER BY updated ASC.
3. Cloud: cursor pagination (enhanced_search_issues / nextPageToken). Server / Data Center: startAt offset. Page size about 100.
4. Request expand=changelog on the search, or fetch changelog per issue. Keep items where field == status. Sort histories by created.
5. Build segments:
   - Initial status = fromString of the first status change, else the current status name.
   - First segment entered_at = issue created.
   - Each status change closes the prior segment (exited_at = change time) and opens the next.
   - Final segment has exited_at null.
6. Normalize all timestamps to UTC and store them consistently (all naive UTC or all UTC-aware — pick one).
7. Write unmodified JSON to the raw prefix, then Parquet as the next BPE activity. Omit Hive partition keys (source, dt) from the Parquet body.

Lead time vs cycle time (compute from the extract)

Do this in SQL or the transform. Do not re-hit Jira at analysis time for the clock.

Metric	Start	End	Unit	Cohort
Lead time	issue created	resolutiondate (closed_at)	business days (Mon–Fri, fractional)	issues resolved in the window
Cycle time	earliest entered_at whose status is in the configured Started set	same closed_at	same business-day clock	same cohort, only if Started was entered

Business days: elapsed Mon–Fri only; include partial days by second overlap; return 0 when end ≤ start.

Started is a contract parameter, not a hardcoded display name. Record status names or IDs in the Scope Contract (example convention: case-insensitive In Progress). Teams that work in In Development or Active must map those names. A single hard-coded string will silently drop most of the cohort.

Rules

• Done for these clocks is resolutiondate, not “last transition to Done”. Those timestamps can diverge.
• Cycle start is the first Started entry, not the last stint, not the sum of active segments. Reopens do not reset the clock under this definition.
• Issues that never entered Started are excluded from cycle (not scored as zero).
• Time-in-status / journey charts may use calendar hours. Do not mix that unit with lead/cycle business days.
• Incremental sync only refreshes issues with updated >= watermark. Old tickets need a periodic bounded backfill.
• Sprint report / velocity snapshots (Agile board reports) are a parallel track. Never use them as the lead/cycle clock.

Pitfalls

• Search expand=changelog can truncate long histories. For accurate cycle time, paginate GET /issue/{key}/changelog on high-churn issues.
• Cloud search_issues with startAt is deprecated — use cursor search.
• Treating current status as the whole timeline (no changelog) makes cycle time a guess.
• Using calendar days for lead/cycle KPIs while journey uses hours, then comparing the numbers.
• Inferring “started” from the first non-backlog column without writing the Started set in the contract.