Chapter 4 - Data Quality

Question 1

integrating multiple systems and inappropriate database design are two causes of what? What is meant by inappropriate design?

Answer 1

Data redundancy
Inappropriate design means when transactional databases aren’t in 3rd normal form.

Question 1

What types of data do the below statements describe?

1) Same or similar data elements exist in multiple places
2) Identical copies of the same information exists in multiple places

Answer 1

1) Redundant Data
2) Duplicate Data

Question 2

Can you list the 8 Data Quality challenges?

Answer 2

1) Duplicate Data
2) Redundant Data
3) Invalid Data
4) Data Type Validation
5) Missing Values
6) Specification Mismatch
7) Nonparametric Data
8) Data Outliers

Question 3

what must you watch out for on the data quality challenge around nonparametric data?

Answer 3

if the rank order of the values is significant

Question 4

which data quality issue will get if you don’t ensure validation of inbound data consistently maps to its target data type?

Answer 4

Specification Mismatch

Question 5

What do you need to do to ensure you don’t get specification mismatch?

Answer 5

you need to ensure that inbound data is correctly mapped to its target data type

Question 6

What data manipulation technique is helpful when you have numeric data you want to split into subcategories categorize to facilitate analysis?

Answer 6

Recoding

increasing the ages in an age column by 4 years is also an example of recoding

Question 7

Regarding data manipulation, what technique describes creating a new variable from a calculation on an existing table?

Answer 7

Derived variable

Question 8

Why is it not a good idea to use a column to store derived variables from another column? What should you do instead?

Answer 8

if the nature of the variables change over time this would need constant updates. Instead, derived variables should always be embedded as code so it is calculated only when needed.

the book misses out that storing a data in a column as a derived variable does not auto-update. Hence you get the issue. It is best to embed the calculation in the query itself or code.

Question 9

Which data manipulation technique would you use if you wanted a holistic view of a particular subject?

Answer 9

Data merging

Question 10

What data manipulation technique helps to ensure data is consistent, complete and accurate?

Answer 10

Data Merging

Question 11

What’s the difference between ETL and data blending when it comes to combining data?

Answer 11

ETL combines multiple sources of data into a single data set in a data warehouse database. Whereas, data blending only does this at the reporting layer.

Question 12

If the IT infrastructure was struggling to do ETL effectively, what other technique can you use to combine datasets that has less impact on IT?

Answer 12

Data blending using a data visualization tool

Question 13

A data analyst MUST understand what if they’re to use data blending techniques?

Answer 13

they must understand how data maps across systems

Question 14

if you needed to combine the variables of several columns into a single variable column, what data manipulation technique would you use?

Answer 14

Concatenation

Question 15

a data append combines data from different sources into a single data set, but how does it differ from data merge?

Answer 15

it differs by the fact that the source data structure must be the SAME. I.e. if combining two data sources together, those data sources contain exactly the same data attributes/columns.
Whereas, with data merge, the source data comes from different data sets

Question 16

you have the same data being recorded in different locations and you need to combine them into a single data set, what manipulation technique does this?

Answer 16

Data Append

Question 17

Imputation is a data manipulation technique to deal with what problem? [IMPORTANT]

Answer 17

missing values in a data set.

Question 18

List the 5 data imputation methods for dealing with missing values numeric values

Answer 18

1) Removing rows containing missing values

2) Replace with zero

3) Replace with overall average

4) Replace with Most Frequent (mode)

5) Closest Value average

Question 19

reduction is the process of what?

Answer 19

shrinking a dataset WITHOUT negatively impacting its analytical value

Question 20

Removing attributes to reduce a dataset’s overall size is known as what? Why would this be done?

Answer 20

It’s known as dimensionality reduction. It’s done to make data analysis on big datasets more efficient

Question 21

Histograms are a method of __________ __________

Answer 21

numerosity reduction

this reduces QUANTITATIVE data

Question 22

list the 3 methods to reduce a dataset to make big data analysis more efficient?

Answer 22

numerosity reduction
dimensionality reduction
sampling

Question 23

what manipulation technique summarizes data saving you going through it by searching?

Answer 23

Data aggregation calculations

Question 24

if you wanted to present data but maintain privacy, which manipulation technique allows you to do this?

Answer 24

