DataAcuity — BI Pipeline Implementation Runbook

Status: 🔵 DESIGNED — implementation tracking starts at Phase 0 Owner: Tinashe Bhengu Companion docs: DataAcuity_BI_Pipeline.md (architecture/design), DataAcuity_BI_Anonymisation_Standard.md (controls), DataAcuity_BI_Compliance_Map.md (regulator mapping), DataAcuity_Security_Posture.md (security pre-reqs)

The execution-side counterpart to the BI Pipeline design doc. Phase-by-phase: what to build, exact commands, acceptance tests, rollback procedures, monitoring queries.

Security pre-requisite: Security Phases A+B+C+D from DataAcuity_Security_Posture.md MUST be complete before BI Pipeline Phase 0 starts carrying customer data. Phase 0 itself (foundation audit) can happen in parallel.

1. Implementation status — at a glance

Update this table as work progresses.

2. BI Phase 0 — Foundation audit (1 week)

Goal: Honest accounting of what exists today so subsequent phases build on truth, not assumptions.

Tasks

Acceptance

• Updated docs reflect current state accurately
• Grafana dashboard is live
• All roles required for Phase 1 exist
• n8n audit is committed to the repo

Rollback

Phase 0 is read-only. No rollback needed.

3. BI Phase 1 — Extract framework (1 week)

Goal: Reliable, idempotent, restartable extraction from .105 into data_warehouse.raw.* for the 3 revenue-critical source DBs.

Tasks

Schema for raw.*

Tables are named raw.{source_db}__{table}. Schema mirrors source exactly except:

• Add _extracted_at timestamp column
• Add _extract_batch_id UUID column (so we can correlate rows extracted together)
• Keep all source columns (no filtering yet — that happens at staging)

Acceptance

• Every 5 min, fresh rows from the 3 revenue-critical sources land in raw.*
• Freshness alert fires correctly when extract is paused
• Extract is idempotent — running it twice on the same window doesn't create duplicates

Rollback

Stop the extract scheduler. Optionally TRUNCATE raw.* for the affected tables. No app impact since this is read-only against .105.

4. BI Phase 2 — Anonymisation layer (1 week)

Goal: Move data from raw.* → staging.* → intermediate.* with all PII removed at the intermediate.* boundary per DataAcuity_BI_Anonymisation_Standard.md.

Pre-requisites

• Security Phase A complete (port lockdown) ✅
• compliance.salts table exists on .118 (Phase 0 task 0.5)
• A named compliance reviewer is identified

Tasks

Acceptance

• An independent reviewer (not the model author) verifies a sample of intermediate.* rows and confirms no PII leaks
• dbt test suite passes on every intermediate.* model
• Salt rotation procedure documented and tested in staging environment

Rollback

If a PII leak is found post-deployment:

1. Immediately REVOKE ALL ON ALL TABLES IN SCHEMA intermediate FROM PUBLIC and other roles
2. Drop the leaky model
3. Fix the model, re-run anonymisation tests, re-deploy
4. Log the incident in compliance.incident_log

5. BI Phase 3 — First 5 marts + push-back (2 weeks)

Goal: Five highest-value marts built; postgres_fdw push-back wired; BidBaas reads its summary from .104 analytics_db.

Marts to build (in order)

1. marts.fct_ledger_transactions_anon — every ledger event with hashed user, FX-normalised
2. marts.fct_bidbaas_auctions — auction × winner-hash × revenue
3. marts.fct_sdpkt_payments — payments × merchant-hash × FX-normalised
4. marts.dim_user_360_anon — anonymised user × 30-day rollup across all apps in scope
5. marts.agg_revenue_daily_app_country — daily revenue rolled up to (app, country, day)

Each mart:

• Has a description: block stating purpose (POPIA Sec 13 / GDPR Art 5(1)(b))
• Has meta.owner identifying the named owner
• Has meta.compliance_class from {operational, ops_analytics, marketing, financial}
• Has dbt tests: not_null on PK, unique on natural key, k-anonymity ≥ 5 for user-aggregating marts

Push-back tasks

Acceptance

