CompTIA Data+

Flat JSON structure vs. nested JSON structure

A flat JSON structure stores records in a single array making filtering and aggregation easier. A nested structure organizes records inside other objects or arrays and may require additional navigation

Why is a flat JSON array useful for analysis?

Records can be filtered and aggregated directly without repeatedly navigating nested objects

What is a nested JSON structure?

A structure where objects or arrays contain other objects or arrays

What is public data?

Data that laws or regulations require organizations to make accessible to the public

What is publicly available data?

Data an organization voluntarily makes available to the public without being legally required to do so

What is sample data?

A subset of a larger population used for analysis or testing

What is aggregated data?

Data that has been summarized using counts sums averages or other calculations

Public data vs. publicly available data

Public data is legally required to be shared. Publicly available data is voluntarily shared by an organization

Sample data vs. aggregated data

Sample data is a subset of a population. Aggregated data is summarized data

What is synthetic data?

Artificially generated data designed to resemble real-world data

What is OLTP?

Online Transaction Processing. It handles frequent real-time transactions such as purchases and account updates

What is OLAP?

Online Analytical Processing. It supports complex analysis of large datasets without disrupting transactional systems

OLTP vs. OLAP

OLTP handles operational transactions. OLAP supports analytical queries and reporting

What is a relational database?

A database that stores data in tables consisting of rows and columns

What is a non-relational database?

A database that stores data using structures other than traditional relational tables such as documents key-value pairs or graphs

What is a primary key?

A field or combination of fields that uniquely identifies each record in a table

What is a foreign key?

A field that references a key in another table to establish a relationship

What is referential integrity?

A rule ensuring relationships between tables remain valid and references point to existing records

What is normalization?

Organizing database tables to reduce data duplication and improve data integrity

What is denormalization?

Combining or duplicating data to improve read performance or simplify queries

What is a fact table?

A table containing measurable business events or metrics such as sales quantities and revenue

What is a dimension table?

A table containing descriptive attributes used to provide context for facts such as customer product or date

What is a star schema?

A data warehouse design with a central fact table connected to dimension tables

What is a Boolean data type?

A data type that represents True or False values often stored as 1 or 0

When should a Boolean field be used?

When storing binary responses such as Yes or No or True or False

What are alphanumeric characters?

Characters consisting of letters and numbers commonly used in text strings

What is a BLOB?

Binary Large Object. It stores binary content such as images audio video and files

What is a CLOB?

Character Large Object. It stores large amounts of text

BLOB vs. CLOB

BLOB stores binary data. CLOB stores character-based text data

What is a CSV file?

A comma-separated values file that stores tabular data as text

What is a JSON file?

JavaScript Object Notation. A structured data format that stores information using key-value pairs and arrays

What is a key-value pair?

A data structure containing a key that identifies a value

What does a pandas library do?

It provides Python tools for manipulating cleaning analyzing and organizing structured data

What is R commonly used for in data analytics?

Statistical computing and data analysis

What is SQL used for?

Querying managing and manipulating data in relational databases

What is RPA?

Robotic Process Automation. Software that automates repetitive rule-based tasks

How can RPA improve reporting?

It can extract data generate reports and distribute them automatically on a schedule

What is ETL?

Extract Transform Load. A process that moves data from source systems into a destination such as a data warehouse

What happens during extraction?

Data is accessed and collected from source systems so it can be transformed and loaded

What happens during transformation?

Data is cleaned standardized converted or otherwise prepared for its destination

What happens during loading?

Prepared data is inserted into the target system or data warehouse

ETL vs. ELT

ETL transforms data before loading it. ELT loads data first and transforms it within the destination system

What is data profiling?

Examining data to understand its structure quality completeness and characteristics

What should an analyst examine during data profiling?

The data source record count structure completeness patterns and quality issues

Why is record count important during data profiling?

It helps identify missing records unexpected changes and completeness problems

What is data cleansing?

Identifying and correcting inaccurate incomplete inconsistent or irrelevant data

What does CLEAN() do in Excel?

Removes nonprintable characters from text

What does TRIM() do in Excel?

Removes extra spaces from text while retaining single spaces between words

What is RegEx?

Regular expressions are patterns used to search match or replace text

When should RegEx be used during data cleaning?

