About

### **About the Project** We’re a **Canadian pharmaceutical organization** seeking an **experienced Analytics Engineer** to help us **modernize our data warehouse** and streamline our **analytics stack**. Our current environment is hosted on **Google BigQuery** and integrates multiple data sources — commercial, clinical trials, manufacturing quality, finance, and real-world evidence (RWE). Over time, data pipelines and tables have grown organically, resulting in high query costs, inconsistent data models, and limited governance. We’re looking for a professional who can **design scalable ELT pipelines with dbt Cloud**, optimize **BigQuery performance**, and deliver clean, trusted data models powering **Power BI dashboards** for strategic decision-making. ### **Key Objectives** ### **1. Data Audit & Assessment** - Map all data sources, flows, and dependencies across our BigQuery ecosystem. - Identify issues related to schema design, partitioning, and cost management. - Evaluate compliance and audit trail requirements (GxP, PHIPA/PIPEDA). - Propose a pragmatic roadmap for **data warehouse modernization**. ### **2. ELT Modeling & Data Transformation (dbt Cloud)** - Design a **robust dbt Cloud project** with best practices (naming, macros, packages). - Implement **data tests** (`unique`, `not null`, `relationships`) and **automated documentation** (`dbt docs`). - Enable **CI/CD pipelines** for dbt jobs and implement environment-specific deployment (dev, staging, prod). - Introduce data quality alerts and lineage visibility. ### **3. BigQuery Optimization & Data Governance** - Redesign queries and models to improve cost and performance efficiency. - Implement table **partitioning, clustering**, and **incremental materialization**. - Build a **FinOps dashboard** tracking BigQuery usage, costs, and hotspots. - Set up **governance practices** for cataloging, lineage, and change control. ### **4. Business Intelligence Layer (Power BI)** - Create **certified Power BI datasets** aligned with the dbt data models. - Standardize metrics and DAX calculations for business teams. - Deliver 2–3 key **Power BI dashboards** (e.g. Sales Analytics, Manufacturing Quality, Market Access). - Ensure strong **refresh performance and semantic modeling** practices. ### **5. Enablement & Knowledge Transfer** - Deliver documentation and training to data analysts and business users. - Provide **best practices** for ongoing maintenance, testing, and scaling. - Establish a sustainable, auditable, and compliant data workflow. --- ### **Deliverables** - Full **data audit report** (architecture, risks, and recommendations). - Production-ready **dbt Cloud repository** (models, tests, documentation, CI/CD). - Certified **Power BI datasets and dashboards**. - **BigQuery FinOps** and governance dashboards. - Documentation & knowledge transfer package. --- ### **Tech Stack** - **Google Cloud Platform (BigQuery)** — core data warehouse - **dbt Cloud** — ELT modeling & documentation - **Power BI (Service + Desktop)** — business intelligence & visualization - **Git / CI tools / optional orchestration** (Airflow, Dagster, or similar) - Typical data sources: **Veeva CRM, ERP, LIMS/MES, clinical trial data, RWE** ### **Required Experience** - 4+ years of experience as an **Analytics Engineer** or **Data Engineer** in a cloud-based environment. - Strong knowledge of **BigQuery**, **SQL optimization**, and **dbt Cloud best practices**. - Proven experience building and maintaining **Power BI dashboards**. - Background in **data warehouse modernization** and **data governance frameworks**. - Understanding of **compliance requirements** (GxP, PHIPA/PIPEDA, or HIPAA). - Excellent communication and documentation skills. --- ### **Engagement Details** - **Contract / Freelance mission** (full-time or part-time, to be discussed). - **Remote** — preference for availability within **Eastern Time (Canada)** hours. - **Duration:** 8–12 weeks (extendable). - **Start:** ASAP. Please include in your proposal: - Short description of your **approach or methodology** for ELT design. - **Examples or screenshots** (anonymized) of dbt/BigQuery projects. - **References or case studies** (if available). - Your **hourly rate** or **fixed project quote**. ### **Success Criteria** - Reduction in **BigQuery cost per query** and improvement in performance. - High **data test coverage and documentation quality** in dbt Cloud. - Consistent **Power BI metrics adoption** across departments. - Stable refresh performance and sustainable data governance model. --- 💡 **Keywords for discoverability:** BigQuery ELT, dbt Cloud, Power BI dashboards, Analytics Engineer, Data Warehouse Modernization, Data Governance, FinOps, Cloud Data Engineering, GCP, Pharma Analytics, Healthcare Data, PIPEDA Compliance, GxP Data.