Overview
- Explore Core Data Concepts
  - Explore Core Data Concepts
    - Data formats
    - File formats
      - Avro
      - ORC (Optimized Row Columnar)
      - Parquet
    - Database details
      - Relational databases
      - Non-relational databases
    - Transactional data processing
      - Atomicity
      - Consistency
      - Isolation
      - Durability
    - Analytical data processing
  - Explore Data Roles and Services
    - Explore Job Roles in the World of Data
      - Database Administrator
      - Data Engineer
      - Data Analyst
      - Others
    - Identify Data Services
      - Azure SQL
      - Azure Database
      - Azure Cosmos DB
      - Azure Storage
      - Azure Data Factory
      - Azure Synapse Analytics
      - Azure Databricks
      - Azure HDInsight
      - Azure Stream Analytics
      - Azure Data Explorer
      - Azure Purview
      - Microsoft Power BI
- Microsoft Azure Data Fundamentals: Explore Relational Data in Azure
  - Explore Fundamental Relational Data Concepts
    - Understand Relational Data
    - Understand Normalization
    - Explore SQL
      - Common SQL Languages
      - Types of SQL Statements
        
        DDL Statements
        
        DCL Statements
        
        DML Statements
      - Describe Database Objects
        
        Views
        
        Stored Procedures
        
        Indexes

Overview

Self-studying Mastering Azure Basics: Data Fundamentals on Microsoft Learn
This course consists of four learning paths. Notes below cover the first two:
- Explore Core Data Concepts
- Explore Relational Data in Azure
- Explore Non-Relational Data in Azure
- Explore Modern Data Warehouse Analytics in Azure

Explore Core Data Concepts

Data Formats

Structured data: tables, etc.
Semi-structured data: JSON
Unstructured data: images, videos

File Formats

Delimited text files (CSV, etc.)
JSON
XML
BLOB (images, video, etc.)
Avro
ORC
Parquet

Avro

Row-based. Created by Apache. Each record includes a header that describes the structure of the data in that record. Headers are stored as JSON. Data is stored as binary. Applications use header information to parse the binary data and extract stored fields. Data can be compressed to minimize storage and network bandwidth usage.

ORC (Optimized Row Columnar)

Column-based. Developed by HortonWorks. Optimizes read and write operations in Apache Hive. ORC files contain "stripes" of data. Each stripe holds the data for one column or a set of columns.

Parquet

Column-based. Created by Cloudera and Twitter. Parquet files contain row groups. Data for each column is stored together in the same row group.

Database Details

Relational Databases

Entity: a table in a relational database
Instance: each row in a table; also called a record
Primary Key: identifier for an instance
Foreign Key (Freign Key): reference to a related table

Non-Relational Databases

Also called NoSQL databases.

Key-Value databases
Document databases: key-value databases where the value is a document
Column-family databases: columns are organized into groups called column families
Graph databases: store entities as nodes and links that define the relationships between them

Transactional Data Processing

Example of transactional data: transferring money between bank accounts
Requires immediate access to data being processed
Called Online Transaction Processing (OLTP)
Must support ACID semantics (the four fundamental properties of transactions)

Atomicity

Each transaction is treated as a single unit — it must either succeed completely or fail completely. For example, a transaction that debits one bank account and credits another must complete both actions. If either action cannot be completed, the other must also fail.

Consistency

A transaction can only take the database from one valid state to another. Continuing the debit/credit example, the completed state of the transaction must reflect the transfer of funds from one account to the other.

Isolation

Concurrent transactions must not interfere with each other and must result in a consistent database state. For example, while a fund transfer transaction is in progress, another transaction checking account balances must return consistent results — it cannot see one account's balance after the transfer and the other account's balance before the transfer.

Durability

Once a transaction is committed, it remains committed. After a fund transfer transaction completes, the updated account balances are persisted — even if the database system is shut down, the committed transaction will be reflected when it comes back online.

Analytical Data Processing

Common architecture:
- Data lake/OLTP database → ETL → Data warehouse → OLAP model → Query execution
  - Data lake: stores both structured and unstructured data in raw form
  - Data warehouse: stores structured data optimized for reading
  - OLAP model: data storage for analytical workloads; supports drill-up and drill-down operations

Explore Data Roles and Services

Explore Job Roles in the World of Data

Database Administrator

Assign permissions to users
Backup and restore data

Data Engineer

Data integration
Data cleaning
Define data governance rules
Implement pipelines to move and transform data

Data Analyst

Explore and analyze data to create visualizations and charts
Enable organizations to make informed decisions

Others

Data Scientist
Data Architect
Application Developer
Software Engineer

Identify Data Services

Azure SQL

Azure SQL Database: fully managed PaaS database
Azure SQL Managed Instance: instance hosting a SQL Server, with more flexible configuration than Azure SQL DB
Azure SQL VM: virtual machine with SQL Server installed; most flexible configuration

Azure Database

For open-source relational databases:

Azure Database for MySQL
Azure Database for MariaDB
Azure Database for PostgreSQL

Azure Cosmos DB

Non-relational (NoSQL) database
Stores data as JSON documents, key-value pairs, column families, and graphs

Azure Storage

Can store multiple types of data and host a data lake.

Blob Containers: scalable, cost-effective storage for binary files
File Shares: network file shares like those on corporate networks
Tables: key-value storage for applications that need fast read/write of data values

