🏭 Data Warehousing

Think of an order system. When a customer places an order, that write hits OLTP. When a manager asks "how many orders did we sell last quarter by city?", that read hits OLAP. A warehouse is an OLAP store. It copies data from many OLTP systems and models it for analysis. In production you do not run heavy sales reports on the checkout database. You load order facts into the warehouse so the shop stays fast.

Old sales warehouses often cleaned orders on an ETL server, then loaded a star schema. Modern stacks land raw order files in a lake or bronze layer, then build clean customer and sales tables with SQL. Production ELT still needs contracts: schema checks, dedupe, and tests before gold reports. ETL is not dead. Use it when you must mask PII or reduce volume before the data can sit in the warehouse.

In a sales company, Kimball starts with an order fact, a customer dimension, and a product dimension that other marts can reuse. Inmon first stores 3NF customer, order, and product tables in a corporate warehouse, then publishes a sales mart. Production teams often mix both: a normalized or vault hub, plus Kimball stars for BI. Interviewers want the planning difference, not a brand fight.

Customer number lives on a hub. The sale that ties a customer to a product lives on a link. Name, city, and segment live on satellites with load dates. When the CRM adds a new customer field, you add a satellite, not rewrite the sales star. Production vaults are raw vault plus business vault. BI still usually reads a Kimball star or wide gold table on top, not the raw hubs.

If sales, finance, and support all need the same customer history, a vault hub can take CRM, billing, and ticket feeds without breaking old satellites. The sales mart can still be a star with fact_sales and dim_customer. Production cost is real: vault has more tables and more joins. Do not vault a single source with a fixed order grain just because it is trendy.

In a sales star, dim_product has product, brand, and category on one row. In a snowflake, product points to brand, and brand points to category. Analysts join more tables in a snowflake. Production warehouses usually keep stars for hot marts. Snowflake a dimension only when a hierarchy is shared, huge, or maintained in its own system.

fact_sales at order-line grain has one row per order line. It stores date_sk, customer_sk, product_sk, sales_amt, and qty. Do not put customer city on the fact. City belongs on dim_customer. In production, facts are huge, partitioned by date, and append-only or merge-by-grain. If the grain is wrong, every dashboard is wrong.

dim_customer has customer key, name, city, and segment. A sales report groups by city because city lives on the customer dimension, not on every fact row. Dimensions are smaller than facts but get used in almost every query. In production they need surrogate keys, change policy (SCD), and an unknown row so facts can always join.

Say it out loud: "one row per order line per day." That is not the same as "one row per order" or "one row per customer per day." If you mix order headers and order lines in one table, a customer with three lines looks three times richer than a customer with one line. In production, grain is a contract. Tests should fail when a key at that grain repeats.

Sales facts keep order_id on fact_sales. There is no dim_order if the only extra field is the order number itself. Analysts still filter "show order 88312." In production, keep that id for drill to the source system. Do not build an empty dimension table just to feel dimensional.

An order may have is_gift, is_priority, and channel. Each flag has a few values. Instead of three tiny dimensions, build dim_order_flags with every useful combination and one junk_sk on fact_sales. Production junk dimensions should stay small. If a flag becomes a real business entity with its own history, promote it to a proper dimension.

dim_customer used by sales facts and returns facts is conformed when "customer 55" is the same person in both marts. Then you can ask "who bought and later returned." In production, one team owns the conformed customer table. Local copies that rename segment or reuse customer ids will break cross-process reports.

fact_sales has order_date_sk, ship_date_sk, and delivery_date_sk. All three point at dim_date. A report can say "ordered in January, shipped in February." In production, do not clone dim_date three times. Use views like dim_order_date so BI tools can join the same calendar in three roles.

If a customer row stores original_signup_city = Pune, that city stays Pune even if they move to Delhi. Reports that need "city at signup" still work years later. In production, Type 0 is for durable original facts, not for attributes you must correct. A wrong date of birth still needs a controlled fix, not a silent overwrite policy.

Customer email changes from old@mail.com to new@mail.com. Type 1 updates dim_customer in place. Yesterday's report now also shows the new email if it reads the dimension today. In production, Type 1 is right for typo fixes and for columns compliance says you must not keep. Do not use it for segment if finance needs last year's segment.

