# Dataform-bstore

Dataform-bstore is a dbt project that migrates and transforms retail planning data from BigQuery into Azure Databricks Unity Catalog. It builds layered silver, gold, reporting, diamond, and platinum tables for the bstore client, including sales, stock, financial-planning, and WSSI planning-tool metadata. Deployment is automated through Bitbucket Pipelines and Databricks Asset Bundles; standalone scripts support connection checks and BigQuery-to-Databricks Parquet migration.

## Business Summary

This repository prepares retail data for merchandise planning in Databricks. It turns sales, stock, financial, and planning-configuration data into organised tables that can support WSSI analysis and other reporting or planning tools. It also includes a utility for moving selected legacy BigQuery data into Databricks storage.

## Capabilities

| Capability | Category | Status | Access | Exposure |
|---|---|---|---|---|
| Retail Data Transformation | Reporting | `production` | `write` | user_facing, agent_facing, internal |
| WSSI Planning Data Management | Planning | `production` | `write` | user_facing, agent_facing |
| Financial Planning Data Preparation | Planning | `partial` | `write` | user_facing, agent_facing |
| Retail Reporting Data Production | Reporting | `production` | `write` | user_facing, agent_facing |
| BigQuery to Databricks Data Migration | Other | `partial` | `execute` | agent_facing, internal |
| Databricks Connectivity and Schema Checking | Other | `internal_only` | `execute` | agent_facing, internal |

**Retail Data Transformation** — Converts source retail data into curated tables for downstream merchandise planning, analytics, and reporting.

**WSSI Planning Data Management** — Provides the structured planning-tool data needed to define, calculate, display, and audit WSSI metrics and saved planning views.

**Financial Planning Data Preparation** — Prepares sales, gross-profit, discount, purchase-cost, purchase-retail, and related budget and forecast data used in merchandise financial planning.

**Retail Reporting Data Production** — Creates reporting-ready views of stock and operational retail activity for analysis and monitoring.

**BigQuery to Databricks Data Migration** — Moves selected legacy BigQuery tables into Databricks storage so they can be used during platform migration.

**Databricks Connectivity and Schema Checking** — Allows operators to verify Databricks connectivity and inspect catalog tables and schemas during deployment or migration work.

## Workflows

**Validate dbt changes** — Pull requests are checked without writing model data to Databricks.

1. Install dbt-databricks.
2. Run dbt dependencies, allowing dependency installation failure to be non-blocking.
3. Parse the project against the prod target.
4. Compile selected bstore and bstorerascals models without building tables.

**Deploy and run production models** — Changes merged to main are validated, deployed as a Databricks Asset Bundle, and run as a dbt job.

1. Run the validation step.
2. Install dbt-databricks and the Databricks CLI.
3. Authenticate the deployment service principal through pipeline-provided credentials.
4. Deploy the bstore-dbw Databricks bundle.
5. Run the configured dbt job against the production workspace.

**Build layered planning data** — dbt materializes client-specific models into Unity Catalog schemas.

1. Read source tables from the configured Databricks catalogs and schemas.
2. Resolve model dependencies through dbt refs and SQL transformations.
3. Materialize silver, gold, reporting, diamond, and platinum models as tables.
4. Write outputs to the bstore catalog, or the bstorerascals catalog for that project namespace.

**Migrate selected BigQuery tables** — An operator migrates configured source tables as Parquet files into a Databricks bronze staging volume.

1. Select a migration profile such as bstore, blueillusion, or citybeach.
2. Export each configured BigQuery table server-side to Parquet in GCS.
3. Download Parquet files to local disk using concurrent workers.
4. Upload files to the target Unity Catalog bronze migration-staging volume.
5. Leave the Parquet files as the final migration output; no Delta table is created.

## Architecture

This is a batch-oriented dbt transformation repository rather than an application server. SQL models are organised by client and medallion-style layer, then deployed and executed in Azure Databricks through a Databricks Asset Bundle. Supporting Python scripts handle migration and operational checks.

**Components:** dbt project configuration in dbt_project.yml and profiles configuration under ci/profiles., bstore and bstorerascals model trees under models/, including silver, gold, reporting, diamond, and platinum layers., SQL macros, including generate_schema_name.sql for Dataform-compatible schema naming., Databricks Asset Bundle configuration under databricks/ and its job resources., Bitbucket and Azure pipeline definitions for validation, deployment, secret retrieval, and rollback., Python operational scripts under scripts/., SQL tests under tests/., Databricks Unity Catalog catalogs and schemas used as model targets and bronze migration staging.

**Patterns:** Layered ELT/medallion data modelling., dbt ref-based dependency DAGs with table materializations., Client/catalog-specific model namespaces., Dataform-to-dbt migration compatibility through custom schema naming and aliases., Batch deployment and scheduled Databricks job execution., Concurrent file migration using Python ThreadPoolExecutor., Delta table properties for platinum column mapping.

## Interfaces

