{
  "affected_assets": [
    {
      "critical": false,
      "hop": 1,
      "name": "fct_order_sales",
      "owners": [
        "commerce-analytics@astervale.demo"
      ]
    },
    {
      "critical": true,
      "hop": 2,
      "name": "loyalty_customer_value",
      "owners": [
        "loyalty-platform@astervale.demo"
      ]
    },
    {
      "critical": false,
      "hop": 2,
      "name": "regional_returns",
      "owners": [
        "store-operations@astervale.demo"
      ]
    },
    {
      "critical": true,
      "hop": 3,
      "name": "executive_revenue_dashboard",
      "owners": [
        "finance-data@astervale.demo"
      ]
    }
  ],
  "change": {
    "column": "customer_id",
    "current_type": "varchar",
    "proposed_type": "bigint",
    "source": "stg_orders.customer_id"
  },
  "company": "AsterVale Living",
  "evidence": {
    "confidence": "HIGH",
    "datahub_assets": 4,
    "hidden_sql_objects": 4,
    "regions": 5,
    "source": "Bundled AsterVale Living DataHub metadata"
  },
  "regional_exposure": [
    {
      "asset_names": [
        "fct_order_sales",
        "loyalty_customer_value",
        "executive_revenue_dashboard"
      ],
      "owners": [
        "commerce-analytics@astervale.demo",
        "loyalty-platform@astervale.demo",
        "finance-data@astervale.demo"
      ],
      "policy_flags": [
        "CUSTOMER_DATA",
        "CA_PRIVACY_REVIEW",
        "EXECUTIVE_REPORTING"
      ],
      "region": "NORTHEAST",
      "risk": "HIGH",
      "sql_objects": [
        "usp_reconcile_loyalty_customer",
        "vw_customer_revenue_bridge"
      ]
    },
    {
      "asset_names": [
        "fct_order_sales",
        "regional_returns",
        "executive_revenue_dashboard"
      ],
      "owners": [
        "commerce-analytics@astervale.demo",
        "store-operations@astervale.demo",
        "finance-data@astervale.demo"
      ],
      "policy_flags": [
        "EXECUTIVE_REPORTING"
      ],
      "region": "SOUTH",
      "risk": "MEDIUM",
      "sql_objects": [
        "usp_match_regional_returns",
        "vw_customer_revenue_bridge"
      ]
    },
    {
      "asset_names": [
        "fct_order_sales",
        "regional_returns",
        "executive_revenue_dashboard"
      ],
      "owners": [
        "commerce-analytics@astervale.demo",
        "store-operations@astervale.demo",
        "finance-data@astervale.demo"
      ],
      "policy_flags": [
        "EXECUTIVE_REPORTING"
      ],
      "region": "MIDWEST",
      "risk": "MEDIUM",
      "sql_objects": [
        "usp_match_regional_returns",
        "vw_customer_revenue_bridge"
      ]
    },
    {
      "asset_names": [
        "fct_order_sales",
        "loyalty_customer_value",
        "regional_returns",
        "executive_revenue_dashboard"
      ],
      "owners": [
        "commerce-analytics@astervale.demo",
        "loyalty-platform@astervale.demo",
        "store-operations@astervale.demo",
        "finance-data@astervale.demo"
      ],
      "policy_flags": [
        "CUSTOMER_DATA",
        "CA_PRIVACY_REVIEW",
        "EXECUTIVE_REPORTING"
      ],
      "region": "WEST",
      "risk": "HIGH",
      "sql_objects": [
        "usp_reconcile_loyalty_customer",
        "usp_match_regional_returns",
        "vw_customer_revenue_bridge"
      ]
    },
    {
      "asset_names": [],
      "owners": [],
      "policy_flags": [
        "REGION_METADATA_MISSING"
      ],
      "region": "UNKNOWN",
      "risk": "REVIEW",
      "sql_objects": [
        "usp_export_customer_segments"
      ]
    }
  ],
  "sql_dependencies": [
    {
      "confidence": "HIGH",
      "manual_review_reason": null,
      "match_kind": "convert",
      "object_name": "usp_reconcile_loyalty_customer",
      "object_type": "SQL_STORED_PROCEDURE",
      "proposed_sql": "SELECT customer_id FROM loyalty.member_snapshot WHERE TRY_CONVERT(BIGINT, customer_id) = @customer_id;",
      "regions": [
        "NORTHEAST",
        "WEST"
      ],
      "schema_name": "loyalty",
      "snippet": "SELECT customer_id FROM loyalty.member_snapshot WHERE TRY_CONVERT(INT, customer_id) = @customer_id;"
    },
    {
      "confidence": "MEDIUM",
      "manual_review_reason": "Verify both join operands are BIGINT before changing the source.",
      "match_kind": "join",
      "object_name": "usp_match_regional_returns",
      "object_type": "SQL_STORED_PROCEDURE",
      "proposed_sql": null,
      "regions": [
        "SOUTH",
        "MIDWEST",
        "WEST"
      ],
      "schema_name": "operations",
      "snippet": "SELECT r.return_id FROM operations.returns r JOIN commerce.orders o ON r.customer_id = o.customer_id;"
    },
    {
      "confidence": "HIGH",
      "manual_review_reason": null,
      "match_kind": "cast",
      "object_name": "vw_customer_revenue_bridge",
      "object_type": "VIEW",
      "proposed_sql": "SELECT TRY_CAST(customer_id AS BIGINT) AS customer_id, net_revenue FROM finance.revenue_daily;",
      "regions": [
        "NORTHEAST",
        "SOUTH",
        "MIDWEST",
        "WEST"
      ],
      "schema_name": "finance",
      "snippet": "SELECT CAST(customer_id AS INT) AS customer_id, net_revenue FROM finance.revenue_daily;"
    },
    {
      "confidence": "LOW",
      "manual_review_reason": "Contains dynamic SQL, so the referenced object and runtime type need manual review.",
      "match_kind": "dynamic_sql",
      "object_name": "usp_export_customer_segments",
      "object_type": "SQL_STORED_PROCEDURE",
      "proposed_sql": null,
      "regions": [],
      "schema_name": "exports",
      "snippet": "DECLARE @sql nvarchar(max) = N'SELECT customer_id FROM ' + @table; EXEC sys.sp_executesql @sql;"
    }
  ],
  "unresolved_risks": [
    "Hidden consumers outside the observed lineage may still depend on `customer_id`.",
    "Critical downstream assets require coordinated rollout: executive_revenue_dashboard, loyalty_customer_value.",
    "Verify both join operands are BIGINT before changing the source.",
    "Contains dynamic SQL, so the referenced object and runtime type need manual review."
  ]
}