Data Warehousing Concepts

What does ETL stand for in the context of data warehousing?

• Extract, Transmit, Load

• Extract, Transform, Load

• Extract, Transfer, Load

• Extract, Translate, Load

Explain the difference between star schema and snowflake schema.

• Star schema has denormalized dimension tables directly connected to the fact table, while snowflake schema has normalized dimension tables with sub-dimension tables.

• Star schema has sub-dimension tables with normalized dimension tables.

• Snowflake schema has denormalized dimension tables directly connected to the fact table.

• Star schema has normalized dimension tables directly connected to the fact table.

What is the main difference between OLAP and OLTP?

• OLAP is used for real-time data processing, while OLTP is used for historical data analysis.

• OLAP is for analyzing data, OLTP is for managing transactional data.

• OLAP focuses on individual transactions, while OLTP focuses on aggregated data.

• OLAP is designed for online shopping platforms, while OLTP is designed for social media analytics.

Describe the components of a typical data warehouse architecture.

• data sources, ETL process, data storage, data access, metadata

• data ingestion, data cleaning, data querying, data reporting

• data processing, data visualization, data security, data modeling

• data transformation, data analysis, data retrieval, data governance

What are some common data mining techniques used in data warehousing?

• Sequential Pattern Mining

• Clustering, Classification, Regression, Association Rule Mining, Anomaly Detection

• Decision Trees

• Neural Networks

Why is data governance important in the context of data warehousing?

• Data governance is irrelevant for data warehousing

• Data governance only focuses on data quantity, not quality

• Data governance ensures data is stored in physical warehouses

• Data governance is important in the context of data warehousing to ensure data accuracy, consistency, security, and compliance with regulations.

What is the purpose of the Extract phase in the ETL process?

• To load data into the target system

• To transform data into meaningful insights

• To retrieve data from the source systems

• To clean and validate data

How does data modeling help in designing a data warehouse?

• Data modeling in a data warehouse involves randomly selecting data without any structure or relationships.

• Data modeling is irrelevant in designing a data warehouse and does not impact data retrieval or storage.

• Data modeling provides a structured approach to organizing data, defining relationships, and optimizing data retrieval, storage, and processing in a data warehouse.

• Data modeling helps in designing a data warehouse by creating chaos and confusion in data organization.

Give an example of a real-world scenario where OLAP would be more suitable than OLTP.

• Analyzing historical sales data to identify trends and make strategic decisions.

• Recording customer transactions at a point of sale terminal

• Tracking real-time inventory levels in a retail store

• Storing user login information for authentication purposes

What are some best practices for ensuring data quality in a data warehouse?

• Ignore data profiling and data quality rules

• Implement data cleansing procedures only once

• Skip regular assessments and metadata management

• Perform data profiling, establish data quality rules, implement data cleansing procedures, conduct regular assessments, ensure data lineage and metadata management