Cross-Schema Dependency Analysis Prompt

This prompt is built for data platform engineers and backend teams who own migrations that span multiple schemas, databases, or services. The job-to-be-done is producing a structured cross-schema impact map before a migration reaches production—identifying every foreign reference, materialized view dependency, ETL pipeline breakage risk, and cross-database query that could fail when the schema changes. You should use this prompt when a migration script touches objects referenced outside its own schema boundary, when you lack a single automated dependency graph, or when you need a human-auditable impact assessment to attach to a change request.

The prompt requires specific input context to be useful: the full migration DDL, the current schema definitions for all affected and potentially dependent schemas, and a list of known ETL pipelines, materialized views, or cross-database queries that reference the target objects. Without this context, the model will hallucinate dependencies. The output is a structured impact map with dependency type, affected object, failure mode, and severity classification. This is not a prompt for simple single-schema migrations, for environments where a complete automated dependency graph already exists and is trusted, or for migrations where you cannot provide the cross-schema metadata the analysis requires.

Do not use this prompt as a replacement for actual migration dry-run execution or transaction safety review. It identifies dependency risks but does not simulate runtime behavior, lock contention, or data correctness. The output should feed into your migration review checklist alongside rollback safety analysis, data profiling results, and dry-run logs. For high-risk migrations affecting financial data, PII, or compliance-bound systems, always pair this analysis with human review and a rollback plan before execution.

This prompt template is designed to be the core instruction set you send to a model. It accepts a proposed migration script, the current state of all related schemas, and a set of constraints to produce a structured, risk-ranked dependency impact map. The template uses square-bracket placeholders for all dynamic inputs, making it straightforward to parameterize in your application code. The goal is to surface hidden breakages—such as a renamed column that orphans a materialized view in another schema or a dropped table that silently breaks an ETL pipeline—before the migration reaches production.

textCopy
You are a database reliability engineer reviewing a migration script for cross-schema impact. Your task is to produce a structured dependency impact map.

## INPUTS

### PROPOSED MIGRATION
[MIGRATION_SCRIPT]

### CURRENT SCHEMA CONTEXT
This includes DDL for all related schemas, materialized views, stored procedures, and known ETL pipeline definitions.
[SCHEMA_CONTEXT]

### KNOWN DEPENDENCIES
A list of known cross-schema references, foreign keys, views, and ETL jobs that touch the affected tables.
[KNOWN_DEPENDENCIES]

## CONSTRAINTS
[CONSTRAINTS]

## OUTPUT SCHEMA
You must respond with a single JSON object conforming to this structure:
{
  "migration_summary": "string (one-sentence summary of what the migration does)",
  "impact_map": [
    {
      "target_object": "string (fully qualified schema.object name)",
      "dependency_type": "string (e.g., FOREIGN_KEY, MATERIALIZED_VIEW, ETL_PIPELINE, STORED_PROCEDURE, CROSS_DATABASE_QUERY)",
      "impact_description": "string (explain exactly how the migration breaks or alters this dependency)",
      "risk_level": "string (HIGH | MEDIUM | LOW)",
      "fix_suggestion": "string (a concrete, actionable recommendation to resolve the issue)"
    }
  ],
  "rollback_risks": ["string (list of risks specific to rolling back this migration)"],
  "unresolved_questions": ["string (list of questions that must be answered before proceeding, e.g., unclear ownership of a dependent object)"]
}

## INSTRUCTIONS
1. Parse the [MIGRATION_SCRIPT] to identify all DDL and DML operations.
2. For each operation, cross-reference the [SCHEMA_CONTEXT] and [KNOWN_DEPENDENCIES] to find every dependent object.
3. Do not report dependencies that are unaffected by the change.
4. If a dependency is mentioned in [KNOWN_DEPENDENCIES] but cannot be verified in [SCHEMA_CONTEXT], flag it as an unresolved question.
5. Adhere strictly to the [CONSTRAINTS] provided.
6. Output only the JSON object. No markdown, no preamble.

To adapt this template, replace each bracketed placeholder with data from your own systems. [MIGRATION_SCRIPT] should be the raw SQL or ORM-generated diff. [SCHEMA_CONTEXT] is the most critical input—it must include the full DDL for every schema that could be affected, not just the one being modified. For large environments, you may need to use a retrieval-augmented generation (RAG) approach to pull in only the relevant schema objects. [KNOWN_DEPENDENCIES] can be sourced from your data catalog, ETL scheduler, or a manual list maintained by the team. Use [CONSTRAINTS] to enforce team-specific policies, such as "ignore dependencies on schemas owned by team X" or "treat any impact on a materialized view as HIGH risk." The output schema is rigid by design; parse the JSON response in your harness and validate it against this structure before presenting results to the user.

This prompt is designed to be the reasoning engine inside a broader application harness, not a standalone chat interface. The core workflow involves: (1) collecting schema metadata from all relevant databases, (2) assembling the migration script and its context, (3) executing the prompt, (4) validating the structured output, and (5) routing the impact map to the appropriate reviewers or blocking the migration pipeline. The harness is responsible for data gathering and output enforcement; the prompt is responsible for analysis and reasoning.

Input Assembly: The harness must gather the full DDL for all schemas referenced in the migration, including views, materialized views, triggers, and stored procedures. This is non-negotiable; the prompt cannot analyze dependencies it cannot see. Use pg_dump --schema-only or equivalent introspection tools. The [SOURCE_SCHEMA_DDL] and [TARGET_SCHEMA_DDL] placeholders should be populated with the complete, unredacted schema definitions. The [MIGRATION_SCRIPT] placeholder receives the exact SQL to be reviewed. For ETL and cross-database dependencies, the harness should also include a [CROSS_DB_ACCESS_MAP] documenting foreign data wrappers, dblink usage, or application-level connection strings that reference other databases.

Model Selection and Tool Use: This task requires a model with strong SQL comprehension and the ability to reason about implicit dependencies (e.g., a view referencing a table in another schema via a qualified name). A frontier model like GPT-4o or Claude 3.5 Sonnet is recommended. The prompt does not require tool use for the analysis itself, but the harness should provide a schema introspection tool that the model can call if it identifies a missing dependency not included in the initial DDL. The tool should accept a schema and object name and return its DDL, allowing the model to request additional context on demand.

Output Validation and Retries: The prompt requests a strict JSON schema. The harness must validate the response against this schema immediately. If validation fails, implement a single retry by feeding the validation error back into the prompt as [PREVIOUS_OUTPUT_ERROR]. If the retry also fails, log the raw output, flag the review as incomplete, and escalate to a human. Do not loop indefinitely. For high-risk migrations (e.g., those involving financial data or PII), the harness should automatically set [RISK_LEVEL] to high, which instructs the prompt to be more conservative and flag borderline cases as requires_review.

Integration and Logging: Wire the harness into your CI/CD pipeline as a required check on migration PRs. Log the full prompt, the model response, the validated JSON, and the final pass/fail decision to your prompt observability platform. This audit trail is critical for post-incident review. The impact_map output should be parsed and used to automatically request reviews from the owners of affected downstream systems. A finding with severity: blocker should fail the CI check. The next step is to pair this prompt with a Migration Dry-Run Simulation Prompt that tests the script against a production-like schema, using this dependency map to ensure all affected objects are included in the simulation scope.