Data Aggregation allows you to do this by summarizing the data instead.

Question 25

What data manipulation technique re-arranges the data for better visibility and prepares it for use in data visualization tools

Answer 25

Transposition

Question 26

what data manipulation technique helps to reveal patterns hidden in the original format of the data?

Answer 26

Transposition

Question 27

What combination of data manipulation techniques greatly enhances ability to see trends, efficiently calculate dimensions, and can provide a more holistic view?

Answer 27

Transposition and Aggregation

Question 28

pick the right word for the sentences: Use ______ to address composite structural issues Use ______ to address distributed structural issues Words: Parsing / String Manipulation

Answer 28

Use parsing to address composite structural issues Use string manipulation to address distributed structural issues (When would you use string over concatenation?)

Question 29

What stage do you need to perform string manipulation, before or after you start analysis?

Answer 29

before analysis

Question 30

data quality: influencing data quality as early as possible in WHAT part of the analytics life-cycle?

Answer 30

the Acquisition phase.

Question 31

Data quality issues can be introduced at all stages of the data life-cycle, these are places to introduce quality control checks, but where are they?

Answer 31

Data Acquisition phase Data Manipulation Phase: --Data Transformation (ETL) --Data Conversion (ETL) --Data Manipulation (ETL) Data visualization phase: (Final Product Preparation)

Question 32

add more questions about data quality issues

Question 33

You're implementing some automated validation, what do you need understand first?

Answer 33

how source data fields map to their corresponding database columns and specifically, their data-types!

Question 34

what type of validation automation would you implement to control input form errors?

Answer 34

you would use data-type validation automation to ensure the correct data-type is used in the input form.

Question 35

What two examples of automated validation does the author provide?

Answer 35

Data-type validation Validation of number of data points

Question 36

How might you prevent missing data (as in, complete rows of data and not just missing values) from flowing into you analytics environment? e.g. if source day-temp was measured 24 times a day, you should always get 24 rows.

Answer 36

Implement automated validation on the verification of the number of data points expected for a given attribute

Question 37

List the 6-dimensions of data quality

Answer 37

1) Accuracy 2) Completeness 3) Consistency 4) Timeliness 5) Uniqueness 6) Validity

Question 38

What data quality dimension denotes how closely a given attribute matches how you intended to use it?

Answer 38

data accuracy

Question 39

Match the data quality dimension to the questions asked: 1) Accuracy 2) Completeness 3) Consistency 4) Timeliness 5) Uniqueness 6) Validity a) what do I intend to this use this attribute for? b) is my attribute within its expected range? c) is it relevant when I obtain this data? d) how reliable is this data, i.e. is it correct? or the the same across different systems? e) Does my data have the minimum number of attributes in order to carry out the purpose the data is collected for? f) Can we reduce the number of systems this data attribute exists on?

Answer 39

1) Accuracy / a) what do I intend to this use this attribute for? 2) Completeness / e) Does my data have the minimum number of attributes in order to carry out the purpose the data is collected for? 3) Consistency / d) how reliable is this data, i.e. is it the same across different systems? 4) Timeliness / c) is it relevant when I obtain this data? 5) Uniqueness / f) Can we reduce the number of systems this data attribute exists on? 6) Validity / b) is my attribute within its expected range?

Question 40

when is the best time to check the validity of data?

Answer 40

At the moment of creation.

Question 41

Taking the count of rows that pass or fail during an ETL load is one method to measure what?

Answer 41

data conformity

Question 42

the author lists 5 methods to validate quality, list them: ## Footnote these methods are broader than validating data-types, at creation checks or manipulation during ETL

Answer 42

1) Reasonable Expectations 2) Data Profiling 3) Data Audits 4) Sampling 5) Cross-Validation

Question 43

Babs expects roughly 30 million records to be loaded monthly into the DWH. What data validation check could she do to detect issues with the ETL load?

Answer 43

Implement a Reasonable Expectation check by creating an exception report that will alert if the number of records is below a reasonably expected amount.

Question 44

whether the data in your analytic environment looks as it should. A.K.A?

Answer 44

Reasonable Expectations

Question 45

Which data quality validation uses statistical measures to check for discrepancies and is good at identifying irregular patterns within your data?

Answer 45

Data Profiling

Question 46

