English | 简体中文

Python Data Orchestration Lab

A production-minded Python data orchestration demo that collects heterogeneous business data through pluggable source adapters, converges results at a shared Pydantic boundary, and produces repeatable SQLite, CSV, or Google Sheets outputs.

Client Value

This case study demonstrates the reliable data layer behind automation projects, AI assistants, and agent workflows. Provider payload differences stay inside adapters, while record processing, state persistence, and delivery formats remain stable and independently testable.

• Four provider-oriented adapters or adapter examples cover Google Places, Apollo.io contact enrichment, commercial property feeds, and job-signal feeds, all feeding one typed BusinessRecord boundary.
• The offline path normalizes, deduplicates, merges, and persists records to SQLite, and can export deterministic CSV rows with stable field order.
• The verified baseline contains 51 automated tests and two synthetic offline records.
• Google Places and Google Sheets are opt-in live integrations that require locally supplied credentials.

Preview

The Demo Script provides a reproducible 75-second recording path.

The synthetic workflow runs without an external account. Its verified offline dry-run summary is:

Run summary: collected=2 updated=0 failed=0 errors=0

Persistence mode assigns stable master identifiers, preserves source history, and upserts records into local SQLite. Passing --csv-output also writes a CSV with a fixed header order.

Workflow

Provider APIs or synthetic fixtures
  -> provider-oriented adapters
  -> Pydantic BusinessRecord
  -> address normalization and deduplication
  -> master-record merge and source history
  -> SQLite state
  -> CSV or opt-in Google Sheets output

Adapters share the search, normalize, and validate contract. Provider-specific mapping stays at the adapter layer, while orchestration handles a common record shape. Synthetic input passes through the same type validation, deduplication, merge, and output stages.

Core Capabilities

• Google Places new and legacy API modes, single queries, and deterministic area-and-keyword query plans.
• An Apollo.io adapter example for decision-maker contact enrichment.
• Commercial property and job-signal adapter examples backed by configured endpoints or local fixtures.
• A shared BusinessRecord for company, location, contact, provenance, property, decision-maker, and growth-signal fields.
• Deduplication and master matching using company, address, website, phone, and Google Place identifiers.
• SQLite upserts, stable CSV column order, and optional Google Sheets delivery.
• Bounded live-integration validation with reports that exclude credential values.

Architecture

src.main is the CLI orchestration entry point. It loads settings, chooses synthetic data or Google Places collection, creates BusinessRecord instances, then invokes deduplication, master-record merge, SQLite persistence, and exporters. Apollo, commercial property, and job-signal modules are independently tested adapter examples; the current CLI live collection path orchestrates Google Places, and its live delivery path can orchestrate Google Sheets.

This separation keeps new providers behind the adapter boundary and allows persistence and exports to be verified against one normalized record contract. See the architecture document for subsystem and runtime details.

Quick Start

Python 3.9 or newer is required.

# Install runtime and test dependencies
python3 -m pip install -r requirements.txt -r requirements-dev.txt

# Run the synthetic pipeline without database or external writes
python3 -m src.main --dry-run --use-sample-data

# Persist synthetic records to SQLite and export deterministic CSV
python3 -m src.main --use-sample-data --csv-output data/output/master_records.csv

The default synthetic input is data/sample/business_records.json. The second run command writes local SQLite and CSV files and should be executed from a writable project directory.

Configuration

Use .env.example as the field inventory and configure the paths you intend to enable in a local .env. Credentials enter the runtime only through environment variables or an explicit local credentials file.

Variable	Purpose
GOOGLE_PLACES_API_KEY	Enable live Google Places search
GOOGLE_PLACES_API_VERSION	Select the new or legacy API mode
APOLLO_API_KEY	Enable Apollo.io adapter calls
GOOGLE_SHEETS_CREDENTIALS_JSON	Supply Google service-account JSON at runtime
GOOGLE_SHEETS_NAME / GOOGLE_SHEETS_WORKSHEET	Select the destination spreadsheet and worksheet
SCRAPER_QUERY / SCRAPER_LOCATION	Configure one search and optional latitude/longitude bias
SCRAPER_SEARCH_AREAS / SCRAPER_RADIUS_METERS	Configure query-plan regions and search radius
USE_QUERY_PLAN	Enable deterministic area and keyword expansion
COMMERCIAL_PROPERTY_ENDPOINT / COMMERCIAL_PROPERTY_SAMPLE_PATH	Configure a commercial property source or fixture
JOB_SIGNALS_ENDPOINT / JOB_SIGNALS_SAMPLE_PATH	Configure a job-signal source or fixture
SQLITE_DB_PATH	Select the local SQLite state file
DRY_RUN	Validate records and skip persistence and exports
USE_SAMPLE_DATA / SAMPLE_DATA_PATH	Enable and select synthetic offline records

Google Places and Google Sheets live paths are explicitly enabled by the operator and require local credentials. Google API responses, account permissions, and quotas remain external service boundaries.

Reliability Controls

• Every HTTP request uses a finite 30-second timeout, and adapters and live validators bound result volume.
• Sample inputs are constrained to data/sample, reducing accidental access to unrelated local files.
• Pydantic validates records before merge, persistence, and export stages.
• Address normalization, stable company IDs, source-history merging, and SQLite conflict updates support repeatable runs.
• CSV and Google Sheets share fixed export headers; Google Sheets validation checks the actual written range.
• Automated tests cover success paths and failures such as empty responses, malformed input, request errors, and missing configuration.

Verification

# Run the full test suite, then the offline synthetic-data dry-run
python3 -m scripts.verify_portfolio

# Run the deterministic full suite without bytecode or pytest cache artifacts
PYTHONDONTWRITEBYTECODE=1 python3 -m pytest -p no:cacheprovider -q

# Validate the offline orchestration path independently
PYTHONDONTWRITEBYTECODE=1 python3 -m src.main --dry-run --use-sample-data

Verified locally: The current 61-test complete portfolio suite combines the 51-test business-pipeline baseline with 10 portfolio documentation and verification contracts; the offline synthetic-data summary is two synthetic offline records and zero failures. The manual Python 3.11 pipeline passed; its successful Gitee Go build record requires Gitee sign-in to view. Live validators are located in scripts/ and run only after the corresponding Google credentials are available locally.

Implementation Scope

This repository is a production-minded demo, not a hosted production data service. It demonstrates module boundaries, deterministic record processing, persistence, exports, failure reporting, and testing strategy through a CLI with local state. Production scheduling infrastructure, multi-tenant authorization, continuous monitoring, and external-service SLAs are outside this implementation. A real deployment requires client-side credential management, data compliance review, runtime hosting, scheduling, and observability decisions.

Project Layout

src/
  adapters/       Adapter contract, four source implementations or examples, shared schema
  exports/        CSV and Google Sheets outputs
  records/        Deterministic master-record matching and merge
  storage/        SQLite persistence
  utils/          Address normalization, deduplication, and logging
  main.py         CLI orchestration entry point
scripts/          Bounded live-integration validation runners
data/sample/      Synthetic offline fixtures
tests/            Unit, workflow, integration-boundary, and documentation-contract tests

Documentation

• Architecture: runtime flow, subsystems, and security boundaries.
• Interfaces: module interfaces and data contracts.
• Developer guide: local development and verification workflow.
• 中文案例文档: complete Simplified Chinese case study.

License

This project is released under the MIT License.