Data Warehousing Interview Questions

Interviewers use this question to see whether you design from business processes and questions, not from source tables. Structure your answer around the Kimball design steps and show real trade-offs.

• Context — name the business process and users briefly: “The retail operations team needed daily store-level sales and margin reporting; they were stitching together exports from the POS and ERP systems.”
• Requirements — explain how you gathered them:
  • Workshops with store managers and finance to list the actual questions they asked every week.
  • A review of existing Excel reports to find the metrics and filters people already relied on.
  • Source-system profiling, to learn what data really existed and how clean it was.
• Design decisions — the core of your answer:
  • Grain: you chose one row per POS receipt line rather than daily store totals, because users needed product-level and hourly analysis, and aggregates can always be built from atomic data.
  • Dimensions: date, store, product, customer and promotion, with product and store tracked as SCD Type 2 so historical reports stayed correct after re-categorisation.
  • Facts: quantity, net sales and cost — all additive — with margin calculated in the BI layer rather than stored.
• Validation — a prototype in Power BI with a few real users, and a reconciliation of totals against finance before go-live.
• Result — report preparation dropped from two days to near real time, and the same conformed dimensions were reused for the inventory mart later.

Note: Mention one thing you would do differently now. It shows maturity and gives the interviewer an easy follow-up to explore.

This question tests calm incident handling, communication and learning. Organise your answer into triage, communicate, recover, prevent.

• Situation — “At 6 a.m. on month-end close, the finance load failed. The CFO's revenue pack was due at 10 a.m.”
• Triage — describe how you found the cause quickly:
  • Checked the orchestrator logs to locate the failing task rather than rerunning everything blindly.
  • Identified the root cause: the ERP team had added a new currency code, and a lookup with no default rejected the batch.
  • Assessed impact: which tables were stale and which reports depended on them.
• Communicate — you told the finance lead immediately, gave an estimated fix time, and agreed a fallback in case the deadline slipped. Early warning matters more than a perfect explanation.
• Recover — added the missing mapping, reran only the failed task and its downstream dependencies, and reconciled the key totals against the ERP before announcing the data was ready. Because loads were idempotent, rerunning created no duplicates.
• Prevent — afterwards you ran a blameless post-incident review and added:
  • A default Unknown member so new codes no longer fail the load, plus an alert listing unmapped values.
  • A schema and reference-data change notice agreed with the source team.
  • Freshness monitoring so failures are flagged by 3 a.m., not 6 a.m.

Finish with the outcome: the pack went out at 9:30 a.m., and there were no similar failures in the following quarters.

Note: Interviewers listen for whether you fixed the system, not just the symptom. Always close with the preventive change.

This is extremely common, and the way you handle it decides whether people trust the warehouse. Show both a methodical investigation and good stakeholder management.

1. Take it seriously and get specifics. Ask which report, which number, which filters and which source screen they compared against. “Sales are wrong” is not investigable; “Mumbai net sales for 3 September show ₹42 lakh in the warehouse and ₹45 lakh in the ERP” is.

2. Reconcile systematically, from the most likely causes to the least:

• Timing — was the warehouse loaded before late transactions arrived? Are time zones or cut-off times different?
• Definitions — gross versus net, including or excluding returns, tax, cancelled orders or test stores.
• Filters and mappings — a store assigned to a different region, or a product in an Unknown category.
• Load issues — rejected rows, duplicate loads, failed CDC or a truncated file.
• Model issues — a fan-out join or an SCD lookup picking the wrong version.

Compare counts and totals layer by layer — source, staging, integration, mart, report — to find exactly where the gap appears.

3. Explain with a bridge. Show the difference broken into causes and amounts, so users see where every rupee went.

4. Fix and prevent. Correct any genuine defect, restate history if needed, and add automated reconciliation checks between source and warehouse. Document definitions in the data catalogue.

In your example, highlight that you kept the user informed during the investigation and that the new checks caught the next discrepancy before users did.

Note: Often both systems are right for different purposes. Clear labelling and definitions resolve as many disputes as bug fixes do.

The interviewer wants evidence that you diagnose with data before changing things, and that you can quantify results. Use STAR with the technical detail concentrated in the Action.

• Situation — “Our sales dashboard took over two minutes to load and the nightly load was running into business hours, delaying reports for 300 users.”
• Task — bring dashboard load time under 10 seconds and finish the batch by 6 a.m., without adding hardware cost.
• Action — show your diagnosis first, then the fixes:
  • Pulled the query history and execution plans to find the costliest queries rather than guessing.
  • Found the fact table scanned in full because filters wrapped the date column in a function, preventing partition pruning.
  • Rewrote the filters, and clustered the table on date and store to match the most common access pattern.
  • Replaced a join on a string key with an integer surrogate key, and removed a many-to-many join that was multiplying rows.
  • Built a daily aggregate table for the dashboard's summary visuals, leaving detail queries on the atomic fact.
  • Converted the full nightly reload into an incremental MERGE on changed partitions only.
• Result — dashboard load fell to about 6 seconds, the batch finished by 4 a.m., and compute cost dropped by around 30 percent.

Mention how you validated correctness: row counts and totals matched the old version before switching users over. Faster but wrong is worse than slow.

Note: Use real metrics from your own experience. Specific before-and-after numbers make a performance story believable.

Migrations are high-risk projects, so interviewers look for planning discipline, validation and stakeholder care. If you have not led one, describe your part in one or how you would approach it.

1. Assess and plan.

• Inventory everything: tables, ETL jobs, stored procedures, reports, users and downstream feeds. Usage logs show what is actually used; often a third of objects can be retired.
• Choose the approach: lift and shift (fast, keeps technical debt) or re-platform and remodel (slower, fixes design problems). Many teams lift and shift first, then modernise.
• Define success criteria: data parity, performance targets, cost budget and a cut-over date.

2. Migrate in waves. Move one subject area at a time, such as finance first, rather than a big bang. Translate SQL dialects and rewrite ETL as ELT where it makes sense.

3. Validate rigorously.

• Run old and new systems in parallel for several load cycles.
• Automate comparisons of row counts, sums by key dimensions and hash checks on sampled rows.
• Have business users sign off their key reports.

4. Manage people and cost.

• Train analysts on the new platform and publish a mapping from old to new objects.
• Set up cost monitoring, auto-suspend and resource limits from day one — consumption pricing surprises teams used to fixed hardware.
• Plan security and compliance early: network access, encryption, role design and data residency in an Indian region where required.

5. Cut over and decommission with a rollback plan, then switch off the old system so costs do not double.

Note: Close with a lesson learned, such as underestimating stored procedure rewrites. Honest lessons impress more than a flawless story.

This question probes judgement. Interviewers do not want a purist who takes six months to deliver anything, nor someone who builds a new one-off table for every request. Show that you deliberately choose where to invest.

