Consultative Discovery and Requirements Gathering Questions

1. Reflective/paraphrasing: after a stakeholder explains a need I restate it in my own words (e.g., “So you want a weekly executive view of LTV by cohort with ability to filter by region — is that right?”). This surfaces misunderstandings immediately and keeps the conversation aligned.

2. Task-based probing and scenarios: I ask them to walk me through how they’d use the report (decisions they’ll make, frequency, who will act). Example prompts: “Show me the exact steps you’d follow to investigate a drop in revenue,” or “What cutoff would trigger an escalation?” This reveals required dimensions, granularity, refresh cadence, and edge cases.

3. Visual prototypes and sample outputs: I sketch a mock dashboard or produce a quick wireframe/sample dataset in Power BI/Tableau so stakeholders see concrete outputs. Visuals force precise requirements (e.g., chart types, sorting, drilldowns).

Validation approach:

• Immediate read-back and capture: summarize acceptance criteria live and confirm (agree on KPIs, time windows, filters).
• Written requirements & examples: send a one-page spec with mock screenshots, field-level definitions, and sample SQL or data dictionary; ask for explicit sign-off.
• Rapid prototype + iteration: deliver an MVP dashboard using representative data, run a validation session where stakeholders execute real tasks, log issues, and iterate until acceptance.
• Traceability: map each requirement to dashboard elements and test cases so nothing is missed.

These techniques reduce ambiguity, catch hidden needs, and provide measurable validation before production.

Clarifying questions (aim to make metric unambiguous)

• Business intent: Is conversion defined as ANY purchase event, only paid completed orders, or net-paid orders after refunds?
• Numerator: Should conversions count unique orders, unique customers who purchased, or purchase line-items? Should multiple purchases in window count once or multiple times?
• Denominator: Is denominator sessions, visitors (unique users), new users, or eligible users (e.g., landing on campaign page)?
• Attribution window & model: What lookback window (e.g., 7/14/30 days)? First-touch, last-touch, or rule-based multi-touch? For multi-session flows, how to assign conversion to an earlier session?
• Returns/refunds: If refunded fully within window, should conversion be excluded/negated? Partial refunds? Time cutoff for considering refunds?
• Guest vs registered: Should guest purchases be attributed to sessions (cookie/session id) and included in user-level metrics? If a guest later registers, how to stitch?
• Session stitching: Are we permitted to stitch across devices via deterministic IDs (user_id) only, or probabilistic stitching? Preferred priority: user_id > email hash > cookie.
• Edge business rules: How to handle fraudulent orders, test transactions, canceled-but-not-refunded orders?

SQL-level acceptance criteria (tests the implementation)

1. Numerator matches business rule: count of completed_orders within attribution window and not fully refunded within refund_grace_period.

• Example (order-level unique conversions):

SELECT COUNT(DISTINCT order_id) AS conversions
FROM orders
WHERE status = 'completed'
  AND completed_at BETWEEN @start AND @end
  AND COALESCE(refunded_amount,0) < total_amount;

2. Denominator as unique users/sessions:

-- unique visitors
SELECT COUNT(DISTINCT COALESCE(user_id, anon_id)) AS visitors
FROM sessions
WHERE landing_page = @campaign_page
  AND session_start BETWEEN @start AND @end;

3. Attribution correctness: conversions attributed to last session within 30 days before order:

-- join order to sessions where session_start <= order_time AND session_start >= order_time - interval '30 days'

4. Refund handling test: orders fully refunded within 14 days are excluded — verify by toggling refund window and comparing counts.
5. Guest stitching: guest orders tied to anon_id in sessions; when user later registers, conversions retroactively attributed only if deterministic_link exists — provide test rows to assert expected mapping.

Key edge cases to validate

• Multiple orders by same user in window (should count as N conversions or 1 based on rule)
• Partial refunds: reduce order value but does it invalidate conversion?
• Order created before window, completed inside window
• Refund occurs after attribution window — should it retroactively remove conversion?
• Cross-device purchases with no deterministic id (drop or probabilistically attribute?)
• Bot/test traffic and duplicate events
• Orders with multiple payment attempts, abandoned then completed
• Timezone mismatches between session and order timestamps
• Merged user accounts (duplicate user_ids consolidated)

Suggested deliverables

• A decision matrix capturing answers to all clarifying questions
• SQL test cases (seeded rows) that assert expected numerator/denominator under each rule
• Dashboard notes documenting attribution window, refund policy, and stitching method so metric is interpretable and reproducible.

Requirements & constraints:

