Voice of the DBA

Tag: career

  • ETL v ELT

    This is part of a series on my preparation for the DP-900 exam. This is the Microsoft Azure Data Fundamentals, part of a number of certification paths. You can read various posts I’ve created as part of this learning experience.

    I don’t have an ELT tag, and I’m not likely to make one. I tend to think of ETL as loading data somewhere, even though I know it means more.

    The important concepts for DP-900 here are that ELT is becoming more important and you need to understand what this means. I’ll cover these concepts, but also give a little overlap with where the different Azure services fit in with this.

    ETL

    For most of my career, the pattern for loading data was Extract-Transform-Load. In this pattern we:

    • grab data from a source
    • make changes to clean/change/etc.
    • write to a target (or sink)

    It’s how tools like SSIS work. They connect a source to a target and have a bunch of tasks or transforms in the middle that change the data in some way.

    This is a good pattern for getting the work done when the target system is just built for querying data, such as a data warehouse. It is also good when you need to scrub some data, perhaps for privacy reasons.

    This isn’t a good pattern when you are trying to load data quickly as the transform process takes time.

    ELT

    This is the new way of doing things. I this patter we Extract-Load-Transform, though really, it’s not a pattern that quite makes sense in that the process of moving the data just moves it.

    Here we:

    • grab data from a source
    • write it to a target

    Where’s the transform? Well, that happens on the target, often when someone queries the data. Modern analytic systems, like Snowflake and Synapse, can work with vast quantities of data, often stored in a data lake or blob system, and consume that with powerful computational capabilities. There could be some minor re-shaping of the data on write, but that’s not the idea.

    This is good when you might not read all the data. Why process (transform) what isn’t being read. Before you complain that you should know what is used, none of us know if all our data is being used. Unless we write crappy SELECT * code with no WHERE clauses.

    This is also good when we need to work at speed and privacy isn’t a concern. It’s great for the known formats of files sent to us, as the target system can project a table on top of a ser of files.

    ELT seems to be the current future direction of many analytical and warehouse systems.

  • Is the Time of the DBA Ending?

    I ran across a piece on the fall of the DBA (or part of a piece), and wondered if this is the time when the DBA is disappearing. I do know that plenty of organizations never formally adopted the DBA as a position, and plenty more have abandoned it. Not that there aren’t people managing databases, but they have moved into Database Engineer, SRE, or plain old sysadmin. They might even have another job but function as an accidental DBA.

    The DBA role has often been a mix of many things. Certainly, this is a tactical, operations role that keeps systems running, dealing with performance, security, and availability. DBAs also fix quality issues in production, deploy code changes, and often advise developers on what works well and what doesn’t. They tend to be a bit of insurance against things going wrong as well as the scapegoat when anything does go wrong. I found the DBA role to be a constantly changing, never-ending grab-bag of challenges on a regular basis.

    Those tasks don’t go away with a title change, nor with a move to the cloud, the adoption of NoSQL data stores, or the move to a DevOps style of software development. They might get handled by another role or team, but they still need to be dealt with. The DBA might need to change titles or groups, and they might need to learn how to use automation, GitOps, or some other new protocol for handling the work. As the article linked above mentions, they may need to tackle work at a higher level, not the manual, click, click repetitive process they might have followed in the past. The trend is often to work smarter, not harder.

    As with any change, there will be people who succeed and do better in the future, as well as those that find their best days behind them. I find change often brings opportunities if I am open to them and make an effort to look for those opportunities. You might find your title changes and you can ask for more compensation or more interesting work as the role changes.

    Embrace change, learn something new and find a way to make change work to your advantage. There is always a way to make this happen.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • Data Analysis Techniques

    This is part of a series on my preparation for the DP-900 exam. This is the Microsoft Azure Data Fundamentals, part of a number of certification paths. You can read various posts I’ve created as part of this learning experience.

    I didn’t think much of this bullet on the DP-900 skills document: describe analytics techniques.

    Learn these concepts. They are definitely part of the knowledge needed. This is a part of some MS Learn courses, like this one.

    Descriptive Analytics

    Descriptive analytics have to do with describing what the data shows. In a report or visualization, we are reporting data to the user. An example of this is showing the top 10 products according to sales.

    These are explanatory analytics and think about that in the test. Are we explaining something.

    Diagnostic Analytics

    A diagnosis is an explanation of why something happened. Diagnostic analytics help explain the reason that something occurred. We might look at the top 10 sales against advertising for products and determine that more advertising shows higher sales.

    In the link above from MS Learn, what we typically do here is identify some anomaly or thing we want to explain, get data related to this, and then use some statistics to find a relationship.

    Predictive Analytics

    As you might guess, this type of analytics relate to the future. We are looking to predict what will happen. An example might be forecasting future demand for services.

    When you see a request to predict, forecast or extrapolate, think predictive analytics.

    Prescriptive Analytics

    This type of analysis was a little harder for me to understand or grasp. I get the general idea, but in prep I missed this one a few times. Prescriptive analytics are developing a prescription for how to change something.

    An example might be if I want to find where to cut costs to impact profit, I am looking for a set of things to change. I need a prescription on what will help me achieve my goal.

    In relation to the exam, this has to do with aiming for a goal (10% more or 12 less) and how to reach that goal. If you see specific numbers, usually that relates to a goal and prescriptive analytics.

    Cognitive Analytics

    This type of analysis has to do with thinking. In data analysis and computing, usually I think ML, AI, or some other type of data mining. This can be deriving conclusions or inferences from data, but the blend between this and the descriptive/diagnostic stuff becomes blurry.

    If you need to do speech to text or video transcription or image recognition, think cognitive.

    Summary

    This is how I viewed this techniques on the exam when answering questions:

    • Descriptive
      – What

    • Diagnostic
      – Why

    • Predictive
      – What will happen

    • Prescriptive
      – What do I do

    • Cognitive – Learning about data in new ways

  • Goal Progress for Feb 2022

    I set goals at the beginning of the year, and I’m tracking my progress in these updates during 2022.

    February became more of a work month for me, and a somewhat busy month outside of work with life. I didn’t do enough on my career, and am more stalled than I’d like. I let distractions take over more than I should, and I felt a bit bad at the end of the month, rushing through some things the last week of Feb to make a little progress.

    I’m slowing down, but at least I did something. I’m lowering the grade because I just didn’t do enough.

    Grade: C+

    Here are the goals:

    Work

    • DP-900 – Passed
    • DP-300
      – Stated studying, practice tests around 60% correct
    • Demo with SQL Server – 5%
    • Demo with PostgreSQL – 0%
    • Demo with MySQL – 0%
    • Feedback book – 72%
    • Good Strategy/Bad Strategy – 0%

    Personal

    I made a slight alteration here. I didn’t try to hook up to Google Sheets, but instead pasted in some data and started to try and get this moving.

    • Link Google Sheets to Power BI – 0%
    • Create Report – 0%
    • Create Dashboard – 0%
    • Get people using it – 0%

    Community

    • Support the Colorado groups by speaking twice and helping get one event set up
      – 33%

      • CO Springs event set up
    • Speak at 3 other user groups outside of Colorado – 33%
      • Spoke at DBA Fundamentals group
    • Support SQL Saturdays – Help get 10 events run in 2022
      • 3 scheduled, 2 more close
    • Volunteer 4 days with Habitat – 0 days