# Dataform-blueillusion

Dataform-blueillusion is a retail data transformation repository for the Merchmix Databricks product. It uses dbt with the Databricks adapter to build curated silver, gold, reporting, diamond, and platinum datasets in Azure Databricks/Unity Catalog, with retail calendar and synchronization policy seeds. The repository also contains deployment automation, scheduled Databricks jobs, incremental-run window resolution, and comparison tooling against the BigQuery/Dataform source of truth.

## Business Summary

This service turns raw retail sales, stock, purchase, and calendar information into reliable datasets used by Merchmix planning and reporting. It provides the data foundation for weekly sales and inventory analysis, purchase-order tracking, stock availability, and WSSI planning information. It runs automatically in Databricks and supports controlled refreshes for hourly, daily, and weekly data corrections.

## Capabilities

| Capability | Category | Status | Access | Exposure |
|---|---|---|---|---|
| Retail Data Transformation | Other | `production` | `execute` | internal |
| Sales and Inventory Analytics Data | Reporting | `production` | `read` | user_facing, internal |
| Purchase and Receipt Data Management | Suppliers | `production` | `read` | user_facing, internal |
| WSSI Data Foundation | Planning | `production` | `read` | user_facing, internal |
| Incremental Retail Data Synchronization | Other | `production` | `execute` | internal, agent_facing |
| Planning Metric Output Tables | Planning | `partial` | `read` | user_facing, internal |
| Data Pipeline Validation and Reconciliation | Reporting | `production` | `execute` | internal, agent_facing |

**Retail Data Transformation** — Converts retail source data into structured datasets that Merchmix can use for merchandise planning and analysis.

**Sales and Inventory Analytics Data** — Provides prepared sales, stock-on-hand, stock movement, and availability data for analysing retail performance and inventory position.

**Purchase and Receipt Data Management** — Makes purchase orders, purchase-order lines, and received stock available for planning and operational reporting.

**WSSI Data Foundation** — Stores the planning definitions, calculations, saved views, and runtime information needed to support WSSI analysis.

**Incremental Retail Data Synchronization** — Refreshes recently changed retail data without rebuilding every historical record, while supporting wider daily and weekly correction scans.

**Planning Metric Output Tables** — Publishes structured outputs for full-price and markdown sales, margin, discount, stock, and purchase measures used by planning workflows.

**Data Pipeline Validation and Reconciliation** — Checks that Databricks outputs remain aligned with the existing BigQuery source-of-truth datasets.

## Workflows

**Production dbt deployment** — Validates and deploys the dbt project to the production Databricks workspace.

1. Install dbt-databricks 1.10.19.
2. Run dbt dependency installation, parsing, and compilation during validation.
3. Deploy the Databricks Asset Bundle to the prod target.
4. Run the configured production dbt build/job sequence against the blueillusion catalog.

**Tiered incremental synchronization** — Refreshes changed retail data according to hourly, daily, weekly, or automatically selected schedules.

1. Select a synchronization tier or use auto mode.
2. Resolve policy windows and second-run recomputation pairs outside dbt parsing.
3. Pass the resolved values to dbt through --vars.
4. Build the affected silver and gold models using incremental strategies.

**Second-run reconciliation** — Compares selected full-price and markdown second-run outputs between BigQuery and Databricks.

1. Connect to BigQuery and Databricks using configured credentials.
2. Read the configured FP/MD tables for year 2026.
3. Compare row counts and W1-W53 numeric column totals.
4. Write a printed report and compare_second_run_results.csv.

## Architecture

A dbt transformation project runs inside Azure Databricks and publishes tables into Unity Catalog layers. Databricks Asset Bundles package the repository and define deployment targets/jobs, while Bitbucket and Azure pipeline files automate validation, deployment, rollback, and secret retrieval. Some definitions retain Dataform-style SQLX/JavaScript artifacts, including generated platinum schemas, indicating migration or coexistence with the prior BigQuery/Dataform implementation.

**Components:** dbt project configuration in dbt_project.yml and profiles., Silver, gold, reporting, diamond, and platinum model layers., Seeds for retail calendar weeks and synchronization policy., SQL macros for schema naming, row counts, ISO date formatting, and platinum-table protection., Python scripts for synchronization-variable resolution, recomputation-pair resolution, Databricks queries, and BigQuery/Databricks comparison., Databricks Asset Bundle configuration and job resources., Bitbucket and Azure Pipelines for validation, deployment, and rollback.

**Patterns:** Layered retail warehouse architecture., dbt table materializations by catalog and schema., Incremental processing with replace_where and affected-key/MERGE-style recomputation., External resolution of runtime windows because dbt config parsing cannot reliably query the warehouse., Seed-backed fallback for job contexts without a live warehouse connection., Compile-only pull-request validation followed by automatic production deployment on main.

## Interfaces

