system.access.table_lineage - source and target table meanings
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
3 weeks ago
I've been using the system.access.table_lineage table, and I'm trying to understand when the source and target tables are defined.
For example, picking a specific job run and looking at the lineage:
I see a few scenarios:
- source_type = null and target_type =TABLE (I think this is a write to the table)
- source_type = TABLE, target_type = null (I think this is a read of a table)
- source_type = TABLE (table X), target_type = TABLE (table Y) - I don't know what this is
- source_type = VIEW, target_type = TABLE - I don't know what this is
- source_type = TABLE, target_type = VIEW (I think this is a read of the "target" view, indicating it then reads from the "source" of the table)
I looked over the documentation in
but it did not help with my understanding.
- Labels:
-
Delta Lake
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
3 weeks ago
Hi Eriodega,
1. **source_type = null and target_type = TABLE**:
- This indicates a write operation to the table. The source is null because there is no source table involved in the operation, only a target table that is being written to.
2. **source_type = TABLE, target_type = null**:
- This indicates a read operation from the table. The target is null because there is no target table involved in the operation, only a source table that is being read from.
3. **source_type = TABLE (table X), target_type = TABLE (table Y)**:
- This scenario represents a transformation or data movement operation where data is read from one table (table X) and written to another table (table Y). This could be part of an ETL (Extract, Transform, Load) process or a similar data pipeline.
4. **source_type = VIEW, target_type = TABLE**:
- This indicates that data is being read from a view and written to a table. The view acts as the source, and the table is the target where the data is being written.
5. **source_type = TABLE, target_type = VIEW**:
- This scenario suggests that the view is being created or updated based on the data read from the table. The table is the source, and the view is the target that is being defined or modified.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
a week ago
@Sidhant07thanks for the answer, I think it is good, but I am questioning scenario #5 (source=table,target=view).
I'm looking at some examples in our table_lineage, and we aren't modifying the view or creating the view from within a job. I think scenario #5 is more likely "queried a view X and the query that defines view X has queried table Y".
Or does "target" always imply some sort of write operation?
![](/skins/images/582998B45490C7019731A5B3A872C751/responsive_peak/images/icon_anonymous_message.png)
![](/skins/images/582998B45490C7019731A5B3A872C751/responsive_peak/images/icon_anonymous_message.png)