The same data lives in four systems. Only one copy is governed.
This page is what Fadlie found on the
DataHub showcase-ecommerce catalog. Nothing here was written by
hand: it is generated from real responses of the deployed agent, captured on
2026-08-06, by scripts/faire-la-page.py.
A disagreement is not a gap. When two copies of the same data
carry different values, there is nothing to copy — someone decided, or
someone erred, and neither is an agent's call.
Conflicting domain
dbt/order_details
Data Platform Team
looker/order_details
Data Platform Team
looker/Order Details
Data Platform Team
snowflake/ORDER_DETAILS
Ecommerce Operations
left to a human: Fadlie reports the conflict, it does not pick
What is missing, and where it already exists
Every row below is a value one dataset carries and its twin does
not. No value here was produced by a model. Each names the
dataset it would be copied from — the type that carries a gap refuses to be
constructed without a source. Open a dataset to see its own.
# Order Details Model
## Overview
The `order_details` model provides a comprehensive view of customer orders by combining data from multiple tables in the `order_entry` schema. It includes detailed information about orders, customers, products, shipping, billing, inventory, and promotions.
This denormalized view is designed to support reporting, analysis, and operational insights across the order management process.
## Model Information
- **Name**: `order_details`
- **Type**: View
- **Source Schema**: `order_entry`
- **Database**: `order_entry_db`
- **Update Frequency**: Refreshed on query with current timestamp
## Data Structure
### Order Information
| Column | Type | Description | Example |
| --------------------- | ------------ | ---------------------------------------------------------- | ---------------------------------- |
| order_id | NUMBER | Unique identifier for the order | 12345 |
| order_date | TIMESTAMP_TZ | Date and time when the order was placed including timezone | 2023-05-01 14:30:00-07:00 |
| order_mode | VARCHAR | Method of order placement | 'online', 'phone', 'direct' |
| order_status | NUMBER | Current status of the order | 1=Pending, 2=Processing, 3=Shipped |
| order_total | NUMBER | Total monetary value of the order | 156.78 |
| cost_of_delivery | NUMBER | Shipping and handling cost | 12.99 |
| delivery_type | VARCHAR | Shipping method | 'Standard', 'Express', 'Overnight' |
| wait_till_complete_yn | VARCHAR | Ship complete order or partial | 'Y', 'N' |
| payment_method_code | VARCHAR | Code indicating payment method used | 'CC', 'PayPal', 'ApplePay' |
### Customer Information
| Column | Type | Description | Example |
| --------------- | ------- | ---------------------------------- | ---------------------------- |
| customer_id | NUMBER | Unique identifier for the customer | 5001 |
| cust_first_name | VARCHAR | Customer first name | 'John' |
| cust_last_name | VARCHAR | Customer last name | 'Smith' |
| cust_email | VARCHAR | Customer email address | 'john.smith@example.com' |
| phone_number | VARCHAR | Customer contact phone number | '555-123-4567' |
| customer_class | VARCHAR | Classification of the customer | 'Platinum', 'Gold', 'Silver' |
### Billing Address Information
| Column | Type | Description | Example |
| --------------------- | ------- | --------------------------------------------- | --------------- |
| billing_address_line1 | VARCHAR | First line of customer billing address | '123 Main St' |
| billing_address_line2 | VARCHAR | Second line of customer billing address | 'Apt 4B' |
| billing_town_city | VARCHAR | Town or city of customer billing address | 'Springfield' |
| billing_country | VARCHAR | Country name for the billing address | 'United States' |
| billing_zipcode | VARCHAR | Postal code of customer billing address | '12345' |
| billing_region | VARCHAR | Region/state/province for the billing address | 'Illinois' |
### Shipping Address Information
| Column | Type | Description | Example |
| ---------------------- | ------- | ------------------------------------------ | --------------- |
| shipping_address_line1 | VARCHAR | First line of customer shipping address | '456 Oak St' |
| shipping_address_line2 | VARCHAR | Second line of customer shipping address | 'Suite 101' |
| shipping_town_city | VARCHAR | Town or city of customer shipping address | 'Chicago' |
| shipping_country | VARCHAR | Country name for the shipping address | 'United States' |
| shipping_zipcode | VARCHAR | Postal code of customer shipping address | '60601' |
| shipping_region | VARCHAR | Region/state/province for shipping address | 'Illinois' |
### Warehouse Information
| Column | Type | Description | Example |
| -------------- | ------- | ------------------------------------------------ | ----------------------------- |
| warehouse_id | NUMBER | Identifier of the warehouse fulfilling the order | 3 |
| warehouse_name | VARCHAR | Name of the warehouse location | 'Chicago Distribution Center' |
### Promotion Information
| Column | Type | Description | Example |
| --------------------- | ------- | ------------------------------------------------ | ---------------------------- |
| promotion_id | NUMBER | Identifier of the promotion applied to the order | 42 |
| promotion_name | VARCHAR | Name of the marketing promotion | 'Summer Sale 2023' |
| promotion_description | VARCHAR | Detailed description of the promotion | '20% off all summer apparel' |
### Order Line Item Information
| Column | Type | Description | Example |
| ------------------- | ------- | ------------------------------------------------------ | --------------------------------------------- |
| line_item_id | NUMBER | Line item sequence number within the order | 1 |
| product_id | NUMBER | Identifier of the product ordered | 10042 |
| product_name | VARCHAR | Name of the product | 'Ergonomic Office Chair' |
| product_description | VARCHAR | Brief description of the product | 'Adjustable office chair with lumbar support' |
| category_id | NUMBER | Identifier of the product category | 15 |
| category_name | VARCHAR | Name of the product category | 'Office Furniture' |
| unit_price | NUMBER | Selling price per unit at time of order | 149.99 |
| quantity | NUMBER | Number of units ordered | 2 |
| line_total | NUMBER | Total price for the line item (unit_price \* quantity) | 299.98 |
| dispatch_date | DATE | Date when item was dispatched from warehouse | 2023-05-03 |
| return_date | DATE | Date when item was returned if applicable | 2023-05-15 |
| gift_wrap | VARCHAR | Flag or type of gift wrapping requested | 'Y', 'Premium', 'Basic' |
| condition | VARCHAR | Condition of product | 'New', 'Refurbished' |
| estimated_delivery | DATE | Estimated delivery date for this item | 2023-05-07 |
| list_price | NUMBER | Standard list price for the product | 179.99 |
| product_status | VARCHAR | Current status of the product | 'Available', 'Discontinued' |
| quantity_on_hand | NUMBER | Current inventory count in the fulfilling warehouse | 24 |
| stock_status | VARCHAR | Status of current inventory levels | 'Low Stock', 'In Stock', 'Overstocked' |
### Calculated Fields
| Column | Type | Description | Example |
| ---------------- | ------- | ---------------------------------------------------- | ---------------------------------------- |
| discount_amount | NUMBER | Amount of discount applied (list_price - unit_price) | 30.00 |
| discount_percent | NUMBER | Percentage discount applied | 16.67 |
| delivery_status | VARCHAR | Current status of delivery | 'Not Shipped', 'In Transit', 'Delivered' |
| return_status | VARCHAR | Status indicating if item was returned | 'Returned', 'Not Returned' |
### Timestamp Information
| Column | Type | Description | Example |
| ---------- | ------------ | ----------------------------------------------------------------------- | ----------------------------------- |
| updated_at | TIMESTAMP_TZ | Timestamp when this record was last refreshed with nanosecond precision | 2023-07-15 08:23:45.123456789-07:00 |
## Sources and Joins
This model combines data from the following source tables:
- `orders` - Primary table containing order header information
- `customers` - Customer demographic and contact information
- `addresses` - Billing and shipping address details
- `countries` - Country information for addresses
- `regions` - Regional information for addresses
- `warehouses` - Warehouse details for order fulfillment
- `promotions` - Promotion information applied to orders
- `order_items` - Individual line items within each order
- `products` - Product information for ordered items
- `product_categories` - Category information for products
- `inventories` - Inventory levels for products at warehouses
## Data Tests
The following data quality tests are applied to this model:
### Primary Key Tests
- `order_id` and `line_item_id` combination is unique
- `order_id` is not null
### Referential Integrity Tests
- `customer_id` exists in the customers table
- `product_id` exists in the products table
- `warehouse_id` exists in the warehouses table
- `promotion_id` exists in the promotions table (when not null)
- `category_id` exists in the product_categories table
### Value Tests
- `unit_price` and `quantity` are positive values
- `line_total` equals `unit_price` \* `quantity`
- `order_status` contains valid status codes
- `wait_till_complete_yn` contains only 'Y' or 'N'
- `stock_status` contains only valid values: 'Low Stock', 'In Stock', 'Overstocked', 'Unknown'
- `delivery_status` contains only valid values: 'Not Shipped', 'In Transit', 'Delivered', 'Unknown'
- `return_status` contains only valid values: 'Returned', 'Not Returned'
## Usage Examples
### Basic Order Query
```sql
SELECT
order_id,
order_date,
cust_first_name || ' ' || cust_last_name AS customer_name,
order_total,
delivery_status
FROM order_details
WHERE order_date >= DATEADD(month, -1, CURRENT_DATE())
ORDER BY order_date DESC
```
### Order Analysis by Category
```sql
SELECT
category_name,
COUNT(DISTINCT order_id) AS order_count,
SUM(line_total) AS total_revenue,
AVG(discount_percent) AS avg_discount_pct
FROM order_details
WHERE order_date >= DATEADD(year, -1, CURRENT_DATE())
GROUP BY category_name
ORDER BY total_revenue DESC
```
### Delivery Performance
```sql
SELECT
warehouse_name,
delivery_type,
AVG(DATEDIFF(day, order_date, dispatch_date)) AS avg_processing_days,
AVG(DATEDIFF(day, dispatch_date, estimated_delivery)) AS avg_transit_days,
COUNT(*) AS shipment_count
FROM order_details
WHERE dispatch_date IS NOT NULL
AND order_date >= DATEADD(month, -6, CURRENT_DATE())
GROUP BY warehouse_name, delivery_type
ORDER BY warehouse_name, avg_processing_days
```
### Returns Analysis
```sql
SELECT
product_name,
COUNT(*) AS total_ordered,
SUM(CASE WHEN return_status = 'Returned' THEN 1 ELSE 0 END) AS total_returned,
(SUM(CASE WHEN return_status = 'Returned' THEN 1 ELSE 0 END) / COUNT(*)::float) * 100 AS return_rate
FROM order_details
WHERE order_date >= DATEADD(month, -3, CURRENT_DATE())
GROUP BY product_name
HAVING COUNT(*) > 10
ORDER BY return_rate DESC
LIMIT 20
```
## Maintenance and Refresh
As a view, this model is refreshed with each query. The timestamp indicates when the data was last queried and can be used to track data freshness. Since the model pulls directly from source tables, it always reflects the current state of the data. `updated_at`
## Dependencies
This model depends on:
- Source tables from the schema `order_entry`
- Any data quality tests defined in the file `schema.yml`
## Additional Notes
- This model includes sensitive customer information. Ensure appropriate access controls are in place.
- Performance considerations: This view joins multiple tables and may be resource-intensive for large datasets. Consider materializing as a table with scheduled refreshes for frequent reporting needs.
- For large reporting needs, consider creating aggregated models derived from this model rather than running complex aggregations directly.
# Order Details Model
## Overview
The `order_details` model provides a comprehensive view of customer orders by combining data from multiple tables in the `order_entry` schema. It includes detailed information about orders, customers, products, shipping, billing, inventory, and promotions.
This denormalized view is designed to support reporting, analysis, and operational insights across the order management process.
## Model Information
- **Name**: `order_details`
- **Type**: View
- **Source Schema**: `order_entry`
- **Database**: `order_entry_db`
- **Update Frequency**: Refreshed on query with current timestamp
## Data Structure
### Order Information
| Column | Type | Description | Example |
| --------------------- | ------------ | ---------------------------------------------------------- | ---------------------------------- |
| order_id | NUMBER | Unique identifier for the order | 12345 |
| order_date | TIMESTAMP_TZ | Date and time when the order was placed including timezone | 2023-05-01 14:30:00-07:00 |
| order_mode | VARCHAR | Method of order placement | 'online', 'phone', 'direct' |
| order_status | NUMBER | Current status of the order | 1=Pending, 2=Processing, 3=Shipped |
| order_total | NUMBER | Total monetary value of the order | 156.78 |
| cost_of_delivery | NUMBER | Shipping and handling cost | 12.99 |
| delivery_type | VARCHAR | Shipping method | 'Standard', 'Express', 'Overnight' |
| wait_till_complete_yn | VARCHAR | Ship complete order or partial | 'Y', 'N' |
| payment_method_code | VARCHAR | Code indicating payment method used | 'CC', 'PayPal', 'ApplePay' |
### Customer Information
| Column | Type | Description | Example |
| --------------- | ------- | ---------------------------------- | ---------------------------- |
| customer_id | NUMBER | Unique identifier for the customer | 5001 |
| cust_first_name | VARCHAR | Customer first name | 'John' |
| cust_last_name | VARCHAR | Customer last name | 'Smith' |
| cust_email | VARCHAR | Customer email address | 'john.smith@example.com' |
| phone_number | VARCHAR | Customer contact phone number | '555-123-4567' |
| customer_class | VARCHAR | Classification of the customer | 'Platinum', 'Gold', 'Silver' |
### Billing Address Information
| Column | Type | Description | Example |
| --------------------- | ------- | --------------------------------------------- | --------------- |
| billing_address_line1 | VARCHAR | First line of customer billing address | '123 Main St' |
| billing_address_line2 | VARCHAR | Second line of customer billing address | 'Apt 4B' |
| billing_town_city | VARCHAR | Town or city of customer billing address | 'Springfield' |
| billing_country | VARCHAR | Country name for the billing address | 'United States' |
| billing_zipcode | VARCHAR | Postal code of customer billing address | '12345' |
| billing_region | VARCHAR | Region/state/province for the billing address | 'Illinois' |
### Shipping Address Information
| Column | Type | Description | Example |
| ---------------------- | ------- | ------------------------------------------ | --------------- |
| shipping_address_line1 | VARCHAR | First line of customer shipping address | '456 Oak St' |
| shipping_address_line2 | VARCHAR | Second line of customer shipping address | 'Suite 101' |
| shipping_town_city | VARCHAR | Town or city of customer shipping address | 'Chicago' |
| shipping_country | VARCHAR | Country name for the shipping address | 'United States' |
| shipping_zipcode | VARCHAR | Postal code of customer shipping address | '60601' |
| shipping_region | VARCHAR | Region/state/province for shipping address | 'Illinois' |
### Warehouse Information
| Column | Type | Description | Example |
| -------------- | ------- | ------------------------------------------------ | ----------------------------- |
| warehouse_id | NUMBER | Identifier of the warehouse fulfilling the order | 3 |
| warehouse_name | VARCHAR | Name of the warehouse location | 'Chicago Distribution Center' |
### Promotion Information
| Column | Type | Description | Example |
| --------------------- | ------- | ------------------------------------------------ | ---------------------------- |
| promotion_id | NUMBER | Identifier of the promotion applied to the order | 42 |
| promotion_name | VARCHAR | Name of the marketing promotion | 'Summer Sale 2023' |
| promotion_description | VARCHAR | Detailed description of the promotion | '20% off all summer apparel' |
### Order Line Item Information
| Column | Type | Description | Example |
| ------------------- | ------- | ------------------------------------------------------ | --------------------------------------------- |
| line_item_id | NUMBER | Line item sequence number within the order | 1 |
| product_id | NUMBER | Identifier of the product ordered | 10042 |
| product_name | VARCHAR | Name of the product | 'Ergonomic Office Chair' |
| product_description | VARCHAR | Brief description of the product | 'Adjustable office chair with lumbar support' |
| category_id | NUMBER | Identifier of the product category | 15 |
| category_name | VARCHAR | Name of the product category | 'Office Furniture' |
| unit_price | NUMBER | Selling price per unit at time of order | 149.99 |
| quantity | NUMBER | Number of units ordered | 2 |
| line_total | NUMBER | Total price for the line item (unit_price \* quantity) | 299.98 |
| dispatch_date | DATE | Date when item was dispatched from warehouse | 2023-05-03 |
| return_date | DATE | Date when item was returned if applicable | 2023-05-15 |
| gift_wrap | VARCHAR | Flag or type of gift wrapping requested | 'Y', 'Premium', 'Basic' |
| condition | VARCHAR | Condition of product | 'New', 'Refurbished' |
| estimated_delivery | DATE | Estimated delivery date for this item | 2023-05-07 |
| list_price | NUMBER | Standard list price for the product | 179.99 |
| product_status | VARCHAR | Current status of the product | 'Available', 'Discontinued' |
| quantity_on_hand | NUMBER | Current inventory count in the fulfilling warehouse | 24 |
| stock_status | VARCHAR | Status of current inventory levels | 'Low Stock', 'In Stock', 'Overstocked' |
### Calculated Fields
| Column | Type | Description | Example |
| ---------------- | ------- | ---------------------------------------------------- | ---------------------------------------- |
| discount_amount | NUMBER | Amount of discount applied (list_price - unit_price) | 30.00 |
| discount_percent | NUMBER | Percentage discount applied | 16.67 |
| delivery_status | VARCHAR | Current status of delivery | 'Not Shipped', 'In Transit', 'Delivered' |
| return_status | VARCHAR | Status indicating if item was returned | 'Returned', 'Not Returned' |
### Timestamp Information
| Column | Type | Description | Example |
| ---------- | ------------ | ----------------------------------------------------------------------- | ----------------------------------- |
| updated_at | TIMESTAMP_TZ | Timestamp when this record was last refreshed with nanosecond precision | 2023-07-15 08:23:45.123456789-07:00 |
## Sources and Joins
This model combines data from the following source tables:
- `orders` - Primary table containing order header information
- `customers` - Customer demographic and contact information
- `addresses` - Billing and shipping address details
- `countries` - Country information for addresses
- `regions` - Regional information for addresses
- `warehouses` - Warehouse details for order fulfillment
- `promotions` - Promotion information applied to orders
- `order_items` - Individual line items within each order
- `products` - Product information for ordered items
- `product_categories` - Category information for products
- `inventories` - Inventory levels for products at warehouses
## Data Tests
The following data quality tests are applied to this model:
### Primary Key Tests
- `order_id` and `line_item_id` combination is unique
- `order_id` is not null
### Referential Integrity Tests
- `customer_id` exists in the customers table
- `product_id` exists in the products table
- `warehouse_id` exists in the warehouses table
- `promotion_id` exists in the promotions table (when not null)
- `category_id` exists in the product_categories table
### Value Tests
- `unit_price` and `quantity` are positive values
- `line_total` equals `unit_price` \* `quantity`
- `order_status` contains valid status codes
- `wait_till_complete_yn` contains only 'Y' or 'N'
- `stock_status` contains only valid values: 'Low Stock', 'In Stock', 'Overstocked', 'Unknown'
- `delivery_status` contains only valid values: 'Not Shipped', 'In Transit', 'Delivered', 'Unknown'
- `return_status` contains only valid values: 'Returned', 'Not Returned'
## Usage Examples
### Basic Order Query
```sql
SELECT
order_id,
order_date,
cust_first_name || ' ' || cust_last_name AS customer_name,
order_total,
delivery_status
FROM order_details
WHERE order_date >= DATEADD(month, -1, CURRENT_DATE())
ORDER BY order_date DESC
```
### Order Analysis by Category
```sql
SELECT
category_name,
COUNT(DISTINCT order_id) AS order_count,
SUM(line_total) AS total_revenue,
AVG(discount_percent) AS avg_discount_pct
FROM order_details
WHERE order_date >= DATEADD(year, -1, CURRENT_DATE())
GROUP BY category_name
ORDER BY total_revenue DESC
```
### Delivery Performance
```sql
SELECT
warehouse_name,
delivery_type,
AVG(DATEDIFF(day, order_date, dispatch_date)) AS avg_processing_days,
AVG(DATEDIFF(day, dispatch_date, estimated_delivery)) AS avg_transit_days,
COUNT(*) AS shipment_count
FROM order_details
WHERE dispatch_date IS NOT NULL
AND order_date >= DATEADD(month, -6, CURRENT_DATE())
GROUP BY warehouse_name, delivery_type
ORDER BY warehouse_name, avg_processing_days
```
### Returns Analysis
```sql
SELECT
product_name,
COUNT(*) AS total_ordered,
SUM(CASE WHEN return_status = 'Returned' THEN 1 ELSE 0 END) AS total_returned,
(SUM(CASE WHEN return_status = 'Returned' THEN 1 ELSE 0 END) / COUNT(*)::float) * 100 AS return_rate
FROM order_details
WHERE order_date >= DATEADD(month, -3, CURRENT_DATE())
GROUP BY product_name
HAVING COUNT(*) > 10
ORDER BY return_rate DESC
LIMIT 20
```
## Maintenance and Refresh
As a view, this model is refreshed with each query. The timestamp indicates when the data was last queried and can be used to track data freshness. Since the model pulls directly from source tables, it always reflects the current state of the data. `updated_at`
## Dependencies
This model depends on:
- Source tables from the schema `order_entry`
- Any data quality tests defined in the file `schema.yml`
## Additional Notes
- This model includes sensitive customer information. Ensure appropriate access controls are in place.
- Performance considerations: This view joins multiple tables and may be resource-intensive for large datasets. Consider materializing as a table with scheduled refreshes for frequent reporting needs.
- For large reporting needs, consider creating aggregated models derived from this model rather than running complex aggregations directly.
Contains information about marketing promotions and campaigns
dbt/promotions
—
domain
Data Platform Team
dbt/promotions
—
owner
urn:li:corpGroup:b2fd91.ORG_DATA_PLATFORM
dbt/promotions
—
owner
urn:li:corpuser:b2fd91.EMP006
dbt/promotions
postgres/regions8 gaps
column
kind
value it is missing
copied from
country_id
column description
Foreign key to the countries table
dbt/regions
nls_language
column description
Default language code for the region
dbt/regions
region_id
column description
Unique identifier for the region
dbt/regions
region_name
column description
Name of the region (state, province, etc.)
dbt/regions
—
description
Reference table for regional information within countries
dbt/regions
—
domain
Data Platform Team
dbt/regions
—
owner
urn:li:corpGroup:b2fd91.ORG_DATA_PLATFORM
dbt/regions
—
owner
urn:li:corpuser:b2fd91.EMP006
dbt/regions
postgres/warehouses8 gaps
column
kind
value it is missing
copied from
location_id
column description
Reference to the physical location
dbt/warehouses
warehouse_id
column description
Unique identifier for the warehouse
dbt/warehouses
warehouse_name
column description
Name of the warehouse location
dbt/warehouses
wh_geo_location
column description
Geographic coordinates of the warehouse
dbt/warehouses
—
description
Contains information about physical distribution centers
dbt/warehouses
—
domain
Data Platform Team
dbt/warehouses
—
owner
urn:li:corpGroup:b2fd91.ORG_DATA_PLATFORM
dbt/warehouses
—
owner
urn:li:corpuser:b2fd91.EMP006
dbt/warehouses
powerbi/ORDER_DETAILS12 gaps
column
kind
value it is missing
copied from
—
description
# Order Details Model
## Overview
The `order_details` model provides a comprehensive view of customer orders by combining data from multiple tables in the `order_entry` schema. It includes detailed information about orders, customers, products, shipping, billing, inventory, and promotions.
This denormalized view is designed to support reporting, analysis, and operational insights across the order management process.
## Model Information
- **Name**: `order_details`
- **Type**: View
- **Source Schema**: `order_entry`
- **Database**: `order_entry_db`
- **Update Frequency**: Refreshed on query with current timestamp
## Data Structure
### Order Information
| Column | Type | Description | Example |
| --------------------- | ------------ | ---------------------------------------------------------- | ---------------------------------- |
| order_id | NUMBER | Unique identifier for the order | 12345 |
| order_date | TIMESTAMP_TZ | Date and time when the order was placed including timezone | 2023-05-01 14:30:00-07:00 |
| order_mode | VARCHAR | Method of order placement | 'online', 'phone', 'direct' |
| order_status | NUMBER | Current status of the order | 1=Pending, 2=Processing, 3=Shipped |
| order_total | NUMBER | Total monetary value of the order | 156.78 |
| cost_of_delivery | NUMBER | Shipping and handling cost | 12.99 |
| delivery_type | VARCHAR | Shipping method | 'Standard', 'Express', 'Overnight' |
| wait_till_complete_yn | VARCHAR | Ship complete order or partial | 'Y', 'N' |
| payment_method_code | VARCHAR | Code indicating payment method used | 'CC', 'PayPal', 'ApplePay' |
### Customer Information
| Column | Type | Description | Example |
| --------------- | ------- | ---------------------------------- | ---------------------------- |
| customer_id | NUMBER | Unique identifier for the customer | 5001 |
| cust_first_name | VARCHAR | Customer first name | 'John' |
| cust_last_name | VARCHAR | Customer last name | 'Smith' |
| cust_email | VARCHAR | Customer email address | 'john.smith@example.com' |
| phone_number | VARCHAR | Customer contact phone number | '555-123-4567' |
| customer_class | VARCHAR | Classification of the customer | 'Platinum', 'Gold', 'Silver' |
### Billing Address Information
| Column | Type | Description | Example |
| --------------------- | ------- | --------------------------------------------- | --------------- |
| billing_address_line1 | VARCHAR | First line of customer billing address | '123 Main St' |
| billing_address_line2 | VARCHAR | Second line of customer billing address | 'Apt 4B' |
| billing_town_city | VARCHAR | Town or city of customer billing address | 'Springfield' |
| billing_country | VARCHAR | Country name for the billing address | 'United States' |
| billing_zipcode | VARCHAR | Postal code of customer billing address | '12345' |
| billing_region | VARCHAR | Region/state/province for the billing address | 'Illinois' |
### Shipping Address Information
| Column | Type | Description | Example |
| ---------------------- | ------- | ------------------------------------------ | --------------- |
| shipping_address_line1 | VARCHAR | First line of customer shipping address | '456 Oak St' |
| shipping_address_line2 | VARCHAR | Second line of customer shipping address | 'Suite 101' |
| shipping_town_city | VARCHAR | Town or city of customer shipping address | 'Chicago' |
| shipping_country | VARCHAR | Country name for the shipping address | 'United States' |
| shipping_zipcode | VARCHAR | Postal code of customer shipping address | '60601' |
| shipping_region | VARCHAR | Region/state/province for shipping address | 'Illinois' |
### Warehouse Information
| Column | Type | Description | Example |
| -------------- | ------- | ------------------------------------------------ | ----------------------------- |
| warehouse_id | NUMBER | Identifier of the warehouse fulfilling the order | 3 |
| warehouse_name | VARCHAR | Name of the warehouse location | 'Chicago Distribution Center' |
### Promotion Information
| Column | Type | Description | Example |
| --------------------- | ------- | ------------------------------------------------ | ---------------------------- |
| promotion_id | NUMBER | Identifier of the promotion applied to the order | 42 |
| promotion_name | VARCHAR | Name of the marketing promotion | 'Summer Sale 2023' |
| promotion_description | VARCHAR | Detailed description of the promotion | '20% off all summer apparel' |
### Order Line Item Information
| Column | Type | Description | Example |
| ------------------- | ------- | ------------------------------------------------------ | --------------------------------------------- |
| line_item_id | NUMBER | Line item sequence number within the order | 1 |
| product_id | NUMBER | Identifier of the product ordered | 10042 |
| product_name | VARCHAR | Name of the product | 'Ergonomic Office Chair' |
| product_description | VARCHAR | Brief description of the product | 'Adjustable office chair with lumbar support' |
| category_id | NUMBER | Identifier of the product category | 15 |
| category_name | VARCHAR | Name of the product category | 'Office Furniture' |
| unit_price | NUMBER | Selling price per unit at time of order | 149.99 |
| quantity | NUMBER | Number of units ordered | 2 |
| line_total | NUMBER | Total price for the line item (unit_price \* quantity) | 299.98 |
| dispatch_date | DATE | Date when item was dispatched from warehouse | 2023-05-03 |
| return_date | DATE | Date when item was returned if applicable | 2023-05-15 |
| gift_wrap | VARCHAR | Flag or type of gift wrapping requested | 'Y', 'Premium', 'Basic' |
| condition | VARCHAR | Condition of product | 'New', 'Refurbished' |
| estimated_delivery | DATE | Estimated delivery date for this item | 2023-05-07 |
| list_price | NUMBER | Standard list price for the product | 179.99 |
| product_status | VARCHAR | Current status of the product | 'Available', 'Discontinued' |
| quantity_on_hand | NUMBER | Current inventory count in the fulfilling warehouse | 24 |
| stock_status | VARCHAR | Status of current inventory levels | 'Low Stock', 'In Stock', 'Overstocked' |
### Calculated Fields
| Column | Type | Description | Example |
| ---------------- | ------- | ---------------------------------------------------- | ---------------------------------------- |
| discount_amount | NUMBER | Amount of discount applied (list_price - unit_price) | 30.00 |
| discount_percent | NUMBER | Percentage discount applied | 16.67 |
| delivery_status | VARCHAR | Current status of delivery | 'Not Shipped', 'In Transit', 'Delivered' |
| return_status | VARCHAR | Status indicating if item was returned | 'Returned', 'Not Returned' |
### Timestamp Information
| Column | Type | Description | Example |
| ---------- | ------------ | ----------------------------------------------------------------------- | ----------------------------------- |
| updated_at | TIMESTAMP_TZ | Timestamp when this record was last refreshed with nanosecond precision | 2023-07-15 08:23:45.123456789-07:00 |
## Sources and Joins
This model combines data from the following source tables:
- `orders` - Primary table containing order header information
- `customers` - Customer demographic and contact information
- `addresses` - Billing and shipping address details
- `countries` - Country information for addresses
- `regions` - Regional information for addresses
- `warehouses` - Warehouse details for order fulfillment
- `promotions` - Promotion information applied to orders
- `order_items` - Individual line items within each order
- `products` - Product information for ordered items
- `product_categories` - Category information for products
- `inventories` - Inventory levels for products at warehouses
## Data Tests
The following data quality tests are applied to this model:
### Primary Key Tests
- `order_id` and `line_item_id` combination is unique
- `order_id` is not null
### Referential Integrity Tests
- `customer_id` exists in the customers table
- `product_id` exists in the products table
- `warehouse_id` exists in the warehouses table
- `promotion_id` exists in the promotions table (when not null)
- `category_id` exists in the product_categories table
### Value Tests
- `unit_price` and `quantity` are positive values
- `line_total` equals `unit_price` \* `quantity`
- `order_status` contains valid status codes
- `wait_till_complete_yn` contains only 'Y' or 'N'
- `stock_status` contains only valid values: 'Low Stock', 'In Stock', 'Overstocked', 'Unknown'
- `delivery_status` contains only valid values: 'Not Shipped', 'In Transit', 'Delivered', 'Unknown'
- `return_status` contains only valid values: 'Returned', 'Not Returned'
## Usage Examples
### Basic Order Query
```sql
SELECT
order_id,
order_date,
cust_first_name || ' ' || cust_last_name AS customer_name,
order_total,
delivery_status
FROM order_details
WHERE order_date >= DATEADD(month, -1, CURRENT_DATE())
ORDER BY order_date DESC
```
### Order Analysis by Category
```sql
SELECT
category_name,
COUNT(DISTINCT order_id) AS order_count,
SUM(line_total) AS total_revenue,
AVG(discount_percent) AS avg_discount_pct
FROM order_details
WHERE order_date >= DATEADD(year, -1, CURRENT_DATE())
GROUP BY category_name
ORDER BY total_revenue DESC
```
### Delivery Performance
```sql
SELECT
warehouse_name,
delivery_type,
AVG(DATEDIFF(day, order_date, dispatch_date)) AS avg_processing_days,
AVG(DATEDIFF(day, dispatch_date, estimated_delivery)) AS avg_transit_days,
COUNT(*) AS shipment_count
FROM order_details
WHERE dispatch_date IS NOT NULL
AND order_date >= DATEADD(month, -6, CURRENT_DATE())
GROUP BY warehouse_name, delivery_type
ORDER BY warehouse_name, avg_processing_days
```
### Returns Analysis
```sql
SELECT
product_name,
COUNT(*) AS total_ordered,
SUM(CASE WHEN return_status = 'Returned' THEN 1 ELSE 0 END) AS total_returned,
(SUM(CASE WHEN return_status = 'Returned' THEN 1 ELSE 0 END) / COUNT(*)::float) * 100 AS return_rate
FROM order_details
WHERE order_date >= DATEADD(month, -3, CURRENT_DATE())
GROUP BY product_name
HAVING COUNT(*) > 10
ORDER BY return_rate DESC
LIMIT 20
```
## Maintenance and Refresh
As a view, this model is refreshed with each query. The timestamp indicates when the data was last queried and can be used to track data freshness. Since the model pulls directly from source tables, it always reflects the current state of the data. `updated_at`
## Dependencies
This model depends on:
- Source tables from the schema `order_entry`
- Any data quality tests defined in the file `schema.yml`
## Additional Notes
- This model includes sensitive customer information. Ensure appropriate access controls are in place.
- Performance considerations: This view joins multiple tables and may be resource-intensive for large datasets. Consider materializing as a table with scheduled refreshes for frequent reporting needs.
- For large reporting needs, consider creating aggregated models derived from this model rather than running complex aggregations directly.
Contains multiple shipping and billing addresses for customers
dbt/addresses
—
domain
Data Platform Team
dbt/addresses
—
owner
urn:li:corpGroup:b2fd91.ORG_DATA_PLATFORM
dbt/addresses
—
owner
urn:li:corpuser:b2fd91.EMP006
dbt/addresses
snowflake/COUNTRIES8 gaps
column
kind
value it is missing
copied from
country_code
column description
ISO two-letter country code
dbt/countries
country_id
column description
Unique identifier for the country
dbt/countries
country_name
column description
Name of the country
dbt/countries
nls_territory
column description
NLS territory setting for localization
dbt/countries
—
description
Reference table for country information
dbt/countries
—
domain
Data Platform Team
dbt/countries
—
owner
urn:li:corpGroup:b2fd91.ORG_DATA_PLATFORM
dbt/countries
—
owner
urn:li:corpuser:b2fd91.EMP006
dbt/countries
snowflake/CUSTOMERS1 gap
column
kind
value it is missing
copied from
—
description
Contains customer demographic and contact information
dbt/customers
snowflake/INVENTORIES10 gaps
column
kind
value it is missing
copied from
max_stock_level
column description
Maximum storage capacity for this product
dbt/inventories
product_id
column description
Foreign key to the products table
dbt/inventories
quantity_on_hand
column description
Current inventory count for this product in this warehouse
dbt/inventories
reorder_quantity
column description
Standard reorder quantity when restocking
dbt/inventories
restock_level
column description
Threshold at which restocking is initiated
dbt/inventories
warehouse_id
column description
Foreign key to the warehouses table
dbt/inventories
—
description
Tracks product inventory levels across warehouses
dbt/inventories
—
domain
Data Platform Team
dbt/inventories
—
owner
urn:li:corpGroup:b2fd91.ORG_DATA_PLATFORM
dbt/inventories
—
owner
urn:li:corpuser:b2fd91.EMP006
dbt/inventories
snowflake/ORDERS1 gap
column
kind
value it is missing
copied from
—
description
Contains header information for customer orders
dbt/orders
snowflake/ORDER_DETAILS10 gaps
column
kind
value it is missing
copied from
—
description
# Order Details Model
## Overview
The `order_details` model provides a comprehensive view of customer orders by combining data from multiple tables in the `order_entry` schema. It includes detailed information about orders, customers, products, shipping, billing, inventory, and promotions.
This denormalized view is designed to support reporting, analysis, and operational insights across the order management process.
## Model Information
- **Name**: `order_details`
- **Type**: View
- **Source Schema**: `order_entry`
- **Database**: `order_entry_db`
- **Update Frequency**: Refreshed on query with current timestamp
## Data Structure
### Order Information
| Column | Type | Description | Example |
| --------------------- | ------------ | ---------------------------------------------------------- | ---------------------------------- |
| order_id | NUMBER | Unique identifier for the order | 12345 |
| order_date | TIMESTAMP_TZ | Date and time when the order was placed including timezone | 2023-05-01 14:30:00-07:00 |
| order_mode | VARCHAR | Method of order placement | 'online', 'phone', 'direct' |
| order_status | NUMBER | Current status of the order | 1=Pending, 2=Processing, 3=Shipped |
| order_total | NUMBER | Total monetary value of the order | 156.78 |
| cost_of_delivery | NUMBER | Shipping and handling cost | 12.99 |
| delivery_type | VARCHAR | Shipping method | 'Standard', 'Express', 'Overnight' |
| wait_till_complete_yn | VARCHAR | Ship complete order or partial | 'Y', 'N' |
| payment_method_code | VARCHAR | Code indicating payment method used | 'CC', 'PayPal', 'ApplePay' |
### Customer Information
| Column | Type | Description | Example |
| --------------- | ------- | ---------------------------------- | ---------------------------- |
| customer_id | NUMBER | Unique identifier for the customer | 5001 |
| cust_first_name | VARCHAR | Customer first name | 'John' |
| cust_last_name | VARCHAR | Customer last name | 'Smith' |
| cust_email | VARCHAR | Customer email address | 'john.smith@example.com' |
| phone_number | VARCHAR | Customer contact phone number | '555-123-4567' |
| customer_class | VARCHAR | Classification of the customer | 'Platinum', 'Gold', 'Silver' |
### Billing Address Information
| Column | Type | Description | Example |
| --------------------- | ------- | --------------------------------------------- | --------------- |
| billing_address_line1 | VARCHAR | First line of customer billing address | '123 Main St' |
| billing_address_line2 | VARCHAR | Second line of customer billing address | 'Apt 4B' |
| billing_town_city | VARCHAR | Town or city of customer billing address | 'Springfield' |
| billing_country | VARCHAR | Country name for the billing address | 'United States' |
| billing_zipcode | VARCHAR | Postal code of customer billing address | '12345' |
| billing_region | VARCHAR | Region/state/province for the billing address | 'Illinois' |
### Shipping Address Information
| Column | Type | Description | Example |
| ---------------------- | ------- | ------------------------------------------ | --------------- |
| shipping_address_line1 | VARCHAR | First line of customer shipping address | '456 Oak St' |
| shipping_address_line2 | VARCHAR | Second line of customer shipping address | 'Suite 101' |
| shipping_town_city | VARCHAR | Town or city of customer shipping address | 'Chicago' |
| shipping_country | VARCHAR | Country name for the shipping address | 'United States' |
| shipping_zipcode | VARCHAR | Postal code of customer shipping address | '60601' |
| shipping_region | VARCHAR | Region/state/province for shipping address | 'Illinois' |
### Warehouse Information
| Column | Type | Description | Example |
| -------------- | ------- | ------------------------------------------------ | ----------------------------- |
| warehouse_id | NUMBER | Identifier of the warehouse fulfilling the order | 3 |
| warehouse_name | VARCHAR | Name of the warehouse location | 'Chicago Distribution Center' |
### Promotion Information
| Column | Type | Description | Example |
| --------------------- | ------- | ------------------------------------------------ | ---------------------------- |
| promotion_id | NUMBER | Identifier of the promotion applied to the order | 42 |
| promotion_name | VARCHAR | Name of the marketing promotion | 'Summer Sale 2023' |
| promotion_description | VARCHAR | Detailed description of the promotion | '20% off all summer apparel' |
### Order Line Item Information
| Column | Type | Description | Example |
| ------------------- | ------- | ------------------------------------------------------ | --------------------------------------------- |
| line_item_id | NUMBER | Line item sequence number within the order | 1 |
| product_id | NUMBER | Identifier of the product ordered | 10042 |
| product_name | VARCHAR | Name of the product | 'Ergonomic Office Chair' |
| product_description | VARCHAR | Brief description of the product | 'Adjustable office chair with lumbar support' |
| category_id | NUMBER | Identifier of the product category | 15 |
| category_name | VARCHAR | Name of the product category | 'Office Furniture' |
| unit_price | NUMBER | Selling price per unit at time of order | 149.99 |
| quantity | NUMBER | Number of units ordered | 2 |
| line_total | NUMBER | Total price for the line item (unit_price \* quantity) | 299.98 |
| dispatch_date | DATE | Date when item was dispatched from warehouse | 2023-05-03 |
| return_date | DATE | Date when item was returned if applicable | 2023-05-15 |
| gift_wrap | VARCHAR | Flag or type of gift wrapping requested | 'Y', 'Premium', 'Basic' |
| condition | VARCHAR | Condition of product | 'New', 'Refurbished' |
| estimated_delivery | DATE | Estimated delivery date for this item | 2023-05-07 |
| list_price | NUMBER | Standard list price for the product | 179.99 |
| product_status | VARCHAR | Current status of the product | 'Available', 'Discontinued' |
| quantity_on_hand | NUMBER | Current inventory count in the fulfilling warehouse | 24 |
| stock_status | VARCHAR | Status of current inventory levels | 'Low Stock', 'In Stock', 'Overstocked' |
### Calculated Fields
| Column | Type | Description | Example |
| ---------------- | ------- | ---------------------------------------------------- | ---------------------------------------- |
| discount_amount | NUMBER | Amount of discount applied (list_price - unit_price) | 30.00 |
| discount_percent | NUMBER | Percentage discount applied | 16.67 |
| delivery_status | VARCHAR | Current status of delivery | 'Not Shipped', 'In Transit', 'Delivered' |
| return_status | VARCHAR | Status indicating if item was returned | 'Returned', 'Not Returned' |
### Timestamp Information
| Column | Type | Description | Example |
| ---------- | ------------ | ----------------------------------------------------------------------- | ----------------------------------- |
| updated_at | TIMESTAMP_TZ | Timestamp when this record was last refreshed with nanosecond precision | 2023-07-15 08:23:45.123456789-07:00 |
## Sources and Joins
This model combines data from the following source tables:
- `orders` - Primary table containing order header information
- `customers` - Customer demographic and contact information
- `addresses` - Billing and shipping address details
- `countries` - Country information for addresses
- `regions` - Regional information for addresses
- `warehouses` - Warehouse details for order fulfillment
- `promotions` - Promotion information applied to orders
- `order_items` - Individual line items within each order
- `products` - Product information for ordered items
- `product_categories` - Category information for products
- `inventories` - Inventory levels for products at warehouses
## Data Tests
The following data quality tests are applied to this model:
### Primary Key Tests
- `order_id` and `line_item_id` combination is unique
- `order_id` is not null
### Referential Integrity Tests
- `customer_id` exists in the customers table
- `product_id` exists in the products table
- `warehouse_id` exists in the warehouses table
- `promotion_id` exists in the promotions table (when not null)
- `category_id` exists in the product_categories table
### Value Tests
- `unit_price` and `quantity` are positive values
- `line_total` equals `unit_price` \* `quantity`
- `order_status` contains valid status codes
- `wait_till_complete_yn` contains only 'Y' or 'N'
- `stock_status` contains only valid values: 'Low Stock', 'In Stock', 'Overstocked', 'Unknown'
- `delivery_status` contains only valid values: 'Not Shipped', 'In Transit', 'Delivered', 'Unknown'
- `return_status` contains only valid values: 'Returned', 'Not Returned'
## Usage Examples
### Basic Order Query
```sql
SELECT
order_id,
order_date,
cust_first_name || ' ' || cust_last_name AS customer_name,
order_total,
delivery_status
FROM order_details
WHERE order_date >= DATEADD(month, -1, CURRENT_DATE())
ORDER BY order_date DESC
```
### Order Analysis by Category
```sql
SELECT
category_name,
COUNT(DISTINCT order_id) AS order_count,
SUM(line_total) AS total_revenue,
AVG(discount_percent) AS avg_discount_pct
FROM order_details
WHERE order_date >= DATEADD(year, -1, CURRENT_DATE())
GROUP BY category_name
ORDER BY total_revenue DESC
```
### Delivery Performance
```sql
SELECT
warehouse_name,
delivery_type,
AVG(DATEDIFF(day, order_date, dispatch_date)) AS avg_processing_days,
AVG(DATEDIFF(day, dispatch_date, estimated_delivery)) AS avg_transit_days,
COUNT(*) AS shipment_count
FROM order_details
WHERE dispatch_date IS NOT NULL
AND order_date >= DATEADD(month, -6, CURRENT_DATE())
GROUP BY warehouse_name, delivery_type
ORDER BY warehouse_name, avg_processing_days
```
### Returns Analysis
```sql
SELECT
product_name,
COUNT(*) AS total_ordered,
SUM(CASE WHEN return_status = 'Returned' THEN 1 ELSE 0 END) AS total_returned,
(SUM(CASE WHEN return_status = 'Returned' THEN 1 ELSE 0 END) / COUNT(*)::float) * 100 AS return_rate
FROM order_details
WHERE order_date >= DATEADD(month, -3, CURRENT_DATE())
GROUP BY product_name
HAVING COUNT(*) > 10
ORDER BY return_rate DESC
LIMIT 20
```
## Maintenance and Refresh
As a view, this model is refreshed with each query. The timestamp indicates when the data was last queried and can be used to track data freshness. Since the model pulls directly from source tables, it always reflects the current state of the data. `updated_at`
## Dependencies
This model depends on:
- Source tables from the schema `order_entry`
- Any data quality tests defined in the file `schema.yml`
## Additional Notes
- This model includes sensitive customer information. Ensure appropriate access controls are in place.
- Performance considerations: This view joins multiple tables and may be resource-intensive for large datasets. Consider materializing as a table with scheduled refreshes for frequent reporting needs.
- For large reporting needs, consider creating aggregated models derived from this model rather than running complex aggregations directly.
dbt/order_details
—
owner
urn:li:corpGroup:b2fd91.ORG_BACKEND_ENG
looker/Order Details
—
owner
urn:li:corpGroup:b2fd91.ORG_DATA_PLATFORM
dbt/order_details
—
owner
urn:li:corpuser:b2fd91.EMP006
dbt/order_details
—
owner
urn:li:corpuser:b2fd91.alex@example.com
looker/order_details
—
owner
urn:li:corpuser:b2fd91.bryan@example.com
dbt/order_details
—
owner
urn:li:corpuser:b2fd91.jonny2@example.com
dbt/order_details
—
owner
urn:li:corpuser:b2fd91.kirk@example.com
dbt/order_details
—
owner
urn:li:corpuser:b2fd91.marty@example.com
dbt/order_details
—
owner
urn:li:corpuser:b2fd91.sam@example.com
dbt/order_details
snowflake/ORDER_DETAILS_REPLICA13 gaps
column
kind
value it is missing
copied from
—
description
# Order Details Model
## Overview
The `order_details` model provides a comprehensive view of customer orders by combining data from multiple tables in the `order_entry` schema. It includes detailed information about orders, customers, products, shipping, billing, inventory, and promotions.
This denormalized view is designed to support reporting, analysis, and operational insights across the order management process.
## Model Information
- **Name**: `order_details`
- **Type**: View
- **Source Schema**: `order_entry`
- **Database**: `order_entry_db`
- **Update Frequency**: Refreshed on query with current timestamp
## Data Structure
### Order Information
| Column | Type | Description | Example |
| --------------------- | ------------ | ---------------------------------------------------------- | ---------------------------------- |
| order_id | NUMBER | Unique identifier for the order | 12345 |
| order_date | TIMESTAMP_TZ | Date and time when the order was placed including timezone | 2023-05-01 14:30:00-07:00 |
| order_mode | VARCHAR | Method of order placement | 'online', 'phone', 'direct' |
| order_status | NUMBER | Current status of the order | 1=Pending, 2=Processing, 3=Shipped |
| order_total | NUMBER | Total monetary value of the order | 156.78 |
| cost_of_delivery | NUMBER | Shipping and handling cost | 12.99 |
| delivery_type | VARCHAR | Shipping method | 'Standard', 'Express', 'Overnight' |
| wait_till_complete_yn | VARCHAR | Ship complete order or partial | 'Y', 'N' |
| payment_method_code | VARCHAR | Code indicating payment method used | 'CC', 'PayPal', 'ApplePay' |
### Customer Information
| Column | Type | Description | Example |
| --------------- | ------- | ---------------------------------- | ---------------------------- |
| customer_id | NUMBER | Unique identifier for the customer | 5001 |
| cust_first_name | VARCHAR | Customer first name | 'John' |
| cust_last_name | VARCHAR | Customer last name | 'Smith' |
| cust_email | VARCHAR | Customer email address | 'john.smith@example.com' |
| phone_number | VARCHAR | Customer contact phone number | '555-123-4567' |
| customer_class | VARCHAR | Classification of the customer | 'Platinum', 'Gold', 'Silver' |
### Billing Address Information
| Column | Type | Description | Example |
| --------------------- | ------- | --------------------------------------------- | --------------- |
| billing_address_line1 | VARCHAR | First line of customer billing address | '123 Main St' |
| billing_address_line2 | VARCHAR | Second line of customer billing address | 'Apt 4B' |
| billing_town_city | VARCHAR | Town or city of customer billing address | 'Springfield' |
| billing_country | VARCHAR | Country name for the billing address | 'United States' |
| billing_zipcode | VARCHAR | Postal code of customer billing address | '12345' |
| billing_region | VARCHAR | Region/state/province for the billing address | 'Illinois' |
### Shipping Address Information
| Column | Type | Description | Example |
| ---------------------- | ------- | ------------------------------------------ | --------------- |
| shipping_address_line1 | VARCHAR | First line of customer shipping address | '456 Oak St' |
| shipping_address_line2 | VARCHAR | Second line of customer shipping address | 'Suite 101' |
| shipping_town_city | VARCHAR | Town or city of customer shipping address | 'Chicago' |
| shipping_country | VARCHAR | Country name for the shipping address | 'United States' |
| shipping_zipcode | VARCHAR | Postal code of customer shipping address | '60601' |
| shipping_region | VARCHAR | Region/state/province for shipping address | 'Illinois' |
### Warehouse Information
| Column | Type | Description | Example |
| -------------- | ------- | ------------------------------------------------ | ----------------------------- |
| warehouse_id | NUMBER | Identifier of the warehouse fulfilling the order | 3 |
| warehouse_name | VARCHAR | Name of the warehouse location | 'Chicago Distribution Center' |
### Promotion Information
| Column | Type | Description | Example |
| --------------------- | ------- | ------------------------------------------------ | ---------------------------- |
| promotion_id | NUMBER | Identifier of the promotion applied to the order | 42 |
| promotion_name | VARCHAR | Name of the marketing promotion | 'Summer Sale 2023' |
| promotion_description | VARCHAR | Detailed description of the promotion | '20% off all summer apparel' |
### Order Line Item Information
| Column | Type | Description | Example |
| ------------------- | ------- | ------------------------------------------------------ | --------------------------------------------- |
| line_item_id | NUMBER | Line item sequence number within the order | 1 |
| product_id | NUMBER | Identifier of the product ordered | 10042 |
| product_name | VARCHAR | Name of the product | 'Ergonomic Office Chair' |
| product_description | VARCHAR | Brief description of the product | 'Adjustable office chair with lumbar support' |
| category_id | NUMBER | Identifier of the product category | 15 |
| category_name | VARCHAR | Name of the product category | 'Office Furniture' |
| unit_price | NUMBER | Selling price per unit at time of order | 149.99 |
| quantity | NUMBER | Number of units ordered | 2 |
| line_total | NUMBER | Total price for the line item (unit_price \* quantity) | 299.98 |
| dispatch_date | DATE | Date when item was dispatched from warehouse | 2023-05-03 |
| return_date | DATE | Date when item was returned if applicable | 2023-05-15 |
| gift_wrap | VARCHAR | Flag or type of gift wrapping requested | 'Y', 'Premium', 'Basic' |
| condition | VARCHAR | Condition of product | 'New', 'Refurbished' |
| estimated_delivery | DATE | Estimated delivery date for this item | 2023-05-07 |
| list_price | NUMBER | Standard list price for the product | 179.99 |
| product_status | VARCHAR | Current status of the product | 'Available', 'Discontinued' |
| quantity_on_hand | NUMBER | Current inventory count in the fulfilling warehouse | 24 |
| stock_status | VARCHAR | Status of current inventory levels | 'Low Stock', 'In Stock', 'Overstocked' |
### Calculated Fields
| Column | Type | Description | Example |
| ---------------- | ------- | ---------------------------------------------------- | ---------------------------------------- |
| discount_amount | NUMBER | Amount of discount applied (list_price - unit_price) | 30.00 |
| discount_percent | NUMBER | Percentage discount applied | 16.67 |
| delivery_status | VARCHAR | Current status of delivery | 'Not Shipped', 'In Transit', 'Delivered' |
| return_status | VARCHAR | Status indicating if item was returned | 'Returned', 'Not Returned' |
### Timestamp Information
| Column | Type | Description | Example |
| ---------- | ------------ | ----------------------------------------------------------------------- | ----------------------------------- |
| updated_at | TIMESTAMP_TZ | Timestamp when this record was last refreshed with nanosecond precision | 2023-07-15 08:23:45.123456789-07:00 |
## Sources and Joins
This model combines data from the following source tables:
- `orders` - Primary table containing order header information
- `customers` - Customer demographic and contact information
- `addresses` - Billing and shipping address details
- `countries` - Country information for addresses
- `regions` - Regional information for addresses
- `warehouses` - Warehouse details for order fulfillment
- `promotions` - Promotion information applied to orders
- `order_items` - Individual line items within each order
- `products` - Product information for ordered items
- `product_categories` - Category information for products
- `inventories` - Inventory levels for products at warehouses
## Data Tests
The following data quality tests are applied to this model:
### Primary Key Tests
- `order_id` and `line_item_id` combination is unique
- `order_id` is not null
### Referential Integrity Tests
- `customer_id` exists in the customers table
- `product_id` exists in the products table
- `warehouse_id` exists in the warehouses table
- `promotion_id` exists in the promotions table (when not null)
- `category_id` exists in the product_categories table
### Value Tests
- `unit_price` and `quantity` are positive values
- `line_total` equals `unit_price` \* `quantity`
- `order_status` contains valid status codes
- `wait_till_complete_yn` contains only 'Y' or 'N'
- `stock_status` contains only valid values: 'Low Stock', 'In Stock', 'Overstocked', 'Unknown'
- `delivery_status` contains only valid values: 'Not Shipped', 'In Transit', 'Delivered', 'Unknown'
- `return_status` contains only valid values: 'Returned', 'Not Returned'
## Usage Examples
### Basic Order Query
```sql
SELECT
order_id,
order_date,
cust_first_name || ' ' || cust_last_name AS customer_name,
order_total,
delivery_status
FROM order_details
WHERE order_date >= DATEADD(month, -1, CURRENT_DATE())
ORDER BY order_date DESC
```
### Order Analysis by Category
```sql
SELECT
category_name,
COUNT(DISTINCT order_id) AS order_count,
SUM(line_total) AS total_revenue,
AVG(discount_percent) AS avg_discount_pct
FROM order_details
WHERE order_date >= DATEADD(year, -1, CURRENT_DATE())
GROUP BY category_name
ORDER BY total_revenue DESC
```
### Delivery Performance
```sql
SELECT
warehouse_name,
delivery_type,
AVG(DATEDIFF(day, order_date, dispatch_date)) AS avg_processing_days,
AVG(DATEDIFF(day, dispatch_date, estimated_delivery)) AS avg_transit_days,
COUNT(*) AS shipment_count
FROM order_details
WHERE dispatch_date IS NOT NULL
AND order_date >= DATEADD(month, -6, CURRENT_DATE())
GROUP BY warehouse_name, delivery_type
ORDER BY warehouse_name, avg_processing_days
```
### Returns Analysis
```sql
SELECT
product_name,
COUNT(*) AS total_ordered,
SUM(CASE WHEN return_status = 'Returned' THEN 1 ELSE 0 END) AS total_returned,
(SUM(CASE WHEN return_status = 'Returned' THEN 1 ELSE 0 END) / COUNT(*)::float) * 100 AS return_rate
FROM order_details
WHERE order_date >= DATEADD(month, -3, CURRENT_DATE())
GROUP BY product_name
HAVING COUNT(*) > 10
ORDER BY return_rate DESC
LIMIT 20
```
## Maintenance and Refresh
As a view, this model is refreshed with each query. The timestamp indicates when the data was last queried and can be used to track data freshness. Since the model pulls directly from source tables, it always reflects the current state of the data. `updated_at`
## Dependencies
This model depends on:
- Source tables from the schema `order_entry`
- Any data quality tests defined in the file `schema.yml`
## Additional Notes
- This model includes sensitive customer information. Ensure appropriate access controls are in place.
- Performance considerations: This view joins multiple tables and may be resource-intensive for large datasets. Consider materializing as a table with scheduled refreshes for frequent reporting needs.
- For large reporting needs, consider creating aggregated models derived from this model rather than running complex aggregations directly.
Contains information about marketing promotions and campaigns
dbt/promotions
—
domain
Data Platform Team
dbt/promotions
—
owner
urn:li:corpGroup:b2fd91.ORG_DATA_PLATFORM
dbt/promotions
—
owner
urn:li:corpuser:b2fd91.EMP006
dbt/promotions
snowflake/REGIONS8 gaps
column
kind
value it is missing
copied from
country_id
column description
Foreign key to the countries table
dbt/regions
nls_language
column description
Default language code for the region
dbt/regions
region_id
column description
Unique identifier for the region
dbt/regions
region_name
column description
Name of the region (state, province, etc.)
dbt/regions
—
description
Reference table for regional information within countries
dbt/regions
—
domain
Data Platform Team
dbt/regions
—
owner
urn:li:corpGroup:b2fd91.ORG_DATA_PLATFORM
dbt/regions
—
owner
urn:li:corpuser:b2fd91.EMP006
dbt/regions
snowflake/WAREHOUSES8 gaps
column
kind
value it is missing
copied from
location_id
column description
Reference to the physical location
dbt/warehouses
warehouse_id
column description
Unique identifier for the warehouse
dbt/warehouses
warehouse_name
column description
Name of the warehouse location
dbt/warehouses
wh_geo_location
column description
Geographic coordinates of the warehouse
dbt/warehouses
—
description
Contains information about physical distribution centers
dbt/warehouses
—
domain
Data Platform Team
dbt/warehouses
—
owner
urn:li:corpGroup:b2fd91.ORG_DATA_PLATFORM
dbt/warehouses
—
owner
urn:li:corpuser:b2fd91.EMP006
dbt/warehouses
tableau/Custom SQL Query · 37fcfb1512 gaps
column
kind
value it is missing
copied from
AVERAGE_ORDER_VALUE
column tag
urn:li:tag:COLUMNFIELD
tableau/Order Mode
AVERAGE_ORDER_VALUE
column tag
urn:li:tag:MEASURE
tableau/Order Mode
AVERAGE_ORDER_VALUE
column tag
urn:li:tag:SUM
tableau/Order Mode
ORDER_COUNT
column tag
urn:li:tag:COLUMNFIELD
tableau/Order Mode
ORDER_COUNT
column tag
urn:li:tag:MEASURE
tableau/Order Mode
ORDER_COUNT
column tag
urn:li:tag:SUM
tableau/Order Mode
ORDER_MODE
column tag
urn:li:tag:COLUMNFIELD
tableau/Order Mode
ORDER_MODE
column tag
urn:li:tag:MEASURE
tableau/Order Mode
TOTAL_REVENUE
column tag
urn:li:tag:COLUMNFIELD
tableau/Order Mode
TOTAL_REVENUE
column tag
urn:li:tag:MEASURE
tableau/Order Mode
TOTAL_REVENUE
column tag
urn:li:tag:SUM
tableau/Order Mode
—
owner
urn:li:corpuser:b2fd91.brock1@example.com
tableau/Order Mode
tableau/Custom SQL Query · 4a3af1dd12 gaps
column
kind
value it is missing
copied from
AVERAGE_ORDER_VALUE
column tag
urn:li:tag:COLUMNFIELD
tableau/Top Product Category
AVERAGE_ORDER_VALUE
column tag
urn:li:tag:MEASURE
tableau/Top Product Category
AVERAGE_ORDER_VALUE
column tag
urn:li:tag:SUM
tableau/Top Product Category
CATEGORY_NAME
column tag
urn:li:tag:COLUMNFIELD
tableau/Top Product Category
CATEGORY_NAME
column tag
urn:li:tag:DIMENSION
tableau/Top Product Category
ORDERS_COUNT
column tag
urn:li:tag:COLUMNFIELD
tableau/Top Product Category
ORDERS_COUNT
column tag
urn:li:tag:MEASURE
tableau/Top Product Category
ORDERS_COUNT
column tag
urn:li:tag:SUM
tableau/Top Product Category
TOTAL_REVENUE
column tag
urn:li:tag:COLUMNFIELD
tableau/Top Product Category
TOTAL_REVENUE
column tag
urn:li:tag:MEASURE
tableau/Top Product Category
TOTAL_REVENUE
column tag
urn:li:tag:SUM
tableau/Top Product Category
—
owner
urn:li:corpuser:b2fd91.brock1@example.com
tableau/Top Product Category
tableau/Custom SQL Query · 8bfe748316 gaps
column
kind
value it is missing
copied from
AVERAGE_DISCOUNT_PERCENT
column tag
urn:li:tag:COLUMNFIELD
tableau/Promotions
AVERAGE_DISCOUNT_PERCENT
column tag
urn:li:tag:MEASURE
tableau/Promotions
AVERAGE_DISCOUNT_PERCENT
column tag
urn:li:tag:SUM
tableau/Promotions
AVERAGE_ORDER_VALUE
column tag
urn:li:tag:COLUMNFIELD
tableau/Promotions
AVERAGE_ORDER_VALUE
column tag
urn:li:tag:MEASURE
tableau/Promotions
AVERAGE_ORDER_VALUE
column tag
urn:li:tag:SUM
tableau/Promotions
ORDERS_WITH_PROMOTION
column tag
urn:li:tag:COLUMNFIELD
tableau/Promotions
ORDERS_WITH_PROMOTION
column tag
urn:li:tag:MEASURE
tableau/Promotions
ORDERS_WITH_PROMOTION
column tag
urn:li:tag:SUM
tableau/Promotions
PROMOTION_NAME
column tag
urn:li:tag:COLUMNFIELD
tableau/Promotions
PROMOTION_NAME
column tag
urn:li:tag:COUNT
tableau/Promotions
PROMOTION_NAME
column tag
urn:li:tag:DIMENSION
tableau/Promotions
TOTAL_REVENUE
column tag
urn:li:tag:COLUMNFIELD
tableau/Promotions
TOTAL_REVENUE
column tag
urn:li:tag:MEASURE
tableau/Promotions
TOTAL_REVENUE
column tag
urn:li:tag:SUM
tableau/Promotions
—
owner
urn:li:corpuser:b2fd91.brock1@example.com
tableau/Promotions
tableau/Custom SQL Query · f32082e513 gaps
column
kind
value it is missing
copied from
CATEGORY_NAME
column tag
urn:li:tag:COLUMNFIELD
tableau/Orders By Day
CATEGORY_NAME
column tag
urn:li:tag:COUNT
tableau/Orders By Day
CATEGORY_NAME
column tag
urn:li:tag:DIMENSION
tableau/Orders By Day
ORDER_COUNT
column tag
urn:li:tag:COLUMNFIELD
tableau/Orders By Day
ORDER_COUNT
column tag
urn:li:tag:MEASURE
tableau/Orders By Day
ORDER_COUNT
column tag
urn:li:tag:SUM
tableau/Orders By Day
ORDER_DATE
column tag
urn:li:tag:COLUMNFIELD
tableau/Orders By Day
ORDER_DATE
column tag
urn:li:tag:COUNT
tableau/Orders By Day
ORDER_DATE
column tag
urn:li:tag:DIMENSION
tableau/Orders By Day
TOTAL_SALES
column tag
urn:li:tag:COLUMNFIELD
tableau/Orders By Day
TOTAL_SALES
column tag
urn:li:tag:MEASURE
tableau/Orders By Day
TOTAL_SALES
column tag
urn:li:tag:SUM
tableau/Orders By Day
—
owner
urn:li:corpuser:b2fd91.brock1@example.com
tableau/Orders By Day
Why these are the same data
Names are wrong about one time in five, and the lineage graph
is worse. Every one of the 88 same-name pairs
in this catalog is connected — and so is every one of the
316 pairs picked at random. Connectivity carries
no information at all, and distance carries almost none:
So structure only proposes. A model decides, one pair at a
time, and its reason is kept. A reason is a comment, not a verified fact —
which is exactly why none of them is ever written into the catalog.
Identical data structure and column names, despite different platforms
Writing takes a second argument
Asking Fadlie to fix a dataset does not fix it. The dry run is
the default in the code, not in the interface, so a call that forgets the flag
changes nothing at all. Here is the answer to
apply_governance(dataset="order_details"), exactly as the deployed
agent returned it:
