Roadmap › ETL & Data Warehousing Concepts › Module: Building Reliable Pipelines
Lessons in this course (12)
11. Performance of large loads
AdvancedMediumLesson 11 of 12
- Extract only needed columns and only changed rows (incremental/CDC).
- Bulk load (SSIS fast load,
SqlBulkCopy,BULK INSERT, PolyBase/COPY INTO in cloud warehouses) with batch sizes andTABLOCKfor minimal logging. - Drop/disable non-clustered indexes before huge loads and rebuild after (or load into an empty partition and switch in).
- Set-based SQL instead of row-by-row (avoid cursors, OLE DB Command per row).
- Parallelism — load independent tables concurrently.
- Partitioning of large facts (by date) for fast loads/archival.
- Push transformations to where data lives (ELT) when the target is powerful.
- Avoid blocking transformations (sort, aggregate) on huge flows in SSIS; sort in the source query instead.