A strong answer covers:

• Separate exploration from production. For a one-off question, a quick query or a sandbox table is fine. Anything that will be reused, scheduled or shown to leadership moves into the modelled, tested layer.
• Deliver in thin slices. Build one business process end to end — sources, a small star schema, one dashboard — in a few weeks, then extend it. Users get value early and the model grows with real feedback.
• Standardise the expensive-to-change parts first: conformed dimensions such as customer, product and date, and core metric definitions. Everything else can evolve.
• Make shortcuts visible. If you ship a tactical solution, label it, log the technical debt and agree a date to replace it.
• Automate the governance so it does not slow delivery: templates, dbt tests, CI checks and catalogue documentation generated from code.

Give an example: “Marketing needed campaign ROI reporting within a week for a board meeting. I delivered a view on staging data, clearly labelled provisional, and then over the next sprint built a proper campaign fact table with conformed customer and date dimensions. The provisional view was retired and the board pack used the certified data from the next month.”

Close by explaining the long-term payoff: every new report built on shared dimensions and tested metrics is faster to deliver than the last, which is the real argument for modelling.

Note: Use the language of trade-offs — value, risk and cost of rework — rather than presenting modelling standards as rules to follow for their own sake.

Schema changes break things people rely on, so this question tests impact analysis, communication and safe release practice.

• Situation — “The product hierarchy was being restructured, so the category columns in our product dimension had to change. Over 40 dashboards and two finance extracts used those columns.”
• Action — walk through a controlled process:
  • Impact analysis. Used lineage in the data catalogue and dbt, plus BI metadata and query logs, to list every downstream model, report and scheduled extract that touched the columns, and their owners.
  • Design for compatibility. Added the new columns alongside the old ones instead of renaming in place, and exposed the old names through a view so nothing broke on day one.
  • Communicate early. Sent owners a change notice with dates, what was changing and what they needed to do, and held a short session for the heaviest users.
  • Test. Built the change in a development environment, ran automated tests and compared key report totals before and after.
  • Phase the rollout. Migrated reports in batches, tracked progress, and dropped the deprecated columns only after usage logs showed no remaining queries.
• Result — the restructure went live on schedule with no broken reports during month-end.

Mention any safeguard you introduced afterwards, such as data contracts with source teams, a versioning convention for breaking changes, or a CI check that flags downstream models affected by a change.

Note: The key idea is expand, migrate, then contract — add the new structure, move consumers across, and only then remove the old one.

Cost ownership is now an expected skill for warehouse engineers. Structure your answer as visibility, quick wins, structural fixes and guardrails, with numbers.

• Visibility first. “Our monthly bill had doubled in six months. I built a cost dashboard from the account usage views — Snowflake's warehouse metering history or BigQuery's jobs information schema — broken down by warehouse, user, team and query pattern.” Usually a small number of workloads drive most of the spend.
• Quick wins:
  • Set auto-suspend to 60 seconds and right-sized warehouses that were larger than their workloads needed.
  • Stopped dashboards refreshing every 15 minutes when data only loaded once a day.
  • Found scheduled queries still running for reports nobody opened.
  • In BigQuery, removed SELECT * from heavy jobs and required partition filters on large tables.
• Structural fixes:
  • Converted full reloads into incremental models.
  • Added partitioning and clustering aligned with the most common filters.
  • Built aggregate tables for dashboards that repeatedly scanned the detail fact.
  • Separated workloads onto their own warehouses or reservations so costs could be attributed and tuned.
• Guardrails: resource monitors or budget alerts, maximum bytes billed per query, and a monthly review with team leads showing their spend.

Result — for example, a 35 percent reduction in monthly cost with no loss of freshness, and cost per query tracked as a standard KPI.

Note: Stress that you balanced savings against user experience. Cutting cost by making every dashboard slow simply moves the cost to the business.

This question tests whether you have a repeatable, end-to-end process. Walk through it as stages, adding brief examples from your experience.

1. Understand the need. Which business questions will the new source answer, who will use it, and how fresh must it be? This decides scope and load frequency.
2. Discover and profile the source. Meet the system owner, get the data dictionary, and profile the tables: volumes, keys, NULL rates, data types, how updates and deletes happen, and whether reliable change timestamps exist.
3. Agree an interface. Choose the extraction method — API, file drop, database replica, or log-based CDC — with minimal load on the source. Agree a data contract covering schema, delivery times and how breaking changes will be announced.
4. Land raw data. Load into the raw or bronze layer as is, with load metadata such as batch ID, load time and source file, so everything can be reprocessed.
5. Integrate and model.
   • Clean and standardise in staging: types, codes, time zones, deduplication.
   • Map entities to existing conformed dimensions. Matching the new system's customers to the warehouse customer dimension is often the hardest part.
   • Add new facts or dimensions following the existing grain and naming conventions.
6. Test. Add automated tests for keys, relationships and accepted values, and reconcile counts and totals with the source.
7. Secure and document. Classify sensitive columns, apply masking and access roles, and publish descriptions and lineage in the catalogue.
8. Operate. Schedule in the orchestrator, add freshness and failure alerts, and assign an owner.

Note: Stress conformance with existing dimensions. A new source that creates its own separate customer table fragments the warehouse instead of enriching it.

This question checks whether you understand data protection obligations and can apply them without becoming a blocker. Show principle, practicality and communication.

• Situation — “An audit found that customer phone numbers, email addresses and PAN numbers were readable by every analyst in the warehouse. We had to restrict access, but marketing and support analysts used some of those fields daily.”
• Action — describe a structured approach:
  • Classify. Tagged every column containing personal data, with the data owners, using the catalogue or policy tags.
  • Understand real needs. Interviewed the heavy users. Most only needed to count or join customers, not see their contact details.
  • Design least-privilege access. Applied dynamic masking so most roles saw masked values, replaced raw identifiers with hashed keys for joins, and gave full access only to a small approved role for campaign execution.
  • Row-level security so regional teams saw only their own customers.
  • Offer alternatives — pre-built aggregated and de-identified datasets that met most analysis needs.
  • Communicate the why: legal obligations under India's Digital Personal Data Protection Act, audit findings and real breach risks, not just “security says no”.
• Handling pushback — a manager insisted his team needed full access. You agreed a time-limited exception with documented approval from the data owner and moved his use case onto a masked dataset within a month.
• Result — the audit issue was closed, analysts kept working, and access requests moved to a documented approval flow with regular reviews.

Note: Interviewers value candidates who make the secure path the easy path. Good de-identified datasets reduce pushback far more than strict rules do.

Real-time Data Warehousing refers to systems that reflect the warehouse's status in real-time. When a query is run against the real-time data warehouse to learn about a particular aspect of a business or entity, the answer reflects the state of that entity at the time the query was run. Data warehouses typically have latent data - or data that reflects the business at a past time. Real-time data warehouses provide current data with low latency.

