Control Questions – Data Warehousing Exam Preparation

Scope: Chapters 1–2 (Data Ingestion & Data Transformation) + Practical DW/OLAP Materials — Generated on 06 Nov 2025

1. Core Concepts & Definitions

  • 1. Define data ingestion in the context of a data warehouse.
  • 2. Explain the difference between ETL and ELT. In which modern architectures is ELT more common, and why?
  • 3. What is meant by a source and a target in data ingestion? Give two examples of each relevant to a data warehouse.
  • 4. Define batch, micro-batch, and streaming ingestion. For each, give one appropriate use case.
  • 5. Explain the difference between bounded and unbounded datasets, and why this matters for ingestion strategy.
  • 6. What is data transformation? How is it different from ingestion, and why is it critical before data reaches the analytical layer of the warehouse?
  • 7. Describe the Bronze / Silver / Gold (medallion) architecture and map each layer to its role in a data warehouse pipeline.
  • 8. Define idempotency in ETL/ELT jobs and explain why it is important.
  • 9. Explain the difference between normalization and denormalization, and how they relate to data warehouse schemas.
  • 10. Define incremental load and contrast it with full reload. When is each appropriate?

2. Sources, Targets & Storage Choices

  • 11. List at least five questions you should ask when evaluating a new source system before ingesting it into a data warehouse.
  • 12. Explain why the phrase “storage is cheap, complexity is expensive” is relevant when deciding whether to keep raw data in a warehouse architecture.
  • 13. Differentiate OLTP and OLAP systems. Why is an OLAP-optimized environment preferred for data warehousing?
  • 14. Explain why using a staging (landing) area before transforming and loading into the core warehouse is considered a best practice. Give two benefits.
  • 15. A team wants near real-time dashboards but only receives a daily CSV export from the source. Using ingestion concepts from Chapters 1–2, explain why sub-minute latency is not realistic with this setup.

3. Ingestion Patterns & Tooling

  • 16. Compare declarative vs imperative ingestion/ETL tools. Give one advantage and one drawback of each from a maintainability perspective.
  • 17. What is a hybrid ingestion approach, and in what situations might it be necessary in a data warehousing program?
  • 18. An organization ingests from many SaaS APIs and internal databases. List at least four criteria that should guide the choice between a managed connector platform and custom-built ingestion jobs.
  • 19. Why is it risky to rely only on GUI-based ingestion tools without version-controlled configurations? Relate your answer to governance and recoverability in data warehousing.

4. Transformation Patterns & Data Quality

  • 20. Give three examples of basic transformations and three examples of advanced transformations typically applied before loading facts and dimensions.
  • 21. Explain how a Bronze → Silver → Gold flow supports (a) auditability, (b) data quality, and (c) usability of the warehouse for BI and analytics.

22. Why should important business rules (for example, revenue definitions or customer segments) live in governed transformation layers rather than only in dashboards or reports?

23. Describe how to design an idempotent transformation process for a daily customer dimension load.

24. What is an update pattern? Give an example of when you would use an UPSERT/MERGE operation instead of only INSERTs in a data warehouse.

25. How can poor handling of NULLs, schema drift, or late-arriving data in transformations erode trust in data warehouse reports? Provide a concrete scenario.

5. Applied Scenario Questions

  • 26. You ingest sales data hourly from a stable API. (a) Would you choose batch, micro-batch, or streaming, and why? (b) Where do you store raw vs cleaned data? (c) How does this design support downstream star-schema reporting?
  • 27. An HR dataset is delivered once per day as CSV and occasionally changes schema. Propose an ingestion mode, staging strategy, and an incremental update approach for the related dimensions.
  • 28. For an IoT sensor use case with very high volume and frequency: (a) When might you aggregate or filter before storing all raw events? (b) How does that impact analytical flexibility in the data warehouse?
  • 29. You integrate advertising, web analytics, and CRM data. (a) Which aspects of each source must be evaluated before ingestion? (b) How do you design conformed dimensions to enable cross-channel KPIs?
  • 30. A transformation job sometimes runs twice and duplicates fact rows. (a) Which principle from your ingestion/transformation learning is being violated? (b) Propose a redesign to prevent this problem.

6. Short Explain / Compare Prompts

  • 31. Explain the difference between “right-time” ingestion and “real-time” ingestion, and why “right-time” is often sufficient for analytics.
  • 32. Compare the roles of the Bronze and Silver layers. What should (and should not) happen in each?
  • 33. Describe how systematic data quality checks and monitoring contribute to trustworthy data warehouse reporting.
  • 34. Why can an over-normalized schema be problematic for analytical workloads, even if it is elegant from a transactional modeling perspective?
  • 35. Summarize how a well-designed ETL/ELT pipeline favors simplification over over-engineering.

