Introduction
One way to work with the data files existing in organization is to import them
into a lakehouse and build a data model based on them that can be reused in
the various solutions (incl. Power BI). As a reminder, a
lakehouse
is a data architecture platform for storing, managing, and analyzing
structured and unstructured data in a single location.
The tutorials available on lakehouses are pretty useful for getting an idea
how to start, though probably each seasoned professional has his/her way of
doing things, at least for testing the capabilities before building a proper
solution. The target is thus, to create the minimum for testing
the capabilities needed for a proof-of-concept solution.
The files used in this post are available on Microsoft's
GitHub. Download the files and store them to be easily available for the next
steps. The following files were considered for the current post: customers.csv, orders.csv and products.csv.
Create the Lakehouse
It's recommended to have a naming convention in place for the various items created in a workspace, e.g. a combination between item type (lakehouse, warehouse), system type (Prod, UAT, Dev, PoC) and eventually department (e.g. FIN, SCM, HR, etc.). One should try to balance between meaning and usefulness. Ideally, one should use 2 maximum 4 letters for each part encoded in the name. For example, the following scripts were created in the LH_SCM_PoC lakehouse.
More complex naming conventions can include the system (e.g. D365, CRM, EBS) or the company. The target is to easily identify the systems, independently how complex the rules are. Given that it can become challenging to rename the schemas afterwards, ideally the naming convention should be available from the early stages.
Create the Schema
A lakehouse comes with a dbo schema available by default, though it's
recommended to create meaningful schema(s) as needed. The schemas should ideally
reflect the domain of the data (e.g. departments or other key areas) and the schemas shouldn't change when the objects are deployed between the different environments. Upon case, one should consider creating multiple schemas that reflect the model's
key areas. The names should be simple and suggestive.
-- create schema
CREATE Schema Orders
Create a Staging Area
The next step is to create a staging area where the files in scope can be made available and then further loaded in the lakehouse. One needs to compromise between creating a deep hierarchical structure that reflects the business structure and the need to easily identify, respectively manage the files. An hierarchical structure with 1-2 level could provide the needed compromise, though each additional level tends to increase the complexity.
One should also consider rules for archiving or even deleting the files.
Upload the Files
Microsoft Fabric allows users to upload
multiple files together into a single step. Ideally the files should have proper names for each column, otherwise overheads deriving from this may appear later in the process.
When the files are available in multiple folders in a complex hierarchical structure, a set of shortcuts could help in their management.
Load the Data
A file's data can be loaded into the lakehouse on the fly by providing a valid table name:
Files >> SCM_Orders >> (select file) >> Load to Tables >> new table >> Load file to new table >> (provide information) >> Load
 |
