Data Warehousing Technology Flashcards

Question 1

What is a data warehouse in simple terms?

Accepted Answer

A data warehouse is a central repository that stores large volumes of historical data gathered from many operational systems. It is organized for analysis and reporting rather than for running day-to-day transactions. Analysts and business users query it to find trends, compare performance over time, and support decisions.

Question 2

How does an OLTP system differ from an OLAP system?

Accepted Answer

OLTP systems handle many small, frequent transactions such as orders, payments, and account updates, and they prioritize fast writes and data accuracy. OLAP systems support complex analytical queries that read large amounts of data, often aggregating across many records. Data warehouses are typically built to serve OLAP workloads.

Question 3

What does it mean that a data warehouse is subject-oriented?

Accepted Answer

Subject-oriented means the warehouse is organized around major business topics such as customers, products, sales, or finance, rather than around individual applications. Data from several source systems about the same topic is brought together under one consistent structure. This makes analysis across the business easier.

Question 4

Why is a data warehouse described as integrated?

Accepted Answer

Integrated means that data from different sources is cleaned and standardized before it is stored, so the same concept uses the same name, format, and meaning everywhere. For example, one system may code gender as M and F while another uses numbers, and the warehouse maps both to one standard.

Question 5

What is the meaning of nonvolatile in data warehousing?

Accepted Answer

Nonvolatile means that once data is loaded into the warehouse, it is not changed or deleted by routine operations. Instead, new data is added in batches while older records remain available for historical analysis. This stability lets reports return the same results for the same past period.

Question 6

What is time variance in a data warehouse?

Accepted Answer

Time variance means every record in the warehouse carries a time element, such as a date or period, so the same subject can be tracked across many points in time. Analysts can compare this quarter with last quarter or view how a customer's status changed.

Question 7

What is a data mart and how does it relate to a warehouse?

Accepted Answer

A data mart is a smaller, focused subset of data designed for one department or business area, such as marketing or sales. It may be fed from an enterprise warehouse or built directly from source systems. Data marts give specific users faster, simpler access to the data they need.

Question 8

Why do organizations separate operational databases from the data warehouse?

Accepted Answer

Operational databases are tuned for fast, reliable updates during daily work, while warehouses are tuned for large read-heavy analysis. Running heavy reports directly on production systems can slow transactions and strain resources. Separating the two protects day-to-day operations and gives analysts a stable, well-organized place to work.

Question 9

What is a star schema, and what does its layout look like?

Accepted Answer

A star schema is a dimensional model with one central fact table surrounded by several dimension tables, resembling a star when drawn. The fact table holds measurable events, such as sales amounts, and foreign keys linking to each dimension. Dimension tables hold descriptive context like product, store, and date.

Question 10

What is a fact table and what kind of data does it store?

Accepted Answer

A fact table stores the measurements or events a business wants to analyze, such as order quantity, revenue, or units shipped. Each row usually represents one event at a defined level of detail called the grain. Numeric measures are aggregated, while foreign keys connect each row to descriptive dimensions.

Question 11

What is a dimension table and why is it useful?

Accepted Answer

A dimension table holds descriptive attributes that give context to facts, such as customer names, product categories, calendar dates, or regions. Analysts use these attributes to filter, group, and label results. Keeping descriptions separate from the fact table makes queries more readable and the model easier to extend.

Question 12

What is grain in dimensional modeling and why must it be defined first?

Accepted Answer

Grain is the precise description of what one row in the fact table represents, such as one line item on one invoice. It must be declared before choosing dimensions or measures, because every other design decision depends on it. A clear grain prevents double counting and keeps totals consistent.

Question 13

What is a snowflake schema and how does it differ from a star schema?

Accepted Answer

A snowflake schema normalizes dimension tables into several related tables, so a product dimension might link to separate category and supplier tables. A star schema keeps each dimension as one denormalized table. Snowflakes save some storage and reduce redundancy, but they usually require more joins and can be slower for analysts.

Question 14

What is a slowly changing dimension and why does it matter?

Accepted Answer

A slowly changing dimension is an attribute of a dimension, such as a customer's address, that changes over time but not frequently. Warehouses handle these changes with rules, such as overwriting the old value or keeping history in a new row. The choice determines whether past reports show values as they were then.

