Using cs_tools searchable, how can we see the dependancies between a table column and its associated worksheet column name?

[bmofkn]

We've used the awesome cs_tools kit to export all metadata to SQLite via cs_tools tools searchable metadata --config prod_ts --syncer sqlite://syncerDefinition.toml.

This creates several SQLite tables including:

• ts_metadata_object
• ts_metadata_column
• ts_dependent_object

It's likely right in front of us... but we cannot figure out how to show the relationship between an original table.column and its friendly worksheet.column.

Using ts_dependent_object, we can filter by the ts_metadata_column.column_id, but this only lists the tables and worksheets the column belongs to (it doesn't explicitly list a column as being dependent on another column). The TML for the worksheet indicates the source for the friendly worksheet column name... how do we find this dependency between the table.column and worksheet.column using cs_tools and SQLite?

[boonhapus]

Something like this will get you close @bmofkn I added comments to the SQL query because TS autogens the aliases.

SELECT 
  "ta_1"."NAME" "ca_1",         // UpstreamTableOrView
  "ta_2"."COLUMN_NAME" "ca_2",  // UpstreamTableOrViewColumn
  "ta_3"."NAME" "ca_3"          // Worksheet
FROM "GTM_DB"."CS_TOOLS_V150"."TS_DEPENDENT_OBJECT" "ta_3"
  INNER JOIN "GTM_DB"."CS_TOOLS_V150"."TS_METADATA_COLUMN" "ta_2"
    ON (
      "ta_3"."COLUMN_GUID" = "ta_2"."COLUMN_GUID"
      AND "ta_3"."CLUSTER_GUID" = "ta_2"."CLUSTER_GUID"
    )
  RIGHT OUTER JOIN "GTM_DB"."CS_TOOLS_V150"."TS_METADATA_OBJECT" "ta_1"
    ON (
      "ta_2"."CLUSTER_GUID" = "ta_1"."CLUSTER_GUID"
      AND "ta_2"."OBJECT_GUID" = "ta_1"."OBJECT_GUID"
    )
WHERE (
  LOWER("ta_3"."OBJECT_TYPE") = 'logical_table'                         // ta_3 = TS_DEPENDENT_OBJECT
  AND LOWER("ta_3"."OBJECT_SUBTYPE") = 'worksheet'                      // ta_3 = TS_DEPENDENT_OBJECT
  AND LOWER("ta_3"."NAME") = 'cs tools - worksheet column utilization'  // ta_3 = TS_DEPENDENT_OBJECT
  AND "ta_2"."HIDDEN" = FALSE                                           // ta_3 = TS_METADATA_COLUMN
)
GROUP BY 
  "ca_1", 
  "ca_2", 
  "ca_3"
ORDER BY "ca_1" ASC NULLS LAST

The object hierarchy in the model looks like this Object <- Columns <- Dependents. In order to see Worksheet columns, you'd probably want a subquery that first fetches Object + Columns FILTER BY Object.subtype = 'WORKSHEET' and then INNER JOIN or WHERE sq.object_guid IN ( ... ) that to a similar result set as above query.

[boonhapus]

Aside: what exactly are you trying to do here? 😄

[bmofkn]

Thanks @boonhapus, I'll try to tweak the SQL to work with SQLite (which doesn't appear to like "RIGHT" outer joins).

Here's what we are after:

• we know a column in a table is consumed by dozens of worksheets, which are also consumed by dozens of answers and liveboards
• We'd like to find every place a table.column was consumed... not just the worksheet it was consumed in, but the column within the worksheet.
• we are wanting to see how the original column name has been renamed by either an internal user or by one of our TSE customers when used in a worksheet, answer or liveboard
• this will help us with a project to rename the columns in the "master" worksheets that may be a bit more intuitive for our customers when selecting a column in an answer.

Example:

• original table.column = ext_ca_shipment_date
• one worksheet consumes the above as "Shipment Release Date (Canada)"
• another answer has consumed the above and renamed to "Canada Release Date"
• ... and the list goes on

We want to see all of the variations of a column name against it's original source column name

[boonhapus]

This makes sense @bmofkn ! The data model (really, the ThoughtSpot Dependency APIs) does not show direct lineage, instead shows "for a given column, what is the downstream lineage?" so your example would be..

object JOIN column LEFT JOIN dependents

Dependents table would list the Worksheet, then also Answer/Liveboards that are used by that column as well.

The reason why we don't show direct lineage..