| Kind | Identifier | Description |
|---|---|---|
| `cli` | `dbt build / dbt run / dbt seed` | Builds, runs, and seeds the transformation project. Runtime synchronization values are supplied with dbt --vars. |
| `cli` | `scripts/resolve_sync_vars.py` | Prints a JSON --vars payload for hourly, daily, weekly, or auto synchronization runs. |
| `cli` | `scripts/resolve_second_run_pairs.py` | Prints a JSON --vars payload containing the financial-year/week recomputation pairs for second-run models. |
| `cli` | `scripts/dbx-query.py` | Runs an ad-hoc SQL query against Databricks SQL and prints pipe-delimited or CSV results. |
| `cli` | `scripts/compare_second_run.py` | Runs a BigQuery-versus-Databricks reconciliation and writes a CSV report. |
| `other` | `Databricks Asset Bundle: blueillusion-dbw` | Packages and deploys the dbt project and its Databricks job resources to dev, test, or prod targets. |
| `other` | `Bitbucket Pipelines branches/main and pull-requests` | Validates pull requests and automatically deploys and runs the production bundle after changes reach main. |

## Data

| Entity | Ownership | Description |
|---|---|---|
| Retail sales | `owns` | Sales transaction and weekly sales measures used for performance and planning calculations. |
| Stock on hand and stock availability | `owns` | Current stock, stock deltas, remaining stock, and availability representations. |
| Purchase orders and receipts | `owns` | Purchase-order headers, order items, and received-stock records. |
| Retail calendar | `owns` | Financial years, retail weeks, and week date ranges used to scope processing and reporting. |
| Size templates | `owns` | Size-template data used by retail merchandise models. |
| WSSI metrics and configuration | `owns` | Metric definitions, formulas, dependencies, labels, lineage, UI configuration, runtime snapshots, saved views, and committed results. |
| Full-price and markdown planning measures | `owns` | FP/MD sales, gross profit, discount, CSOH, and purchase measures represented in gold and platinum outputs. |
| Synchronization policy | `owns` | Static hourly, daily, and weekly window settings used to control incremental processing. |
| BigQuery/Dataform second-run tables | `reads` | Existing BigQuery tables used as the comparison source of truth during migration or reconciliation. |

## Dependencies

| Name | Kind | Relationship | Criticality |
|---|---|---|---|
| Azure Databricks / Unity Catalog | `external_service` | `writes` | `critical` |
| Databricks SQL warehouse | `database` | `reads` | `critical` |
| BigQuery | `database` | `reads` | `required` |
| dbt-databricks 1.10.19 | `library` | `uses` | `critical` |
| Databricks CLI | `library` | `uses` | `required` |
| Google Cloud BigQuery Python client | `library` | `uses` | `required` |
| Databricks SQL Python connector | `library` | `uses` | `required` |
| Retail calendar and sync-policy seed files | `other` | `reads` | `required` |
| raw.githubusercontent.com | `external_service` | `uses` | `supporting` |

## Technology

Python, SQL, JavaScript, dbt, dbt-databricks 1.10.19, Azure Databricks, Databricks SQL, Unity Catalog, BigQuery, Databricks Asset Bundles, Bitbucket Pipelines, Azure Pipelines

## Limitations

- The supplied source evidence does not establish the upstream bronze ingestion mechanism or the exact source-system owners for sales, stock, and purchasing data.
- No HTTP, GraphQL, gRPC, queue, or webhook interface is evidenced; interaction is primarily through dbt, Databricks jobs, SQL, and generated warehouse tables.
- The platinum generator creates empty schema-only tables with LIMIT 0, so populated platinum planning outputs are not demonstrated by the evidence.
- The repository contains Dataform-style definitions alongside the dbt project, but the evidence does not establish whether those SQLX/JavaScript definitions are still deployed or are migration leftovers.
- Production status is strongly supported for the Databricks/dbt pipeline, but the evidence does not include runtime job history or table row counts.

## Agent Instructions

Treat this repository as an internal retail data-platform and warehouse-transformation service, not as an end-user application.

- For retail sales, inventory, purchase, calendar, or WSSI dataset questions, inspect the corresponding Databricks catalog/schema models before considering application repositories.
- Use dbt model and seed definitions as the primary source for current Databricks behavior; treat SQLX/JavaScript definitions as historical or migration evidence unless deployment configuration proves otherwise.
- For incremental refreshes, use scripts/resolve_sync_vars.py or scripts/resolve_second_run_pairs.py rather than inventing dbt runtime variables.
- Use dbx-query.py for read-only Databricks investigation and compare_second_run.py for BigQuery-versus-Databricks reconciliation.
- Do not request, expose, or reproduce credential values. Environment variable names identify configuration only.
- Do not assume platinum tables contain populated metrics; the generator evidence shows schema-only tables.

## Source

| Field | Value |
|---|---|
| Repository | `Dataform-blueillusion` |
| Branch | `main` |
| Commit | `5356e18e2f6f` |
| Generated by | `gpt-5.6-luna` |

*Generated by the Merchmix Skills Platform from the current codebase.*