The following phases are generally checked and optimized during the execution of the data warehousing project:

Performance and scalability: Ensure that data loads and queries perform within expected periods and that the technical architecture is scalable.

Integration testing. Confirm that the ETL process functions well with other upstream and downstream processes.

User-acceptance testing: Certify the data warehousing solution meets users’ current expectations and anticipates their future expectations.

Regression testing: Ensure existing functionality remains intact each time a new release of ETL code and data is completed.

The data warehouse manager is expected to fulfill the following responsibilities:

• Monitor all long-term objectives.
• Train data warehouse staff.
• Ensure appropriate maintenance and development of all data.
• Evaluate staff performance.
• Administer database consolidation.
• Administer all Service Level Agreements.
• Maintain various big enterprises.

Data warehouse snapshots can be used to track activities. An employee changing his address, for example, can alert the data warehouse for a snapshot to be taken. Whenever an event occurs, a snapshot is taken.

A snapshot in this regard has three components –

• The time when the event occurred.
• A key to identifying the snapshot.
• Data that relates to the key.

It is common in data warehouse design to run into yes/no indicator fields in source systems. Such information is rather crucial.

In other words, if we keep all those indicator fields in the fact table, we need to create many dimension tables as well as store a tremendous amount of information, resulting in performance and management issues.

The junk dimension is the solution to this problem. We combine these indicator fields into a junk dimension. Thus, we only need to build one dimension table, and the size and number of fields in the fact table can be reduced. 

Views are created by combining data from different tables. As a result, a view has no data of its own.

The Materialized view, which is usually used in data warehousing, has data. Decisions can be made based on this data, calculations can be performed, etc. By using queries, the data is calculated beforehand and stored.

Creating a view does not store data in the database. The data is created when a query is fired on the view. Whereas, data of a materialized view is stored.

An attribute that conforms to each fact with which it is associated in data warehousing is called a conformed attribute. A conformed dimension ensures consistency of reporting across multiple facts and/or data marts by categorizing and describing facts and measures consistently.

The Operational Database is the source of information for the data warehouse. It includes detailed information used to run the day-to-day operations of the business. The data frequently changes as updates are made and reflect the current value of the last transactions.

Operational Database Management Systems also called OLTP (Online Transactions Processing Databases), are used to manage dynamic data in real-time.

Data Warehouse Systems serve users or knowledge workers for the purpose of data analysis and decision-making. Such systems can organize and present information in specific formats to accommodate the diverse needs of various users. These systems are called as Online-Analytical Processing (OLAP) Systems.

When data is grouped or combined in multidimensional matrices called Data Cubes. The data cube method has a few alternative names or a few variants, such as "Multidimensional databases," "materialized views," and "OLAP (On-Line Analytical Processing)."

The general idea of this approach is to materialize certain expensive computations that are frequently inquired about.

A data cube enables data to be modeled and viewed in multiple dimensions. A multidimensional data model is organized around a central theme, like sales and transactions. A fact table represents this theme. Facts are numerical measures. Thus, the fact table contains measures (such as Rs_sold) and keys to each of the related dimensional tables.

Dimensions are a fact that defines a data cube. Facts are generally quantities, which are used for analyzing the relationship between dimensions.

A pattern used in a data warehouse environment to retrieve client data is called a data mart. It is a structure specific to the data warehouse and used by the business domain in the team. Every organization has a single data mart which is located in the data warehouse repository. Different types of data marts are dependent, independent, and hybrid data marts. Dependent data marts take data that is already created whereas independent data marts take data from external sources and from a data warehouse. We can call data marts logical subsets of the data warehouse.

Metadata describes data. It has information about how and when, by whom certain data was collected, and the data format. This data of data or metadata is essential to understand information that is stored in data warehouses and XML-based web applications.

On the other hand, A data dictionary contains the basic definitions of a database. There is a list of files in the database, the number of records in each file, and information about the fields in the data dictionary.

In ETL, data is extracted from source systems, transformed into a consistent data type, and then loaded into a single repository. During ETL testing, data is validated, verified, and qualified to prevent duplicate records and data loss.

The ETL testing ensures that data is transferred from heterogeneous sources to the central data warehouse in compliance with all transformation rules and validation checks. This is different as opposed to the data reconciliation used in database testing, ETL testing is applied to data warehouse systems to obtain relevant information for analytics and business intelligence.

The data warehouse distribution process involves the following subsets of processes:

Step 1: Determine Business Objectives

Learn the requirements and objectives of the business and its owners and stakeholders and convert those requirements into quantifiable form. These quantifiable key performance indicators will then be your business objectives.

Step 2: Collect and Analyze Information

Ask questions, acquire various sales, CRM, and marketing reports(as needed), and then consolidate all this information into a structured format

Step 3: Understand core business processes

Once you have the data, you will be able to better understand the business processes and come up with core objectives you need to solve with data warehousing

Step 4: Initiate a conceptual data model

Create a conceptual model of the data after identifying the business processes. The next goal will be to determine the subjects expressed as fact tables and dimensions relating to the facts.

Step 5: Locate data sources and plan data transformations

Analyze core data sources and set to work on building the data pipelines and transformation models

Step 6: Set tracking duration

To process large amounts of data you need to optimize the time requirements. Since data warehouses track performance over time, the data should be available virtually forever. So a solution should be accounted for that.

Step 7: Start final implementation

Once the plan is developed, start allocating resources and scheduling tasks to get things in the pipeline.

Normalization also referred to as Database normalization, is the process of rearranging or organizing the columns and the tables that are associated with a relational database. This reduces data redundancy and improves data visibility.

Moreover, this process simplifies the database design so that the optimal structure can be achieved. In short, it helps split tables into additional data thus improving data interoperability and at the same time making it easy while retrieving the data.

Fact tables contain information about measurements, facts, and metrics of business processes. Usually, it sits in the center of a star schema, it is also known as a snowflake schema. Usually, a fact table consists of two types of columns:

• Fact data
• Foreign key relations

Only one of these fact tables is stored in the star schema or snowflake schema. So, multiple fact tables are stored under the fact constellation schema.

They are the two classic philosophies of data warehouse architecture.

Inmon (top-down). Bill Inmon defined the data warehouse as subject-oriented, integrated, time-variant and non-volatile. You first build a central enterprise data warehouse in third normal form that integrates all sources into a single source of truth. Departmental data marts, often dimensional, are then built from it.

Kimball (bottom-up). Ralph Kimball's approach builds dimensional models — star schemas — one business process at a time, such as sales, inventory or claims. They are integrated through conformed dimensions shared across processes, planned with an enterprise bus matrix. The collection of these stars is the warehouse.