Azure Data Factory

Data pipelines to move and transform data
Used to build ETL solutions
ETL stands for Extract, Transform, Load

Azure Synapse Analytics

An integrated data analytics solution that provides a single service interface for:

Pipelines
SQL
Apache Spark
Azure Synapse Data Explorer: real-time queries on log and telemetry data using Kusto Query Language (KQL)

Azure Databricks

Integration of the common Databricks platform into Azure
Combines the Apache Spark data processing platform with SQL database semantics and integrated management interface for large-scale data analytics

Azure HDInsight

Provides clusters hosting Apache big data processing technologies:

Apache Spark
Apache Hadoop
Apache HBase
Apache Kafka
Apache Storm

Azure Stream Analytics

Real-time stream processing engine
Ingests data streams, extracts data with queries, and writes query results to output
Ingests streaming data for real-time visualization

Azure Data Explorer

Standalone service for querying log and telemetry data

Azure Purview

Enterprise-scale data governance
Creates a data map and tracks data lineage across multiple data sources and systems
Ensures data integrity

Microsoft Power BI

Platform for analytical data modeling and reporting
Interactive data visualization
Publish and distribute via web-based reports and apps in the Power BI service, and the Power BI mobile app

Microsoft Azure Data Fundamentals: Explore Relational Data in Azure

Explore Fundamental Relational Data Concepts

Understand Relational Data

Entity = Table
Instance = Row in a table
Each column stores a specific data type. Example: the Quantity column in an Order table is restricted to integer values

Understand Normalization

Normalization is a term used by database professionals
Describes a schema design process that minimizes data duplication and enforces data integrity
Normalization can be achieved with the following rules:
- Separate each "entity" into its own table.
- Separate individual "attributes" into their own columns.
- Use a "primary key" to uniquely identify each entity instance (row).
- Use "foreign key" columns to link related entities.

Explore SQL

SQL stands for "Structured Query Language"
Used to communicate with relational databases
Use SQL statements to perform tasks such as updating data in a database or retrieving data from a database
Common relational database management systems using SQL include Microsoft SQL Server, MySQL, PostgreSQL, MariaDB, and Oracle

Common SQL Languages

Transact-SQL (T-SQL): This version of SQL is used by Microsoft SQL Server and Azure SQL services.
pgSQL: This language includes extensions implemented in PostgreSQL.
PL/SQL: The language used by Oracle. PL/SQL stands for Procedural Language/SQL.

Types of SQL Statements

DDL Statements

Create, modify, and delete tables and other objects in a database:

CREATE
ALTER
DROP
RENAME

CREATE TABLE Product
(
    ID INT PRIMARY KEY,
    Name VARCHAR(20) NOT NULL,
    Price DECIMAL NULL
);

DCL Statements

Manage access to objects in a database by granting, denying, or revoking permissions to users or groups:

GRANT
DENY
REVOKE: removes a previously granted permission

GRANT SELECT, INSERT, UPDATE
ON Product
TO user1;

DML Statements

Manipulate rows in tables: retrieve (query), insert new rows, modify existing rows, or delete rows no longer needed.

SELECT
INSERT
UPDATE
DELETE

SELECT FirstName, LastName, Address, City
FROM Customer
WHERE City = 'Seattle'
ORDER BY LastName;

WHERE: only rows matching the condition are selected, updated, or deleted
ORDER BY: data is sorted by the specified column
JOIN: connects rows in one table with rows in another

SELECT o.OrderNo, o.OrderDate, c.Address, c.City
FROM Order AS o
JOIN Customer AS c
ON o.Customer = c.ID

SET: updates the value of a specific column

UPDATE Customer
SET Address = '123 High St.'
WHERE ID = 1;

DELETE FROM Product
WHERE ID = 162;

INTO: specifies the table and columns
VALUES: specifies the list of values to store in the columns specified by INTO

INSERT INTO Product(ID, Name, Price)
VALUES (99, 'Drill', 4.99);

Describe Database Objects

Views

A virtual table based on the results of a SELECT query

Create a view:

CREATE VIEW Deliveries
AS
SELECT o.OrderNo, o.OrderDate,
       c.FirstName, c.LastName, c.Address, c.City
FROM Order AS o JOIN Customer AS c
ON o.CustomerID = c.ID;

Query the created view:

SELECT OrderNo, OrderDate, LastName, Address
FROM Deliveries
WHERE City = 'Seattle';

Stored Procedures

A program that bundles multiple database operations into a single unit that can be stored alongside data
Can be executed with the same procedure as running a query from outside.

Create a stored procedure:

CREATE PROCEDURE RenameProduct
    @ProductID INT,
    @NewName VARCHAR(20)
AS
UPDATE Product
SET Name = @NewName
WHERE ID = @ProductID;

Execute the created stored procedure:

EXEC RenameProduct 201, 'Spanner';

Indexes

Without an index: walk through every unit in an apartment building and check each nameplate to find the delivery address
With an index: check a floor plan with resident information to locate the delivery address directly
Indexes are most effective on the following types of columns:
- Columns that appear frequently in SQL search conditions or join conditions
- Columns where the data to be retrieved is small relative to the total data in the table
- For B-tree indexes: high-cardinality columns (columns with many distinct values); for bitmap indexes: low-cardinality columns

CREATE INDEX idx_ProductName
ON Product(Name);

products.sint.co.jp