One Person Many Roles: Designing a Unified Person Dimension in Google BigQuery

When building our internal data warehouse, we quickly noticed that people and companies sit at the centre of most business processes and that the same person or company often plays multiple roles over time.

A consultant might be a staff member one day, a project lead the next and a contract signatory when closing a deal. Marketing contacts become clients, clients become referral partners and candidates sometimes become employees.

While these roles share common attributes such as name, title and contact details, others are role-specific. It is tempting to model each role separately with dimensions for staff, contacts or candidates but this quickly leads to silos, duplication and the inevitable question of which table holds the correct email address.

Instead, we modelled people in BigQuery using a single unified person dimension that captures every role a person plays across the business.

The problem with role-specific tables

Most analytics warehouses grow organically. You start with a CRM and build a contacts dimension. HR adds employee data so you create an employees dimension. Recruiting wants candidates, finance wants signatories and before long you are maintaining several tables that all store the same basic fields.

This fragmentation causes predictable problems:

• Duplicate records with conflicting data
• Identity confusion across systems
• Complex queries that require unions instead of joins
• Higher maintenance as changes cascade across models
• An incomplete view of how a person relates to the business over time

Most importantly, it breaks the relationship history. When a prospect becomes a client, their marketing journey disappears. When a contractor becomes an employee, their project history is left behind.

A unified approach using BigQuery STRUCTs

We use Google BigQuery as our data warehousing managed service and instead created a single persons_dim table that accommodates every role a person could play in our business.

Uses boolean flags for common filters. Queries like WHERE is_staff = TRUE are cleaner and faster than WHERE staff_attributes IS NOT NULL.

Stores current roles as an array. The current_roles field enables queries like WHERE 'PARTNER_CONTACT' IN UNNEST(current_roles).

Groups related attributes in STRUCTs. Rather than 20 columns prefixed with staff_, nest them in staff_attributes.

CREATE TABLE `analytics.persons_dim` (
  person_pk STRING NOT NULL,
  person_name STRING,
  person_phone STRING,
  all_emails ARRAY<STRING>,
  person_linkedin_url STRING,
  is_staff BOOL DEFAULT FALSE,
  is_contractor BOOL DEFAULT FALSE,
  is_client_contact BOOL DEFAULT FALSE,
  is_partner_contact BOOL DEFAULT FALSE,
  is_prospect BOOL DEFAULT FALSE,
  is_candidate BOOL DEFAULT FALSE,
  current_roles ARRAY<STRING>,
  primary_role STRING,
  staff_attributes STRUCT<
    job_title STRING,
    team_name STRING,
    employment_start_date DATE,
    weekly_capacity INT64,
    default_hourly_rate INT64,
    cost_rate INT64,
    manager_person_fk STRING
  >,
  crm_attributes STRUCT<
    lifecycle_stage STRING,
    lead_status STRING,
    lead_source STRING,
    conversion_event_name STRING
  >,
  candidate_attributes STRUCT<
    resume STRING,
    current_application_stage STRING,
    is_referred BOOL
  >,
  partner_attributes STRUCT<
    partner_company_fk STRING,
    is_primary_contact BOOL,
    has_signatory_authority BOOL
  >
);

Uses bridge tables for contextual relationships. A person’s role on a specific project or deal isn’t an attribute of the person — it’s a relationship.

Maintains a primary role. We calculate primary_role based on a precedence hierarchy (staff > contractor > partner > client > prospect).

How the person dimension joins to fact tables

With a unified person dimension, every fact table that involves people can join through a single, consistent foreign key.

This consistency transforms cross-functional analysis. Instead of building complex unions or maintaining role-specific join logic, every query follows the same pattern.

Bridge tables for contextual roles

Some roles however require more context recorded for them in-order for them to be useful in making decisions; a person isn’t just “a project team member” in the abstract; they’re a team member on specific projects with specific allocations.

These bridge tables let us track the same person across multiple projects or deals with different roles on each.

A unified person view for more effective messaging

Looker, through its tight integration with Google BigQuery, makes it easy to import this unified person dimension table schema into your LookML project.

The look below, for example, is filtered to only show people where has_multiple_roles = Yes and sorted by role count descending.

This helps us spot people like:

• Partners who are former staff, and who would know our culture and working style intimately
• Clients who became referral sources, our strongest advocates worth nurturing
• Prospects who are also community members, who are already engaged with our content and forums such as the dbt Labs Slack
• Candidates who are also prospects, who have a dual interest in working with/for us

Altogether: a foundation for people analytics

A unified person dimension such as this one transforms what’s possible with your analytics.

The investment in consolidating person data pays dividends across every function as sales gets complete contact history, delivery sees full project context and finance can track the same individual from prospect through to paid invoice.

Interested? Find out more!

Rittman Analytics is a boutique data analytics consultancy that helps ambitious, digital-native businesses scale-up their approach to data, analytics and generative AI.

We’re authorised delivery partners for Google Cloud along with dbt Labs, Cube, Dagster, Embeddable, SnowCatCloud and Fivetran.

If you’re looking for some help and assistance with your data or AI initiative, contact us now to organise a 100%-free, no-obligation call — we’d love to hear from you!

Recent Posts

RSS Feed

Published Year

Tag Cloud