Enterprise Data Productization (Data Mesh) Best Practices
Each business domain publishes its data as "data products," where each domain independently manages its schema, defines data contracts, and controls consumption permissions. Cross-domain analytics are performed through a unified data product layer using federated queries rather than direct access to source tables. This guide uses three business domains — Sales, HR, and Finance — to demonstrate the complete end-to-end implementation path of Domain Schema → Data Contract → Semantic View → RBAC → Cross-Domain Analytics.
Overview
Core problems with traditional centralized data warehouses, and Singdata Lakehouse's Data Mesh solutions:
Problem
Singdata Solution
Multiple business teams compete for the same schema, interfering with each other
Each domain has an independent schema (e.g., best_practice_data_mesh_sales), fully autonomous
Semantic Views encapsulate business logic and serve as the stable interface for data products
Fine-grained control over who can see what data is impossible
GRANT/REVOKE to specific views, role-level authorization
Cross-domain JOINs require data movement or manual alignment
Dynamic Tables directly federate across domain schemas without copying data
Opacity about who uses data products and how often
sys.information_schema.job_history tracks query frequency and SLA
SQL Commands Used
Command / Function
Purpose
Notes
CREATE SCHEMA
Create an independent schema for each business domain
Foundation for domain isolation
CREATE TABLE
Create source data tables under a domain schema, with column comments as data contracts
COMMENT fields serve as data contracts
CREATE VIEW
Create Semantic Views in the data product layer
Encapsulates business logic; consumers query this view
CREATE DYNAMIC TABLE
Cross-domain union aggregation with automatic incremental refresh
Cross-schema references without data copying
GRANT SELECT ON VIEW
Grant view access to a role
Data product owner controls who can consume
REVOKE SELECT ON VIEW
Revoke access
Used when a data product is retired or a consumer changes
SHOW GRANTS ON VIEW
View the current permission list for a view
Audit data product consumers
REFRESH DYNAMIC TABLE
Manually trigger a cross-domain aggregation refresh
Used for initial builds or debugging
Prerequisites
This guide runs under the following four schemas. Each domain schema is fully independent, and the data product layer has its own dedicated schema:
-- Data product layer (unified interface layer)
CREATE SCHEMA IF NOT EXISTS best_practice_data_mesh;
-- Business domain schemas
CREATE SCHEMA IF NOT EXISTS best_practice_data_mesh_sales;
CREATE SCHEMA IF NOT EXISTS best_practice_data_mesh_hr;
CREATE SCHEMA IF NOT EXISTS best_practice_data_mesh_finance;
Sales Domain: Tables and Data Contracts
Create Source Data Tables
The Sales domain independently manages two tables. Column comments (COMMENT) are the core expression of the data contract — consumers understand field semantics by reading the comments rather than relying on verbal agreements.
CREATE TABLE IF NOT EXISTS best_practice_data_mesh_sales.doc_sales_orders (
order_id STRING COMMENT 'Unique order identifier',
customer_id STRING COMMENT 'Customer identifier',
sales_rep_id STRING COMMENT 'Sales representative identifier',
region STRING COMMENT 'Sales region',
order_date DATE COMMENT 'Date the order was placed',
status STRING COMMENT 'Order status: pending/confirmed/shipped/cancelled',
total_amount DOUBLE COMMENT 'Total order amount in USD',
currency STRING COMMENT 'Currency code'
) COMMENT 'Sales domain: order header table (data contract v1.0)';
CREATE TABLE IF NOT EXISTS best_practice_data_mesh_sales.doc_sales_items (
item_id STRING COMMENT 'Unique line item identifier',
order_id STRING COMMENT 'Reference to doc_sales_orders.order_id',
product_id STRING COMMENT 'Product identifier',
product_name STRING COMMENT 'Product display name',
category STRING COMMENT 'Product category',
quantity INT COMMENT 'Ordered quantity',
unit_price DOUBLE COMMENT 'Unit price at time of order',
line_total DOUBLE COMMENT 'quantity * unit_price'
) COMMENT 'Sales domain: order line items table (data contract v1.0)';
The table-level COMMENT includes a version number at the end (data contract v1.0), making it easy to check the contract version with DESC TABLE and determine whether consumers need to adapt.
Insert Sample Data
Import data from a local CSV file (recommended):
-- Step 1: Upload the local CSV file to User Volume via SQL PUT
PUT '/path/to/your/doc_sales_orders.csv' TO USER VOLUME FILE 'doc_sales_orders.csv';
-- Step 2: COPY INTO the table from User Volume
COPY INTO best_practice_data_mesh_sales.doc_sales_orders
FROM USER VOLUME
USING csv
OPTIONS('header'='true', 'sep'=',', 'nullValue'='')
FILES ('doc_sales_orders.csv');
You can also directly insert a small batch of test data inline (no CSV file needed):
INSERT INTO best_practice_data_mesh_sales.doc_sales_orders VALUES
('ORD001','CUST001','REP001','APAC', CAST('2026-01-05' AS DATE),'shipped', 15200.00,'USD'),
('ORD002','CUST002','REP001','APAC', CAST('2026-01-08' AS DATE),'shipped', 8500.00,'USD'),
('ORD003','CUST003','REP002','EMEA', CAST('2026-01-12' AS DATE),'confirmed', 23000.00,'USD'),
('ORD004','CUST004','REP003','AMER', CAST('2026-01-15' AS DATE),'cancelled', 4200.00,'USD'),
('ORD005','CUST005','REP002','EMEA', CAST('2026-01-20' AS DATE),'shipped', 31500.00,'USD'),
('ORD006','CUST001','REP001','APAC', CAST('2026-02-03' AS DATE),'shipped', 9800.00,'USD'),
('ORD007','CUST006','REP004','AMER', CAST('2026-02-10' AS DATE),'pending', 6750.00,'USD'),
('ORD008','CUST007','REP003','AMER', CAST('2026-02-14' AS DATE),'shipped', 18400.00,'USD'),
('ORD009','CUST008','REP002','EMEA', CAST('2026-02-18' AS DATE),'confirmed', 11200.00,'USD'),
('ORD010','CUST009','REP005','APAC', CAST('2026-02-25' AS DATE),'shipped', 27600.00,'USD'),
('ORD011','CUST010','REP004','AMER', CAST('2026-03-02' AS DATE),'shipped', 5300.00,'USD'),
('ORD012','CUST002','REP001','APAC', CAST('2026-03-07' AS DATE),'shipped', 14100.00,'USD');
INSERT INTO best_practice_data_mesh_sales.doc_sales_items VALUES
('ITEM001','ORD001','PROD001','Laptop Pro 15','Electronics', 2, 4200.00, 8400.00),
('ITEM002','ORD001','PROD002','USB-C Hub', 'Accessories', 5, 160.00, 800.00),
('ITEM003','ORD001','PROD003','Laptop Bag', 'Accessories', 2, 80.00, 160.00),
('ITEM004','ORD002','PROD004','Wireless Mouse','Peripherals', 10, 85.00, 850.00),
('ITEM005','ORD003','PROD001','Laptop Pro 15', 'Electronics', 4, 4200.00, 16800.00),
('ITEM006','ORD003','PROD005','Monitor 27in', 'Electronics', 2, 3100.00, 6200.00),
('ITEM007','ORD005','PROD001','Laptop Pro 15', 'Electronics', 6, 4200.00, 25200.00),
('ITEM008','ORD005','PROD003','Laptop Bag', 'Accessories', 6, 80.00, 480.00),
('ITEM009','ORD006','PROD002','USB-C Hub', 'Accessories', 3, 160.00, 480.00),
('ITEM010','ORD006','PROD004','Wireless Mouse','Peripherals', 8, 85.00, 680.00),
('ITEM011','ORD008','PROD005','Monitor 27in', 'Electronics', 4, 3100.00, 12400.00),
('ITEM012','ORD010','PROD001','Laptop Pro 15', 'Electronics', 5, 4200.00, 21000.00),
('ITEM013','ORD010','PROD006','Keyboard MX', 'Peripherals', 10, 195.00, 1950.00),
('ITEM014','ORD011','PROD004','Wireless Mouse','Peripherals', 5, 85.00, 425.00),
('ITEM015','ORD012','PROD001','Laptop Pro 15', 'Electronics', 2, 4200.00, 8400.00);
Verify Sales domain data volumes:
SELECT COUNT(*) AS order_count FROM best_practice_data_mesh_sales.doc_sales_orders;
SELECT COUNT(*) AS item_count FROM best_practice_data_mesh_sales.doc_sales_items;
The HR domain includes two tables: employees and departments. The salary field stores real values in the source table but is not directly exposed to consumers — the Semantic View in the data product layer will exclude this field, exposing only the organizational structure dimension.
CREATE TABLE IF NOT EXISTS best_practice_data_mesh_hr.doc_departments (
dept_id STRING COMMENT 'Unique department identifier',
dept_name STRING COMMENT 'Department name',
cost_center STRING COMMENT 'Finance cost center code',
location STRING COMMENT 'Office location',
head_count INT COMMENT 'Planned headcount'
) COMMENT 'HR domain: department master table (data contract v1.0)';
CREATE TABLE IF NOT EXISTS best_practice_data_mesh_hr.doc_employees (
employee_id STRING COMMENT 'Unique employee identifier',
dept_id STRING COMMENT 'Reference to doc_departments.dept_id',
full_name STRING COMMENT 'Employee full name',
job_title STRING COMMENT 'Job title',
hire_date DATE COMMENT 'Date of hire',
salary DOUBLE COMMENT 'Annual salary in USD',
status STRING COMMENT 'active / on_leave / terminated',
manager_id STRING COMMENT 'Direct manager employee_id (nullable for top level)'
) COMMENT 'HR domain: employee master table (data contract v1.0)';
Insert Sample Data
INSERT INTO best_practice_data_mesh_hr.doc_departments VALUES
('DEPT01','Sales', 'CC-SALE','Shanghai', 30),
('DEPT02','Engineering', 'CC-ENG', 'Beijing', 80),
('DEPT03','Finance', 'CC-FIN', 'Shanghai', 20),
('DEPT04','Human Resources','CC-HR', 'Shanghai', 15),
('DEPT05','Marketing', 'CC-MKT', 'Shenzhen', 25);
INSERT INTO best_practice_data_mesh_hr.doc_employees VALUES
('EMP001','DEPT01','Alice Wang', 'Sales Manager', CAST('2020-03-01' AS DATE),180000.00,'active', NULL),
('EMP002','DEPT01','Bob Chen', 'Sales Rep', CAST('2021-06-15' AS DATE),110000.00,'active', 'EMP001'),
('EMP003','DEPT01','Carol Liu', 'Sales Rep', CAST('2021-09-20' AS DATE),108000.00,'active', 'EMP001'),
('EMP004','DEPT02','David Zhang', 'Engineering Lead', CAST('2019-01-10' AS DATE),220000.00,'active', NULL),
('EMP005','DEPT02','Eva Sun', 'Senior Engineer', CAST('2020-07-22' AS DATE),180000.00,'active', 'EMP004'),
('EMP006','DEPT02','Frank Zhao', 'Engineer', CAST('2022-04-05' AS DATE),140000.00,'active', 'EMP004'),
('EMP007','DEPT03','Grace Li', 'Finance Manager', CAST('2018-11-01' AS DATE),190000.00,'active', NULL),
('EMP008','DEPT03','Henry Wu', 'Accountant', CAST('2021-02-28' AS DATE),120000.00,'active', 'EMP007'),
('EMP009','DEPT04','Ivy Zhou', 'HR Manager', CAST('2019-05-15' AS DATE),160000.00,'active', NULL),
('EMP010','DEPT04','Jack Luo', 'HR Specialist', CAST('2022-08-01' AS DATE),100000.00,'on_leave','EMP009'),
('EMP011','DEPT05','Karen Xu', 'Marketing Manager', CAST('2020-10-12' AS DATE),170000.00,'active', NULL),
('EMP012','DEPT01','Leo Ma', 'Sales Rep', CAST('2023-01-16' AS DATE),105000.00,'active', 'EMP001');
Finance Domain: Tables and Data Contracts
Create Source Data Tables
The Finance domain manages two tables: invoices and payments, linked by invoice_id, forming the accounts receivable (AR) system.
CREATE TABLE IF NOT EXISTS best_practice_data_mesh_finance.doc_invoices (
invoice_id STRING COMMENT 'Unique invoice identifier',
order_id STRING COMMENT 'Reference to sales domain order_id',
customer_id STRING COMMENT 'Customer identifier',
issue_date DATE COMMENT 'Invoice issue date',
due_date DATE COMMENT 'Payment due date',
amount DOUBLE COMMENT 'Invoice amount in USD',
status STRING COMMENT 'draft / issued / paid / overdue'
) COMMENT 'Finance domain: invoice header table (data contract v1.0)';
CREATE TABLE IF NOT EXISTS best_practice_data_mesh_finance.doc_payments (
payment_id STRING COMMENT 'Unique payment identifier',
invoice_id STRING COMMENT 'Reference to doc_invoices.invoice_id',
payment_date DATE COMMENT 'Date payment was received',
amount_paid DOUBLE COMMENT 'Amount paid in USD',
method STRING COMMENT 'Payment method: bank_transfer / credit_card / check',
status STRING COMMENT 'completed / failed / pending'
) COMMENT 'Finance domain: payment records table (data contract v1.0)';
Insert Sample Data
INSERT INTO best_practice_data_mesh_finance.doc_invoices VALUES
('INV001','ORD001','CUST001', CAST('2026-01-06' AS DATE), CAST('2026-02-06' AS DATE), 15200.00,'paid'),
('INV002','ORD002','CUST002', CAST('2026-01-09' AS DATE), CAST('2026-02-09' AS DATE), 8500.00,'paid'),
('INV003','ORD003','CUST003', CAST('2026-01-13' AS DATE), CAST('2026-02-13' AS DATE), 23000.00,'paid'),
('INV004','ORD005','CUST005', CAST('2026-01-21' AS DATE), CAST('2026-02-21' AS DATE), 31500.00,'overdue'),
('INV005','ORD006','CUST001', CAST('2026-02-04' AS DATE), CAST('2026-03-04' AS DATE), 9800.00,'paid'),
('INV006','ORD008','CUST007', CAST('2026-02-15' AS DATE), CAST('2026-03-15' AS DATE), 18400.00,'issued'),
('INV007','ORD009','CUST008', CAST('2026-02-19' AS DATE), CAST('2026-03-19' AS DATE), 11200.00,'paid'),
('INV008','ORD010','CUST009', CAST('2026-02-26' AS DATE), CAST('2026-03-26' AS DATE), 27600.00,'issued'),
('INV009','ORD011','CUST010', CAST('2026-03-03' AS DATE), CAST('2026-04-03' AS DATE), 5300.00,'paid'),
('INV010','ORD012','CUST002', CAST('2026-03-08' AS DATE), CAST('2026-04-08' AS DATE), 14100.00,'draft');
INSERT INTO best_practice_data_mesh_finance.doc_payments VALUES
('PAY001','INV001', CAST('2026-01-28' AS DATE), 15200.00,'bank_transfer','completed'),
('PAY002','INV002', CAST('2026-02-01' AS DATE), 8500.00,'credit_card', 'completed'),
('PAY003','INV003', CAST('2026-02-10' AS DATE), 23000.00,'bank_transfer','completed'),
('PAY004','INV005', CAST('2026-02-25' AS DATE), 9800.00,'bank_transfer','completed'),
('PAY005','INV007', CAST('2026-03-05' AS DATE), 11200.00,'credit_card', 'completed'),
('PAY006','INV009', CAST('2026-03-20' AS DATE), 5300.00,'check', 'completed');
Data Product Layer: Semantic Views
The data product layer (best_practice_data_mesh) is the sole access entry point for consumers. Each data product is a Semantic View that encapsulates business filtering, JOIN, and calculation logic. Consumers only see "cleansed" fields and are unaware of the physical table structure within the domain.
Sales Data Product: Order Revenue View
CREATE OR REPLACE VIEW best_practice_data_mesh.dp_sales_revenue AS
SELECT
o.order_id,
o.customer_id,
o.region,
o.order_date,
o.status,
o.total_amount,
i.product_name,
i.category,
i.quantity,
i.unit_price,
i.line_total
FROM best_practice_data_mesh_sales.doc_sales_orders o
JOIN best_practice_data_mesh_sales.doc_sales_items i
ON o.order_id = i.order_id
WHERE o.status NOT IN ('cancelled');
This view does two things: JOINs order headers with line items, and filters out cancelled orders. When consumers query, they don't need to worry about the source table JOIN logic, and they won't accidentally include cancelled orders that would skew revenue figures.
Verify revenue by region:
SELECT region, COUNT(*) AS orders, ROUND(SUM(total_amount), 2) AS revenue
FROM best_practice_data_mesh.dp_sales_revenue
GROUP BY region ORDER BY revenue DESC;
SELECT category, COUNT(*) AS item_lines, ROUND(SUM(line_total), 2) AS category_revenue
FROM best_practice_data_mesh.dp_sales_revenue
GROUP BY category ORDER BY category_revenue DESC;
The Electronics category contributed USD 98,400 in revenue, accounting for over 94% — the dominant driver of sales.
HR Data Product: Organizational Structure View
The HR domain only exposes organizational dimensions externally. The salary field does not appear in the data product view. Only employees with status = 'active' are returned; on_leave and terminated records are retained within the domain.
CREATE OR REPLACE VIEW best_practice_data_mesh.dp_hr_org AS
SELECT
e.employee_id,
e.dept_id,
d.dept_name,
d.cost_center,
d.location,
e.job_title,
e.hire_date,
e.status,
e.manager_id
FROM best_practice_data_mesh_hr.doc_employees e
JOIN best_practice_data_mesh_hr.doc_departments d
ON e.dept_id = d.dept_id
WHERE e.status = 'active';
Verify active headcount by department:
SELECT dept_name, COUNT(*) AS headcount, location
FROM best_practice_data_mesh.dp_hr_org
GROUP BY dept_name, location ORDER BY headcount DESC;
⚠️ Note: The HR data product view only outputs active employees. Employees with on_leave status (e.g., Jack Luo) are not in the consumer results. If consumers need records including those on leave or terminated, they need to request a dedicated view from the HR domain owner, or add a version option to the data product definition.
Finance Data Product: Accounts Receivable View
The Finance domain aggregates the two tables — invoices and payments — exposing each invoice's outstanding balance and AR status externally.
CREATE OR REPLACE VIEW best_practice_data_mesh.dp_finance_ar AS
SELECT
inv.invoice_id,
inv.order_id,
inv.customer_id,
inv.issue_date,
inv.due_date,
inv.amount,
inv.status AS invoice_status,
COALESCE(SUM(pay.amount_paid), 0) AS total_paid,
inv.amount - COALESCE(SUM(pay.amount_paid), 0) AS outstanding_balance,
CASE
WHEN inv.status = 'paid' THEN 'CLOSED'
WHEN inv.status = 'overdue' THEN 'OVERDUE'
WHEN CURRENT_DATE() > inv.due_date THEN 'OVERDUE'
ELSE 'OPEN'
END AS ar_status
FROM best_practice_data_mesh_finance.doc_invoices inv
LEFT JOIN best_practice_data_mesh_finance.doc_payments pay
ON inv.invoice_id = pay.invoice_id
GROUP BY
inv.invoice_id, inv.order_id, inv.customer_id,
inv.issue_date, inv.due_date, inv.amount, inv.status;
Verify accounts receivable summary:
SELECT ar_status, COUNT(*) AS cnt, ROUND(SUM(outstanding_balance), 2) AS total_outstanding
FROM best_practice_data_mesh.dp_finance_ar
GROUP BY ar_status ORDER BY total_outstanding DESC;
There are currently 4 overdue invoices with a total outstanding balance of USD 91,600 — an AR risk that requires focused follow-up by the finance team.
View details:
SELECT invoice_id, customer_id, amount, total_paid, outstanding_balance, ar_status
FROM best_practice_data_mesh.dp_finance_ar
ORDER BY ar_status, outstanding_balance DESC;
Data product owners control who can access which data products through GRANT SELECT ON VIEW. Authorization targets are views, not source tables — even if a consumer has schema access, they cannot bypass the view to directly query the source table (SELECT permission on source tables is not granted).
Grant Data Products to Consumer Roles
-- Grant three data product views to the analyst role
GRANT SELECT ON VIEW best_practice_data_mesh.dp_sales_revenue TO ROLE workspace_analyst;
GRANT SELECT ON VIEW best_practice_data_mesh.dp_hr_org TO ROLE workspace_analyst;
GRANT SELECT ON VIEW best_practice_data_mesh.dp_finance_ar TO ROLE workspace_analyst;
Verify Grant Results
SHOW GRANTS ON VIEW best_practice_data_mesh.dp_sales_revenue;
When a data product is retired or a consumer needs a permission change:
REVOKE SELECT ON VIEW best_practice_data_mesh.dp_sales_revenue FROM ROLE workspace_analyst;
⚠️ Note: GRANT/REVOKE applies to the view object, not the underlying source tables referenced by the view. If a consumer role already has ALL permissions on the source schema through another path (e.g., workspace_dev), revoking the view will not prevent direct queries to the source table. Permission design must ensure source tables within domain schemas are only accessible to domain owners.
Cross-Domain Analytics
Cross-Domain Join Query: Orders + Accounts Receivable Status
Data products from different domains can be directly JOINed without copying data. The Finance team views which orders have outstanding payment risk:
CREATE OR REPLACE VIEW best_practice_data_mesh.dp_cross_domain_order_ar AS
SELECT
s.order_id,
s.customer_id,
s.region,
s.order_date,
ROUND(s.total_amount, 2) AS order_amount,
f.invoice_status,
ROUND(f.total_paid, 2) AS total_paid,
ROUND(f.outstanding_balance, 2) AS outstanding_balance,
f.ar_status
FROM best_practice_data_mesh.dp_sales_revenue s
LEFT JOIN best_practice_data_mesh.dp_finance_ar f
ON s.order_id = f.order_id;
Query overdue or open orders:
SELECT order_id, customer_id, order_amount, invoice_status, outstanding_balance, ar_status
FROM best_practice_data_mesh.dp_cross_domain_order_ar
WHERE ar_status IN ('OVERDUE', 'OPEN')
ORDER BY outstanding_balance DESC;
CUST005's ORD005 (USD 31,500) is overdue and unpaid — the highest-risk receivable.
Cross-Domain Dynamic Table: Sales Rep Performance
A cross-domain Dynamic Table directly references source tables from both the Sales and HR domains, automatically performing incremental aggregation without manual ETL.
CREATE DYNAMIC TABLE IF NOT EXISTS best_practice_data_mesh.dp_sales_rep_performance
AS
SELECT
o.sales_rep_id,
emp.full_name AS rep_name,
emp.dept_id,
o.region,
COUNT(DISTINCT o.order_id) AS order_count,
ROUND(SUM(o.total_amount), 2) AS total_revenue,
ROUND(AVG(o.total_amount), 2) AS avg_order_value,
SUM(CASE WHEN o.status = 'shipped' THEN 1 ELSE 0 END) AS shipped_count
FROM best_practice_data_mesh_sales.doc_sales_orders o
LEFT JOIN best_practice_data_mesh_hr.doc_employees emp
ON o.sales_rep_id = emp.employee_id
WHERE o.status != 'cancelled'
GROUP BY o.sales_rep_id, emp.full_name, emp.dept_id, o.region;
⚠️ Note: In this example, sales_rep_id (e.g., REP001) and employee_id (e.g., EMP001) follow different coding conventions, so the JOIN result rep_name is NULL. In a real Data Mesh scenario, cross-domain JOIN ID alignment is a core challenge in data product design — the Sales domain needs to clearly specify the encoding convention for sales_rep_id in the data contract, or the HR domain provides an ID mapping view, resolved through data contract negotiation between both parties.
SELECT sales_rep_id, rep_name, region, order_count, total_revenue, avg_order_value, shipped_count
FROM best_practice_data_mesh.dp_sales_rep_performance
ORDER BY total_revenue DESC;
REP002 and REP001 are the top contributors in EMEA and APAC, contributing USD 65,700 and USD 47,600 respectively.
Configure Periodic Refresh (Lakehouse Studio Task)
Dynamic Table scheduled refresh is managed by creating tasks in Lakehouse Studio, not by writing REFRESH INTERVAL in the DDL. Steps:
Go to Development -> Tasks, select the skill_test profile
Under the path best_practices/data_mesh/, create a new task named refresh_dp_sales_rep_performance
Set the task SQL to: REFRESH DYNAMIC TABLE best_practice_data_mesh.dp_sales_rep_performance;
Configure a Cron schedule (e.g., every hour: 0 * * * *)
Attach data quality rules (e.g., order_count > 0) and alert notifications to the same task
💡 Tip: Binding the refresh task and data quality rules to the same Lakehouse Studio task allows centralized management in one place: schedule trigger → refresh → quality check → alert notification — no additional monitoring scripts needed.
Data Product Usage Monitoring
Use sys.information_schema.job_history to track query frequency and SLA compliance for data products:
SELECT
SUBSTR(job_text, 1, 60) AS sql_preview,
COUNT(*) AS query_count,
ROUND(AVG(execution_time), 3) AS avg_elapsed_sec,
MAX(rows_produced) AS max_rows_produced,
SUM(CASE WHEN status = 'SUCCEED' THEN 1 ELSE 0 END) AS success_count
FROM sys.information_schema.job_history
WHERE workspace_name = 'quick_start'
AND pt_date = CURRENT_DATE()
AND job_text LIKE '%best_practice_data_mesh%'
GROUP BY SUBSTR(job_text, 1, 60)
ORDER BY query_count DESC
LIMIT 10;
By filtering with job_text LIKE '%dp_%' on data product view query records, you can aggregate daily average query counts and P99 response times per data product as input for SLA reports.
Object Overview
All objects in the best_practice_data_mesh data product layer:
Three Semantic Views (is_dynamic = false) correspond to the single-domain data products of each domain. One Dynamic Table (dp_sales_rep_performance) handles cross-domain aggregation, and one cross-domain view (dp_cross_domain_order_ar) supports joint queries between Sales and Finance.
Notes
Do not grant source tables to consumers: GRANT only applies to data product views. SELECT permission on source tables within domains is held only by domain owners. If a role can access the source schema through inherited permissions, you need to explicitly REVOKE at the schema level or isolate via role design.
Version data contracts: Add a version number at the end of the table-level COMMENT (data contract v1.0). Update the version number synchronously when column COMMENT changes, and notify authorized consumers. Consumers can see the contract version in DESC TABLE or SHOW TABLES and proactively adapt.
Cross-domain ID alignment is a hidden coupling point: When different domains use different entity coding conventions (e.g., REP001 vs EMP001), cross-domain JOINs must explicitly define alignment paths in data contracts, or one party provides an ID mapping view. This is the most common friction point in Data Mesh implementation and requires cross-domain negotiation — neither party can unilaterally assume.
Permissions for cross-domain Dynamic Table references:dp_sales_rep_performance references tables from both the best_practice_data_mesh_sales and best_practice_data_mesh_hr domains. The user creating the Dynamic Table needs SELECT permission on source tables in both domains. If the Dynamic Table is created by the data product layer's technical team, they need to request read-only permissions from both domains in advance.
View layers and performance:dp_cross_domain_order_ar queries two views — dp_sales_revenue and dp_finance_ar — and each query will expand two layers of views before execution. If query frequency is high and performance becomes a bottleneck, the cross-domain union view can be converted to a Dynamic Table to trade materialized results for query speed.