Results of data profiling can be used as a Reasonable Expectations test, true or false?

Answer 46

TRUE. The results can highlight patterns that can be defined as being within or outside of reasonable expectations

Question 47

which data quality validation method also uses Data Profiling techniques to help validate data integrity, but also helps in identifying security issues?

Answer 47

Data Audits

Question 48

Regular _______ ensures that data quality remains high throughout the data life-cycle

Answer 48

data profiling

Question 49

data profiling is often used at which part of the data life-cycle?

Answer 49

At the Acquisition phase

Question 50

which data quality validation method is a cost effective way to check the quality of data?

Answer 50

Data Sampling

Question 51

Cross-validation, whilst isn't directly used for data quality validation, indirectly helps identify issues with another data quality validation method, which is it?

Answer 51

Cross-validation helps detect bias in the method of data SAMPLING

Question 52

what do you need to be mindful of when it comes to data outliers?

Answer 52

Their impact on statistical analysis

Question 53

what is the difference between the two data quality challenges of duplicate data and redundant data?

Answer 53

1) Duplicate data is identical data or values that appear more than once (maybe considered necessary/valid duplication) 2) Whereas redundant data is unnecessary duplication that can be removed safely. ## Footnote think about redundancy in terms of normalization of tables, instead of record the same value multiple times, these can be 'removed' by creating a reference table.

Question 54

what data manipulation technique would you use if you wanted to create a master customer profile made from different database subjects about a particular customer?

Answer 54

Data Merge

Question 55

when does data merging usually occur?

Answer 55

At the Transformation part of ETL process

Question 56

What data manipulation technique is useful when you have numeric data you want to analyse by category?

Answer 56

Recoding

Question 57

regarding recoding data, what is meant by when the values are ordinal and nominal?

Answer 57

Ordinal values where the category has an inherent rank, like T-shirt size for example Nominal values are when the categories have no particular order, like hair colour for instance.

Question 58

An analyst ideally needs to automate quality improvements at which part of the data life-cycle?

Answer 58

the data acquisition phase (collection and preparation)

Question 59

What step must you undertake before applying Imputation to deal with null values?

Answer 59

You must understand the context of the null values by explicitly checking them.

Question 60

Bizarrely, the data quality dimension of "data validity" specifically identifies what? The author also says it is interchangeable with another term which I disagree with, what is it?

Answer 60

Whether a given value falls within an expected range The author also says data validity is AKA as data integrity!

Question 61

Data derived from categorical or ordinal rank

Answer 61

nonparametric data

Question 62

two reasons responsible for creating duplicate data | H

Answer 62

In order: 1) Humans 2) Multiple data sources

Question 63

