Skip to main content

Organize data for a small organization

Use this pattern when one or two data teams in a single business unit share a Databricks account. It separates experimental work from the trusted data that feeds business reports, gives every project the same schema layout, and grants each group only the access that it needs. It doesn't use the business unit prefixes that larger organizations use.

The pattern has three parts:

  • A catalog for each environment, bound to the matching workspace.
  • Bronze, silver, and gold schemas for each project.
  • Schema-level grants to groups, with production writes reserved for service principals.

Create a catalog for each environment​

Create a dev, staging, and prod catalog, and bind each catalog to the workspace for its environment. Optionally, add a sandbox catalog for exploratory work and demos that aren't part of a project.

A metastore with sandbox, dev, staging, and prod catalogs. The sandbox and dev catalogs are both bound to the development workspace, staging to the staging workspace, and prod to the production workspace.

Each catalog has a different purpose:

  • dev: Development and experimentation. All data teams have access, and nothing in this catalog affects production.
  • staging: Integration testing before production. Teams deploy finished work here, and validate the output before release.
  • prod: Trusted data that business users rely on for dashboards and reports. Only automated jobs and pipelines that run as service principals write to this catalog.

By default, every catalog in the metastore is accessible from any workspace attached to it. The exception is the default workspace catalog, which is bound only to its own workspace. To restrict a catalog to its workspace, bind the catalog to that workspace. Users in other workspaces then can't access the catalog, even if they have privileges on it. See Workspace-catalog binding.

Create medallion schemas for each project​

Within each catalog, create three schemas for each project, one for each layer of the medallion architecture. You need USE CATALOG and CREATE SCHEMA on the parent catalog. See Create schemas. For example, a Customer 360 project uses c360_bronze, c360_silver, and c360_gold.

Bronze, silver, and gold schemas for each project in the dev, staging, and prod catalogs.

Each schema holds data for a different stage of processing:

Schema

Contents

Purpose

<project>_bronze

Streaming tables for raw cloud object storage data, and tables from Lakeflow Connect ingestion pipelines

Raw source data for reprocessing after downstream changes

<project>_silver

Streaming tables and tables for cleaning, deduplicating, and conforming bronze data

Validated data for analysis and gold tables

<project>_gold

Materialized views, metric views, and aggregate tables

Business-level aggregates for dashboards, reports, and business intelligence (BI) tools

Schema

Contents

Purpose

<project>_bronze

Streaming tables for raw cloud object storage data, and tables from Lakeflow Connect ingestion pipelines

Raw source data for reprocessing after downstream changes

<project>_silver

Streaming tables and tables for cleaning, deduplicating, and conforming bronze data

Validated data for analysis and gold tables

<project>_gold

Materialized views, metric views, and aggregate tables

Business-level aggregates for dashboards, reports, and business intelligence (BI) tools

Grant access to groups​

Grant privileges to groups at the schema level. Objects in Unity Catalog inherit the privileges of their parent, so tables that you add to a schema later get the same access without new grants.

Permissions on the Customer 360 schemas: data-engineers owns each schema and has Data Editor access, data-scientists has Data Reader access to silver and gold, and bi-users has Data Reader access to gold.

Apply the following rules:

  • Ownership: The group that builds a project owns its schemas. For example, if data-engineers builds the Customer 360 pipelines, it owns c360_bronze, c360_silver, and c360_gold. Transfer each schema to that group, not to an individual user. See Transfer Unity Catalog object ownership to groups.
  • Read and write access: Schema owners grant other groups access to the schema. In Catalog Explorer, open the schema's Permissions tab, click Grant, and choose a privilege preset such as Data Reader, or select privileges by hand. The Data Reader preset grants USE SCHEMA, SELECT, EXECUTE, READ VOLUME, and BROWSE on the schema. The Data Editor preset grants USE SCHEMA, SELECT, MODIFY, READ VOLUME, WRITE VOLUME, APPLY TAG, BROWSE, and the privileges to create tables, functions, volumes, models, and materialized views. Neither preset grants USE CATALOG, so grant that on the catalog separately.
  • Least privilege: Grant each group only the access that it needs. For example, grant data scientists read access to silver and gold, and grant BI users read access to gold.
  • Production writes: Grant write access to the prod catalog only to the service principals that run production jobs and pipelines.

Run the following SQL statements on Unity Catalog-compatible compute to create the Customer 360 schemas in the dev catalog and grant access to them. Run the ownership transfers as the current schema owner, a metastore admin, the owner of the parent catalog, or a user with MANAGE on the schema, and run the grants as an owner or a user with MANAGE on the target object:

SQL
CREATE SCHEMA IF NOT EXISTS dev.c360_bronze;
CREATE SCHEMA IF NOT EXISTS dev.c360_silver;
CREATE SCHEMA IF NOT EXISTS dev.c360_gold;

ALTER SCHEMA dev.c360_bronze OWNER TO `data-engineers`;
ALTER SCHEMA dev.c360_silver OWNER TO `data-engineers`;
ALTER SCHEMA dev.c360_gold OWNER TO `data-engineers`;

GRANT USE CATALOG ON CATALOG dev TO `data-scientists`;
GRANT USE SCHEMA, SELECT ON SCHEMA dev.c360_silver TO `data-scientists`;
GRANT USE SCHEMA, SELECT ON SCHEMA dev.c360_gold TO `data-scientists`;

GRANT USE CATALOG ON CATALOG dev TO `bi-users`;
GRANT USE SCHEMA, SELECT ON SCHEMA dev.c360_gold TO `bi-users`;

For the privileges that you can grant, see Manage privileges in Unity Catalog.

Grow into the medium and large pattern​

Move to the medium and large organization pattern when a second business unit joins, or when teams from different departments must not see each other's data. That pattern keeps the same environments, schemas, and grant rules, and adds a business unit prefix to workspaces, catalogs, and groups.

Next steps​