Customer 55 moves from Pune to Delhi. You close the Pune row (valid_to, current_flag = N) and insert a Delhi row with a new customer_sk. Old sales stay on the Pune key. New sales get the Delhi key. In production, Type 2 needs a business key, a surrogate key, non-overlapping dates, and exactly one current row per business key. It is the default interview SCD.

dim_customer has segment and previous_segment. When a customer moves from Silver to Gold, you copy Silver into previous_segment and set segment to Gold. You can report "now Gold, was Silver." You cannot report the segment from five changes ago. In production, Type 3 is rare as the only policy. It is often a column on top of Type 2.

Customer profile stays Type 1 on dim_customer. Credit band and risk score, which change often, go to dim_customer_score with its own key on the fact. That is the Kimball Type 4 / mini-dimension idea. The other Type 4 keeps dim_customer_current and dim_customer_history. Production should pick one story and document it. Do not implement both and call them the same Type 4.

Customer 55 was Silver, now Gold. Type 2 adds a new row for Gold. Type 6 also stamps current_segment = Gold on the old Silver row and the new Gold row. A report can group historical sales by today's segment without an as-of join. In production this helps marketing, but updates must touch every version of that customer. It is more write work than plain Type 2.

An order arrives today for a new customer. The customer MDM feed arrives tomorrow. Today's fact_sales row cannot wait. You insert an inferred customer and attach the sale. Tomorrow you update that member or Type 2 it with the real name and city. In production, late dimensions are normal with multiple source systems. Design the unknown row and a repair job before go-live.

A store sends yesterday's missing orders today. Those rows belong on yesterday's sales, not today's. If dim_customer is Type 2, look up the customer version valid on the order date. In production, partition by event date and make the daily load rerunnable for a lookback window, not only for "today."

CRM customer C-55 can be reused, reused after delete, or formatted differently in billing. The warehouse surrogate customer_sk = 80021 never changes and is what fact_sales stores. Type 2 needs a new surrogate for each version of C-55. In production, never join facts to dimensions on the natural key alone if you keep history. Natural keys also make poor clustered keys when they are strings.

sales_amt is additive. You can sum it by customer, product, and date. Inventory on_hand_qty is semi-additive. You can sum it across warehouses on one day, but not across days. Discount percent and average order value are non-additive. In production, store additive parts (sales_amt, qty) and compute ratios in the report or gold layer. Never sum a percentage column.

Every night you write one row per product per warehouse: date, on_hand_qty, reserved_qty. You do not store every stock movement in that table. To see last month's average stock, average the daily pictures. In production, snapshots grow fast: 10,000 products × 50 warehouses × 365 days. Partition by snapshot date and do not update old pictures unless the source restates history.

One row per order has order_date_sk, pick_date_sk, ship_date_sk, delivery_date_sk, and lag days. When the order ships, you update that same row instead of inserting a new fact. Pipeline dashboards become simple. In production, this fact is update-heavy, so keep it only for processes with a fixed set of steps. High-volume click events do not belong here.

An order can belong to two customers in a household. fact_sales stores household_sk. bridge_household_customer lists each customer in that household with allocation_pct. Reports explode the bridge and multiply sales_amt * allocation_pct so the total does not double. In production, weights must sum to 1.0 for each group or you will inflate revenue.

An order line loads with sales_amt = 100, then billing sends 90 after a discount. If it is a correction, update the same grain or version it with is_current. If it is a new adjustment, insert an adjustment fact. In production, never silently rewrite last quarter after finance has closed unless you have a restatement process. Audit columns should show the batch that changed the measure.

If store sales_amt is before tax and web sales_amt is after tax, those facts are not conformed. A company total will be a lie. In production, publish a measure dictionary: grain, currency, tax rule, and timezone. Conformed dimensions are not enough. The numbers must also speak the same language.

Rows might be orders, shipments, inventory, and returns. Columns might be date, customer, product, and store. Orders and returns both mark customer and product, so those dimensions must be conformed. In production the bus matrix is the contract between data teams. New marts should add a row, not invent a private customer table.

Nightly order files land in stg_orders as they came from the shop system. Transforms then build fact_sales. If the transform fails, you do not re-hit the OLTP system. You replay staging. In production, staging is often truncate-and-load or a dated raw partition. Do not add Type 2 logic in staging.