is because an object graph may be simple, but it also may be arbitrarily complex.

[ LIVEBOARD ]   Q   { R, S, T, U }
                |   |
[ WORKSHEET ]   |   X
                |   | \
     [ VIEW ]   |   C  |
                | / |  |
[ WORKSHEET ]   A   |  |
                |   |  |
     [ VIEW ]   |   B  |
                | /    |
    [ TABLE ]   H      J

... both graphs here starting with H and J are valid, but you can see that sometimes the View layer is intermediate and can be built from Tables, Worksheets, or even other Views themselves.

For your purposes, simply relating OBJECT <--INNER-- COLUMN <--LO-- DEPENDENTS WHERE OBJECT.NAME = ... AND OBJECT.SUBTYPE = ONE_TO_ONE_LOGICAL should be enough to yield a dataset that can help you with this purpose. Sprinkle in some CTEs if you want to get even more specific 😄

[bmofkn]

Thanks @boonhapus, appreciate it. And I appreciate the complexities of the graph.

After thinking this through, at a much simpler level, I'm hoping to see in SQLite what I can see in the TML...

• for any given worksheet column (i.e. name: Shipment ID), here is the immediate parent data source (i.e. column_id: shipment_wh_table_1::shipment_id).

Perhaps in a future release, what's referred to as column_id in the worksheet TML can be added to the ts_metadata_column table as a new column? It would make it easy to reference the immediate parent/data_source of the worksheet/table column, where that column was renamed 🙂

[boonhapus]

It's an interesting use case @bmofkn . I think the most repeatable piece I see in it is the "answer columns" part. As users can control the name of columns in charts themselves, it would help you to see if their preferences in naming the column diverge from the "golden source" which is the worksheet.

Currently we dont track AnswerColumns (this is literally how it's named in Answer TML), but this could be a really interesting new table, potentially an XREF between TS_METADATA_OBJECT and TS_METADATA_COLUMN.

I often find with customers that the table layer acts as a global standard, but that may be customized slightly in the worksheet layer. Both represent choices we make on behalf of our user community, but ultimately they will tell us what they want via column names in visualizations (or viz-level formulas).

Names are sometimes highly preferential, so I'd probably recommend keeping the name as-is (unless there's an overwhelming consensus) and then adding a number of Synonyms for the alternative verbiage.

Now that I really understand what you want, I'll try to write a query that helps you identify the renames from Table->Worksheet.

[boonhapus]

@bmofkn OK, so after playing with this for a bit, I've determined we don't enough data in Searchable to answer this question today. 😢

The dotted arrow in the diagram is our missing link. Today, we've got OBJECT + COLUMNS and those COLUMNs' + DEPENDENTS.

We can write a query that accurately shows this information.

// OBJECTIVE: See column name drift from Table -> Worksheet.
//
SELECT
      o.name         AS table_name
    , oc.column_name AS table_column
    , oc.column_guid AS upstream_column_guid
    , '..tbd'        AS downstream_column_guid
    , '..tbd'        AS worksheet_column
    , d.name         AS worksheet_name
FROM
    TS_METADATA_OBJECT  AS o
JOIN
    TS_METADATA_COLUMN  AS oc ON oc.object_guid = o.object_guid
JOIN
    TS_DEPENDENT_OBJECT AS d  ON d.column_guid = oc.column_guid
WHERE
    // PARENT: Table, CHILD: Worksheet
    o.object_subtype = 'ONE_TO_ONE_LOGICAL' AND d.object_subtype = 'WORKSHEET'
    
    // USE THIS AS TEST DATA
    AND o.name = 'TS_DEPENDENT_OBJECT'
    AND oc.column_name = 'Dependent Created At'

When run against my internal CS Tools SpotApp environment, it produces 2 downstream results. The column is used in both of the Worksheets under different names (Dependent Created Date in the Column Utilization worksheet, and Object Created At in the BI Server Advanced worksheet).

TABLE_NAME	TABLE_COLUMN	UPSTREAM_COLUMN_GUID	DOWNSTREAM_COLUMN_GUID	WORKSHEET_COLUMN	WORKSHEET_NAME
TS_DEPENDENT_OBJECT	Dependent Created At	a77b45cf-00a3-4866-babf-f1eb7613edf4	..tbd	..tbd	CS Tools - Worksheet Column Utilization
TS_DEPENDENT_OBJECT	Dependent Created At	a77b45cf-00a3-4866-babf-f1eb7613edf4	..tbd	..tbd	CS Tools - BI Server Advanced