Which is better? Neither universally. Inmon suits large enterprises with many overlapping sources and a strong need for a single integrated core. Kimball suits organisations that want fast, user-friendly analytics and have the discipline to keep dimensions conformed.

Modern practice is usually a hybrid: raw data lands in a lake or staging layer, an integration layer — normalised or Data Vault — handles history and sources, and Kimball-style star schemas form the presentation or gold layer for BI tools.

Note: Interviewers like to hear that without conformed dimensions a Kimball warehouse becomes a set of disconnected data marts — the main risk of the approach.

Both are dimensional models with a central fact table surrounded by dimensions. The difference is how the dimensions are stored.

• Star schema — each dimension is one denormalised table joined directly to the fact. A product dimension holds product name, brand, category and department in one table, repeating category and department values on every product row.
• Snowflake schema — dimensions are normalised into several related tables. Product links to a brand table, which links to a category table, which links to a department table. The diagram branches out like a snowflake.

-- Star: one join gives sales by category
SELECT p.category, SUM(f.net_sales)
FROM fact_sales f JOIN dim_product p ON p.product_key = f.product_key
GROUP BY p.category;

When to choose each:

• Star is the default for BI and self-service analytics. Dimensions are small relative to facts, so the redundancy costs little, especially on compressed columnar storage.
• Snowflaking can make sense for very large dimensions with deep hierarchies, when a sub-dimension is shared by several dimensions (for example a geography table used by customer and store), or when a hierarchy changes often and must be maintained in one place.
• A common compromise is to keep normalised tables in the integration layer and publish flattened star dimensions to users.

Note: The snowflake schema has nothing to do with the Snowflake cloud warehouse product; both schemas run fine on that platform.

A slowly changing dimension (SCD) is a dimension whose attributes change occasionally — a customer moves city, a product changes category, an employee changes department. The SCD type decides whether and how history is kept. Take a customer who moves from Pune to Bengaluru.

Type 2 is the most important and needs extra columns: a surrogate key, the natural key, effective_from, effective_to and an is_current flag. Facts are linked to the surrogate key that was valid when the event happened, so last year's sales stay attributed to Pune even after the customer moves.

Choosing a type is a business decision made per attribute, not per table. In the same customer dimension, a name correction may be Type 1 while city is Type 2.

Note: Type 2 dimensions grow with every change. If one attribute changes very often, a mini-dimension (Type 4) avoids exploding the dimension's row count.

A Type 2 load compares incoming source records with the current dimension rows, closes the rows whose tracked attributes changed, and inserts new versions. Assume a dimension with a surrogate key, the natural key customer_id, tracked attributes, a row_hash of those attributes, effective_from, effective_to and is_current.

-- Step 1: expire current rows whose tracked attributes changed
UPDATE dim_customer d
SET effective_to = CURRENT_DATE - 1, is_current = FALSE
FROM stg_customer s
WHERE d.customer_id = s.customer_id
  AND d.is_current = TRUE
  AND d.row_hash <> s.row_hash;

-- Step 2: insert new versions and brand-new customers
INSERT INTO dim_customer
  (customer_id, city, segment, row_hash, effective_from, effective_to, is_current)
SELECT s.customer_id, s.city, s.segment, s.row_hash,
       CURRENT_DATE, DATE '9999-12-31', TRUE
FROM stg_customer s
LEFT JOIN dim_customer d
  ON d.customer_id = s.customer_id AND d.is_current = TRUE
WHERE d.customer_id IS NULL;

After step 1, changed customers no longer have a current row, so step 2 picks up both changed and new customers in one statement. The surrogate key is generated by an identity column or sequence.

Important details:

• Hash comparison of the tracked columns (for example MD5 of the concatenated values) is simpler and faster than comparing each column, and must handle NULLs consistently.
• Type 1 attributes in the same table are updated in place across all versions in a separate statement.
• Run both steps in one transaction so the dimension is never left with a customer who has no current row.
• Deduplicate staging first; two source rows for one customer in the same batch will create two current rows.
• Fact lookups must pick the version valid on the event date: f.event_date BETWEEN d.effective_from AND d.effective_to.

Many platforms let you do this with a single MERGE, and dbt snapshots generate Type 2 logic automatically.

Note: Use a far-future end date like 9999-12-31 rather than NULL for current rows; it keeps BETWEEN lookups simple and index-friendly.

The grain is the precise definition of what one row in a fact table represents — for example “one row per product per POS receipt line” or “one row per account per day”. Declaring it is the most important decision in dimensional design, because it determines which dimensions can attach, which facts make sense and which questions the model can answer.

Kimball's four-step process puts grain second:

1. Select the business process, such as retail sales or order fulfilment.
2. Declare the grain.
3. Identify the dimensions that are true at that grain.
4. Identify the numeric facts that are true at that grain.

How to choose it:

• Prefer the atomic grain — the lowest level captured by the source system. Detailed data can always be rolled up, but summarised data can never be broken down to answer a new question.
• Start from business questions. If users need basket analysis or product-level margins, a daily store total cannot serve them.
• Consider volume and cost. Very high-volume events, such as clickstream, may justify an additional aggregated fact table for common queries, but keep the atomic one as the foundation.
• Check dimension fit. Every dimension must have exactly one value per fact row. If a promotion applies to only some lines of an order, it belongs at line grain.

The biggest mistake is mixing grains in one table. If an order-level delivery charge is stored on every order line, summing it overstates delivery revenue. The fixes are to allocate the charge across lines using a documented rule, or to model order headers and order lines as separate fact tables.

Note: Write the grain as a sentence in the table's documentation. Most double-counting bugs trace back to someone misunderstanding what one row means.

These are the three fundamental fact table types in dimensional modelling. Each captures a different kind of measurement.

When each is useful:

• Transaction facts answer “what happened?” at the finest detail and are the most flexible. Facts are usually fully additive.
• Periodic snapshots answer “what was the status at the end of each period?” without replaying every transaction. They are dense — a row exists even when nothing happened — and contain semi-additive measures such as balances, which cannot be summed across time.
• Accumulating snapshots track pipeline or workflow performance: how long orders take between steps, how many loans are stuck at underwriting. They carry several date foreign keys, one for each milestone, and are the only type where rows are routinely updated.

Many warehouses keep more than one type for the same process: a transaction fact of inventory movements plus a daily inventory snapshot, for example.

A fourth pattern, the factless fact table, records events or coverage with no numeric measures, such as student attendance or which products were on promotion.

Note: Do not confuse a periodic snapshot fact table with a database or storage snapshot — the former is a modelling pattern, the latter a point-in-time copy of data.

Facts are classified by whether they can be summed meaningfully across dimensions. Getting this wrong produces totals that look plausible but are wrong.

