Tracking Datasets connected in other DataFlows

larryjacobs · 2023-06-23T22:26:53+00:00

There was an error rendering this rich post.

Is there a way to track if a dataset is connected to another data flow? Developing a card right now that looks at if a dataset has zero cards in order to see if we need to keep the dataset in domo. If a dataset has zero cards but is connected to another dataset via dataflow, I will want to keep it in domo, otherwise I want to get rid of the dataset.

Find more posts tagged with

Accepted answers

MarkSnodgrass

@larryjacobs Here's a screenshot of a dataflow that I use to find unused datasets.

Here's the JSON for it, which you can copy and paste into an empty ETL and then connect your Domo Governance Datasets at each input spot and then see which fields I am using for each. Hopefully, this will help you know which field to use in which spot.

{"contentType":"domo/dataflow-actions","data":[{"name":"Governance Cards and Datasets","id":"33894b88-f1db-4c12-b219-5fc61214ed00","type":"LoadFromVault","gui":{"x":336,"y":396,"color":3238043,"colorSource":null,"sampleJson":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"c8948098-4d4e-44ea-98b9-1ec27e87201f","executeFlowWhenUpdated":true,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Governance Dataflow Details","id":"65fbdb57-a671-44fb-96b9-c47ca68e1a8d","type":"LoadFromVault","gui":{"x":60,"y":84,"color":3701798,"colorSource":null,"sampleJson":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"3f42202b-a267-4529-a759-d4bee36bf425","executeFlowWhenUpdated":true,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Governance Datasets","id":"9932cdf4-77ee-46f7-b75a-f6934c295c99","type":"LoadFromVault","gui":{"x":420,"y":264,"color":10827556,"colorSource":null,"sampleJson":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"45936f76-96e7-41d9-bc14-c4ea8342b113","executeFlowWhenUpdated":true,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Add Formula 1","id":"54019408-04fb-4e51-af88-4c7491434811","type":"ExpressionEvaluator","gui":{"x":516,"y":264,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["9932cdf4-77ee-46f7-b75a-f6934c295c99"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"LOWER(Dataset ID)","fieldName":"Dataset ID","settings":null}]},{"name":"Get dataset list","id":"ca1e9b38-29e0-4292-80d1-32e3c7640cb8","type":"SelectValues","gui":{"x":624,"y":264,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["54019408-04fb-4e51-af88-4c7491434811"],"removeByDefault":false,"notes":[],"fields":[{"name":"Dataset ID","rename":null},{"name":"Name","rename":"Dataset Name"},{"name":"Description","rename":null},{"name":"Owner Name","rename":null},{"name":"DataSet Last Updated Date/Time","rename":null}]},{"name":"Add Formula 2","id":"17ea7d42-8c59-463e-8e46-156912fcea68","type":"ExpressionEvaluator","gui":{"x":432,"y":396,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["33894b88-f1db-4c12-b219-5fc61214ed00"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"LOWER(Dataset ID)","fieldName":"Dataset ID","settings":null}]},{"name":"Select Columns","id":"f774d400-fd50-45c6-b8c9-ac83264ac6bc","type":"SelectValues","gui":{"x":540,"y":396,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["17ea7d42-8c59-463e-8e46-156912fcea68"],"removeByDefault":false,"notes":[],"fields":[{"name":"Dataset ID","rename":"Card Dataset ID"}]},{"name":"Get Distinct List of Datasets from Cards","id":"27da235a-ba24-4039-8797-703282fef344","type":"Unique","gui":{"x":648,"y":396,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["f774d400-fd50-45c6-b8c9-ac83264ac6bc"],"removeByDefault":false,"notes":[],"countRows":false,"partitionedAggregation":false,"fields":[{"name":"Card Dataset ID","caseInsensitive":false}]},{"name":"Datasets used in Cards","id":"4a82b468-03b8-46aa-acac-058581359745","type":"MergeJoin","gui":{"x":744,"y":336,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["ca1e9b38-29e0-4292-80d1-32e3c7640cb8","27da235a-ba24-4039-8797-703282fef344"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"ca1e9b38-29e0-4292-80d1-32e3c7640cb8","step2":"27da235a-ba24-4039-8797-703282fef344","keys1":["Dataset ID"],"keys2":["Card Dataset ID"],"schemaModification1":[],"schemaModification2":[],"partitioningInputId":""},{"name":"Add Formula","id":"5182b3ca-c63e-4cb2-9ba8-a86105237049","type":"ExpressionEvaluator","gui":{"x":168,"y":84,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["65fbdb57-a671-44fb-96b9-c47ca68e1a8d"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"LOWER(Input Dataset ID)","fieldName":"Input Dataset ID","settings":null},{"expression":"LOWER(Input Dataset ID)","fieldName":"Output Dataset ID","settings":null}]},{"name":"Input Datasets","id":"c67400ed-ce79-4c5a-aedb-0540453710a6","type":"Filter","gui":{"x":288,"y":48,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["5182b3ca-c63e-4cb2-9ba8-a86105237049"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Input Dataset ID","rightExpr":null,"operator":"NN","andFilterList":[]}]},{"name":"Select Columns 1","id":"6bbfa462-5f36-40c6-9937-74f95f8e2aba","type":"SelectValues","gui":{"x":396,"y":48,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["c67400ed-ce79-4c5a-aedb-0540453710a6"],"removeByDefault":false,"notes":[],"fields":[{"name":"Input Dataset ID","rename":"Dataflow Dataset ID"}]},{"name":"Output Datasets","id":"0b6606d8-6fbd-452a-b4c2-d4a827b83f5d","type":"Filter","gui":{"x":288,"y":144,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["5182b3ca-c63e-4cb2-9ba8-a86105237049"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Output Dataset ID","rightExpr":null,"operator":"NN","andFilterList":[]}]},{"name":"Select Columns 2","id":"44d46c42-4d14-48c0-ae80-5fb9a5d4bb05","type":"SelectValues","gui":{"x":396,"y":144,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["0b6606d8-6fbd-452a-b4c2-d4a827b83f5d"],"removeByDefault":false,"notes":[],"fields":[{"name":"Output Dataset ID","rename":"Dataflow Dataset ID"}]},{"name":"Append Rows","id":"d77f59d1-ba97-40fa-bf07-e12d3a173345","type":"UnionAll","gui":{"x":516,"y":96,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["6bbfa462-5f36-40c6-9937-74f95f8e2aba","44d46c42-4d14-48c0-ae80-5fb9a5d4bb05"],"removeByDefault":false,"notes":[],"unionType":"INCLUDE_FROM","schemaSource":"6bbfa462-5f36-40c6-9937-74f95f8e2aba","strict":false},{"name":"Get Distinct List of Dataset IDs from Dataflows","id":"ed20cf4d-77fe-438f-b4d2-089ec24f1dac","type":"Unique","gui":{"x":624,"y":96,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["d77f59d1-ba97-40fa-bf07-e12d3a173345"],"removeByDefault":false,"notes":[],"countRows":false,"partitionedAggregation":false,"fields":[{"name":"Dataflow Dataset ID","caseInsensitive":true}]},{"name":"Datasets used in Dataflows","id":"f3770b41-7fd6-457b-bece-83643a7e16e8","type":"MergeJoin","gui":{"x":744,"y":192,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["ca1e9b38-29e0-4292-80d1-32e3c7640cb8","ed20cf4d-77fe-438f-b4d2-089ec24f1dac"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"ca1e9b38-29e0-4292-80d1-32e3c7640cb8","step2":"ed20cf4d-77fe-438f-b4d2-089ec24f1dac","keys1":["Dataset ID"],"keys2":["Dataflow Dataset ID"],"schemaModification1":[],"schemaModification2":[],"partitioningInputId":""},{"name":"Append Rows 1","id":"be88fa60-c968-4d83-b730-986c5a9bcf62","type":"UnionAll","gui":{"x":900,"y":264,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["f3770b41-7fd6-457b-bece-83643a7e16e8","4a82b468-03b8-46aa-acac-058581359745"],"removeByDefault":false,"notes":[],"unionType":"INCLUDE_MATCHES","schemaSource":"f3770b41-7fd6-457b-bece-83643a7e16e8","strict":false},{"name":"Join Data","id":"c471c3f1-19a8-476c-be71-2388ab2301d1","type":"MergeJoin","gui":{"x":1020,"y":288,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["ca1e9b38-29e0-4292-80d1-32e3c7640cb8","be88fa60-c968-4d83-b730-986c5a9bcf62"],"removeByDefault":false,"notes":[],"joinType":"LEFT OUTER","step1":"ca1e9b38-29e0-4292-80d1-32e3c7640cb8","step2":"be88fa60-c968-4d83-b730-986c5a9bcf62","keys1":["Dataset ID"],"keys2":["Dataset ID"],"schemaModification1":[],"schemaModification2":[{"name":"Dataset ID","rename":"Used Dataset ID","remove":false},{"name":"Dataset Name","rename":"Append Rows 1.Dataset Name","remove":true},{"name":"Description","rename":"Append Rows 1.Description","remove":true},{"name":"Owner Name","rename":"Append Rows 1.Owner Name","remove":true},{"name":"DataSet Last Updated Date/Time","rename":"Append Rows 1.DataSet Last Updated Date/Time","remove":true}],"partitioningInputId":""},{"name":"Datasets Not Used","id":"da93d898-e6cc-4123-bf8f-8d08ef34d5f4","type":"Filter","gui":{"x":1128,"y":288,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["c471c3f1-19a8-476c-be71-2388ab2301d1"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Used Dataset ID","rightExpr":null,"operator":"NL","andFilterList":[]}]},{"name":"Unused Datasets","id":"5069208b-aaf1-4006-9a6b-2accdb850590","type":"PublishToVault","gui":{"x":1260,"y":288,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["da93d898-e6cc-4123-bf8f-8d08ef34d5f4"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"7963e9b8-4642-4e9d-a17b-2b70b64fafdc","type":"DataFlow","name":"Unused Datasets","description":"Uses Domo Governance Datasets to identify unused datasets","cloudId":"domo"},"versionChainType":"REPLACE","partitionIdColumns":[],"upsertColumns":[],"retainPartitionExpression":""}]}

All comments

MarkSnodgrass

Yes, you can do this with some of the dataset that are available in the Domo Governance connector. Specifically, you would want the Dataflow Details dataset and the Datasets Dataset. You can then build an ETL to let you know which ones are used and which ones aren't.

larryjacobs

Hi Mark.

Which column in the output would show that the dataset is connected to a dataflow? Would it be based on the number of inputs column?

MarkSnodgrass

@larryjacobs Here's a screenshot of a dataflow that I use to find unused datasets.

{"contentType":"domo/dataflow-actions","data":[{"name":"Governance Cards and Datasets","id":"33894b88-f1db-4c12-b219-5fc61214ed00","type":"LoadFromVault","gui":{"x":336,"y":396,"color":3238043,"colorSource":null,"sampleJson":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"c8948098-4d4e-44ea-98b9-1ec27e87201f","executeFlowWhenUpdated":true,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Governance Dataflow Details","id":"65fbdb57-a671-44fb-96b9-c47ca68e1a8d","type":"LoadFromVault","gui":{"x":60,"y":84,"color":3701798,"colorSource":null,"sampleJson":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"3f42202b-a267-4529-a759-d4bee36bf425","executeFlowWhenUpdated":true,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Governance Datasets","id":"9932cdf4-77ee-46f7-b75a-f6934c295c99","type":"LoadFromVault","gui":{"x":420,"y":264,"color":10827556,"colorSource":null,"sampleJson":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"45936f76-96e7-41d9-bc14-c4ea8342b113","executeFlowWhenUpdated":true,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Add Formula 1","id":"54019408-04fb-4e51-af88-4c7491434811","type":"ExpressionEvaluator","gui":{"x":516,"y":264,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["9932cdf4-77ee-46f7-b75a-f6934c295c99"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"LOWER(Dataset ID)","fieldName":"Dataset ID","settings":null}]},{"name":"Get dataset list","id":"ca1e9b38-29e0-4292-80d1-32e3c7640cb8","type":"SelectValues","gui":{"x":624,"y":264,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["54019408-04fb-4e51-af88-4c7491434811"],"removeByDefault":false,"notes":[],"fields":[{"name":"Dataset ID","rename":null},{"name":"Name","rename":"Dataset Name"},{"name":"Description","rename":null},{"name":"Owner Name","rename":null},{"name":"DataSet Last Updated Date/Time","rename":null}]},{"name":"Add Formula 2","id":"17ea7d42-8c59-463e-8e46-156912fcea68","type":"ExpressionEvaluator","gui":{"x":432,"y":396,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["33894b88-f1db-4c12-b219-5fc61214ed00"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"LOWER(Dataset ID)","fieldName":"Dataset ID","settings":null}]},{"name":"Select Columns","id":"f774d400-fd50-45c6-b8c9-ac83264ac6bc","type":"SelectValues","gui":{"x":540,"y":396,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["17ea7d42-8c59-463e-8e46-156912fcea68"],"removeByDefault":false,"notes":[],"fields":[{"name":"Dataset ID","rename":"Card Dataset ID"}]},{"name":"Get Distinct List of Datasets from Cards","id":"27da235a-ba24-4039-8797-703282fef344","type":"Unique","gui":{"x":648,"y":396,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["f774d400-fd50-45c6-b8c9-ac83264ac6bc"],"removeByDefault":false,"notes":[],"countRows":false,"partitionedAggregation":false,"fields":[{"name":"Card Dataset ID","caseInsensitive":false}]},{"name":"Datasets used in Cards","id":"4a82b468-03b8-46aa-acac-058581359745","type":"MergeJoin","gui":{"x":744,"y":336,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["ca1e9b38-29e0-4292-80d1-32e3c7640cb8","27da235a-ba24-4039-8797-703282fef344"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"ca1e9b38-29e0-4292-80d1-32e3c7640cb8","step2":"27da235a-ba24-4039-8797-703282fef344","keys1":["Dataset ID"],"keys2":["Card Dataset ID"],"schemaModification1":[],"schemaModification2":[],"partitioningInputId":""},{"name":"Add Formula","id":"5182b3ca-c63e-4cb2-9ba8-a86105237049","type":"ExpressionEvaluator","gui":{"x":168,"y":84,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["65fbdb57-a671-44fb-96b9-c47ca68e1a8d"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"LOWER(Input Dataset ID)","fieldName":"Input Dataset ID","settings":null},{"expression":"LOWER(Input Dataset ID)","fieldName":"Output Dataset ID","settings":null}]},{"name":"Input Datasets","id":"c67400ed-ce79-4c5a-aedb-0540453710a6","type":"Filter","gui":{"x":288,"y":48,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["5182b3ca-c63e-4cb2-9ba8-a86105237049"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Input Dataset ID","rightExpr":null,"operator":"NN","andFilterList":[]}]},{"name":"Select Columns 1","id":"6bbfa462-5f36-40c6-9937-74f95f8e2aba","type":"SelectValues","gui":{"x":396,"y":48,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["c67400ed-ce79-4c5a-aedb-0540453710a6"],"removeByDefault":false,"notes":[],"fields":[{"name":"Input Dataset ID","rename":"Dataflow Dataset ID"}]},{"name":"Output Datasets","id":"0b6606d8-6fbd-452a-b4c2-d4a827b83f5d","type":"Filter","gui":{"x":288,"y":144,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["5182b3ca-c63e-4cb2-9ba8-a86105237049"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Output Dataset ID","rightExpr":null,"operator":"NN","andFilterList":[]}]},{"name":"Select Columns 2","id":"44d46c42-4d14-48c0-ae80-5fb9a5d4bb05","type":"SelectValues","gui":{"x":396,"y":144,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["0b6606d8-6fbd-452a-b4c2-d4a827b83f5d"],"removeByDefault":false,"notes":[],"fields":[{"name":"Output Dataset ID","rename":"Dataflow Dataset ID"}]},{"name":"Append Rows","id":"d77f59d1-ba97-40fa-bf07-e12d3a173345","type":"UnionAll","gui":{"x":516,"y":96,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["6bbfa462-5f36-40c6-9937-74f95f8e2aba","44d46c42-4d14-48c0-ae80-5fb9a5d4bb05"],"removeByDefault":false,"notes":[],"unionType":"INCLUDE_FROM","schemaSource":"6bbfa462-5f36-40c6-9937-74f95f8e2aba","strict":false},{"name":"Get Distinct List of Dataset IDs from Dataflows","id":"ed20cf4d-77fe-438f-b4d2-089ec24f1dac","type":"Unique","gui":{"x":624,"y":96,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["d77f59d1-ba97-40fa-bf07-e12d3a173345"],"removeByDefault":false,"notes":[],"countRows":false,"partitionedAggregation":false,"fields":[{"name":"Dataflow Dataset ID","caseInsensitive":true}]},{"name":"Datasets used in Dataflows","id":"f3770b41-7fd6-457b-bece-83643a7e16e8","type":"MergeJoin","gui":{"x":744,"y":192,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["ca1e9b38-29e0-4292-80d1-32e3c7640cb8","ed20cf4d-77fe-438f-b4d2-089ec24f1dac"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"ca1e9b38-29e0-4292-80d1-32e3c7640cb8","step2":"ed20cf4d-77fe-438f-b4d2-089ec24f1dac","keys1":["Dataset ID"],"keys2":["Dataflow Dataset ID"],"schemaModification1":[],"schemaModification2":[],"partitioningInputId":""},{"name":"Append Rows 1","id":"be88fa60-c968-4d83-b730-986c5a9bcf62","type":"UnionAll","gui":{"x":900,"y":264,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["f3770b41-7fd6-457b-bece-83643a7e16e8","4a82b468-03b8-46aa-acac-058581359745"],"removeByDefault":false,"notes":[],"unionType":"INCLUDE_MATCHES","schemaSource":"f3770b41-7fd6-457b-bece-83643a7e16e8","strict":false},{"name":"Join Data","id":"c471c3f1-19a8-476c-be71-2388ab2301d1","type":"MergeJoin","gui":{"x":1020,"y":288,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["ca1e9b38-29e0-4292-80d1-32e3c7640cb8","be88fa60-c968-4d83-b730-986c5a9bcf62"],"removeByDefault":false,"notes":[],"joinType":"LEFT OUTER","step1":"ca1e9b38-29e0-4292-80d1-32e3c7640cb8","step2":"be88fa60-c968-4d83-b730-986c5a9bcf62","keys1":["Dataset ID"],"keys2":["Dataset ID"],"schemaModification1":[],"schemaModification2":[{"name":"Dataset ID","rename":"Used Dataset ID","remove":false},{"name":"Dataset Name","rename":"Append Rows 1.Dataset Name","remove":true},{"name":"Description","rename":"Append Rows 1.Description","remove":true},{"name":"Owner Name","rename":"Append Rows 1.Owner Name","remove":true},{"name":"DataSet Last Updated Date/Time","rename":"Append Rows 1.DataSet Last Updated Date/Time","remove":true}],"partitioningInputId":""},{"name":"Datasets Not Used","id":"da93d898-e6cc-4123-bf8f-8d08ef34d5f4","type":"Filter","gui":{"x":1128,"y":288,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["c471c3f1-19a8-476c-be71-2388ab2301d1"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Used Dataset ID","rightExpr":null,"operator":"NL","andFilterList":[]}]},{"name":"Unused Datasets","id":"5069208b-aaf1-4006-9a6b-2accdb850590","type":"PublishToVault","gui":{"x":1260,"y":288,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["da93d898-e6cc-4123-bf8f-8d08ef34d5f4"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"7963e9b8-4642-4e9d-a17b-2b70b64fafdc","type":"DataFlow","name":"Unused Datasets","description":"Uses Domo Governance Datasets to identify unused datasets","cloudId":"domo"},"versionChainType":"REPLACE","partitionIdColumns":[],"upsertColumns":[],"retainPartitionExpression":""}]}

larryjacobs

@MarkSnodgrass Thank you! Seems to be what I am needing for now.