{"id":3162,"date":"2025-10-29T16:01:37","date_gmt":"2025-10-29T08:01:37","guid":{"rendered":"https:\/\/www.dpriver.com\/blog\/2025\/10\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/"},"modified":"2025-10-29T17:08:45","modified_gmt":"2025-10-29T09:08:45","slug":"tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/","title":{"rendered":"Tracking Column Lineage in BigQuery Views: A Practical Guide for the SELECT * Challenge"},"content":{"rendered":"
\n

Data professionals often face a common challenge in BigQuery and other SQL databases: tracking the journey of a specific column through multiple layers of views, especially when those views are created with SELECT *<\/code>. While SELECT *<\/code> is convenient during development, it can obscure dependencies and make impact analysis a daunting task.<\/p>\n

The Problem: “Where is This Column Used?”<\/h2>\n

Imagine this scenario, which is a common question on platforms like Reddit and Stack Overflow:<\/p>\n

You have a base table in BigQuery, dataset.table1<\/code>, with two columns, col1<\/code> and col2<\/code>. You then create a view, dataset.view1<\/code>, using a simple select * from dataset.table1<\/code>. To make things more interesting, you create another view, dataset.view2<\/code>, based on the first one: select * from dataset.view1<\/code>.<\/p>\n

Now, the critical question arises: How can you programmatically determine that table1.col1<\/code> is used in view2<\/code>?<\/strong><\/p>\n

BigQuery’s INFORMATION_SCHEMA<\/code> can help if columns are explicitly named in the view’s DDL, but it falls short when SELECT *<\/code> is used. This leaves you with the difficult and error-prone task of manual inspection.<\/p>\n

A Concrete Example<\/h3>\n

Here is the SQL code that illustrates this exact problem:<\/p>\n

create table dataset.table1(\n col1 int,\n col2 char\n);\n\ncreate view dataset.view1 as select * from dataset.table1;\ncreate view dataset.view2 as select * from dataset.view1;<\/code><\/pre>\n
How can we trace col1<\/code> and col2<\/code> from their origin in table1<\/code> all the way to view2<\/code>?<\/p>\n
The Solution: Automated Column-Level Data Lineage<\/h2>\n
The most reliable and efficient way to solve this is with an automated data lineage tool. These tools parse your SQL code, understand the dependencies created by statements like CREATE VIEW<\/code>, and map the flow of data from source to destination, right down to the column level\u2014even through complex SELECT *<\/code> statements.<\/p>\n
Here are three easy ways to get this done.<\/p>\n
Method 1: Use a No-Code Web Application<\/h3>\n
For a quick and visual answer, you can use an online tool like SQLFlow<\/a>. You can simply paste your SQL code into the web interface and get an instant, interactive diagram and report of the data lineage. This is perfect for quick, one-off analyses.<\/p>\n
\"image\"<\/p>\n
Method 2: Use a VS Code Extension<\/h3>\n
If you prefer to work within your development environment, you can use a free VS Code extension like Gudu SQL Omni<\/a>. This brings the power of data lineage directly into your editor. You can analyze your SQL files and visualize the column-level relationships without ever leaving your IDE, streamlining your workflow.<\/p>\n
\"image\"<\/p>\n
Method 3: Use a REST API for Automation<\/h3>\n
For programmatic access, automation, or integration into a larger data governance framework, a REST API is the ideal solution. You can submit your SQL code via a simple curl<\/code> command and receive the detailed, column-level lineage in a structured format like CSV.<\/p>\n
Here\u2019s how you can do it with the SQLFlow API. First, save the SQL above into a file (e.g., views.sql<\/code>), then run the following command:<\/p>\n
curl -X POST \"https:\/\/api.gudusoft.com\/gspLive_backend\/sqlflow\/generation\/sqlflow\/exportFullLineageAsCsv\" \\\n-H \"Request-Origion:testClientDemo\" \\\n-H \"Content-Type:multipart\/form-data\" \\\n-F \"sqlfile=@\/path\/to\/your\/views.sql\" \\\n-F \"dbvendor=dbvbigquery\" \\\n-F \"userId=YOUR USER ID HERE\" \\\n-F \"token=YOUR SECRET KEY\"<\/code><\/pre>\n
The API will process the SQL and return a detailed CSV report that explicitly maps the column relationships. This is the “Aha!” moment.<\/p>\n
The Result<\/h4>\n
Here is a sample of the CSV output you would receive from the API:<\/p>\n
source_db,source_schema,source_table_id,source_table,source_column_id,source_column,target_db,target_schema,target_table_id,target_table,target_column_id,target_column,relation_type,effect_type,procedure\nDEFAULT,dataset,4,table1,5,COL1,DEFAULT,dataset,12,view1,14,COL1,fdd,create_view,\nDEFAULT,dataset,4,table1,6,COL2,DEFAULT,dataset,12,view1,15,COL2,fdd,create_view,\nDEFAULT,dataset,12,view1,14,COL1,DEFAULT,dataset,21,view2,23,COL1,fdd,create_view,\nDEFAULT,dataset,12,view1,15,COL2,DEFAULT,dataset,21,view2,24,COL2,fdd,create_view,<\/code><\/pre>\n
As you can see, the output clearly shows the lineage:<\/p>\n
    \n
  • table1.COL1<\/code> flows to view1.COL1<\/code><\/li>\n
  • view1.COL1<\/code> flows to view2.COL1<\/code><\/li>\n<\/ul>\n
    This directly answers the question and provides the precise, actionable information needed for reliable impact analysis.<\/p>\n
    A Note on Other Databases<\/h2>\n
    While this article uses BigQuery as the primary example, it’s important to note that this column-level lineage challenge is universal across all SQL databases. The solutions presented here are not limited to BigQuery; they work just as effectively for other major platforms, including Snowflake, Redshift, Oracle, SQL Server, PostgreSQL, MySQL<\/strong>, and many others. The core principles of parsing SQL to trace dependencies remain the same, regardless of the database vendor.<\/p>\n
    Conclusion<\/h2>\n
    While SELECT *<\/code> can be a convenient shortcut in SQL, it doesn’t have to be a black box for data lineage. By leveraging modern data lineage tools, you can easily trace data flows through complex views, ensuring you always have a clear understanding of your data’s journey.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
    Data professionals often face a common challenge in BigQuery and other SQL databases: tracking the journey of a specific column through multiple layers of views, especially when those views are created with SELECT *. While SELECT * is convenient during development, it can obscure dependencies and make impact analysis a daunting task. The Problem: “Where is This Column Used?” Imagine this scenario, which is a common question on platforms like Reddit and Stack Overflow: You have a base table in BigQuery, dataset.table1, with two columns, col1 and col2. You then create a view, dataset.view1, using a simple select * from\u2026<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[19,8,93],"tags":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nTracking Column Lineage in BigQuery Views: A Practical Guide for the SELECT * Challenge<\/title>\n<meta name=\"description\" content=\"Tracking Column Lineage in BigQuery Views: A Practical Guide for the SELECT * Challenge\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Tracking Column Lineage in BigQuery Views: A Practical Guide for the SELECT * Challenge\" \/>\n<meta property=\"og:description\" content=\"Tracking Column Lineage in BigQuery Views: A Practical Guide for the SELECT * Challenge\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta property=\"article:published_time\" content=\"2025-10-29T08:01:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-10-29T09:08:45+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/raw.githubusercontent.com\/sqlparser\/sqlflow_public\/refs\/heads\/master\/assets\/images\/sqlflow-web-bigquery-column-lineage-view-example1.png\" \/>\n<meta name=\"author\" content=\"James\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"James\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\",\"name\":\"SQL and Data Blog\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"sameAs\":[],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"contentUrl\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"width\":251,\"height\":72,\"caption\":\"SQL and Data Blog\"},\"image\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"name\":\"SQL and Data Blog\",\"description\":\"SQL related blog for database professional\",\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/\",\"name\":\"Tracking Column Lineage in BigQuery Views: A Practical Guide for the SELECT * Challenge\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2025-10-29T08:01:37+00:00\",\"dateModified\":\"2025-10-29T09:08:45+00:00\",\"description\":\"Tracking Column Lineage in BigQuery Views: A Practical Guide for the SELECT * Challenge\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dpriver.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Tracking Column Lineage in BigQuery Views: A Practical Guide for the SELECT * Challenge\"}]},{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/\"},\"author\":{\"name\":\"James\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/7bbdbb6e79c5dd9747d08c59d5992b04\"},\"headline\":\"Tracking Column Lineage in BigQuery Views: A Practical Guide for the SELECT * Challenge\",\"datePublished\":\"2025-10-29T08:01:37+00:00\",\"dateModified\":\"2025-10-29T09:08:45+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/\"},\"wordCount\":622,\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"articleSection\":[\"best practices\",\"SQL language\",\"SQLFlow\"],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/7bbdbb6e79c5dd9747d08c59d5992b04\",\"name\":\"James\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eeddf4ca7bdafa37ab025068efdc7302?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eeddf4ca7bdafa37ab025068efdc7302?s=96&d=mm&r=g\",\"caption\":\"James\"},\"sameAs\":[\"http:\/\/www.dpriver.com\"],\"url\":\"https:\/\/www.dpriver.com\/blog\/author\/james\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Tracking Column Lineage in BigQuery Views: A Practical Guide for the SELECT * Challenge","description":"Tracking Column Lineage in BigQuery Views: A Practical Guide for the SELECT * Challenge","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/","og_locale":"en_US","og_type":"article","og_title":"Tracking Column Lineage in BigQuery Views: A Practical Guide for the SELECT * Challenge","og_description":"Tracking Column Lineage in BigQuery Views: A Practical Guide for the SELECT * Challenge","og_url":"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/","og_site_name":"SQL and Data Blog","article_published_time":"2025-10-29T08:01:37+00:00","article_modified_time":"2025-10-29T09:08:45+00:00","og_image":[{"url":"https:\/\/raw.githubusercontent.com\/sqlparser\/sqlflow_public\/refs\/heads\/master\/assets\/images\/sqlflow-web-bigquery-column-lineage-view-example1.png"}],"author":"James","twitter_card":"summary_large_image","twitter_misc":{"Written by":"James","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Organization","@id":"https:\/\/www.dpriver.com\/blog\/#organization","name":"SQL and Data Blog","url":"https:\/\/www.dpriver.com\/blog\/","sameAs":[],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","contentUrl":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","width":251,"height":72,"caption":"SQL and Data Blog"},"image":{"@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"WebSite","@id":"https:\/\/www.dpriver.com\/blog\/#website","url":"https:\/\/www.dpriver.com\/blog\/","name":"SQL and Data Blog","description":"SQL related blog for database professional","publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/","url":"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/","name":"Tracking Column Lineage in BigQuery Views: A Practical Guide for the SELECT * Challenge","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2025-10-29T08:01:37+00:00","dateModified":"2025-10-29T09:08:45+00:00","description":"Tracking Column Lineage in BigQuery Views: A Practical Guide for the SELECT * Challenge","breadcrumb":{"@id":"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dpriver.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Tracking Column Lineage in BigQuery Views: A Practical Guide for the SELECT * Challenge"}]},{"@type":"Article","@id":"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/#article","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/"},"author":{"name":"James","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/7bbdbb6e79c5dd9747d08c59d5992b04"},"headline":"Tracking Column Lineage in BigQuery Views: A Practical Guide for the SELECT * Challenge","datePublished":"2025-10-29T08:01:37+00:00","dateModified":"2025-10-29T09:08:45+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dpriver.com\/blog\/tracking-column-lineage-in-bigquery-views-a-practical-guide-for-the-select-challenge\/"},"wordCount":622,"publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"articleSection":["best practices","SQL language","SQLFlow"],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/7bbdbb6e79c5dd9747d08c59d5992b04","name":"James","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eeddf4ca7bdafa37ab025068efdc7302?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eeddf4ca7bdafa37ab025068efdc7302?s=96&d=mm&r=g","caption":"James"},"sameAs":["http:\/\/www.dpriver.com"],"url":"https:\/\/www.dpriver.com\/blog\/author\/james\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/3162"}],"collection":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/comments?post=3162"}],"version-history":[{"count":3,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/3162\/revisions"}],"predecessor-version":[{"id":3166,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/3162\/revisions\/3166"}],"wp:attachment":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/media?parent=3162"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/categories?post=3162"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/tags?post=3162"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}