heart of data modeling webinar: the ticking timebombs in your data model
TRANSCRIPT
![Page 1: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/1.jpg)
Ticking Timebombs in Your Data Models
Thomas LaRock, Solarwinds
Karen Lopez, InfoAdvisors
Know them, fix them, prevent them
![Page 2: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/2.jpg)
Karen López
Karen has 20+ years of data and information architecture experience on large, multi-project programs.
She is a frequent speaker on data modeling, data-driven methodologies and pattern data models.
She wants you to love your data.
![Page 3: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/3.jpg)
A Little About Me…
Thomas has over 15 years experience in roles including programmer, developer, analyst, and DBA.
He is a frequent speaker, published author, and avid blogger on data related technologies.
He enjoys working with data, probably too much to be healthy, really.
![Page 4: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/4.jpg)
Yes, Please do Tweet/Share today’s event
@datachick #heartdata
![Page 5: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/5.jpg)
Use Q&A for
formal questions
Get them in now!
Use chat to discuss with each
other
We have a great community
Yes!
Slides
Recording
…next week…
![Page 6: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/6.jpg)
POLL: Who Are You?
Aug 2014
![Page 7: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/7.jpg)
POLL: On Call?
Aug 2014
![Page 8: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/8.jpg)
Agenda
1.Issue
2.Background
3.Story Telling
4.What to do
AD-322-S
MichaelJSwart.com
![Page 9: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/9.jpg)
Primary Scope: Transactional Designs
Time constraints
Designs differ depending on usage of data
AD-322-S
![Page 10: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/10.jpg)
What’s a time bomb?
Perfectly fine design choice
Can be misused
Often leads to 3 AM on-call event
Often misunderstood
Needs monitoring and care
![Page 11: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/11.jpg)
Ready?
![Page 12: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/12.jpg)
Identities
Issue: How people use them
AD-322-S
25532,767
9,223,372,036,854,775,807
2,147,483,647
IDENTITY [ (seed , increment) ]
![Page 13: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/13.jpg)
What could go wrong?
Message: Arithmetic overflow error converting IDENTITY to data type int.
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
![Page 14: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/14.jpg)
Identities
Issue: How people monitor them
IDENTITY [ (seed , increment) ]
AD-322-S
![Page 15: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/15.jpg)
How do you fix it if it’s already gone off?
RESEED
GAPS
Not UNIQUE?
Change DataType
![Page 16: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/16.jpg)
We have a script (for SQL server)!
AD-322-S
http://thomaslarock.com/2015/11/sql-server-identity-values-check/
![Page 17: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/17.jpg)
What About SEQUENCEs?
CREATE SEQUENCE [schema_name . ] sequence_name[ AS [ built_in_integer_type | user-defined_integer_type ] ] [ START WITH <constant> ] [ INCREMENT BY <constant> ] [ { MINVALUE [ <constant> ] } | { NOMINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NOMAXVALUE } ] [ CYCLE | { NOCYCLE } ] [ { CACHE [ <constant> ] } | { NO CACHE } ] [ ; ]
![Page 18: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/18.jpg)
How do you fix it if it’s already gone off?
RESTART
GAPS
Not UNIQUE?
Change DataType
![Page 19: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/19.jpg)
RESTART
ALTER SEQUENCE [schema_name. ] sequence_name
[ RESTART [ WITH <constant> ] ]
[ INCREMENT BY <constant> ]
[ { MINVALUE <constant> } | { NO MINVALUE } ] [ { MAXVALUE <constant> } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ] [ { CACHE [ <constant> ] } | { NO CACHE } ]
[ ; ]
AD-322-S
![Page 20: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/20.jpg)
How do you prevent it?
Proper Design
Do the math
SEED Value
Increment Value
Use another feature
Monitor
Max allowable value
Max current value
Gaps
Predict* countdown timer
Team Data Script
Know your hottest tables
Prioritize
![Page 21: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/21.jpg)
Datatypes
• Issue: Are you real-world proof?
AD-322-S
CREATE TABLE dbo.customer(CustomerID int PRIMARY KEY NOT NULL,
EmailAddress varchar(25) NULL)
![Page 22: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/22.jpg)
What could go wrong?
Wrong datatype
ZIPCode
López
Account Number
Wrong Length/Precision
karenmariadeguadalupeannlopezymartinez@torontolab.contractor.restricted.reallylongdomainnameherereal
lyreally.com
![Page 23: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/23.jpg)
How do you fix it if it’s already gone off?
ALTER Column
Extended Alter
![Page 24: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/24.jpg)
How do you prevent it?
Proper Design
Do the math
Do research
User Engagement
Go long
Data Profiling
Legacy data
Statistical analysis
Edge cases
Get out a little
![Page 25: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/25.jpg)
DeNormalization
Issue: Denormalizations blow up
•Repeating columns instead of child table
•“Manually” Calculated data
•Roll up, Roll down
AD-322-S
![Page 26: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/26.jpg)
Simple Denormalization
• CREATE TABLE customer(CustomerID int,LastName varchar(255),FirstName varchar(255),EmailAddress varchar(50),
• EmailAddressTwo varchar(255),
• EmailAddressThree varchar(255));
![Page 27: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/27.jpg)
What could go wrong?
![Page 28: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/28.jpg)
SELECT *
Issue: How people use them
• SELECT * FROM ….
AD-322-S
![Page 29: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/29.jpg)
What could go wrong?
![Page 30: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/30.jpg)
SELECT *
Issue: How people monitor them
GovernanceReviewsScriptsTools
![Page 31: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/31.jpg)
How do you fix it if it’s already gone off?
Fix the source table
Fix the query
Fix the target table
Throw out your ORM
![Page 32: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/32.jpg)
Do you have a McGuyver Moment?
Defuse a bomb at 3AM?
![Page 33: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/33.jpg)
Your Time Bombs
What went wrong? How did you fix/prevent it?
![Page 34: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/34.jpg)
Other Time Bombs
• Collations
• Running out of storage space
• Bad Backups (ergo, no restores)
• Mutually dependent tables
• Using deprecated features in new designs
• Accepting a “just for now server”
• Triggers Firing
• Real world scaling (Dev vs. Prod)
• Linked Servers & Nested Views
• Bucket Columns (Bad DEV!)
• VM Snapshots
• Wrong SQL Server License…or “over installed”
• Installing external script without understanding license.
• Server Settings (whole other hour!)
• Faux NULLs
• GenerationNEXT installation process
• Multi-tenant DB Design
• Clustering on Unique Key
• GUIDs when SEQUENCES might do
• SELECT *
• Use of ORMs (generated DML)
![Page 35: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/35.jpg)
Finally…
These are perfectly fine design decisions, but..
• Fully implemented
• Monitored and managed
• Fully understood
• Emergency response plans needed
• Sometimes a paper clip and a stick of gum is not enough (Sorry McGuyver)
AD-322-S
![Page 36: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/36.jpg)
Thank you, you were great.
Let’s do this next month!
Karen Lopez @datachick
#heartdata
![Page 37: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/37.jpg)
Resources
IDENTITY Script:
http://thomaslarock.com/2015/11/sql-server-identity-values-check/
• Karen’s Blog: www.datamodel.com
• Tom’s Blog: www.thomaslarock.com
![Page 38: Heart of Data Modeling Webinar: The Ticking Timebombs in Your Data Model](https://reader031.vdocuments.us/reader031/viewer/2022030215/588948de1a28abde5a8b5d87/html5/thumbnails/38.jpg)
Thank you!
You were GREAT!
….We should do this again….