{"id":9797,"date":"2024-02-28T23:32:21","date_gmt":"2024-02-28T23:32:21","guid":{"rendered":"https:\/\/craftydba.com\/?p=9797"},"modified":"2024-12-26T21:02:42","modified_gmt":"2024-12-26T21:02:42","slug":"thread-05-data-engineering-with-fabric","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=9797","title":{"rendered":"Thread 05 \u2013 Data Engineering with Fabric"},"content":{"rendered":"

\"Microsoft<\/p>\n

Data Presentation Layer<\/h2>\n

Microsoft Fabric allows the developer to create delta tables in the lake house. The bronze tables contain multiple versions of the truth and the silver tables a cleaned up, single version of the truth. How can we combine the silver tables into a relational model for consumption from the gold layer?<\/p>\n

Business Problem<\/h4>\n

Our manager at adventure works<\/b> has asked us to use a metadata driven solution to ingest CSV files from external storage into a Microsoft Fabric. A typical medallion architecture will be used in the design. The final goal of the project is to have end users access the gold layer tables using their favorite SQL tool.<\/p>\n

Technical Solution<\/h4>\n

Microsoft Fabric supports two data warehouse components<\/a>: SQL Analytics Endpoint and Synapse Data Warehouse. The image below shows that the endpoint is read-only<\/b> while the warehouse supports both read<\/b> and write<\/b> operations. It makes sense to use the SQL Analytics Endpoint since we do not have to write to the tables.<\/p>\n

<\/p>\n

The following topics will be covered in this article (thread).<\/p>\n

    \n
  1. create new lake house<\/li>\n
  2. create + populate metadata table<\/li>\n
  3. import + update child pipeline<\/li>\n
  4. create + execute parent pipeline<\/li>\n
  5. review bronze + silver tables<\/li>\n
  6. use warehouse view to combine tables<\/li>\n
  7. use warehouse view to aggregate table<\/li>\n
  8. connect to warehouse via SSMS<\/li>\n<\/ol>\n

    Architectural Overview<\/h4>\n

    The architectural diagram for the metadata driven design is seen below. This is the child pipeline<\/b> that I have been talking about. Please remember that the data pipeline uses a full refresh pattern. The bronze tables will contain all processes files and the silver tables will contained the most recent file (data).<\/p>\n


    \n<\/p>\n

    The parent pipeline<\/b> will call the child pipeline for each meta data entry. At the end of a successful execution, the lake house will be created for all saleslt<\/b> tables for both the bronze and silver zones. We will be working on the creating parent pipeline in this article.<\/p>\n


    \n<\/p>\n

    The above diagram shows how the presentation layer works. By default, all tables in the lake house (spark engine) are available to the SQL Analytics Endpoint as read only. To date, spark views are not supported<\/font>. Do not fret. We can combine and aggregate data using SQL views in the data warehouse. After completing the warehouse or gold layer, we will retrieve some data using my favorite tool (SQL Server Management Studio).<\/p>\n

    Meta Data Tables<\/h4>\n

    A meta data driven design allows the developer to automate the copying of data into the lake (raw<\/b> files) as well as building the bronze<\/b> and silver<\/b> tables. The image below shows the meta data table viewed from the Lake House explorer. Please note, only the meta data table exists at this time and the local raw folder is empty.<\/p>\n

    <\/p>\n

    Let’s review on the meta data table right now with the following fields.<\/p>\n

      \n
    1. pipeline id – just an identifier<\/li>\n
    2. container name – same path in source and destination storage<\/li>\n
    3. file name – same file in source and destination storage<\/li>\n
    4. header flag – does the data file have a header line<\/li>\n
    5. delimiter string – how is the tuple divided into columns<\/li>\n
    6. table name – the root name of the table<\/li>\n
    7. schema string – infer schema or use this definition<\/li>\n<\/ol>\n

      Each piece of information in the delta table is used by either the pipeline activities or spark notebooks to dictate which action to perform. Of course, there is a one to one mapping from source data files to rows of data in the meta data table. Please see the folder listing of the source storage account as shown below.<\/p>\n

      <\/p>\n

      Please look at the nb-create-meta-data<\/b> notebook for more details on how to reload the meta data table from scratch. Just download the zip file at the end of the article and find the file under the code folder.<\/p>\n

      Parent Pipeline<\/h4>\n

      If you did not notice, I created a new lake house named lh_adv_wrks<\/b>. Since this task is so easy, I did not include any instructions. See the MS Learn webpage<\/a> for more details. The data pipeline named pl-refresh-advwrks<\/b> is the parent pipeline we are building out today. It calls the child pipeline named pl-delimited-full-load<\/b> with the pipeline id ranging from 1 to 11.<\/p>\n

      <\/p>\n

      To write a while loop, we usually need two variables in a programming language like Python. The first variable is a counter and the second is the dynamic limit.<\/p>\n

      \ncnt = 1\nlmt = 5\nwhile cnt < lmt:\n  print(f\"Number is {cnt}!\")\n  cnt = cnt + 1\n<\/pre>\n
      Data Factory does not allowed the developer to set a variable to itself.  It is just a limitation of tool.  The image below shows the error when trying to set the variable named var_cnt<\/b> to itself plus one.  The solution to this problem is to use another variable.<\/p>\n
      <\/p>\n
      The image below shows the test condition for the until loop<\/a>.<\/p>\n
      <\/p>\n
      The variable named var_cur<\/b> is used as a temporary variable since we can not use self assignment.  Thus, we set the current value equal to the counter plus one.<\/p>\n
      <\/p>\n
      The last step of the process is to set the counter to the current value.  See the assignment of the var_cnt<\/b> variable below.<\/p>\n
      <\/p>\n
      After successful execution of the parent pipeline, the lake house will contain refreshed data.<\/p>\n
      System Review<\/h4>\n
      The workspace is where all the objects in Fabric are shown.<\/p>\n
      <\/p>\n
      The table below shows the objects that are part of the Adventure Works lake house solution.<\/p>\n
        \n
      1. nb-create-meta-data - this delta table describes how to load the system<\/li>\n
      2. nb-delimited-full-load - read raw files, refresh bronze + silver tables<\/li>\n
      3. nb-test-schema - this Spark SQL code tests the schema used in meta data table<\/li>\n
      4. pl-delimited-full-load - copy file from source to raw, execute spark notebook<\/li>\n
      5. pl-refresh-advwrks - call the full load pipeline for all source files\/li>\n
      6. lh_adv_wrks - the lake house<\/li>\n