Artur Skrzeta

Data Warehousing for Business Insights

Intro

This section presents a process of data collection, processing and analyzing to provide meaningful business insights. I also provide some theoretical introduction on what tools and techniques need to be applied to make it works.

ETL components:

• Data source.
• Data ingestion.
• Data storage.
• Data visualization.
• Documentation.

Data Integration:

• Combining data from different sources and providing users with unified view of them.
• It integrates data from heterogeneous database systems and transforms it into a single coherent data store.
• We can find it helpful in data mining and business analysis.
• With the data integration, the sources may be entirely within your own systems. On the other hand, data ingestion suggests that at least part of the data is pulled from another location (e.g. a website, SaaS application, or external database).

ETL:

• It can be defined as a repeatable programmed data movement.
• It stands for:
  - extract: getting data from different sources.
  - transform: filter/map/enrich/combine/validate/aggregate/sort/applying business rules to the data
  - load: store data in the data warehouse or data mart.
• Data Warehouse (DW) vs Data Mart (DM):
  - DW is broadly focused on all the departments. It is possible that it can even represent the entire company.
  - DM is a specific business subject-oriented, and it is used at a department level.
• ETL process can be applied in making data strucutred from data lake keeping unstructrued data from different sources:
  
  

ETL designing:

1. Operational db:
   - we don't go to an operaional database directly, as we don't want to weigh its performance down with analytical functions,
   - operaitonal db is only resposible for transactions saving which cosumes a lot of performance power.
2. Flat files (CSV, JSON, XML):
   - extracting data from operational db like a snapshot with some additional meatadata like timestamp, system name etc.
   - extracting flat files and working on them eliminates any operaional db perfomrance issue risks.
3. Raw database:
   - loading data as it is in the flat files without any business logic applied yet,
   - the purpose is to be able to check if the data makes sense, for instance if the user name is ok, or if the zip-code has an expected format, if the commas is in a wrong place etc,
   - we perform some QA check on the fields matching, if some data don't fall into inappropriate db field,
   - here, we don't apply any transofrmation logic as we want to test pure data, otherwise it would be hard to debug some issues if they come from either the logic or data itslef,
   - we can move on with some logging here to track what files has been loaded, how many rows etc.
4. Staging database:
   - here we apply business logic, some mappings, standarization, deduplicaiton, denormalization to transform the data up to business requirements,
   - QA perfomed after appling transomration - we can be chekcing if the amount of distinct records is still as expected after applying some aggregating funcitons,
   - basically, QA at this stage confirms if I can trust the data or not,
   - we can also here do some logging on how many records have been loaded, what stred procedures were run, if they were successfull or not, how long they work.
5. Data Warehouse:
   - final destination of ETL,
   - here we proceed with further data analysis,
   - can be a back-end of Tableau dashboard,
   - consists of fact tables and dimension tables.
6. Here is the schema that shows the pahses of ETL:
   source: Seattle Data Guy

Additonal things for ETL:

• Data QA,
• Error hanlding and logging:
  - tracking when etl fails,
  - tracking why is that,
  - it's important for the maintenance.
• Dashborad for logs visualization,
• Data linage and data catalog:
  - tracking fileds dependencies.
• Metadata database:
  - tracking pipelines dependencies.
• Types of transformation:
  - Cleansing: removing information that is inaccurate, irrelevant, or incomplete.
  - Deduplication: deleting duplicate copies of information.
  - Joining: combining two or more database tables that share a matching column.
  - Splitting: dividing a single database table into two or more tables.
  - Aggregation: creating new data by performing various calculations (e.g. summing up the revenue from each sales representative on a team).
  - Validation: ensuring that the data is accurate, high-quality, and using a standard data type.

Data Lineage:

• Records the whole life cycle of data - the data flow from the source to the consumption alogn with all of the transformations between.
• Benefits:
  - tracking errors during data processing,
  - implementing process changes with lower risk,
  - performing system migrations with higher confidence,
  - maintaining metadata and data mapping framework,
  - discavering anomalies fast,
  - validating data accuracy and consistency,
  - making sure data is transoformed correctly.

Data governance:

• Managing users accesss on who can access what on which level.
• There is a so-called Data Steward who manages this.

QA on database:

• We may want to refer to previous data.
• We can perform many checks simply with SELECT queries.
• We can perform following checks:
  - Categorical checks - when we know a field has a very specific value.
  - Null checks - depending if the fileds accept ones. If there are nulls/blanks in a numeric column, we may want to fill it up with an average value.
  - Aggregate checks - counting records both in raw table and prod table and comparing the results. We can do these checks agains pivot table in excel or with sql statement aggreagring as the same fileds as dashboard. It may appear thet some records in prod are missing and we can find the missing one in the raw table. These kind of changes can be a result of accidental removal throughout the process.
  - Anomaly checks - test for data that doesn't make sense - f.e. average values is lower than minimum value.
  - Outlier checks - detecting numeric values that far away from an aversage.
  - Valudation checks - string/dates format.
