In ETL for data warehousing, what does the term “load and index” typically mean in the context of preparing warehouse tables for analytics?
Correct Answer: A process to load the data in the data warehouse and to create the necessary indexes
Introduction / Context:Once data is extracted and transformed, it must be loaded into target tables and optimized for query performance. The phrase “load and index” captures this final stage of ETL/ELT processes.
Given Data / Assumptions:
- Warehouse tables (fact and dimensions) need data and performance structures.
- Indexes (or clustering/sort keys in columnar systems) improve query speed.
- Quality steps may occur pre- or post-load, but are distinct from “load and index.”
Concept / Approach:“Load and index” means bulk loading data into the warehouse tables followed by creating or refreshing indexes/statistics to optimize access paths for BI queries. In some platforms, indexing may be deferred until after loading for efficiency.
Step-by-Step Solution:
Identify the two actions: load data, then build indexes/statistics.Exclude definitions that imply rejection or quality improvement without loading.Select the definition that states load + index creation.Verification / Alternative check:Many DBMS bulk-load guides recommend disabling/rebuilding indexes around loads for speed, which aligns with this interpretation.
Why Other Options Are Wrong:
- A: rejection is not the purpose here.
- C/D: quality upgrades are cleansing steps, not “load and index.”
Common Pitfalls:Assuming indexing refers only to B-tree creation; cost-based stats and columnar sort/encode steps also fall under performance preparation.
Final Answer:A process to load the data in the data warehouse and to create the necessary indexes