Two ways to deal with duplicate data | `

Answer 63

1) Prevent creation in first place (best) 2) Employ duplicate resolution process ## Footnote Use warning prompts in software to prevent creation

Question 64

causes of redundant data

Answer 64

Integrating multiple systems that use shared data elements Tables not in 3NF

Question 65

Options to treat redundant data

Answer 65

Synchronize changes to shared data elements Tables in 3NF

Question 66

values outside the valid range for a given attribute

Answer 66

Invalid data

Question 67

Invalid values are easy to check for what data?

Answer 67

Numeric and date data

Question 68

It's trickier to check invalid data when it is what data? What would you implement to avoid invalid data of this kind?

Answer 68

Text data Implementing good referential integrity resolves this

Question 69

when the source column has a different datatype to the destination column

Answer 69

Specification Mismatch

Question 70

how to resolve specification mismatch

Answer 70

validate data mapping of columns

Question 71

data-type inconsistency (within the same column) during data load causes what?

Answer 71

Database load process to fail/stop

Question 72

prevention of database load failures caused by inconsistent data-types

Answer 72

Validate data-type consistency before data load

Question 73

combines multiple sources of data into a single dataset and presents it at the reporting layer

Answer 73

data blending | this is done through software/vizualizer

Question 74

Aggregating a set of value ranges into a bin and the counting how frequently the range within that bin occurs What is this and what is it an example of?

Answer 74

A histogram An example of numerosity reduction ## Footnote because it summarizes values into bins and counts their frequency instead of the individual datapoints themselves. Thereby reducing the data

Question 75

a raw data source has multiple, distinct values combined within a single character column e.g. m014 (sex = m, age = 0-14)

Answer 75

Composite column | considered a composite structural issue

Question 76

The are all part of the data cleaning and manipulation phase. What process typically handles these? 1) Data Validation: Ensuring data consistency and accuracy during extraction. 2) Data Screening: Identifying issues during the extraction phase. 3) De-duplication: Removing duplicate records. 4) Handling Invalid Data: Transforming invalid entries into valid ones. 5) Handling Missing Data: Addressing missing values during transformation.

Answer 76

ETL process

Question 77

verify limitations of data source AND present drop-down lists on form entry are two ways to check for data quality at what phase of the data analysis life-cycle?

Answer 77

Data Acquisition

Question 78

Where source data comes from more than one source and you're combining these into one table, what quality issue can arise and you should check for? What phase of the data analysis life-cycle does this align with and what process handles this?

Answer 78

Conversion issues (data consistency) You would check the source data doesn't differ in value types and if it does you'd convert the data from one source so the column contains consistent data This occurs in the Data Manipulation/Cleaning phase and the ETL process handles it

Question 79

Identifying WHEN data is acquired and HOW it is transformed can act as a quality check in what process? What phase of the data analysis life-cycle does this happen?

Answer 79

At ETL process Data Manipulation phase | at any ETL stage! Intermediary or final hop into the Data Warehouse

Question 80

How can data quality issues be introduced right at the last phase of the data analysis life-cycle (Data Visualization)? How might you mitigate / prevent this?

Answer 80

If the data analysis doesn't have a good knowledge of how the data is mapped, they may end up joining tables wrongly and producing errors. To avoid this happening, the analyist must know the data and its mapping differences between the source systems clearly.

Question 81

elements of data accuracy, consistency, uniqueness and validity taken altogether are a measure of what?

Answer 81

Data Conformity

Question 82

Moving failed rows into a staging area for remediation is an efficient way of dealing with what issue? Why?

Answer 82

Data Conformity issues It prevents the entire load process from failing

Question 83

which of the 5 broader data quality methods can detect data inconsistencies like missing, duplicate or incorrect data types?

Answer 83

Data Profiling

Question 84

If you have to deal with a very large dataset and need to assess the quality of it, which of the 5 broader methods to validate data quality mentioned in the book would you use?

Answer 84

Sampling | and thus running descriptive

Question 85

which part of the ETL process does data conversion take place?

Answer 85

Extract process

Question 86

Which data collection methods are good for a valuable source of research and insight

Answer 86

Human in the loop (Survey's and observation)

Question 87

Which is the more modern variant - ELT or ETL?

Answer 87

ELT

Question 88

sampling occurs where data is broken down into subgroups, like gender, and then randomly sampled from each of the groups.

Answer 88

Stratified

Question 89

sampling method where each record of data has an equal chance of being selected for the data set used in the analysis

Answer 89

Simple Random

Question 90

which out of the below methods is suited for minute-by-minute updates into the datawarehouse? ELT or ETL

Answer 90

ELT

Question 91

data that is repeated (identical) within the same dataset

Answer 91

duplicate data

Question 92

nonparametric data can have what effect on the normal distribution?

Answer 92

it can skew it left or right

Question 93

leading spaces, trailing spaces and non printable characters in a field will mean the data is what?

Answer 93

invalid data

Question 95

it combines multiple fields into a single field it allows analysts to consolidate data sets into a single dataset

Answer 95

DATA MERGE

Question 96

to combine data from ONE set with another data set

Answer 96

DATA APPEND

Question 97

a variable is one that’s created from existing data through calculations, transformations, or other operations.

Answer 97

DERIVED

Question 98

function to join two datasets with same columns and type and delete original table afterwards?

Answer 98

INLINE APPEND

Question 99

What is a field property setting that tells the database that a field needs to be tabulated?

Answer 99

INDEXING

Question 100

internal processing field property setting that controls how much data must be looked at when processing the data for queries involving commonly sorted or filtered data?

Answer 100

INDEX FIELD

Question 101

data is entered the same way each time and as intended based on the business rules. Example of what quality dimension?

Answer 101

Consistency

Question 102

what is the most common method to prepare data for a DWH? ETL or ELT?

Answer 102

ETL