Dataform-blueillusion

current

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.

main·5356e18e2f6f·generated by gpt-5.6-luna·9/4/2026, 1:18:49 PM View as Markdown

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

Retail Data TransformationproductionexecuteOtherinternal98%

Converts retail source data into structured datasets that Merchmix can use for merchandise planning and analysis.

dbt models are materialized as tables across the blueillusion silver, gold, reporting, diamond, and platinum schemas/catalogs. The repository includes models for sales, stock, purchase orders, receipts, calendars, size templates, reporting tables, and WSSI metadata/results.

Sales and Inventory Analytics DataproductionreadReportinguser facinginternal94%

Provides prepared sales, stock-on-hand, stock movement, and availability data for analysing retail performance and inventory position.

Evidence includes silver models for sales, daily sales key statistics, CSOH, reporting models for stock availability and stock deltas, gold remaining stock, and second-run FP/MD sales, gross sales, discount, GP, and CSOH tables referenced by the comparison tooling.

Purchase and Receipt Data ManagementproductionreadSuppliersuser facinginternal96%

Makes purchase orders, purchase-order lines, and received stock available for planning and operational reporting.

Silver-layer models explicitly include purchase_order, purchase_order_items, and purchase_receipts. The runtime scripts also select purchase-order models for incremental dbt builds.

WSSI Data FoundationproductionreadPlanninguser facinginternal97%

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

The diamond layer contains WSSI audit commits, committed results, formula releases, metric definitions, metric dependencies, metric labels, metric lineage, UI configuration, runtime snapshots, saved views, view columns, and view configurations.

Incremental Retail Data SynchronizationproductionexecuteOtherinternalagent facing98%

Refreshes recently changed retail data without rebuilding every historical record, while supporting wider daily and weekly correction scans.

resolve_sync_vars.py resolves hourly, daily, weekly, and auto synchronization policies into dbt --vars values. Seeds contain sync_policy.csv and retail_calendar_weeks.csv. resolve_second_run_pairs.py resolves recent-week or full-year recomputation pairs for second-run models using replace_where or affected-key workflows.

Planning Metric Output TablespartialreadPlanninguser facinginternal91%

Publishes structured outputs for full-price and markdown sales, margin, discount, stock, and purchase measures used by planning workflows.

The platinum generator defines a collection of FP/MD tables including sales, GP, discount percentages, move-to-markdown CSOH, identified purchases, total purchase measures, and minimum CSOH. The generated definitions are explicitly empty LIMIT 0 schemas, so this capability is currently only partially implemented.

Data Pipeline Validation and ReconciliationproductionexecuteReportinginternalagent facing97%

Checks that Databricks outputs remain aligned with the existing BigQuery source-of-truth datasets.

compare_second_run.py compares BigQuery and Databricks second-run tables for row counts and W1-W53 numeric totals, filtered to year 2026, and emits a CSV comparison report. CI also runs dbt parse and compile before deployment.

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

PythonSQLJavaScriptdbtdbt-databricks 1.10.19Azure DatabricksDatabricks SQLUnity CatalogBigQueryDatabricks Asset BundlesBitbucket PipelinesAzure 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.