dww

1. What is the primary purpose of a Data Warehouse?. a) Transactional processing. b) Data backup. c) Decision support and analytical processing. d) Real-time operations management.

2. What does ETL stand for in the context of Data Warehousing?. a) Extract, Transform, and Load. b) Extract, Transmit, and Log. c) Execute, Transfer, and Load. d) Extract, Transform, and List.

3. Which of the following is an example of a fact table in a Data Warehouse?. Customer. Product. Sales. Region.

4. A star schema in a Data Warehouse typically includes which of the following?. Central fact table connected to normalized dimension tables. A fact table with no dimension tables. A fully normalized fact table. Fact and dimension tables with minimal redundancy.

5. In a snowflake schema, dimension tables are typically: Denormalized. Normalized. Not used. Combined with fact tables.

6. Which of the following is typically NOT stored in a Data Warehouse?. Historical data. Transactional data. Aggregated data. Analytical data.

7. What is a Data Mart?. A smaller subset of a data warehouse focusing on a particular business area. A fully integrated data warehouse. A system for transactional data management. A data storage system for operational data.

8. OLAP stands for: Online Logical Analytical Processing. Online Analytical Processing. Online Application Processing. Operational Logical Analytical Processing.

9. Which of the following is a key feature of OLAP cubes?. Data is processed in real-time. Data is presented in a multidimensional format. Data is normalized. Data is stored in operational systems.

10. Which schema is considered simpler and generally faster for querying in a Data Warehouse?. Snowflake schema. Star schema. Galaxy schema. Hybrid schema.

11. What is the primary purpose of SSIS (SQL Server Integration Services)?. To design relational databases. To manage and automate database backups. To extract, transform, and load (ETL) data from various sources. To manage user permissions in SQL Server.

1. Data Warehousing is the process of collecting and managing data from different sources to provide meaningful business insights. T. F.

A Data Warehouse (DW) typically stores transactional data for real-time processing. TT. F.

3. ETL stands for Extract, Transform, and Load. T. FF.

Data Warehouses are primarily used for operational processing and transactional systems. T. F.

. A data mart is a subset of a data warehouse, usually focused on a particular business area or department. T. F.

6. OLTP (Online Transaction Processing) systems are designed for large-scale data analysis. T. FF.

7. OLAP (Online Analytical Processing) systems are optimized for complex queries and data analysis. T. F.

Data warehouse architecture typically includes staging, data integration, and presentation layers. TT. F.

9. A star schema in a data warehouse has a central fact table connected to multiple dimension tables. T. F.

10. In a snowflake schema, dimension tables are normalized into multiple related tables. T. F.

11. A fact table in a data warehouse stores descriptive data like names and addresses. T. FF.

12. Data warehouses use a normalized schema to reduce data redundancy. T. FF.

13. Data mining is the process of querying large datasets to find patterns, trends, and relationships. T. F.

14. Data lakes are typically unstructured and less governed than data warehouses. T. F.

15. Data warehouse data is generally updated in real time to ensure accuracy. T. F.

16. A dimension in a data warehouse typically stores qualitative, descriptive data. T. F.

1. Which of the following best describes a data warehouse?. A transactional database. A system optimized for real-time updates. A centralized repository for integrated data from multiple sources. A tool for managing network traffic.

2. The primary purpose of a data warehouse is to: Support day-to-day operations. Facilitate decision-making through analytical processing. Manage email communications. Control network security.

3. Which schema is characterized by a central fact table connected to multiple dimension tables?. Snowflake schema. Star schema. Galaxy schema. Flat schema.

4. In data warehousing, ETL stands for: Extract, Transform, Load. Evaluate, Transfer, Load. Extract, Transfer, Link. Evaluate, Transform, Link.

5. Which of the following is a characteristic of OLAP systems?. Handles high-volume transactional data. Supports complex queries for analysis. Processes real-time data updates. Manages day-to-day operations.

6. Data granularity in a data warehouse refers to: The size of the database. The level of detail or summarization of data. The number of users accessing the system. The speed of data retrieval.

7. Which of the following is NOT a typical component of a data warehouse architecture?. Data sources. ETL processes. Operational systems. Data marts.

8. A data mart is: A large-scale data warehouse. A subset of a data warehouse focused on a specific business area. A tool for data mining. An operational database.

9. Which of the following best describes metadata in a data warehouse?. Data about data. Actual transactional data. User access logs. Data encryption keys.

21. Data replication is primarily used to: Delete outdated data. Copy data from one location to another. Encrypt sensitive information. Compress large datasets.

Which type of replication involves copying data changes as they occur?. Snapshot replication. Transactional replication. Merge replication. Full replication.

In SQL Server, transactional replication is best suited for: Environments with infrequent data changes. Real-time reporting systems. Scenarios requiring bidirectional data updates. Systems with low data volumes.

Merge replication is characterized by: One-way data flow. Conflict resolution mechanisms. Immediate data consistency. Lack of synchronization.

25. Snapshot replication: Provides near real-time data updates. Captures data at specific points in time. Requires complex conflict resolution. Is ideal for high-volume transaction systems.