• Versioned compliance requirements (immutable history), approval workflow, impact analysis for BI artifacts, immutable audit trail, stakeholder notifications, role-based access, traceability from dashboards/queries to requirement versions, low-latency lookups for analysts.

High-level architecture:

• Governance Service (web app + API)
• Versioned Requirement Store (immutable append-only DB / event store)
• Workflow Engine (approvals, notifications)
• Impact Analyzer (static + dynamic lineage)
• BI Artifact Registry (catalog with artifact → requirement links)
• Audit Log (WORM storage + cryptographic signing)
• Notification Bus (email/Slack/webhooks)
• Authentication/Authorization (RBAC + SSO)

Key components & responsibilities:

1. Requirement Store: Each change creates a new immutable version object (v1, v2...) with metadata: policy_owner, effective_date, change_reason, diff. Use event-sourcing (append-only) or immutable snapshots in DB.
2. Workflow Engine: Triggers on new version -> reviewers (change_approver, legal, technical_owner) assigned, supports parallel/serial approvals, timeouts, escalations.
3. Impact Analyzer: Combines static lineage (catalog of datasets, tables, queries) and dynamic validation (sample queries, tests). Produces impacted BI artifacts list and severity score.
4. BI Artifact Registry: Catalog entries for dashboards/queries with metadata: owner, source tables, last validated requirement version, linked requirement_versions (many-to-many), automated tests.
5. Audit Trail: Every action (create/change/approve/link) appended to WORM store, signed, and exportable for auditors.
6. Notifications: On proposal, approval, rejection, and when an artifact is impacted; supports templated messages and poke/retry.

Data flow:

• Policy owner creates change -> Requirement Store (new version) -> Workflow Engine notifies approvers -> upon approval, Workflow Engine triggers Impact Analyzer -> Impact report saved; BI owners receive notifications and must update/validate artifacts referencing new requirement version -> BI Artifact Registry updated; all steps recorded in Audit Log.

Roles:

• Policy Owner: creates/authorizes requirement edits, defines effective_date.
• Change Approver: reviews technical and legal impact, signs off.
• Technical Owner: assesses feasibility, assigns remediation to BI owners.
• BI Owner (Analyst): updates dashboards/queries, runs validations, links artifact to new requirement version.
• Compliance/Audit: reads audit trail, requests reports.

Traceability & BI artifact tracking:

• Each BI object stores linked requirement_version IDs and validation status. CI pipeline enforces automated tests (data checks, KPI thresholds). When requirement version changes, linkage marks artifact as "stale" until revalidated; dashboards display requirement_version badge.

Security, scaling & trade-offs:

• Use RBAC + SSO; scale services horizontally; lineage accuracy vs. cost: start with catalog-driven static lineage, add sampled dynamic analysis later. Event-sourcing gives immutable history but increases complexity; WORM & cryptographic signing ensures tamper-evidence.

Metrics & SLAs:

• Time-to-approve, % artifacts revalidated within SLA, mean-time-to-remediate, lineage coverage.

Clarifying questions (grouped):

Ownership & Stewardship

• Who is the executive sponsor for the SSOT initiative?
• Which team owns the metrics layer platform (Analytics/BI, Data Engineering, or centralized Data Platform)?
• For each metric (e.g., ARR, CAC, MAU), who is the business steward (Finance, Marketing, Product)?

Transformation logic & definitions

• For each core metric, what is the authoritative definition and formula? (Data sources, filters, windows)
• Are there canonical dimension definitions (customer, account, product, cohort)?
• How should late-arriving data, deduplication, and corrections be handled?

SLA: refresh & accuracy

• Required refresh cadence per metric (real-time, hourly, daily, weekly)?
• Maximum acceptable data latency and freshness SLA?
• Accuracy tolerance and validation thresholds (e.g., <0.1% variance vs financial close)?

Access controls & security

• Who should have read vs. write vs. publish permissions?
• Are there PII/PIA considerations requiring masking, row-level security, or restricted dashboards?
• Audit/logging requirements for metric access and changes?

Change-control & provenance

• What is the change request workflow for metric definition updates?
• Required approvals for changes (steward, data platform, legal/finance for monetary metrics)?
• Versioning, rollout (canary vs. full), and backfill policies?

Downstream migration & adoption

• Which downstream reports/dashboards will be migrated first (priority list)?
• How will consumers be notified and supported during migration?
• Rollback strategy if SSOT metric diverges from existing reports?

Operational & monitoring

• Monitoring/alerting for ETL failures, significant deltas, and SLA breaches
• Runbooks and escalation paths for incidents affecting metrics

