Agentic AI data warehouse demonstration

Reader-facing examples

1. What this demonstration is

A self-contained worked example of agent-assisted warehouse design and reporting assurance. It walks through source profiling, dimensional design, SQL and pipeline specifications, mart building and report QA — using only synthetic extracts for a fictional trust.

The practical value: agentic AI can support data engineering, source profiling, documentation, validation, reporting assurance and analysis — but does not replace accountable human review. This shows a governed way of working: bounded agents, rules, checkpoints, synthetic data, validation and sign-off.

2. Why synthetic data was used

Real patient data, staff records, supplier schemas, credentials and live Azure resources cannot be used on a public demonstration site. Synthetic data allows a realistic multi-system scenario — including extract changes and linkage problems — without confidentiality risk.

3. How this fits the rest of the site

The existing draft reports and agent operating model show how agents support public-data briefs and governed performance work. This demonstration complements them by showing the upstream data journey: what happens before a reporting table exists — when source extracts are messy, joins are uncertain, and extract rules change.

4. How this differs from the draft reports page

The draft reports page uses public aggregate NHS data for Dorset HealthCare (RDY) — MHSDS, CSDS, Oversight Framework and similar published sources.

This warehouse demo uses fictional internal source systems → mock warehouse → reporting tables → QA. DRH and RDY must not be read as the same provider.

5. The fictional source systems

• CareCall — urgent and unscheduled care contacts (IUCS pathway)
• CareCase — case management opens and status
• Legendary Care — referrals and encounters
• RosterFlow — bank and substantive shifts
• LedgerWise — agency and bank spend by cost centre
• LocalOps spreadsheets — extract change logs, user ID mappings and validation notes

End-to-end workflow

What happened at each stage

6. How synthetic source data was created

A Python generator created CSV/XLSX extracts, manifest and summary for fictional Demo Rivers Health. See warehouse-demo README and demo run index.

7. How agents profiled the messy source data

A Source Profiling Agent documented grain, linkage, volume trends, a data-quality register and extract-change risk — without proposing dimensional models or SQL. Outputs: profiling report, worked conversation.

8. How agents proposed the warehouse design

A Warehouse Design Agent proposed staging, dimensions, bridge tables and linkage strategy from profiling evidence. See design proposal and human review pack.

9. How Azure SQL-style artefacts were generated

DDL, QA views and deployment notes — specification only, not deployed to live Azure SQL. See SQL README and deployment notes.

10. How ADF-style pipeline specifications were generated

JSON pipeline specs for ingest, staging refresh and mart build. See pipeline overview.

11. How the mock warehouse supported report QA

Provider-month mart measures were built offline. A flawed urgent-care brief was corrected after Report QA against profiler evidence. See flawed draft, corrected brief and QA conversation.

12. Likely genuine change vs likely data artefact

Agents distinguished patterns using profiler and mart outputs only:

• Jan–Feb 2026 (likely genuine — needs review): IUCS contacts, operational cases, bank shifts and agency spend elevated together — multi-source corroboration suggests possible operational pressure.
• March 2026 (likely artefactual — needs review): Case opens rose while IUCS fell; most new cases lack CareCall source link; CareCase extract rule change recorded in LocalOps log.
• December 2025 (likely artefactual attribution — needs review): 46 contacts show date-boundary mismatch aligned with CareCall export schedule change.

Full analysis: synthetic urgent-care warehouse analysis.

13. Human review and sign-off

Even with agent support, accountable people must still:

• Approve the warehouse design via the human review pack before SQL work
• Sign off checkpoints at each run (audit trail in warehouse-demo/checkpoints/)
• Block flawed report narratives until profiler evidence is applied
• Confirm reporting-table assurance before operational use

Bounded agent rules, forbidden sources lists and synthetic data keep the method visible and reviewable.

14. Limitations and next steps

• SQL and ADF are demonstration artefacts — not executed on live Azure
• Mart built by offline Python script, not a live pipeline
• Partial DDL vs full design documentation — see internal QA notes
• Human reviewers may compare findings separately; agents did not use the reviewer answer key as evidence

Technical artefacts