Use data lineage to optimize costs

In this scenario, you manage a BigQuery project with datasets derived from the Quarterly Census of Employment and Wages (QCEW) . The project contains numerous tables and views for various dashboards and analyses, such as tracking employment trends or wage comparisons. As a result, your storage costs increase. To reduce costs, you identify tables or views that are not actively used as sources for jobs or assets within BigQuery, making them candidates for deletion.

Use data lineage to identify assets that are not being used as a source by any other BigQuery jobs. By checking each asset for downstream links, systematically flag unused resources for deletion to reduce storage costs.

Get started

To complete the use case, first set up the environment and run the data transformations. Use the prerequisites and setup page to connect a remote repository to Dataform. This repository contains the code necessary to set up the dataset and transform the data.

After you finish the environment setup, use BigQuery and Lineage Explorerto visually identify assets without downstream dependencies.

Identify unused assets with Lineage Explorer

After the dataset is prepared, identify assets that lack downstream dependencies. While you can do this visually for individual tables, you can also use a programmatic approach for larger datasets.

In this example, compare active assets with standalone assets to determine which can be safely removed:

1. In Google Cloud console, go to the BigQuerypage.
2. Use the search field to find a standalone table (for example, an old analysis table from a specific year).
3. Click the Lineagetab.
4. In the Lineage Explorerpane, do the following:
   1. In the Directionsection, select the Downstreamdirection.
   2. Click Apply.

Observe the graph. If there are no downstream nodes, the asset is not being used as a source for other tracked BigQuery jobs. For more information on visualizing data with data lineage graph, see Lineage graph view .