Modifying a Database Table

When you make a change to a database, that change can have unintended consequences for systems that rely on its data. Modifications to schemas, keys, columns, or tables can affect downstream processes and ultimately reports in ways that are sometimes obvious and sometimes subtle. So when planning a database modification, you want to be confident that you understand the downstream impact of your changes so that you can modify other systems if needed to ensure your business users continue to have accurate and reliable data on which to base business decisions.

Spectrumâ„¢ Technology Platform Metadata Insights provides a visual representation of the flow of data throughout your flows, databases, and files. You can select the database you are modifying and see the flows, databases and files that the use the data. With this information you can determine the impact of database modifications. For more information, see Viewing Lineage and Impact Analysis.

  1. In a web browser, go to:

    http://server:port/metadata-insights

    Where server is the server name or IP address of your Spectrumâ„¢ Technology Platform server and port is the HTTP port. By default, the HTTP port is 8080.

  2. Click the Select Entity button .
  3. Click Database Tables.
  4. Select the table that you are considering modifying then click OK.

The resulting diagram shows the connection that contains the table. To the left of the connection (the table's lineage) is any entity that writes data to the table. To the right of the table are any entities that use data from the table, such as flows.

Example

You are working on a project to standardize column names across several databases. You have found a column the table Customer_Data that you want to rename. Before modifying the column name, you want to find out which entities are using data from this table so that you can analyze them to find out if they must be updated to accommodate the new column name.

To accomplish this, you open Metadata Insights, go to Lineage & Impact Analysis, and open the entity browser. You click Connections, then the name of the connection for the database containing the table. Then you select the table and click OK. In the resulting diagram, you expand the connection icon containing the table, which shows you which flows are connected to the table:

The diagram reveals that the HouseholdRelationships flow writes data to the Customer_Data table, and also reads data from it. In addition, the Promotions flow reads data from the Customer_Data table. As a result of reviewing this diagram, you can open the HouseholdRelationships and Promotions flows in Enterprise Designer to find out if there are any changes you need to make to accommodate the renaming of columns in the Customer_Data table.