| Load file to new table |
Of course, the table's name must be unique within the Schema and the further properties must define files' definition.
One should consider loading first a couple of tables, performing a rough validation of the data imported, and only after that the remaining tables can be imported. This allows to identify the issues that typically lead to reimports of the data (wrong formatting, invalid column names, duplicated files, etc.) or rework.
If the files have different characteristics (e.g. delimiters, number of attributes/records, special data types), one should consider this in the initial scope and have at least one example from each category.
Review the Metadata
Once the files were made available, there's the tendency to start directly with the development without analyzing the data, or equally important, the metadata available. To review the metadata of the tables newly created, one can use the objects
from the standard INFORMATION_SCHEMA (see post):
-- retrieve the list of tables
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'orders'
ORDER BY TABLE_SCHEMA
Further on, one can review columns' definition:
-- retrieve column metadata
SELECT TABLE_CATALOG
, TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
, ORDINAL_POSITION
, DATA_TYPE
, CHARACTER_MAXIMUM_LENGTH
, NUMERIC_PRECISION
, NUMERIC_SCALE
, DATETIME_PRECISION
, CHARACTER_SET_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'orders'
ORDER BY ORDINAL_POSITION
It's a good idea to save the metadata to a file and use it later for reviews, respectively for metadata management, when no other solution is in place for the same (e.g. Purview). That's useful also for the people with limited or no access to the workspace.
Alternatively, one can use a notebook with the following SQL-based commands:
%%sql
DESCRIBE TABLE LH_SCM_PoC.orders.sales;
DESCRIBE TABLE EXTENDED LH_SCM_PoC.orders.sales;
One can even provide meaningful descriptions for each table and its attributes via scripts like the ones below, however it might be a good idea to do this in the later phases of the PoC, when the logic become stable:
%%sql
-- modify a table's COMMENT
COMMENT ON TABLE LH_SCM_PoC.orders.sales IS 'Customer sales orders';
-- modify columns' COMMENT for an existing table
ALTER TABLE LH_SCM_DWH.orders.sales
ALTER COLUMN SalesOrderNumber COMMENT 'Sales Order Number';
Data Validation
Before diving into building any business logic, besides identifying the primary, foreign keys and further attributes used in bringing the data together, it's recommended to get an
overview of data's intrinsic and extrinsic characteristics relevant to the analysis. Some of the rules used typically for studying the quality of data apply to some extent also in here, though one needs to prioritize accordingly, otherwise one replicates the effort that's typically part of the Data Management initiatives.
In addition, it's important to check how much the identified issues impact the business logic, respectively on whether the issues can be corrected to match the expectations. Often, no logic can compensate for major data quality issues, and this can also affect PoC's results as soon as the outcomes are validated against the expectations!
Data Understanding
Further on, it makes sense to get a high-level understanding of the data by looking at the distribution of values, respectively at the records participating in the joins. Of course, more similar queries can be built, though again, one should try to focus on the most important aspects!
The analysis could for example consider the following points:
/* validation of Products */
-- review duplicated product numbers (should be 0)
SELECT ProductName
, count(*) RecordCount
FROM orders.products
GROUP BY ProductName
HAVING count(*)>1
-- review most (in)expensive products
SELECT top 100 ProductID
, ProductName
, Category
, ListPrice
FROM orders.products
ORDER BY ListPrice DESC --ASC
-- review category distribution
SELECT Category
, count(*) RecordCount
FROM orders.products
GROUP BY Category
ORDER BY RecordCount DESC
-- review price ranges (
SELECT Len(floor(ListPrice)) RangeCount
, count(*) RecordCount
FROM orders.products
GROUP BY Len(floor(ListPrice))
ORDER BY RangeCount DESC
/* validation of Customers */
-- duplicated email address
SELECT CST.CustomerID
, CST.FirstName
, CST.LastName
, CST.EmailAddress
, DUP.RecordCount
FROM (-- duplicates
SELECT EmailAddress
, count(*) RecordCount
FROM orders.customers
GROUP BY EmailAddress
HAVING count(*)>1
) DUP
JOIN orders.customers CST
ON DUP.EmailAddress = CST.EmailAddress
ORDER BY DUP.RecordCount DESC
, DUP.EmailAddress
-- duplicated Customer names (not necessarily duplicates)
SELECT CST.CustomerID
, CST.FirstName
, CST.LastName
, CST.EmailAddress
, DUP.RecordCount
FROM (-- duplicates
SELECT FirstName
, LastName
, count(*) RecordCount
FROM orders.customers
GROUP BY FirstName
, LastName
HAVING count(*)>1
) DUP
JOIN orders.customers CST
ON DUP.FirstName = CST.FirstName
AND DUP.LastName = CST.LastName
ORDER BY DUP.RecordCount DESC
, DUP.FirstName
, DUP.LastName
/* validation of Orders */
-- review a typical order
SELECT SalesOrderID
, OrderDate
, CustomerID
, LineItem
, ProductID
, OrderQty
, LineItemTotal
FROM orders.orders
WHERE SalesOrderID = 71780
ORDER BY SalesOrderID
, LineItem
-- review orders' distribution by month
SELECT Year(OrderDate) Year
, Month(OrderDate) Month
, count(*) RecordCount
FROM orders.orders
GROUP BY Year(OrderDate)
, Month(OrderDate)
ORDER BY Year
, Month
-- checking for duplicates
SELECT SalesOrderID
, LineItem
, count(*) RecordCount
FROM orders.orders ord
GROUP BY SalesOrderID
, LineItem
HAVING count(*)>1
-- checking for biggest orders
SELECT SalesOrderID
, count(*) RecordCount
FROM orders.orders ord
GROUP BY SalesOrderID
HAVING count(*) > 10
ORDER BY NoRecords DESC
-- checking for most purchased products
SELECT ProductID
, count(*) NoRecords
FROM orders.orders ord
GROUP BY ProductID
HAVING count(*) > 8
ORDER BY NoRecords DESC
-- checking for most active customers
SELECT CustomerID
, count(*) RecordCount
FROM orders.orders ord
GROUP BY CustomerID
HAVING count(*) > 10
ORDER BY RecordCount DESC
/* join checks */
-- Prders without Product (should be 0)
SELECT count(*) RecordCount
FROM orders.orders ord
LEFT JOIN orders.products prd
ON ord.ProductID = prd.ProductID
WHERE prd.ProductID IS NULL
-- Prders without Customer (should be 0)
SELECT count(*) RecordCount
FROM orders.orders ORD
LEFT JOIN orders.customers CST
ON ORD.CustomerID = CST.CustomerID
WHERE CST.CustomerID IS NULL
-- Products without Orders (153 records)
SELECT count(*) RecordCount
FROM orders.products prd
LEFT JOIN orders.orders ord
ON prd.ProductID = ord.ProductID
WHERE ord.ProductID IS NULL
-- Customers without Orders (815 records)
SELECT count(*) RecordCount
FROM orders.customers CST
LEFT JOIN orders.orders ORD
ON ORD.CustomerID = CST.CustomerID
WHERE ORD.CustomerID IS NULL
The more tables are involved, the more complex the validation logic can become. One should focus on the most important aspects.
Building the Logic
Once one has an acceptable understanding of the data entities involved and the relation between them, it's time to build the needed business logic by joining the various tables at the various levels of detail. One can focus on the minimum required, respectively attempt to build a general model that can address a broader set of requirements. For the PoC it's usually recommended to start small by addressing the immediate requirements, though some flexibility might be needed for exploring the data and preparing the logic for a broader set of requirements. Independently of the scope, one should consider a set of validations.
Usually, it makes sense to encapsulate the logic in several views or table-valued functions that reflect the logic for the main purposes and which allow a high degree of reuse (see [1]). Of course, one can use the standard approach for modelling the bronze, silver, respectively the gold layers adopted by many professionals. For a PoC, even if that's not mandatory, it might still be a good idea to make steps in the respective direction.
In this case, dealing with only three tables - a fact table and two dimensions table - there are several perspectives that can be built:
a) all records from fact table + dimension records
The following view provides the lowest level of details for the fact table, allowing thus to look at the data from different perspectives as long as focus is only the values used is Sales Orders:
-- create the view
CREATE OR ALTER VIEW orders.vSalesOrders
-- Sales Orders with Product & Customer information
AS
SELECT ORD.SalesOrderID
, ORD.OrderDate
, ORD.CustomerID
, CST.FirstName
, CST.LastName
, CST.EmailAddress
, ORD.LineItem
, ORD.ProductID
, PRD.ProductName
, PRD.Category
, ORD.OrderQty
, ORD.LineItemTotal
, PRD.ListPrice
, ORD.OrderQty * PRD.ListPrice ListPriceTotal
FROM orders.orders ORD
JOIN orders.products PRD
ON ORD.ProductID = PRD.ProductID
JOIN orders.customers CST
ON ORD.CustomerID = CST.CustomerID
-- test the view
SELECT *
FROM orders.vSalesOrders
WHERE SalesOrderID = 71780
One can use full joins unless some of the references dimensions are not available.
b) aggregated data for all dimension combinations
The previous view allows to aggregate the data at the various levels of details:
-- Sales volume by Customer & Product
SELECT ORD.EmailAddress
, ORD.ProductName
, ORD.Category
, SUM(ORD.OrderQty) OrderQty
, SUM(ORD.LineItemTotal) LineItemTotal
FROM orders.vSalesOrders ORD
WHERE ORD.OrderDate >= '2022-06-01'
AND ORD.OrderDate < '2022-07-01'
GROUP BY ORD.EmailAddress
, ORD.ProductName
, ORD.Category
ORDER BY ORD.EmailAddress
, ORD.ProductName
One can comment out the dimensions not needed. The query can be included in a view as well.
c) all records from each dimension table + aggregated fact records
Sometimes, it's useful to look at the data from a dimension's perspective, though it might be needed to create such an object for each dimension, like in the below examples. For the maximum of flexibility the logic can be included in a table-valued function:
-- create the user-defined function
CREATE OR ALTER FUNCTION orders.tvfProductsSalesVolume(
@StartDate date NULL,
@EndDate date NULL
)
RETURNS TABLE
-- Sales volume by Product
AS
RETURN (
SELECT PRD.ProductID
, PRD.ProductName
, PRD.Category
, ORD.FirstOrderDate
, ORD.LastOrderDate
, IsNull(ORD.TotalSalesQty, 0) TotalSalesQty
, IsNull(ORD.TotalSalesValue, 0) TotalSalesValue
, IsNull(ORD.OrderCount, 0) OrderCount
, IsNull(ORD.LineCount, 0) LineCount
FROM orders.products PRD
OUTER APPLY (
SELECT Min(ORD.OrderDate) FirstOrderDate
, Max(ORD.OrderDate) LastOrderDate
, SUM(ORD.OrderQty) TotalSalesQty
, SUM(ORD.LineItemTotal) TotalSalesValue
, count(DISTINCT SalesOrderID) OrderCount
, count(*) LineCount
FROM orders.orders ORD
WHERE ORD.ProductID = PRD.ProductID
AND ORD.OrderDate >= @StartDate
AND ORD.OrderDate < @EndDate
) ORD
);
-- test the user-defined function
SELECT *
FROM orders.tvfProductsSalesVolume('2022-06-01','2022-07-01') PRD
WHERE TotalSalesValue <> 0
ORDER BY TotalSalesValue DESC
, LastOrderDate DESC
-- create the user-defined function
CREATE OR ALTER FUNCTION orders.tvfCustomersSalesVolume(
@StartDate date NULL,
@EndDate date NULL
)
RETURNS TABLE
-- Sales volume by Customer
AS
RETURN (
SELECT CST.CustomerID
, CST.FirstName
, CST.LastName
, CST.EmailAddress
, ORD.FirstOrderDate
, ORD.LastOrderDate
, IsNull(ORD.TotalSalesValue, 0) TotalSalesValue
, IsNull(ORD.OrderCount, 0) OrderCount
, IsNull(ORD.LineCount, 0) LineCount
FROM orders.customers CST
OUTER APPLY (
SELECT Min(ORD.OrderDate) FirstOrderDate
, Max(ORD.OrderDate) LastOrderDate
, SUM(ORD.LineItemTotal) TotalSalesValue
, count(DISTINCT SalesOrderID) OrderCount
, count(*) LineCount
FROM orders.orders ORD
WHERE ORD.CustomerID = CST.CustomerID
AND ORD.OrderDate >= @StartDate
AND ORD.OrderDate < @EndDate
) ORD
);
-- test the user-defined function
SELECT *
FROM orders.tvfCustomersSalesVolume('2022-06-01','2022-07-01') PRD
WHERE TotalSalesValue <> 0
ORDER BY TotalSalesValue DESC
, LastOrderDate DESC
When restructuring the queries in similar ways, there's always a compromise between the various factors: (re)usability, performance or completeness.
Further Comments
The above database objects should allow users to address most of the requirements, though, as usual, there can be also exceptions, especially when the data needs to be aggregated at a different level of detail that requires the query to be structured differently.
The number of perspectives can increase also with the number of fact tables used to model a certain entity (e.g. Sales order headers vs. lines). For example,
In theory, one can also find ways to automate the process of creating database objects, though one must choose the relevant attributes, respectively include logic that makes sense only within a certain perspective.
No matter the data, respectively systems used as source, expect surprises and test your assumptions! For example, in the file used to create the orders.customers table, there seem to be duplicated entities with the same name and email address. One must clarify how such entities must be handled in data analysis, respectively in data modeling. For example, a person can appear twice because of the roles associated with the name or can be other entitled reasons.
The files in scope of this post are small compared with the files existing in
organizations. In many scenarios files' size could range from GB to TB and thus require
partitioning and different other strategies.
|>> Next Post
References
[1] sql-troubles (2023) Architecture Part IV: Building a Modern Data Warehouse with Azure Synapse [link]
Resources
[1] Microsoft Learn (2024) Fabric: Lakehouse and Delta Lake tables [link]