• QA for db is not as such developed and standardized as for app's code. Most ofthen, in order to get app tested out, the testing tools and environments peel away dependency layers such as databases. This means a traditional QA unit testing practices are designed to overlook the databases just to get focused on examination of the code itselft.

Data Lake:

• It doesn't enfore a data schema as it keeps the door open for all organization's data.
• Can store different forms of data:
  - structured data from relational databases (rows and columns),
  - semi-structured data (CSV, logs, XML, JSON),
  - unstructured data (emails, documents, PDFs),
  - binary data (images, audio, video).
• We put a structure on a data when quering it (ELT process).
• Can be both established on-premise or in the cloud.
• The disadvantage can be that user may find the data uncategrized with no context what the data represents.
• There is also a data swamp which is unmanagable and inaccessible or low-value-providing data lake.
• With data lake came in, the emphasis has been switched from data lineage to data catalog.

ELT:

• It stands for Extract, Load and Transform.
• This process first loads the data into the target storage and then leverages the target system to do the transformation.
• It makes sense when target system is based on high-performace engine like Hadoop cluster.
• Hadoop clusters work by breaking a problem into smaller chunks, then distributing those chunks across a large number of machines for processing.

Data Ingestion:

• Any process that transports data from one location to another so that it can be taken up for further processing or analysis.
• To ingest something means literally to take something in or absorb something located outside your internal systems. Data can be streamed in real time or ingested in batches:
  - streaming data ingestion - data is being loaded into target locatrion almost immediately it appears in the systems. For example manula inputs from the user utilizing the write-back function in the Tableau dashboard. Records are entered into target table right away and can be undergo the analysis or can be displayed on another dashboards.
  - >b>batch data ingestion - data is collected and transferred in batches at regular intervals. For example extracting flat files from SAP systems and loading them using coded pipelines.

Db vs DW

• Db:
  - mainly for transactional databases,
  - many inserts and updates or deletes,
  - for live apps,
  - normalized,
  - saves space,
  - limits possible performance and redundancy issues,
  - mainly for operational purposes.
• Dw:
  - centralizes and standardizes all of the data from either company's or third party's applicatons,
  - for analytical purposes separating it from transactional systems in order to keep the performance,
  - consists of fact tables that groups all transactions,
  - consists of dimension tables with which we can break down transactions for example by category/regiona and sum up the sales,
  - used by analytical teams to quickly select the data without joining tables and understanding whole relationship logic.

Fact Table and Dimension Table:

• Fact table is a central table of the star schema in a data warehouse. It stores quantitative information for the analysis.
• Fact table being kept denormalized in order to combine multiple tables into one so that it can be queried quickly.
• A fact is every event that can undergo analysis. Facts have measures, for instance a transaction (fact) may have 2 measures (value, units sold) - numerical or boolean.
• A fact table works with dimension tables. While the fact table holds the data to be analyzed, the dimension table stores data about the ways in which the data in the fact table can be analyzed.
• Here is how we can visualize the dependecy between facts and dimensions:

  SELECT salary
  FROM employees
  WHERE country IN ('Poland', 'Germany', 'France')
  

  - salary is one of facts and country is one of the dimensions.
• Thus, the fact table consists of two types of columns:
  - foreign key columns that joins dimension tables (Time ID, Product ID, Customer ID for example below),
  - measure columns containing quantitative data being analyzed (Unit Sold).
• Here is the example of fact table. Every sale is a fact that happens, and the fact table is used to record these facts:
  source: searchdatamanagement.techtarget.com

  Time ID	Product ID	Customer ID	Unit Sold
  4	17	2	1
  8	21	3	2
  8	4	1	1

• Dimension table:
  source: searchdatamanagement.techtarget.com

  Customer ID	Name	Gender	Income	Education	Region
  1	Brian Edge	M	2	3	4
  2	Fred Smith	M	3	5	1
  3	Sally Jones	F	1	7	3

• In the above example, the customer ID column, in the fact table, is the foreign key that joins with the dimension table. Let's have a look at fact table's 2nd row:
  - row 2 of the fact table records the fact that customer 3, Sally Jones, bought two items on day 8.
  
