Activities - Build the Operational Data Lake Pipeline

#	Name	Abbrev.	Guidance	Actions
Datalake Formation 6
1	Stand Up Serverless Ingestion from Scoped Sources	BPE-1	Purpose Land raw JSON events from scoped sources into the raw bucket. Implement only Lambdas …	Read the 'BPE-1 Stand Up Serverless Ingestion from Scoped Sources' activity from playbook 'Delivery Constraint & Opportunity Analysis' (workflow 'Build the Operational Data Lake Pipeline'). Follow its guidance when assisting me. ## Purpose Land raw JSON events from scoped sources into the raw bucket. Implement only Lambdas the Scope Contract lists. Org webhook registration is a human GitHub (or GitLab) Settings step after the first CI deploy — deploy does not by itself land events. ## Inputs - Lake Scope Contract (`sources`, `views`, bucket names, `secrets.github_webhook`). - CDK Application (IngestionStack already declares the functions). Gate is the artifact — predecessors cannot cross workflows. - Stack output `GithubWebhookUrl` (job summary after `deploy.yml`, also `docs/deploy-manifest.yaml`). - Skill Serverless Data Ingestion → Recipe. ## Steps 1. Open skill Serverless Data Ingestion. Handlers already live under `infra/lambdas/` in the starter (`github_webhook.py`, and `jira_flow.py` only after you copy `infra/lambdas/jira_flow.py.example`). Edit signatures / secret names from the contract; do not scaffold empty. 2. Confirm Secrets Manager `ops-lake/github-webhook-secret` exists (CDK creates it; populate-lake may sync). Value must match the GitHub webhook secret you will paste in org Settings. 3. Register the org webhook (human): GitHub org → Settings → Webhooks → Add webhook. Payload URL = `GithubWebhookUrl`. Content type `application/json`. Secret = the Secrets Manager value. SSL verify. Events: Issues, Pull requests, Pushes, Workflow runs. Save. Send a test ping. 4. GitHub handler: `GithubWebhookFn` behind `/webhooks/github`, verify `X-Hub-Signature-256`. Return 200; enqueue SQS for slow work. Prove: CloudWatch `/aws/lambda/GithubWebhookFn`; raw object at `s3://{raw}/github/{yyyy}/{mm}/{dd}/`. 5. GitLab: implement only if `gitlab` is in the contract. Secret `ops-lake/gitlab-webhook-secret`; header `X-Gitlab-Token` (skill 63). 6. Jira: implement only if `jira` is in the contract. Copy `infra/lambdas/jira_flow.py.example` → `jira_flow.py`. The poller lands raw JSON. Historical issues, changelog, and lead/cycle clocks are Backfill Historical Jira Issues and Status History plus skill Jira Flow Extract — a current-status poller is not a cycle-time extract. 7. Write unmodified JSON to `s3://{raw}/{source}/{yyyy}/{mm}/{dd}/{event-id}.json`. No Parquet here. 8. Idempotent on event id. `GithubProjectsPollFn` remains a heartbeat until GraphQL pagination ships — do not join `github_project_items` as a populated Master Roadmap. ## Done when - Org webhook is registered (or GitLab equivalent if that host is scoped). - One working handler per scoped source; zero handlers for unscoped systems. - Test ping produced a raw object (or CloudWatch shows a verified 200). - Secrets live in Secrets Manager / SSM, referenced from CDK. ## Feeds Backfill Historical GitHub Events (when github is scoped), Backfill Historical Jira Issues and Status History (when jira is scoped), then Store Compressed and Partitioned in S3.
2	Backfill Historical GitHub Events	BPE-2	Purpose Pull historical GitHub issues, PRs, commits, and workflow runs into the raw bucket so …	Read the 'BPE-2 Backfill Historical GitHub Events' activity from playbook 'Delivery Constraint & Opportunity Analysis' (workflow 'Build the Operational Data Lake Pipeline'). Follow its guidance when assisting me. ## Purpose Pull historical GitHub issues, PRs, commits, and workflow runs into the raw bucket so Constraint and Flow Analysis can compute cycle time. Webhooks only catch events after they are registered. ## Inputs - Lake Scope Contract (`repos`, `secrets.github_api_token`). - CDK Application (`GithubBackfillFn` already declared). - Starter workflow `.github/workflows/populate-lake.yml` — template `https://github.com/FeatureFactory-io/ops-lake-starter/blob/main/.github/workflows/populate-lake.yml`. Do not laptop-invoke against shared env. - Skills Serverless Data Ingestion and Populate the Lake. ## Steps 1. Keep starter `infra/lambdas/github_backfill.py` and `populate-lake.yml`. Open skill Populate the Lake. 2. Issues and pulls: `state=all` and `per_page=100`. GitHub's default is open only — that was the empty cycle-time dashboard. Wrap each item `{backfill: true, kind, repo, payload}` with `kind` in `issues` \| `pulls` \| `commits` \| `workflow_runs`. 3. Paginate `Link: rel=next` for list endpoints. Commits API pagination is not the same as issue/PR Link headers — follow `page=` until empty. Timeout 10 minutes / 1024 MB. 4. Repo secret `OPSLAKE_GITHUB_PAT` (`repo`, `read:org`) — never `GITHUB_`. Populate-lake syncs it into Secrets Manager. 5. GitHub → Actions → Populate the Lake* → Run workflow. Watch CloudWatch for `written=N`. Never laptop invoke against shared env. 6. After raw objects land, the same workflow runs `scripts/sync_raw_to_curated.py` then crawlers then Athena proof. Athena LIMIT 10 is activity Present the Catalog via Athena; here you only need `written=N` and raw keys under `s3://{raw}/github/`. ## Done when - Glue `github_issues` will contain closed rows after Parquet + crawl (`payload.state='closed'`), not only open. - CloudWatch shows `written=N` for issues, pulls, commits, workflow_runs. ## Feeds Store Compressed and Partitioned in S3, then Glue/Athena. Cycle-time EDA depends on this history. ## Honesty `GithubProjectsPollFn` is a heartbeat until GraphQL pagination ships. Do not join `github_project_items` as if Master Roadmap items are populated.
3	Backfill Historical Jira Issues and Status History	BPE-3	Purpose When jira is in the Scope Contract, pull historical issues plus a status timeline …	Read the 'BPE-3 Backfill Historical Jira Issues and Status History' activity from playbook 'Delivery Constraint & Opportunity Analysis' (workflow 'Build the Operational Data Lake Pipeline'). Follow its guidance when assisting me. ## Purpose When `jira` is in the Scope Contract, pull historical issues plus a status timeline into the raw bucket so Constraint and Flow Analysis can compute lead time and cycle time. A poller that only sees current status cannot reconstruct cycle start. ## Skip when Scope Contract `sources` does not include `jira`. Do not stand up Jira Lambdas or views "just in case". Do not copy `jira_flow.py.example` onto a GitHub-only lake. ## Inputs - Lake Scope Contract (`sources`, project keys from skill Scope the Operations Lake → Jira flow keys, `views`, Started status set, watermark / secret names). - Starter stub `infra/lambdas/jira_flow.py.example` — copy to `infra/lambdas/jira_flow.py` only if jira is scoped. - CDK Application (`JiraPollFn` / Jira backfill function already declared when jira is scoped). - Skill Jira Flow Extract → Extraction recipe. - Skill Serverless Data Ingestion (handler wiring only). ## Steps 1. If jira is scoped: copy `infra/lambdas/jira_flow.py.example` → `jira_flow.py`. Open skill Jira Flow Extract → Extraction recipe. Implement that contract — do not invent a different clock. Secret `ops-lake/jira-api-token`; base URL from contract Jira flow keys. 2. Persist both issue snapshots (`closed_at` = `resolutiondate`) and status segments (`status`, `entered_at`, `exited_at`). 3. JQL is scoped and bounded (`updatedDate`). Cloud: cursor pagination. Server/DC: `startAt`. Page size about 100. 4. Build the timeline from changelog `field == status`. Paginate per-issue changelog when search expand truncates. 5. Record the Started status set on the contract. Compute lead and cycle from the landed columns (business days). Do not re-query Jira in EDA for those clocks. 6. Incremental cursor in DynamoDB keyed by `source`. Periodic bounded backfill for tickets older than the watermark. Populate-lake today is GitHub-shaped — if you add a Jira invoke job, put it in the same workflow; do not laptop-invoke shared env. 7. Write unmodified JSON to `s3://{raw}/jira/{yyyy}/{mm}/{dd}/{event-id}.json`. Parquet is the next activity. ## Done when - Glue (or the contract views) contain resolved rows (`closed_at` not null), not only open issues. - `jira_status_segments` (or the named contract view) has at least one closed segment per resolved issue that has changelog. - Athena proof LIMIT 10 succeeds for every jira view that has Parquet. - Lead/cycle can be computed from curated columns without another Jira call. ## Feeds Store Compressed and Partitioned in S3, then Glue/Athena. Constraint analysis may use these clocks as measured touch/wait inputs; it does not replace impact scoring. ## Honesty Sprint/velocity board reports are out of scope here. Do not treat them as lead or cycle.
3	Store Compressed and Partitioned in S3	BPE-3	Purpose Turn raw landing-zone JSON into Hive-partitioned Parquet in the curated bucket. Inputs Raw Operational …	Read the 'BPE-3 Store Compressed and Partitioned in S3' activity from playbook 'Delivery Constraint & Opportunity Analysis' (workflow 'Build the Operational Data Lake Pipeline'). Follow its guidance when assisting me. ## Purpose Turn raw landing-zone JSON into Hive-partitioned Parquet in the curated bucket. ## Inputs - Raw Operational Events (S3 Landing Zone). - CDK Application (`ParquetTransformFn` lives in IngestionStack). - Lake Scope Contract paths: raw vs curated. - Populate-Lake Pipeline (batch transform after backfill). - Skill S3 Data Partitioning and Compression → Recipe step 2 (event→table map). ## Canonical layout (do not invent another) - Raw: `s3://{raw}/{source}/{yyyy}/{mm}/{dd}/{event-id}.json` - Curated: `s3://{curated}/{table}/source={source}/dt={yyyy-mm-dd}/part-{uuid}.parquet` - `{table}` is a name from contract `views`. Two buckets, always. ## Steps 1. Open skill S3 Data Partitioning and Compression. Handler path: `infra/lambdas/parquet_transform.py` (`_normalize_row` omits `source`). 2. Event → table map (skill 64 recipe 2): `push` → `github_commits`, `pull_request` → `github_pull_requests`, `issues` → `github_issues`, `workflow_run` → `github_workflow_runs`; backfill `kind` `commits`/`pulls`/`issues`/`workflow_runs`. Projects v2 → `github_project_items` only when that poller is real. 3. `ParquetTransformFn` writes pyarrow Parquet. Omit `source`/`dt` from the row body — they are Hive partitions only. 4. Webhooks: SQS buffer. Backfill: `scripts/sync_raw_to_curated.py` from populate-lake only, not a laptop against shared env. 5. Lifecycle: hot recent partitions Standard; cold raw/history IA or Glacier. 6. Confirm lake buckets have S3 Bucket Keys (`bucket_key_enabled=True`) via pytest synth (`tests/unit/test_ingestion_stack.py`), not the S3 console encryption tab alone. 7. If `jira` is scoped, run Backfill Historical Jira Issues and Status History (sibling of GitHub backfill) before Glue — this activity's predecessor is GitHub backfill only because workflows cannot cross-link both. ## Done when - Curated prefixes exist for every contract `views` entry that backfill produced. - Glue can crawl without "duplicate columns". - No EMR/Spark cluster. - SSE-KMS Bucket Keys are on (new objects do not call KMS per Put/Get). ## Feeds Catalog with AWS Glue.
4	Catalog with AWS Glue	BPE-4	Purpose Register curated prefixes in Glue so Athena can see tables without hand-written DDL. Inputs …	Read the 'BPE-4 Catalog with AWS Glue' activity from playbook 'Delivery Constraint & Opportunity Analysis' (workflow 'Build the Operational Data Lake Pipeline'). Follow its guidance when assisting me. ## Purpose Register curated prefixes in Glue so Athena can see tables without hand-written DDL. ## Inputs - Partitioned Parquet Data Lake. - Lake Scope Contract (`athena_database` default `ops_catalog`, `views`). - Populate-Lake Pipeline (one-shot crawler start). Starter: `.github/workflows/populate-lake.yml`. - Skills AWS Glue Cataloging and Populate the Lake. ## Steps 1. Open skills AWS Glue Cataloging and Populate the Lake. Do not start crawlers in the Glue console. 2. One database named from contract `athena_database` (default `ops_catalog`). One crawler per `views` entry, scoped to `s3://{curated}/{table}/`. CDK already declared them — intern does not click Create crawler. 3. Daily schedule `cron(0 6 * * ? )` plus populate-lake job step `start-crawler` after Parquet sync. Wait until State=READY: `aws glue get-crawler --name {table}_crawler` (name from CatalogStack). 4. Built-in Parquet classifier only unless a source fails auto-detect. 5. If Glue reports duplicate columns, go back to Store Compressed and Partitioned in S3* (`source` leaked into Parquet body). Do not patch the crawler. 6. Escalate to Operations Analyst before applying a crawler change that alters an existing column type. ## Done when - Glue tables exist for every contract view that has Parquet (table name = view name). - No crawlers for unscoped systems. ## Feeds Present the Catalog via Athena.
5	Present the Catalog via Athena	BPE-5	Purpose Prove the Athena surface. Glue crawler tables are the contract views. Downstream consumers (Streamlit, …	Read the 'BPE-5 Present the Catalog via Athena' activity from playbook 'Delivery Constraint & Opportunity Analysis' (workflow 'Build the Operational Data Lake Pipeline'). Follow its guidance when assisting me. ## Purpose Prove the Athena surface. Glue crawler tables are the contract views. Downstream consumers (Streamlit, Pandas, MCP) query those tables (or thin views if you add them), never random Glue names. ## Inputs - AWS Glue Data Catalog. - Lake Scope Contract (`views`, `athena_workgroup` default `ops-lake-analysts`, `athena_database` default `ops_catalog`). - Populate-Lake Pipeline. - Skills Athena SQL Querying and Populate the Lake. - SQL template `sql/proof_views.sql` (placeholders `{database}`, `{view}`). ## Steps 1. Open skills Athena SQL Querying and Populate the Lake. 2. One workgroup from contract `athena_workgroup` (AWS name default `ops-lake-analysts`) with `EnforceWorkGroupConfiguration` and a per-query scan cap (start 5GB). 3. After crawlers READY, populate-lake runs `scripts/create_athena_views.py` — LIMIT 10 per contract view; skip missing tables. Do not laptop-run against shared env. Console alternative: Athena query editor → workgroup dropdown `ops-lake-analysts` → database `ops_catalog`. 4. Copy-this proof: `SELECT COUNT() AS n FROM {view} WHERE source = '{source}'` then `SELECT FROM {view} LIMIT 10`. Backfill JSON: extract `$.payload.`. Prefer closed snapshots when deduping issue numbers. Record each `QueryExecutionId` on artifact Athena Catalog Views. 5. Glue tables already are* the views; the script is a proof runner, not a second catalog of DDL. 6. Hands off to Operations Analyst when all contract views that have data are queryable. ## Done when - Each populated contract view runs a `SELECT ... LIMIT 10` in the workgroup. - Streamlit/Pandas/MCP are pointed at these tables/views only. ## Feeds Constraint and Flow Analysis (artifact gate).

Activities in Build the Operational Data Lake Pipeline