Skip to main content
What is Database Analytics What are the main types of data systems used in analytics? How do you implement database analytics on AWS? How can AWS support your database analytics needs?

What is Database Analytics

Data underscores decision-making within businesses, hence requiring careful management, handling, and analysis. Improper data operations, even by the most skilled data analysts, can lead to incorrect assumptions and misguided decisions.

A mature data analysis pipeline enables organizations to accurately identify trends, perform descriptive analytics, prescriptive analytics, and statistical analysis, as well as introduce machine learning and AI capabilities.

Choosing a database analytics system depends on your existing data, current database formats, and other types of analysis required. Data is stored in businesses in various formats, including relational databases, nonrelational databases, and other file formats. Relational and nonrelational databases have in-built support for basic analytics, but these alone are not enough to derive deeper insights across business functions and sources.

Data analysts require data warehouses, data lakes, and lakehouses for data integration from disparate sources, making them ready for cross-format and cross-functional data mining and analytics.

We explore all these different technologies within the database analytics space in the rest of the guide.

What are the main types of data systems used in analytics?

Here is a brief overview of the different types of systems available for use in analytics

Relational databases

Relational databases are collections of structured data arranged into tables with rows and columns. Each table contains a collection of related data that represents real-world objects or concepts.

Each row in a table represents a single record, such as a customer’s details, including name, phone number, and address. Each table can be related to one or more other tables. For example, a customer table can be related to a purchase table, allowing each purchase to be linked to a specific customer.

All relational database management systems have a fixed schema, as described above, and support Structured Query Language (SQL) for data querying across and within tables.

Examples of relational database services on AWS include Amazon Relational Database Service and Amazon Aurora, a high-performance, globally scalable relational database solution for PostgreSQL, MySQL, and DSQL.

Non-relational databases

Non-relational databases have a flexible schema and are also known as NoSQL databases, as they do not support queries via SQL. The different types of non-relational databases include: key-value databases, document databases, wide-column databases, graph databases, in-memory databases, and search databases.

Each type of NoSQL database is suited for a specific use case. For example, a document database is a suitable fit for an internal Content Management System, and a wide-column store is well-suited for time-series data from an IoT fleet.

Some examples of non-relational database services on AWS are given below.

  • Amazon DynamoDB is a serverless, NoSQL, fully managed database with single-digit millisecond performance, suitable for key-value databases and document stores.
  • Amazon DocumentDB (with MongoDB compatibility) is a fully managed native JSON document database service.
  • Amazon Keyspaces (for Apache Cassandra) is a scalable, highly available, and managed service for Apache Cassandra–compatible wide-column databases.
  • Amazon Neptune is a high-performance, serverless graph database service that delivers superior analytics, scalability, and availability.
  • Amazon ElastiCache is a fully managed, in-memory caching service compatible with Valkey, Redis, and Memcached in-memory databases.
  • Amazon MemoryDB is a Valkey- and Redis OSS-compatible, durable, in-memory database service for ultra-fast performance.

Data warehouse

A data warehouse is an analytics solution that extends the capabilities of relational databases on a massive scale, supporting SQL querying. Data warehouses are used for storing and analyzing relational data across a large number of databases. A warehouse solution can transform non-relational data during the Extract, Transform, Load (ETL) process, normalizing it so that it’s ready for analytics.

Amazon Redshift is a managed data warehouse solution that helps you store data and scale data analytics workloads effortlessly.

Data lake

A data lake is a centralized repository that allows you to store all your structured and unstructured data at any scale. Transformation of data can occur before or after it is transferred to the data lake. A data lake requires additional services for ETL and analysis; analyzing raw data is typically not an option.

Amazon S3 is an object data storage designed to retrieve any amount of data from anywhere, which can serve as a data lake. S3 can be combined with AWS Lake Formation for data access permission and sharing of stored data

Data lakehouse

A data lakehouse is a combination of a data warehouse and a data lake. A data lakehouse can store structured and unstructured data, provides a format layer to add schema and structure, and includes a querying engine. A data lakehouse is a necessary layer in modern enterprise data analytics due to its ability to execute queries across all data simultaneously.

