1. Data strategy
Purpose, scope, and design intent for this phase.
This page documents a proposed approach for the fictional GreenBox portfolio. Decisions and outcomes are illustrative until validated against business requirements, source data, security constraints, and measured implementation results.
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.
2. Implementation considerations
Controls and decisions to make the design supportable, explainable, and testable.
Data generation requirements
Include split shipments, partial fulfilment, returns, duplicate-like source identities, late-arriving updates, multiple currencies where relevant, missing optional attributes, and reconciliation-friendly totals. Ensure edge cases are intentional and documented.
Testing strategy
Unique and not-null tests for keys; relationship tests for foreign keys; accepted-value tests for statuses; freshness checks; grain-specific uniqueness tests; reconciliation checks for quantities and amounts; business-rule tests only after rules are approved.
Engineering standards
Use version control, environment-specific configuration, reusable macros only where they clarify logic, model descriptions, owner metadata, and repeatable build commands. Avoid embedding secrets or production credentials.
Acceptance criteria
Synthetic data is reproducible; no employer data is present; core dbt models build; critical tests pass; model documentation explains grain and assumptions; row counts and totals can be reconciled through the lifecycle.
3. Acceptance criteria and handoff
Evidence required before this phase should be considered complete.
Step 06
Next step
Define and publish governed metric definitions on top of tested models.
- Requirements and assumptions are documented and traceable.
- Business owners and technical owners are identified where applicable.
- Security, quality, and operational implications are considered.
- Validation evidence is captured; proposed designs are not presented as implemented outcomes.
← Project roadmapPrevious: Step 5 · Snowflake Platform ArchitectureNext: Step 7 · Semantic Metrics Layer →