BigDAG: A Simple Tool for Managing BigQuery Workflows

Hey everyone,

I wanted to share a tool I've been working on called BigDAG. It's born out of my own frustrations with managing data transformations in BigQuery, and I figured others might find it useful too.

The Core Problem: BigQuery Complexity

BigQuery is fantastic for data analysis, but when you start building real pipelines, things can get messy. You end up with:

• SQL Files Everywhere: Queries for tables, views, and transformations scattered across different files.


• Manual Dependency Tracking: Figuring out the correct order to run scripts becomes a chore.


• Repetitive bq Commands: Creating datasets, tables, and views involves running similar commands repeatedly.


• Deployment Headaches: Pushing changes to BigQuery can be a manual and error-prone process.

I needed a way to bring some order to this chaos, to define my workflows as code, and to automate the deployment process. That's the motivation behind BigDAG.

BigDAG: A Practical Solution

BigDAG is a Python tool that helps you manage data workflows in BigQuery. It's not trying to be a full-fledged data orchestration platform; it's a practical tool to solve a specific problem. Here's the basic idea:

1. Organize Your Files: You structure your SQL scripts, schema definitions, and external table definitions in a folder. This folder represents your data pipeline.


2. Dependency Detection: BigDAG automatically detects dependencies between your SQL scripts by looking at the queries. If a view uses another table, BigDAG knows it needs to be created first.


3. Manual Overrides: If automatic detection isn't enough, you can specify dependencies in a deps.yaml file.


4. Simple CLI: BigDAG provides a command-line interface (CLI) to execute your workflows. You can create, update, or delete objects in BigQuery with a single command.


5. Basic Templating: BigDAG uses simple templating to inject your project ID and dataset name into your SQL queries.

Key Features

• Dependency Management: Automatically figures out the order to run your scripts.


• Code-Based Workflows: Define your pipelines using files and YAML.


• CLI for Deployment: Deploy changes to BigQuery with a single command.


• Dry Run Option: See the commands that will be executed before running them.


• Recreation Support: Easily recreate your entire dataset and all objects.

A Simple DAG Example

Let's look at a simple example of how you might structure your DAG folder:

my_dag/

├── raw/

│   └── sales.sheet.def.json

│   └── sales.sheet.schema.json

├── trusted/

│   └── sales.view.sql

└── refined/

    └── monthly_sales.table.sql

Here's what the files might contain:

my_dag/raw/sales.sheet.def.json:

{

    "sourceFormat": "GOOGLE_SHEETS",

    "sourceUris": [

      "https://docs.google.com/spreadsheets/d/FAKE_SPREADSHEET_ID"

    ],

    "googleSheetsOptions": {

        "range": "sales!A1:Z",

        "skipLeadingRows": 1

    }

}

my_dag/raw/sales.sheet.schema.json:

{

  "fields": [

    {"name": "sale_date", "type": "DATE", "mode": "REQUIRED"},

    {"name": "product_id", "type": "STRING", "mode": "REQUIRED"},

    {"name": "amount", "type": "NUMERIC", "mode": "REQUIRED"}

  ]

}

my_dag/trusted/sales.view.sql:

-- SQL script for creating the sales view

SELECT

    *

FROM

    raw_sales;

my_dag/refined/monthly_sales.table.sql:

-- SQL script for creating the monthly_sales table

SELECT

    date_trunc('month', sale_date) AS month,

    SUM(amount) AS total_sales

FROM

    `{{project_id}}.{{dataset}}.trusted_sales`

GROUP BY

    month;

In this example:

• sales.sheet.def.json and sales.sheet.schema.json define an external table based on a Google Sheet.


• sales.view.sql creates a view on top of the raw sales data.


• monthly_sales.table.sql creates a table based on the trusted sales view.

BigDAG will automatically infer that monthly_sales.table.sql depends on trusted_sales, and that trusted_sales depends on raw_sales.

How to Use It (Quickly)

1. 
   

   Install:
   
   

   
   

   git clone https://github.com/feroult/bigdag.git
   
   cd bigdag
   
   pip install -e .
   
   

   
   
   



2. Set up your DAG folder: Organize your SQL scripts and definitions.



3. 
   

   Run the CLI:
   
   

   
   

   bigdag --folder path/to/your/dag --project your_project_id --dataset your_dataset_name
   
   

   
   
   

The Goal: Making Life Easier

BigDAG is about making data workflows more manageable. It's about:

• Reducing Manual Errors: Automating repetitive tasks to avoid mistakes.


• Improving Workflow Clarity: Making it easier to understand and maintain your data pipelines.


• Saving Time: Spending less time on infrastructure and more time on analysis.

Check it Out

If you're dealing with similar challenges in BigQuery, you might find BigDAG useful. It's open-source, so feel free to take a look, contribute, or just use it as is.

https://github.com/feroult/bigdag

Hope it helps!