A call-center ODS can show today's customer, open orders, and last payment in one place. It updates often. It will not keep five years of Type 2 city history. That history belongs in the warehouse. In production, do not make the ODS do both jobs. Near-real-time current and deep historical analytics fight each other.

The warehouse holds sales, inventory, and finance. The sales mart is fact_sales plus customer, product, and date for the sales team. Independent marts that each build their own customer table will disagree on "number of customers." In production, prefer dependent marts from one hub or one lakehouse gold layer.

A sales cube might precompute sales by month, category, and city. Pivot tools feel instant. The cost is refresh time and rigidity when grain changes. In production, many teams now skip cubes and use columnar warehouses plus a semantic layer. Cubes still appear in interviews and in older finance stacks.

SKU 88 moves from "Mobile" to "Electronics > Accessories." Old sales can stay under Mobile if you Type 2 the product row. If you overwrite category (Type 1), last year's mobiles become accessories in every report. In production, denormalize the current path on Type 2 product rows, or keep a hierarchy bridge with effective dates. Recalculating the whole tree on every report is slow and unstable.

When a customer city changes, CDC emits the old and new row. The warehouse applies that change to dim_customer as Type 1 or Type 2. You do not reload 20 million customers every night. In production, log-based CDC is kinder to OLTP. You still need ordering, deletes, and a replay topic. Timestamp CDC misses deletes unless the source soft-deletes.

A 200-row dim_pay_method can truncate-reload in seconds. A 5-year fact_sales cannot. Incremental sales loads take yesterday's orders, plus a lookback for late facts. In production, truncate-reload of a Type 2 dimension wipes history unless you rebuild from a full archive. Prefer incremental for facts and SCD2 dimensions.

The 15 March sales job fails after insert and is rerun. If the job only appends, 15 March doubles and revenue explodes. If it deletes 15 March then inserts, or merges on (order_id, line_id), totals stay correct. In production, every scheduled warehouse job must be rerunnable. Airflow retries are not safe without idempotency.

Last night the sales extract ended at src_updated_at = 2026-03-15 18:00. Tonight you pull rows with src_updated_at > that watermark. You advance the watermark only after the load commits. In production, add a lookback window because late orders can carry old timestamps. Store watermarks per source table, not one global clock.

fact_sales may have sales_amt for the business and batch_id for operations. When yesterday's total looks wrong, you trace the batch and the source file. Data Vault makes load_dts and record_source first-class. In production, do not reuse order_date as the load date. Event time and load time answer different questions.

BI often wants current city: WHERE current_flag = 'Y'. Late facts and as-of reports need order_date between valid_from and valid_to. In production, enforce one current row per natural key and non-overlapping ranges. Open-ended current rows usually use valid_to = 9999-12-31. Do not leave valid_to null if your engine cannot index range joins well — pick a convention and test it.

dim_customer keeps name and signup date. Credit band, risk score band, and activity band change often, so they live in dim_customer_profile with one row per combination. Each sale stores customer_sk and profile_sk. In production this is the Kimball answer to "customer Type 2 grew to 40 versions each." Do not put high-cardinality fields like email into the mini-dimension.

dim_customer may store primary_store_sk and join dim_store as an outrigger. dim_promotion may have start_date_sk pointing at dim_date. Keep outriggers rare. In production, BI tools struggle when every attribute is two joins away. If the outrigger is used in almost every sales query, denormalize the few columns onto the main dimension.

fact_sales is at order-line grain. fact_inventory_daily is at product-warehouse-day grain. They share dim_product and dim_date, but you do not join the two facts on a fake common grain. Drill-across happens by aggregating each fact to a shared grain, then combining. In production this is healthier than one giant fact that mixes processes.

A sales OBT has sales_amt, customer city, product category, and order date on one row. Dashboards get simple. The cost is size, duplicated attributes, and painful Type 2. In production, build OBT in gold from a star, not as the only model. Several OBTs without conformed sources will drift like independent marts.

Sales teams want SUM(sales_amt) and SUM(qty) as columns. A tall "metric fact" can add "packaging_fee" without an ALTER, but every report needs filters and pivots. In production, keep core additive money measures wide. Use tall for a metrics store or for sparse custom measures. Do not mix units in one tall column without a unit field.

