Box — Sources — Binom Governance

Copy formulas into the KPI Definition Card, then adapt grain and filters.

Example

Files uploaded

How many files were uploaded in the period?

COUNT(*) FROM file WHERE created_at IN period

Grain: Uploaded file
Fields: File.id, File.created_at, File.parent_folder_id
Dimensions: metadata_template, job_title

created_at from the file object; exclude trash/temp folders.

Adapt: Separate by metadata template for contracts vs other files.

Example

Active legal holds

How many files are currently under legal hold?

COUNT(*) FROM legal_hold_assignment WHERE status = 'active'

Grain: Active legal hold (snapshot)
Fields: LegalHoldAssignment.item_id, LegalHoldAssignment.status, LegalHoldAssignment.policy_id
Dimensions: legal_hold_status

Check status values per policy configuration.

Adapt: Separate by policy for different litigation cases.

Versions count

How many new file versions were created in the period?

COUNT(*) FROM file_version WHERE created_at IN period

Grain: File version
Fields: FileVersion.file_id, FileVersion.id, FileVersion.created_at
Dimensions: metadata_template

Use daily aggregates per folder at high volume.

Adapt: Separately flag auto-sync versions (desktop client).

Storage volume by template

How much storage volume sits in each metadata template?

SUM(size) FROM file_version JOIN metadata_instance ON metadata_instance.file_id = file_version.file_id GROUP BY metadata_instance.template_key

Grain: File version (latest per file)
Fields: FileVersion.size, MetadataInstance.template_key
Dimensions: metadata_template

Count only the latest version per file.

Adapt: Report large media templates (video/design) separately.

Collaboration grants per file

How many active collaboration grants does the average file have?

AVG(grant_count) FROM collaboration GROUP BY item_id

Grain: File (collaboration snapshot)
Fields: Collaboration.item_id, Collaboration.accessible_by_user_id, Collaboration.role
Dimensions: collaboration_role, job_title

Distinguish group vs individual collaborations — group expansion distorts counts.

Adapt: Evaluate "company"/external co-owner grants separately as a risk flag.

Formula overview

Same formulas as a table — copy into KPI cards or SQL drafts.

Label	Formula	Grain	Fields
Files uploaded	`COUNT(*) FROM file WHERE created_at IN period`	Uploaded file	File.id, File.created_at, File.parent_folder_id
Active legal holds	`COUNT(*) FROM legal_hold_assignment WHERE status = 'active'`	Active legal hold (snapshot)	LegalHoldAssignment.item_id, LegalHoldAssignment.status, LegalHoldAssignment.policy_id
Versions count	`COUNT(*) FROM file_version WHERE created_at IN period`	File version	FileVersion.file_id, FileVersion.id, FileVersion.created_at
Storage volume by template	`SUM(size) FROM file_version JOIN metadata_instance ON metadata_instance.file_id = file_version.file_id GROUP BY metadata_instance.template_key`	File version (latest per file)	FileVersion.size, MetadataInstance.template_key
Collaboration grants per file	`AVG(grant_count) FROM collaboration GROUP BY item_id`	File (collaboration snapshot)	Collaboration.item_id, Collaboration.accessible_by_user_id, Collaboration.role

Id	Label	Grain	Notes
`metadata_template`	Metadata template	metadata_instance.template_key	Templates differ per scope (enterprise vs global) — do not mix schemas in one report.
`collaboration_role`	Collaboration role	collaboration.role	Track viewer/editor/co-owner grants separately for access reviews.
`task_status`	Task status	task.status	Distinguish pending vs completed vs overdue.
`legal_hold_status`	Legal hold status	legal_hold_assignment.status	Active vs released holds drive different compliance KPIs.
`job_title`	Job title	user.job_title	Normalize free-text titles into a small role taxonomy for reporting.

PII hotspots

Inventory direct and quasi identifiers for this source — tag, restrict RAW, hash or drop before marts.

