Azure data Fundamentals: 1. Explore core data concepts
The raw data might not be in a format that is suitable for querying. The data might contain anomalies that should be filtered out, or it may require transforming in some way. The process of reformatting, cleaning, standardizing, removal of bad information, creating new features, etc. is called
Data Transformation/Data Processing
what is data?
Data is a collection of facts such as numbers, descriptions, and observations used in decision making
______________ is simply the conversion of raw data to meaningful information through a process
Data processing
_____________ guarantees that once a transaction has been committed, it will remain committed even if there's a system failure such as a power outage or crash.
Durability
azure data processing tools
Functions; Cognitive Services; Databricks; Other tools
What is an analytical system?
In contrast to systems designed to support OLTP, an analytical system is designed to support business users who need to query data and gain a big picture view of the information held in a database. Analytical systems are concerned with capturing raw data, and using it to generate insights
__________________ ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially.
Isolation
In those key-value arrays, a _______ serves as a unique identifier to retrieve a specific value. Those values can be anything from a number or a string to a complex object, like a JSON file.
Key
is the time taken for the data to be received and processed.
Latency
_____________databases enable you to store data in a format that more closely matches the original structure
Non-relational
what applications require a graph database?
OLTP apps with highly correlated data; apps that require easy updates to single or many objects; apps with a flexible data modelling; apps with data that has requirements that evolve; apps with data that has hierarchical data structures
After your service is provisioned, the service needs to be configured so that users can be given access to the data. You can typically define several levels of access:
Read-only; Read/Write; Owner privilege (full access)
_________________ provides a graphical interface, enabling you to query data, perform general database administration tasks, and generate scripts for automating database maintenance and support operations.
SQL Server Management Studio
_________________ data is information that doesn't reside in a relational database but still has some structure to it
Semi-structured
ORC is
The Optimized Row Columnar (ORC)
Performance: Batch vs. Streaming
The latency for batch processing is typically a few hours. Stream processing typically occurs immediately, with latency in the order of seconds or milliseconds.
___________ the data can often be useful as a tool for examining data. You can generate charts such as bar charts, line charts, plot results on geographical maps, pie charts, or illustrate how data changes over time
Visualizing
Blob stands for
Binary Large Object
The process of capturing the raw data
Data ingestion
what is unstructured data?
Does not naturally contain fields
How is data in a relational table organized?
Rows and columns
______________ is typically tabular data that is represented by rows and columns in a database
Structured data
in a normalized database schema, no data
duplication exists
diagnostic analytics is used to
figure out why something is happening. Take the findings of descriptive analytics. 1. identify anomalies 2. collect data related to anomalies 3. use analytics to explain the anomalies
descriptive data analytics examples
generating reports to provide and view sales data
A ___________ contains nodes (information about objects), and edges (information about the relationships between objects).
graph
You can use a _____________database to store and query information about complex relationships
graph
_____________ stores entities centric around relationships. Enables applications to perform queries by traversing a network of nodes and edges
graph database
cosmos db supports graph databases through the _______ api
gremlin
Transactional systems are often
high-volume, sometimes handling many millions of transactions in a single day.
OLTP stands for
online transaction processing
views are created to
simplify the query
parquet is developed for
storing nested data types efficiently
Analysis: Batch vs. Streaming
You typically use batch processing for performing complex analytics. Stream processing is used for simple response functions, aggregates, or calculations such as rolling averages.
what is Power BI
a collection of software, services, aps, and connectors
a view is
a virtual table based on the results set of a query
Data processing solutions often fall into one of two broad categories:
analytical systems, and transaction processing systems.
In ____________ processing, newly arriving data elements are collected into a group. The whole group is then processed at a future time as a ____________
batch ; batch
Buffering and processing the data in groups is called _____________
batch processing.
non-relational collections are often defined
by labeling each field with the name it represents
data processing
cleans the data and converts it into a more meaningful format for exploration
each _______________ in a table is defined by a datatype
column
_____________ database can appear very similar to a relational database
column family
apache cassandra is a
column family database
Each row in a table has the same set of
columns
An example of an effective use of batch processing would be a
connection to a mainframe system. Vast amounts of data need to be transferred into a data analysis system and the data is not real-time.
data ingestion can be imported by
continuous stream or in batches
____________ is stored in a table
data
steps in data analytics
data ingestion (raw data) --> data processing (clean/transform data) --> data exploration (queries, visualizations)
Most analytical data processing systems need to perform similar tasks:
data ingestion, data transformation, data querying, and data visualization
Differences between batch and streaming data include:
data scope; data size; performance; analysis
what is semi-structured data?
data structure is defined within the actual data by fields.
In data analytics, different data sources are typically brought together in a
data warehouse so you can answer questions with the data
A column family is a
database object that contains columns of related data. It is a tuple (pair) that consists of a key-value pair, where the key is mapped to a value that is a set of columns. In analogy with relational databases, a column family is as a "table", each key-value pair being a "row". Each column is a tuple (triplet) consisting of a column name, a value, and a timestamp
you can often categorize your data analytics as
descriptive; diagnostic; predictive; prescriptive; cognitive
A ____________database is a database in which data is stored across different physical locations. It may be held in multiple computers located in the same physical location (for example, a datacenter), or may be dispersed over a network of interconnected computers
distributed
azure cosmos db is an example of a
document-based database
One disadvantage of a document database is
duplication due to information being stored by document. For example, if two customer documents have the same address, the address would need to be stored in both documents. Duplication increases storage required and also makes maintenance more complex.
information about the relationship between objects in a graph database
edge
unstructured data is often used to
extract data form and categorize or identify "structures"
ELT
extract, load, transform
ETL stands for
extract, transform, load
data analytics help you
identify strengths and weaknesses in your organization
cognitive analytics
inspired by how the human brain works. self-learning feedback loop
the aim of the data processing
is to convert the data into meaningful structures
json
javascript object notation
in a normalized database schema, data is retrieved by
joining tables together in a query
in a relational database, data is retrieved by
joining tables together in a query
example of semi-structured data
json
types of non-relational data
json, avro, orc, parquet
A ___________ database stores Associative arrays
key-value
4 examples of NoSQL categories
key-value stores, document based, column family databases, graph databases
Other types of semi-structured data
key-value stores; graph databases
examples of Power BI visualizations
line graph, bar chart; matrix; tree map; scatter plot;
What is NoSQL?
loose term, to describe non-relational databases
While normalization enables fast throughput for transactions, it can make querying
more complex
non-relational collections can have
multiple entities in the same collection or container with different fields; ;
Streaming data processing is beneficial in most scenarios where
new, dynamic data is generated on a continual basis.
information about objects in a graph database
nodes
non-relational collections can have a different
non-tabular schema
A document database is a type of
nonrelational database that is designed to store and query data as JSON-like documents
Typically, the end result of the _____________ process is that your data is split into a large number of narrow, well-defined tables (a narrow table is a table with few columns), with references from one table to another, as shown in the image below.
normalization
typically when we design a relational database, we need to _____________ the data
normalize
Splitting tables out into separate groups of columns to support fast processing is called
normalized.
In __________________, information systems typically facilitate and manage transaction-oriented applications
online transaction processing
Parquet is an
open source file format available to any project in the Hadoop ecosystem
predictive analytics used to
predict what will happen in the future
data ingestion
processing of obtaining and importing data for immediate or later use
The act of setting up the database server is called
provisioning.
an index optimizes
queries for faster data retrieval;
After data is ingested and transformed, you can ________ the data to analyze it
query
the focus of a key-value store is to
read/write data quickly
Streaming handles data in __________
real time
data is normalized to
reduce
Databases that hold tables in the form of structured data are called
relational databases
Structured data is typically stored in a ____________database such as _________ or _____________
relational; SQL Server; Azure SQL Database
avro is a
row-based format
Avro is a
row-oriented remote procedure call and data serialization framework developed within Apache's Hadoop project
all _____________ in a table have the same # of columns
rows
benefit of relational database
simple structure; easier to understand; can remove duplication with normalization
In _______________, each new piece of data is processed when it arrives. For example, data ingestion.
stream processing
Processing data as it arrives is called ______________
streaming
characteristics of a relational database
structure of tables and columns, rigid structure, tables are related to each other with primary and foreign keys
A key-value database stores data as a single collection without
structure or relation
3 classifications of data
structured, semi-structured, or unstructured.
a _______________ is where data is stored in a relational database
table
____________ consists of rows and columns
tables
When compared to non-distributed database systems, any data update to a distributed database will
take time to apply across multiple locations.
frequent uses of unstructured data in ML
text analytics, sentiment analysis, vision api
an index reduces
the amount of data pages that need to be read to retrieve the data in a SQL statement;
data processing generally occurs after
the data has been ingested
to query a key-value store you need to know
the keys you want to look up the values for
in a column family database
there are groups of columns that are logically grouped together
Stream processing is ideal for
time-critical operations that require an instant real-time response.
A ______________ is a sequence of operations that are atomic. This means that either all operations in the sequence must be completed successfully, or if something goes wrong, all operations run so far in the sequence must be undone
transaction
A primary use of relational databases is to handle
transaction processing.
An example of ineffective batch-processing would be to
transfer small amounts of real-time data, such as a financial stock-ticker.
Depending on the __________ such as structured, semi-structured, or unstructured, data will be stored _______________.
type of data; differently
In a non-relational database, each key should have a
unique id
Not all data is structured or even semi-structured. For example, audio and video files, and binary data files might not have a specific structure. This is called
unstructured data
You can categorize data in many different ways, depending not only on how it's structured, but also on how the data is __________
used
prescriptive analytics
used to answer questions about what actions need to be taken to achieve certain targets. rely on machine learning to figure out patterns in past experience
in a normalized database schema primary keys and foreign keys are
used to defined relationships
examples of unstructured data
video, audio, media streams, documents
Relational and non-relational databases are suited to different
workloads.
Which one of the following tasks is a role of a database administrator? ( 1. Backing up and restoring databases 2. Creating dashboards and reports 3. Identifying data quality issues )
1. Backing up and restoring databases
difference between etl and elt
1. ETL loads data first into the staging server and then into the target system whereas ELT loads data directly into the target system. 2. ETL model is used for on-premises, relational and structured data while ELT is used for scalable cloud structured and unstructured data sources. 3. ETL is mainly used for a small amount of data whereas ELT is used for large amounts of data.
Advantages of batch processing include:
1. Large volumes of data can be processed at a convenient time.; 2. It can be scheduled to run at a time when computers or systems might otherwise be idle, such as overnight, or during off-peak hours.
Which one of the following roles is not a data job role? ( 1. Systems Administrator 2. Data Analyst 3. Database Administrator )
1. Systems Administrator
Disadvantages of batch processing include:
1. The time delay between ingesting the data and getting the results. 2. All of a batch job's input data must be ready before a batch can be processed. This means data must be carefully checked. Problems with data, errors, and program crashes that occur during batch jobs bring the whole process to a halt.
Which of the following is an example of unstructured data? (1. An Employee table with columns Employee ID, Employee Name, and Employee Designation 2. Audio and Video files 3. A table within SQL Server database )
2. Audio and Video files
Which of the following tools is a visualization and reporting tool? ( 1. SQL Server Management Studio 2. Power BI 3. SQL )
2. Power BI
Examples of streaming data include:
A financial institution tracks changes in the stock market in real time, computes value-at-risk, and automatically rebalances portfolios based on stock price movements.
What is a transactional system?
A transactional system is often what most people consider the primary function of business computing. A transactional system records transactions
A transactional database must adhere to the ______________ properties to ensure that the database remains consistent while processing transactions.
ACID (Atomicity, Consistency, Isolation, Durability)
_______________ are typically read-only systems that store vast volumes of historical data or business metrics, such as sales performance and inventory levels. _______________ are used for data analysis and decision making.
Analytical workloads
______________ are generated by aggregating the facts presented by the raw data into summaries, trends, and other kinds of "Business information."
Analytics
______________ guarantees that each transaction is treated as a single unit, which either succeeds completely, or fails completely
Atomicity
You can provision other services as well in Azure. For example, if you want to store unstructured data such as video or audio files, you can use
Azure Blob storage
If you want to store semi-structured data such as documents, you can use a service such as
Azure Cosmos DB.
_______________ currently provides connections to on-premises SQL Server databases, Azure SQL Database, PostgreSQL, Azure SQL Data Warehouse, and SQL Server Big Data Clusters, amongst others.
Azure Data Studio
_______________ provides a graphical user interface for managing many different database systems.
Azure Data Studio
____________ Database is a service that runs in the cloud. You can use it to create and access relational tables
Azure SQL
____________ is managed and run by Azure, you just specify that you want a database server to be created.
Azure SQL Database service
________________ provides database services in Azure. It's similar to SQL Server, except that it runs in the cloud. You can manage _____________ using Azure portal.
Azure SQL database; Azure SQL database
Data scope: Batch vs. Streaming
Batch processing can process all the data in the dataset. Stream processing typically only has access to the most recent data received, or within a rolling time window (the last 30 seconds, for example).
Data size: Batch vs. Streaming
Batch processing is suitable for handling large datasets efficiently. Stream processing is intended for individual records or micro batches consisting of few records.
__________ ensures that a transaction can only take the data in the database from one valid state to another. A ___________ database should never lose or create data in a manner that can't be accounted for.
Consistency; consistent