• BidBaas in-app shows "your bids this week" with data sourced from analytics_db
• End-to-end latency (event captured → visible in app) < 1 hour
• No PII leaks past the intermediate.* boundary (verified by dbt tests)
• Push-back job is idempotent (re-running doesn't duplicate or corrupt)

Rollback

If the BidBaas integration breaks:

1. App falls back to direct query against bidbaas_db (the existing path) — verify this fallback works before shipping
2. Drop the FDW connection
3. Diagnose, fix, re-deploy

6. BI Phase 4 — Coverage expansion (2 weeks)

Goal: All source DBs in scope are being extracted, anonymised, and the per-app analytics.*_user_summary views are live.

Tasks

Acceptance

• Every TGN app with an analytics surface is reading from analytics_db
• Source registry is exhaustive (no app data missing)
• Per-app team has signed off on their summary's shape

7. BI Phase 5 — Enrichment expansion (1 week)

Goal: Geographic, temporal, FX, and cross-app context attached to relevant marts.

Tasks

Acceptance

• marts.fct_* tables have enriched columns populated
• A test "user journey" trace across 3+ apps shows correct session linking
• FX conversion is correct (spot check: 100 ZAR transaction shows ~5.5 USD at current rate)

8. BI Phase 6 — BI surfaces (1-2 weeks)

Goal: BigBruh! Analytics tab is live with Superset embeds; per-app dashboards published.

Tasks

Acceptance

• BigBruh!'s Overview includes both live ops counters AND historical analytics
• Each per-app dashboard has a named owner and a refresh schedule
• Performance: dashboards load < 3 s for typical queries

9. BI Phase 7 — ML feature stores (open-ended)

Goal: First two ML feature stores defined, tested for leakage, wired to a training pipeline.

Will be specced out when an ML team is in place. For now:

• ml.* schema reserved
• Naming convention: ml.feat_<entity>_v<N>
• Strict time-aligned features (no future-bleed)
• Versioned tables (never DROP TABLE; add v2, v3 alongside v1)

10. Monitoring queries

Useful one-liners for the on-call to run.

10.1 Extract freshness

-- Show last extract per source table
SELECT table_name, max(_extracted_at) AS last_extract_at, now() - max(_extracted_at) AS lag
FROM (
  SELECT 'ledger_db__transactions' AS table_name, _extracted_at FROM raw.ledger_db__transactions
  UNION ALL
  SELECT 'sdpkt_db__payments', _extracted_at FROM raw.sdpkt_db__payments
  -- ... etc
) t
GROUP BY table_name
ORDER BY lag DESC;

10.2 dbt test failure summary

-- After a dbt run, what's failing?
SELECT model_name, test_name, status, num_failing_rows
FROM dbt_artifacts.test_results
WHERE run_id = (SELECT max(run_id) FROM dbt_artifacts.test_results)
  AND status != 'pass'
ORDER BY num_failing_rows DESC;

10.3 K-anonymity health check (ad-hoc)

-- For a given mart, find groups below k=5
SELECT city, age_band, count(DISTINCT user_hash) AS n
FROM marts.fct_some_user_event
GROUP BY city, age_band
HAVING count(DISTINCT user_hash) < 5
ORDER BY n;

10.4 Push-back lag

-- On .104 analytics_db
SELECT 'bidbaas.user_summary' AS view, max(last_updated_at) AS last_push, now() - max(last_updated_at) AS lag
FROM bidbaas.user_summary
UNION ALL
SELECT 'tagme.user_summary', max(last_updated_at), now() - max(last_updated_at) FROM tagme.user_summary
-- ... etc
ORDER BY lag DESC;

10.5 Token vault access in last 24h

-- On .118 compliance_db
SELECT accessed_by, count(*) AS access_count, array_agg(DISTINCT token_family) AS families
FROM compliance.token_access_audit
WHERE accessed_at > now() - interval '24 hours'
GROUP BY accessed_by;

11. Deploy procedures

11.1 Deploying a new dbt model

1. PR includes: model SQL, description: block, meta.owner and meta.compliance_class, dbt tests, sample output (10 rows of post-anonymisation result)
2. CI runs dbt build --select <new_model>+ on staging warehouse
3. Compliance reviewer signs off if it touches intermediate.* or any user data
4. Merge → next dbt run on .106 picks up the new model
5. Verify: query the new model, check row count + sample output

11.2 Deploying a new push-back view

1. Create the marts.agg_* source mart first
2. Create the analytics.{app}_* view definition
3. Run the push-back job manually once: docker exec dbt_transform dbt run --select <push_back_model>
4. Verify psql -h 104 analytics_db shows the new view
5. App team queries the view, integrates into UI
6. Schedule push-back in cron

11.3 Salt rotation (annual)

1. Schedule a change window (low traffic period)
2. Generate new salt: openssl rand -base64 32 > /tmp/new_salt.txt
3. Update compliance.salts SET is_retired = true, then INSERT new row
4. Update orchestrator's env var with new salt
5. Run full intermediate.* rebuild (will use new salt)
6. Cascade through marts.*, analytics.*
7. Verify analytics still work (joins succeed)
8. Document rotation in compliance.salts audit columns

11.4 Right-to-be-forgotten request

1. Request lands at AuthAPI → users.deleted_at set on .104
2. Within 24 h: extract job picks up the soft-delete
3. Within 7 days: retention job cascades deletes through all warehouse layers
4. Within 30 days: compliance ticket closed with compliance.deletion_completion row + email confirmation to user

Manual escalation if SLA at risk: data engineer runs retention job ad-hoc.

12. Cross-references

• DataAcuity_BI_Pipeline.md — the design
• DataAcuity_BI_Anonymisation_Standard.md — the controls
• DataAcuity_BI_Compliance_Map.md — the regulator mapping
• DataAcuity_Security_Posture.md — security pre-reqs
• Deployment/deployment-credentials.ps1 — replicator credentials for ETL

13. Change log