• We can put table structure through:
  
  

  Normalization	Denormaliaztion
  Used to remove redundant data.	Used to combine multiple tables for quick querying.
  Focuses on clearing the database from unused data.	Focuses on achieving the faster execution.
  Uses optimized memory.	Causes some sort of wastage of memory.
  Used where number of insert/update/delete operations are performed and joins of those tables are not expensive.	Used where joins are expensive and frequent query is executed on the tables.
  Ensures data integrity i.e. any addition or deletion of data from the table will not create any mismatch in the relationship of the tables.	Doesn't ensure data integrity.

• DWH structure where we have denormalized fact table (or a few of them) containing measures and foreign keys to denormalized dimension tables is called star schema.
• DWH structure where we have denormalized fact table (or a few of them) containing measures and foreign keys to normalized dimension is called snowflake schema.

Slowly Changing Dimensions:

• They are dimensions for which attributes change slowly over time due to business needs.
• Slowly over time means changes happen occasionally rather than on a regular schedule.
• We can apply different approaches:
  - type 1: overwriting the value with the new one and not tracking the historical data;
  - type 2: preserving the history by creating another records with updated value and unique primary key - we can put anothe column with versioning;
  - type 3: preserving the partial history where we create a pair of attributes to track current value and previous one;
  

Columnar databae:

• In relational databases, tables are defined as an unordered collection of rows.
• However, columnar database stores data in columnar sequence instead of row's.
• Here is employees table:

  rowid		NrPrac		Imie	 	Nazwisko	Pensja
  001 		10 		Alicja 		Zielinska	5200
  002 		11 		Bogdan 		Wrona		4900
  003 		12		Czesław 	Kowalski 	5600
  004 		114		Dominik 	Wrona 		5200
  

  source: if.uj.edu.pl
  - in columnar store, above data looks like following:

  10:001; 11:002; 12:003; 114:004;
  Alicja:001; Bogdan:002; Czesław:003; Dominik:004;
  Zielinska:001; Wrona:002,004; Kowalski:003;
  5200:001,004; 4900:002; 5600:003;
  

  source: if.uj.edu.pl
  - where the first line contains the values of the whole column named NrPrac
  - the same for the rest of columns,
  - attribute's value indicates specific rowid.
  
• When quering database about one or tow columns then a relational database's engine needs to read all records anyway. It slows down the overall performance. In columnar database, engine read only columns that we query. We fetch data fast and only that data we need. On the other hand, any record operations are very slow in a columnar database.
• In columanr storage data is being divided into micropartitions with specific metadata assigned. Thanks to metadata db engine knows more or less at what place of the disk a specific range of data's id is being kept. This is why we can fetch entire row even though we work with a columnar storage.
• Becasue fact tables in data warehouses are often long (many rows) and wide (many columns) and their data doesn't change while OLAP, they are being stored in te columnar form.

Data Warehous (DWH) desing:

• Designing DWH we need to pay closer attention to business requirements of further reporting.
• Data dimensions and granuality of data needs to be adjusted to reports that will be issued based on the DWH.
• The granuality depends on business requirements. In sales, besides price and units sold, we can associate transaction fact with foreign keys to dimensions as time, product, shop, salesman and even more. The more dimensions, the more foreign keys, the more detailed analysis in the OLAP.
• Dimensions need to follow granuality, no the other way around.

DWH working modes:

• Operational or analytical mode:
  - operations limited only to analytical queries,
  - no for any data or table schemas modifications,
  - data in DWH stays unchanged.
• Modification mode:
  - authorized users ingest the data into DWH,
  - it can be done automatically on specific time,
  - there can be also actualization of indexes perfomed,
  - analytical users loses connection at time of DWH modificaiton,
  - any schamas modification may pose a danger for any predefiend queries and report.

Query optimalization:

• DWH is espacially optimized for reading operations.
• To keep high level of optimalizaton we should be avoiding performing one operation many times but we want to redefine query to run it only once instead.
• Here is unefficeint way to query with WHERE clause:

  SELECT ...
  FROM ...
  WHERE X AND P1
  
  SELECT ...
  FROM ...
  WHERE X AND P2
  
  ........................
  
  SELECT ...
  FROM ...
  WHERE X AND Pn
  

  - as we can see X condition repeateas itself many times in WHERE clause,
  - we can take condition X as general condition put on the data,
  - applying that X condition reduces data set into a smaller sub-set,
  - instead of putting X condtion with every select we can put it once in the materialized view:
  

  CREATE MATERIALIZED VIEW my_mat_view AS
  SELECT ...
  FROM ...
  WHERE X
  

  - we obtain subset resulting from filtering data set into sub-set that is in line with X condition,
  - we can then proceed with further variable conditions: P1, P2, Pn:

  SELECT ...
  FROM my_mat_view
  WHERE P1
  
  SELECT ...
  FROM my_mat_view
  WHERE P2
  
  ........................
  
  SELECT ...
  FROM my_mat_view
  WHERE Pn
  

  - the main difference between a materialized view and a regular view:

  Materialized view	Regular view
  When calling, it references to outcome is physically saved in the system.	When calling, it performs hidden query execution.
  It's getting refreshed automtically.	There is no automated refreshing.