Entity	Fields	Classification	Stage treatment	Treatment
User	`name, login`	Direct	RAW: restrict access; Curated: id only; Mart: aggregates only	Enterprise user master data (login = email) — only with legal basis; keep id as key.
File owner reference	`owned_by display name`	Quasi	RAW: id only; Curated: id only; Mart: aggregates only	Owner display resolves to user PII — join only by id.
Full-text preview / content search index	`extracted file content`	Sensitive	RAW: never store; Curated: never; Mart: never	Full text can contain personal/contract data — never land it in the warehouse.
Preview renditions (thumbnails/watermark)	`preview image body`	Sensitive	RAW: never store; Curated: never; Mart: never	Preview binaries are derived content — no warehouse load.
Metadata instance values (customer-related templates)	`customer/contact name custom fields`	Quasi	RAW: workforce policy; Curated: aggregates only; Mart: aggregates only	Custom metadata values can contain third-party PII depending on the template — review per template.

DSDR discovery

Where personal data can hide beyond obvious contact fields — warehouse copies included.

Focus	Notes
Match keys	file.id, user.id, file_version.id, folder.id.
Primary objects	File, folder, collaboration, legal hold assignment, user — no file content, no full text.
Batch / export copies	Legal-hold exports and backup copies duplicate file metadata and owner PII.
Sandbox / test enterprise copies	Do not mix sandbox/test enterprise instances with prod DMS marts.

Source-specific guidance: what goes wrong, how to prevent it, what staff must know — plus where metadata lives and how to access it.

Data quality guides 6

Prevent issues in the source first. Warehouse checks are a safety net — not a substitute for staff habits.

High Box: document without owner

Problem: Documents without owner distort ACL and ownership KPIs.
Prevent: Create policy: owner required; sync orphan cleanup.
Staff must know: Admins: Set owner on create.
Checks: file.owner_id IS NULL
Fix in source: Assign owner or archive.
Warehouse safety net: Quarantine; Unknown-owner dim.

High Version without parent document

Problem: Versions without document join break storage and version counts.
Prevent: Always load parent id in extract.
Staff must know: Data: Version natural key includes parent.
Checks: file_version.file_id IS NULL OR file_id NOT IN dim_file
Fix in source: Re-extract; tombstones for deleted docs.
Warehouse safety net: Quarantine orphan versions.

High ACL without principal

Problem: Permission grants without user/group are dead security ballast.
Prevent: ACL write: principal required.
Staff must know: Security: No empty ACL rows.
Checks: permission.principal_id IS NULL
Fix in source: Clean ACLs.
Warehouse safety net: Flag orphan_acl; exclude from coverage KPIs.

Medium Document without folder/cabinet path

Problem: Documents without filing path distort taxonomy rollups.
Prevent: Default folder/cabinet on create.
Staff must know: Records: Filing path is a required dim.
Checks: file.folder_id IS NULL
Fix in source: Move into default filing location.
Warehouse safety net: Unknown-folder dim.

Medium Document without MIME/content type

Problem: Missing content type breaks storage-by-type KPIs.
Prevent: Upload: detect MIME; allowlist.
Staff must know: Platform: Persist MIME on ingest.
Checks: file.mime_type IS NULL AND status = 'active'
Fix in source: Backfill MIME.
Warehouse safety net: Unknown mime bucket.

Medium Duplicate document id in extract

Problem: Duplicate keys distort document counts.
Prevent: Idempotent load; check pagination.
Staff must know: Data: Natural key = file_id.
Checks: COUNT(*) GROUP BY file_id HAVING COUNT > 1
Fix in source: Re-extract.
Warehouse safety net: Dedup by latest _loaded_at.

MDM / duplicates 2

File Document duplicates / rename

Match keys: file_id, external_id, name+folder_path, content_hash_meta
Prevent in source: Stable object id; track renames.
Resolve in warehouse: Golden file_id; name SCD2.
Survivorship: Active document with latest version wins.

User User duplicates

Match keys: user_id, login, email (hashed), external_idp_id
Prevent in source: IdP as source of truth.
Resolve in warehouse: Golden user id; login SCD2.
Survivorship: Active user with latest activity wins.

Product settings to improve 3

Extract / Integration Metadata/ACL/version meta only — no file bodies

Why: Analytics without content leak and volume explosion.
How: Allowlist fields; never land binary endpoints in warehouse.

Security / Permissions ACL export as principal ids

Why: Access KPIs without cleartext mails in marts.
How: Principal id + role; email RAW restricted only.

Records / Retention Track retention labels and legal hold metadata

Why: Compliance KPIs without content bodies.
How: Hold/retention flags + dates; no binary.

Metadata: what exists & how to get it 4

Source-native catalogs for schema, picklists/properties and access paths — not a full dump.

