Omnichannel Sell-Through Dashboard ETL and Refresh Script

Role: Principal Retail Business Intelligence Engineer with 12+ years optimizing omnichannel reporting pipelines and merchandise analytics.

Context

• Point-of-sale transactional source: {{pos_data_source}}
• Central inventory balance table: {{inventory_warehouse_table}}
• Store aggregation hierarchy: {{store_hierarchy_level}}
• Markdown discount trigger percentage: {{markdown_threshold_pct}}
• BI platform target destination: {{target_dashboard_engine}}
• Automated orchestration cadence: {{reporting_cadence}}

Task

Develop an enterprise-grade data transformation and dashboard publishing script that ingests transactional POS feeds and current inventory snapshots, computes core retail velocity metrics, and refreshes the semantic reporting layer for merchandising teams.

Method

1. Ingest incremental point-of-sale transactions from {{pos_data_source}} and reconcile channel returns across digital and physical storefronts.
2. Join real-time inventory balances from {{inventory_warehouse_table}} partitioned by the specified {{store_hierarchy_level}}.
3. Compute net sell-through rate percentages by dividing net units sold by the sum of beginning stock and incoming transfers.
4. Calculate stock-to-sales ratios and segment SKU performance into velocity quartiles.
5. Apply conditional logic for products exceeding {{markdown_threshold_pct}} to flag margin compression risks.
6. Format dimensional tables and aggregate metric views tailored for rapid query execution in {{target_dashboard_engine}}.
7. Implement data quality assertion tests to identify and quarantine negative stock anomalies before pushing downstream.
8. Embed schedule management and dashboard cache invalidation triggers aligned with {{reporting_cadence}}.

Constraints

• MUST execute idempotent incremental loads to prevent record duplication during execution retries.
• MUST NOT hardcode database credentials or connection URIs; use environment variables and key vault references.
• Schema output MUST enforce standard two-decimal precision on currency values and sell-through percentages.
• Inline error handling MUST log failed records to a dead-letter table without halting pipeline execution.

Output format

• Complete production script (Python with PySpark or SQL/dbt model files) with explicit variable bindings.
• Data dictionary specifying calculated metric formulas and partition keys.
• Logging and alerting configuration module for automated run failure notices.
• Performance tuning recommendations for memory allocation and dashboard query optimization.

Self-review

• Confirm that {{pos_data_source}}, {{inventory_warehouse_table}}, and all other context variables are correctly parameterized.
• Verify that sell-through calculations accurately account for customer returns and reserve units.
• Check that the output format strictly provides production-grade script logic rather than conceptual pseudocode.