Additionally, we can query for those dependent's columns as well.

// OBJECTIVE: See column name drift from Table -> Worksheet.
//
SELECT
      o.name         AS table_name
    , oc.column_name AS table_column
    , oc.column_guid AS upstream_column_guid
    , dc.column_guid AS downstream_column_guid
    , dc.column_name AS worksheet_column
    , d.name         AS worksheet_name
FROM
    TS_METADATA_OBJECT  AS o
JOIN
    TS_METADATA_COLUMN  AS oc ON oc.object_guid = o.object_guid
JOIN
    TS_DEPENDENT_OBJECT AS d  ON d.column_guid = oc.column_guid
JOIN
    TS_METADATA_COLUMN  AS dc ON dc.object_guid = d.dependent_guid
WHERE
    // PARENT: Table, CHILD: Worksheet
    o.object_subtype = 'ONE_TO_ONE_LOGICAL' AND d.object_subtype = 'WORKSHEET'
    
    // USE THIS AS TEST DATA
    AND o.name= 'TS_DEPENDENT_OBJECT'
    AND oc.column_name = 'Dependent Created At'

However this now produces an awkward result, which is effectively a CROSSJOIN from table.column to worksheet.column.

TABLE_NAME	TABLE_COLUMN	UPSTREAM_COLUMN_GUID	DOWNSTREAM_COLUMN_GUID	WORKSHEET_COLUMN	WORKSHEET_NAME
TS_DEPENDENT_OBJECT	Dependent Created At	a77b45cf-00a3-4866-babf-f1eb7613edf4	122bf719-31a4-48a4-86bd-969693a120f0	Is Additive	CS Tools - Worksheet Column Utilization
TS_DEPENDENT_OBJECT	Dependent Created At	a77b45cf-00a3-4866-babf-f1eb7613edf4	625c5642-60a1-43c0-a3ce-e4ae652c1927	(Hidden, Filtered) Column Parent is Worksheet	CS Tools - Worksheet Column Utilization
TS_DEPENDENT_OBJECT	Dependent Created At	a77b45cf-00a3-4866-babf-f1eb7613edf4	33b27c3c-c8a7-4f82-93fb-9da53c7044fd	Format Pattern	CS Tools - Worksheet Column Utilization
...
TS_DEPENDENT_OBJECT	Dependent Created At	a77b45cf-00a3-4866-babf-f1eb7613edf4	44fe0906-b458-4447-92cf-b433014b5fa6	Object GUID	CS Tools - BI Server Advanced
TS_DEPENDENT_OBJECT	Dependent Created At	a77b45cf-00a3-4866-babf-f1eb7613edf4	0b867f9b-fb5d-4bc0-9014-d91d3ade2e85	ThoughtSpot URL	CS Tools - BI Server Advanced
TS_DEPENDENT_OBJECT	Dependent Created At	a77b45cf-00a3-4866-babf-f1eb7613edf4	d2cf6e26-fe2b-44b3-a750-b2eeb0e0913d	Latency (s)	CS Tools - BI Server Advanced
...

What's really needed is a dataset that relates columns to columns, rather than object <--- column <--- object.

---

At the moment, I don't have any strong plans to build this because in order to "do it right" , I would want to relate any object with columns, to its upstream counterpart(s).

This would mean we need to consider the following.. mostly the hairy part is derived columns

• Today, we do not track the Liveboard -> Visualization lineage, which would be necessary to find the defined columns
• Columns which are part of a Visualization spec, but Not Visualized or marked as Hidden (thus still a dependent)
• Columns used implicitly as part of global Worksheet Filters
• Columns used for the purposes of Row Level Security
• Derived Columns (Formulas) - which are vulnerable to all the above
  • Formulas may be composed of "any number" of underlying columns
  • Formulas which are defined as part of the Worksheet spec, but not added as a searchable / exposed column

---

These are points I'd want to consider to help solve your problem of "I want to see users who have renamed columns downstream" which I assume your goal is a form of data governance / auditing.

I am currently prototyping a new lineage tool that would go into Searchable. This may go into the data model, or the data may be seeded at runtime, I'm still very early stages on it. At the moment it's at the object level rather than the column level, but my intent is for it to work similar to the node diagram you'd find in dbt explorer or other metadata tooling.

[bmofkn]

Thanks for the detailed update! Appreciate the deep dive and long term desire to see this come to fruition.