• Additive facts can be summed across every dimension. Examples: sales amount, quantity sold, cost, number of calls. Summing sales across stores, products and days is always valid. These are the most useful facts and should be the default where possible.
• Semi-additive facts can be summed across some dimensions but not across time. Examples: account balance, inventory on hand, headcount. Adding the balances of all accounts on 31 March is fine, but adding one account's balance across 30 days of a month gives a meaningless number. Across time, use the closing value, opening value or average instead.
• Non-additive facts cannot be summed across any dimension. Examples: ratios and percentages such as margin percentage, conversion rate, unit price, and distinct counts. Summing five stores' conversion rates or averaging them gives the wrong overall rate.

How to handle them in the model:

• Store the additive components, not the ratio. Keep profit and sales, and calculate margin percentage in the BI layer as sum of profit divided by sum of sales for whatever slice the user selects.
• Tag semi-additive measures in the semantic layer with the correct aggregation over time, such as last non-empty value, so users cannot sum them by accident.
• Distinct counts must be recalculated from detail for each slice; summing daily unique users does not give monthly unique users.

-- Correct month-end balance per branch
SELECT branch, SUM(balance) AS total_balance
FROM fact_account_daily
WHERE snapshot_date = DATE '2026-03-31'
GROUP BY branch;

Note: In Power BI or SSAS, semi-additive behaviour is implemented with functions such as LASTNONBLANK or CLOSINGBALANCEMONTH rather than a plain SUM.

Both move data from sources into an analytical store; they differ in where and when the transformation happens.

• ETL (Extract, Transform, Load) — data is extracted, transformed in a separate processing engine or ETL server, and only the cleaned, modelled result is loaded into the warehouse. Classic tools: Informatica PowerCenter, IBM DataStage, SSIS, Talend.
• ELT (Extract, Load, Transform) — raw data is loaded into the warehouse or lakehouse first, and transformed there using the warehouse's own SQL engine. Typical stack: Fivetran or Airbyte for loading, dbt for transformation, on Snowflake, BigQuery, Redshift or Databricks.

Choose ETL when:

• Sensitive data must be masked or removed before it lands in the analytical platform, for compliance reasons.
• The target has limited compute, such as a legacy on-premise warehouse.
• Transformations are complex non-SQL work, such as parsing binary files or heavy data science processing.

Choose ELT when: you use a modern cloud warehouse with elastic compute, want raw history available for new use cases and reprocessing, and want transformations version-controlled and tested as SQL code.

In practice many pipelines are hybrid: light cleansing or PII masking in flight, heavy modelling in the warehouse.

Note: ELT shifts cost to warehouse compute. Poorly written transformations that rebuild huge tables every hour can make it more expensive than ETL ever was.

The staging area is an intermediate storage layer where extracted source data lands before it is transformed and loaded into the integrated warehouse or dimensional models. Business users do not query it.

Why it exists:

• Decouples extraction from transformation. Data is pulled from the source quickly and the source connection is released, minimising load on production systems.
• Provides a restart point. If a transformation fails, it can rerun from staged data without extracting from the source again.
• Enables comparison. Change detection, deduplication and reconciliation against the previous load happen here.
• Supports auditing. You can prove exactly what the source delivered in each batch.

Typical processing in or right after staging: data type conversion, trimming and standardising codes, time zone alignment, deduplication, validating mandatory fields, and routing rejected rows to an error table.

Two styles:

• Transient staging — truncated and reloaded each batch. Simple and small, but history is lost.
• Persistent staging — keeps every batch, often append-only. Lets you rebuild downstream models from scratch after a logic change. This is the idea behind the raw or bronze layer in modern stacks.

Best practices:

• Land data as close to the source format as possible; do not apply business rules on the way in.
• Add metadata columns: batch or load ID, load timestamp, source system and source file name.
• Keep one staging table per source table, with consistent naming, for example stg_crm_customer.
• Record row counts per batch and reconcile them with the source extract.
• Restrict access, since staging often contains unmasked personal data.
• Define retention rules so persistent staging does not grow without limit.

Note: Keeping staging free of business logic means that when a rule changes, you only rewrite the transformations, not the extraction.

Data Vault, created by Dan Linstedt, is a modelling method for the integration layer of an enterprise warehouse. It is designed for auditability, full history and easy addition of new sources, rather than for direct end-user querying.

The three core table types:

• Hubs — one row per unique business key, such as a customer number or product SKU, with a hash key, load date and record source. Hubs rarely change and represent core business concepts.
• Links — relationships or transactions between hubs, such as an order linking customer, product and store hub keys. All relationships are modelled as many-to-many, so a change in cardinality never forces a redesign.
• Satellites — the descriptive attributes and their history, attached to a hub or link. A new satellite row is inserted whenever attributes change, with a load date and record source. Different sources usually get separate satellites, such as customer details from the CRM and from the billing system.

hub_customer (customer_hk, customer_number, load_dts, record_source)
sat_customer_crm (customer_hk, load_dts, name, city, segment, hash_diff, record_source)
link_order (order_hk, customer_hk, product_hk, load_dts, record_source)

Strengths:

• Insert-only loading, so every historical state is auditable — valuable in banking and insurance.
• New sources are added as new satellites without restructuring existing tables.
• Hash keys allow hubs, links and satellites to be loaded in parallel.

Weaknesses:

• Many more tables and joins, so direct queries are complex and slow.
• It needs a presentation layer — star schemas or information marts — built on top for BI.
• Requires modelling discipline and automation tools such as dbt packages or specialised generators.

Note: Data Vault complements dimensional modelling rather than replacing it: vault for integration and history, stars for consumption.

These describe how an OLAP system stores and retrieves the multidimensional data that analysts slice, dice and drill into.

• MOLAP (Multidimensional OLAP) — data and pre-calculated aggregations are stored in a proprietary multidimensional cube format. Examples: SSAS Multidimensional, Oracle Essbase.
  • Very fast queries, because aggregates are precomputed.
  • Rich calculations such as time intelligence and allocations.
  • Limited scalability for large, high-cardinality data; cube processing takes time, so data is less fresh.
• ROLAP (Relational OLAP) — data stays in relational star schemas, and the OLAP engine generates SQL at query time. Examples: MicroStrategy, and most modern BI tools running live queries against cloud warehouses.
  • Scales to very large detail data and is always current.
  • Slower for complex aggregations unless the warehouse has aggregate tables or materialised views.
• HOLAP (Hybrid OLAP) — aggregations are stored in the cube for speed, while detailed data stays in the relational database and is fetched only on drill-down.
  • Balances speed and storage, but adds architectural complexity.

Where things stand today: in-memory columnar models such as Power BI import mode and SSAS Tabular have largely replaced classic MOLAP cubes, while powerful cloud warehouses make ROLAP-style direct query practical at scale. Power BI's import, DirectQuery and composite modes are close modern parallels of MOLAP, ROLAP and HOLAP.

Note: Relate the terms to tools you have used. Interviewers value seeing the concept mapped onto a real stack.

Partitioning splits a large table into separate segments based on the value of a column, most often a date. The engine keeps metadata about each partition, so a query filtering on that column reads only the relevant partitions — a technique called partition pruning.

Benefits:

• Faster and cheaper queries. A query for last week's sales reads 7 daily partitions instead of five years of data. On BigQuery this directly reduces the bytes billed.
• Easier maintenance. Reload or replace a single day's partition, or drop partitions past their retention period, instead of running huge deletes.
• Simpler incremental loads. Loads can overwrite only the partitions that changed, which is naturally idempotent.

Common partitioning methods: range (by date or number), list (by region or country) and hash (spreading rows evenly by a key).

Choosing a partition key:

• Pick the column most queries filter on — usually the event or transaction date.
• Aim for a moderate number of reasonably large partitions. Partitioning by a high-cardinality column such as customer ID creates huge numbers of tiny partitions and small files, which hurts performance.
• Match the granularity to the data volume: daily for large event tables, monthly for smaller ones.
• Use clustering or sort keys for the next most common filters, such as store or product, within each partition.

Pitfalls:

• Wrapping the partition column in a function, such as WHERE EXTRACT(YEAR FROM order_date) = 2026, may prevent pruning. Filter on the raw column with a range instead.
• Queries with no partition filter scan everything; BigQuery can enforce a required partition filter.

Platforms differ: BigQuery and Databricks use explicit partitioning, Snowflake divides data into micro-partitions automatically and relies on optional clustering keys, and Redshift relies mainly on sort keys and zone maps.

Note: Partition by how data is queried, not by how it arrives, although in most warehouses the two are the same date.

Snowflake is a cloud-native warehouse built on a multi-cluster, shared-data architecture that fully separates storage from compute. Traditional on-premise warehouses tie storage and processing to the same fixed hardware.

Three layers:

• Storage — data is automatically organised into compressed, columnar micro-partitions held in cloud object storage (on AWS, Azure or Google Cloud). Snowflake records min and max values for each micro-partition, enabling pruning without manual indexes.
• Compute — virtual warehouses are independent compute clusters, sized from X-Small upwards. Each team or workload can have its own warehouse, so a heavy data science job does not slow the finance dashboards. Warehouses auto-suspend when idle and resume on demand; multi-cluster warehouses add clusters for high concurrency.
• Cloud services — metadata, query optimisation, transactions, security and access control.

Key features interviewers expect you to know:

• Time Travel — query or restore data as it was at an earlier point, for one day by default and up to 90 days on higher editions; UNDROP TABLE recovers dropped objects.
• Fail-safe — a further seven-day recovery period managed by Snowflake for disaster recovery.
• Zero-copy cloning — instantly clone a database or table for testing without duplicating storage.
• Semi-structured data — JSON and Parquet loaded into VARIANT columns and queried with SQL.
• Snowpipe, Streams and Tasks — continuous loading, change tracking and scheduled SQL.
• Secure data sharing — share live data with other accounts without copying it.
• Result cache — identical repeated queries return instantly without using compute.

Cost model: storage is billed per terabyte per month, and compute in credits per second while a warehouse runs, with a 60-second minimum each time it starts. Right-sizing and auto-suspend are therefore the main cost levers.

Note: Scaling up (a bigger warehouse) speeds up complex queries; scaling out (more clusters) handles more concurrent users. Interviewers often ask for this distinction.

BigQuery is Google Cloud's serverless data warehouse. There are no clusters to manage: storage and compute are separate, data is stored in a compressed columnar format, and queries are executed by the Dremel engine across thousands of workers, with compute measured in slots.

Pricing models:

• On-demand — you pay for the bytes each query scans.
• Capacity-based — you reserve slots through editions and pay for that capacity, regardless of bytes scanned.

Under on-demand pricing, reducing bytes scanned is the main lever for both cost and speed.

Cost and performance practices:

• Select only the columns you need. Because storage is columnar, SELECT * on a wide table can cost many times more than selecting three columns. LIMIT generally does not reduce bytes billed.
• Partition large tables by date or ingestion time, and set require_partition_filter so nobody accidentally scans everything.
• Cluster on frequently filtered or joined columns, such as customer or country, so blocks are skipped within partitions.
• Check before running. The console and a dry run show the bytes a query will process; set a maximum bytes billed limit and custom quotas per user or project.
• Precompute with materialised views or aggregate tables for dashboards that run the same heavy query repeatedly, and use BI Engine for in-memory acceleration.
• Use nested and repeated fields (STRUCT and ARRAY) to store related data together, such as order lines inside an order, reducing expensive joins.
• Filter early and avoid unnecessary cross joins; for joins, filter large tables before joining.
• Previewing a table in the console is free, unlike running a query to look at sample rows.

Monitor spend with the INFORMATION_SCHEMA.JOBS views, grouping by user and query to find the most expensive workloads.

Note: A single careless scheduled query on a multi-terabyte table can dominate a month's bill, so cost guardrails matter as much as tuning.

Amazon Redshift is a massively parallel processing (MPP) warehouse. A leader node plans queries and compute nodes, each divided into slices, store and process the data in parallel. How rows are spread across slices and ordered on disk has a large effect on performance.

Distribution styles decide which slice each row is stored on:

• KEY — rows with the same value in the distribution column go to the same slice. Distributing a large fact table and its largest dimension on the same join key, such as customer_id, lets them join locally without moving data across the network.
• ALL — a full copy of the table is stored on every node. Ideal for small, slowly changing dimension tables that join to everything.
• EVEN — rows are spread round-robin. Suitable when a table has no dominant join key.
• AUTO — Redshift chooses and adjusts the style as the table grows; the default for new tables.

Choosing a distribution key: it should be frequently used in joins and have high cardinality with evenly spread values. A skewed key — for example a customer column where one wholesale account owns 30 percent of rows — overloads one slice, and every query waits for it.

Sort keys determine the physical order of rows. Redshift keeps min and max values for each block (zone maps), so filters on the sort key skip blocks entirely.

• Compound sort key — sorts by columns in order; best when queries filter on the leading column, typically a date.
• Interleaved sort key — gives equal weight to several columns; useful for varied filters but costly to maintain, so it is used less often now.

Maintenance: run ANALYZE to keep statistics current and VACUUM to re-sort and reclaim space after heavy updates and deletes, although automatic table optimisation and auto-vacuum now handle much of this.

Note: Check the SVV_TABLE_INFO system view for skew and unsorted percentages. It quickly reveals poorly chosen keys.

A data lakehouse combines the low-cost, flexible storage of a data lake with the reliability and SQL performance of a data warehouse, using one copy of the data for BI, data science and machine learning.

The two older patterns:

