blueillusion-vm-bq-sync
currentA FastAPI service that extracts data from Microsoft SQL Server views and synchronizes it to BigQuery, Databricks, or both. It supports full, incremental, hash-difference, and bounded backfill synchronization, with background job tracking in SQLite. The repository also contains an auxiliary Postman/cURL tooling package for generating and importing API test requests.
Business summary
This service moves retail data from a Microsoft SQL Server reporting database into cloud data platforms so Merchmix and other analytics systems can use current information. It can refresh all data, update only changed records, compare source and target data, and repair historical gaps. Operators can monitor jobs and receive Slack notifications when synchronization succeeds or fails.
Capabilities
10Copies data from Microsoft SQL Server views into BigQuery, Databricks, or both for downstream retail planning and analytics.
FastAPI sync endpoints create background jobs executed by worker.py. SyncRequest supports full, incremental, hash-diff, and backfill modes, configurable source schema, dataset, destination, primary keys, watermark columns, chunk sizes, deletion handling, filters, and table configuration.
Updates cloud data using only records that changed since the previous synchronization, reducing processing time and data transfer.
The synchronization request model supports sync_mode='incremental', primary keys, watermark_column, and table configuration. The implementation exposes /api/v1/sync/incremental.
Finds inserted, updated, or missing records by comparing source data with target data, with an option to remove records missing from the source.
Hash-diff synchronization is exposed at /api/v1/sync/hash_diff and supports delete_missing, hash_diff_threshold, primary keys, and chunked processing. The worker reports row counts and deleted-row totals.
Repairs missing or incorrect historical data for a selected table and date or range field.
Backfill endpoints support creating bounded backfill jobs, checking source/target gaps, and rolling back or processing rolling historical ranges. Backfill requests validate identifiers and date ranges, construct filtered synchronization requests, and use merge-style processing.
Lists the available reporting views in a Microsoft SQL Server schema so an operator can choose data to synchronize.
POST /api/v1/tables connects through SQLAlchemy and ODBC, querying INFORMATION_SCHEMA.VIEWS for the requested schema.
Allows authorized operators to inspect source data with SQL queries restricted to SELECT statements.
POST /api/v1/query builds a SQL Server ODBC connection and rejects requests whose SQL does not begin with SELECT.
Accepts structured draft records for styles, purchase orders, and store transfers, supporting merchandise and operational planning workflows.
POST /api/v1/drafts is backed by Pydantic models for style drafts, purchase-order drafts with size lines, and store-transfer drafts with transfer lines. The provided evidence does not establish the persistence or downstream handling of these drafts.
Protects the service with API keys and allows an authorized operator to rotate or revoke the current key.
API keys are hashed and stored in the local SQLite api_keys table. All operational endpoints use the API-key dependency; POST /api/v1/keys/rotate creates a replacement key and DELETE /api/v1/keys/revoke revokes the current key.
Provides job identifiers and status information so operators can track long-running data transfers.
Jobs and job logs are stored in SQLite. The API exposes GET /api/v1/sync and GET /api/v1/sync/{job_id}; synchronization work is launched through FastAPI background execution and status updates are handled by database helpers.
Creates cURL examples from the service's OpenAPI document and imports them into Postman for API testing.
The postman-curl-importer package fetches /openapi.json, resolves schemas and example request data, generates cURL entries, converts cURL requests into Postman collection items, and can call the Postman API for collection import.
Workflows
4Starts a background extraction and loads the selected SQL Server data into one or more cloud targets.
- 1.An authenticated client submits a full or incremental sync request.
- 2.The API creates a job record and returns a job identifier.
- 3.The worker connects to Microsoft SQL Server through ODBC and extracts configured views or tables.
- 4.The worker writes data to BigQuery, Databricks, or both, using chunking and Parquet/GCS staging where configured.
- 5.The job status and table-level results are recorded and a Slack notification may be sent.
- 6.The client retrieves job status using the job identifier.
Compares source and target records and applies detected changes.
- 1.An authenticated client submits a hash-diff request with keys and comparison options.
- 2.The worker compares source and destination data in chunks.
- 3.If changes exceed the configured threshold, the implementation can fall back to a full extract.
- 4.Changed records are merged into the destination and optionally missing records are deleted.
- 5.The job result reports counts, modes, keys, and deletions.
Identifies and repairs records missing from a cloud target for a bounded historical period.
- 1.An authenticated client submits table, key, filter-column, value-column, and start/end date parameters.
- 2.The service validates identifiers and ensures the date range is bounded and ordered.
- 3.A gap-check endpoint compares normalized source and target keys and aggregates.
- 4.A backfill endpoint creates a filtered synchronization job for the selected range.
- 5.The client monitors the resulting job.
Converts the running API contract into reusable Postman requests.
- 1.The importer fetches the FastAPI OpenAPI document.
- 2.It resolves schema references and generates example request bodies and cURL commands.
- 3.The cURL parser converts requests into Postman collection items.
- 4.The tooling imports requests into a new or existing Postman folder and can apply variable mappings.
Architecture
A single FastAPI application provides the HTTP API and delegates long-running synchronization to worker functions executed as background jobs. Local SQLite stores job metadata, logs, and API-key hashes; SQLAlchemy/pyodbc reads Microsoft SQL Server, while Google Cloud and Databricks clients write target data. Deployment is VM-based, with CI/CD copying Python and shell files to an Azure VM on the main branch; an older Google Cloud Build deployment configuration is also present.
Interfaces
16| Kind | Identifier | Description |
|---|---|---|
| http | GET /version | Returns the application version. |
| http | POST /api/v1/keys/rotate | Generates and stores a replacement API key for an authenticated caller. |
| http | DELETE /api/v1/keys/revoke | Revokes the authenticated API key. |
| http | POST /api/v1/tables | Lists SQL Server views in a requested schema. |
| http | POST /api/v1/query | Executes an authenticated SELECT-only query against SQL Server. |
| http | POST /api/v1/sync/full | Starts a full synchronization job. |
| http | POST /api/v1/sync/incremental | Starts an incremental synchronization job. |
| http | POST /api/v1/sync/hash_diff | Starts a hash-difference synchronization job. |
| http | GET /api/v1/sync | Returns synchronization job information. |
| http | GET /api/v1/sync/{job_id} | Returns status or details for a specific synchronization job. |
| http | POST /api/v1/backfill | Starts a bounded historical backfill job. |
| http | POST /api/v1/backfill/check | Checks source/target gaps for a bounded historical range. |
| http | POST /api/v1/backfill/rolling | Starts a rolling backfill operation. |
| http | POST /api/v1/drafts | Accepts structured style, purchase-order, or store-transfer draft data. |
| other | FastAPI OpenAPI document | The running application exposes the standard /openapi.json document used by the repository's Postman tooling. |
| other | Postman importer CLI/Makefile | Internal scripts generate cURL requests and import them into Postman collections. |
Data
9| Entity | Ownership | Description |
|---|---|---|
| Synchronization jobs | owns | Job identifiers, status, errors, and creation timestamps for background synchronization and backfill operations. |
| Job logs | owns | Messages associated with synchronization jobs. |
| API keys | owns | Hashed API keys, short prefixes, creation timestamps, and revocation state. |
| SQL Server source views and query results | reads | Retail and business data read from configured Microsoft SQL Server databases and schemas. |
| BigQuery target tables | writes | Synchronized source data written to configured Google BigQuery projects and datasets. |
| Databricks target tables | writes | Synchronized source data written to configured Databricks catalogs and schemas. |
| Style drafts | writes | Draft style metadata including codes, descriptions, merchandising classifications, season, colour, pricing, quantity, and manufacturer information. |
| Purchase-order drafts | writes | Draft purchase orders with supplier, dates, status, quantities, costs, and size-level lines. |
| Store-transfer drafts | writes | Draft store transfers with source/destination, style, status, quantities, metadata, and size-level lines. |
Dependencies
10| Name | Kind | Relationship | Criticality |
|---|---|---|---|
| Microsoft SQL Server | database | reads | critical |
| BigQuery | database | writes | required |
| Google Cloud Storage | other | uses | supporting |
| Databricks SQL | database | writes | optional |
| SQLite jobs database | database | depends_on | critical |
| Slack | external service | publishes | optional |
| Postman API | external service | calls | supporting |
| FastAPI | library | uses | critical |
| SQLAlchemy | library | uses | critical |
| Pandas and PyArrow | library | uses | required |
Technology
Limitations
7- —The evidence does not show the complete implementations of all route handlers, so exact response schemas and detailed sync semantics are not fully established.
- —The /api/v1/drafts route and draft models are present, but the evidence does not establish whether drafts are persisted locally, written to a cloud target, or forwarded elsewhere.
- —No retail planning calculations such as WSSI, OTB, range planning, size curves, markdown optimization, allocation, or replenishment are implemented in the provided source evidence; this repository is an integration and data-movement service.
- —BigQuery and Databricks are supported by imports and request configuration, but deployment-specific credentials, connectivity, and target table conventions are environment-dependent.
- —The service has a local SQLite state store and VM deployment configuration; no queue broker or separate job service is evidenced.
- —The repository includes both Azure VM and older Google Cloud Build deployment configurations, so the authoritative production deployment path should be confirmed operationally.
- —Some source excerpts are redacted or truncated, including authentication details and parts of worker.py; secrets and exact implementation details cannot be assessed from them.
Agent instructions
Use this repository for SQL Server-to-cloud data synchronization, historical backfills, source inspection, and synchronization job monitoring. Do not route merchandise-planning calculation requests here unless another service is identified.
- →Authenticate API calls with the configured API-key mechanism; do not request, expose, or infer secret values.
- →For data movement, first use POST /api/v1/tables to discover available SQL Server views, then choose full, incremental, hash-diff, or backfill synchronization based on the data-repair need.
- →Treat synchronization and backfill operations as asynchronous: capture the returned job_id and poll GET /api/v1/sync/{job_id}.
- →Use POST /api/v1/query only for read-only SELECT inspection; the request model explicitly rejects non-SELECT statements.
- →When requesting incremental or hash-diff synchronization, provide valid primary-key and watermark/table configuration as required by SyncRequest validation.
- →Use the backfill check endpoint before a repair when the goal is to quantify source-only and target-only records.
- →Do not assume draft submission has durable storage or a downstream business effect until the complete /api/v1/drafts implementation is inspected.
- →Do not claim this service performs WSSI, OTB, range planning, markdown, allocation, replenishment, or forecasting calculations based on the current evidence.
- →For API testing or contract generation, use the internal postman-curl-importer tooling rather than treating it as a retail-facing capability.