Why this matters
As an ETL Developer, you load data into target systems that must serve analytics and downstream jobs fast. The wrong indexes make loads crawl and queries slow. The right ones make upserts reliable, joins cheap, and bulk loads smooth.
- Speed up nightly fact-table loads by disabling/rebuilding nonclustered indexes appropriately.
- Design composite indexes that match MERGE/UPSERT keys and frequent filters.
- Reduce cluster costs by using partitioning/clustering where traditional indexes are not used (e.g., some cloud warehouses).
Real tasks you will face
- Adding a covering index so a BI query runs in seconds, not minutes.
- Choosing index order to make an upsert find matches in O(log n).
- Bulk-loading 100M rows without turning your target into a write bottleneck.
Who this is for
- ETL/ELT developers moving data into relational or warehouse targets.
- Data engineers optimizing pipelines for throughput and query performance.
- Analytics engineers responsible for reliable dimensional models.
Prerequisites
- Basic SQL (SELECT, INSERT, UPDATE, JOIN).
- Familiarity with ETL/ELT batch loads and incremental loads.
- High-level understanding of fact/dimension modeling.
Concept explained simply
Indexes are like sorted quick-lookup maps for your tables. They speed up finding rows but can slow down writing rows because the map must be updated.
- Row-store databases: typically use B-tree indexes (clustered and nonclustered). Composite index order matters.
- Columnar/cloud warehouses: often rely on partitioning and clustering instead of traditional indexes.
- Covering index: includes extra columns so some queries can read only the index.
- Unique index: enforces no duplicates (great for upsert match keys).
Mental model
Think in two phases:
- Load phase: fewer indexes = faster writes. Consider disabling or delaying some indexes for big batches.
- Query/serve phase: add the minimal set of indexes (or clustering) that benefit real workloads. Every extra index costs write time and storage.
Rules of thumb
- Index the columns you use to locate target rows during upserts (MERGE keys).
- Put the most selective and most-used equality filter columns first in composite indexes.
- For bulk loads, disable or drop non-essential nonclustered indexes, load, then rebuild.
- On partitioned tables, prefer local/partition-aligned indexes for easier maintenance.
- In cloud warehouses without classic indexes, use partitioning on time and clustering on frequently filtered/joined keys.
Worked examples
Example 1: Fact table nightly load (row-store)
Scenario: Loading 50M rows into FACT_SALES (clustered on surrogate key). BI filters by sale_date and store_id. Upserts match on (order_id).
- Load strategy: Drop/disable nonclustered indexes before bulk load; keep a unique nonclustered index on order_id if required for dedup/upsert matching speed. After load, rebuild a nonclustered index on (sale_date, store_id) INCLUDE (amount, quantity) to cover common BI filters.
- Why: Minimizes write amplification during load; ensures BI queries hit the covering index afterward.
Example 2: Dimension SCD Type 2
Scenario: DIM_CUSTOMER with SCD2. Queries filter on current_flag = true and customer_natural_key. Upserts match by customer_natural_key and effective_date ranges.
- Indexes: Unique index on (customer_natural_key, effective_start_dt, effective_end_dt) to prevent overlapping versions; nonclustered index on (customer_natural_key, current_flag) INCLUDE (surrogate_key) for quick current lookup.
- Why: Guarantees SCD integrity and speeds current-row lookups for joins.
Example 3: Cloud warehouse target (no traditional indexes)
Scenario: Loading events into a warehouse that uses partitioning/clustering, not classic indexes.
- Design: Partition table by event_date. Cluster by (user_id, event_type). Ensure MERGE matches on (event_id) which is naturally unique. Sort incoming data by partition key to improve file pruning.
- Why: Partitioning prunes large date ranges; clustering improves pruning on frequent filters; sorted loads enhance clustering quality.
Example 4: Composite index order for upserts
Scenario: MERGE into target on WHERE customer_id = ? AND event_ts BETWEEN ? AND ?. Which index?
- Prefer index on (customer_id, event_ts) so equality (customer_id) is first, then range (event_ts). This optimizes the seek + range scan pattern.
Practical step-by-step
- List core access patterns: upsert match keys; most frequent filters and joins.
- Choose minimal indexes for correctness and performance: unique constraints for keys, composite indexes aligned to predicates.
- Plan load-time handling: leave only essential indexes enabled for bulk loads; rebuild after load if needed.
- For warehouses, define partitioning and clustering; organize loads by partition for efficient pruning.
- Monitor and iterate: compare load duration and query latency before/after changes.
Helpful checks
- Does your upsert predicate have a supporting index or clustering?
- Are you indexing columns actually used by queries, in the same order?
- Can a BI query be satisfied by a covering index to avoid table scans?
Common mistakes and self-check
- Mistake: Adding many indexes "just in case." Cost: slower loads and updates. Self-check: For each index, name the exact query it helps.
- Mistake: Wrong composite order. Self-check: Equality columns first, then ranges, then includes.
- Mistake: Rebuilding large indexes daily without need. Self-check: Rebuild only after heavy bulk load or fragmentation impacts IO.
- Mistake: Using classic indexes in systems that don't use them. Self-check: In warehouses, rely on partitioning/clustering.
- Mistake: No unique constraint for upserts. Self-check: Ensure natural/business keys are enforced to prevent duplicates.
Exercises
These exercises are available to everyone. If you log in, your progress will be saved.
Exercise 1: Choose indexes for a star schema
Target tables:
- FACT_ORDERS: columns (order_id, customer_sk, store_sk, order_dt, amount)
- DIM_CUSTOMER: columns (customer_sk, customer_nk, current_flag)
Workload:
- Nightly bulk insert into FACT_ORDERS (5M).
- MERGE into DIM_CUSTOMER SCD2 using customer_nk.
- BI queries: FACT_ORDERS filtered by order_dt and store_sk; join to DIM_CUSTOMER on customer_sk where current_flag = true.
Task:
- Propose which indexes/partitioning to have enabled during load vs after load.
- Specify composite order and include columns where relevant.
Hints
- Upsert keys need fast lookup.
- Equality first, then ranges.
- Cover frequent BI filters.
- Checklist: Did you include a unique constraint for DIM_CUSTOMER SCD2?
- Checklist: Did you minimize indexes during the FACT bulk load?
- Checklist: Did you align index order to BI filter order?
Learning path
- Before this: Understand incremental loads and MERGE/UPSERT mechanics.
- Now: Master index/clustering choices for targets, including load-time strategies.
- Next: Partitioning strategies, compression/encoding, and workload-driven schema design.
Practical projects
- Project 1: Take a sample star schema, run a timed bulk load with/without nonclustered indexes. Document the delta and final query latencies.
- Project 2: Implement MERGE for an SCD2 dimension with proper unique/index design. Validate no overlapping effective periods.
- Project 3: In a warehouse without indexes, design partitioning/clustering for a 1B-row events table and measure pruning effectiveness.
Next steps
- Review actual workloads and pick the top 3 queries to optimize with indexes or clustering.
- Automate post-load index rebuilds only when needed.
- Set up monitoring to catch regressions (load time, query time).
Mini challenge
You have a table with frequent upserts on (account_id, txn_ts) and BI filters on date ranges. Propose a single composite index that helps both and explain why. Write your answer in 3 sentences.
When you are ready, take the quick test below. Test is available to everyone; only logged-in users will have progress saved.