GreenBoxEnterprise Data Architecture
From fragmented reports to a trusted, governed data platform that powers better decisions.
This project demonstrates how I designed a complete enterprise data architecture for GreenBox, including business architecture, dimensional modelling, Snowflake platform design, and a clear implementation roadmap — using realistic, industry-standard practices and tools.
- Snowflake
- dbt
- SQL
- Power BI
- Data Modelling
- Data Architecture
GreenBox
Sustainable packaging for a better tomorrow
Better data.
Greener decisions.
ProjectRoadmap
The end-to-end process from business discovery to AI-ready data consumption.
Step 1: Executive & Department Discovery
Business priorities, pain points, stakeholders and current-state challenges.
Step 2: Business Requirements & Scope
Translate discovery into measurable outcomes and define what the first release will cover.
Step 3: Enterprise Business Architecture
Map capabilities, processes, events, business entities and enterprise KPIs.
Step 4: Enterprise Data & Dimensional Modelling
Conformed dimensions, facts, grains and lifecycle models.
Step 5: Snowflake Platform Architecture
Environments, databases, schemas, roles, warehouses, security and cost controls.
Step 6: Synthetic Source Data & dbt Engineering
Generate safe sample data, land raw sources, transform and test models.
Step 7: Semantic Metrics Layer
Reusable business definitions, lineage and ownership.
Step 8: BI & Reporting
Reports that answer business questions using governed metrics.
Step 9: Governance, Quality & Operations
Ownership, quality, access, lineage, monitoring and change control.
Step 10: AI-Ready Data Consumption
Enable natural-language analytics and AI use cases safely and securely.
01 / Project Overview
Enterprise Data Architecture
Designing a scalable, governed and business-aligned data architecture for GreenBox, a fictional sustainable packaging manufacturer and distributor operating across Australia, New Zealand and Singapore.
- Annual Revenue
- AUD 850M
- Employees
- 2,500
- Operating Markets
- 3
Business Context
Fictional ScenarioGreenBox has grown through acquisitions and geographic expansion, resulting in fragmented business processes, disconnected systems and inconsistent reporting practices.
Finance, Sales, Supply Chain, Marketing and Customer Service operate with different definitions of key business metrics. Leadership lacks a consistent enterprise-wide view of performance.
Key Business Challenges
- Inconsistent definitions of Revenue and Gross Margin.
- Fragmented data across ERP, CRM, e-commerce and warehouse systems.
- Limited visibility across the end-to-end order-to-cash process.
- Unclear ownership of enterprise KPIs and business definitions.
- Limited trust in operational and financial reporting.
Project Objective
Establish an enterprise data architecture that connects business capabilities, processes, events, KPIs and data models into a consistent foundation for analytics and future AI initiatives.
Business
Capabilities & processes
Requirements
Events & KPIs
Data Architecture
Enterprise data models
Technology
Snowflake & dbt
Consumption
BI & AI-ready data
Architecture Principles
Business First
Architecture decisions are driven by business processes, requirements and measurable outcomes.
Governed by Design
Establish clear ownership, definitions, lineage and data quality standards.
Scalable & Integrated
Build reusable enterprise data models and conformed dimensions across business domains.
This is a fictional portfolio case study. GreenBox, its employees, business figures, systems and stakeholder findings are illustrative. No former-employer data is used.
Explore Executive & Department DiscoveryThis section provides a high-level view of the end-to-end architecture, showing how business processes, data sources and the modern data platform work together to deliver trusted, governed and actionable insights.
Source Systems
- NetSuiteERP
- SAPERP
- ShopifyeCommerce
- HubSpotCRM
- ManhattanWMS
- 3PL / Logistics
- Other Sources
Ingestion & Staging
Fivetran
- Raw LayerExternal data landed in Snowflake
Transformation & Modelling
dbt
- Staging Models
- Business Models
- Dimensional Models
- Data Tests
Enterprise Data Platform
Snowflake
- RAW
- STAGING
- CORE
- MARTS
Consumption
- Power BIAnalytics & Reporting
- Semantic LayerGoverned Metrics
- AI / LLMsFuture Use Cases
Key Outcomes
Single source of truth for core business metrics
End-to-end visibility across the supply chain
Improved operational efficiency and OTIF
AI-ready data for future innovation
Step 3 · Business-first design
Business Architecture
Translate business priorities into a structured view of GreenBox's capabilities, processes, events, business entities and enterprise performance measures.
Capabilities
What the business must be able to do
Processes
How work is performed
Events
Meaningful business occurrences
KPIs & rules
How outcomes are measured
Data design
Models and governed metrics
Enterprise capability map
01
Customer & Market Management
Understand markets, prospects, customers, accounts and commercial relationships.
02
Sales & Commercial Management
Manage opportunities, quotes, orders, commercial terms and customer sales performance.
03
Product & Supply Management
Manage product information, procurement, supplier relationships and supply availability.
04
Manufacturing & Operations
Plan and execute production and operational activities. Requirements need further discovery.
05
Order Fulfilment
Allocate stock, pick and pack orders, coordinate shipments and confirm delivery outcomes.
06
Financial Management
Manage financial transactions, accounting, revenue reporting, profitability and payments.
07
Customer Experience Management
Manage cases, complaints, returns-related interactions and customer feedback.
08
Enterprise Performance Management
Monitor business outcomes through governed KPIs, reporting and performance reviews.
09
Data Governance & Analytics
Manage data definitions, ownership, quality, lineage, analytical models and trusted consumption.
10
Risk & Compliance Management
Manage relevant controls, obligations and compliance evidence. Scope requires further discovery.
Step 4 · Data architecture
Enterprise Bus Matrix
The matrix shows which shared dimensions are expected to support each business process. This is a proposed logical design and must be validated against source data and business definitions.
| Business process / fact candidate | Date | Customer | Product | Location | Supplier | Order | Campaign | Account |
|---|---|---|---|---|---|---|---|---|
| Sales order line | Yes | Yes | Yes | Yes | No | Yes | No | No |
| Return line | Yes | Yes | Yes | Yes | No | Yes | No | No |
| Purchase order line | Yes | No | Yes | Yes | Yes | No | No | No |
| Supplier delivery line | Yes | No | Yes | Yes | Yes | No | No | No |
| Inventory movement | Yes | No | Yes | Yes | No | No | No | Conditional |
| Inventory snapshot | Yes | No | Yes | Yes | No | No | No | Conditional |
| Shipment line | Yes | Yes | Yes | Yes | No | Yes | No | No |
| Invoice line | Yes | Yes | Yes | No | No | Yes | No | Conditional |
| Payment application | Yes | Yes | No | No | No | No | No | Yes |
| Lead / campaign performance | Yes | Conditional | No | No | No | No | Yes | No |
| Customer case | Yes | Yes | Conditional | No | No | Conditional | No | No |
● Candidate dimension relationship. *Conditional or role-specific relationship requiring validation.
Explore Enterprise Data & Dimensional ModellingStep 4 · Data architecture
Dimensional Model
Each fact table has a declared grain and a business purpose. Measure definitions and exact source mappings remain subject to validation and business approval.
Conformed dimensions
- dim_customer
- dim_product
- dim_location
- dim_date
- dim_supplier
- dim_campaign
Fact table catalogue
| Fact candidate | Proposed grain — one row per… |
|---|---|
fct_sales_order_line | Product line on a customer sales order |
fct_return_line | Product line on a return transaction |
fct_purchase_order_line | Product line on a purchase order |
fct_supplier_delivery_line | Product line received against a supplier delivery |
fct_inventory_movement | One stock movement for a product and location |
fct_inventory_snapshot | Product × location × snapshot timestamp or date |
fct_order_fulfilment | Order-line fulfilment allocation or lifecycle instance |
fct_shipment_line | Order-line allocation within a shipment |
fct_invoice_line | Product or charge line on an invoice |
fct_payment_application | Payment amount applied to an invoice or receivable |
fct_lead / fct_opportunity | One lead record / one opportunity record or lifecycle instance |
fct_customer_case | One customer service case |
fct_campaign_performance | Proposed campaign × channel × reporting period (to validate) |
Step 5 · Platform
Snowflake Architecture
Design the platform foundation that securely and efficiently supports GreenBox's data products.
Design area 01
Environment strategy
Separate development, test and production responsibilities; promote changes through controlled deployment rather than editing production objects directly.
Design area 02
Database and schema layers
RAW for source-aligned landing data; STAGING for source-specific cleaning; INTERMEDIATE for reusable business transformations; ANALYTICS for dimensions, facts and governed consumption. These are logical conventions to validate against the team's deployment approach.
Design area 03
Compute and workload isolation
Use virtual warehouses sized for ingestion, transformation, BI and ad hoc work as appropriate. Configure auto-suspend/auto-resume, resource monitors and workload-specific sizing; measure before scaling.
Design area 04
Security and access
Use role-based access control and least privilege. Separate service roles from human roles, restrict production write access, protect sensitive columns, and use managed secrets rather than credentials in code.
Step 6 · Engineering
Synthetic Data Plan
Build a safe, reproducible implementation using fictional records that exercise realistic business processes.
Design area 01
Synthetic, never copied
All portfolio data must be generated for this case study. Do not reuse former-employer records, customer names, transaction values, identifiers, screenshots containing confidential information, or proprietary logic.
Design area 02
Candidate source domains
NetSuite: products, locations, customers, orders, order lines and invoices; legacy SAP: selected historical financial or inventory records; Shopify: acquired-business products, customers and orders; HubSpot: leads, contacts, campaigns and opportunities; WMS/3PL: allocations, picks, shipments and deliveries. Treat these as illustrative sources pending validation.
Design area 03
Layered dbt approach
Source definitions and freshness → staging models → intermediate identity mapping and business rules → dimensions and facts → tests and documentation. Keep transformations modular and traceable.
Design area 04
Proposed model sequence
stg_netsuite__* and stg_shopify__* → int_product_mapping and customer identity resolution → int_orders / int_fulfilment → dim_product, dim_customer, dim_location → fct_sales_order_line, fct_shipment_line, fct_invoice_line and other approved facts.
Steps 7–10
Next Steps
With the models and platform designed, the remaining steps turn tested data into governed metrics, trusted reporting, sustainable operations and controlled AI consumption.
Step 7
Semantic Metrics Layer
Provide a governed business-facing layer so BI and future AI consumers reuse approved definitions.
Step 8
BI & Reporting
Demonstrate that the architecture answers business questions with trusted, understandable reporting.
Step 9
Governance, Quality & Operations
Make the data platform maintainable, accountable, observable and safe to operate.
Step 10
AI-Ready Data Consumption
Explore how governed GreenBox data could support natural-language analytics and AI workflows without bypassing controls.