Skip to content

Latest commit

 

History

History
84 lines (56 loc) · 6.42 KB

Challenge-01.md

File metadata and controls

84 lines (56 loc) · 6.42 KB

Challenge 01 - Building Out the Bronze

< Previous Challenge - Home - Next Challenge >

Introduction

The overall goal of this hackathon is to take data from 2 different data sources and combine them into consolidated tables so that the business user(s) consuming this data do not realize that it originated from different data sets

We will use the AdventureWorksLT and WideWorldImporters SQL databases for our source data.

For this hackathon you will use Azure Synapse and/or Azure Databricks to copy data from these source systems and format them appropriately for business user consumption by utilizing a three-tiered data architecture.

Description

We are now ready to setup the environment and populate the data into the Bronze Data Layer. For this challenge, we want to bring in the data "as is". No data transformation is needed at this layer.

  • Environmental Setup
    We need to set up the proper environment for the Hackathon. Thus, we need everyone on the team to have access to the Azure Synapse and Databricks environments. Also, any ancillary resources such as Power BI, the Azure Storage Accounts and Key Vault.

    It would be a good idea for each team to host the solution in a new Resource Group in a subscription that all particpants have access to. Thus, at least one person should be owner of the Resource Group and then provide the rest of the team the proper access to that Resource Group. For more informaton on this topic, see the Learning Resources below.

    This way each person in the team can take turns to lead the hack and just in case one person has to drop, the rest of the team can still progress through the challenges.

  • Hydration of Data in the Bronze Data Lake
    For this challenge we will be working with two SQL data sets:

    • AdventureWorks
    • WideWorldImporters

    You will not setup the source databases for this challenge, they are setup and configured already. Your coaches will provide the connection details for these data sources for you to utilize.

    The goal is not to import all data from these databases, just choose to only bring in either the Customer or Sales Order data. There is no need to do both.

    HINT: Customers have addresses and Sales Orders have header, detail and product associated with them. Both have their own complexities, so one is not easier than the other. See the graphic below for reference.

picture alt

Things to keep in mind about data in the Bronze layer:

  • Organized by Source
    Have a look at the above diagram to get an idea of what your storage organization would be like.
  • Data Landed in Native Format​
    In an effort to load data as-is, we often keep the data format in the Raw Layer the same as it is in the source. This means our Raw Layer often has CSV and TXTs, depending on the type of source.
    That being said, if we manage to bring in the data in Parquet format instead, that would be an added bonus.
  • Additional Checks
    • Schema Validated​
    • Lake Hierarchy Applied​
    • Timeliness Assessed​
    • Elements Tagged​
    • Completeness and Accuracy Accepted

Business Case

Now that we know what we need to do, it’s also important to understand why we are doing this.
From an organizational standpoint, the Bronze layer serves two main purposes:

  • Long Term Storage and Recovery
    Organizations often need a place to dump huge amounts of structured and unstructured data for processing and Azure Data Lake Storage is often the cheapest and most efficient way to do say. The Raw layer is designed in a way to effectively act as a sink for further processing as well as a store for data for however long it is required. This can act as a backup for other systems as well.
  • Auditability
    Since we don’t do much in terms of additional processing on the data we bring into the Raw layer, the data is unfiltered and unpurified, which is a good thing in terms of auditability. This raw data is always immutable and can act as a reference of what the source system looked like at a given point in time. We usually keep the data in its native format, but this holds true even if we decide to store it in a more modern data format.

Even though the Bronze layer is generally locked down in most organizations, some teams are often given access to it to do some quick discovery work. This is often the case with Data Science teams working on prototyping a new solution.

Success Criteria

To complete this challenge successfully, you should be able to:

  • Validate that all resources exist in one Resource Group and are tagged appropriately.
  • Validate that all team members have proper access to all the assets: Storage Accounts, Synapse and Databricks Workspaces, etc.
  • Showcase the data copied into the Bronze layer and be able to articulate the file type and folder structure and the reasons for your choices.
  • Showcase that no credentials are stored in an Azure Synapse Linked Service or a Databricks Notebook.

Learning Resources

The following links may be useful to achieving the success criteria listed above.

Bonus Challenge