41. Change Data Capture (CDC) is used to: A) Archive historical data. B) Identify and capture changes in data sources. C) Perform data cleansing. D) Encrypt data during transmission.

42. Which CDC method utilizes database transaction logs to capture changes?. Trigger-based CDC. Log-based CDC. Query-based CDC. API-based CDC.

43. A key advantage of CDC over traditional ETL processes is: Higher data redundancy. Increased latency. Real-time data synchronization. Simplified data modeling.

44. In CDC, the term 'latency' refers to: The size of the data being processed. The delay between data change and its capture. The number of data sources. The frequency of data backups.

45. Which of the following is a common use case for CDC?. Static data reporting. Real-time analytics. Data archiving. Manual data entry.

11. A key difference between OLTP and OLAP is: OLAP uses normalized schemas. OLTP handles complex queries. OLTP is optimized for fast transactions, OLAP for analysis. OLAP is always real-time.

12. Which type of data warehouse schema normalizes dimensions?. Star. Snowflake. Flat. Circular.

13. Fact tables in a warehouse primarily contain: Descriptive data. Keys and measures. Metadata. Configuration data.

14. The time-variant property of a data warehouse means: Data is only current. Data is associated with time periods. Data is not stored. Data changes in real-time.

15. Which of the following is an example of a dimension table attribute?. Total Revenue. Product Name. Discount Rate. Order ID.

16. A surrogate key is: A business key. A foreign key. A synthetic unique identifier. A duplicate key.

17. Which layer of a data warehouse is responsible for staging raw data?. Presentation layer. Data staging layer. Metadata layer. Query layer.

18. The process of cleaning and transforming data before loading is known as: Extraction. Cleansing. ETL. Data Mining.

31. Which replication method creates a full copy periodically?. Snapshot. Merge. Transactional. Incremental.

One benefit of replication is: Reduced data availability. Slower reporting. Improved disaster recovery. Decreased performance.

33. Which replication type is best for scalability and minimal latency?. Snapshot. Merge. Transactional. Manual.

34. Transactional replication uses: Log-based changes. Full data dump. Schema-only replication. Manual backups.

41. Change Data Capture (CDC) supports: Stale data reads. Real-time change monitoring. Backup automation. Manual updates.

42. In CDC, change tables typically store: Backup copies. Only deletes. Changes (inserts, updates, deletes). Only inserts.

43. Which SQL Server feature enables native CDC?. Triggers. Temporal tables. Change Data Capture option. Stored procedures.

44. CDC allows downstream systems to: Remain static. Sync with stale data. React to live data changes. Ignore changes.

45. One challenge of CDC is: It reduces performance overhead. Conflict handling is automatic. Schema evolution handling. CDC logs never grow.

46. Log-based CDC works by: o A) o B) o C) Checking primary keys o D) Answer: B. Monitoring system time. Reading transaction logs. Checking primary keys. Using batch scripts.

1. A data warehouse is designed for transactional processing. T. F.

ETL stands for Extract, Transform, Load. T. F.

Star schema includes a central fact table connected to dimension tables. T. F.

4. Data marts are larger than data warehouses. T. F.

5. OLAP systems are optimized for complex analytical queries. T. F.

Metadata provides information about the data in a data warehouse. T. F.

7. Snapshot replication captures data changes in real-time. T. F.

8. Transactional replication is suitable for high-volume, real-time data updates. T. FF.

9. Merge replication does not handle data conflicts. T. F.

10. Change Data Capture (CDC) helps in identifying data changes as they occur. TT. F.

11. A data warehouse integrates data from multiple sources. T. F.

12. OLTP systems are optimized for analytical processing. T. F.

13. The star schema uses normalized dimension tables. T. F.

14. ETL stands for Extract, Transform, Load. T. F.

15. Data warehouses are time-variant, storing historical data. T. F.

16. Fact tables typically contain descriptive attributes. T. FF.

17. Surrogate keys are often used in dimension tables. T. F.

18. Snowflake schema increases query performance over star schema. T. F.

19. A data mart is a subset of a data warehouse focused on a specific business area. T. F.

20. Data lakes and data warehouses serve the same purpose. T. F.

21. Snapshot replication updates data continuously in real-time. TT. F.

22. Merge replication supports data changes from multiple sources. T. F.

23. Transactional replication is typically used for high-latency environments. T. F.

24. Replication is only available in Oracle databases. T. F.

25. Replication improves availability and redundancy. T. FF.

26. Change Data Capture (CDC) enables real-time data movement. T. FF.

27. CDC works by exporting data from backups. T. F.

29. CDC systems always detect schema changes automatically. T. F.

30. CDC can be used to sync data across heterogeneous systems. T. F.

31. Change tracking is a simpler form of CDC. T. F.

32. In CDC, inserts, updates, and deletes are recorded. T. F.

33. SQL Server supports CDC natively. T. F.

34. CDC can add overhead to database transaction logs. T. F.

35. CDC is not useful for data warehouses. T. F.

36. Change tables are used to store historical changes in CDC. T. F.

38. CDC eliminates the need for any data transformation. False. T. F.

39. Log-based CDC uses the database's transaction log to track changes. T. F.

40. CDC is suitable for batch data processing only. T. F.