Data Dictionary (Latest)

ERD

The “ERD” below is a visual representation of the consumer layer data model. Many of the joinable lines have been omitted to reduce chaos and complexity in the visualization. All columns with the same name are joinable (even if there’s not a line from one table to the other). The relations depicted are to call the analyst’s attention to less obvious joins.

The goal is to present a data model that unifies the different parts of the platform. The Overwatch team will continue to work with Databricks platform teams to publish and simplify this data. The gray boxes annotated as “Backlog/Research” are simply a known gap, and a pursuit of the Overwatch dev team, it does NOT mean it’s going to be released soon but rather that we are aware of the missing component, and we hope to enable gold-level data here in the future.

Consumption Layer “Tables” (Views)

All end users should be hitting consumer tables first. Digging into lower layers gets significantly more complex. Below is the data model for the consumption layer. The consumption layer is often in a stand-alone database apart from the ETL tables to minimize clutter and confusion. These entities in this layer are actually not tables at all (with a few minor exceptions such as lookup tables) but rather views. This allows for the Overwatch development team to alter the underlying columns, names, types, and structures without breaking existing transformations. Instead, view column names will remain the same but may be repointed to a newer version of a column, etc.

Data Organization

The large gray boxes in the simplified ERD below depict the two major, logical sections of the data model:

• Databricks Platform - Metadata captured by the Databricks platform that can be used to assist in workspace governance. This data can also be enriched with the Spark data enabling in-depth analyses. The breadth of metadata is continuing to grow, stay tuned for additional capabilities.
• Spark UI The spark UI section is derived from the spark event logs and essentially contains every single piece of data from the Spark UI. There are a few sections that are not included in the first release but the data is present in spark_events_bronze albeit extremely complex to derive. The Overwatch development team is working tirelessly to expose additional SparkUI data and will publish as soon as it’s ready.

Column Descriptions

Complete column descriptions are only provided for the consumption layer. The entity names are linked below.

• cluster

• clusterStateFact

• instanceDetails

• job

• jobrun

• jobRunCostPotentialFact

• sqlQueryHistory

• notebook

• instancePool

• dbuCostDetail

• accountLogin

• accountMod

• sparkExecution

• sparkExecutor

• sparkJob

• sparkStage

• sparkTask

• sparkStream

• warehouse

• notebookCommands

• Common Meta Fields

  • There are several fields that are present in all tables. Instead of cluttering each table with them, this section was created as a reference to each of these.

Cluster

SAMPLE

KEY – organization_id + cluster_id + unixTimeMS

Incremental Columns – unixTimeMS

Partition Columns – organization_id

Write Mode – Append

ClusterStateFact

SAMPLE

KEY – organization_id + cluster_id + state + unixTimeMS_state_start

Incremental Columns – state_start_date + unixTimeMS

Partition Columns – organization_id + state_start_date

Z-Order Columns – cluster_id + unixTimeMS_state_start

Write Mode – Merge

Unsupported Scenarios

A few scenarios are not yet supported by Overwatch; they are called out here. Please stay tuned for updates as it’s our intention to include everything we can as soon as possible after Databricks product GAs new features but there will be a delay.

• Costs for DBSQL clusters
  • Even though DBSQL warehouses may show up here (they normally will not), the costs for these cannot be calculated at this time. Databricks doesn’t yet publish the warehouse event logs (i.e. start/stop/scale) and until that is available, we cannot estimate costs for warehouses like we do for traditional clusters.
• Costs for Fleet Instance Type
  • Right now for AWS Fleet Clusters are not supported. For Fleet Instance type specific instances are determined by the price and capacity conditions at the time of cluster creation, and right now Databricks doesn’t publish information when each of these specific instances are allocated for fleet instance type, so we cannot estimate costs for warehouses like we do for traditional clusters.

Costs and state details by cluster at every state in the cluster lifecycle. The Cost Functions are detailed below the definitions of this table.

Cost Functions Explained

EXPECTATIONS – Note that Overwatch costs are derived. This is good and bad. Good as it allows for costs to be broken down by any dimension at the millisecond level. Bad because there can be significant differences between the derived costs and actual costs. These should generally be very close to equal but may differ within margin of error by as much as 10%. To verify the cost functions and the elements therein feel free to review them in more detail. If your costs are off by a large margin, please review all the components of the cost function and correct any configurations as necessary to align your reality with the Overwatch config. The default costs are list price and often do not accurately reflect a customer’s costs.

