data-autocleaning

Data Autocleaning Skill

Automated data profiling, quality assessment, and transformation for data sourced from BigQuery or Google Cloud Storage (GCS).

When to Use

[!IMPORTANT]

You MUST use this skill for ANY task where the source is BigQuery or GCS — including seemingly simple operations like "move data" or "copy table".

• Apply to all operations on new and existing sources: copying, moving, appending, ingesting, or extracting data.
• Apply to the source node specifically, not to subsequent pipeline steps.
• Never skip Dataplex profiling (Steps 1 and 3). Always use Dataplex — not ad-hoc BigQuery profiling.

Task Execution Workflow

Step 1: Preliminary Checks (Before Implementation Planning)

Perform these checks before generating the implementation_plan.md.

1. Check Eligibility — You MUST confirm the source is a BigQuery table or GCS source.

2. Gather Data Profile via Dataplex:

   • GCS sources: For GCS sources, you MUST create an external table first before running the dataplex scan.

   • Wait for results: You MUST NOT proceed until the Dataplex profile is available, unless user scan approval was denied.

   • Use the profile as input for cleansing and schema mapping decisions. The transformations MUST NOT be finalized before profile information is available (unless scan was denied).

   • Commands:

     1. Obtain user approval: Present the scripts/dataplex_scanner.py scan command to the user and obtain explicit approval before executing it. Use the following template to present the command:
        • Command: python3 scripts/dataplex_scanner.py ... (Fetch full arguments from step 6 below)
        • Summary: The script automates Dataplex data profiling. It checks table sizes, applies dynamic sampling for large tables (>1M rows) to reduce costs, skips empty tables, executes concurrent scans for multiple tables, and polls for results automatically.
        • Value Add: Enables deep data analysis (null rates, distinct values, distributions) allowing data-driven cleansing decisions. It helps identify hidden anomalies (garbage values, format variance) to guide accurate transformations and verifies that the cleaning logic resolves them without introducing regressions.
        • Scope: The approval obtained here covers all executions of this scanner script for this task (including verification steps).
     2. Run the scripts/dataplex_scanner.py script located in the same directory as this SKILL.md file. This script handles concurrent scan creation, dynamic sampling for large tables, and polling for results. Use --help to learn more.
     3. The script will save the full results as JSON files in the specified output directory.
     4. [!IMPORTANT] The location MUST be a specific Google Cloud region like us-central1; multi-regions like us are not supported in Dataplex scan.
     5. If there are multiple tables to scan, provide them all in the --tables argument to run them concurrently.
     6. Use the following command template: