Data warehousing and ETL

In short

A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data built for analysis and decision support, kept separate from the transactional systems that run the business. ETL (Extract, Transform, Load) is the process that pulls data out of source systems, cleans and conforms it, and loads it into the warehouse; ELT flips the order and transforms inside the warehouse engine. The topic carries steady marks in university "Data Warehousing and Data Mining" / "Data Engineering" papers and appears in GATE DA (data preprocessing, schemas) and in nearly every data-engineering or analytics interview. Indian employers — TCS, Infosys, Wipro, Accenture, Cognizant, LTIMindtree, Flipkart, Zomato, Paytm, HDFC Bank, Jio, Fractal, ZS — hire heavily for ETL and dimensional modelling, and RBI/SEBI/UIDAI-regulated data stores make the governance side examinable too.

Explained

Why a warehouse: OLTP versus OLAP

• OLTP (online transaction processing): the operational system — order entry, UPI payments, ticket booking. Many small, short, concurrent read-write transactions; highly normalised (3NF) to avoid update anomalies; measured in transactions/second.
• OLAP (online analytical processing): few but huge read-mostly queries that scan millions of rows and aggregate — "monthly GMV by state and category for three years". Denormalised, measured in query latency and scan throughput.
• Running analytics on the OLTP database starves the live application of CPU, locks rows, and cannot answer historical questions because operational systems overwrite old values. Hence a separate store.
• Inmon's four properties of a warehouse (the standard definition to quote):
  1. Subject-oriented — organised around business subjects (customer, sales, claim), not around applications.
  2. Integrated — inconsistent codes, units and encodings from many sources are conformed to one standard (e.g. "M/F", "1/2", "Male/Female" → one code; ₹ vs paise → one unit).
  3. Time-variant — every row carries a time context; the warehouse keeps 5-10 years of history, not just the current state.
  4. Non-volatile — loaded and read, not updated transaction-by-transaction by users; changes arrive as new loads.
• Operational Data Store (ODS): an integrated, *current-valued*, volatile store used for operational reporting; sits between sources and the warehouse.
• Data mart: a subject- or department-scoped slice (Sales mart, Risk mart). Dependent mart is fed from the warehouse; independent mart is built straight from sources (fast, but breeds silos).

Architectures and reference models

• Single-tier: reports run directly on sources — minimal, rarely used in practice.
• Two-tier: sources → warehouse → BI tools. Simple, but mixes staging with serving.
• Three-tier (standard, draw this in exams):
  1. Bottom tier — source systems (OLTP databases, ERP, CRM, flat files, APIs, logs) plus the staging area where extracted raw data lands and is cleaned.
  2. Middle tier — the warehouse database and the OLAP server (ROLAP/MOLAP/HOLAP) that exposes cubes and aggregates.
  3. Top tier — query, reporting, dashboard and mining tools (Power BI, Tableau, Qlik, Excel, SQL clients).
• Metadata repository spans all tiers: technical metadata (schemas, data types, load jobs), business metadata (definitions, owners, KPI formulae), and operational metadata (load times, row counts, rejects). A warehouse without metadata is unmaintainable.
• Two classical design philosophies:
  • Inmon (top-down): build one enterprise warehouse in normalised 3NF (the Corporate Information Factory), then spin off dependent marts. Consistent and extensible; slow and costly to start.
  • Kimball (bottom-up): build dimensional data marts first for high-value business processes, tied together by a bus architecture of conformed dimensions (shared, identical Customer and Date dimensions). Quick value; needs discipline to avoid inconsistency.
  • Data Vault 2.0 (asked in modern interviews): Hubs (business keys), Links (relationships) and Satellites (descriptive, historised attributes) — auditable and insert-only, loaded in parallel, then presented as star schemas.

Dimensional modelling

• Fact table: the measurements of a business process. Columns = foreign keys to dimensions + measures (numeric) + degenerate dimensions (e.g. invoice number). Very deep, narrow.
• Dimension table: the descriptive "who, what, where, when, how" — textual attributes used for filtering and grouping. Wide, shallow, denormalised.
• Grain: the meaning of one fact row, declared *before* anything else — "one row per order line per day". Mixing grains is the classic design error.
• Measure additivity:
  • Additive — summable across all dimensions (sales amount, quantity).
  • Semi-additive — summable across some dimensions but not time (account balance, stock on hand → average or closing-balance over time).
  • Non-additive — ratios and percentages (margin %, conversion rate); store numerator and denominator and compute the ratio after aggregation.
• Fact table types: transaction (one row per event), periodic snapshot (one row per entity per period — daily balance), accumulating snapshot (one row per process instance with milestone dates updated as it progresses — loan application), factless fact (events with no measure — student attendance, promotion coverage).
• Schemas:
  • Star schema — one fact table surrounded by denormalised dimensions. Fewest joins, fastest, easiest for users; stores redundant text.
  • Snowflake schema — dimensions normalised into sub-dimension tables (Product → Brand → Category). Saves space, more joins, slower queries.
  • Fact constellation / galaxy schema — multiple fact tables sharing conformed dimensions (Sales fact and Shipments fact both using Date, Product, Store).
• Surrogate key: a meaningless integer primary key for each dimension row, independent of the source's natural/business key. Needed to absorb source-key changes, handle multiple sources, support history (SCD Type 2), and keep joins narrow and fast.
• Date dimension: always a physical table, pre-populated with calendar attributes plus Indian-specific flags — financial year (1 April to 31 March), quarter FY1-FY4, GST return period, festival/holiday flags, weekend definition. Never derive these from date functions at query time.
• Hierarchies: natural (Day → Month → Quarter → Year; Pincode → City → State → Zone), used for drill-down and roll-up.

Slowly Changing Dimensions (SCD)

• Type 0 — retain original; attribute never changes (date of birth, original credit score).
• Type 1 — overwrite; no history kept. Use for corrections of genuine errors (misspelt name).
• Type 2 — add a new row with a new surrogate key plus effective_from, effective_to, is_current flag and optionally a version number. Preserves full history; the standard answer. Fact rows joined to the key that was current at the event date give correct "as-was" reporting.
• Type 3 — add a column (current_state, previous_state); keeps limited history, only one prior value.
• Type 4 — move the volatile attributes to a separate mini-dimension or history table.
• Type 6 — hybrid 1+2+3: Type 2 rows plus a current-value column, so the same fact can be reported "as-was" and "as-is".
• Rapidly changing dimension: a frequently changing attribute inside a big dimension; split it into a mini-dimension (banded values, e.g. income band) to avoid row explosion.

The ETL process, step by step

1. Extract — read from sources: full extract (whole table), incremental extract (by last_modified timestamp or an increasing ID), or CDC (change data capture) from the database transaction log/binlog (lowest impact, captures deletes). Log-based CDC is preferred for large OLTP tables; trigger-based CDC is accurate but slows the source.
2. Land in staging — write raw, untransformed data to a staging schema or object store. Staging is transient, has no indexes or constraints, and exists so extraction is never repeated and so transforms are restartable.
3. Transform — in order: data-type casting; cleansing (nulls, defaults, trimming, case); standardisation (addresses, pincodes, state names, GSTIN format, 10-digit mobile normalisation); de-duplication and record matching/survivorship; validation against business rules with rejects routed to an error table; derivation of calculated columns; aggregation; surrogate key lookup/generation; SCD handling.
4. Load — dimensions first (so fact lookups find keys), then facts. Initial/full load once, then incremental loads. Load patterns: insert-only append, upsert/merge (MERGE statement), truncate-and-reload for small tables, partition exchange/swap for large daily partitions.
5. Post-load — rebuild indexes and statistics, refresh materialized views and aggregates, run reconciliation counts, publish the batch as available, update the metadata/audit log.
6. Audit columns on every table: batch_id, source_system, load_timestamp, record_hash. A hash (checksum) of attributes is the cheap way to detect a changed dimension row.
7. Idempotence and restartability: a job re-run for the same batch must produce the same result. Achieved with batch IDs, high-water marks, delete-then-insert of the batch's own partition, and transactional or atomic swap loads.
8. Error handling: reject file/table + severity (warn, reject row, abort batch), row counts reconciled source vs target, and alerting. Late-arriving facts (event date older than the current batch) must look up the dimension version valid at the event date; early-arriving facts need an inferred ("late-arriving dimension") placeholder row.

ETL versus ELT, and the engineering side

• ETL: transform on a dedicated engine (Informatica PowerCenter, IBM DataStage, Talend, SSIS, Pentaho, or Python/PySpark) before loading. Good when the target is costly to compute on, when heavy cleansing or masking must happen before data lands, or when the transform logic must be reusable.
• ELT: load raw into a powerful MPP/columnar warehouse and transform with SQL inside it (the dbt pattern — models, tests, lineage). Exploits scalable compute and keeps raw data for re-processing; now the default in cloud projects.
• Push-down optimisation: an ETL tool translating its transforms into SQL executed on the source or target engine.
• Performance levers: partitioning (usually by date), columnar storage with compression, clustering/sort keys, bulk/batch loaders instead of row-by-row DML, parallel pipelines, disabling constraints/indexes during big loads, avoiding row-level cursors, and pushing filters to the extract.
• Aggregates and views: summary/aggregate fact tables and materialized views pre-compute common roll-ups; query rewrite silently redirects a detail query to the aggregate.
• OLAP storage options: MOLAP (pre-aggregated multidimensional cube — fastest, limited scale, data explosion), ROLAP (relational tables, scales to huge data, slower), HOLAP (detail relational + aggregates in cube).
• OLAP operations: roll-up, drill-down, slice (fix one dimension), dice (sub-cube on several dimensions), pivot/rotate, drill-across (two facts via conformed dimensions), drill-through (to the underlying detail row).
• Data quality dimensions examined: accuracy, completeness, consistency, timeliness, validity, uniqueness; plus data profiling before design and reconciliation after load.
• Governance and Indian practice: the Digital Personal Data Protection Act, 2023 governs personal data in warehouses — purpose limitation, data minimisation, storage limitation (erase when the purpose is served), consent records, and breach notification; the DPDP Rules were notified in 2025 with phased implementation, so check the current commencement dates before quoting deadlines. Hence masking/tokenisation of PII in staging, column-level access control, row-level security, retention and purge jobs, and audit trails. Sector rules add to this: RBI's directions require certain payment-system data to be stored in India, SEBI and RBI prescribe records retention (commonly 8 years for many securities-market records — verify the specific circular), and GST/e-invoice data feeds warehouses with its own return periods. Financial reporting dimensions follow the 1 April-31 March financial year.

Key points

• Data warehouse - subject-oriented, integrated, time-variant, non-volatile store for decision support (Inmon's definition).
• OLTP vs OLAP - many short read-write transactions and 3NF versus few large read-mostly aggregating queries and denormalised models.
• Data mart - departmental subset; dependent (from warehouse) is safe, independent breeds silos.
• Three-tier architecture - sources + staging, warehouse + OLAP server, front-end BI tools, with metadata across all three.
• Inmon vs Kimball - top-down normalised enterprise warehouse versus bottom-up dimensional marts linked by conformed dimensions.
• Grain - what one fact row means; declared first, never mixed within a fact table.
• Fact table - foreign keys plus numeric measures; types: transaction, periodic snapshot, accumulating snapshot, factless.
• Additivity - additive, semi-additive (not over time, e.g. balances), non-additive (ratios).
• Star vs snowflake - denormalised dimensions and fewer joins versus normalised dimensions, less redundancy, more joins.
• Fact constellation - several fact tables sharing conformed dimensions; enables drill-across.
• Surrogate key - integer key owned by the warehouse; decouples from source keys and enables SCD Type 2 history.
• SCD Type 1/2/3 - overwrite / new row with effective dates and current flag / extra previous-value column.
• ETL order - extract, stage, transform (cleanse, standardise, de-duplicate, validate, derive, key lookup), load dimensions then facts.
• CDC - capture inserts, updates and deletes from the transaction log for cheap incremental extraction.
• ELT - load raw first, transform with SQL inside an MPP warehouse (dbt pattern); keeps raw for re-processing.
• Idempotent loads - batch IDs, high-water marks and partition swaps so a re-run is safe.
• MOLAP/ROLAP/HOLAP - cube, relational, and hybrid OLAP storage; operations are roll-up, drill-down, slice, dice, pivot.
• DPDP Act, 2023 - consent, purpose limitation and storage limitation drive PII masking, access control and purge jobs in Indian warehouses.

Formulas and examples

Example 1 — Fact table sizing and storage. A retail chain has 1,200 stores, each averaging 900 bills a day with 4 line items per bill. One fact row per line item is kept for 3 years (1,095 days).

• Rows/day = 1,200 × 900 × 4 = 4,320,000.
• Rows in 3 years = 4,320,000 × 1,095 = 4,730,400,000 rows (≈ 4.73 billion).
• Row width: 6 foreign keys × 4 B = 24 B, 4 measures × 8 B = 32 B, audit columns 16 B → 72 B.
• Raw size = 4.7304 × 10⁹ × 72 B = 340.6 × 10⁹ B ≈ 317 GiB (340.6 × 10⁹ ÷ 1.0737 × 10⁹).
• At a columnar compression ratio of 4:1 → ≈ 79 GiB on disk, which is why columnar storage and date partitioning are mandatory here.

Example 2 — SCD Type 2 row growth. A bank has N = 4,00,00,000 (4 crore) customers. On average c = 0.3 attribute changes per customer per year (address, mobile, risk band), history kept t = 7 years.

• Rows ≈ 4,00,00,000 × (1 + 0.3 × 7) = 4,00,00,000 × 3.1 = 12,40,00,000 rows (12.4 crore).
• If the dimension row is 400 B wide, raw size = 1.24 × 10⁸ × 400 = 4.96 × 10¹⁰ B ≈ 46.2 GiB.
• With Type 1 (overwrite) it would be 4 crore rows ≈ 14.9 GiB — but every historical report would be wrong, because old facts would join to today's attribute values.

Example 3 — Cuboids in a cube lattice. A sales cube has 4 dimensions: Time, Product, Store, Customer.

• No hierarchies → cuboids = 2⁴ = 16 (from the base cuboid to the apex "ALL").
• With hierarchies — Time (day, month, quarter, year = 4 levels), Product (item, brand, category = 3), Store (store, city, state = 3), Customer (customer, segment = 2): total = (4+1) × (3+1) × (3+1) × (2+1) = 5 × 4 × 4 × 3 = 240 cuboids.
• Materialising all 240 is impossible in practice; only the most-queried aggregates are built — this is the cube materialisation / view-selection problem.

Example 4 — Incremental load versus full reload. A source order table has 50,00,000 rows; 1.8% change daily. Load throughput is 25,000 rows/s and the nightly window is 02:00-04:00 (7,200 s).

• Full reload: 50,00,000 ÷ 25,000 = 200 s — comfortably inside the window today.
• Changed rows/day = 50,00,000 × 0.018 = 90,000 → incremental load = 90,000 ÷ 25,000 = 3.6 s.
• If the table grows 10× to 5 crore rows: full reload = 2,000 s (still fits, headroom = (7,200 − 2,000)/7,200 ≈ 72%), incremental = 36 s. But a full reload destroys SCD history and re-reads the source, so CDC is still preferred.

Example 5 — Semi-additive measure. A savings account has daily closing balances over 5 days: ₹12,000, ₹9,500, ₹9,500, ₹40,000, ₹18,000.

• Summing over time = ₹89,000 — meaningless; the customer never had ₹89,000.
• Correct aggregates: closing balance = ₹18,000; average daily balance = 89,000 ÷ 5 = ₹17,800 (the quarterly version of this is what banks use for minimum-balance charges).
• Across accounts at a point in time the balance *is* additive: 3 accounts holding ₹18,000 + ₹5,000 + ₹7,000 = ₹30,000 of deposits.

-- SCD Type 2 merge: expire the changed row, insert the new version
UPDATE dim_customer d
   SET effective_to = :batch_date - 1, is_current = 'N'
 FROM stg_customer s
WHERE d.customer_id = s.customer_id
  AND d.is_current  = 'Y'
  AND d.attr_hash  <> s.attr_hash;

INSERT INTO dim_customer
      (customer_sk, customer_id, name, city, risk_band,
       attr_hash, effective_from, effective_to, is_current, batch_id)
SELECT nextval('dim_customer_seq'), s.customer_id, s.name, s.city, s.risk_band,
       s.attr_hash, :batch_date, DATE '9999-12-31', 'Y', :batch_id
  FROM stg_customer s
  LEFT JOIN dim_customer d
         ON d.customer_id = s.customer_id AND d.is_current = 'Y'
 WHERE d.customer_sk IS NULL          -- brand-new customer
    OR d.attr_hash <> s.attr_hash;    -- changed attributes

-- Fact load: point each event at the dimension version valid on that date
INSERT INTO fact_sales (date_sk, customer_sk, product_sk, qty, amount, batch_id)
SELECT dt.date_sk, dc.customer_sk, dp.product_sk, s.qty, s.amount, :batch_id
  FROM stg_sales s
  JOIN dim_date    dt ON dt.full_date = s.txn_date
  JOIN dim_customer dc ON dc.customer_id = s.customer_id
                      AND s.txn_date BETWEEN dc.effective_from AND dc.effective_to
  LEFT JOIN dim_product dp ON dp.product_id = s.product_id;

Where it is used

• IT services and consulting (TCS, Infosys, Wipro, Accenture, Capgemini, LTIMindtree, Cognizant): large ETL migration and modernisation projects — Informatica/DataStage to cloud SQL + dbt — are among the highest-volume hiring areas for fresh AI&DS graduates.
• Banking, insurance and NBFCs (HDFC, ICICI, SBI, Bajaj Finserv, LIC): customer 360, risk and limit marts, AML/transaction-monitoring feeds, and regulatory returns to the RBI's ADF/CIMS reporting platform — Type 2 history is non-negotiable for audit.
• E-commerce, delivery and fintech (Flipkart, Amazon India, Swiggy, Zomato, Nykaa, Paytm, PhonePe): daily sales, order-funnel and delivery-SLA fact tables; semi-additive inventory snapshots; GST-ready invoice marts.
• Telecom, media and travel (Jio, Airtel, Hotstar, IRCTC, MakeMyTrip): call/usage detail records and clickstream aggregated into subscriber and content marts.
• Government and public data (GSTN, UIDAI, NPCI, state dashboards, NITI Aayog's data portals, Census/NSS): integrated reporting stores built on standard dimensional models.
• Job roles: data engineer, ETL/Informatica developer, analytics engineer (dbt), BI developer, data warehouse architect, data-quality analyst; the usual first-job skill set is SQL + one ETL tool + Python + a cloud warehouse.

Interview questions

• Q: What makes a data warehouse different from a database? A: A warehouse is a denormalised, historical, non-volatile, subject-oriented store optimised for large analytical reads, whereas an operational database is normalised and tuned for many small concurrent transactions on current data.
• Q: Star or snowflake — which would you choose and why? A: Star by default, because fewer joins make queries faster and the model is easier for BI tools and business users; snowflake only when a dimension is very large or its hierarchy changes often enough that redundancy becomes a maintenance problem.
• Q: Why use surrogate keys instead of the source's primary key? A: They insulate the warehouse from source key changes, reuse and format differences, let the same entity arrive from several systems, keep joins narrow, and are essential for SCD Type 2 where one business key needs many rows.
• Q: Explain SCD Type 2 and how you implement it. A: Each change inserts a new dimension row with a new surrogate key, effective_from/effective_to dates and an is_current flag; the old row is expired. Implementation compares an attribute hash between staging and the current row, then expires and inserts in one merge.
• Q: What is the grain of a fact table and why declare it first? A: The grain is what one fact row represents (e.g. one order line per day); every dimension and measure must be true at that level, so changing it later invalidates the whole design.
• Q: What is a semi-additive measure? A: A measure summable across every dimension except time — account balance or stock on hand — so over time it must be averaged or taken as the closing value.
• Q: ETL or ELT for a cloud project? A: ELT, because an MPP columnar warehouse can transform faster than a separate ETL server and raw data stays available for re-processing; ETL still wins when PII must be masked before landing or the target compute is expensive.
• Q: How do you do incremental loading? A: Keep a high-water mark (max last_modified or ID) per table, extract only rows beyond it, or use log-based CDC to capture inserts, updates and deletes; then merge/upsert into the target and advance the mark only after a successful commit.
• Q: Why load dimensions before facts? A: Fact loading looks up surrogate keys from dimensions; if a dimension row is missing the fact either fails or gets an unknown key, so dimensions must be current first (missing members get an inferred placeholder row).
• Q: How do you handle a late-arriving fact? A: Join on the dimension version whose effective date range contains the event date, not the current row, and re-aggregate the affected partitions and materialized views.
• Q: How do you make an ETL job restartable? A: Stage the extract, tag every row with a batch ID, delete-then-insert that batch's own partition (or swap a partition atomically), and commit the high-water mark last — so a re-run reproduces the same result.
• Q: What is a conformed dimension and why does it matter? A: A dimension with identical keys and meanings shared across fact tables; it lets you compare and drill across different business processes (sales versus returns) and prevents each mart defining "customer" differently.
• Q: Which Indian law shapes what you may keep in a warehouse? A: The Digital Personal Data Protection Act, 2023 — consent, purpose limitation, data minimisation and storage limitation (duties in force from 13 May 2027 under the DPDP Rules 2025) — implemented through PII masking, access control, retention and purge jobs; sector regulators like RBI and SEBI add storage-location and record-retention requirements.

Common mistakes

• Mixing grains in one fact table (order-level and line-level rows together), which double-counts every aggregate.
• Summing a semi-additive balance or averaging an average instead of recomputing the ratio from summed numerator and denominator.
• Using the source natural key as the dimension primary key, then being unable to keep Type 2 history or merge two source systems.
• Overwriting dimension attributes (Type 1) where history is needed, so last year's sales silently get re-reported under this year's territory or price band.
• Deriving date attributes with SQL date functions instead of building a date dimension with the Indian financial year and holiday flags.
• Loading facts before dimensions, or dropping rows whose dimension member is missing instead of inserting an inferred/"unknown" member row.
• Treating the staging area as permanent storage — indexing it, reporting off it, and never clearing it — and running row-by-row updates instead of bulk loads, which blows the nightly window.
• Writing non-idempotent jobs with no batch ID or high-water mark, so a re-run after a failure duplicates rows.

Related topics

Big data: Hadoop and Spark · Data pipelines: Airflow and Kafka · Data lakes and lakehouses · Cloud data platforms: BigQuery, Snowflake and Databricks · DBMS: SQL, normalisation and indexing · Business intelligence and data visualisation