• Data lake — files of any format (CSV, JSON, Parquet, images, logs) stored cheaply in object storage such as Amazon S3, Azure Data Lake Storage or Google Cloud Storage. Schema is applied when data is read. Flexible and cheap, but historically lacked transactions, quality enforcement and fast SQL, so many became unmanaged “data swamps”.
• Data warehouse — structured, modelled data with schema enforced on write, ACID transactions, strong governance and fast SQL. Excellent for BI, but traditionally more expensive and less suited to unstructured data and ML workloads.

What makes a lakehouse possible is an open table format — Delta Lake, Apache Iceberg or Apache Hudi — layered over Parquet files in the lake. These formats add:

• ACID transactions, so concurrent writes and reads do not corrupt data.
• Schema enforcement and schema evolution.
• Time travel to query earlier versions of a table.
• Row-level updates, deletes and MERGE, essential for CDC and privacy deletion requests.
• File statistics for pruning, plus compaction to fix small-file problems.

Platforms include Databricks, as well as Spark, Trino and cloud warehouses such as Snowflake and BigQuery that can now read and write Iceberg tables.

Note: A lakehouse still needs modelling and governance. Open formats solve reliability, not the organisation of the data.

The medallion architecture, popularised by Databricks, organises a lake or lakehouse into three layers of increasing quality and business readiness. It is similar in spirit to the classic staging, integration and presentation layers of a warehouse.

Typical work in each transition:

• Bronze to silver — parse JSON, cast data types, remove duplicates, apply data quality rules and quarantine bad rows, standardise codes and time zones, handle CDC merges, and mask sensitive fields.
• Silver to gold — apply business logic and metric definitions, build star schemas or wide denormalised tables, and pre-aggregate for dashboards.

Benefits:

• Raw data is always kept, so any downstream layer can be rebuilt when logic changes or bugs are found.
• Clear quality expectations and ownership at each stage.
• Lineage is easy to follow, and access can be tightened as data becomes more sensitive or curated.

Common pitfalls:

• Treating the layers as a data model. Medallion says where data sits, not how it is modelled; gold still needs proper dimensional design.
• Letting business users query bronze or silver directly, which spreads inconsistent logic.
• Adding more and more intermediate layers, which raises cost and latency without adding value.

Note: Interviewers often ask where a star schema fits. The answer is usually the gold layer, built from conformed silver entities.

Change data capture (CDC) identifies inserts, updates and deletes in a source system so the warehouse can load only what changed, instead of copying entire tables every time.

Log-based CDC is the modern standard for high-volume sources, using tools such as Debezium with Kafka, AWS DMS, Oracle GoldenGate, Fivetran or Qlik Replicate.

Applying the changes in the warehouse:

• Land change events in raw or bronze storage with operation type, source timestamp and log position.
• Deduplicate to the latest change per key within the batch, ordered by log position rather than arrival time.
• Apply with a MERGE: update matching keys, insert new ones, and either delete or soft-delete rows for delete events.
• Feed the same events into Type 2 dimension logic when history is required.
• Plan for schema changes, initial full loads and recovery if the log position is lost.

Note: Always ask how deletes are handled. It is the most common gap in timestamp-based incremental loads and leads to phantom records in the warehouse.

Source systems do not always deliver data in a convenient order. Two situations must be designed for explicitly.

1. Early-arriving facts (late-arriving dimension members). A sales transaction arrives for customer C-9912, but the CRM feed has not yet delivered that customer. The fact cannot find a surrogate key. Options:

• Inferred member (recommended). Insert a placeholder row in the customer dimension with the natural key and default attributes such as Unknown, flagged as inferred, and link the fact to it. When the real customer record arrives, update the inferred row in place (Type 1), clear the flag, and apply normal SCD rules from then on. Facts keep their key, so nothing needs reloading.
• Suspense table. Park unmatched facts and retry on later loads. Keeps dimensions clean but delays facts and needs monitoring.
• Unknown member. Map to a generic −1 row. Simple, but the link to the real customer is lost unless the fact is reprocessed later.

2. Late-arriving facts. A transaction from 5 September arrives on 12 September, perhaps from an offline store. Problems and fixes:

• Look up the correct dimension version. With Type 2 dimensions, match on the event date, not the load date, so the fact links to the version valid on 5 September.
• Load into the right partition by event date, and make incremental logic look back over a window of days rather than only processing yesterday.
• Refresh derived tables. Aggregates, periodic snapshots and accumulating snapshots covering 5 September must be recomputed.
• Communicate restatements if closed periods change; some businesses post late facts to the current period instead, by agreed policy.

Keep both an event date and a load date on fact rows to support auditing and reprocessing.

Note: Track how many inferred members remain unresolved. A growing count signals a broken or delayed dimension feed.

A role-playing dimension is a single physical dimension referenced several times by the same fact table, each time with a different meaning. The classic example is the date dimension: an order fact may have an order date, a ship date and a delivery date, all pointing to the same dim_date table. Other examples are an airport dimension used as origin and destination, or an employee dimension used as sales representative and approving manager.

Implementation in the warehouse:

• The fact table holds one foreign key per role: order_date_key, ship_date_key, delivery_date_key.
• There is only one physical date table, so there is no duplicated data to maintain.
• Create a view per role with renamed columns, such as dim_ship_date with ship_month and ship_year, so users are never confused about which date they are filtering.

SELECT od.month_name AS order_month,
       sd.month_name AS ship_month,
       COUNT(*) AS orders
FROM fact_orders f
JOIN dim_date od ON od.date_key = f.order_date_key
JOIN dim_date sd ON sd.date_key = f.ship_date_key
GROUP BY od.month_name, sd.month_name;

The same table is joined twice with different aliases, one per role.

Implementation in BI tools:

• In Power BI, only one relationship between two tables can be active. Either keep one active relationship and use USERELATIONSHIP inside measures for the other roles, or import the date table once per role so each has its own active relationship and its own slicers.
• In semantic layers such as LookML or dbt metrics, define each role as a separately named join or dimension.

Why it matters: business questions often depend on the role — sales booked in March versus shipped in March can differ significantly at quarter-end, so reports must make the chosen date explicit.

Note: Mark the main date table as the date table in Power BI so time intelligence functions work correctly for every role.

Traditional transactional databases are row-oriented: all the columns of a row are stored together, which is ideal for reading or updating a single order. Analytical warehouses such as Redshift, BigQuery, Snowflake and Synapse, and file formats such as Parquet and ORC, are column-oriented: all the values of each column are stored together.

Why columnar suits analytics:

• Read only what you need. A query summing revenue by month from a 120-column fact table reads just the date and revenue columns — a small fraction of the data.
• Much better compression. Values in one column share a type and often repeat, so encodings such as dictionary, run-length and delta compression shrink data many times over. Less data on disk means less I/O and lower storage cost.
• Block-level pruning. Min and max statistics per block or micro-partition let the engine skip blocks that cannot match a filter.
• Vectorised execution. Processing batches of values from one column at a time makes efficient use of modern CPUs.
• Late materialisation. Filters are applied on a few columns first, and the remaining columns are fetched only for rows that survive.

