Skip to content
All case studies
Multi-source ELT Foundation Build

Building a unified data warehouse

A marketing agency in the UAE with critical business data scattered across Greenhouse, Hubspot, Salesforce, Jira, Freshdesk and more. Pulled it all into one place.

8+

source systems unified

1

central source of truth

Cost-effective

and scalable on BigQuery

The client

A growing marketing agency based in the UAE, using a wide variety of best-of-breed tools across recruitment, sales, support, and engineering. The team needed a single place to query data across functions.

The problem

Critical business data was dispersed across multiple platforms, blocking seamless decision-making. Recruitment and people data lived in Greenhouse, Lever, and Hibob. Lead data was in Hubspot and Salesforce. Engineering tickets in Jira. Support in Freshdesk and Freshchat. Web traffic in Google Analytics. Cross-functional questions required copy-pasting between tools.

Architecture

flowchart LR R["Recruitment
Greenhouse, Lever, Hibob"] -->|Fivetran| W S["Sales / Marketing
Hubspot, Salesforce"] -->|Fivetran| W E["Engineering
Jira"] -->|Fivetran| W C["Customer Support
Freshdesk, Freshchat"] -->|Fivetran| W G["Web traffic
Google Analytics"] -->|Google API| W W[("BigQuery warehouse
fact + dim tables")] W --> RP["Aggregated
reporting layer"] RP --> A["Analysts &
dashboards"] style R fill:#f0e8db,stroke:#1a1a2e style S fill:#f0e8db,stroke:#1a1a2e style E fill:#f0e8db,stroke:#1a1a2e style C fill:#f0e8db,stroke:#1a1a2e style G fill:#f0e8db,stroke:#1a1a2e style W fill:#d8c8ed,stroke:#1a1a2e style RP fill:#b6e0c2,stroke:#1a1a2e style A fill:#f4c8a8,stroke:#1a1a2e

How it ran

How critical business data scattered across 8+ tools became one queryable warehouse.

Step 1

Discovery

Stakeholder interviews across departments to understand the actual data requirements and desired outcomes.

Step 2

Mapping

Analysed each source system's schema. Built a comprehensive data mapping plan showing how elements relate.

Step 3

Tool selection

Market research on ELT and warehousing tools. Presented options to decision-makers, mutual decision on stack.

Step 4

Pipelines

Implemented BigQuery + Fivetran. Configured connections to all source systems for automated extraction and loading.

Step 5

Security

Role-based access control across the warehouse. Sensitive data segregated by department and role.

Step 6

Reporting

Built fact + dimension tables and an aggregated reporting layer that analysts can query without rebuilding joins.

What I did

  • 01.Collaborated closely with stakeholders from each department to understand specific data requirements and desired outcomes.
  • 02.Analysed the data structures and schemas of each source system and developed a comprehensive data mapping plan.
  • 03.Conducted market research for ELT and warehousing tools, presented options to decision-makers, and made a mutual decision.
  • 04.Implemented BigQuery and Fivetran for the organisation.
  • 05.Configured connections between source systems and Fivetran to automate extraction, loading, and transformation.
  • 06.Loaded all Google Analytics data into BigQuery using the Google APIs.
  • 07.Implemented role-based access security for the warehouse.
  • 08.Created the necessary fact and dimension tables, plus an aggregated reporting layer in BigQuery.

InteractiveSee it run

The question that used to need five tools

By hand

0m

copy-paste across the source tools

    From the warehouse

    1 query 
    
            
    
          
    
        
    
      
    
    
    

    
    
    
      
    
        
    Result
    
        
    What the unified warehouse unlocked.
    
      
    
      
    

        
    
          
          
    Foundations
    
          
    A clean base layer for both dashboard creation and ad-hoc data analysis. Future BI work builds straight on top of it.
    
        
    

        
    
          
          
    Streamlined decision-making
    
          
    A single repository plus a clean reporting layer means leadership stops triangulating between tools and starts making the call.
    
        
    

        
    
          
          
    Cost-effective and scalable
    
          
    BigQuery and Fivetran handle increasing data volumes without re-architecting. The bill scales with usage as volumes grow.
    
        
    

      
    
    
    

    
    
      
    Tools used
    
      
    BigQuery
Fivetran
Greenhouse
Lever
Hibob
Hubspot
Salesforce
Jira
Freshdesk
Freshchat
Google Analytics
    
    
    

    
    
      
    
        
        
    "Bernadett is a highly professional and competent data consultant. She proactively understood our needs and led an implementation that more than fit our requirements. Despite moving goalposts and some tricky situations, she was great at adapting her solutions!"
    
        
    
          
    Hisham Ali
    
          
    Director of Data & AI
    
        
    
      
    
    
    

  
    

  
    
    
    
      
    A similar problem in your stack?
    
      
    Send me the rough shape of it. I'll figure out scope on a 30-min call.
    
      
        Book a call