Bronze keeps raw order JSON from the shop. Silver turns it into clean order lines and Type 2 customers. Gold is fact_sales and sales dashboards. In production, do not skip silver. Bronze-to-gold copies source bugs into finance. Late data, schema drift, and SCD belong in silver. Gold should be rerunnable from silver.

If marketing defines "active customer" as 30-day ordered and finance defines it as 90-day paid, meetings turn into number fights. A metrics layer (LookML, dbt metrics, Cube, a semantic view) points both tools at one definition on top of gold facts. In production, the store must declare grain, filters, and time windows. It does not replace the warehouse. It sits on it.

A government moves a holiday, or finance changes 4-4-5 week assignment. If you overwrite dim_date for 12 Jan, last year's year-over-year holiday report shifts. Production calendars should be generated, versioned, and released like code. Keep a durable date_sk = yyyymmdd. Version holiday and fiscal attributes, or snapshot the calendar used for a closed year.

A retailer may have fiscal year starting in February and weeks that are 4-4-5. dim_date has both calendar_year and fiscal_year, both calendar_week and fiscal_week. Sales facts only store date_sk. In production, never compute fiscal year in every query. One wrong week-start and the whole P&L shifts. Generate the calendar once and test year boundaries.

A new customer places an order before MDM publishes the customer file. fact_sales arrives first. Create a customer inferred member with the natural key from the order, then attach the sale. When MDM arrives, fill the inferred row or Type 2 it. This is the same family as late arriving dimensions, seen from the fact side. Production pipelines should not wait for a perfect master.

Order 88312 comes with customer_id = C-55, but dim_customer has no C-55. You insert customer_sk = 90001, name Unknown, inferred_flag = Y. The fact uses 90001. When the customer feed arrives, you fill name and city on that same surrogate (Type 1 repair) or close it and add a Type 2 version. In production, inferred members keep referential integrity. Facts should not store null dimension keys.

fact_promotion_coverage has one row per product, store, and date the product was on promo. There is no amount. To ask "which products were on promo but did not sell," you left join sales facts to this coverage fact. In production, still declare grain and surrogate keys. You can add a dummy event_count = 1 to make COUNT(*) obvious, but the table is still factless in meaning.

dim_date turns 2026-03-15 into fiscal_year, is_weekend, and month_name. Sales queries group by those columns. Generate it for 20+ years, including future dates for plans. In production, date_sk is often yyyymmdd as an integer. That is readable in facts and still a surrogate by contract.

An order arrives with a blank product id. fact_sales.product_sk = -1 joins dim_product "Unknown." Revenue still shows up in totals, under Unknown. Inferred members are for real natural keys that will be repaired. Dummy unknown is for truly missing keys. In production, never let null SKs into facts if BI uses inner joins.

Raw order JSON can live in a lake. fact_sales belongs in a warehouse or gold lakehouse tables. Databricks, Iceberg, and similar tools try to give both: cheap storage plus MERGE, SCD2, and BI. In production, a lake without a model is not a warehouse. You still need grain, keys, and quality.

Customer C-55 lives in Pune. Tomorrow the CRM says Delhi. You must keep the Pune version for old sales and add a Delhi version for new sales. Production SCD2 uses a surrogate key per version, one current row per natural key, and non-overlapping dates. Run the close step and the insert step in one transaction so a crash does not leave two current rows.

1. The MERGE looks only at current dimension rows (current_flag = 'Y').
2. If name, city, or segment changed, it closes that row with valid_to = today and current_flag = 'N'.
3. The INSERT adds a new current row for new customers and for those closed changes.
4. New rows get a fresh surrogate key, valid_from = today, and valid_to = 9999-12-31.
5. Old sales keep the old surrogate key, so Pune history does not become Delhi.

Sales by city and category for 2025 should use the customer version stored on the fact. That is the city at order time. Production BI should hide the SKs and expose city, category, and year. If you join current_flag = 'Y', movers will have last year's orders reported in this year's city.

1. fact_sales holds the measures and the dimension keys.
2. Each join uses the surrogate key stored on the fact.
3. Year, city, and category come from dimensions, not from the fact.
4. SUM(sales_amt) is safe because sales amount is additive.
5. There is no current_flag filter, so a customer who moved still reports under the city on that order.

If the same order line lands twice, every sales dashboard doubles. Production tests should run this after every load and fail the job. Duplicate grain is more dangerous than a missing column because the table still "looks fine."