Drawbacks:

• Single-row operations are expensive. Inserting or updating one row touches many separate column files, so columnar stores favour bulk loads and batch or micro-batch updates over row-at-a-time writes.
• Fetching whole rows is slower. SELECT * on a wide table loses most of the benefit.
• Frequent small updates cause fragmentation, needing compaction, vacuuming or re-clustering.
• Not suitable for OLTP workloads with high volumes of small, concurrent transactions.

Practical implications: load in batches, select only required columns, keep wide denormalised tables since unused columns cost little to store and nothing to skip, and use MERGE in batches rather than looping through single-row updates.

Note: Hybrid systems now combine both — row stores for recent transactional writes and column stores for analytics — but the underlying trade-off remains.

An incremental load processes only new or changed data since the last run. A load is idempotent when running it again for the same period produces exactly the same result — essential because loads fail and get retried, and backfills rerun old periods.

Common incremental patterns:

• High-water mark — load rows whose updated_at is later than the maximum already loaded.
• CDC stream — apply inserts, updates and deletes from a change log.
• Partition replacement — recompute and overwrite whole date partitions, such as the last three days.

Techniques that make loads idempotent:

• MERGE on the business key instead of a plain INSERT, so a rerun updates existing rows rather than adding duplicates.
• Delete-then-insert or insert-overwrite by partition within one transaction, so reprocessing a day always replaces that day completely.
• Deterministic windows. Parameterise each run with an explicit start and end time from the orchestrator, rather than using the current time inside the SQL, so a rerun of Tuesday's job processes Tuesday's data.
• Deduplicate staging with ROW_NUMBER() to keep only the latest record per key before merging.
• Look-back window of a few days to catch late-arriving records; MERGE makes the overlap harmless.
• Record watermarks only after success, in a control table, so a failed run does not advance the watermark.

MERGE INTO fact_orders t
USING (
  SELECT * FROM stg_orders
  QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1
) s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET status = s.status, amount = s.amount, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, status, amount, updated_at)
  VALUES (s.order_id, s.status, s.amount, s.updated_at);

QUALIFY is supported in Snowflake, BigQuery and Databricks; elsewhere use a subquery. Schedule a periodic full reconciliation, since incremental logic can drift from the source over months.

Note: Test idempotency directly: run the same load twice in a development environment and confirm row counts and totals do not change.

Data governance is the set of roles, policies and processes that make warehouse data trustworthy, secure, compliant and easy to find. It is not only a tool; it is about clear ownership and agreed rules.

Core components:

• Ownership and stewardship — every important dataset has a business owner who decides definitions and access, and a steward who looks after quality.
• Data catalogue and business glossary — searchable descriptions of tables, columns and metrics, with certified datasets clearly marked. Tools include Unity Catalog, Collibra, Alation, DataHub and Microsoft Purview.
• Lineage — where data comes from and which reports depend on it, for impact analysis and audits.
• Data quality — defined rules, automated tests, and SLAs for freshness and accuracy.
• Classification — tagging personal and sensitive data such as Aadhaar and PAN numbers, phone numbers, salaries and health information.
• Retention and deletion — how long data is kept and how deletion requests are honoured, in line with regulations such as India's Digital Personal Data Protection Act 2023 and sector rules from RBI or IRDAI.
• Auditing — logs of who accessed what and when.

Implementing access control:

• Role-based access control — grant privileges to roles such as finance analyst or data engineer, never directly to individuals, following least privilege.
• Column-level security and dynamic masking — show the full value only to authorised roles, and a masked version such as XXXXXX1234 to everyone else. Snowflake masking policies and BigQuery policy tags do this natively.
• Row-level security — filter rows by user attributes, so each regional manager sees only their region.
• Tokenisation or hashing of identifiers, so analysts can join and count without seeing raw values.
• Separate environments — no production personal data in development without masking.
• Periodic access reviews to remove stale permissions.

Note: Governance works best when built into pipelines — tags, tests and documentation defined in code — rather than maintained by hand in spreadsheets.

dbt (data build tool) handles the T in ELT. It lets analysts and engineers write transformations as SQL SELECT statements, and dbt compiles and runs them inside the warehouse — Snowflake, BigQuery, Redshift, Databricks and others. It does not extract or load data; tools such as Fivetran, Airbyte or custom pipelines do that, and an orchestrator such as Airflow or Dagster often schedules everything.

Core concepts:

• Models — each SQL file defines one table or view. The ref() function references other models, and dbt builds a dependency graph (DAG) so models run in the right order.
• Materialisations — choose how each model is built: view, table, incremental (only new or changed rows are processed) or ephemeral (inlined as a CTE).
• Sources and freshness — declare raw tables and alert when they have not been updated recently.
• Tests — built-in tests such as unique, not_null, accepted_values and relationships, plus custom SQL tests, run on every build.
• Documentation — descriptions in YAML generate a documentation site with column-level detail and a lineage graph.
• Snapshots — implement Type 2 slowly changing dimension history automatically.
• Jinja macros and packages — reusable logic, and community packages such as dbt_utils.

-- models/marts/fct_orders.sql
SELECT o.order_id, o.order_date, c.customer_key, o.amount
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('dim_customer') }} c ON c.customer_id = o.customer_id

Why it became standard: it brings software engineering practice to analytics — version control in Git, code review, separate development and production environments, CI checks that build and test changed models before they are merged, and modular, reusable logic. Typical projects are layered as staging, intermediate and marts, which maps neatly onto bronze, silver and gold.

dbt Core is open source and run from the command line; dbt Cloud adds a hosted IDE, scheduling and a semantic layer.

Note: dbt runs everything on warehouse compute, so poorly designed models directly raise your warehouse bill. Incremental models and sensible materialisations matter.

An operational data store (ODS) is an integrated database that consolidates current data from several operational systems to support operational reporting and day-to-day decisions. It sits between the transactional sources and the data warehouse.

Typical example: a bank's customer service team needs a single up-to-date view of a customer's accounts, cards, loans and recent complaints, which live in separate core systems. An ODS combines them in near real time so an agent can see everything on one screen.

How it relates to the warehouse: the ODS can act as a cleansed, integrated source feeding the warehouse, so integration logic is written once. In Inmon-style architectures it is a named component alongside the enterprise warehouse.

In modern stacks the ODS role is often played by a read replica, a CDC-fed silver layer in a lakehouse, or a real-time analytics database. The concept — integrated, current, operational data — still matters even when the term is not used.

Note: An ODS answers “what is the situation right now?”, while a warehouse answers “how has it changed over time and why?”