- Lab
-
Libraries: If you want this lab, consider one of these libraries.
- Data
Implement CDC for an Incremental Load in SQL Server
Incremental loads are a vital part of your data retention and analysis strategy, and Change Data Capture (CDC) is a key mechanism for implementing these workflows. In this lab, you will learn how to configure CDC, build an incremental load utilizing it, and validate that the load is delivering the results you expect.
Lab Info
Table of Contents
-
Challenge
Configure SQL Server Change Data Capture
As the SQL Server DBA, you've been tasked with configuring the needed sales tables for incremental loads using Change Data Capture (CDC).
- You will start with two databases, a populated sample database and a warehouse database with an empty table.
- Use T-SQL to configure the source database for CDC. (SalesOrderHeader and SalesOrderDetail are recommended tables.)
- Generate a mock workload and verify that CDC captures transactions.
-
Challenge
Build an Incremental Load Using CDC Change Data
With CDC properly configured on your source database, you now need to build an incremental load that will utilize the information it tracks and replicate transactions to the data warehouse.
- Use T-SQL to view recent source activity.
- Replicate the CDC transactions to the warehouse database. (It is recommended to implement this as a stored procedure.)
- Maintain load metadata so that they can be tracked.
-
Challenge
Validate and Operate the Incremental Load
With all of your hard work complete, it's now time to make sure everything works as expected. You will need to verify that the load works consistently and delivers the expected results reliably.
- Run multiple cycles of the incremental load.
- Verify that the warehouse table matches the source tables.
- Simulate re-running an interrupted load over the same timeframe.
Real skill practice before real-world application
Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.
Learn by doing
Engage hands-on with the tools and technologies you’re learning. You pick the skill, we provide the credentials and environment.
Follow your guide
All labs have detailed instructions and objectives, guiding you through the learning process and ensuring you understand every step.
Turn time into mastery
On average, you retain 75% more of your learning if you take time to practice. Hands-on labs set you up for success to make those skills stick.