Initial Governance Charter Outline

1. Purpose

• Establish a single source of truth for cross-functional metrics to ensure consistent, auditable business reporting.

2. Scope

• Core finance, marketing, and product KPIs; canonical dimensions; metrics transformation code in the centralized metrics layer.

3. Stakeholders & Roles

• Executive Sponsor: VP Data/Head of Analytics — strategic ownership, funding.
• Platform Owner: Data Engineering — implements infrastructure, CI/CD, monitoring.
• BI Owner: BI Team Lead/Manager — publishes curated datasets and dashboards.
• Metric Stewards: Domain SMEs (Finance Lead, Marketing Lead, Product Analytics Lead) — own definitions, approve changes.
• Consumers: Business users, product managers, finance analysts — read access and migration recipients.
• Security/Compliance: InfoSec/Legal — data access approvals and PII controls.

4. Responsibilities

• Metric Stewards: define metrics, maintain requirements doc, approve changes.
• Data Engineering: maintain pipelines, enforce SLAs, implement SCD/backfill logic.
• BI Team: build and publish semantic layer, maintain lineage, run migrations.
• Change Review Board: cross-functional group (stewards + engineering + BI + compliance) to evaluate changes.

5. Processes

• Definition repository: canonical metric catalog (metric ID, definition, owner, source, SQL/transformation, tests).
• Change-control: RFC → automated tests → staging deploy → steward approval → production deploy → consumer notification.
• Versioning & Backfill: every change gets version tag; backfill policy documented per metric.
• Migration playbook: inventory dependents → prioritized migration waves → dual-run period (old vs. SSOT) → decommission old reports.

6. SLAs & Monitoring

• Categorize metrics by criticality (P1 financial closes: daily hourly; P2 operational: daily; P3 exploratory: weekly).
• Define freshness, accuracy thresholds, and alerting channels.
• Weekly health dashboard (pipeline status, drift alerts, test failures).

7. Access & Security

• RBAC model: groups (read/publish/admin).
• Row-level security for sensitive entities; column masking for PII.
• Audit logs retained for X months.

8. KPIs for Governance Success

• % of high-priority dashboards migrated to SSOT within 6 months
• Number of conflicting metric definitions eliminated
• Mean time to detect/resolve metric incidents

9. Onboarding & Communication

• Training sessions, migration office hours, changelog, and a consumer-facing SLA page.

10. Review Cadence

• Quarterly governance reviews; annual charter refresh.

This charter provides the foundation—first sprint should produce the canonical metric catalog for top 10 business metrics, CI tests for transformations, and a migration plan for the top 3 dashboards.

1. Data accuracy — daily violation counts match source-of-truth ETL table

• Pass: Aggregated counts on the dashboard equal the results of the canonical SQL query against the compliance_events table for each region/date (exact match).
• Fail: Any region/date count differs; or discrepancy > 0 rows. Test: run provided SQL and compare.

2. Freshness — dashboard reflects most recent load within SLA

• Pass: Dashboard shows data up to the last ETL batch timestamp; the latest event timestamp is ≤ 24 hours old (or SLA agreed) and matches ETL metadata.
• Fail: Latest data older than SLA or ETL metadata indicates failure. Test: compare dashboard max(event_time) to ETL run_time.

3. Permissions & access control

• Pass: Role-based access enforces permissions: users without compliance role cannot view PII columns or restricted regions; filters respect row-level security; test users validated.
• Fail: Unauthorized user can access restricted data or export sensitive columns. Test: validate with five personas (admin, compliance, regional manager, auditor, guest).

4. Reproducibility — query and ETL lineage provided

• Pass: Dashboard includes documented SQL/dashboard queries, ETL job IDs, dataset version, and instructions to reproduce the daily numbers end-to-end. Running documented steps yields identical results.
• Fail: Missing or non-functional reproduction steps. Test: follow docs to reproduce counts.

5. Auditability & logging

• Pass: All dashboard exports, data refreshes, and user views are logged; logs include user, timestamp, action, and dataset version; logs retained per policy (e.g., 1 year).
• Fail: Missing logs or insufficient detail for an audit. Test: produce sample audit log for a dashboard view and export.

6. Visual & granularity checks

• Pass: Time granularity defaults to daily; region filter includes all active regions and maps consistently to canonical region codes; date axis shows local timezone per spec.
• Fail: Missing regions, incorrect granularity, or mismatched region codes. Test: compare listed regions to master region table and verify default date binning.

Each criterion is binary: provide evidence (query outputs, ETL metadata, permission test results, logs) to mark Pass/Fail before release.