Purview - Use REST API toCreate custom data source with tabular schema, similar to Azure Databricks Table

@Isaac Hiles

Thanks for the question and using MS Q&A platform.

It looks like you’re trying to model a custom data source in Microsoft Purview, but the custom column entities are showing up as individual assets instead of being nested under the parent table. This is different from the behavior you see with Azure Databricks tables, where columns are not listed individually.

To achieve the desired behavior, you need to ensure that the relationship between the table and its columns is correctly defined and that the columns are treated as schema elements of the table.

steps to help you troubleshoot and adjust your definitions:

• Your relationship definition seems correct, with test_entity_tabular as the container for test_entity_table_column. Make sure this relationship is properly applied when creating instances of these entities.
• In your test_entity_tabular definition, you have the options attribute set to "schemaElementsAttribute": "columns". This is correct and should ensure that columns are treated as part of the table schema.
• When creating the table entity, ensure that you are correctly associating the column entities as schema elements. This typically involves setting the columns attribute of the table entity to include the column entities.

Here’s an example of how you might create the table and columns using the REST API:

{
  "entities": [
    {
      "typeName": "test_entity_tabular",
      "attributes": {
        "name": "test_table",
        "columns": [
          {
            "typeName": "test_entity_table_column",
            "attributes": {
              "name": "test_column1",
              "dataType": "string",
              "isNullable": true,
              "comment": "This is a test column"
            }
          },
          {
            "typeName": "test_entity_table_column",
            "attributes": {
              "name": "test_column2",
              "dataType": "string",
              "isNullable": true,
              "comment": "This is another test column"
            }
          }
        ]
      }
    }
  ]
}

This JSON payload creates a test_entity_tabular entity with two test_entity_table_column entities nested under it. Ensure that the columns attribute is correctly populated with the column entities.

ensure the relationships and attributes are correctly defined, your custom columns should be nested under the parent table, similar to the Azure Databricks table behavior.

I hope the above steps will resolve the issue, please do let us know if issue persists. Thank you

This is what an instance of the table looks like (name changed from test_entity_tabular to test_entity_table but definition is the same), and the columns are still not properly nested.