1. Query 1 lists order lines that appear more than once.
2. The summed amount shows how badly revenue would inflate.
3. Query 2 counts null business keys and null surrogate keys.
4. Query 3 compares row count with distinct grain. If they differ, the grain is broken.
5. A passing load returns zero duplicate groups and zero null keys.

A new customer can order before MDM sends the profile. The sale still needs a customer_sk. Production inferred members keep referential integrity and stop inner-join reports from dropping revenue. Later, update the same SK (Type 1 repair) or Type 2 a complete version.

1. Distinct customer keys from orders are checked against dim_customer.
2. Missing keys become inferred members with unknown attributes and inferred_flag = 'Y'.
3. Fact load can now always resolve a customer_sk.
4. When the real customer feed arrives, the inferred row is filled in place.
5. Old facts keep the same surrogate key, so you do not rewrite the fact table.

The warehouse needs "how much stock did we hold each day," not every stock movement. Production snapshot jobs must be rerunnable for one date. If 15 March is reloaded, only 15 March is replaced.

1. Deletes any previous snapshot for 15 March so a rerun does not duplicate.
2. Reads the source on-hand picture for that day.
3. Maps product and warehouse natural keys to surrogate keys.
4. Inserts one row per product and warehouse for that date.
5. Keeps on_hand_qty as a picture, not as a movement.

Two customers share one order. If you join both customers with no weight, revenue doubles. Production bridges always carry a weight. Test that each household_sk sums to 1.

1. The fact stays at order-line grain with one household key.
2. The bridge lists every customer in that household plus a share.
3. Multiplying sales_amt * allocation_pct splits the order without inflating it.
4. The second query finds households whose shares do not add to 1.
5. Those bad groups would silently create or lose revenue.

100 units sitting for 30 days is still 100 units, not 3,000. Production inventory dashboards should default to last-day balance. Average on-hand is the right monthly KPI.

1. The first query finds the last snapshot date in March.
2. It sums on-hand only for that one day, then by category.
3. The inner part of the second query sums products for each warehouse-day.
4. The outer query averages those daily totals across March.
5. Neither query sums the same stock across 31 days.

A shop funnel is view → cart → order. Production funnels need a stable entity (session_id or customer_nk) and event timestamps. Count people or sessions, not event rows, or one chatty session looks like many conversions.

1. It scans the event fact for March.
2. It counts distinct sessions that viewed, added to cart, or ordered.
3. Conversion rates divide later steps by earlier steps.
4. NULLIF stops divide-by-zero when a step is empty.
5. One session with five page views still counts as one view.

Finance asks "trailing twelve months revenue" every month. Production TTM should use a month grain table or a gold aggregate. A 12-month window on daily facts will mix day rows unless you aggregate first.

1. Sales are summed to one row per month.
2. Months are ordered by month_start_date.
3. The window looks at the current month and the 11 months before it.
4. Each output row is trailing-twelve-month revenue.
5. Early months with fewer than 12 history rows will have a partial window unless you filter them out.

Yesterday's orders often arrive as a full replacement file for that date. Replacing the date slice is simpler than matching every line. CDC customer changes fit MERGE better. Production choice is about the source shape, not fashion.

1. Pattern A deletes all sales for 15 March, then inserts that day again.
2. Running pattern A twice still leaves one copy of 15 March.
3. Pattern B matches each order line and applies insert, update, or delete.
4. MERGE is the CDC-style path. Delete+insert is the snapshot-slice path.
5. Both can be idempotent if the match key or the replaced slice is complete.

An order from 12 Jan arrives on 20 Jan. The customer may have moved on 15 Jan. The sale still belongs to the 12 Jan version. Production as-of joins are required for late facts against Type 2 dimensions.

1. Late orders keep their real order_date, not the load date.
2. Customer and product are joined on natural key plus date range.
3. Half-open ranges (>= from and < to) stop a row from matching two versions.
4. The fact stores those historical surrogate keys.
5. A customer who moved after the order does not steal the old sale.

Email and phone often Type 1. Yesterday's typo should disappear. Production Type 1 still needs a surrogate for the fact join, but that surrogate stays stable.

