What are the different types of databases used for Business Intelligence (BI)?

One advantage and one disadvantage of using relational databases, which may not have been fully covered in the article, are: Advantage: Data Integrity Relational databases are known for their strong data integrity. They enforce data consistency through features like constraints, foreign keys, and referential integrity, which ensure that data is accurate and relationships between tables are maintained. This is crucial for maintaining high-quality, reliable data. Disadvantage: Scalability Especially in situations where the volume of data grows rapidly. Adding more data or expanding the database to handle increased workloads can be complex and expensive, which can limit their effectiveness in handling big data or high-traffic applications.

RDBMS is the most traditional and widely used type of database for BI. It organizes data into tables with predefined relationships, and it uses Structured Query Language (SQL) for querying. Key Characteristics: Data is organized in rows and columns, allowing for efficient storage and retrieval. Advantages: Data Integrity and Consistency: RDBMS enforces data integrity through features like constraints and foreign key relationships, ensuring that data remains accurate and consistent. Mature Ecosystem: RDBMS systems have been widely used for decades, resulting in a mature ecosystem with extensive support, documentation, and a large community of users and developers.

Relational DB supports ACID properties and write optimized. RDBMS is used mostly for OLTP applications, data stored in normalized forms. We could definitely use RDBMS for OLAP as well but fit dimensional modeling, Star or Snowflake schema structure. RDBMS uses schema-on -write and schema-on-read. There are other OLAP optimized datastores available to work with best results.

Relational databases, while widely used, harbor lesser-known facets: Mathematical Roots: Relational databases are grounded in set theory and predicate logic, owing much to the academic work of Edgar F. Codd in the early 1970s. Non-Relational Extensions: Some relational databases, like PostgreSQL, embrace non-relational features, blurring the line between traditional SQL databases and NoSQL systems, incorporating JSON support and other extensions. SQL Standardization: SQL, the language for querying relational databases, is an international standard, but its implementations often exhibit variations, leading to nuances in syntax and functionality across different database systems.

NoSQL databases are designed for handling large volumes of unstructured or semi-structured data. They are highly scalable and flexible in data modeling. Key Characteristics: Well-suited for handling diverse data types, including text, documents, graphs, and more. Common types include document-oriented, key-value, column-family, and graph databases. Advantages: Scalability and Flexibility: NoSQL databases are highly scalable and can handle large volumes of unstructured or semi-structured data. They are also flexible in data modeling, making them suitable for dynamic BI needs. High Performance for Certain Use Cases: NoSQL databases can offer high performance for specific types of analytical queries and real-time processing.

NO SQL is quite broad and has specific usage based on different NO SQL Databases. Mong fits apps dev while Neo4J for network structure and easy discovery of nodes. Redis is fit for in-memory cache and HBase supports realtime analytical use cases due to columnar storage.There is no one size fits all solution/answer here. We need to choose DB options based on the specific need.

Data warehouses are indispensable for business intelligence, offering a consolidated view of organizational data, facilitating historical analysis, and ensuring data quality. Scalability is a key advantage, allowing adaptation to growing volumes and user demands. However, challenges include the complexity of implementation, requiring specialized skills, and limited support for unstructured data. While the centralized repository streamlines analysis, it demands careful handling of diverse data sources and formats during integration. The historical data storage capability empowers decision-making based on trends, enhancing the strategic value of business intelligence.

A data lake is a centralized repository that stores vast amounts of structured, semi-structured, and unstructured data in its raw format. It serves as a comprehensive data hub, enabling organizations to collect, store, process, and analyze all types of data without limitations imposed by traditional data warehouses. This unfiltered approach allows for the preservation of all data, regardless of its format or potential value, enabling organizations to extract insights from a broader range of sources. Google Cloud stands out as a leader in data lake solutions, offering a suite of services that cater to the entire data lifecycle, from data ingestion to analysis and visualization.

A data warehouse is a specialized type of database optimized for analytical queries. It consolidates data from various sources into a centralized repository for reporting and analysis. Key Characteristics: Data is transformed, cleaned, and integrated from multiple sources to provide a unified view. Often used for historical analysis and reporting. This approach birthed the ELT method. Advantages: Centralized Data Storage: Data warehouses serve as a centralized repository for storing large volumes of structured data from various sources. Optimized for Analytics: Data warehouses are specifically designed and optimized for analytical queries.