ETL issues:

• Multiple data sources as multiple seources of thurth - > different analysis -> different total values.
• Avoiding errors in excel:
  - treat complex Excel sheet like a code - that means test when you make it, test it when you edit it and retest,
  - limit logic to as few places as possible - often it can be better to apply business logic in a single place like sql cor code layer other than adding more logic into Excel and Tableau.
• Too many dashboards and not enough action:
  - why we build the dashboard and what are the decision it will drive,
  - if it's not driving decision then why we building them.
• Low quality and incomplete data:
  - automated QA system,
  - ingegration tests - making sure the logic inside te dashboard tool didn't change the initial data - if the numbers that we can see on the dashboard are the same sa we can calculated in the query from the dataset,
  - unit tests - level of data table itself.

Errors-prone practicies:

• Not building reproducable, extendable, maintainable code.
• Trusting data source accuraccy - a need for QA system.
• Complex logic in one sql query - a need for breaking up into smaller staging steps.
• Bulding things without a purpose:
  - including no decision driven analysis/dashboards,
  - not understanding the bussines case and the business impact,
  - not understanding the user or developing not a user-friendly systems.
• Putting transformation logic outside of integration tools or not encapsulating it in a data catalog.

Database for stateful apps:

• Stateless:
  - Stateless microservices don’t store data on the host.
  - Stateless service can work using only pieces of information available in the request payload, or can acquire the required pieces of information from a dedicated stateful service, like a database.
  - The server doesn’t hold onto the state information between requests (doesn’t rely on information from earlier requests), the state can be held into an external service, like a database.
  - Different requests can be processed by different servers - any service instance can retrieve all service state necessary to execute a behavior from elsewhere enables resiliency, elasticity.
  - It scalee automatically and when your usebase is big and geographically distributed across the globe.
• Stateful:
  - Stateful microservices require some kind of storage on the host who serves the requests.
  - The server processes requests based on the information relayed with each request and information stored from earlier requests.
  - The same server must be used to process all requests linked to the same state information, or the state information needs to be shared with all servers that need it.
  - Need to ensure some kind of scaling for your stateful services, and also plan for backups and rapid disaster recovery.
source: proud2becloud.com

Analytical MPP database:

• Analytical Massively Parallel Processing (MPP) Databases that are optimized for analytical workloads: aggregating and processing large datasets.
• MPP databases tend to be columnar, so rather than storing each row in a table as an object (a feature of transactional databases). MPP databases generally store each column as an object.
• Having columnar store architecture allows complex analytical queries to be processed much more quickly and efficiently. This allows to only access the fields needed to complete a query (as opposed to transactional databases, which must access all fields in a row).
• Columnar stora also enables to easliy index very large tables and to keep them denormalized.
• These analytic databases distribute their datasets across many machines, or nodes, to process large volumes of data (hence the name). These nodes (groupped in a cluster) all contain their own storage and compute capabilities, enabling each to execute a portion of the query. Adding more nodes to a cluster, the same workload can be distributed to more servers and completed more quickly.
• All MPP systems are fast because a “leader” can make a query plan and then distribute the actual work of executing the query to many workers (nodes).
• These technologies are typically optimized for batch loads.
• Some analytical data warehouses are solely available via a hosted architecture; Amazon Redshift, Snowflake, and Google BigQuery for example, are offered solely through the cloud. Others, like Teradata are able to be deployed both on-premise, packaged as appliances (software and hardware bundled), or deployed via a hosted model in the cloud.
• Analytical MPP databases can easily scale their compute and storage capabilities linearly by adding more servers to the system.
source: looker.com/

Data pipeline on cloud:

1. SAP scheduler job runs ABAP code to extract data locally into the server.
2. OpenFT or SFTP script takes the flat files and transfer them into the target system (AWS S3 bucket).
3. Amazon SNS (simple nofitication service) fires events of new files coming in.
4. Event triggers snowpiplne ETL which applies the transformation logic and feeds the snowflake data tables.
5. There are materialized views of target table in the access schea being integrated with Snowflake server ensures the data on the dashboard are up to date (live connection to the data source).