7. Data Quality & Preparation

Understanding Data Quality

Data quality directly impacts the reliability of your analysis. RangerIO automatically assesses quality across multiple dimensions and helps you fix issues before using the data.

Quality Workflow

Automated Quality Process:
1. Import triggers automatic quality scan
2. Report highlights issues across 4 dimensions
3. Prepare Wizard guides you through fixes
4. Cleaned data is validated and ready

Quality Dimensions

1. Completeness

What it measures:
- Missing values (NULL, empty strings)
- Required fields populated
- Data coverage

Quality indicators:
- ✅ Excellent: <1% missing
- ⚠️ Good: 1-5% missing
- ❌ Poor: >5% missing

Common issues:
- Optional fields left blank
- System errors during data collection
- Incomplete data exports

2. Accuracy

What it measures:
- Data type compliance
- Format validation (emails, phones, dates)
- Range checks (min/max values)
- Domain validation

Quality indicators:
- ✅ Excellent: <0.1% errors
- ⚠️ Good: 0.1-1% errors
- ❌ Poor: >1% errors

Common issues:
- Manual entry mistakes
- Format inconsistencies
- Invalid values (negative ages, future dates)

3. Consistency

What it measures:
- Cross-field relationships
- Business rule compliance
- Referential integrity

Quality indicators:
- ✅ Excellent: No conflicts
- ⚠️ Good: Minor inconsistencies
- ❌ Poor: Major conflicts

Common issues:
- End date before start date
- Conflicting status fields
- Broken foreign key relationships

4. Uniqueness

What it measures:
- Duplicate records
- Primary key violations
- Fuzzy duplicates

Quality indicators:
- ✅ Excellent: No duplicates
- ⚠️ Good: <1% duplicates
- ❌ Poor: >1% duplicates

Common issues:
- Data merged from multiple sources
- System bugs creating duplicates
- Manual entry of existing records

Viewing Quality Reports

Quality Dashboard

After importing data, access the quality dashboard:

💡 UI Location: Select your data source, then click the "Data Quality" tab in the main area

1. Select your data source from the sidebar
2. Click "Data Quality" tab in the main content area
3. Review overall score (A-F grade)

Dashboard sections:
- Summary: Overall quality metrics
- By Dimension: Scores for each quality dimension
- By Column: Issue count per column
- Detailed Issues: List of specific problems

Quality Score

Overall grade calculation:
- A (90-100%): Excellent quality
- B (80-89%): Good quality, minor issues
- C (70-79%): Acceptable, needs attention
- D (60-69%): Poor quality, significant issues
- F (<60%): Critical quality problems

Grade factors:
- Completeness weight: 30%
- Accuracy weight: 30%
- Consistency weight: 20%
- Uniqueness weight: 20%

Issue Severity

Each issue is categorized:

• 🔴 Critical: Data unusable or seriously flawed
• 🟡 Warning: Issue exists but data still usable
• 🟢 Info: Observation, not necessarily a problem

Privacy Scan

What the Privacy Scan finds