{'referredEntities': {'496c8790-b5df-4c7a-bac5-955b15ff9215': {'typeName': 'test_entity_table_column',
   'attributes': {'owner': None,
    'replicatedTo': None,
    'userDescription': None,
    'replicatedFrom': None,
    'qualifiedName': 'https:some/table/column1',
    'displayName': None,
    'dataType': 'STRING',
    'isNullable': True,
    'name': 'Test Column 1',
    'description': 'This is a test column',
    'comment': 'Hi'},
   'lastModifiedTS': '1',
   'guid': '496c8790-b5df-4c7a-bac5-955b15ff9215',
   'isIncomplete': False,
   'provenanceType': 0,
   'status': 'ACTIVE',
   'createdBy': '2bf90d31-e183-4dc6-bd56-0aa3f1117d1a',
   'updatedBy': '2bf90d31-e183-4dc6-bd56-0aa3f1117d1a',
   'createTime': 1734432006652,
   'updateTime': 1734432006652,
   'version': 1,
   'isIndexed': True,
   'relationshipAttributes': {'inputToProcesses': [],
    'schema': [],
    'sources': [],
    'sinks': [],
    'attachedSchema': [],
    'meanings': [],
    'table': {'guid': '57df4184-ad78-46d7-9bcd-b3069e49370d',
     'typeName': 'test_entity_table',
     'entityStatus': 'ACTIVE',
     'displayText': 'Test table',
     'relationshipType': 'test_entity_table_columns',
     'relationshipGuid': '5f4837fb-003f-4324-a24e-2e0ab098dff2',
     'relationshipStatus': 'ACTIVE',
     'relationshipAttributes': {'typeName': 'test_entity_table_columns'}},
    'outputFromProcesses': []},
   'labels': [],
   'displayText': 'Test Column 1',
   'proxy': False},
  '56794121-bf00-474a-a657-b36f0357800c': {'typeName': 'test_entity_table_column',
   'attributes': {'owner': None,
    'replicatedTo': None,
    'userDescription': None,
    'replicatedFrom': None,
    'qualifiedName': 'https:some/table/column4',
    'displayName': None,
    'dataType': 'STRING',
    'isNullable': True,
    'name': 'Test Column 4',
    'description': 'This is a test column',
    'comment': 'Hi'},
   'lastModifiedTS': '1',
   'guid': '56794121-bf00-474a-a657-b36f0357800c',
   'isIncomplete': False,
   'provenanceType': 0,
   'status': 'ACTIVE',
   'createdBy': '2bf90d31-e183-4dc6-bd56-0aa3f1117d1a',
   'updatedBy': '2bf90d31-e183-4dc6-bd56-0aa3f1117d1a',
   'createTime': 1734432008268,
   'updateTime': 1734432008268,
   'version': 1,
   'isIndexed': True,
   'relationshipAttributes': {'inputToProcesses': [],
    'schema': [],
    'sources': [],
    'sinks': [],
    'attachedSchema': [],
    'meanings': [],
    'table': {'guid': '57df4184-ad78-46d7-9bcd-b3069e49370d',
     'typeName': 'test_entity_table',
     'entityStatus': 'ACTIVE',
     'displayText': 'Test table',
     'relationshipType': 'test_entity_table_columns',
     'relationshipGuid': '9459bd73-821f-48d5-99d4-9ec8695671a5',
     'relationshipStatus': 'ACTIVE',
     'relationshipAttributes': {'typeName': 'test_entity_table_columns'}},
    'outputFromProcesses': []},
   'labels': [],
   'displayText': 'Test Column 4',
   'proxy': False},
  '167daedb-4eba-4986-8815-6a8bb51deec5': {'typeName': 'test_entity_table_column',
   'attributes': {'owner': None,
    'replicatedTo': None,
    'userDescription': None,
    'replicatedFrom': None,
    'qualifiedName': 'https:some/table/column3',
    'displayName': None,
    'dataType': 'STRING',
    'isNullable': True,
    'name': 'Test Column 3',
    'description': 'This is a test column',
    'comment': 'Hi'},
   'lastModifiedTS': '1',
   'guid': '167daedb-4eba-4986-8815-6a8bb51deec5',
   'isIncomplete': False,
   'provenanceType': 0,
   'status': 'ACTIVE',
   'createdBy': '2bf90d31-e183-4dc6-bd56-0aa3f1117d1a',
   'updatedBy': '2bf90d31-e183-4dc6-bd56-0aa3f1117d1a',
   'createTime': 1734432007582,
   'updateTime': 1734432007582,
   'version': 1,
   'isIndexed': True,
   'relationshipAttributes': {'inputToProcesses': [],
    'schema': [],
    'sources': [],
    'sinks': [],
    'attachedSchema': [],
    'meanings': [],
    'table': {'guid': '57df4184-ad78-46d7-9bcd-b3069e49370d',
     'typeName': 'test_entity_table',
     'entityStatus': 'ACTIVE',
     'displayText': 'Test table',
     'relationshipType': 'test_entity_table_columns',
     'relationshipGuid': '21b1dc59-6a22-496a-8f5d-1dc424d92c3e',
     'relationshipStatus': 'ACTIVE',
     'relationshipAttributes': {'typeName': 'test_entity_table_columns'}},
    'outputFromProcesses': []},
   'labels': [],
   'displayText': 'Test Column 3',
   'proxy': False},
  '78c6b425-5f78-43da-a55a-22ae7a86efa4': {'typeName': 'test_entity_table_column',
   'attributes': {'owner': None,
    'replicatedTo': None,
    'userDescription': None,
    'replicatedFrom': None,
    'qualifiedName': 'https:some/table/column2',
    'displayName': None,
    'dataType': 'STRING',
    'isNullable': True,
    'name': 'Test Column 2',
    'description': 'This is a test column',
    'comment': 'Hi'},
   'lastModifiedTS': '1',
   'guid': '78c6b425-5f78-43da-a55a-22ae7a86efa4',
   'isIncomplete': False,
   'provenanceType': 0,
   'status': 'ACTIVE',
   'createdBy': '2bf90d31-e183-4dc6-bd56-0aa3f1117d1a',
   'updatedBy': '2bf90d31-e183-4dc6-bd56-0aa3f1117d1a',
   'createTime': 1734432007065,
   'updateTime': 1734432007065,
   'version': 1,
   'isIndexed': True,
   'relationshipAttributes': {'inputToProcesses': [],
    'schema': [],
    'sources': [],
    'sinks': [],
    'attachedSchema': [],
    'meanings': [],
    'table': {'guid': '57df4184-ad78-46d7-9bcd-b3069e49370d',
     'typeName': 'test_entity_table',
     'entityStatus': 'ACTIVE',
     'displayText': 'Test table',
     'relationshipType': 'test_entity_table_columns',
     'relationshipGuid': '506f7bf2-cf57-441a-88fb-19cae5aecd78',
     'relationshipStatus': 'ACTIVE',
     'relationshipAttributes': {'typeName': 'test_entity_table_columns'}},
    'outputFromProcesses': []},
   'labels': [],
   'displayText': 'Test Column 2',
   'proxy': False},
  '9090fc66-13d0-4e7a-a84b-fb16c7b6f2ad': {'typeName': 'test_entity_table_column',
   'attributes': {'owner': None,
    'replicatedTo': None,
    'userDescription': None,
    'replicatedFrom': None,
    'qualifiedName': 'https:some/table/column0',
    'displayName': None,
    'dataType': 'STRING',
    'isNullable': True,
    'name': 'Test Column 0',
    'description': 'This is a test column',
    'comment': 'Hi'},
   'lastModifiedTS': '1',
   'guid': '9090fc66-13d0-4e7a-a84b-fb16c7b6f2ad',
   'isIncomplete': False,
   'provenanceType': 0,
   'status': 'ACTIVE',
   'createdBy': '2bf90d31-e183-4dc6-bd56-0aa3f1117d1a',
   'updatedBy': '2bf90d31-e183-4dc6-bd56-0aa3f1117d1a',
   'createTime': 1734432006056,
   'updateTime': 1734432006056,
   'version': 1,
   'isIndexed': True,
   'relationshipAttributes': {'inputToProcesses': [],
    'schema': [],
    'sources': [],
    'sinks': [],
    'attachedSchema': [],
    'meanings': [],
    'table': {'guid': '57df4184-ad78-46d7-9bcd-b3069e49370d',
     'typeName': 'test_entity_table',
     'entityStatus': 'ACTIVE',
     'displayText': 'Test table',
     'relationshipType': 'test_entity_table_columns',
     'relationshipGuid': '02276aea-8eb5-43d7-8df6-b8412ffad5ff',
     'relationshipStatus': 'ACTIVE',
     'relationshipAttributes': {'typeName': 'test_entity_table_columns'}},
    'outputFromProcesses': []},
   'labels': [],
   'displayText': 'Test Column 0',
   'proxy': False}},
 'entity': {'typeName': 'test_entity_table',
  'attributes': {'owner': None,
   'replicatedTo': None,
   'userDescription': None,
   'replicatedFrom': None,
   'qualifiedName': 'https:some/qualified/name',
   'displayName': None,
   'name': 'Test table',
   'description': 'This is a test table',
   'comment': None},
  'lastModifiedTS': '1',
  'guid': '57df4184-ad78-46d7-9bcd-b3069e49370d',
  'isIncomplete': False,
  'provenanceType': 0,
  'status': 'ACTIVE',
  'createdBy': '2bf90d31-e183-4dc6-bd56-0aa3f1117d1a',
  'updatedBy': '2bf90d31-e183-4dc6-bd56-0aa3f1117d1a',
  'createTime': 1734432005473,
  'updateTime': 1734432005473,
  'version': 1,
  'isIndexed': True,
  'relationshipAttributes': {'inputToProcesses': [],
   'schema': [],
   'sources': [],
   'sinks': [],
   'columns': [{'guid': '496c8790-b5df-4c7a-bac5-955b15ff9215',
     'typeName': 'test_entity_table_column',
     'entityStatus': 'ACTIVE',
     'displayText': 'Test Column 1',
     'relationshipType': 'test_entity_table_columns',
     'relationshipGuid': '5f4837fb-003f-4324-a24e-2e0ab098dff2',
     'relationshipStatus': 'ACTIVE',
     'relationshipAttributes': {'typeName': 'test_entity_table_columns'}},
    {'guid': '56794121-bf00-474a-a657-b36f0357800c',
     'typeName': 'test_entity_table_column',
     'entityStatus': 'ACTIVE',
     'displayText': 'Test Column 4',
     'relationshipType': 'test_entity_table_columns',
     'relationshipGuid': '9459bd73-821f-48d5-99d4-9ec8695671a5',
     'relationshipStatus': 'ACTIVE',
     'relationshipAttributes': {'typeName': 'test_entity_table_columns'}},
    {'guid': '78c6b425-5f78-43da-a55a-22ae7a86efa4',
     'typeName': 'test_entity_table_column',
     'entityStatus': 'ACTIVE',
     'displayText': 'Test Column 2',
     'relationshipType': 'test_entity_table_columns',
     'relationshipGuid': '506f7bf2-cf57-441a-88fb-19cae5aecd78',
     'relationshipStatus': 'ACTIVE',
     'relationshipAttributes': {'typeName': 'test_entity_table_columns'}},
    {'guid': '9090fc66-13d0-4e7a-a84b-fb16c7b6f2ad',
     'typeName': 'test_entity_table_column',
     'entityStatus': 'ACTIVE',
     'displayText': 'Test Column 0',
     'relationshipType': 'test_entity_table_columns',
     'relationshipGuid': '02276aea-8eb5-43d7-8df6-b8412ffad5ff',
     'relationshipStatus': 'ACTIVE',
     'relationshipAttributes': {'typeName': 'test_entity_table_columns'}},
    {'guid': '167daedb-4eba-4986-8815-6a8bb51deec5',
     'typeName': 'test_entity_table_column',
     'entityStatus': 'ACTIVE',
     'displayText': 'Test Column 3',
     'relationshipType': 'test_entity_table_columns',
     'relationshipGuid': '21b1dc59-6a22-496a-8f5d-1dc424d92c3e',
     'relationshipStatus': 'ACTIVE',
     'relationshipAttributes': {'typeName': 'test_entity_table_columns'}}],
   'attachedSchema': [],
   'meanings': [],
   'outputFromProcesses': []},
  'labels': [],
  'displayText': 'Test table',
  'proxy': False}}

