{"id":134,"date":"2025-11-06T21:32:34","date_gmt":"2025-11-06T21:32:34","guid":{"rendered":"http:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/?page_id=134"},"modified":"2025-11-06T21:32:34","modified_gmt":"2025-11-06T21:32:34","slug":"control-questions-data-warehousing-exam-preparation","status":"publish","type":"page","link":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/control-questions-data-warehousing-exam-preparation\/","title":{"rendered":"Control Questions \u2013 Data Warehousing Exam Preparation"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Scope: Chapters 1\u20132 (Data Ingestion &amp; Data Transformation) + Practical DW\/OLAP Materials \u2014 Generated on 06 Nov 2025<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">1. Core Concepts &amp; Definitions<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>1. Define data ingestion in the context of a data warehouse.<\/li>\n\n\n\n<li>2. Explain the difference between ETL and ELT. In which modern architectures is ELT more common, and why?<\/li>\n\n\n\n<li>3. What is meant by a source and a target in data ingestion? Give two examples of each relevant to a data warehouse.<\/li>\n\n\n\n<li>4. Define batch, micro-batch, and streaming ingestion. For each, give one appropriate use case.<\/li>\n\n\n\n<li>5. Explain the difference between bounded and unbounded datasets, and why this matters for ingestion strategy.<\/li>\n\n\n\n<li>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?<\/li>\n\n\n\n<li>7. Describe the Bronze \/ Silver \/ Gold (medallion) architecture and map each layer to its role in a data warehouse pipeline.<\/li>\n\n\n\n<li>8. Define idempotency in ETL\/ELT jobs and explain why it is important.<\/li>\n\n\n\n<li>9. Explain the difference between normalization and denormalization, and how they relate to data warehouse schemas.<\/li>\n\n\n\n<li>10. Define incremental load and contrast it with full reload. When is each appropriate?<\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\">2. Sources, Targets &amp; Storage Choices<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>11. List at least five questions you should ask when evaluating a new source system before ingesting it into a data warehouse.<\/li>\n\n\n\n<li>12. Explain why the phrase \u201cstorage is cheap, complexity is expensive\u201d is relevant when deciding whether to keep raw data in a warehouse architecture.<\/li>\n\n\n\n<li>13. Differentiate OLTP and OLAP systems. Why is an OLAP-optimized environment preferred for data warehousing?<\/li>\n\n\n\n<li>14. Explain why using a staging (landing) area before transforming and loading into the core warehouse is considered a best practice. Give two benefits.<\/li>\n\n\n\n<li>15. A team wants near real-time dashboards but only receives a daily CSV export from the source. Using ingestion concepts from Chapters 1\u20132, explain why sub-minute latency is not realistic with this setup.<\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\">3. Ingestion Patterns &amp; Tooling<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>16. Compare declarative vs imperative ingestion\/ETL tools. Give one advantage and one drawback of each from a maintainability perspective.<\/li>\n\n\n\n<li>17. What is a hybrid ingestion approach, and in what situations might it be necessary in a data warehousing program?<\/li>\n\n\n\n<li>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.<\/li>\n\n\n\n<li>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.<\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\">4. Transformation Patterns &amp; Data Quality<\/h1>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<ul class=\"wp-block-list\">\n<li>20. Give three examples of basic transformations and three examples of advanced transformations typically applied before loading facts and dimensions.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li>21. Explain how a Bronze \u2192 Silver \u2192 Gold flow supports (a) auditability, (b) data quality, and (c) usability of the warehouse for BI and analytics.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">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?<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">23. Describe how to design an idempotent transformation process for a daily customer dimension load.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n<\/div><\/div>\n\n\n\n<h1 class=\"wp-block-heading\">5. Applied Scenario Questions<\/h1>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<ul class=\"wp-block-list\">\n<li>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?<\/li>\n\n\n\n<li>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.<\/li>\n\n\n\n<li>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?<\/li>\n\n\n\n<li>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?<\/li>\n\n\n\n<li>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.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n<h1 class=\"wp-block-heading\">6. Short Explain \/ Compare Prompts<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>31. Explain the difference between \u201cright-time\u201d ingestion and \u201creal-time\u201d ingestion, and why \u201cright-time\u201d is often sufficient for analytics.<\/li>\n\n\n\n<li>32. Compare the roles of the Bronze and Silver layers. What should (and should not) happen in each?<\/li>\n\n\n\n<li>33. Describe how systematic data quality checks and monitoring contribute to trustworthy data warehouse reporting.<\/li>\n\n\n\n<li>34. Why can an over-normalized schema be problematic for analytical workloads, even if it is elegant from a transactional modeling perspective?<\/li>\n\n\n\n<li>35. Summarize how a well-designed ETL\/ELT pipeline favors simplification over over-engineering.<\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\">7. True \/ False (with Justification)<\/h1>\n\n\n\n<p class=\"wp-block-paragraph\">For each statement below, mark True\/False and justify your answer in one or two sentences.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>36. If we use ELT, we no longer need to worry about data quality during ingestion.<\/li>\n\n\n\n<li>37. Streaming ingestion is always better than batch ingestion for a data warehouse.<\/li>\n\n\n\n<li>38. Keeping raw (landing) data helps in debugging and replaying pipeline runs.<\/li>\n\n\n\n<li>39. Idempotent ETL\/ELT jobs can be safely re-run without corrupting warehouse tables.<\/li>\n\n\n\n<li>40. Denormalization is never acceptable in a serious data warehouse design.<\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\">8. Dimensional Modeling \u2013 Star Schema (Practical 1 DW)<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>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.<\/li>\n\n\n\n<li>42. Based on the business questions (sales over time, by store, by product, by region), justify why a star schema is appropriate.<\/li>\n\n\n\n<li>43. Design a candidate fact_sales table and its dimension tables. Indicate primary keys and foreign keys.<\/li>\n\n\n\n<li>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?<\/li>\n\n\n\n<li>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.<\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\">9. Dimensional Modeling \u2013 Snowflaking &amp; Conformed Dimensions (TD-2b)<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>46. According to the snowflaking rules you studied, list at least three constraints on what should or should not be snowflaked.<\/li>\n\n\n\n<li>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?<\/li>\n\n\n\n<li>48. Design a snowflaked geography where Customer and Store share Region\/Country. Sketch tables and keys, and explain how this supports conformed dimensions.<\/li>\n\n\n\n<li>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?<\/li>\n\n\n\n<li>50. Describe how a report \u201cYearly revenue by Country \u2192 Region \u2192 City and by Brand\u201d would traverse your snowflake schema (which tables and relationships are used?).<\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\">10. ETL \/ Implementation \u2013 Star &amp; Snowflake Notebooks<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>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?<\/li>\n\n\n\n<li>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?<\/li>\n\n\n\n<li>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?<\/li>\n\n\n\n<li>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.<\/li>\n\n\n\n<li>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?<\/li>\n\n\n\n<li>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?<\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\">11. OLAP &amp; Advanced SQL \u2013 Snowflake &amp; Challenge Handouts<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>57. Define slice, dice, roll-up, and drill-down in the context of an OLAP cube over Product \u00d7 Region \u00d7 Time.<\/li>\n\n\n\n<li>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.<\/li>\n\n\n\n<li>59. Explain how a snowflake schema supports roll-up from City \u2192 Region \u2192 Country and drill-down from Category \u2192 Subcategory \u2192 Product.<\/li>\n\n\n\n<li>60. Why is a dimensional or snowflake schema more natural than a 3NF OLTP schema for expressing OLAP operations?<\/li>\n\n\n\n<li>61. Using the OLAP challenge examples: explain conceptually how GROUPING SETS allows multiple aggregation levels in a single query.<\/li>\n\n\n\n<li>62. Differentiate ROLLUP and CUBE: which subtotals does each generate in a sales reporting context?<\/li>\n\n\n\n<li>63. For \u201cTop-N per group\u201d problems (e.g., Top-3 products per Region-Quarter), why are window functions needed instead of a simple GROUP BY?<\/li>\n\n\n\n<li>64. For running totals, period-over-period changes, or YTD metrics, which OLAP\/SQL features are most appropriate and why?<\/li>\n\n\n\n<li>65. Explain how the dimensional model (facts + conformed dimensions) simplifies percentage-of-total and share-of-wallet style metrics.<\/li>\n\n\n\n<li>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?<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Scope: Chapters 1\u20132 (Data Ingestion &amp; Data Transformation) + Practical DW\/OLAP Materials \u2014 Generated on 06 Nov 2025 1. Core Concepts &amp; Definitions 2. Sources, Targets &amp; Storage Choices 3. Ingestion Patterns &amp; Tooling 4. Transformation Patterns &amp; Data Quality &hellip; <a href=\"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/control-questions-data-warehousing-exam-preparation\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":11,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-134","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages\/134","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/comments?post=134"}],"version-history":[{"count":1,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages\/134\/revisions"}],"predecessor-version":[{"id":136,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages\/134\/revisions\/136"}],"wp:attachment":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/media?parent=134"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}