1. Each staging customer is matched on customer_nk.
2. Existing rows are overwritten. Old email is gone.
3. New natural keys get a new surrogate and one current row.
4. Facts that already stored that surrogate now see the new email.
5. There is no close-and-insert history step.

Ops wants "orders placed but not shipped" without assembling many transaction facts. Production accumulating snapshots are update-heavy. Index the order natural key.

1. The pipeline fact is keyed by order_id, not by order line.
2. A new order inserts a row with order date and null later dates.
3. When shipping happens, the same row gets ship_date_sk.
4. Lag days are recomputed from the date dimension values.
5. Status dashboards read one row per order instead of stitching events.

Marketing needs "on promo but did not sell," which sales facts alone cannot answer. Production coverage facts still have grain: product, store, date.

1. Every promo coverage row is a product-store-day on promotion.
2. Sales are left-joined at that same grain.
3. COALESCE turns no sales into zero.
4. HAVING ... = 0 keeps promo days that did not sell.
5. There is no fake revenue on the coverage fact.

An order can be placed in March and shipped in April. Finance and logistics disagree if you have only one date. Production models keep both keys on the fact.

1. od is the order-date role of dim_date.
2. sd is the ship-date role of the same table.
3. The fact holds two different date surrogate keys.
4. Filters can ask for March orders that shipped in April.
5. One physical calendar supports both roles.

CRM deletes happen. A warehouse that only upserts will keep ghost customers. Production CDC needs event order, a primary key, and a rule for deletes. Soft delete is safer when old facts still point at the SK.

1. Several CDC events for one customer are reduced to the latest event.
2. MERGE matches the live dimension row on natural key.
3. Delete ops close the row and set is_deleted.
4. Insert/update ops overwrite Type 1 attributes.
5. New keys insert a current row. Delete events for unknown keys do nothing.

Orders have src_updated_at. A watermark of last night's max timestamp is the next starting point. Production watermarks are per table. Late rows need a lookback window, not a naive > last_ts.

1. The job reads the last committed timestamp for erp.orders.
2. It extracts rows from two hours before that mark up to now.
3. Those rows land in staging, then the normal incremental load.
4. Only after success does the watermark move to the new max timestamp.
5. The lookback recaptures late-updated orders that would be skipped by a strict > last_ts.

A retailer fiscal year may start in February. YEAR(order_date) would still say 2026 in January while finance says FY2025. Production reports must use the calendar table finance signed off.

1. Each sale already has an order date surrogate key.
2. Fiscal year and quarter come from dim_date, not from YEAR().
3. Filters use fiscal_year = 2026 as finance defines it.
4. Additive facts are summed by fiscal quarter.
5. Changing a 4-4-5 rule means rebuilding dim_date, not rewriting every query.

A broken order line with no SKU should still count in revenue. Production unknown rows are created once per dimension, not per load.

1. The unknown product member exists with product_sk = -1.
2. Orders try a real product join first.
3. Unmatched or null product ids become -1 through COALESCE.
4. The fact always has a product key, so inner-join reports keep the sale.
5. Unknown revenue is visible instead of missing.

Some sources send the same order line again with a corrected amount. Delete+insert of a whole day also works, but MERGE is better when the file is only a subset of the day. Production MERGE still needs the full change for that key. Partial columns will overwrite with nulls if you set every column blindly.

1. Staging order lines are mapped to surrogate keys.
2. MERGE matches the fact on the grain (order_id, line_id).
3. Existing grain keys are updated in place.
4. New grain keys are inserted.
5. Replaying the same staging file does not create a second line.

is_gift, channel, and priority have few values. The combination table stays small. Production junk dimensions are generated from distinct combinations in staging, plus a default unknown combo.

1. Distinct flag combinations from orders are inserted if missing.
2. Each combination gets one junk_sk.
3. The fact already stored that single key at load time.
4. The report groups by channel and gift flag from the junk dimension.
5. The fact table does not hold three extra flag columns or three extra FKs.

Order 88312 has 3 lines and 50 freight. If each line stores 50 freight, freight becomes 150. Production models either allocate freight by line share or keep a separate fact_order_header.

1. Line merchandise stays at line grain.
2. Option 1 splits header freight across lines by sales share.
3. Those allocated freight amounts sum back to the header freight.
4. Option 2 keeps freight only on an order-header fact.
5. Both options avoid repeating 50 freight on each of three lines.