One data professional's adventures with data..and beyond
Author Archives: diligentdba
My name is Mala, I am a DBA on SQL Server for the past 15 years..and an active member of the PASS community. Aside from my profession i love to read, write, paint,travel and play practical jokes.
In the last post I covered briefly the history around graph databases. In this one am going to look at the structure. There have been a lot of blog posts written up about structure of graph tables in SQL Server. I really like this series by my friend Niko Neugebauer.
The simplest way to understand a graph data model is that there are just two entities – Nodes, which is what we call Entities in the relational world, and Edges, which are what we call relationships. They are typically represented like below, with the circles standing for nodes, and the arrows for relationships. The emphasis, as we can see is on the bold arrows – because relationships are what graph data is about, with less emphasis on entities/nodes.
To illustrate with an example – I took the free movie dataset from IMDB and designed it the relational way. So, I have a few tables – movies, actors, directors and such, connected like below.
Relational Data Model
Why is this a good candidate for graph data?
1 It has more than one many-to-many relationship, with the candidate tables having significant amounts of data. 2 The nature of relationships are worth querying on – for example, how many directors are also actors, which actor has co starred with which actor in how many movies, what is the shortest way to reach one actor from another..and so on.
If I want to redesign this in graph model – the main thing to remember is that the concept of graph data is largely implemented as nosql, so there is no ANSI like standard to stick to. We’d have to make our own rules. At the designing stage, the main rule is ‘design around relationships.’ So, think in terms of the verbs ‘who produced what’, ‘who acted in what’, ‘who directed in what’. All of these make our edges, or arrows. Then we can see what connects those arrows – the two nodes, Person, and Movies. Those are our entities. So, my graph data model of the same database looks like below.
In the next post we can look at how to create sql graph tables and query on them with this model. Thanks for reading!
I have been looking into this feature and also into understanding graph data in general. I believe introduction of graph database feature in SQL Server has many advantages – although I also believe it is important to understand the background/origin/ and how it was done before. In this series I will start with the history and cover several ways it was done before we got to where we are now.
Origins of Graph Theory: The theory behind graph data is old and goes back what is popularly known as Konigsberg Bridge Problem. The problem has been narrated several times – in short it was about how to traverse a town with 4 land masses (2, but 4 if you include two banks of a river) connected by 7 bridges. A mathematician named Leonard Euler took it up and came up with a mathematical concept of ‘nodes'(land masses) ,’edges’ (the connections between nodes or bridges), and the number of ‘edges’ coming out of each node (degrees). Euler’s theory, put very simply, says if you have more than two nodes with odd degrees in a configuration then you cannot traverse the graph from one end to another. This laid the foundation for graph data and what we have now.
How is it different from relational data? : Every thing we do with graph data modeling and querying can be done in the relational world. But to classify data as graph data it has to be a certain way. Graph data has way more relationships than it does entities. I like to use the simple example of social media followers. I am an entity, for example, with 100 followers. My friend has 200 followers. Between us we have 50 friends in common. Those friends in turn have friends in common with me. And so on. If you have to model this in the relational world, the number of relationships will be too large and difficult to represent, let alone query on. This is the kind of problem that graph data modeling and querying helps us model and deal with.
What are some common examples of graph data? Graph data is all around us…some of the most common examples are Chart of Accounts, Organizational Charts, Transportation Systems (GPS), Bill of Materials and social media connections.
In the next post I can discuss some examples of data modeling with graph data, and what are the specific problems/algorithms we can solve by modeling it this way.
The session line up for PASS Summit 2019 was announced today...there are so many good sessions to go to..managing time and what we do with our limited time there is an important skill..to some extent. I’d say it is partly skill, and partly luck to get the most out of it. We can control the skill part, so let’s see how.
1 If I am sponsored by my job, I’d consider the top sessions that I can go to that would add value to what I do at work. So let us say Powershell is one of them – I would shortlist all the sessions on powershell and decide which ones would add most value to what am doing, and attend those. One of the key things i’ve learned here is that a beginner level session on something I know does not necessarily disqualify it. It may certainly not be the best place for me to learn – but some beginner sessions are very creatively done, and can often offer new insights into something I already know. It also depends on the speaker who is presenting – I know certain speakers whose sessions I will attend, no matter what, which brings me to the next point.
2 Check the schedule for my favorite speakers and what they are presenting on. There are too many this year but I try to go to as many as I possibly can.
3 If I have skills I need to or like to learn personally – I try to attend all those sessions too. I have an ongoing list of those and keep up as much as I can.
Two of the challenges I face every year are as below:
1 Session I want to go to cancelled, room is full, or is not as good as I expected – in all of these cases I go to the next one I planned to. (Always have a backup plan). It does not hurt at all though to just stand in hallway and talk to someone I’ve not seen in a long time. Or visit the exhibit hall, or the community zone. All of these activities are part of the summit.
2 I feel too tired to walk to the session I want to go to – this is not at all uncommon while doing back-to-back sessions and the next session is at the other end of the conference center. Even if I manage the sprint, chances that the session will have room for me may be doubtful, unless it is one of the larger rooms. It is for reasons like this that we have summit recordings – this time they seem to cost extra (they are usually free for summit attendees). I would still invest in them to listen to all the talks I missed.
Last few tips are to attend keynotes, visit vendor area and do after hour parties. Also don’t forget to have your business cards on, you can self order if your company doesn’t. It is inexpensive and very worth it. Networking is a very important part of the summit – probably more important than going to sessions. Get rest as appropriate for you – there is no point having a very detailed list of classes to attend if you feel drowsy or fall asleep in them. Stay out for partying but watch for not overdoing it and killing the purpose why you’re there. Wear good walking shoes, and drink plenty of water. Hope this was helpful, and have fun!!
This month’s T-SQL Tuesday is organized by Mohammed Darab(t|b). Mohammed has a really interesting topic – things you would say to yourself if you were 20. I am well past 20, and this is something I think about often. There are many,many things I wish I’d known earlier, that would have significantly changed my life for the better. But, life is intended to be just that – a series of obstacles and lessons we learn as we go along. If we knew everything to start with, there would be no obstacles and that’s not how it is intended to be. But, I would absolutely put down things I’ve learned the hard way that younger people or even older people with dissimilar challenges can learn and understand from. Below are a few of my answers, the answers that age taught me, and things I’ve learned, as a woman, a person of color, an immigrant and a woman in tech. Below are some things I’d have done totally differently.
1 Pick my battles. We live in a conflicted world. There is no way we are going to end up fixing every single thing we see that is wrong. I’d be annoyed or get turned off by a number of things starting with people violating traffic rules to the bro culture at work.It didn’t make my life any better to get involved in all of them. I’d say, now, to pick one or two that really matter, and understand that a lot of life is about just getting along.
2 Watch my back at work. I was given to being very open, and very candid about a lot of things, and often times went into battles without anyone looking out for me. Work situations are complicated, and people are in it mainly to make a living, not fight social causes. Now, I speak up on issues where I am assured of some sort of support. And if I am in unethical or immoral situations I just leave as quickly as I can.
3 Try less to belong, more to be who I am. As a somewhat lonely child I desperately wanted to belong. I’d often times hang out among people who were not like me in any way, or ponder too deeply on why some people are cliquish and others more accepting..on and on. I still sometimes have those feelings. Truth is that you can’t belong among people who are too different than you, and you can do absolutely nothing about other people having their cliques. Being who I am has attracted way more positive energy and like minded people.
4 Networking is about helping people. God, this lesson has taken a few decades. I am not extroverted, and for many years I had no clue what people said ‘networking’ was. I thought it was about brown nosing, going to golf, or visiting bars after hours..on and on. My volunteer work in the SQL community has led me to believe that quality networking is nothing more than honest, open availability to help people, and listen. I cannot even articulate how many doors opened for me after I learned this.
5 Nothing helps your career more than consistent efforts in any direction. As techies there are a billion different ways our paths can go, and most of us constantly worry about if we are doing this or that, compared to those other people who are. I worried a lot about not traveling and speaking as much as most friends do, not being an MVP, or a rockstar blogger..any number of such things. Truth is that consistent efforts in any one or two of those things pay rich rewards and we can’t be doing all of it. Spreading too thin is in fact a serious issue with many people. I’ve limited my efforts to things I enjoy – blogging, occasional speaking, writing, and volunteering wherever I can. That is how much I can do with my limited spare time. And the rewards it yields have been good enough.
6 Set small goals – I was given to setting very big, ambitious goals – like one blog post every week, getting through all certifications, and so on. Big goals are a form of self sabotage, albeit subtle. Few people get to achieve them, and somehow we all feel setting smaller goals is a lowly thing to do. It isn’t. Set small, measurable, easy goals, and document progress. It is true of every single thing – diet, exercise, tech learning.
7 Take vacations seriously – I spent a few decades of my youth working. I was a die hard workaholic, and the need to impress people was very strong. I canceled a vacation with my now late mom because of a reorganization at work. I never got to vacation with her again. We work to live, not the other way around. Vacations matter. Spending time and energy with loved ones is why we work.
8 Maintain community – I can’t really claim this is entirely something that I learned with age. Partly yes. As someone living many thousand miles away I learned the hard way that bonding between people, even people in your own family, needs effort and maintenance. Taking people for granted means you can’t guarantee the responses or regard you need will be available when you need it. It is the same with professional ties and with friends. It is partly why I make it a point to go to the PASS Summit every single year, when I have choices of other conferences. Meeting people in person is important. Saying hello, even once a year, and checking in on them matters and makes a difference. And no, just saying hi online is not quite the same thing. Take time for the people in your life. Invest in relationships. It is important.
9 All good things come to an end – This year marks my 16th year at PASS Summit. For the initial 12 years – I have nothing to show by way of photographs or memories. Some of those people are no longer around. I also don’t have any pictures of many people who were friends with me at earlier gigs, or neighbors or any number of things. These things matter. Nowadays I make it a point to capture those memories. It won’t be too long before many of us are old enough – those will be the only things that remain to remind us of good times.
10 Forgive and let go – Last but not the least. Don’t dwell on disappointments and resentments too much. I spent a LOT of emotional energy on this. One of my favorite quotes attributed to the Buddha is that holding a hot coal hurts the hand the holds it. I’ve basically learned that there are two kinds of people – those who are not worth it, and those who make mistakes and come around. The former are just that, let go of them when you learn it. The latter, forgive, include, accept. We are all human and mistakes happen. When you see that the person has a clear heart, and if he/she does, let go of the small stuff. The others, just not the right people for you, that is all.
I have learned all these lessons the hard way. But I have learned them. And it has made my life better. Thank you Mohammed for hosting!!
One of the most significant and hardest challenges we face as data professionals (or generally anyone in IT) is how to keep up with learning. Most of us are good with ambitious goals – ‘I want to do data science’, ‘I want to learn Linux for SQL’, ‘I want to be an AI expert’..this and that. But when it comes to real learning, we realize we don’t have enough time. Or that what we read needs some effort to practice and we don’t have the environments for that. Or that it sounds cool on paper but we hardly know how to make it work in real life…on and on. Learning is sort of like medication – you need to know how much of it you can ingest to make a difference. The quantity and the method are important. I like to learn in stages…a lot of people do. I also like to learn with focus, not just turning something on while I work or do other things. I like to walk away with something I feel like I can apply to real life. There are few trainings that meet all these criteria.
One of the most common issues I have had and heard people say also, at the PASS summit, is that they feel pulled in too many directions with the variety of sessions. One hour of security, one hour of something in BI, one hour of analytics, this and that..and at the end of the day you are like someone who has had 3 buffet lunches at Vegas..you don’t know what you ate, you liked some of it and you’re not sure how it has gone down 🙂
This year, we have what is called ‘Learning Paths’. These are guided pathways through sessions that target specific learning needs. Each ‘pathway’ is multiple sessions taught by multiple people. You have no obligation to follow a pathway to attend them but you would probably gain more if you did. For example, a ‘pathway’ on SQL Performance for the developer has 3 sessions as below.
Another pathway on Becoming an Azure Data Engineer is as below.
The complete list of pathways is as below and you can find the content on PASS summit website.
AI for Everyone
Cloud Migration
Data Visualization & Storytelling
Linux for SQL Server Professionals
Communication for Technical Professionals
Data Security Pathway
SQL Performance for the Developer
Technical Leadership
Modernizing with SQL Server 2019
Becoming an Azure Data Engineer
I hope you will find this method useful to enhance your learning. And do stop by at the bloggers table to say hello to me while you’re there!!
Last month I was fortunate to have my first ever article published on Simple-Talk, among the best quality website for sql server articles ever. During the process of writing this article I ran into several errors related to temporal tables that I have not seen before. Some of these are documented by Microsoft, some are fairly obvious to understand and others are not. Below I summarize the list of errors you can possibly run into if you are using this really cool feature.
Error Message
Reason
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column.
Defaults have to be specified if using an existing table as a history table.
Msg 13523, Level 16, State 1, Line 62 Setting SYSTEM_VERSIONING to ON failed because table ” has ‘’ columns and table ‘WideWorldImporters.Sales.Region_History’ has ‘’ columns.
History table and main table should have exactly the same structure.
Msg 13518, Level 16, State 1, Line 62 Setting SYSTEM_VERSIONING to ON failed because history table ‘’ has IDENTITY column specification. Consider dropping all IDENTITY column specifications and trying again.
Columns with identity are disallowed on history table.
Msg 13575, Level 16, State 0, Line 2 ADD PERIOD FOR SYSTEM_TIME failed because table ” contains records where end of period is not equal to MAX datetime.
End date on table being versioned has a value that is not equal to the default specified. Update the table to make sure all end dates equal the maximum default.
Msg 13515, Level 16, State 1, Line 15 Setting SYSTEM_VERSIONING to ON failed because history table ‘’ has custom unique keys defined. Consider dropping all unique keys and trying again.
History table cannot have any primary keys defined. Remove primary keys.
Msg 13543, Level 16, State 0, Line 116 Setting SYSTEM_VERSIONING to ON failed because history table ” contains invalid records with end of period set to a value in the future.
Data in history table is not following rules – all end date should be before start date for the corresponding main record, and less than current date.
Msg 13573, Level 16, State 0, Line 7 Setting SYSTEM_VERSIONING to ON failed because history table ” contains overlapping records.
There are multiple records for the same record with overlapping start and end dates. The end date for the last row in the history table should match the start date for the active record in the parent table
Its really hard to believe that its been 20 years since the start of the PASS organization – the volunteer run database education group that I am proud to be associated with. My association runs to 18 of those 20 years, and 16 of those have been with attending the PASS Summit. Sadly, I do not have any pictures to show for most of those years. But I have a few taken during the past five years. These pictures are memorable moments with friends, and turning points in my own career..how many people can claim both of those together? I do!! So below are some from my favorite collection. Thank you PASS, for the many memorable moments and look forward to more!!
I cannot write this without stating some history. I have been part of PASS community since 1999. That is 21 years now. One of my favorite sub groups of PASS is WIT. 20 years ago WIT was restricted to one afternoon lunch session at the yearly summit. Majority panelists were white women. The discussions were good, but the lack of diversity always hit me hard. And we’d hear things like ‘after am done collecting more new shoes’ (don’t think that person is part of our community any more but she actually said it in response to someone who asked how they’d find time to learn after hours).
Fast forward 20 years later – WIT is doing a lot of great things. There is a monthly newsletter. There is a virtual group where with women speakers on technical subjects. Our books are promoted, and there are tweets promoting women speakers at sql saturdays. My own second book is in progress thanks to support from Kathi Kellenberger, who is one of the WIT team leads. 20 years ago, we didn’t even know there was such a thing as Women’s History month. But now we do. And tomorrow I am part of an awesome panel with the amazing likes of Kendra Little (@Kendra_Little), Lori Edwards (@loriedwards), Cathrine Wilhelmson(@cathrinew), Kellyn Pot’Vin-Gorman(@DBAKevlar), and Monica Rathbun (@SQLEspresso). The leaders of PASS WIT, Rie Irish (@IrishSQL) and Kathi Kellenberger (@auntkathi), will be leading the discussion.
Thank you, Rie and Kathi, for making WIT such an awesome cause and group to be part of. And a special thank you to Denny Cherry and Associates as well – for your active support and promotion of the cause. Now, if you’re reading this..time to register for tomorrow’s discussion, here!
The last T-SQL Tuesday of this year is hosted by Jason Brimhall – one of my long-time friends in the SQL community. Jason’s topic is ‘To influence and to be influenced’ – call out times when something you did influenced someone , and you were in turn influenced by something that person said or did. It is a positive way to end the year on a tone of gratitude to people who have influenced you – and it is doubly positive/uplifting to see/hear of what you have done to other people too.
What I did to inspire – This year was a big one for me because I became a published author with my first book – Data Professionals at Work. My book is a collection of stories of people in the data industry – their journeys, careers and choices they made along the way. I had a great time putting it together, and I did not expect anything more than a ‘thank you’ (which I got) from all the people who were part of it. I was, however, tremendously moved by how big it was for some people – particularly those who had never had their name in a book before. I saw them post proudly on social media about being featured in a book, and their loved ones/friends respond on how proud they were. It made me feel tremendously humbled and thankful to have done it. I was also very humbled by how many celebrities at the PASS summit wanted my book – some even with autographs on it. These were people I have been following for years, and had many books/awards to their credit. What I was inspired by – I always attend the board Q&A when am at PASS summit, as am interested in the direction the organization is going and the answers they have for many people all over the world. Sometimes the answers have left me feeling good, and other times it has been lack lustre. During the past few years I’ve seen an increase in arguments and debates, and even watched people leave openly angry/unhappy and so on. This year was a good year – exceptionally good, in fact. Questions were posed in civil, polite but candid ways – and answered to-the-point by everyone up there. The meeting ended on a hugely positive note (I don’t mean to say everyone there was happy – there were things people were not happy about but the atmosphere was one of civility and optimism). I was inspired the people on the board who made it happen. It is not an easy task, being there – and they are all volunteers with jobs/families and responsibilities like the rest of us. They were able to put on a great show and a create a civil environment with positive interaction. It was inspiring.
Thank you Jason, for a good topic to finish the year with. Happy 2019 to all.
This will be my last post for the year. In this am going to look back on goals I set for myself over past two years, how well I’ve done, and what I plan to do in the year ahead.
The idea of epic life quest was started (in community) by Steve Jones and Brent Ozar – it is about writing down goals and calling yourself to account on how you met them every year. My first post on those lines is here. I wrote this in early 2017. I did not write one in 2018, and that was because last winter was a pretty chaotic time for me and was waiting for things to look in better shape before setting more goals for myself. This year things are looking better, and below is my summary of goals I set, and how I did.
2017:
1 Complete Microsoft Data Science Program and Diploma in Healthcare Analytics from UCDavis.
I got the second one done partially. I could not complete the first due to lack of time. And it wasn’t as great as I thought it would be. It was expensive, quality wasn’t up to the mark and practical gains out of it were none. I learned from this is not to commit to expensive learning programs without fully understanding if they are worth it.
2 Stick to blogging goals – one blog post per week, one contribution to sqlservercentral.com per two weeks.
I was able to keep up with this in 2017 and part of 2018. My blogging got rather irregular half way through 2018 because of a relocation and job change, I think I can excuse myself for that. Plan to pick this back up in 2019.
3 Keep up exercise goals of 10,00 steps per day and one yoga workout per week.
I am doing fine here and this will continue as a permanent goal.
4 Speak at local user group as often as I can (my limitations with travel do not allow me to speak at too many sql saturdays or out of town events)
I did not do good on this one. Partly because my job and other personal obligations left little time to even prepare a good talk, let alone present it. Also partly because I was in a very confused place on what to talk about. Things have cleared up a bit now.
5 Submit to speak at PASS speaker idol event.
This did not happen either,for same reasons as above. Hope to make it happen in 2019.
6 Hike the Grand Canyon with my sister, we are travelling companions and love to see places together.
YES! We made and hiked several other parks too in these two years. This will be an ongoing goal.
7 See two new countries atleast – Mexico with SQL Cruise, one more towards end of the year – remains undecided for now. But two countries it is.
YES! I saw two new countries – Mexico and Jamaica.
8 Blog on books read so that I can understand the time I devote to reading and range I read in that time.
I did not get to blogging much on what I read but managed to read quite a few books. Also contemplated on reading strategy and this will be a different type of goal (not number based) in the days to come.
9 Get home renovation work done – am undecided on if I want to keep this condo or sell it, but either way, I’d have to get work done on it. Best if it got done this year, but involves considerable financial commitment that am not sure I can meet. As of now it looks doable for this year, but may move to next year if I have to reset goals.
YES! I also sold the home after renovation. This has also changed, don’t plan to own a home anytime soon. Too much money and effort maintaining it and very limiting in so many ways.
10 Increase collection of annotated classics by year end. This is an ongoing goal to build a library for retirement. The only books I buy in print are annotated ones or those with pictures. There are not many of those and my collection is upto 30-40% of what I need already. I keep adding to it @3-4 books a year.
I am doing good on this and don’t think this should be a yearly goal, more like a long term one. I got rid of many books which I thought I did not need in paper form at all.
10 Take a course on cartooning and short story writing – both of these are my pet hobbies and never had as much time for them as I’d like – this year would like to atleast take a course on each to deepen my love and interest. This did not happen. Too many conflicting priorities. But I did get to writing my first book and plan to pursue writing seriously in the years to come.
2018: 1 Submit to speak at PASS Summit. This did not happen for same reasons cited above. I do not plan on this being a goal until 2020.
2 Organize SQL Saturday #10 at Louisville (not clear how different this will be yet…). YES! SQL Saturday Louisville #10 was a phenomenal success – handed over reins of running it to new team and finding other things to do with the time I spent on it.
3 Keep up same goals for exercising. YES! This is going on well.
4 Visit one new country with my sister – am looking at Bali/Indonesia now.
We visited three national parks as opposed to one originally planned (Zion and Quinault Rainforest). So this was not met in theory but substituted with other valid fun things to do.
5 Visit one more new country on SQLCruise, hopefully, or on my own. Either way, I do it. YES! I visited Mexico in 2017 and Jamaica in 2018 with SQLCruise.
6 Biggie – Pay off my mortgage. Yes, this is important and am not that far away. The only thing that keeps me from it is a bit undecided on how long I can live here with job opportunities being what they are. But I will assume those will be the same and in that case, the house will be ready to be paid off in 2018. The house was sold instead of being paid off as I relocated to another state. I consider this goal as met although in a different way.
7 Do actual analytics work – by this time I will have a reasonable understanding of R/SAS/Microsoft Data science related skills, and expect it to take me to the next level professionally.
This was a bad goal to set, to begin with. My interviewing experience told me that very few places are doing analytics seriously to begin with, and those few places are looking for ph ds and people with a decade or more of experience to fill the role. I was able to get off pager carrying DBA role and get into doing more of data architect/coding type of work, which was what I wanted to do. Future goals will not be so specific, instead focus on what direction I want to go and where am going, instead of landing actual work which will happen on its own with time.
Goals for 2019 are as below: 1 Finish off Microsoft Big Data Certification. I am already working my way through it. I will devote every saturday evening to it and hope to get it done. 2 Continue with watching pluralsight/pass summit videos and listening to podcasts whenever time permits. 3 Complete writing second book I have committed to. 4 Read two pages of a tech book every day evening with tea. I am intentionally keeping this goal very small and doable. This is also based on a few experiences with reading. I do not have to read a tech book page-to-page. There are parts that are more interesting and useful than others. I need to keep notes to recall/reinforce what I read. I will be resuming reading with this in mind. 5 Blog @ one post every two weeks – again, scaled down from one post per week and trying to keep it modest and consistent. 6 I am planning on 4 tech events – SQL Saturdays at Raleigh, neighboring Charlotte, Louisville and then the PASS Summit towards end of the year.
On personal front – 1 I plan to continue with hiking and exploring national parks. I am planning a trip in Spring/early summer with some visiting family members. I do not think an overseas trip will materialise this year. 2 I plan to continue with healthy eating and exercising goals.
That is my rather simple goal list. If there is one lesson i have learned with writing this down is that – it helps improve my personal commitment level, it helps keeping the list small and simple. I think with a short list, commitment and simplicity, we can all get there. Happy 2019!
Curious..about data 