Andrea Besozzi Data Technical Manager Gibraltar Kindred Group

Andrea Besozzi – Data Technical Manager Gibraltar @ Kindred Group Using Change Tracking to build a Live Data Warehouse © Kindred Group plc 2018 1

Using Change Tracking to build a Live Data Warehouse Andrea Besozzi Data Technical Manager Gibraltar @ Kindred Group plc 16+ years as SQL Professional MCSE: Data Management and Analytics Gibraltar SQL Server User Group leader © Kindred Group plc 2018 2

Agenda ‒ Benefits of live data ‒ Change Tracking in detail ‒ Live DW cycle ‒ Deal with deletions ‒ Live DW and performance considerations ‒ Tips and tricks ‒ Q&A © Kindred Group plc 2018 3

Benefits of live data ‒ Why we need live data? • Live dashboards, alerts, monitoring • Live marketing campaigns • 24/7 systems and customer support ‒ Impact on releases is immediate. No more sleepless nights thinking the ETL might fail © Kindred Group plc 2018 4

Change Tracking – Overview ‒ What is Change Tracking? • Lightweight and efficient mechanism to track changes • Easy to set up and use • Synchronous process • It doesn’t require schema changes ‒ What does it track? • Inserts, Updates, Deletes © Kindred Group plc 2018 5

Change Tracking – Overview ‒ What does it store? • One row per Primary Key • Version number • Change operation • Transaction datetime ‒ Ability to know what column was changed © Kindred Group plc 2018 6

Change Tracking – Alternatives ‒ Change Data Capture • It keeps the history of every change • Bigger overhead than CT ‒ Service Broker • Asynchronous message queuing system • Real nightmare to set up and maintain © Kindred Group plc 2018 7

Change Tracking – Alternatives ‒ Replication • Heavy impact on performance • Difficult to maintain and get rid of ‒ Triggers • Dangerous if not aware of them • Require custom objects and logic to be created © Kindred Group plc 2018 8

Change Tracking – Requirements and recommendations ‒ Requirements: • SQL Server 2008 or newer versions • Primary Keys on source tables ‒ Permissions: • ALTER permission on source database and tables • SELECT and VIEW CHANGE TRACKING permission on source tables ‒ Recommendations: • Primary Keys on staging and DW tables • Log change versions in a table © Kindred Group plc 2018 9

Change Tracking – Setup and configuration ‒ Enable CT on database ‒ Enable CT on table © Kindred Group plc 2018 10

Change Tracking – Objects ‒ Functions: • CHANGETABLE (CHANGES) • CHANGETABLE (VERSION) • CHANGE_TRACKING_MIN_VALID_VERSION() • CHANGE_TRACKING_CURRENT_VERSION() • CHANGE_TRACKING_IS_COLUMN_IN_MASK() ‒ Views: • sys. change_tracking_databases • sys. change_tracking_tables • sys. dm_tran_commit_table • sys. internal_tables ‒ Stored procedures: - sp_flush_commit_table_on_demand (undocumented) © Kindred Group plc 2018 11

Change Tracking – Querying changes ‒ Get maximum version number for a database ‒ Get changes for a table © Kindred Group plc 2018 12

Live DW cycle © Kindred Group plc 2018 13

CT and Live DW – Demo © Kindred Group plc 2018 14

Deal with deletions ‒ Deletions are tracked, but only PK values are returned ‒ Understand actions to be taken on deletions: • Never delete rows from staging tables • Delete Live DW rows on main table deletions • Update Live DW rows on left joined table deletions ‒ Consider when to read or ignore staging table deletions (Change Operation = D) © Kindred Group plc 2018 15

Live DW considerations ‒ Building a Live DW is generally more complex than a classic DW • More objects to be developed • Deletions may be tricky • Extra indexes and columns needed • Troubleshooting is generally more time consuming ‒ Keep it simple, it can become messy otherwise ‒ Remember to populate from scratch new tables © Kindred Group plc 2018 16

Performance considerations ‒ Performance impact on DML commands ‒ Impact on storage • Internal tables • Change operation and version columns ‒ Clean up process can cause blocking ‒ User queries and reports can affect ETL © Kindred Group plc 2018 17

Tips and tricks ‒ Process only required changes ‒ Enable CT only where needed ‒ Monitor and get alerted: • ETL failures and performance • Auto clean up process ‒ Choose the retention period wisely ‒ Performance is key © Kindred Group plc 2018 18

Resources ‒ Change Tracking https: //docs. microsoft. com/en-us/sql/relational-databases/track-changes/about-change-tracking-sqlserver ‒ CT Functions https: //docs. microsoft. com/en-us/sql/relational-databases/system-functions/change-tracking-functionstransact-sql ‒ CT Views https: //docs. microsoft. com/en-us/sql/relational-databases/system-catalog-views/change-trackingcatalog-views-sys-change-tracking-databases https: //docs. microsoft. com/en-us/sql/relational-databases/system-dynamic-managementviews/change-tracking-sys-dm-tran-commit-table https: //docs. microsoft. com/en-us/sql/relational-databases/system-catalog-views/sys-internal-tablestransact-sql ‒ Slides and demo scripts will be uploaded to the SQLBits website © Kindred Group plc 2018 19