bigquery-cleaner 0.1.7

CLI tool to clean up your BigQuery old and unused datasets and tables.

🧹 BigQuery Cleaner

BigQuery Cleaner is a powerful CLI tool designed to help you declutter your Google BigQuery environment. It identifies tables that haven't been queried recently and provides safe mechanisms to rename or prepare them for deletion.

---

🚀 Quick Start

Get up and running in seconds:

# 1. Install via uv (recommended)
uv tool install bigquery-cleaner

# Or via pip
pip install bigquery-cleaner

# 2. Find unused tables (older than 30 days and not queried)
bigquery-cleaner list-unused-tables --project your-gcp-project --all-datasets --days 30

---

✨ Features

• 🔍 Unused Table Detection: Scans INFORMATION_SCHEMA.JOBS to find tables that aren't being used.
• 📊 Storage Insight: Displays table sizes in GB and provides per-dataset and grand total summaries.
• 📂 Multi-Dataset Support: Target specific datasets, exclude others, or scan your entire project.
• 🏷️ Safe Renaming: Dry-run mode allows you to see what would happen before making changes.
• 🔄 Easy Revert: Renamed a table by mistake? Revert it easily with the revert-renamed-tables command.
• 🗑️ Permanent Cleanup: Use delete-tables to remove suffixed tables once you've confirmed they are no longer needed.
• 🧹 Dataset Cleanup: Remove empty datasets that no longer contain any tables or views using delete-empty-datasets.
• ⚙️ Configurable: Use a cleaner.toml file to save your project defaults and lookback windows.
• ⚡ Built with Speed: Powered by uv, Typer, and Rich for a beautiful, fast terminal experience.

---

📋 Prerequisites

• Python 3.10+
• uv package manager installed.
• Google Cloud Credentials: Configured via Application Default Credentials (ADC).

  gcloud auth application-default login
  

---

🛠️ Installation

Using uv (Recommended)

uv tool install bigquery-cleaner

Using pip

pip install bigquery-cleaner

From Source (Development)

# Clone the repository
git clone https://github.com/elvainch/bigquery-cleaner.git
cd bigquery-cleaner

# Sync dependencies and install the tool
uv sync
uv tool install .

---

📖 Usage Guide

Help Command

Every command and sub-command supports the --help flag for detailed information on available options.

Example: bigquery-cleaner list-unused-tables --help

Run bigquery-cleaner --help to see all available commands.

Connectivity Check

Ensure your credentials and project access are working:

bigquery-cleaner ping --project YOUR_PROJECT

Exploration

List available datasets and tables:

# List all datasets
bigquery-cleaner datasets --project YOUR_PROJECT

# List tables in specific datasets
bigquery-cleaner tables --datasets dataset1,dataset2 --project YOUR_PROJECT

Identifying Waste

The core functionality to find old, unreferenced tables:

# List unused tables across all datasets
bigquery-cleaner list-unused-tables --all-datasets --days 90

Cleanup Operations

Safely rename unused tables with a suffix:

# Dry run first!
bigquery-cleaner rename-old-tables --all-datasets --days 90 --dry-run

# Perform the rename
bigquery-cleaner rename-old-tables --all-datasets --days 90

# Delete renamed tables after verification
# Dry run first!
bigquery-cleaner delete-tables --all-datasets --suffix "_renamed_20241225" --dry-run

# Perform the deletion
bigquery-cleaner delete-tables --all-datasets --suffix "_renamed_20241225"

# Remove empty datasets
bigquery-cleaner delete-empty-datasets --all-datasets

---

⚙️ Configuration

Tired of typing the same flags? Create a cleaner.toml file in your project root. All CLI options can be persisted here:

[bigquery_cleaner]
# GCP Project ID (defaults to ADC project if omitted)
project = "your-gcp-project"

# List of datasets to scan (dataset names only)
datasets = ["dataset1", "dataset2"]

# List of datasets to ignore
exclude_datasets = ["logs_dataset", "temp_staging"]

# Extra projects whose INFORMATION_SCHEMA.JOBS history should count as usage.
# The main project is always included automatically.
jobs_projects = ["analytics-project", "bi-project"]

# If true, scans all datasets in the project (overrides 'datasets' list)
all_datasets = true

# Lookback window in days for identifying unused tables (default: 30)
days = 60

# Suffix used for renaming and identifying tables for deletion (default: _renamed_YYYYMMDD)
rename_suffix = "_old_backup"

# Default behavior for commands (true = dry run by default)
dry_run = false

# Logging level (DEBUG, INFO, WARNING, ERROR)
log_level = "INFO"

# BigQuery Location (e.g. "US", "EU"). 
# Note: Multi-dataset mode usually auto-detects this.
location = "US"

Then run with:

bigquery-cleaner list-unused-tables --config cleaner.toml

---

📝 Notes

• Detection Logic: The list-unused-tables command identifies tables created more than N days ago that do not appear in INFORMATION_SCHEMA.JOBS.referenced_tables within that same window.
• Cross-Project Usage Checks: Set jobs_projects or pass --jobs-projects to also scan query history from other projects that may read the same tables.
• Rich Output: All results are displayed in beautiful, sortable tables thanks to the Rich library. Includes total table counts and storage size summaries.
• Linting & Quality: The project uses Ruff for fast linting and formatting.

---

Check out the project on:

• PyPI: https://pypi.org/project/bigquery-cleaner/
• GitHub: https://github.com/elvainch/bigquery-cleaner

Developed by Alan Vainsencher.