To identify and remove unwanted patterns such as HTML tags or inconsistent text characters

What does LOWER() do in Excel?

Converts text to lowercase

What does UPPER() do in Excel?

Converts text to uppercase

What does LEN() do in Excel?

Returns the number of characters in a text string

How can IF() or Replace standardize inconsistent text?

They can conditionally identify variations and replace them with consistent values

Why does GROUP BY not clean inconsistent text?

GROUP BY groups existing values but does not correct spelling capitalization or extra spaces

What is data imputation?

Replacing missing values with estimated or substituted values

What is a query parameter?

A value supplied to a query to filter results based on user-selected criteria

Why should filtering occur at the query level?

It reduces the amount of data retrieved and improves report performance

What is a temporary table?

A temporary database table used to store intermediate query results during a session or process

What is an index in a database?

A data structure that helps the database locate records faster

What is query optimization?

Improving query performance through techniques such as indexing filtering parameters and efficient joins

Why should historical data not be deleted just to improve query speed?

Historical data may be important for analysis and retention. Query optimization should be considered first

What does SELECT do in SQL?

Retrieves specified columns or expressions from a database

What does SELECT * mean?

Returns all columns from the selected table

What does WHERE do in SQL?

Filters individual rows before grouping or aggregation

What does GROUP BY do in SQL?

Groups rows that share the same values so aggregate calculations can be performed

What does HAVING do in SQL?

Filters grouped results based on aggregate conditions

WHERE vs. HAVING

WHERE filters rows before grouping. HAVING filters groups after GROUP BY

Can AVG() be used in a WHERE clause to filter grouped results?

No. Use HAVING to filter results based on aggregate calculations

What does ORDER BY do in SQL?

Sorts query results in ascending or descending order

What does ASC mean in SQL?

Sorts values in ascending order

What does DESC mean in SQL?

Sorts values in descending order

What does DELETE do in SQL?

Removes selected rows from a table

What does DELETE with a WHERE clause do?

Removes only rows that match the specified condition

What happens when DELETE is used without WHERE?

All rows in the table are deleted while the table structure remains

What does TRUNCATE do?

Removes all rows from a table while retaining the table structure

DELETE vs. TRUNCATE

DELETE can remove selected rows using WHERE. TRUNCATE removes all rows

What does an INNER JOIN return?

Only rows with matching values in both tables

What does a LEFT OUTER JOIN return?

Every row from the left table and matching rows from the right table. Unmatched right-side values are NULL

What does a RIGHT OUTER JOIN return?

Every row from the right table and matching rows from the left table. Unmatched left-side values are NULL

What does a FULL OUTER JOIN return?

All rows from both tables including unmatched rows with NULL values where no match exists

What does a CROSS JOIN return?

Every possible combination of rows from two tables. This is called a Cartesian product

What does UNION do in SQL?

Combines the results of queries and removes duplicate rows by default

UNION vs. UNION ALL

UNION removes duplicates. UNION ALL retains duplicates

What is a slowly changing dimension?

A data warehouse technique for managing changes to dimension attributes over time

What is Slowly Changing Dimension Type 1?

Overwrites the existing value and does not preserve historical values

What is Slowly Changing Dimension Type 2?

Creates a new row to preserve the historical record of a changed value

What is Slowly Changing Dimension Type 3?

Adds a column to retain a previous value alongside the current value

SCD Type 1 vs. Type 2

Type 1 overwrites history. Type 2 preserves history by adding a new row

SCD Type 2 vs. Type 3

Type 2 adds a new row for each change. Type 3 adds a column for a previous value

What is the mean?

The arithmetic average calculated by adding values and dividing by the number of values

What is the median?

The middle value in an ordered dataset

What is the mode?

The value that occurs most frequently in a dataset

Which measure of central tendency is most affected by outliers?

The mean

Which measure of central tendency is most resistant to outliers?

The median

Mean vs. median

Mean is the arithmetic average. Median is the middle value and is less affected by extreme values

What are minimum and maximum values?

The smallest and largest values in a dataset. They are measures of spread or range rather than central tendency

What is range?

The difference between the maximum and minimum values

What is standard deviation?

A measure of how spread out values are from the mean

What is variance?

A measure of the average squared deviation from the mean