Every imported file runs through an entity-aware scan backed by a local NER model. It identifies sensitive content across these classes:
- Names and personal identifiers
- Addresses (physical and email)
- Phone numbers
- Government IDs (SSN, passport, driver's license, tax IDs)
- Financial identifiers (credit cards, bank/account numbers)
- Dates, locations, and organizations
- Custom patterns (user-defined regex for organization-specific identifiers)

Confidence Tiers

Each finding is tiered so you're not drowning in noise:

• Confirmed — High-confidence sensitive value
• Suspected — Likely sensitive; review recommended
• Dismissed — Filtered as a structural false positive by the sanity gate

Detection report shows:
- Column name with the finding
- Entity type detected
- Number of occurrences
- Sample (masked for safety)
- Tier (confirmed / suspected / dismissed)

Actions per Column

One click on the column dot lets you choose how that column is handled. Overrides persist across rescans:

1. Leave as-is — Keep the original values; no masking applied.

2. Mask in place — Partial redaction (e.g., XXX-XX-1234) or full masking. Reversible: the mapping to the original lives only on your device.

3. Replace with stable stand-ins — Swap real values for consistent fake values so joins and grouping still work.

4. Remove — Drop the column entirely. Irreversible at export; use when the field isn't needed downstream.

5. Override — Promote a "suspected" finding to "confirmed," or dismiss a false positive. Your override is remembered.

Sensitivity Levels

You can mark overall dataset sensitivity for governance and to drive export warnings:

• 🔓 Public: Safe to share externally
• 🔒 Internal: Company use only
• 🔐 Confidential: Restricted access
• 🚨 Highly Confidential: Maximum protection — RangerIO will warn before any Stage 2 handoff

Prepare Wizard

The Prepare Wizard guides you through data cleaning and transformation in a structured workflow.

Starting the Wizard

1. Click "Prepare" in left sidebar
2. Select data source(s) to prepare
3. Click "Start Wizard"

Step 1: Intent Capture

Define your goal:
- What do you want to achieve?
- What will you use this data for?
- Any specific requirements?

Example intents:
- "Clean customer data for CRM import"
- "Merge sales data from multiple regions"
- "Remove PII for public dataset"
- "Standardize date and phone formats"

AI assistance:
- Suggests relevant transformations
- Identifies potential issues
- Recommends cleaning steps

Step 2: Scope Selection

Choose what to include:

Data Sources:
- ☑ Select which sources to use
- Combine multiple sources
- Preview selected data

Columns:
- ☑ Include/exclude columns
- Rename for clarity
- Reorder columns

Rows:
- Filter by conditions
- Remove outliers
- Sample for testing

Step 3: Consolidation

If using multiple sources:

Merge Strategy:
- Union: Stack datasets (same columns)
- Join: Combine by key field
- Lookup: Enrich from reference table

Join Configuration:
- Join type: Inner, Left, Right, Full
- Join key: Field to match on
- Handle conflicts: Keep first, keep last, merge

AI helps with:
- Detecting common columns
- Suggesting join keys
- Identifying schema mismatches

Step 4: Quality Actions

Review and fix quality issues:

For each issue type:

Missing Values:
- Fill with: Mean, Median, Mode, Custom value
- Forward/backward fill
- Remove rows
- Leave as-is

Invalid Values:
- Correct format
- Replace with valid value
- Remove value/row
- Flag for manual review

Duplicates:
- Keep first occurrence
- Keep last occurrence
- Merge (combine data from duplicates)
- Remove all duplicates

Inconsistencies:
- Standardize values
- Apply business rules
- Fix relationships
- Manual correction

Bulk actions:
- Apply same fix to similar issues
- AI-suggested batch corrections
- Preview changes before applying

Step 5: Sensitivity & PII

Review PII detection:
- See all detected PII fields
- Review sample values (masked)
- Choose action for each field

Apply PII actions:
- Mask selected fields
- Remove unnecessary PII
- Encrypt sensitive columns
- Flag for downstream handling

Set sensitivity level:
- Mark overall dataset sensitivity
- Classify by column
- Add handling instructions

Step 6: Export Configuration

Prepare for export:

Format Selection:
- CSV (delimiter, encoding)
- Excel (.xlsx, multiple sheets)
- JSON (format, nesting)
- Parquet (compression)
- SQL INSERT statements

Options:
- Include header row
- Quote text fields
- Date format
- Null value representation

Destination:
- Save to file
- Export to database
- Copy to clipboard
- Generate code (for automation)

Preview:
- See first 100 rows
- Verify transformations applied
- Check format rendering

Completing the Wizard

Final steps:

1. Review summary:
2. All transformations listed
3. Quality improvement shown

4. PII handling confirmed

5. Save prepared dataset:

6. Give it a name
7. Add description

8. Save to project

9. Export (optional):

10. Export immediately
11. Save for later
12. Schedule recurring export

Data Transformations

Column Operations

Available transformations:

Rename Columns:
- Single or bulk rename
- Apply naming conventions
- Remove special characters

Change Data Types:
- Auto-detect and convert
- String ↔ Number ↔ Date
- Handle conversion errors

Split Columns:
- By delimiter
- By position
- By pattern (regex)

Merge Columns:
- Concatenate with separator
- Conditional merge
- Formula-based

Extract Patterns:
- Phone numbers
- Email domains
- Dates from text
- Custom regex

Row Operations

Filter Rows:

Column: revenue
Condition: greater than
Value: 1000
Result: Keep rows where revenue > 1000

Remove Duplicates:
- Exact match
- Fuzzy matching (similarity threshold)
- Based on specific columns

Sort:
- Single or multiple columns
- Ascending/descending
- Custom sort order

Sample:
- First N rows
- Last N rows
- Random sample
- Stratified sampling

Value Operations

Fill Missing Values:

Statistical methods:
- Mean (for numbers)
- Median (robust to outliers)
- Mode (most common value)

Propagation methods:
- Forward fill (use previous value)
- Backward fill (use next value)

Custom:
- Specific value
- Formula-based
- Conditional fill

Replace Values:
- Find and replace
- Bulk replacement
- Conditional replacement
- Pattern-based

Standardize Formats:

Dates:
- YYYY-MM-DD
- MM/DD/YYYY
- DD-MMM-YYYY
- Custom format

Phone Numbers:
- (XXX) XXX-XXXX
- XXX-XXX-XXXX
- +1-XXX-XXX-XXXX
- International formats

Text:
- Uppercase/lowercase
- Title case
- Remove extra spaces
- Trim whitespace

Natural Language Transformations

Plain-English Transforms

The query layer can drive transformations from natural-language prompts. The same intelligence pipeline that answers questions can also propose and apply changes to your data.

How it works:

1. Describe the transformation in plain English
2. RangerIO proposes SQL or a step-by-step plan
3. Preview the result
4. Apply if correct

Example commands:

"Convert all dates to YYYY-MM-DD format"
- Identifies date columns
- Applies standardized format
- Shows before/after

"Remove rows where revenue is negative"
- Filters data
- Shows the rows removed
- Confirms action

"Create a new column for profit_margin calculated as (revenue - cost) / revenue"
- Adds the calculated column
- Handles null values
- Applies to all rows

"Group by category and sum the sales"
- Aggregates the data
- Creates a summary table
- Preserves the original

AI Suggestions

Automatic recommendations:

When you open Prepare Wizard, AI analyzes your data and suggests:
- Common cleaning tasks
- Format standardizations
- Likely transformations
- Quality improvements

Example suggestions:
- "Standardize phone number format (3 different formats detected)"
- "Remove 42 duplicate rows based on customer_id"
- "Fill missing email addresses (15% missing)"
- "Convert price column to numeric (stored as text)"

Quality Improvement Tracking

Before/After Comparison

The wizard shows:
- Quality score before: C (72%)
- Quality score after: A (94%)
- Improvement: +22%

Detailed breakdown:
- Completeness: 65% → 98%
- Accuracy: 78% → 92%
- Consistency: 70% → 95%
- Uniqueness: 75% → 92%

Transformation Log

Every change is logged:
- What was changed
- How it was changed
- Who/when (timestamp)
- Before/after values (sample)

Use log to:
- Audit data preparation
- Reproduce transformations
- Troubleshoot issues
- Document process

Best Practices

When to Use Prepare Wizard

Always use for:
- ✅ Data with quality issues
- ✅ Merging multiple sources
- ✅ PII-containing datasets
- ✅ Data for external sharing
- ✅ Datasets requiring standardization

Not needed for:
- Clean, well-structured data
- Quick exploratory analysis
- Data you won't export

Order of Operations

Recommended sequence:
1. Remove duplicates first
2. Handle missing values
3. Fix format/type issues
4. Standardize values
5. Handle PII
6. Final validation

Why this order:
- Duplicates can affect statistics
- Missing data impacts calculations
- Format fixes enable other operations
- PII handling should be last (to preserve for validation)

Saving Transformations

Create reusable templates:
1. Complete wizard with typical dataset
2. Save transformation steps
3. Apply to similar datasets later

Templates useful for:
- Regular data imports
- Consistent formatting
- Standard cleaning procedures