Data Repository: Centralized Data Storage

Data Repository

• Data Repository (Data Store) refers to a centralized storage area where data is collected, organized, isolated, and managed
• It facilitates business operations, reporting, and data analysis
• It can range from simple databases to complex data warehouses
• It can include supporting infrastructures such as servers or cloud platforms
• The purpose is to create a single source of truth for an organization's data

Characteristics of Data Repositories

• Centralization means consolidating data from many sources into one location, providing a unified view
• Centralization eliminates redundancy and inconsistencies
• It ensures a single source of truth for reporting and analytics
• Scalability refers to the capability of a system to handle greater workloads or data volumes
• Scalability allows the system to adapt to business growth
• It prevents performance degradation as workload increases
• Elasticity is when a scalable system automatically adjusts capacity, scaling up or down based on demand
• Elasticity ensures efficient resource use and cost optimization
• Data integrity ensures data is accurate, reliable, consistent, and trustworthy throughout its lifecycle
• Data integrity maintains trust in data-driven decisions, preventing corruption and errors
• Accessibility refers to how easily authorized users can retrieve and use data from the repository
• Accessibility allows timely decision-making and facilitates collaboration
• Security protects data from unauthorized access, theft, corruption, or loss
• Security is essential for safeguarding sensitive data and ensuring compliance

Benefits of a Data Repository

• Provides a single, unified source of truth for all data within an organization
• Enforces data integrity and consistency, improving data quality
• Makes it easier to retrieve and analyze data with efficient data access
• Ensures data is secure and protected with data security
• Can handle large volumes of data and scale as the organization grows with scalability
• Enables organizations to derive meaningful insights from their data with analytics

Data Pipeline

• Is a set of tools, processes, and automated workflows designed to transfer data
• Transfers data from one system or source to another for storage, processing, or analysis
• It delivers datasets to databases, analytical tools, or applications
• Enables quick and reliable access to consolidated information

Data Pipeline Challenges

• Data can become corrupted
• Bottlenecks may cause latency
• Data sources may conflict, generating duplicate or incorrect data
• Incompatible data types may arise
• Careful planning and testing are needed to filter out junk data and duplicates

ETL & ELT Pipelines

• ETL Pipelines involves extracting data from different sources
• They transform data to match a unified format for specific business purposes
• Then they load reformatted data to the storage (mainly, data warehouses)
• ELT Pipelines extract data from different sources
• They load data to the storage
• They transform data to match a unified format for specific business purposes

ETL vs ELT

• ETL extracts, transforms, and then loads data
• In ETL, transformation occurs before loading into the target
• With ETL, data loading happens after transformation
• ETL has slower loading speeds due to transformation
• ETL may require more infrastructure for scalability
• ELT extracts, loads, and then transforms data
• In ELT, transformation often occurs after loading into the target
• With ELT, data loading happens before transformation
• ELT has faster loading speeds as loading is done first
• ELT can scale well with the target system
• Apache NiFi, Talend, and Informatica are tools for building and managing data pipelines

Databases

• Is a collection of data, or information, designed for input, storage, search, retrieval, and modification
• Database Management System (DBMS) is a set of programs that creates and maintains the database
• DBMS allows storing, modifying, and extracting information from the database using querying

Factors influencing the choice of database

• Data Type: Relational databases work for structured data, while NoSQL is suited for semi- or unstructured data
• Structure: Relational databases need fixed schemas, while NoSQL gives flexibility
• Querying Mechanisms: SQL is excellent for complex queries with joins and aggregations
• NoSQL provides faster performance for simple queries on large datasets
• Latency Requirements: Consider the speed at which data needs to be retrieved
• Transaction Speeds: Consider the need for high transaction speeds
• Intended use of the Data: Consider how the data will be used and the application's requirements

Types of Databases

• Relational Databases are also referred to as RDBMS
• Relational Databases build on the organizational principles of flat files
• Data is organized into a tabular format with rows and columns
• Relational Databases have a well-defined structure and schema
• Optimized for data operations and querying
• Structured Query Language (SQL) is the standard querying language for relational databases
• Non-relational Databases known as NoSQL or "Not Only SQL"
• Non-relational Databases emerged in response to volume, diversity, and speed of data being generated
• Influenced by cloud computing, the Internet of Things, and social media proliferation
• NoSQL is built for speed, flexibility, and scale
• It stores data in a schema-less or free-form fashion
• NoSQL is widely used for processing big data

Data Warehouses

• Is a central repository for storing, managing, and analyzing data from various sources
• Designed to facilitate reporting and analysis
• Consolidates data from disparate sources into a single, unified view
• Used for business intelligence (BI) purposes
• It allows users to make informed decisions based on various views of business operations

Need of Data Warehousing