Question 15

What is a surrogate key and why do warehouses use it?

Accepted Answer

A surrogate key is a system-generated integer identifier with no business meaning, assigned to each dimension row. Warehouses use it instead of the source system's natural key because natural keys can change, be reused, or come from several systems. Surrogate keys also make it simple to track history when a dimension row changes.

Question 16

What is a conformed dimension and why is it valuable?

Accepted Answer

A conformed dimension is a dimension with the same keys, attribute names, and meanings wherever it is used across multiple fact tables or data marts. For example, a single date dimension shared by sales and inventory facts. It allows results from different business processes to be combined and compared accurately in one report.

Question 17

What does ETL stand for and what are its three main steps?

Accepted Answer

ETL stands for extract, transform, and load. Extract pulls data from source systems, transform cleans, standardizes, and reshapes it, and load writes the prepared data into the warehouse. Together these steps move raw operational data into a form ready for analysis.

Question 18

How does ELT differ from ETL in the order of operations?

Accepted Answer

ELT loads raw data into the target system first and performs transformations afterward, inside the warehouse or lakehouse engine. ETL transforms data in a separate staging process before loading it. ELT is common in cloud platforms where storage and compute are relatively inexpensive and scalable, letting teams keep raw data for later use.

Question 19

What kinds of data quality checks are commonly applied during transformation?

Accepted Answer

Common checks include validating data types, confirming required fields are present, removing duplicate records, and checking values against expected ranges or reference lists. Rows that fail may be rejected, corrected, or routed to an error table for review. These controls keep warehouse reports trustworthy.

Question 20

What role does a staging area play in an ETL pipeline?

Accepted Answer

A staging area is a temporary storage location where extracted data lands before transformation and loading. It lets teams keep an untouched copy of source data, rerun steps after a failure, and isolate source system problems from the warehouse. Staging tables are usually cleared or refreshed on each load cycle.

Question 21

What is the difference between a full load and an incremental load?

Accepted Answer

A full load replaces or reloads the entire target dataset each time, which is simple but expensive for large tables. An incremental load moves only new or changed records since the last run, often tracked by timestamps. Incremental loading saves time but needs careful tracking to avoid missed or duplicate rows.

Question 22

What is change data capture and why is it useful?

Accepted Answer

Change data capture is a set of techniques that identify and record inserts, updates, and deletes made in a source system. Instead of scanning entire tables, the pipeline reads these recorded changes, often from database logs. This reduces load on source systems and keeps the warehouse close to current.

Question 23

What is data lineage and why do ETL teams track it?

Accepted Answer

Data lineage is a record of where a piece of data originated, what transformations were applied, and where it moved afterward. Teams track it to trace errors back to their source, support audits, and explain how a reported figure was calculated. Clear lineage builds trust in warehouse results.

Question 24

Why is an ETL job usually designed to be idempotent?

Accepted Answer

An idempotent job produces the same result whether it runs once or is rerun several times after a failure. This matters because pipelines often fail partway through, and operators need to restart them safely without creating duplicate rows or inflated totals. Designing for idempotence usually means using upserts, deletes by partition, or checkpoints.

Question 25

How does columnar storage differ from row storage for analytics?

Accepted Answer

Row storage keeps all fields of a record together, which suits transactions that read or write whole records. Columnar storage keeps each column's values together, so an analytical query that reads only a few columns can skip most of the data. Columns with similar values also compress well, reducing storage and scan time.

Question 26

What is table partitioning and how does it help query performance?

Accepted Answer

Partitioning divides a large table into smaller segments, usually by a column such as date or region. When a query filters on that column, the engine reads only the relevant partitions instead of the whole table. Partitioning also makes it easier to load new data and drop old data one segment at a time.

Question 27

What is a sort key or clustering key in a warehouse table?

Accepted Answer

A sort or clustering key defines the physical order in which rows are stored on disk. When queries filter or join on that key, the engine can skip blocks that contain no matching values. Choosing a key that matches common filter patterns reduces the amount of data scanned.

Question 28

What is a materialized view and why do analysts rely on it?

Accepted Answer