This is the behaviour I'm seeing at the moment.

*I tried to add the types in a more readable formate but they get mangled

@Isaac Hiles

Thanks for sharing the details. It looks like the columns are still being treated as individual assets rather than being nested under the parent table.

please check these additional steps to troubleshoot this issue:

• Double-check that the relationship between the table and columns is correctly applied when creating the entities. The columns attribute in the table entity should reference the column entities.
• Ensure that the relationship definitions are correctly set up in your schema. The test_entity_table_columns relationship should be defined as a composition relationship, with the table as the container.
• When creating the table entity, make sure the columns attribute is populated with the correct references to the column entities. Here's an example of how to create the table and columns using the REST API:

{
  "entities": [
    {
      "typeName": "test_entity_table",
      "attributes": {
        "name": "Test table",
        "qualifiedName": "https:some/qualified/name",
        "columns": [
          {
            "typeName": "test_entity_table_column",
            "attributes": {
              "name": "Test Column 1",
              "qualifiedName": "https:some/table/column1",
              "dataType": "STRING",
              "isNullable": true,
              "comment": "Hi"
            }
          },
          {
            "typeName": "test_entity_table_column",
            "attributes": {
              "name": "Test Column 2",
              "qualifiedName": "https:some/table/column2",
              "dataType": "STRING",
              "isNullable": true,
              "comment": "Hi"
            }
          }
        ]
      }
    }
  ]
}

• Ensure that the relationshipAttributes for the table entity include the columns attribute with the correct GUIDs for the column entities.

I hope the above steps will resolve the issue, please do let us know if issue persists. Thank you