Datalake Formation - Delivery Constraint & Opportunity Analysis

Description

Stand up the operational data lake itself: pull raw activity from engineering systems (GitLab/GitHub/Jira) through a lightweight serverless pipeline, land it in S3 compressed and partitioned, index it with Glue, and expose it as a queryable catalog through Athena — no EMR, no persistent clusters.

Activities in this Phase

Order	Name	Actions
Model and Deploy Infrastructure as Code
#1	Scope Sources, Org, and Environment	Read the 'MCE-1 Scope Sources, Org, and Environment' activity from playbook 'Delivery Constraint & Opportunity Analysis' (workflow 'Model and Deploy Infrastructure as Code'). Follow its guidance when assisting me. ## Purpose Write the Lake Scope Contract before any AWS resource is modeled. Later activities read only this file for org, sources, CI host, AWS account, region, buckets, Athena view names, lake repo, OIDC sub, and secret keys. ## Inputs - Artifact Operations Lake Starter Kit — clone `https://github.com/FeatureFactory-io/ops-lake-starter` (Use this template). Not the live opsdl lake. - Human target (GitHub org / GitLab group / Jira site). - `aws sts get-caller-identity` and configured region. - Where the lake repo will live (GitHub vs GitLab). - Skill Scope the Operations Lake → Copy-this YAML (and Jira flow keys if jira is scoped). ## Steps 1. Clone the starter kit into the org (GitHub → Use this template). Rename `lake_repo` if the clone is not `ops-lake-starter`. Open skill Scope the Operations Lake. 2. Fill `docs/lake-scope-contract.yaml` in the clone: `org`, `host`, `sources`. List repos: `gh api orgs/{org}/repos --paginate`. Projects v2: `gh api graphql -f query='query($org:String!){organization(login:$org){projectsV2(first:20){nodes{id title}}}}' -f org=YOUR_ORG`. Default github entities = commits, pull_requests, issues, workflow_runs, project_items. Drop unused. Do not add gitlab or jira unless this org uses them. 3. Set `ci` to exactly one of `github_actions` \| `gitlab_ci` matching `host`. 4. Fill `aws_account`, `aws_region`, `env`, `lake_name`, `lake_repo`, `oidc_sub`. Derive `views` as `{source}_{entity}` (copy-this list is in the YAML). 5. Fill `secrets.github_webhook` and `secrets.github_api_token`. GitHub Actions PAT secret must not be named `GITHUB_` — use `OPSLAKE_GITHUB_PAT`. If `jira` is in `sources`, copy skill 106 Jira flow keys* (project keys, started_statuses, jira views) — otherwise ignore skill Jira Flow Extract. 6. Canonical paths in the template (raw JSON vs curated Hive Parquet). Two buckets, always. Keep `streamlit/data/contract_views.yaml` in sync with `views` so mart tabs skip correctly. ## Done when - Starter kit cloned (not opsdl). - `docs/lake-scope-contract.yaml` is filled and committed. - `views` is the complete catalog surface later activities will create. - `ci` is XOR; `lake_repo` and `oidc_sub` are set. ## Feeds Activity Model the Data Lake as CDK Stacks.
#2	Model the Data Lake as CDK Stacks	Read the 'MCE-2 Model the Data Lake as CDK Stacks' activity from playbook 'Delivery Constraint & Opportunity Analysis' (workflow 'Model and Deploy Infrastructure as Code'). Follow its guidance when assisting me. ## Purpose Model every AWS resource the lake needs as a Python CDK app (TypeScript only if the host repo is already TypeScript). No console clicks. No separate SAM template. ## Inputs - Starter clone `infra/` already has four stacks. Customize from the filled contract; do not scaffold empty. - `docs/lake-scope-contract.yaml`. Instantiate only sources/tables listed there. - Skill AWS CDK Infrastructure as Code → Recipe / Copy-this tests. ## Steps 1. Open skill AWS CDK Infrastructure as Code. Files to touch: `infra/ops_lake/scope.py` (loader), then `deploy_stack.py`, `ingestion_stack.py`, `catalog_stack.py`, `eda_stack.py` only as the contract requires. Do not scaffold empty `infra/`. 2. `DeployStack`: GitHub (or GitLab) OIDC + deploy IAM role. Trust both name-only and ID-qualified `sub` patterns (skill GitHub Actions CI/CD → Copy-this IAM trust). Output `DeployRoleArn` for `AWS_DEPLOY_ROLE_ARN`. 3. `IngestionStack`: raw + curated + athena-results buckets (names from contract), `BucketEncryption.KMS_MANAGED` and `bucket_key_enabled=True`, lifecycle; HTTP API; one webhook Lambda per scoped source; `GithubBackfillFn` (10 min / 1024 MB); EventBridge poller for Projects v2 if github scoped (heartbeat until GraphQL ships); DynamoDB cursor; SQS; `ParquetTransformFn`. Do not attach a customer-managed `kms.Key` to buckets that notify SQS. If jira scoped, wire `jira_flow.py` copied from `infra/lambdas/jira_flow.py.example`. 4. `CatalogStack`: Glue database `athena_database`; one crawler per contract `views` on `s3://{curated}/{table}/`; Athena workgroup; named preview queries matching `views`. 5. `EdaStack`: Streamlit on ECS Fargate + ALB (`linux/amd64`). Dockerfile COPY `.py`, `pages/`, and `data/`. Grant task role Athena, Glue GetTables, curated-bucket read, Athena-results read/write. Output `StreamlitMartUrl`. 6. Environment from CDK context / SSM. Synth: from repo root `cdk synth -c skip_bundling=true` (or `cd infra` if that is how this clone is wired). 7. Pytest: `tests/unit/test_deploy_stack.py`, `test_ingestion_stack.py`, `test_catalog_stack.py`, `test_eda_stack.py`, `test_story_data.py`. Assert BucketKeyEnabled on all three lake buckets. ## Done when - `infra/app.py` instantiates all four stacks. - Synth tests pass, including S3 Bucket Keys. - GitHub-only contract → no GitlabWebhookFn / live JiraPollFn. ## Feeds XOR: Deploy via GitHub Actions* or Deploy via GitLab CI.
#3	Deploy via GitHub Actions	Read the 'MCE-3 Deploy via GitHub Actions' activity from playbook 'Delivery Constraint & Opportunity Analysis' (workflow 'Model and Deploy Infrastructure as Code'). Follow its guidance when assisting me. ## Purpose Deploy the CDK app through GitHub Actions with OIDC. Production path for a GitHub-hosted lake repo. ## Inputs - Lake Scope Contract (`ci` must be `github_actions`). - CDK Application (includes DeployStack). - Starter `.github/workflows/deploy.yml` — copy from the clone; do not invent a second pipeline. Template: `https://github.com/FeatureFactory-io/ops-lake-starter/blob/main/.github/workflows/deploy.yml`. - Skill GitHub Actions CI/CD → Recipe. ## Skip when `ci` is `gitlab_ci`. Do not create deploy.yml "just in case". ## Steps 1. Keep starter `.github/workflows/deploy.yml`. 2. Chicken-and-egg: laptop, same region as the contract: `cdk bootstrap` then `cdk deploy OpsLakeDeploy` (or `--all`) once. Read `DeployRoleArn` from stack outputs. GitHub → Settings → Secrets and variables → Actions → New repository secret `AWS_DEPLOY_ROLE_ARN`. Also `OPSLAKE_GITHUB_PAT` (`repo`, `read:org`). Never `GITHUB_`. After that, never `cdk deploy` a shared env from a laptop. 3. `permissions: id-token: write`. Assume the deploy role via `aws-actions/configure-aws-credentials`. No `AWS_ACCESS_KEY_ID`. 4. Jobs: pytest → `cdk deploy --all --require-approval never`. Print `DeployRoleArn`, `GithubWebhookUrl`, `StreamlitMartUrl` to the job summary. IAM simulate Streamlit task-role S3 read. 5. OIDC trust both* `repo:{org}/{lake_repo}:` and `repo:{org}@/{lake_repo}@:`. A `main` push AssumeRoleWithWebIdentity must succeed (check the Actions log). 6. After first GHA deploy, record URLs/ARNs in `docs/deploy-manifest.yaml`. Org webhook registration is the next human step (activity Stand Up Serverless Ingestion) — deploy does not by itself land events. ## Done when - Workflow file is the only shared-env deploy path. - OIDC covers both `sub` shapes; `AWS_DEPLOY_ROLE_ARN` is set. ## Feeds Pipeline + populate-lake. Shared-env resources appear only after this workflow runs on `main`.
#3	Deploy via GitLab CI	Read the 'MCE-3 Deploy via GitLab CI' activity from playbook 'Delivery Constraint & Opportunity Analysis' (workflow 'Model and Deploy Infrastructure as Code'). Follow its guidance when assisting me. ## Purpose Deploy the CDK app through GitLab CI with OIDC. Alternative to GitHub Actions. ## Inputs - Lake Scope Contract (`ci` must be `gitlab_ci`). - CDK Application. - Starter `.gitlab-ci.yml.example` — copy to `.gitlab-ci.yml`. Skill GitLab CI/CD → Recipe. ## Skip when `ci` is `github_actions`. Running both against one AWS account causes split-brain deploys. ## Steps 1. Copy `.gitlab-ci.yml.example` to `.gitlab-ci.yml`. Do not also keep a live GitHub `deploy.yml` against this account. 2. Chicken-and-egg: laptop `cdk bootstrap` then one `cdk deploy OpsLakeDeploy` to mint the GitLab OIDC IAM role. Store the role ARN as CI/CD variable `AWS_DEPLOY_ROLE_ARN` (not `AWS_ACCESS_KEY_ID`). Trust `aud` = `https://gitlab.com` (or this instance URL) and the project `sub`. 3. `id_tokens` block mints the OIDC token. pytest → `cdk synth` → `cdk diff` → `cdk deploy` with `when: manual` for production. Register GitLab Environment `production`. 4. After first deploy, record URLs/ARNs in `docs/deploy-manifest.yaml`. ## Done when - `.gitlab-ci.yml` is the only shared-env deploy path. - GitHub Actions deploy workflow is absent. - A `main` pipeline assumed the role. ## Feeds Pipeline (populate equivalent on GitLab if you add it; otherwise GitHub populate-lake is the GitHub-ci path).
Build the Operational Data Lake Pipeline
#1	Stand Up Serverless Ingestion from Scoped Sources	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	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	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	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	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	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).