Object / document model

Where in product: Box Content API / Events API
How to access: Inventory entities/properties; select allowlist.
Use for: Extract design, PII tags, join keys.
Watchouts: Never select file/OCR bodies.

ACL / permission model

Where in product: Box permissions / ACLs
How to access: Document principal, role, object scope.
Use for: Access facts, orphan-ACL DQ.
Watchouts: Inherited ACLs — SCD2 recommended.

Version metadata

Where in product: Box version history (meta only)
How to access: Version number, size, author id, timestamps.
Use for: Version counts, storage volume KPIs.
Watchouts: No binary streams.

Folder / category taxonomy

Where in product: Box folders / categories / cabinets
How to access: Export hierarchy and codes.
Use for: Taxonomy dims, path rollups.
Watchouts: Renames — SCD2.

Object overview with short descriptions and load guidance — not a full schema dump.

Table	Description	Grain	Load
`file` File	Box file object — name/size/parent folder; file content not loaded.	One file (id)	Load
`file_version` File version	Version history — sha1/size/created_at per file.	One version (id)	Optional
`folder` Folder	Box folder object — hierarchical name/ParentId.	One folder (id)	Load
`metadata_instance` Metadata instance	Metadata template instance attached to a file (schema-bound key/value pairs).	One metadata instance (id)	Optional
`collaboration` Collaboration	Collaboration grant (ACL) — role per file/folder and user or group.	One collaboration (id)	Optional
`task` Task	Box task (review/approval workflow) on a file — status/due-at/assignees.	One task (id)	Optional
`user` User	Box enterprise user — name/login/job title; core master data with PII.	One user (id)	Load
`legal_hold_assignment` Legal hold assignment	Legal hold policy assignment on a file or folder — status only, no held content.	One assignment (id)	Optional

Most important fields to load early — not a full schema dump.

Entity	Field	Role	Why
File	`File.id`	Key	File join
File	`File.name`	Dimension	File name
File	`File.size`	Measure	Storage volume
File	`File.created_at`	Measure	Creation date
File	`File.modified_at`	Measure	Last modified
File	`File.owned_by`	Dimension	Owner back-join (user)
File	`File.parent_folder_id`	Dimension	Folder back-join
FileVersion	`FileVersion.id`	Key	Version join
FileVersion	`FileVersion.file_id`	Dimension	File back-join
FileVersion	`FileVersion.sha1`	Dimension	Content hash dim
FileVersion	`FileVersion.size`	Measure	Storage volume per version
FileVersion	`FileVersion.created_at`	Measure	Version date
FileVersion	`FileVersion.modified_by`	Dimension	Editor back-join
Folder	`Folder.id`	Key	Folder join
Folder	`Folder.name`	Dimension	Folder name
Folder	`Folder.parent_id`	Dimension	Hierarchy
MetadataInstance	`MetadataInstance.id`	Key	Metadata join
MetadataInstance	`MetadataInstance.file_id`	Dimension	File back-join
MetadataInstance	`MetadataInstance.template_key`	Dimension	Template dim
MetadataInstance	`MetadataInstance.scope`	Dimension	Enterprise/global scope
Collaboration	`Collaboration.id`	Key	Collaboration join
Collaboration	`Collaboration.item_id`	Dimension	File/folder back-join
Collaboration	`Collaboration.accessible_by_user_id`	Dimension	User/group back-join
Collaboration	`Collaboration.role`	Dimension	Viewer/editor/co-owner dim
Task	`Task.id`	Key	Task join
Task	`Task.item_id`	Dimension	File back-join
Task	`Task.status`	Dimension	Task status
Task	`Task.due_at`	Measure	Due date
User	`User.id`	Key	User join
User	`User.name`	PII	Name / PII
User	`User.login`	PII	Login (email) / PII
User	`User.job_title`	Dimension	Role dim
LegalHoldAssignment	`LegalHoldAssignment.id`	Key	Assignment join
LegalHoldAssignment	`LegalHoldAssignment.item_id`	Dimension	File/folder back-join
LegalHoldAssignment	`LegalHoldAssignment.policy_id`	Dimension	Policy back-join
LegalHoldAssignment	`LegalHoldAssignment.status`	Dimension	Active/released dim

System and noise objects/fields you typically should not sync into the warehouse by default.

Skip tables / objects