Amazon SageMaker Lakehouse unifies data across Amazon S3 data lakes and Amazon Redshift analytical database warehouses. Amazon Sagemaker Lakehouse offers the flexibility to access and query your data in-place with all Apache Iceberg–compatible tools and engines.

Other types

In analytics across the enterprise, various data types may not fit neatly into the relational or nonrelational database model, such as raw files and tables. This means they are stored in different formats. For example, semi-structured streaming data can be stored in Apache Avro files, and Amazon S3 can be used to store any type of data.

When selecting a data analytics system, you will likely require the ability to analyze these file types in conjunction with your databases.

How do you implement database analytics on AWS?

Different databases, data types, and database storage and management systems each handle data analytics in unique ways. Performing analytics on data warehouses, data lakes, and lakehouses requires different strategies and technologies.

Ensure foundational data governance from the start by using Amazon DataZone to catalog, discover, share, and govern data stored across AWS, on-premises, and third-party sources.

Amazon Managed Workflows for Apache Airflow (MWAA) can help orchestrate the data analytics process through data transfer and transformation as a pipeline automation tool, and also trigger analytics workflows on your warehouse, lake, or lakehouse.

Step 1 — Centralize data from diverse sources to a larger system

There are various ways to transfer your data from current sources to data warehouses, data lakes, and data lakehouses. Data may need to be transformed and cleansed before storage. There may be other considerations, such as sensitive customer data types, access permissions, and in-place access for some data.

The easiest way to transfer data in preparation for an AWS warehouse, lake, or lakehouse configuration is to first move the data to S3.

Streaming data may require new services such as Amazon Data Firehose for real-time streaming data delivery or Amazon Kinesis Data Streams for intake and aggregation.

Step 2 — Transform and normalize data

To analyze data, some data will require transformation and normalization.

AWS Glue discovers and connects to more than 100 diverse data sources, manages your data in a centralized data catalog, and visually creates, runs, and monitors data pipelines to load data into your data lakes, warehouses, and lakehouses. AWS Glue DataBrew is a visual data preparation tool that makes it easier for data analysts and data scientists to clean and normalize data.

Amazon EMR features performance-optimized runtimes for Apache Spark, Trino, Apache Flink, and Hive big data analytics, simplifying data lake workflows and processing times.

Amazon SageMaker Data Wrangler is the fastest and easiest way to prepare data for machine learning.

Step 3 — Combined data analysis

Once your data is stored, connected, and transformed, data analysts leverage your warehouse, lake, or lakehouse to perform analysis. There are multiple data analytics techniques depending on your use case.

Querying

Amazon Redshift has built-in querying capabilities for your data warehouse. Amazon Athena helps you analyze and query unstructured, semi-structured, and structured data stored in Amazon S3 data lakes. It is optimized for performing real-time data analysis and exploration, which allows users to interactively query and visualize data. Amazon SageMaker Lakehouse also offers built-in querying capabilities.

Business intelligence

Amazon QuickSight provides unified business intelligence (BI) data analytics at scale, spanning data warehouses, data lakes, and lakehouses. Data visualization is a key service in Amazon QuickSight.

Machine learning

Amazon Redshift ML can be used for machine learning analytics on Redshift warehouses. Amazon SageMaker offers machine learning and other analytics capabilities on data lakes and lakehouses.

On Amazon SageMaker Lakehouse

In SageMaker Lakehouse, you access and query your data in-place with all Apache Iceberg–compatible tools on a single copy of data. You can leverage the use of analytics tools and engines of your choice, such as SQL, Apache Spark, business intelligence (BI), and AI/ML tools, and collaborate with data stored across Amazon S3 data lakes and Amazon Redshift warehouses.

Streaming data

Amazon Kinesis can collect, process, and analyze real-time video and data streams in a secure and scalable manner.

How can AWS support your database analytics needs?

Analyzing databases requires far more than just SQL queries in modern enterprise environments. By leveraging data warehouses, data lakes, and lakehouses, data analysts can unlock the value of data, performing data analytics across various sources, types, and functions.

The right database analysis architecture helps ensure your solution is scalable, ready to run, and integrable with now-essential ML services and predictive analytics. Get started by creating a free account on AWS today.