7. True / False (with Justification)

For each statement below, mark True/False and justify your answer in one or two sentences.

  • 36. If we use ELT, we no longer need to worry about data quality during ingestion.
  • 37. Streaming ingestion is always better than batch ingestion for a data warehouse.
  • 38. Keeping raw (landing) data helps in debugging and replaying pipeline runs.
  • 39. Idempotent ETL/ELT jobs can be safely re-run without corrupting warehouse tables.
  • 40. Denormalization is never acceptable in a serious data warehouse design.

8. Dimensional Modeling – Star Schema (Practical 1 DW)

  • 41. In the Practical 1 retail scenario (products, stores, customers, regions, sales): (a) Identify the grain of the fact table. (b) List all dimensions and at least two measures.
  • 42. Based on the business questions (sales over time, by store, by product, by region), justify why a star schema is appropriate.
  • 43. Design a candidate fact_sales table and its dimension tables. Indicate primary keys and foreign keys.
  • 44. Explain how your design supports: (a) overall sales over time, (b) sales by store, (c) sales by product and store. Which dimension keys are involved in each?
  • 45. Give three examples of poor modeling decisions for this scenario (e.g., storing customer name directly in the fact table) and explain why they are problematic.

9. Dimensional Modeling – Snowflaking & Conformed Dimensions (TD-2b)

  • 46. According to the snowflaking rules you studied, list at least three constraints on what should or should not be snowflaked.
  • 47. For a star schema with Date, Store, Product, Customer, and Fact Sales: (a) Which attributes belong in true hierarchies that justify snowflaking? (b) Which attributes should remain in the base dimensions?
  • 48. Design a snowflaked geography where Customer and Store share Region/Country. Sketch tables and keys, and explain how this supports conformed dimensions.
  • 49. Why is it usually a mistake to snowflake low-cardinality attributes such as product color or store manager name, even though it is technically possible?
  • 50. Describe how a report “Yearly revenue by Country → Region → City and by Brand” would traverse your snowflake schema (which tables and relationships are used?).

10. ETL / Implementation – Star & Snowflake Notebooks

  • 51. Describe the main ETL steps implemented in the star-schema notebook: how are dimensions populated and how is fact_sales derived from raw data?
  • 52. Identify at least three places in the ETL notebooks where idempotency or incremental loads should be considered. What are the risks if they are not?
  • 53. In the snowflake ETL notebook, explain how surrogate keys are generated and propagated into child dimensions (e.g., subcategory/category, region/city). Why is this key management essential?
  • 54. Compare loading a star schema vs a snowflake schema: which typically requires more joins when building the fact table, and which is simpler for incremental loads? Explain.
  • 55. Suppose source sales data arrives daily with corrections to past days. Which update pattern should be used for (a) fact_sales and (b) dim_product, and why?
  • 56. Give two examples where explicit data quality checks should be added in the notebooks (e.g., null foreign keys, negative quantities). What should be done when checks fail?

11. OLAP & Advanced SQL – Snowflake & Challenge Handouts

  • 57. Define slice, dice, roll-up, and drill-down in the context of an OLAP cube over Product × Region × Time.
  • 58. For a given snowflake schema, write in words: one slice query, one dice query, one roll-up, and one drill-down that an analyst might run.
  • 59. Explain how a snowflake schema supports roll-up from City → Region → Country and drill-down from Category → Subcategory → Product.
  • 60. Why is a dimensional or snowflake schema more natural than a 3NF OLTP schema for expressing OLAP operations?
  • 61. Using the OLAP challenge examples: explain conceptually how GROUPING SETS allows multiple aggregation levels in a single query.
  • 62. Differentiate ROLLUP and CUBE: which subtotals does each generate in a sales reporting context?
  • 63. For “Top-N per group” problems (e.g., Top-3 products per Region-Quarter), why are window functions needed instead of a simple GROUP BY?
  • 64. For running totals, period-over-period changes, or YTD metrics, which OLAP/SQL features are most appropriate and why?
  • 65. Explain how the dimensional model (facts + conformed dimensions) simplifies percentage-of-total and share-of-wallet style metrics.
  • 66. In scenarios where you must filter only certain subtotal rows from a rollup query, what is tricky about the filtering logic and how should it be handled?