• cloudBillable: Cluster is in a running state
  • GAP: Note that cloud billable ends at the time the cluster is terminated even though the nodes remain provisioned in the cloud provider for several more minutes; these additional minutes are not accounted for in this cost function.
• driver_compute_cost: when cloudBillable –> Driver Node Compute Contract Price Hourly (instanceDetails) * Uptime_In_State_H –> otherwise 0
• worker_compute_cost: when cloudBillable –> Worker Node Compute Contract Price Hourly (instanceDetails) * Uptime_In_State_H * target_num_workers –> otherwise 0
  • target_num_workers used here is ambiguous. Assuming all targeted workers can be provisioned, the calculation is most accurate; however, if some workers cannot be provisioned the worker_compute_cost will be slightly higher than actual while target_num_workers > current_num_workers. target_num_workers used here because the compute costs begin accumulating as soon as the node is provisioned, not at the time it is added to the cluster.
• photon_kicker:
  • WHEN runtime_engine == “Photon” and sku != “SqlCompute” and isAutomated THEN 2.9
  • WHEN runtime_engine == “Photon” and sku != “SqlCompute” and !isAutomated THEN 2
  • otherwise 1
• worker_dbus:
  • WHEN databricks_billable and !SingleNode THEN current_num_workers * driver_hourly_dbus (instancedetails.hourlyDBUs) * uptime_in_state_H * photon_kicker
    • NOTE: current_num_workers == current_worker, not target. current_num_workers only includes worker nodes after they have become ready and able to receive workloads.
  • otherwise 0
• driver_dbus: when databricks_billable –> driver_hourly_dbus (instancedetails.hourlyDBUs) * uptime_in_state_H * photon_kicker –> otherwise 0
  • NOTE: In Single Node Clusters – only the driver will have dbus
• worker_dbu_cost: houry_dbu_rate for sku (dbuCostDetails.contract_price) * worker_dbus
• driver_dbu_cost: houry_dbu_rate for sku (dbuCostDetails.contract_price) * driver_dbus

InstanceDetails

AWS Sample | AZURE_Sample

KEY – Organization_ID + API_name

Incremental Columns – Pipeline_SnapTS

Partition Columns – organization_id

Write Mode – Append

This table is unique and it’s purpose is to enable users to identify node specific contract costs associated with Databricks and the Cloud Provider through time. Defaults are loaded as an example by workspace. These defaults are meant to be reasonable, not accurate by default as there is a wide difference between cloud discount rates and prices between regions / countries. Everytime Overwatch runs, it validates the presence of this table and whether it has any data present for the current workspace, if it does not it creates and appends the relevant data to it; otherwise no action is taken. This gives the user the ability to extend / customize this table to fit their needs by workspace. Each organization_id (workspace), should provide complete cost data for each node used in that workspace. If you decide to completely customize the table, it’s critical to note that some columns are required for the ETL to function; these fields are indicated below in the table with an asterisk.

The organization_id (i.e. workspace id) is automatically generated for each workspace if that organization_id is not present in the table already (or the table is not present at all). Each workspace (i.e. organization_id) often has unique costs, this table enables you to customize compute pricing.

IMPORTANT This table must be configured such that there are no overlapping costs (by time) and no gaps (by time) in costs for any key (organization_id + API_name) between primordial date and current date. This means that for a record to be “expired” the following must be true:

• original key expired by setting activeUntil == expiry date
• original key must be created with updated information and must:
  • have activeFrom == expiry date of previous record (no gap, no overlap)
  • have activeUntil == lit(null).cast(“date”)

Azure VM Pricing Page

AWS EC2 Pricing Page

dbuCostDetails

KEY – Organization_ID + sku

Incremental Columns – activeFrom

Partition Columns – organization_id

Write Mode – Append

Slow-changing dimension to track DBU contract costs by workspace through time. This table should only need to be edited in very rare circumstances such as historical cost correction. Note that editing these contract prices will not retroactively modify historical pricing in the costing table such as clusterStateFact or jobRunCostPotentialFact. For prices to be recalculated, the gold pipeline modules must be rolled back properly such that the costs can be rebuilt with the updated values.

