dbt Analytics Project with BigQuery

Project Overview

This project uses dbt to transform raw data in BigQuery into clean, analytics-ready tables. The dataset was generated using Faker for testing and development purposes. Key components include:

• Staging models: Standardize and clean raw data

• Core marts: Fact and dimension tables

• Analytics marts: Pre-aggregated metrics for reporting, such as:

  • Monthly Revenue
  • Customer Lifetime Value
  • Top Products

• Power BI dashboards can be built on top of these marts

---

Project Structure

dbt_project/
│── models/
│   ├── staging/
│   ├── marts/
│   │   ├── core/
│   │   └── analytics/

---

Prerequisites

• Python (>=3.9)
• dbt-core and dbt-bigquery
• Access to BigQuery (project ID, dataset, and read/write permissions)
• Power BI Desktop (optional)

---

Setup Instructions

1. Clone the repository

git clone <repo_url>
cd dbt_project

2. Configure dbt profile for BigQuery Edit ~/.dbt/profiles.yml:

my_project:
  target: dev
  outputs:
    dev:
      type: bigquery
      project: your-gcp-project
      dataset: your_dataset
      threads: 4
      timeout_seconds: 300

3. Install dependencies

pip install dbt-core dbt-bigquery

4. Run dbt

dbt deps        
dbt seed        
dbt run         
dbt test       

5. Generate documentation

dbt docs generate
dbt docs serve

---

Verifying Data & Aggregations

• Query your materialized tables in BigQuery to check metrics:

SELECT DATE_TRUNC(order_date, MONTH) AS month,
       SUM(order_amount) AS monthly_revenue
FROM `your_project.your_dataset.fct_orders`
GROUP BY month
ORDER BY month;

• Use sample queries on small slices or partitions to minimize BigQuery costs.

---

Power BI Integration

1. Open Power BI Desktop → Get Data → Google BigQuery
2. Sign in with your Google account or service account key
3. Select your BigQuery project and dataset
4. Load analytics marts (monthly_revenue, customer_lifetime_value, top_products) for dashboards
5. Recommended: Use aggregated tables to reduce query costs

---

Notes

• The dataset was generated using Faker for testing purposes.
• Always filter or limit queries to reduce costs when verifying data
• Consider dbt snapshots for slowly changing dimensions
• Pre-aggregated analytics tables make dashboards faster and cheaper

---