| Kind | Identifier | Description |
|---|---|---|
| `cli` | `dbt` | dbt deps, parse, compile, and build operations are used for dependency installation, validation, compilation, and model execution. |
| `cli` | `scripts/migrate_bq_to_databricks.py <profile>` | Migrates configured BigQuery tables to Parquet files in a Databricks Unity Catalog volume. |
| `cli` | `scripts/db_check.py [sql]` | Runs SELECT 1 or an operator-supplied SQL query through the Databricks SQL connector. |
| `other` | `Databricks Asset Bundle: bstore-dbw` | Deploys the repository and its dbt job resources to Databricks development, test, or production targets. |
| `other` | `Bitbucket Pipelines` | Validates pull requests and automatically deploys and runs production changes merged to main. |

## Data

| Entity | Ownership | Description |
|---|---|---|
| Silver retail datasets | `writes` | Curated intermediate retail tables produced from bronze/source data for downstream transformations. |
| Gold retail datasets | `writes` | Business-ready retail outputs, including sales, stock, style, store, calendar, pricing, and allocation-related data. |
| WSSI metric definitions and dependencies | `owns` | Definitions, labels, formulas, dependencies, and lineage metadata for WSSI metrics. |
| WSSI UI and saved-view configuration | `owns` | Planning-tool UI configuration, view configuration, view columns, and saved planning views. |
| WSSI runtime and audit data | `owns` | Runtime snapshots, committed results, and audit commit records associated with WSSI planning activity. |
| Financial planning tables | `writes` | Budget, forecast, and current-year sales, gross-profit, discount, purchase-cost, and purchase-retail outputs. |
| Retail reporting tables | `writes` | Reporting outputs such as stock deltas, daily invoices, stock availability, and weekly snapshots. |
| BigQuery source tables | `reads` | Selected legacy bstore and other client tables read during migration and, where configured, used as transformation inputs. |
| Migration Parquet files | `writes` | Parquet exports staged in Google Cloud Storage and uploaded to a Databricks bronze migration-staging volume. |

## Dependencies

| Name | Kind | Relationship | Criticality |
|---|---|---|---|
| Azure Databricks / Unity Catalog | `external_service` | `writes` | `critical` |
| Databricks SQL Warehouse | `external_service` | `uses` | `critical` |
| Google BigQuery | `external_service` | `reads` | `required` |
| Google Cloud Storage | `external_service` | `writes` | `required` |
| dbt Databricks adapter | `library` | `uses` | `critical` |
| Databricks SQL Connector | `library` | `uses` | `required` |
| Bitbucket Pipelines | `other` | `uses` | `required` |
| Azure DevOps pipelines | `other` | `uses` | `supporting` |
| Databricks CLI | `library` | `uses` | `required` |

## Technology

Python, SQL, dbt, dbt-databricks 1.10.19, Azure Databricks, Databricks Unity Catalog, Databricks SQL Connector 4.0.5, Delta tables, Google BigQuery, Google Cloud Storage, Bitbucket Pipelines, Azure DevOps Pipelines, Databricks Asset Bundles

## Limitations

- The authoritative evidence does not include the full SQL model contents, so exact source-to-target lineage and transformation rules cannot be established for every table.
- The repository visibly contains bstore and bstorerascals model configurations, but the completeness and production status of bstorerascals outputs are not established.
- The migration script includes profiles beyond bstore, but the supplied source excerpt does not show all profile definitions or prove that those client migrations are complete.
- No HTTP, GraphQL, gRPC, webhook, or user-interface routes are exposed by the repository.
- The evidence does not establish downstream consumers, refresh SLAs, data quality thresholds, or whether the planning tables are directly queried by a production Merchmix application.
- README statements about migration scope and table counts are treated as supporting context only; implementation evidence is limited to the listed files and scanner signals.

## Agent Instructions

Treat this repository as a batch data-engineering and Databricks deployment project, not as a transactional retail application or API service.

- For planning-data questions, inspect the diamond and platinum model outputs first, especially the WSSI and financial-planning tables.
- For retail sales, stock, allocation, or calendar data, inspect the silver and gold model trees and their dbt dependencies.
- Use dbt model names and refs for logical dependencies; physical schemas and catalogs are configured in dbt_project.yml.
- Do not assume a model is an API endpoint or an interactive screen; this repository exposes batch CLI and pipeline interfaces only.
- For migration requests, use scripts/migrate_bq_to_databricks.py only with a known profile and remember that it produces Parquet staging files, not Delta tables.
- For operational connectivity checks, scripts/db_check.py can execute arbitrary operator-supplied SQL against Databricks; handle it as an internal administrative tool.
- Never request, print, or infer values for Databricks, BigQuery, GCS, or pipeline credentials.

## Source

| Field | Value |
|---|---|
| Repository | `Dataform-bstore` |
| Branch | `main` |
| Commit | `6f7830d745b7` |
| Generated by | `gpt-5.6-luna` |

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