Job

SAMPLE

The below columns closely mirror the APIs listed below by action. For more details about these fields and their structures please reference the relevant Databricks Documentation for the action.

Note – Databricks has moved to API2.1 for all jobs-related functions and in-turn, Databricks has moved several fields from the root level to a nested level to support multi-task jobs. These root level fields are still visible in Overwatch as some customers are still using legacy APIs and many customers have historical data by which this data was generated using the legacy 2.0 APIs. These fields can be identified by the prefix, “Legacy” in the Description and have been colored red on the ERD.

KEY – organization_id + job_id + unixTimeMS + action + request_id

Incremental Columns – unixTimeMS

Partition Columns – organization_id

Write Mode – Append

JobRun

SAMPLE

In Databricks, jobs (also called Workflows) can have 1 or more tasks associated with it. Whenever the job is triggered, a job run occurs, which will have the execution of the tasks. In this table, each row represents the metrics of each task executed during the job run. Each record references the full lifecycle of a single task run with some legacy fields to accommodate historical job-level runs (and jobs/runs still being created/launched from the deprecated Jobs 2.0 API). Since the inception of multi-task jobs and Databricks jobs API 2.1, all run logic has been migrated from the job-level to the task-level. Overwatch must accommodate both as many customers have historical data that is still important. As such, some of the fields seem redundant and the analyst must apply the correct logic based on the circumstances. Please carefully review the field descriptions to understand the rules.

Unsupported Scenarios

A few scenarios are not yet supported by Overwatch; they are called out here. Please stay tuned for updates as it’s our intention to include everything we can as soon as possible after Databricks product GAs new features but there will be a delay.

• DLT details – Delta Live tables aren’t yet supported even though the run_ids may show up here
• Runs that failed to launch due to error in the launch request – these never actually create a run and never receive a run_id therefore they will not be present in this table at this time.
• Runs executing for more than 30 Days – This is a limitation for performance. This will be an externalized config at a later time but for now the hard limit is 30 days. The progress of this feature can be tracked in Issue 528

KEY – organization_id + run_id + startEpochMS

Incremental Columns – startEpochMS

Partition Columns – organization_id

Write Mode – Merge

Inventory of every canonical task run executed by databricks workspace.

JobRunCostPotentialFact

SAMPLE

Databricks has moved to “multi-task jobs” and each run now refers to the run of a task not a job. Please reference jobRuns table for more detail

Unsupported Scenarios

• Costs for runs of that execute SQL/DBT/DLT tasks

KEY – organization_id + run_id + startEpochMS

Incremental Columns – startEpochMS

Partition Columns – organization_id

Write Mode – Merge

This fact table defines the job, the cluster, the cost, the potential, and utilization (if cluster logging is enabled) of a cluster associated with a specific Databricks Job Run.

Dimensionality Note that this fact table is not normalized by time but rather by job run and cluster state. Costs are not derived from job runs but from clusters thus the state[s] of the cluster are what’s pertinent when tying to cost. This is extremely important in the case of long running jobs, such as streaming.

SCENARIO: Imagine a streaming job with 12 concurrent runs on an existing cluster that run for 20 days at the end of which the driver dies for some reason causing all runs fail and begin retrying but failing. When the 20 days end, the cost will be captured solely on that date and even more importantly, not only will all 20 days be captured at that date but the cost associated will be cluster runtime for 20 days * number of runs. Overwatch will automatically smooth the costs across the concurrent runs but not the days running since this fact table is not based by on an equidistant time axis.