I am not quite sure if latency is really an attribute of Data Warehouses anymore. With the advent of all streaming data solutions based on event driven architecture like kafka, pub sub, apache beam, storm, spark stream and Columnar highly performing analytical databases like BQ, RDS, Druid, Click house, Pinot etc we can support million of events ingestion per second with realtime stateful analytics at scale.

Unified Analytics: Recent innovations in data lakes focus on integrating analytics tools, enabling seamless data exploration and analysis within the data lake environment. Metadata Management: Advanced metadata tools enhance data discoverability and governance, allowing users to understand and trust the data within the lake. Serverless Architectures: The adoption of serverless computing in data lakes reduces operational complexities, allowing organizations to scale resources dynamically based on workload demands. Data Lakehouse Concept: The emergence of the data lakehouse concept combines the best of data lakes and data warehouses, providing improved structure and performance for analytics and business intelligence.

You can think of a data lake as a big digital storage pond where a company puts all its data. This data can be numbers, words, pictures, or anything else they collect. In the context of Business Intelligence (BI), the data lake helps the company make better decisions by allowing them to gather and analyze a lot of different data from various sources. Data lakes enable the integration of data from different systems and sources. This integration helps BI teams get a complete picture of the organization's data, promoting data-driven decision-making.

With recent innovations in data virtualization, data fabric, delta lake offerings the gap between Data lake and Data Warehouse is blurred. Data lake is mostly the raw storage layer for data ware houses now.

You may be wondering what the difference are between data warehouse and data lakes: 1. Data warehouses store structured data. That is, data is organized into tables following a specific schema. Data lakes store all kinds of data, structured or unstructured. They can handle a variety of data formats like text, images, videos, log files. 2. Data processing in a data warehouse involves Extract, Transform, Load (ETL) processes, where data is extracted from various sources, transformed into a consistent format, and loaded into the warehouse. Data processing in a data lake involves data ingestion and storing. Data is ingested in its raw form and stored.

As a data engineer, it's crucial to consider a few key aspects beyond technical skills. Firstly, effective communication is paramount. You'll need to collaborate with various stakeholders, including data scientists, analysts, and business teams, to understand their requirements and deliver solutions that meet their needs. The field of data engineering is constantly evolving, so staying informed about the latest tools and techniques will help you make informed decisions. Also, a strong focus on data quality and integrity is essential. Implementing robust data validation and quality checks with tools like "Great Expectations and Pytest" will ensures that the information processed and stored is accurate and reliable.

Apart from the mentioned DBs, I would like to about on the Cloud Database. A cloud DB is a collection of data that resides on a private, public or hybrid cloud computing platform. There are two types of cloud DB models: traditional and database as a service (DBaaS). With DBaaS, a service provider performs the administrative tasks and maintenance. Few eg of the Cloud Databases are : Amazon RDS, Oracle ADW, IBM DB2. Few Advantages of the cloud db are: 1. Its scalable - It can easily handle large number of data and also support large number of users and applications. 2. Its flexible - We can add/remove resources as and when required. 3. Reduced Cost - They do not require the upfront investment in hardware and infrastructure like on-premise DB

No BI, temos as principais arquiteturas: Data Warehouse: Focado em dados estruturados e otimizado para análises rápidas e relatórios, utiliza ETL para garantir dados prontos para uso. Data Lake: Armazena grandes volumes de dados variados, incluindo estruturados, semi-estruturados e n?o estruturados, ideal para explora??es analíticas profundas e data science. Data Lakehouse: Mescla o amplo armazenamento do Data Lake com a organiza??o do Data Warehouse, adicionando governan?a e metadados, unificando análises e opera??es escaláveis.

With a flurry of open source technologies we have the freedom of building highly resilient, scalable and available analytics work benches with careful selection of components.Talking about open source I have seen Airflow Pub sub/Kafka/ able to support any highly complex DE needs. Kylin, Druid, Pinot supporting OLAP needs. Superset, Plotly supporting any Analytics needs