Dataset and tables

Tables under default schema

Tables govern access to tabular data: This is were all datasets from your Veracity workspace will be listed. New tables created in Databricks must be synched back to Datasets using workbench library.

Derived datasets (views) are listed in Default schema with tables.

Shared datasets

Shared datasets and derived datasets (such as views) originating from other workspaces are exposed in the Databricks Data Catalog under dedicated schemas. Each source workspace is represented by a schema named: shared_datasets_<tenantAlias>_<WorkspaceName>

All datasets and views shared from the same workspace are grouped within the same schema in Databricks.

Each shared schema is tagged with shared_by_workspaceId.

Each dataset (view) for these schemas includes the following tags:

• dataset_name
• dataset_id
• share_id
• schema_id
• schemaversion_id

When the columns or filters of a shared data set change in Data Workbench, the changes are automatically synchronized with the corresponding views in Databricks.

Read datasets

Datasets from Data workbench are automatically available as Tables (default schema) in Azure Databricks. Use Sql Editor or Notebook with Sql to query tables. To read dataset using python use table name from Catalog/Tables or from Widget

df = spark.read.table("<tablename>")
display(df)

Using widgets to paramaterize the table name

dsName = dbutils.widgets.get("datasetName")
df2 = spark.table(dsName)

# example of sql query in python
query = f"select * from {dsName} where Value > 1000"
df = spark.sql(query)

When using sql, use IDENTIFIER(:widgetName) to get the datasetName

%sql
select * from IDENTIFIER(:inputDataset) where Value > 300

Read shared dataset

To read a table from other schemas than default, include schema name

%sql
select * from shared_datasets_<tenant>_<workspace>.<datasetName>

Synchronize datasets from tables

Datasets in Data workbench are synchronized into Tables in the Databricks environment. You can update existing tables in Databricks and then the dataset in Data workbench is automatically updated. When creating a new table in Databricks, it is not automatically synched back to Data workbech and it requires using dataworkbench library.

Common modes:

• overwrite: Completely replaces the existing table
• append: Adds new rows to the existing table
• ignore: Skips insertion if data already exists
• error: Raises an error if data conflicts (default behavior)

Updating existing tables using overwrite

For existing tables that are already synchronized between Databricks and Data Workbench, you can directly overwrite the table using Spark's write methods.

# Create a new DataFrame -
df = spark.createDataFrame([
    ("d", 1), 
    ("e", 2), 
    ("f", 5)
], ["letter", "number"])

# Overwrite the existing table
df.write.mode("overwrite").saveAsTable("TableNameOverwrite")

Updating existing tables/datasets using append mode

When you want to add new rows to an existing table:

# Append new data to the existing table 
df.write.mode("append").saveAsTable("TableName")

Create new dataframe or table and synch to Data Workbench

New datasets can be created from Databricks and synchronized with Data Workbench. This is especially useful for data transformations (medallion architecture). For creating new dataframes in Databricks that need to be synced to Data Workbench, use Veracity internal library named dataworkbench that comes pre-installed to the cluster.

There are different approaches:

1. Create a dataframe and write the dataframe as a new dataset in Data workbench. After the dataset is written to Data worksbench, the dataset is synched with Databricks and the table will be created.
2. Create a table in databricks and write that table to a new dataset. The dataset you write to DWB needs another name than the table name, since the dataset with autoamtically be synched back. This results in 2 equal tables in databricks. Remember to drop the first table.

Note: you should use a schema id in order to comply to a predefined schema.

In below example a table is written to dataset in Data Workbench using library dataworkbench. The dataset will be synched back to Databricks. The original table should therefore be dropped (deleted).

import dataworkbench
tablename = "table name in databricks"
##Dataset name CANNOT be same as table name, since table already exists and hence will give an error when creating a table from DWB
datasetName = "BKAL2003_1"
description = "Some description"
metadata = {"asset": ["1234"], "export_date": ["2026-03-03"]}
#get schema id from data workbench (schema id and not version id)
schemaId= "<schema id>"

df = spark.read.table(tablename)

## Esure that datatype in table columns match datatypes in schema. 
## You can enforce a casting. In this example the column 'Value' in databricks table is string, but cast to decimal to match schema column in data workbench
df = df.withColumn(
    "Value",
    df["Value"].cast(DecimalType(28, 8))
)

datacatalogue = dataworkbench.DataCatalogue()
datacatalogue.save(
   df,
   datasetName,
   description,
   tags= metadata,
   schema_id= schemaId # Using an existing schema ID   
)

The code above will create a dataset with the existing schema id "current active version". If schema_id is not provided, a new schema will be created based on the definition in the table.

You can also write a filtered view of a table back to a dataset in Dataworkbench

dsName = dbutils.widgets.get("inputDataset")
query = f"select * from {dsName}  where t_set_h > 5.0"
df = spark.sql(query)

Read CSV file from Volume and write as dataset to Data Workbench

This code example reads a CSV file and stores the dataframe as dataset in Data Workbench. The dataset will be synced back to databricks as a table. Hence, the table name must be unique.

import dataworkbench

datasetName = "Test2003_1"
description = "Some description"
#optional with tags
metadata = {"asset": ["1234"], "export_date": ["2026-03-03"]}  
#get schema id from data workbench (schema id and not version id)
schemaId= "<schema id>"

filename = "/Volumes/xxx/sss.csv"
df = spark.read.format("csv").option("header", "true").option("inferSchema", "true").load(filename)

## Esure that datatype in table columns match datatypes in schema. 
## You can enforce a casting.
## In this example the column 'Value' in databricks table is string, but cast to decimal to match schema column in data workbench
df = df.withColumn(
    "Value",
    df["Value"].cast(DecimalType(28, 8))
)

datacatalogue = dataworkbench.DataCatalogue()
datacatalogue.save(
   df,
   datasetName,
   description,
   tags= metadata,
   schema_id= schemaId # Using an existing schema ID   
)

Delete tables

Temporarily tables can be deleted to clean up

%sql
drop table if exists BKAL2003