1What does the acronym ETL stand for in the context of data warehousing?
A.Execute, Transform, Load
B.Extract, Transfer, Load
C.Extract, Transform, Load
D.Enter, Transact, Log
Correct Answer: Extract, Transform, Load
Explanation:
ETL stands for Extract, Transform, and Load, which represents the three key steps in moving data from source systems to a data warehouse.
Incorrect! Try again.
2Which phase of the ETL process involves retrieving data from various source systems?
A.Loading
B.Transformation
C.Extraction
D.Staging
Correct Answer: Extraction
Explanation:
Extraction is the first phase where data is identified and retrieved from distinct operational source systems.
Incorrect! Try again.
3In the context of multi-dimensional data, what is a 'Data Cube'?
A.A hardware storage unit for backups
B.A relational database table
C.A data structure that allows analysis of data from multiple perspectives
D.A three-dimensional visualization tool
Correct Answer: A data structure that allows analysis of data from multiple perspectives
Explanation:
A Data Cube is a multi-dimensional data structure used in OLAP to analyze data across various dimensions (e.g., time, location, product) simultaneously.
Incorrect! Try again.
4What is the primary purpose of the 'Transformation' step in ETL?
A.To store raw data immediately
B.To visualize data for end-users
C.To convert data into a format suitable for the destination system
D.To delete all historical data
Correct Answer: To convert data into a format suitable for the destination system
Explanation:
Transformation involves cleaning, filtering, aggregating, and reformatting data to ensure it meets the schema and quality requirements of the target data warehouse.
Incorrect! Try again.
5Which OLAP operation involves moving from detailed data to a higher level of summarization?
A.Drill-down
B.Roll-up
C.Slice
D.Dice
Correct Answer: Roll-up
Explanation:
Roll-up is an aggregation operation that summarizes data by climbing up a hierarchy or by dimension reduction.
Incorrect! Try again.
6What is the 'Staging Area' in an ETL process?
A.A temporary storage area used between extraction and transformation
B.The user interface for reporting
C.The source system database
D.The final destination for the data
Correct Answer: A temporary storage area used between extraction and transformation
Explanation:
The staging area is an intermediate storage location where data is held temporarily for processing before being loaded into the data warehouse.
Incorrect! Try again.
7Which of the following is a common data transformation task?
A.Data cleansing (removing duplicates/nulls)
B.Creating backup tapes
C.Hardware maintenance
D.Data entry
Correct Answer: Data cleansing (removing duplicates/nulls)
Explanation:
Data cleansing is a critical transformation task to ensure data quality by handling missing values, duplicates, and inconsistencies.
Incorrect! Try again.
8Which OLAP operation allows you to navigate from summarized data to more detailed data?
A.Slice
B.Pivot
C.Roll-up
D.Drill-down
Correct Answer: Drill-down
Explanation:
Drill-down allows users to view data at a more granular level of detail, moving down the concept hierarchy.
Incorrect! Try again.
9What is 'Incremental Loading'?
A.Loading data manually one record at a time
B.Deleting the database and reloading everything
C.Loading data without any transformation
D.Loading only the data that has changed or is new since the last load
Correct Answer: Loading only the data that has changed or is new since the last load
Explanation:
Incremental loading is efficient because it processes only new or modified records, rather than reloading the entire dataset.
Incorrect! Try again.
10In Informatica and other ETL tools, what is a 'Mapping'?
A.A geographical map of server locations
B.The physical cabling diagram
C.A definition of the flow of data from source to target including transformations
D.A list of user passwords
Correct Answer: A definition of the flow of data from source to target including transformations
Explanation:
A mapping graphically represents the logic of how data moves and transforms from sources to targets.
Incorrect! Try again.
11What does CDC stand for in the context of data extraction?
A.Central Data Core
B.Computer Disk Check
C.Create Data Cubes
D.Change Data Capture
Correct Answer: Change Data Capture
Explanation:
Change Data Capture (CDC) is a technique used to identify and capture changes made to data in a database so that only changed data is extracted.
Incorrect! Try again.
12Which operation selects a single dimension from a data cube, effectively creating a 2D view?
A.Rolling
B.Drilling
C.Dicing
D.Slicing
Correct Answer: Slicing
Explanation:
The Slice operation performs a selection on one dimension of the cube, resulting in a sub-cube (often a 2D plane).
Incorrect! Try again.
13What is a 'Full Load' strategy?
A.Loading data incrementally
B.Loading only error logs
C.Completely erasing the target and reloading all data from the source
D.Loading data only on weekends
Correct Answer: Completely erasing the target and reloading all data from the source
Explanation:
A Full Load involves truncating the target tables and reloading the entire dataset from the source systems.
Incorrect! Try again.
14The 'Dice' operation in a data cube performs which action?
A.Summarizes the data
B.Selects a sub-cube by selecting specific values for multiple dimensions
C.Rotates the axes of the cube
D.Selects a single value for one dimension
Correct Answer: Selects a sub-cube by selecting specific values for multiple dimensions
Explanation:
Dicing involves selecting specific ranges or values for two or more dimensions, creating a smaller sub-cube.
Incorrect! Try again.
15Which of the following is a major benefit of using an ETL tool like Informatica over hand-coding SQL?
A.It is always free of cost
B.It eliminates the need for a database
C.It requires no training
D.It provides metadata management and visual workflow design
Correct Answer: It provides metadata management and visual workflow design
Explanation:
ETL tools offer visual interfaces, reusability, metadata management, and performance optimization features that are difficult to maintain with hand-coded scripts.
Incorrect! Try again.
16In a data cube, what are 'Measures'?
A.The numerical data being analyzed (e.g., Sales Amount)
B.The descriptive attributes like Product Name
C.The keys linking tables
D.The security protocols
Correct Answer: The numerical data being analyzed (e.g., Sales Amount)
Explanation:
Measures (or facts) are the quantitative values in a data cube that analysts want to aggregate and calculate, such as revenue, cost, or quantity.
Incorrect! Try again.
17What is 'Data Validation' during the Transformation phase?
A.Ensuring data meets defined business rules and constraints
B.Compressing the data for storage
C.Copying data to a backup drive
D.Encrypting the data
Correct Answer: Ensuring data meets defined business rules and constraints
Explanation:
Validation checks if data falls within acceptable ranges or follows specific formats (e.g., ensuring a date is valid or an age is positive).
Incorrect! Try again.
18Which transformation type is used to combine data from two different sources based on a common key?
A.Sorter
B.Filter
C.Aggregator
D.Joiner
Correct Answer: Joiner
Explanation:
A Joiner transformation is used to merge records from two different source streams based on a matching column or key, similar to a SQL JOIN.
Incorrect! Try again.
19What allows an ETL process to pause and restart from a specific point in case of failure?
A.Checkpoints
B.Firewalls
C.Data Marts
D.Primary Keys
Correct Answer: Checkpoints
Explanation:
Checkpoints store the state of the process, allowing the system to resume from the last successful point rather than restarting the entire job.
Incorrect! Try again.
20The 'Pivot' or 'Rotate' operation is used to:
A.Add new data to the cube
B.Delete dimensions
C.Reorient the view of the data to provide an alternative presentation
D.Filter out null values
Correct Answer: Reorient the view of the data to provide an alternative presentation
Explanation:
Pivot rotates the data axes to view the data from a different perspective, often swapping rows and columns.
Incorrect! Try again.
21Which of the following is an example of a heterogeneous data source in ETL?
A.Extracting from Oracle and loading into Oracle
B.Extracting from a CSV file and a SQL database to load into a Data Warehouse
C.Renaming a table
D.Copying a table within the same database
Correct Answer: Extracting from a CSV file and a SQL database to load into a Data Warehouse
Explanation:
Heterogeneous sources involve different types of storage systems or formats (e.g., Flat files and Relational Databases) interacting in the same process.
Incorrect! Try again.
22What is the role of a 'Surrogate Key' generated during transformation?
A.To act as a foreign key to the source system
B.To replace the natural primary key with a unique internal system identifier
C.To encryption the data
D.To sort the data alphabetically
Correct Answer: To replace the natural primary key with a unique internal system identifier
Explanation:
Surrogate keys are system-generated unique integers used in the data warehouse to maintain independence from changes in source system keys.
Incorrect! Try again.
23Latency in ETL refers to:
A.The time difference between data creation in the source and availability in the target
B.The number of rows in a table
C.The size of the hard drive
D.The cost of the software
Correct Answer: The time difference between data creation in the source and availability in the target
Explanation:
Latency is the delay or lag time involved in moving data from the operational source to the data warehouse for analysis.
Incorrect! Try again.
24Which ETL approach loads data into the target system first and then transforms it?
A.ETL
B.CDC
C.OLTP
D.ELT
Correct Answer: ELT
Explanation:
ELT (Extract, Load, Transform) loads raw data directly into the target system (often a data lake or cloud DW) and uses the target's processing power to transform it.
Incorrect! Try again.
25In simple ETL processing, what is a 'Lookup' transformation used for?
A.To search for relevant values in another table or file based on a source value
B.To export data to Excel
C.To aggregate sums
D.To delete records
Correct Answer: To search for relevant values in another table or file based on a source value
Explanation:
Lookups are used to retrieve related data (like getting a Customer Name based on a Customer ID) from a reference table during the flow.
Incorrect! Try again.
26What is 'Data Granularity'?
A.The level of detail or summary of the data
B.The security level of the data
C.The speed of the processor
D.The type of database used
Correct Answer: The level of detail or summary of the data
Explanation:
Granularity refers to the level of detail. High granularity means very detailed data (e.g., individual transactions), while low granularity means summarized data.
Incorrect! Try again.
27Which component in Informatica PowerCenter executes the sessions and workflows?
A.Repository Service
B.Integration Service
C.Designer
D.Client Tools
Correct Answer: Integration Service
Explanation:
The Integration Service is the engine that reads workflow information and executes the ETL processes (reading, transforming, and loading data).
Incorrect! Try again.
28A 'Dimension' in a data cube represents:
A.The perspective or category by which data is viewed (e.g., Time, Location)
B.The calculation algorithm
C.The quantitative data
D.The total size of the database
Correct Answer: The perspective or category by which data is viewed (e.g., Time, Location)
Explanation:
Dimensions provide the context for facts. For example, sales data can be viewed by the dimensions of Time, Product, or Region.
Incorrect! Try again.
29What is the primary function of an 'Aggregator' transformation?
A.To filter rows
B.To split data into multiple streams
C.To perform calculations like Sum, Avg, Count on groups of data
D.To join tables
Correct Answer: To perform calculations like Sum, Avg, Count on groups of data
Explanation:
Aggregator transformations perform calculations on groups of records, reducing the number of rows passing through to the target.
Incorrect! Try again.
30Why is 'Metadata' important in ETL?
A.It increases the storage size unnecessarily
B.It provides data about the data (e.g., source, format, lineage)
C.It is the actual user data
D.It slows down the extraction process
Correct Answer: It provides data about the data (e.g., source, format, lineage)
Explanation:
Metadata describes the structure, definition, and origin of data, helping developers understand mappings and maintain data lineage.
Incorrect! Try again.
31Which loading strategy performs an 'Update else Insert' (Upsert) operation?
A.Truncate and Load
B.Full Load
C.Static Load
D.Incremental Merge
Correct Answer: Incremental Merge
Explanation:
This strategy checks if a record exists; if it does, it updates the record, and if not, it inserts a new one.
Incorrect! Try again.
32The process of tracking the flow of data from origin to destination is called:
A.Data Lineage
B.Data Mining
C.Data Encryption
D.Data Compression
Correct Answer: Data Lineage
Explanation:
Data lineage tracks the lifecycle of data, showing where it came from, how it was transformed, and where it moves.
Incorrect! Try again.
33What type of transformation is 'Filtering'?
A.Active transformation
B.Passive transformation
C.Extraction transformation
D.Loading transformation
Correct Answer: Active transformation
Explanation:
Filtering is an active transformation because it can change the number of rows passing through it (by rejecting rows that don't meet criteria).
Incorrect! Try again.
34In a Star Schema, which tables connect to the central Fact Table?
A.Dimension Tables
B.Temporary Tables
C.System Tables
D.Other Fact Tables
Correct Answer: Dimension Tables
Explanation:
A Star Schema consists of a central Fact Table containing measures, connected to multiple Dimension Tables containing descriptive attributes.
Incorrect! Try again.
35What is the purpose of 'Error Handling' in ETL?
A.To delete the source database
B.To capture, log, and manage records that fail validation or transformation
C.To ignore all errors
D.To stop the server permanently
Correct Answer: To capture, log, and manage records that fail validation or transformation
Explanation:
Error handling ensures that bad data doesn't crash the process and is logged for analysis and correction.
Incorrect! Try again.
36Which of the following is strictly an ETL Tool?
A.Google Chrome
B.Microsoft Word
C.Informatica PowerCenter
D.Adobe Photoshop
Correct Answer: Informatica PowerCenter
Explanation:
Informatica PowerCenter is a dedicated enterprise data integration and ETL tool.
Incorrect! Try again.
37What is a 'Flat File' in the context of data extraction?
A.A 3D image file
B.A database with no relationships
C.A corrupted file
D.A plain text file (like CSV or TXT) containing data records
Correct Answer: A plain text file (like CSV or TXT) containing data records
Explanation:
Flat files are simple text files often used as sources or targets for data exchange, containing records with no structural relationships like a database.
Incorrect! Try again.
38During the Loading phase, what does 'Referential Integrity' ensure?
A.That data is loaded quickly
B.That the disk is not full
C.That the data is encrypted
D.That relationships between tables remain consistent (e.g., Foreign Keys exist)
Correct Answer: That relationships between tables remain consistent (e.g., Foreign Keys exist)
Explanation:
Referential integrity ensures that a foreign key value in one table points to a valid primary key in another table.
Incorrect! Try again.
39What is the benefit of a 'Passive' transformation?
A.It requires user interaction
B.It does not change the number of rows passing through (e.g., calculation derived from a column)
C.It changes the number of rows
D.It deletes data
Correct Answer: It does not change the number of rows passing through (e.g., calculation derived from a column)
Explanation:
Passive transformations (like Expression) operate on a row-by-row basis without changing the row count or transaction boundary.
Incorrect! Try again.
40In multidimensional data, a 'Hierarchy' defines:
A.The parent-child relationships within a dimension (e.g., Year -> Quarter -> Month)
B.The network topology
C.The security levels of users
D.The speed of data access
Correct Answer: The parent-child relationships within a dimension (e.g., Year -> Quarter -> Month)
Explanation:
Hierarchies define the structure of navigation within a dimension, allowing for roll-up and drill-down operations.
Incorrect! Try again.
41Which ETL process is most likely to be used for 'Real-time' data warehousing?
Real-time warehousing requires continuous ingestion of data streams or micro-batches rather than large, infrequent batch loads.
Incorrect! Try again.
42What is 'Data Normalization' usually associated with?
A.OLAP Systems
B.Flat Files
C.Transactional (OLTP) Source Systems
D.Data Cubes
Correct Answer: Transactional (OLTP) Source Systems
Explanation:
Normalization is a technique used in OLTP systems to reduce redundancy. In ETL, we often denormalize data when moving it to a Data Warehouse (OLAP).
Incorrect! Try again.
43The 'Router' transformation is used to:
A.Send data to a single target
B.Split data into multiple groups based on conditions
C.Join two tables
D.Sort data
Correct Answer: Split data into multiple groups based on conditions
Explanation:
A Router acts like a complex filter that directs rows to different output groups based on specific conditions.
Incorrect! Try again.
44What is the definition of 'Data Quality'?
A.The fitness of data for its intended use (accuracy, completeness, consistency)
B.The speed of the internet connection
C.The cost of the database license
D.The amount of data stored
Correct Answer: The fitness of data for its intended use (accuracy, completeness, consistency)
Explanation:
Data quality measures how well the dataset satisfies criteria like accuracy, completeness, and consistency.
Incorrect! Try again.
45Which step usually comes immediately after Extraction in a standard ETL workflow?
A.Transformation (or Staging)
B.Reporting
C.Loading
D.Archiving
Correct Answer: Transformation (or Staging)
Explanation:
After extraction, data is typically staged or immediately transformed before being loaded.
Incorrect! Try again.
46What is a 'Derived Column'?
A.A new column created through calculation or concatenation during transformation
B.A primary key
C.A column extracted directly from the source
D.A deleted column
Correct Answer: A new column created through calculation or concatenation during transformation
Explanation:
Derived columns are generated by ETL logic, such as calculating 'Total Cost' from 'Price' and 'Quantity'.
Incorrect! Try again.
47In the context of Informatica, what is a 'Workflow'?
A.The database installation file
B.A set of instructions describing how and when to run tasks (sessions)
C.A single SQL query
D.The error log
Correct Answer: A set of instructions describing how and when to run tasks (sessions)
Explanation:
A workflow orchestrates the execution of sessions and other tasks, defining the order and dependencies of execution.
Incorrect! Try again.
48Why might 'Data Type Conversion' be necessary during ETL?
A.Because the source system and target system may use different data formats (e.g., String vs Date)
B.To confuse hackers
C.To make the file look bigger
D.It is never necessary
Correct Answer: Because the source system and target system may use different data formats (e.g., String vs Date)
Explanation:
Different systems handle data types differently (e.g., a text file date vs a SQL date), requiring conversion for compatibility.
Incorrect! Try again.
49Which operation is the opposite of 'Pivot'?
A.Unpivot
B.Join
C.Slice
D.Filter
Correct Answer: Unpivot
Explanation:
Unpivot transforms columns into rows, normalizing the data structure, which is the reverse of a Pivot operation.
Incorrect! Try again.
50What is the main advantage of using a Data Cube over a standard SQL query for analysis?
A.It pre-aggregates data, providing faster query response for multidimensional analysis
B.It uses less memory
C.It is slower
D.It allows for data entry
Correct Answer: It pre-aggregates data, providing faster query response for multidimensional analysis
Explanation:
Data Cubes pre-calculate aggregations at intersections of dimensions, allowing for near-instant responses to complex analytical queries.
Incorrect! Try again.
Did this save you a night before the exam?
LPU Notes is free, and it stays free. Ads cover part of the server bill.
The rest comes out of a student's own pocket: the domain, the storage,
and keeping the site up through the weeks everyone needs it at once.
The payment button didn't load. An ad blocker or a filtered network is the usual reason.
to try again.
Nothing here is ever locked, and nothing unlocks. Chip in only if it was worth it.
What it pays for →