A materialized view is a stored query result that is saved physically rather than recalculated each time it is requested. Analysts rely on it for expensive aggregations, such as monthly totals over large fact tables, because the answer is ready to read. The stored result must be refreshed when data changes.

Question 29

What are summary or aggregate tables, and when should a team build them?

Accepted Answer

Summary or aggregate tables store precomputed totals, counts, or averages at a coarser level than the base fact table, such as daily sales by store. A team should build them when the same rollups are requested often and querying the detailed table is too slow. They trade storage and load time for faster reporting.

Question 30

What is run-length encoding and why does it suit columns?

Accepted Answer

Run-length encoding replaces a sequence of repeated values with a single value and a count. It works well on sorted or low-variety columns, such as a status field where long stretches share the same entry. Less data is read from disk, which often speeds up scans on analytical queries.

Question 31

How do table statistics help a query optimizer choose a good plan?

Accepted Answer

Table statistics describe row counts, value distributions, and data sizes, such as how many distinct values a column holds. The optimizer uses these estimates to decide join order, join method, and whether to use an index or scan. Outdated statistics can lead to poor plans, so warehouses usually refresh them after large loads.

Question 32

What is a result cache and what should users know about it?

Accepted Answer

A result cache stores the output of a query so that an identical request can be answered without running the query again. It saves time and compute for dashboards that repeat the same questions. Users should know that cached results can become stale, so caches are usually invalidated when underlying data changes.

Question 33

What are the main layers of a typical enterprise data warehouse architecture?

Accepted Answer

A common layered design includes source systems, a staging or raw layer, an integrated core layer that stores cleaned and conformed data, and presentation layers such as data marts or semantic models. Each layer has a clear purpose, which keeps responsibilities separate. Exact layer names vary between organizations and tools.

Question 34

How do Inmon and Kimball approaches to warehouse design differ?

Accepted Answer

Inmon's top-down approach builds a normalized enterprise warehouse first, and dependent data marts are derived from it. Kimball's bottom-up approach builds dimensional data marts organized around business processes, which are combined through conformed dimensions. Both aim for consistent data, but they differ in sequence and modeling style.

Question 35

What is a data lakehouse and how does it relate to a data warehouse?

Accepted Answer

A data lakehouse combines low-cost storage of raw files, typical of a data lake, with table formats and management features that support warehouse-style queries and governance. It aims to serve both data science and business reporting from one storage layer. Many organizations use lakehouses alongside, or in place of, a traditional warehouse.

Question 36

What does separating compute from storage mean in cloud warehouses?

Accepted Answer

Separating compute from storage means data is kept in shared, durable storage while query processing runs on compute resources that can be sized and scaled independently. Teams can add processing power for heavy month-end reports without buying more disk, and can pause compute when it is idle.

Question 37

What is metadata and why does a warehouse need a data catalog?

Accepted Answer

Metadata is data about data, such as table definitions, column meanings, owners, refresh schedules, and lineage. A data catalog organizes this metadata so users can search for datasets and understand what they contain. Without it, analysts may duplicate work or misinterpret fields that look similar across tables.

Question 38

What does role-based access control protect in a data warehouse?

Accepted Answer

Role-based access control assigns permissions to roles, and users receive roles that determine which tables, columns, or rows they may view. It protects sensitive data such as personal or financial fields from users who do not need them. Good access design also supports audit trails and compliance with internal policies.

Question 39

How do data quality tests fit into a warehouse release process?

Accepted Answer

Data quality tests are automated checks, such as row count comparisons, null checks, uniqueness checks, and reconciliation of totals against source reports, that run after each pipeline release. Failing tests can block publication of new data so that errors do not reach dashboards. Testing early reduces the time spent investigating wrong numbers later.

Question 40

How would you decide between a cloud warehouse and an on-premises warehouse?

Accepted Answer

The decision depends on factors such as data volume and growth, latency requirements, regulatory or residency constraints, existing skills, and total cost of ownership. Cloud platforms offer elastic scaling and managed operations, while on-premises systems give more direct control over hardware and location.

Data Warehousing Technology Flashcards

1Core Concepts of Data Warehousing

2Dimensional Modeling Foundations

3ETL and Data Integration

4Storage and Query Performance

5Architecture and Platform Choices

Learning Resources