• Potential: Total core_milliseconds for which the cluster COULD execute spark tasks. This derivation only includes the worker nodes in a state ready to receive spark tasks (i.e. Running). Nodes being added or running init scripts are not ready for spark jobs thus those core milliseconds are omitted from the total potential.
• Cost: Derived from the instanceDetails table and DBU configured contract price (see Configuration for more details). The compute costs in instanceDetails table are taken from the “Compute_Contract_Price” values associated with the instance type in instanceDetails.
• Utilization: Utilization is a function of core milliseconds used during spark task execution divided by the total amount of core milliseconds available given the cluster size and state. (i.e. spark_task_runtime_H / worker_potential_core_H)
• Cluster State: The state[s] of a cluster during a run. As the cluster scales and morphs to accommodate the run’s needs, the state changes. The number of state changes are recorded in this table as “run_cluster_states”.
• Run State: Advanced Topic for data engineers and developers. This topic is discussed in considerable detail in the Advanced Topics section. Given a cluster state, the run state is a state of all runs on a cluster at a given moment in time. This is the measure used to calculate shared costs across concurrent runs. A run state cannot pass the boundaries of a cluster state, a run that continues across cluster-state lines will result in a new run state.

sqlQueryHistory

SAMPLE

KEY – organization_id + warehouse_id + query_id + query_start_time_ms

Incremental Columns – query_start_time_ms

Partition Columns – organization_id

Write Mode – Merge

Notebook

SAMPLE

KEY – organization_id + notebook_id + request_id + action + unixTimeMS

Incremental Columns – unixTimeMS

Partition Columns – organization_id

Write Mode – Append

InstancePool

KEY – organization_id + instance_pool_id + timestamp

Incremental Columns – timestamp

Partition Columns – organization_id

Write Mode – Merge

Account Tables

spark.table("overwatch.audit_log_bronze")
  .filter('serviceName === "accounts" && 'actionName === "createGroup")
  .selectExpr("*", "requestParams.*").drop("requestParams")      

AccountLogin

SAMPLE

KEY – organization_id + login_type + login_unixTimeMS + from_ip_address

Incremental Columns – login_unixTimeMS

Partition Columns – organization_id

Write Mode – Append

AccountMod

SAMPLE

KEY – organization_id + acton + mod_unixTimeMS + request_id

Incremental Columns – mod_unixTimeMS

Partition Columns – organization_id

Write Mode – Append

This table tracks any admin changes made to user entities, such as group membership, user updates, etc.

SparkExecution

SAMPLE

KEY – organization_id + spark_context_id + execution_id + date + unixTimeMS

Incremental Columns – date + unixTimeMS

Partition Columns – organization_id

Write Mode – Merge

SparkExecutor

SAMPLE

KEY – organization_id + spark_context_id + executor_id + date + unixTimeMS

Incremental Columns – date + unixTimeMS

Partition Columns – organization_id

Write Mode – Merge

SparkJob

SAMPLE

KEY – organization_id + spark_context_id + job_id + unixTimeMS

Incremental Columns – date + unixTimeMS

Partition Columns – organization_id + date

Z-Order Columns – cluster_id

Write Mode – Merge

SparkStage

SAMPLE

KEY – organization_id + spark_context_id + stage_id + stage_attempt_id + unixTimeMS

Incremental Columns – date + unixTimeMS

Partition Columns – organization_id + date

Z-Order Columns – cluster_id

Write Mode – Merge

SparkTask

SAMPLE

KEY – organization_id + spark_context_id + task_id + task_attempt_id + stage_id + stage_attempt_id + host + unixTimeMS

Incremental Columns – date + unixTimeMS

Partition Columns – organization_id + date

Z-Order Columns – cluster_id

Write Mode – Merge

SparkStream

KEY – organization_id + spark_context_id + cluster_id + stream_id + stream_run_id + stream_batch_id + stream_timestamp

Incremental Columns – date + stream_timestamp

Partition Columns – organization_id + date

Z-Order Columns – cluster_id

Write Mode – Merge

Warehouse

SAMPLE

KEY – organization_id + warehouse_id + unixTimeMS

Incremental Columns – unixTimeMS

Partition Columns – organization_id

Write Mode – Append

NotebookCommands

KEY – notebook_id + unixTimeMS

Incremental Columns – unixTimeMS

Partition Columns – organization_id

Write Mode – Merge

Common Meta Fields

ETL Tables

The following are the list of potential tables, the module with which it’s created and the layer in which it lives. This list consists of only the ETL tables created to facilitate and deliver the consumption layer The gold and consumption layers are the only layers that maintain column name uniformity and naming convention across all tables. Users should always reference Consumption and Gold layers unless the data necessary has not been curated.

Bronze

Silver

Gold