Table	Category	Why
`File content / binary body (all versions)`	System	Full file bodies — never into the warehouse; version metadata suffices.
`Full-text preview / content search index`	System	Full-text PII — metadata is enough for storage KPIs.
`Preview renditions (thumbnails/watermark)`	System	Derived binaries — never into the warehouse.
`Full event stream (Box Events API bulk)`	System	Technical event stream — volume, little mart value; prefer meta/agg.

Skip fields / content

File content / binary body (all versions) — Never into the warehouse — cost/security risk.
Full-text preview / content search index — Can contain personal data.
Preview renditions (thumbnails/watermark) — Binaries without analytics value.
User name/login cleartext in default marts — id is enough for DMS KPIs.
Full event stream (Events API bulk) — Volume — meta/agg instead of full history.

Warehouse-neutral examples for landing (RAW) and curated models — copy and adapt to your dialect. Not vendor SOQL/API DDL.

Landing (RAW)

RAW Box document + user landing

Metadata only — no file bodies; email = PII in RAW.

-- Warehouse-neutral RAW from Box metadata extract
CREATE TABLE raw_box_file (
  file_id             VARCHAR,
  name                 VARCHAR,
  mime_type            VARCHAR,
  size_bytes           BIGINT,
  owner_id             VARCHAR,
  folder_id         VARCHAR,
  created_at           TIMESTAMP,
  modified_at          TIMESTAMP,
  status               VARCHAR,
  _loaded_at           TIMESTAMP
  -- no file body / OCR text
);

CREATE TABLE raw_box_user (
  user_id              VARCHAR,
  login                VARCHAR,
  email                VARCHAR, -- workforce PII
  display_name         VARCHAR,
  active               BOOLEAN,
  _loaded_at           TIMESTAMP
);

RAW version + ACL landing

Version meta + ACL principal ids — no binaries.

CREATE TABLE raw_box_file_version (
  version_id           VARCHAR,
  file_id             VARCHAR,
  version_number       INT,
  size_bytes           BIGINT,
  created_by           VARCHAR,
  created_at           TIMESTAMP,
  _loaded_at           TIMESTAMP
  -- no binary stream
);

CREATE TABLE raw_box_permission (
  permission_id        VARCHAR,
  file_id             VARCHAR,
  principal_id         VARCHAR,
  role                 VARCHAR,
  inherited            BOOLEAN,
  _loaded_at           TIMESTAMP
);

Curated

Curated fact version

Version KPI grain — parent required; no body.

CREATE TABLE curated_fct_box_version AS
SELECT
  v.version_id,
  v.file_id,
  v.version_number,
  v.size_bytes,
  v.created_by,
  v.created_at
FROM raw_box_file_version v
WHERE v.file_id IS NOT NULL;

Curated dim document / ACL fact

Doc dim without owner cleartext; ACL as ids.

CREATE TABLE curated_dim_box_file AS
SELECT
  file_id,
  mime_type,
  size_bytes,
  owner_id,
  folder_id,
  created_at,
  modified_at,
  status
  -- omit email/display_name joins in default dim
FROM raw_box_file;

CREATE TABLE curated_fct_box_permission AS
SELECT
  permission_id,
  file_id,
  principal_id,
  role,
  inherited
FROM raw_box_permission
WHERE principal_id IS NOT NULL;

Curated measure SELECTs

Documents created, versions, storage, ACL grants — adapt period filters.

-- Documents created in period
SELECT COUNT(*) AS documents_created
FROM curated_dim_box_file
WHERE created_at BETWEEN :period_start AND :period_end;

-- Versions count
SELECT COUNT(*) AS versions_count
FROM curated_fct_box_version
WHERE created_at BETWEEN :period_start AND :period_end;

-- Storage volume (bytes)
SELECT COALESCE(SUM(size_bytes), 0) AS storage_bytes
FROM curated_dim_box_file
WHERE status = 'active';

-- ACL grants per document (avg)
SELECT AVG(grant_count) AS avg_acl_grants
FROM (
  SELECT file_id, COUNT(*) AS grant_count
  FROM curated_fct_box_permission
  GROUP BY file_id
) g;

Tools

Open related Binom-Tools workflows to adapt formulas, PII and measures for this source.

Playbooks

Related governance stories for modelling, PII and measure design around this source.

Vendor help on Resources Official docs, governance and learning paths for this source. Open Resources →