> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/build/stage-2-productize/explore-recipes/full-lifecycle-example.md). # Full lifecycle example This recipe walks through the complete `orders-analytics` Data Product end-to-end. It covers every component in the order you would build them: source declarations, seed, bronze models, silver models, gold models, assertions, tests, semantic models, and business metrics. Use it as a reference when starting a new project or as a checklist to verify that your build is complete. *** ## What this product does `orders-analytics` is an e-commerce data product on PostgreSQL. It turns raw order, customer, product, shipment, and region data into trusted metrics for: * Daily and weekly revenue by region, category, and customer * Customer lifetime value and RFM segmentation * Product performance and sales velocity * Fulfillment conversion by region *** ## Project structure ``` orders-analytics/ ├── config.yaml ├── usage.yaml ├── external_models.yaml ├── seeds/ │ └── order_status_lookup.csv ├── macros/ │ ├── __init__.py │ └── orders_helpers.py ├── linter/ │ └── linters.py ├── models/ │ ├── seeds/ │ │ └── order_status_lookup.sql │ ├── bronze/ │ │ ├── orders.sql │ │ ├── order_items.sql │ │ ├── customers.sql │ │ ├── products.sql │ │ ├── regions.sql │ │ ├── suppliers.sql │ │ ├── warehouses.sql │ │ ├── shipments.sql │ │ └── dim_dates.sql │ ├── silver/ │ │ ├── fct_daily_sales.sql │ │ ├── fct_weekly_sales.sql │ │ ├── dim_customer_profile.sql │ │ └── dim_product_profile.sql │ ├── gold/ │ │ ├── rfm_customer_segmentation.sql │ │ └── sales_funnel_analysis.sql │ ├── semantics/ │ │ ├── daily_sales.yml │ │ ├── customer_profile.yml │ │ └── ... │ └── metrics/ │ ├── daily_sales_performance.yml │ ├── customer_lifetime_value.yml │ └── ... ├── dq/ │ └── fct_daily_sales.yml (and 6 others) ├── audits/ │ ├── daily_sales_metric_consistency.sql │ ├── rfm_score_consistency.sql │ └── order_status_lookup_consistency.sql └── tests/ ├── test_fct_daily_sales.yaml └── test_rfm_customer_segmentation.yaml ``` *** ## Step 1: config.yaml ```yaml name: orders-analytics displayName: Orders Analytics Platform description: Governed e-commerce order analytics for revenue, customer segmentation, product performance, fulfillment, and sales funnel monitoring on PostgreSQL. discoverable: true version: 0.1.2 alignment: sourceAligned domain: sales_operations tags: - e-commerce - orders - sales-analytics linter: enabled: true warnRules: - RequireGrainForAllModels - RequireOwnerForAllModels - RequireDqForAnalyticsModels modelDefaults: dialect: postgres start: '2025-01-01' cron: '*/15 * * * *' beforeAll: - CREATE SCHEMA IF NOT EXISTS bronze; - CREATE SCHEMA IF NOT EXISTS silver; - CREATE SCHEMA IF NOT EXISTS gold; - GRANT USAGE ON SCHEMA bronze, silver, gold TO db_owner; - SET statement_timeout = '120s'; afterAll: - ANALYZE silver.fct_daily_sales; - RESET statement_timeout; gateways: default: connection: type: depot address: dataos://postgresDepot notificationTargets: - type: console notifyOn: - run_end - run_failure - audit_failure - plan_change variables: bronze_schema: bronze silver_schema: silver gold_schema: gold ``` *** ## Step 2: Source declarations `external_models.yaml` declares the nine source tables so Vulcan knows their schema without reading from the warehouse directly: ```yaml - name: '"warehouse"."public"."orders_ext"' grains: [order_id] columns: order_id: INT customer_id: INT order_date: TIMESTAMP warehouse_id: INT order_status: TEXT - name: '"warehouse"."public"."order_items_ext"' grains: [order_id, item_id] columns: order_id: INT item_id: INT product_id: INT quantity: INT unit_price: DECIMAL(10, 2) - name: '"warehouse"."public"."customers_ext"' grains: [customer_id] columns: customer_id: INT region_id: INT name: TEXT email: TEXT signup_date: DATE ``` (plus six more for products, regions, suppliers, warehouses, shipments, and dim\_dates) *** ## Step 3: Custom macros `macros/orders_helpers.py` defines two macros used throughout the project: ```python from vulcan import SQL, macro @macro() def safe_ratio(evaluator, numerator: SQL, denominator: SQL, scale: int = 4) -> str: """Return a rounded ratio, defaulting to 0 when the denominator is zero.""" return f"ROUND(COALESCE({numerator}::NUMERIC / NULLIF({denominator}, 0), 0), {scale})" @macro() def revenue_order_filter(evaluator, status_column: SQL) -> str: """Return the standard revenue-bearing order filter.""" return f"{status_column} <> 'Cancelled'" ``` Write these before any model that needs them. *** ## Step 4: Seed model `seeds/order_status_lookup.csv`: ```csv order_status,status_group,is_fulfilled,is_active_sort,description Confirmed,Open,false,1,Order has been accepted but not yet shipped Shipped,Fulfilled,true,2,Order has been shipped to the customer Cancelled,Closed,false,3,Order was cancelled and excluded from revenue metrics ``` `models/seeds/order_status_lookup.sql`: ```sql MODEL ( name bronze.order_status_lookup, kind SEED ( path '../../seeds/order_status_lookup.csv' ), columns ( order_status TEXT, status_group TEXT, is_fulfilled BOOLEAN, is_active_sort INTEGER, description TEXT ), grains [order_status], owner 'data-team', description 'Static lookup table for order status classification.', assertions ( unique_values(columns := (order_status)), not_null(columns := (order_status, status_group, is_fulfilled)), accepted_values(column := order_status, is_in := ('Confirmed', 'Shipped', 'Cancelled')) ) ); ``` *** ## Step 5: Bronze models Bronze models copy source tables into the warehouse with assertions. One model per source table. `models/bronze/orders.sql`: ```sql MODEL ( name bronze.orders, kind FULL, cron '*/15 * * * *', owner 'data-team', grains [order_id], description 'Order transactions with status, customer, and warehouse.', columns ( order_id INTEGER, customer_id INTEGER, order_date TIMESTAMP, warehouse_id INTEGER, order_status TEXT ), assertions ( unique_values(columns := (order_id)), not_null(columns := (order_id, customer_id, order_date, warehouse_id, order_status)), accepted_values(column := order_status, is_in := ('Confirmed', 'Shipped', 'Cancelled')), order_status_lookup_consistency() ) ); SELECT order_id, customer_id, order_date, warehouse_id, order_status FROM public.orders_ext; ``` The `order_status_lookup_consistency()` assertion references the custom assertion in `audits/order_status_lookup_consistency.sql`. It blocks the model if any status value in the source is not in the lookup seed. Create similar models for `order_items`, `customers`, `products`, `regions`, `suppliers`, `warehouses`, `shipments`, and `dim_dates`. *** ## Step 6: Silver fact models Silver models join bronze tables and apply business logic. Revenue calculations always use `@revenue_order_filter` to exclude Cancelled orders. `models/silver/fct_daily_sales.sql` (abbreviated): ```sql MODEL ( name silver.fct_daily_sales, kind FULL, cron '*/15 * * * *', owner 'data-team', grains [order_date, region_id, customer_id, product_id], description 'Daily sales fact table by order date, customer, product, and region.', columns ( order_date TIMESTAMP, region_id INTEGER, region_name TEXT, customer_id INTEGER, product_id INTEGER, category TEXT, total_orders INTEGER, total_items_sold INTEGER, total_revenue NUMERIC(15, 2), avg_order_value NUMERIC(15, 2), total_shipments INTEGER, shipment_rate NUMERIC(10, 4) ), assertions ( not_null(columns := (order_date, region_id, customer_id, product_id)), forall(criteria := (total_orders >= 0, total_revenue >= 0, shipment_rate >= 0)), daily_sales_metric_consistency() ) ); WITH order_metrics AS ( SELECT o.order_date::DATE AS order_date, c.region_id, r.region_name, o.customer_id, oi.product_id, p.category, COUNT(DISTINCT o.order_id)::INTEGER AS total_orders, SUM(oi.quantity)::INTEGER AS total_items_sold, SUM(oi.quantity * oi.unit_price)::NUMERIC(15, 2) AS total_revenue FROM bronze.orders AS o INNER JOIN bronze.customers AS c ON o.customer_id = c.customer_id INNER JOIN bronze.regions AS r ON c.region_id = r.region_id INNER JOIN bronze.order_items AS oi ON o.order_id = oi.order_id INNER JOIN bronze.products AS p ON oi.product_id = p.product_id WHERE @revenue_order_filter(o.order_status) GROUP BY o.order_date::DATE, c.region_id, r.region_name, o.customer_id, oi.product_id, p.category ) SELECT CAST(om.order_date AS TIMESTAMP) AS order_date, om.region_id, om.region_name, om.customer_id, om.product_id, om.category, om.total_orders, om.total_items_sold, ROUND(om.total_revenue, 2) AS total_revenue, @safe_ratio(om.total_revenue, om.total_orders, 2) AS avg_order_value, ... FROM order_metrics AS om; ``` *** ## Step 7: Silver dimension models Dimension models profile each entity across the full history. `models/silver/dim_customer_profile.sql` builds customer lifetime metrics by joining orders, order items, and shipments, then classifies each customer into a segment (High Value, Medium Value, Low Value, Churned, New). *** ## Step 8: Gold models Gold models consume silver models and produce outputs for specific analytics use cases. `models/gold/rfm_customer_segmentation.sql` reads `silver.dim_customer_profile`, computes recency, frequency, and monetary scores (each 1-5), concatenates them into an `rfm_score` string, and assigns a segment label using CASE expressions. `models/gold/sales_funnel_analysis.sql` reads multiple silver models and computes regional conversion rates from customer registration through order placement through shipment. *** ## Step 9: Custom assertions `audits/daily_sales_metric_consistency.sql`: ```sql AUDIT (name daily_sales_metric_consistency, blocking true); SELECT order_date, region_id, customer_id, product_id, total_orders, total_revenue FROM @this_model WHERE total_orders <= 0 OR total_revenue < 0 OR shipment_rate < 0 OR shipment_rate > 1 OR ROUND(total_revenue / NULLIF(total_orders, 0), 2) <> avg_order_value; ``` `audits/rfm_score_consistency.sql`: ```sql AUDIT (name rfm_score_consistency, blocking true); SELECT customer_id, recency_score, frequency_score, monetary_score, rfm_score, rfm_segment FROM @this_model WHERE recency_score NOT BETWEEN 1 AND 5 OR frequency_score NOT BETWEEN 1 AND 5 OR monetary_score NOT BETWEEN 1 AND 5 OR rfm_score <> recency_score::TEXT || frequency_score::TEXT || monetary_score::TEXT OR rfm_segment NOT IN ( 'Champions', 'Loyal Customers', 'Potential Loyalists', 'Recent Customers', 'Promising', 'Customers Needing Attention', 'At Risk', 'Hibernating', 'Lost' ); ``` *** ## Step 10: Unit tests `tests/test_fct_daily_sales.yaml` verifies the revenue filter: ```yaml test_fct_daily_sales_excludes_cancelled_orders: model: silver.fct_daily_sales inputs: bronze.orders: - order_id: 1 customer_id: 10 order_date: "2026-01-01 10:00:00" warehouse_id: 100 order_status: Shipped - order_id: 2 customer_id: 10 order_date: "2026-01-01 11:00:00" warehouse_id: 100 order_status: Cancelled bronze.customers: - customer_id: 10 region_id: 1 name: "Avery Stone" email: "avery.stone@example.com" signup_date: "2025-12-01" ... outputs: query: rows: - customer_id: 10 total_orders: 1 total_revenue: 20.00 partial: true ``` `tests/test_rfm_customer_segmentation.yaml` verifies the Champion classification for a high-value customer. *** ## Step 11: DQ rule packs `dq/fct_daily_sales.yml`: ```yaml kind: dq name: fct_daily_sales_dq depends_on: silver.fct_daily_sales profiles: - order_date - region_name - category - total_revenue - shipment_rate rules: - row_count >= 20: name: minimum_daily_sales_rows dimension: completeness - missing_count(order_date) = 0: name: no_missing_order_date dimension: completeness - invalid_count(total_revenue) = 0: valid min: 0 name: total_revenue_non_negative dimension: validity - invalid_count(shipment_rate) = 0: valid min: 0 valid max: 1 name: shipment_rate_between_zero_and_one dimension: validity ``` Create similar DQ files for the other silver and gold models. *** ## Step 12: Semantic models `models/semantics/daily_sales.yml`: ```yaml kind: semantic name: daily_sales depends_on: silver.fct_daily_sales description: Daily sales performance by customer, product, category, and region. dimensions: - order_date - region_id - region_name - customer_id - product_id - category measures: - name: total_daily_revenue type: sum expression: "{daily_sales.total_revenue}" description: Total daily gross revenue - name: total_daily_orders type: sum expression: "{daily_sales.total_orders}" description: Total orders at daily grain - name: average_shipment_rate type: avg expression: "{daily_sales.shipment_rate}" description: Average shipment rate segments: - name: high_revenue_days expression: "{daily_sales.total_revenue} >= 500" description: Daily rows with revenue of at least 500 ``` `models/semantics/customer_profile.yml` adds a `joins` block linking customer data to daily sales: ```yaml joins: - name: daily_sales type: one_to_many expression: "{customer_profile.customer_id} = {daily_sales.customer_id}" ``` *** ## Step 13: Business metrics `models/metrics/daily_sales_performance.yml`: ```yaml kind: metric name: daily_sales_performance measure: daily_sales.total_daily_revenue ts: daily_sales.order_date granularity: day dimensions: - name: region ref: daily_sales.region_name - name: category ref: daily_sales.category segments: - daily_sales.high_revenue_days description: Daily revenue trends by region and category. ``` `models/metrics/customer_lifetime_value.yml`: ```yaml kind: metric name: customer_lifetime_value measure: customer_profile.avg_customer_lifetime_value ts: customer_profile.first_order_date granularity: month dimensions: - name: region ref: customer_profile.region_name - name: customer_segment ref: customer_profile.customer_segment description: Average customer lifetime value by customer segment and region. ``` *** ## Step 14: Validate and deploy ```bash # Check project structure and connection vulcan info # Run unit tests vulcan test # Inspect model output vulcan evaluate silver.fct_daily_sales --limit 10 vulcan evaluate gold.rfm_customer_segmentation --limit 10 # Create and apply the dev plan vulcan plan dev --auto-apply # Verify in dev vulcan evaluate silver.fct_daily_sales -e dev --limit 10 # Deploy to production vulcan plan prod --auto-apply ``` *** ## What this example demonstrates | Component | What was covered | | ------------------- | -------------------------------------------------------------------------- | | Source declarations | `external_models.yaml` for all nine source tables | | Seed | CSV loaded into a lookup table with assertions | | Custom macros | `@safe_ratio` and `@revenue_order_filter` in Python | | Bronze models | Source-aligned FULL models with inline assertions | | Silver models | Fact and dimension models joining multiple bronze tables | | Gold models | RFM segmentation and funnel analysis consuming silver | | Custom assertions | Blocking SQL assertions for metric consistency and referential integrity | | Unit tests | YAML tests verifying the revenue filter and RFM scoring | | DQ rule packs | Non-blocking row count, null, and value range checks | | Semantic models | `kind: semantic` with dimensions, measures, segments, and joins | | Business metrics | `kind: metric` combining measure, time column, granularity, and dimensions | | Governance | `beforeAll`/`afterAll` hooks, notifications, and custom linter rules | Every component in this example is used in production Data Products. Adapt it to your own schema and build on top of it. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://v2.dataos.info/build/stage-2-productize/explore-recipes/full-lifecycle-example.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.