ETL

Question 1

What is Data?

Answer 1

is anything we can quantify whether it be figures, numbers, texts, pictures, values, dates, etc. (Researching & absorbing)

Question 2

What is Information?

Answer 2

Is any meaningful insight we can gather from the data. Any conclusions we can make by reviewing the data (doing)

Question 3

What is Knowledge?

Answer 3

Gathering of Information to understand what is happening in the Data (interacting)

Question 4

What is Wisdom?

Answer 4

Using that knowledge to make future decisions (reflecting)

Question 5

What is Structured Data?

Answer 5

Eazy to analyze and turn into information

Question 6

What is Unstructured Data?

Answer 6

Difficult or tedious to extract the data into information

Question 7

What is Semi-Structured Data?

Answer 7

Some structure on how the data is presented but may have tough parts to analyze or difficult to extract

Question 8

What are the Data Quality Traits?

Answer 8

Consistent, Relevant, Complete, Accurate, Granular

Question 9

Describe Consistent Data

Answer 9

Data doesn’t contradict or conflict with itself

Question 10

Describe Relevant Data

Answer 10

Data is up-to-date ( Is this data that we still need )

Question 11

Describe Complete Data

Answer 11

No missing records or fields (when using that data in comparison to other data. The data should have same fields)

Question 12

Describe Accurate Data

Answer 12

Represents reality ( The collected data should be realistic and not skewed)

Question 13

Describe Granular Data

Answer 13

How specific is our data ( We need to determine how specific our data is. What kind of conclusion can we draw with how specific our data is )

Question 14

What is Enterprise Data?

Answer 14

Data (Data that is collected across the aspect of our business)

Question 15

What are the different types of Enterprise Data?

Answer 15

Transactional Data, Analytical Data, Master Data, Reference Data, Reporting Data, Meta Data

Question 16

What is transactional Data?

Answer 16

data that is used in on-going operations within the company.

Question 17

What is analytical Data?

Answer 17

numerical values or metrics that provide business intelligence and help make business decisions.

Question 18

What is Master Data?

Answer 18

Data that is persistent and define the primary business entities within the company. This could include

Question 19

What is Reference Data?

Answer 19

Subset of master data. Any information that is usually standardized by some other party.

Question 20

What is Reporting Data?

Answer 20

Aggregated data compiled for the purpose of analytics

Question 21

What is Meta Data?

Answer 21

Data about the data.

Question 22

What is OLTP (Online Transaction Processing) ?

Answer 22

A data system that is based off transactions. Is optimized to run as many concurrent transactions as possible. Focuses on very fast write and update capabilities. Highly normalized and relational. For Client Facing Applications

Question 23

What is OLAP (Online Analysis Processing) ?

Answer 23

A data system for business facing applications. The databases are optimized for reads/querying. Is a denormalized database. Easier to store

Question 24

What is ETL?

Answer 24

EXTRACT TRANSFORM LOAD - A procedure of copying data from one or more sources into a new location called a data target.

Question 25

What is Extract? And the different types

Answer 25

taking data from a data source’

Full Extraction - Taking all data from a data source

Incremental Extraction - taking only new data from a data source

Question 26

What is transformation? And the different types

Answer 26

Converting/modifying extracted data to match a desired data target.

Cleaning(Making data Consistent), DeDuplication, Format Revision, Key Restructing

Advanced: Validation, filtering, derivation

Question 27

What is load? And the different types

Answer 27

Adding the data into the data target, such as an ODS, Staging area, Data Warehouse, or Data Mart.

• Initial Load - populating all the data warehouse tables
• Incremental Load - Adding in new incremenetal data periodically as needed.
• Full Refresh - Removing all data from one or more tables and reloading all fresh data. (kind of truncating and reloading)

Question 28

What is a data source?

Answer 28

• Anywhere that we are gathering our data from ( could be data gathered from structured/unstructured/semistructured)
• Client facing applications
• External Data

Question 29

What is a data warehouse?

Answer 29

Single central hub for the entire company’s dataset (All of the Enterprise Data). Everything extracted from all data sources.

Question 30

What is a staging area?

Answer 30

is a temporary holding location for data that has just been extracted (or potentially through the ETL process)

Question 31

What is an Operational Data Store (ODS)

Answer 31

interim area used before a Data Warehouse

Provides a snapshop of the latest data from multiple data sources

Question 32

What is a Data Mart?

Answer 32

A custom designed database for business intelligence for a specific department / aspect of your company that you wish to draw conclusions from Dimensional Database

Question 33

What is a Dimensional Cube?

Answer 33

A visual representation of the relationship between fact tables and dimensions

Question 34

What is Data Mining?

Answer 34

Process of collecting the data, and modeling the data to find relationships / patterns, and making business decisions based on these models

Question 35

What is Data Purging?

Answer 35

Process of deleting data from a data warehouse. The frequency of a data purge depend entirely on the business requirements. Data is often kept
for 5-10 years before it is purged

Question 36

What is ETL Testing?

Answer 36

The process of validating, verifying, and qualifying data while preventing duplicate records and data loss

Question 37

What is the ETL Testing Lifecycle?

Answer 37

Identify business requirements, Validate our data sources, Design Test cases, ETL, Summary Report, Test Closure

Question 38

What are some different types of ETL tests?

Answer 38

Source to Target Testing, Data Transformation Testing, Data Integration Testing

Question 39

How would you do a source to target Testing with sql?

Answer 39

Can select count(*) from one table and ensure that count equals our target and source

Question 40

How would you do data transformation test with sql?

Answer 40

Changing null values to 0 - SELECT ISNULL(myColumn, 0 ) FROM myTable

Transforming with replace - Update , Set (Replace), Where

Question 41

What is Data Migration Testing?

Answer 41

Process of verifying that data migrates from source to target with no loss of information/data

Question 42

What is data validation?

Answer 42

Method for checking the accuracy and quality of your data

Question 43

How would you do some data validation testing with sql?

Answer 43

isnull function, update, replace

Question 44

Some ETL Testing Scenarios :

Answer 44

Validation, Null validation, Duplicate Validation, Data Cleaning

Question 45

What is business Reporting?

Answer 45

Process of gathering data by utilizing different software and tools to extract relavent information.

Can use google data studios to create this visual representation

Question 46

How would you check for duplicates?

Answer 46

SELECT name, COUNT(email)
FROM users
GROUP BY email
HAVING COUNT(email) > 1

You can select by what you to output with the count of what duplicates you’re looking for from the table you want and group by whatever duplicate you’re looking for with having count greater than 1