ADF and Power BI dependency database architecture

A useful reporting dependency database turns ADF JSON, SQL dependency metadata, Power BI lineage, owners, schedules, and snapshots into one reviewed graph for impact analysis.

Book a discovery call View solutions

Published 2026-06-17 · Updated 2026-06-19

A reporting dependency database should model artifacts and relationships, not copy production report data. The first useful version can stay metadata-only while still answering which pipelines, datasets, SQL objects, semantic models, reports, schedules, and owners are connected.

The practical architecture has four layers: source exports, normalized artifact records, dependency edges with confidence, and release views for impact review, owner notification, testing, and rollback.

Closeaim connects this pattern to the reporting dependency guide, the lineage lab, the reporting impact workflow, a BI proof surface, and a book-call path for scoped dependency mapping.

Start with a code-free architecture diagram

Keep the first diagram simple: source exports on the left, normalized dependency tables in the center, and operational views on the right. ADF pipelines, datasets, linked services, triggers, SQL dependency metadata, Power BI semantic models, reports, workspaces, owners, schedules, and run summaries all become metadata sources. The output views should answer impact, confidence, owner, freshness, and release-gate questions.

Normalize artifacts before edges

Create one artifact record per pipeline, activity, dataset, linked service, trigger, SQL table, view, stored procedure, semantic model, report, dashboard, workspace, schedule, owner group, and release ticket. Each artifact needs a stable id, platform, artifact type, display name, source system, source path, environment, owner, last seen timestamp, sensitivity label, and active or retired state.

Store dependency edges with confidence

A dependency edge should record source artifact, target artifact, relationship type, extraction method, confidence score, first seen, last seen, raw snapshot id, reviewer state, and notes. Direct ADF activity-to-dataset links can be high confidence; dynamic SQL, parameterized datasets, report expressions, and cross-workspace semantic model links often need lower confidence and analyst review.

Use CSV schemas before custom tooling

A practical first pass can use five CSVs: artifacts.csv for nodes, dependencies.csv for edges, snapshots.csv for raw export references and hashes, owners.csv for review contacts and escalation, and release_gates.csv for change ticket, affected artifacts, tests, notification state, decision, and rollback notes. This keeps the architecture understandable before a graph database, warehouse, or internal app is chosen.

Separate design metadata from run history

ADF design JSON tells you what should be connected; run history and diagnostic logs tell you what actually executed. Keep those as separate tables so stale pipelines, failed runs, missing triggers, and recently unused reports do not get confused with structural dependency records. Retention rules should be explicit before any monitoring data is imported.

Protect metadata like sensitive data

Table names, report titles, workspace names, owner names, tenant ids, and schedules can reveal business context. A safe dependency project should start with metadata-only exports, classification, redaction or hashing where needed, fixture demos, read-only extraction, and explicit approval before any production report content or customer data is handled.

Frequently asked questions

What tables should an ADF and Power BI dependency database start with?

Start with artifacts, dependencies, snapshots, owners, and release gates. Artifacts describe pipelines, activities, datasets, linked services, triggers, SQL objects, semantic models, reports, dashboards, schedules, and workspaces. Dependencies describe source-target relationships, confidence, extraction method, and review state.

Do we need a graph database for reporting dependency analysis?

Not at first. A relational schema or CSV export can prove the artifact and edge model. A graph database becomes useful when teams need deeper path queries, multi-hop traversal, or interactive lineage views at larger scale.

Can this be built without production report data?

Yes. The first version can use metadata-only exports, redacted names, hashes, synthetic fixture reports, and read-only snapshots. Production report contents, credentials, customer records, and private metrics should stay out of discovery until access and handling rules are explicit.