• Business users need a data warehouse to look at summarized past data in an uncomplicated way
• Data warehouses are programmed to be applied regularly and make it effortless for company decision-makers
• Data Warehouses are used for storing historical data, for various purposes.
• Data warehouses contribute to making better strategic decisions
• Business strategies depend on the data within warehouses
• Data warehouse must be prepared for somewhat sudden masses and types of queries
• Demanding a degree of flexibility and fast latency

Characteristics of Data Warehouse

• Serves as the ultimate storage: An enterprise data warehouse is a unified repository for all corporate data
• Stores structured data: The data stored is always standardized and structured for easy BI queries
• Subject-oriented data: Warehouse focuses on business data that relates to different domains
• Has time-dependent data: Collected data is usually historical to describe past events
• Not volatile: Data is never deleted, only manipulated, modified, or updated

Data Warehouse Architecture

• Has Data Sources like CRMs, ERPs, SQL/NoSQL databases, IoT devices, Social media, spreadsheets, etc.
• The Staging Area is where data is prepared for moving into the data warehouse via ETL/ELT processes
• The Storage Layer has a Metadata Manager, Data Warehouse and Data Marts
• The Presentation Layer has BI Tools, Reporting tools, and business applications, for analysis

Data Sources

• Relational Databases are internal structured systems such as SQL Server, Oracle, MySQL, and IBM DB2
• Flatfiles and XML Datasets are publicly and privately available. Examples are demographic and economic datasets such as Point-of-Sale, Financial, weather data, with one record per line
• APIs and Web Services provides Applications Program Interfaces, and Web Services, which can be requested and return data in plain text, XML, HTML, JSON, or media files
• Web scraping is used to extract relevant data from unstructured sources and pages

The Layers

• Ingestion Layer: ETL and ELT tools connect to all the source data and perform its extraction, transformation, and loading into a storage system
• Staging Layer: An interim storage region for Data Processing, between the Data Source(s) and the Data Target(s), for extraction, and validation.
• Storage Layer: Includes final data models which are a blueprint of how data will be stored, relational databases, and metadata repository for the data warehouse.
• Data Marts: Subsection of the data warehouse, built specifically for a particular business function, that has faster data access and access control. They can be dependent, independent, and hybrid.
• Presentation Layer : Interface that enables users to interact with and visualize the data, the underlying data, and the end-users, providing insights. Examples are BI tools.

Data Warehouse Schema

• Is the blueprint or architecture of how data will be stored. There is also Star, Galaxy, and snowflake schema

Star Schema

• Star Schema is the most used design for data warehousing and features a central fact table
• This fact table holds the primary data or measures and is connected to multiple dimension tables
• Dimension Tables hold the descriptive information
• Fact table maintains one-to-many relations with dimension tables
• Can be though of as interlinked star structures, normalized to avoid duplication

Snowflake Schema

• Snowflake Schema connects each dimension to a central fact table, some tables may have sub-dimensions.
• This normalization reduces data redundancy
• This leads to less efficient queries compared with Star Schema

Galaxy Schema

• Galaxy Schema connects multiple fact tables with shared normalized dimension tables.
• Can be though of as interlinked star tables, normalized to avoid duplication

Presentation Layer

• Is the interface that enables users to interact with and visualize data, through various tools, queries and applications

Data Warehouse Cloud Technology

• Cloud tech aims to provide lower costs, limitless storage, scale on a pay-as-you go basis, and faster disaster recovery
• Teradata, Oracle, IBM, Amazon Redshift, Google BigQuery, Cloudera, and Snowflake are examples

Data Lakehouse

• Is a repository designed to store, process, and secure large amounts data
• Designed for structured, semi-structured, and unstructured data with any size limits.
• Is usually a single store of all enterprise data,
• May include raw copies of source system data, and data used for reporting, visualization, analytics, and machine learning
• Useful for Inexpensive storage, data retention, real-time data processing. Has a variety of layers, data sources, analytical sandboxes etc.

Data Sources

• Structured data: These are relationa databases in tables such as MySQL, Oracle, and Microsoft SQL Server.
• Semi-structured data: Includes HTML, XML, and JSON files.
• Unstructured data: Includes data in Internet of Things (IoT) applications, videos, audios, social media content such tweets and Facebook messages.

Data Ingestion

• Importing data into the data lake through batch and real-time tools

Data Storage and Processing

• Lands in a the raw where data lives, then data enters a transformation section
• Trusted data becomes reliable
• Contains data cleaning, normalization and structuring
• Enters lastly a refined or conformed data zone

Analytical Sandboxes

• Isolated environments for data exploration
• Activities are discovery, machine learning, predictive modeling, and exploratory data analysis .

Data Consumption and Governance

• The reliable data is now ready for end users and is exposed via Business Intelligence
• Goverance for standards and security
• Monitroing ELT