In real-world scenarios, data pipelines seldom operate within the confines of a single AWS account or region. Modern data lakes scale and often transcend geographical and service boundaries. With dbt-glue, we've already demonstrated how to build a data pipeline that is database engine agnostic. Using dbt, you can easily configure projects to interact with diverse data warehouses and databases, creating a unified, streamlined data transformation process while maintaining data lineage.

As organizations grow, new opportunities and partnerships naturally come up. This often involves sharing data assets with new partners or across different departments. If you’re lucky, your partner organization might already be using the same cloud provider. However, assuming that all data and services will reside in the same region is, more often than not, unrealistic.

With that back-drop let's discuss strategy to extend our existing AWS Glue data pipeline using dbt and Airflow.

The Challenge

Let’s consider the following scenario, Imagine your organization, Oranges, has recently signed a memorandum of understanding (MOU) with another organization, Pears, to share specific data assets. Oranges already has a well-established data lake set up in a AWS region. The agreement specifies that certain datasets from Oranges will be shared with Pears, who will store and process this data in their own AWS account, located in a different region.

This might sound straightforward, but there’s a catch: Oranges has invested heavily in their current data lake infrastructure, and a lift-and-shift to another region or a new tool isn’t feasible. Simply copying files between accounts isn’t a viable solution either; Oranges wants to maintain incremental data processing, apply safeguards, and control what data is handed over. This means we need a solution that is far more sophisticated than a basic file transfer.

With that Oranges and Pears agreed on setting up a dbt-glue job that would select data from certain tables in Oranges AWS account region us-west-1 and write the data into Pears AWS account in region us-east-1

The Solution

After careful consideration, Oranges and Pears agreed on a strategy to set up a dbt-glue job. This job would read data from specific tables in Oranges’ AWS account ( assume region us-west-1) and write the output directly to Pears’ AWS account in a different region (assume us-east-1). Here’s how they tackled it:

1. Glue Catalog Setup: Oranges configured an AWS Glue Data Catalog in the us-east-1 region to manage metadata for the new pipeline.
2. Existing ELT Pipelines: The current ELT pipelines run daily, writing Iceberg tables to S3 buckets in us-west-1. These tables are already registered in the AWS Glue Data Catalog.
3. New Pipeline: The new pipeline reads from these Iceberg tables and registers a new table with a few key considerations:

• The job runs in Oranges’ AWS account (any region).
• Data is written to a shared S3 bucket set up in Pears’ AWS account in the us-east-1 region.

Pros and Cons of the Approach

Pros:

• Familiar Mechanism: This approach extends the existing setup with minimal changes, leveraging the current infrastructure.
• Consistent Data Format: Using Apache Iceberg ensures that the data format remains consistent and optimized for large-scale queries.
• No New Services Required: There’s no need to onboard a new service or tool, which keeps maintenance and complexity low.
• Incremental Processing & Testing: The code is written in Spark SQL and managed by dbt, which provides built-in mechanisms for incremental processing and testing.

Cons:

• Complex Session Configuration: Setting up Glue and Spark session configurations to handle cross-account, cross-region jobs can be tricky and prone to errors.
• Limited Documentation & Support: While there is documentation available, it’s often sparse and the community support for this specific setup is still quite limited.

Before we get too deep into the weeds

Although not necessary, it would be a good idea to familiarize yourself with these topics. If nothing else, it will help youappreciate the saga.

AWS Glue & Athena : Basics of Glue Catalog and Jobs for data integration and ETL workflows and Athena Query editor.

IAM & Permissions: Basic understanding of AWS IAM roles and cross-region/cross-account access.

dbt: Experience with dbt for transforming data, managing models, and using profiles.

Iceberg & S3: Familiarity with Iceberg tables, S3 data storage, and querying strategies.

Spark: Basic knowledge of Spark jobs in AWS Glue and optimizing performance for large datasets.

The Journey: Overcoming Cross-Region Hurdles with Iceberg, Glue, and Spark

Tackling a project like this felt like setting out on an expedition — full of unexpected twists and the thrill of discovery. With Oranges and Pears agreeing to share data across accounts and regions, we had a plan in place, but the path wasn’t as straightforward as it seemed. Here’s how the journey unfolded:

Step 1: Iceberg’s Assumptions (And Ours)

We started with Iceberg’s suggestion: use AssumeRoleAwsClientFactory to dynamically assume a role and switch regions as needed. On paper, it seemed perfect—just assume the right role, make sure it has access to the target bucket in Pears’ AWS account (us-east-1), and let the magic happen.

Reality check: when we registered the Iceberg catalog using this setup, it couldn’t read from the local Glue catalog. We hit our first obstacle — region conflicts. Iceberg couldn’t play nicely when we changed the region within session.

It was like following a map only to realize the road you’re on doesn’t exist. We needed to find a way to access Glue tables across regions.

Step 2: Navigating the AWS Glue Maze

Next, we turned to AWS Glue’s documentation, which pointed us towards creating a data link in the target region. This link would act as a bridge, connecting us to the source region’s database. Great! We tested it out using Athena’s query editor in the target region, and it worked seamlessly.

We were optimistic — until we tried running the Glue interactive session in the source region (us-west-1) and switched regions dynamically to the target (us-east-1). Suddenly, the bridge seemed to vanish. The catalog couldn’t use the data link, throwing errors left and right. Sometimes, it couldn’t find the table; other times, it struggled to resolve the correct S3 endpoint.

It felt like we had found a hidden passage but couldn’t unlock the door. We needed a different approach.

Step 3: The Aha Moment with Multi-Catalog Magic

After scouring forums and reading between the lines of every error message, we pieced together a new plan. Instead of forcing one catalog to do everything, why not set up two catalogs with clear roles?

1. The Default Catalog (glue_catalog): Handles reading and writing data in the local region. It’s our trusty companion for familiar terrain.
2. The Additional Catalog (read_catalog): Specially configured for cross-region reads using an assumed role. This one is like our guide for navigating foreign lands.

In a Spark session, a catalog is a unified interface for managing and querying metadata of databases, tables, and views. It acts as a registry that tells Spark where and how to access data stored across different sources, like Glue, Hive, or Iceberg.

By embracing this dual-catalog setup, we found the sweet spot between the flexibility of a PySpark notebook and the structured capabilities of dbt models. We could read from our local region, switch roles, and then write to the target region without breaking stride.

The Payoff: A Harmonious Workflow

This solution felt like finding the last missing piece of a puzzle. It allowed us to extend our existing dbt-glue setup without adding unnecessary complexity or services. The journey wasn’t without its frustrations, but it led us to a creative and robust solution — a fitting reminder that the path to success is rarely a straight line.

Configuration Journey: Overcoming the Cross-Region Hurdle

To make our cross-account, cross-region pipeline come to life, we needed a solid configuration that blended familiar tools with creative solutions. Our approach was to leverage a dual-catalog setup in dbt-glue:

We created two catalogs:

• glue_catalog: This is our primary catalog for reading and writing data within the target region.
• read_catalog: As the name implies, it’s tailored for reading source data from the original region.

The dbt-glue session kicks off in the target region, where the TARGET_S3_BUCKET is located.

The GLUE_SERVICE_ROLE handles the heavy lifting with permissions across both regions:

• It must be able to assume roles, making it crucial to grant it permissions explicitly, even on itself.
• The role needs full access to discover and manage Glue tables, plus S3 permissions to handle operations on the shared Pears’s S3 bucket.

With these fundamentals in place, here’s how we defined the project configuration:

cross-region-project:
  outputs:
    test-target:
      type: glue
      role_arn: "{{ env_var(‘GLUE_SERVICE_ROLE’) }}"
      region: <target_region>
      glue_version: "4.0"
      workers: 10
      worker_type: G.2X
      idle_timeout: 10
      schema:  "{{ env_var(‘SCHEMA_NAME’) }}"
      session_provisioning_timeout_in_seconds: 900
      location: "{{ env_var(‘TARGET_S3_BUCKET’) }}"
      datalake_formats: iceberg
      default_arguments: "--enable-auto-scaling=true,  
                          --enable-metrics=true, 
                          --enable-continuous-cloudwatch-log=true, 
                          --enable-continuous-log-filter=true, 
                          --enable-spark-ui=true"
      conf: "spark.sql.legacy.allowNonEmptyLocationInCTAS=true
            --conf spark.sql.extensions=org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions 
            --conf spark.serializer=org.apache.spark.serializer.KryoSerializer 
            --conf spark.sql.warehouse={{ env_var(‘TARGET_S3_BUCKET’ )}}        
            --conf spark.sql.catalog.glue_catalog=org.apache.iceberg.spark.SparkCatalog 
            --conf spark.sql.catalog.glue_catalog.catalog-impl=org.apache.iceberg.aws.glue.GlueCatalog 
            --conf spark.sql.catalog.glue_catalog.io-impl=org.apache.iceberg.aws.s3.S3FileIO 
            --conf spark.sql.catalog.glue_catalog.lock-impl=org.apache.iceberg.aws.dynamodb.DynamoDbLockManager 
            --conf spark.sql.catalog.glue_catalog.lock.table=myGlueLockTable
            --conf spark.sql.catalog.glue_catalog.warehouse={{ env_var(‘TARGET_S3_BUCKET’) }}
            --conf spark.sql.catalog.read_catalog=org.apache.iceberg.spark.SparkCatalog 
            --conf spark.sql.catalog.read_catalog.catalog-impl=org.apache.iceberg.aws.glue.GlueCatalog
            --conf spark.sql.catalog.read_catalog.io-impl=org.apache.iceberg.aws.s3.S3FileIO 
            --conf spark.sql.catalog.read_catalog.lock-impl=org.apache.iceberg.aws.dynamodb.DynamoDbLockManager 
            --conf spark.sql.catalog.read_catalog.lock.table=myGlueLockTable
            --conf spark.sql.catalog.defaultCatalog=glue_catalog
            --conf spark.sql.catalog.read_catalog.client.factory=org.apache.iceberg.aws.AssumeRoleAwsClientFactory
            --conf spark.sql.catalog.read_catalog.client.assume-role.arn={{ env_var(‘GLUE_SERVICE_ROLE’)}}
            --conf spark.sql.catalog.read_catalog.client.assume-role.region=us-west-1
            --conf spark.sql.catalog.read_catalog.warehouse={{ env_var(‘TARGET_S3_BUCKET’) }}"
      tags: "application=app_name, owner=DE, env=TEST"

Putting It All Together: Sample Model

The heart of our implementation is a simple but powerful dbt model. We read source data using read_catalog and merge it into the target table using glue_catalog.

Here’s a sample model:

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='sales_id'
) }}

WITH source_data AS (
    SELECT
        sales_id,
        customer_id,
        product_id,
        sales_date,
        sales_amount
    FROM read_catalog.source_database.sales_data
    WHERE sales_date > (SELECT MAX(sales_date) FROM glue_catalog.target_database.sales_data)
),

final_data AS (
    SELECT
        sales_id,
        customer_id,
        product_id,
        sales_date,
        sales_amount
    FROM source_data
)

SELECT * FROM final_data

In this model:

• read_catalog reads from the source data in us-west-1.
• glue_catalog retrieves the watermark for incremental data processing.
• dbt automatically uses glue_catalog for merging the data into the target table in the us-east-1 region.

Tips for a Smooth Ride

1. Permission Setup: Double-check IAM role permissions for seamless cross-account S3 access, Glue catalog operations, and STS role assumptions.
2. Session Configuration: Register your catalogs right at the start of your Spark session to avoid pesky errors about unregistered catalogs.
3. Testing: Start with a small data subset before unleashing it on the full dataset in production.

Wrapping It Up

Our dual-catalog strategy within dbt-glue bridges the gap between two regions and accounts with ease. By leveraging the strengths of Apache Iceberg and dbt, we’ve sidestepped the common pitfalls of default Glue configurations and crafted a scalable solution. This journey wasn’t just about moving data — it was about finding the most efficient path forward, one configuration at a time.

Further Reading

• AWS Glue Documentation
• Apache Iceberg Cross-Region Cross-Account
• dbt-glue GitHub Repository
• AWS Glue Cross-Region Access Guide

I came across the AWS big data blog that advocates using S3 as data store and manage transformations using GLUE spark interactive session, which are run using the DBT core with GLUE adapter.

Ref : https://aws.amazon.com/blogs/big-data/build-your-data-pipeline-in-your-aws-modern-data-platform-using-aws-lake-formation-aws-glue-and-dbt-core/

The issue most Analytics team face with transforming data with warehouse are:

• Scalability / Resource contention between analytics workloads and ELT workloads
• Limit on data formats and interoperability across different data processing engines
• Performance pitfalls when querying highly nested data

Most of these could be solved by migrating to snowflake but it may not always be a feasible option.

A good solution to enjoy processing big data with spark and query tables with Red Shift is using managed GLUE-Spark jobs, however consider the following pros and cons:

Pros:

• The scheduling is in built with GLUE workflows
• Glue Studio provides an interactive visual designer for pipeline development

Cons:

• The data lineage is not provided out of the box
• Code if generated by the visual editor will be boiler plate and not optimized
• The code will be PySpark so significant learning curve if teams are more well versed with SQL, (even if running logic with spark SQL operators)
• Needs explicit integration to commit code to version control tools like git

Another variation which provides the same power and flexibility of using Glue managed spark jobs while maintaining transformations as SQL is using DBT-Glue adapter. Its is a good half way between continuing to use SQL for developments where DBT generates the spark code around your data models to run the managed Glue Jobs. That being said it comes with its own quirks. Below are some pros and cons of this approach.

Pros:

• Analytics team are more familiar using DBT
• DBT is flexible and can be used against several data processing engines (redshift, snowflake, mysql, postgres)
• DBT is primarily SQL code, with short learning curve to pickup DBT
• DBT provides ready way to generate and host DBT docs which help with schema and data-lineage documentation

Cons:

• Does not come with scheduling unless implemented with DBT cloud
• Will require orchestrator like Airflow to schedule transformation
• Configuring the required IAM users and service roles is a tedious task

The reference article talks about setting up the pipeline using AWS Glue run from AWS command shell. It uses lambda service to update and load data into AWS data catalog.

Setting up lambda to load and refresh data is very custom solution and will need maintenance and development efforts. A more out of box easy to configure option for development and deployment is using a crawler. Here is a jist of another end to end implementation in real world application. The use case I am tackling is more ETL then Analytics but to serve analytics at any stage:

• Use DMS to transport data from production database to s3 bucket
• Use a scheduled crawler to register new tables and load partitions into AWS data catalog table
• Use DBT-Glue with Airflow to run models and generate DBT-docs and land data into target s3 bucket
• Optionally use DMS again to take data from s3 target bucket and load into Redshift.
• Redshift can also integrate/query iceberg tables stored on S3 may need additional IAM configurations to allow this.

Now that we have decided to use DBT with Glue for ETL we need make a few more decisions before we can start implementation journey. One of the features that make DBT so desirable is it enables incremental updates and provides different materializations as supported by the underlying database engine. This takes away the complication of managing CDC from developers.

DBT-Glue has few more knobs that we need to tweak. The config block and the profiles allow for various data-lake formats. Each has its own advantage. We need to consider each and select one as this will significantly influence the set up. By default DBT uses Spark SQL and parquet hive storage file system and incremental models work at the partition level. Implementing a suitable data-lake unlocks “merge” operations and for all practical purposes makes DBT-Glue same to any relational databases incremental model, utilizing the unique key to incrementally update models.

The data lake standards supported by DBT-Glue are as follows:

• Hudi
• Delta Lake
• Iceberg

After considering each of the standards I chose to go with Iceberg.

Below are my learnings from setting up DBT to store tables as iceberg, and simulating a continuous updating data pipeline. I have tried to document configurations that are easy to miss and templates that worked for me.

Beginning DBT project starts with getting credentials to the underlying database. With DBT-Glue, glue managed spark jobs in form of interactive sessions serves as the database engine. Thus we need to start with setting up IAM.

To be able to run DBT glue jobs, DBT should be able to perform following actions:

1. Have associated IAM Glue Role: role authenticate with AWS account
2. IAM Glue Role is able to create, destroy and communicate with AWS Glue components to manage job life cycle
3. The role should have assume permission on the AWS Glue Role
4. The glue role suggested for this use case is the AWS managed AWSGlueServiceRole
5. The IAM user should have trust relationship to allow assume role for the AWSGlueServiceRole
6. When DBT executes the assume role, it establishes an assume role session which has its own secrets , session token and session name.
7. This session should also have assume role permissions on the AWSGlueServiceRole.
8. To the AWSGlueServiceRole, do not forget to add any other additional permissions like Dynamo access to create and destroy tables if using the Iceberg lake.

Below is sample trust relationship :

{
  "Version": "2012-10-17"
  "Statement": [ 
          {
            "Effect": "Allow",
            "Principal": {
              "Service": "glue.amazonaws.com" 
            },
            "Action": "sts:AssumeRole"
         },
         {
            "Effect": "Allow",
            "Principal": {
              "AWS": "<GLUE-USER-ARN>" 
            },
            "Action": "sts:AssumeRole"
         },
         {
            "Effect": "Allow",
            "Principal": {
              "AWS": "arn:aws:sts::<account_id>:assumed-role/AWSGLueServiceRole/<session_name" 
            },
            "Action": "sts:AssumeRole"
         }
]
}

Next we need to set up our DBT project to correctly use the IAM user and roles.

   dev_glue:
      type: glue
      role_arn: arn:aws:iam::000000000:role/GlueRoleName
      schema: "my_schema"
      location: "s3://my-s3-bucket/prefix/my_schema"
      iceberg_glue_commit_lock_table: DbtGlueLockTable
      region: <aws-region>
      workers: 8
      worker_type: G.1X
      glue_version: "3.0"
      idle_timeout: 15
      session_provisioning_timeout_in_seconds: 300
      connections: iceberg_connector_name
      conf: "--conf spark.sql.extensions=org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions
             --conf spark.serializer=org.apache.spark.serializer.JavaSerializer
             --conf spark.sql.catalog.glue_catalog.warehouse=s3://s3://my-s3-bucket/prefix/my_schema
             --conf spark.sql.catalog.glue_catalog=org.apache.iceberg.spark.SparkSessionCatalog
             --conf spark.sql.catalog.glue_catalog.catalog-impl=org.apache.iceberg.aws.glue.GlueCatalog
             --conf spark.sql.catalog.glue_catalog.io-impl=org.apache.iceberg.aws.s3.S3FileIO
             --conf spark.sql.catalog.glue_catalog.lock-impl=org.apache.iceberg.aws.glue.DynamoLockManager
             --conf spark.sql.catalog.glue_catalog.lock.table=DbtGlueLockTable
             --conf spark.sql.extensions=org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions
             --conf spark.sql.iceberg.handle-timestamp-without-timezone=true
             --conf spark.hive.default.fileformat=Parquet"
      default_arguments: "--enable-metrics=true, --enable-continuous-cloudwatch-log=true, --enable-continuous-log-filter=true, --enable-spark-ui=true, --spark-event-logs-path=s3://my-s3-bucket/prefix/sparklogs/"

The trouble I had was when I ran my model, dbt created a temporary table in default database. Since I am stickler for quality, I was able to catch that the serde assigned to this table was not compatible with the format. Although the file was parquet, it was not written to S3 with parquet extension. As a result AWS DataCatalog assigned the LazySimpleSerde to it. This results in data mis-allignment. After lot of research and trial and error I was able to come up with the above configs.

NOTE:

• before running model ensure both source and destination database are defined in AWS Glue Databases.
• The schema parameter associates with AWS databases

For more detailed explanation on setting up the DBT profiles for Glue visit DBT documentation: DBT Docs

Now that our project is set up we can run models. One of the common errors with iceberg table with DBT Glue is trouble referencing an existing table. To overcome this add the “glue_catalog” prefix before reference tables.

Declaring tables to be saved as iceberg use the following config block in the models:

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key=["customer_id"],
    file_format='iceberg',
    partition_by=['dt']
) }}

select * from  glue_catalog.{{ ref('my_other_table')}}

Setting up ELT with DBT-Glue can be both exciting and challenging. While DBT Docs offer valuable guidance, the devil lies in the detail. I have tried my best to outline my journey with DBT-Glue and some solutions that worked. Hope this provides you with some direction to start exploring!

In another project based in a real-world scenario, I acted as the Cloud Specialist responsible for migrating a workload running in a Corporate DataCenter to AWS.
The application and database were migrated to AWS using the Lift & Shift (rehost) model, moving both application and database data.

I followed some migration steps: Planning (sizing, prerequisites, resource naming), Execution (resource provisioning, best practices), Go-live (validation test — Dry-run, final migration — Cutover) and Post Go-live (ensure the operation of the application and user access).

Plan

During planning phase account for the on premise components and note down the following:

• Map the capacity being used to understand the cloud infrastructure to be provisioned.
• This includes number of nodes of web servers/databases, versions of OS and database being run CPU memory and total storage being utilized.
• Also note down the pre-requisites of running the application packages that should be installed and their dependencies.
• Maintain a 1–1 mapping of on premise components to the ones that will be created in AWS

Implementation

This is the actual execution of the planned infrastructure.

• Create the instances as planned in AWS.
• Apply best practices as suggested by AWS and industry practices to design the architecture.
• Implement security and safe guard against un-authorized access.

Go Live

• Dry run the application with test loads
• Perform all the workflows the application shoul support
• Identify any bugs or gaps early on.
• Cut over from on premise to cloud by shutting down the on premise, transferring data to cloud and redirecting load to cloud infrastructure.

Post Go Live Support:

• Monitor the solution closely to check work loads verify capacity is sufficient and there are no bottlenecks
• Respond rapidly to any production failures to reduce down time.

After Planning phase the identified architecture is as follows:

As part of this project I will be creating the following AWS Components:

Network:

• A Virtual Private Cloud (VPC) is a virtual network infrastructure provided by AWS. It offers a logically isolated section of the cloud where users can launch resources such as virtual machines, databases, and storage instances.
• The VPC is divided into multiple subnets.
• A public subnet is configured with a routing table that includes a route to the internet gateway, allowing outbound traffic from instances in the subnet to reach the internet and inbound traffic from the internet to reach instances in the subnet.
• A private subnet is configured with a routing table that does not include a route to the internet gateway. This prevents instances within the private subnet from establishing direct connections to the internet.
• An internet gateway is a horizontally scalable, redundant, and highly available component in Amazon Web Services (AWS) that enables communication between instances within a Virtual Private Cloud (VPC) and the internet. It serves as a connection point between the VPC and the public internet, allowing instances in the VPC to access the internet and be accessed from the internet.

RDS:

• Data base instance running MySQL

EC2:

• Ubuntu instance to run the web application

Implementation

VPC

On AWS console navigate to VPC service and start the process by clicking the create VPC. I am going to create all the components separately so proceeding with VPC only option. I provided a name and IPv4 CIDR block.

Considerations for CIDR block:

• It should not overlap with on premise network
• It should not overlap with other cloud infrastructure

As per the architecture the web server needs to be in public subnet such that it can access internet and users can access it. The database server should be within the private subnets and should only be accesses by application server. I will need an Internet Gateway to allow access to internet.

I am keeping this project open to internet since this is not an actual production application. In production application it would be prudent to great pre verified list of IP addresses expected to access the application and accordingly creating security groups.

Subnets:

Navigate to VPC and select subnets. I selected the previously created VPC to create subnet under it.

I name this as public subnet and give ti a CIDR block. 10.0.0.0/24

I selected the availability zone as the first on the list, this will be dictated by the application and its availability constrains if any.

Then I click create subnet and public subnet is ready.

I repeat the process to create a 2 private subnets by providing appropriate names.

Selecting the previously created VPC and assigning the CIDR blocks to ensure there is no overlap as follow :

• 10.0.1.0/24
• 10.0.2.0/24.

The optional step here is to add appropriate tags to the infrastructure. It is a good practice to tag resources as more applications are deployed and the infrastructure stack starts to grow it is difficult to remember where they are used and for what.

Tags can be handy to keep track of components and the associated costs.

Below are the subnets I created:

Internet Gateway

Select Internet Gateway from the VPC menu and select create internet gateway.

I provided a suitable name and click on create internet gateway.

Once the Internet Gateway is created navigate to the dashboard and locate the newly created gateway . Select the gateway and click on action and select attach to VPC.

Once I have the VPC, subnets and Internet Gateway created now to update the route table.

Route Table

Navigate to Route Table on left tab of VPC and select the route table attached to the VPC created previously.

Then click edit routes

The local will be pre populated and I added the access to internet as below:

The ip 0.0.0.0/0 indicates traffic from anywhere will be routed through internet gateway.

This concludes the network set up.

Web Server EC2

Navigate to the EC2 service and select launch an instance

Provide the name for the instance and select the image, here I am going with ubuntu this will again be dictated by the on premise environment.

Next is to select the instance type this depends on the capacity sizing exercise done in planning phase. The goal is to select an instance with at least as much capacity (CPU, memory and storage) as the on premise server.

I created a key pair for accessing this instance and downloaded the key to perform the next steps of set up.

Now I define the network settings by clicking the edit. Select the previously created VPC and assign it the public subnet. I enabled public IP and created a new security group. This allows the EC2 to be connected with SSH from anywhere. Since this is not recommended, I am going to restrict this access to my IP only.

Now add another rule to open up port 8080 to the internet to be able to access the application.

AWS RDS

Navigate to RDS service and click create Database and select standard create.

I will database with MySQL engine and select the version, here I try to select the same version as the on premise data base. I want to keep the same versions so as to not introduce unwanted errors. Upgrades can be taken up as separate projects later once migration is successfull.

I am going to use the free tier for this exercise:

I give the database instance a suitable name and create the root user and set the password

Then I select the capacity, this is defined in the planning phase based on the capacity of the on premise database.

Now I set up the network configuration, the database does not need to connect to compute resource. I choose the previously created VPC and I do not want the database to be accessible from the internet.

I will create a new security group and give an appropriate name and choose the correct availability zone this should be decided during the planning phase.

I set the authentication to be password authentication

Re-configure the security group to allow access to database from EC2 instance:

• Go to the VPC service on console
• Select the security groups in the security sub sections
• Select the security group created for database instance by the name given at creation time.
• It should have singe IP in the in bound rules
• Edit the inbound rules and add another and choose the Private IP of the EC2 instance by navigating to the EC2 instance and copying the private IP.

The database will now accept connection requests only from the EC2 instance.

Installing Pre-requisite on EC2

During the planning phase software versions and packages are identified that need to be installed on EC2 instance.

I will connect to the EC2 and carry out the installations:

• Open terminal on local machine
• Navigate to the folder containing the private key which was downloaded while creating the EC2 instance
• Run the below command highlighted in example below

• Once connected to the EC2 instance install pre requisites by executing unix commands

Uploading Data Dump to Database

I am going to do this by remotely connecting to EC2 instance and then logging into the database instance with root user.

• Follow steps as mentioned before to connect to EC2 instance
• Once connected to EC2 login to MySQL using the command

mysql -h <hostname> -P 3306 -u admin -p 

• When prompted enter root password for admin
• Once connected lets create the data base and application user by running the following

CREATE DATABASE database_name;

CREATE USER user_name IDENTIFIED BY 'password';

GRANT ALL PREVILEGES ON database_name;

FLUSH PREVILEGES;

The database is now ready to be used by application

Starting the Application on EC2

• Again I remotely connect to EC2 instance using the private key and command from earlier.
• Upload the zipped application and the data base dump to the EC2 instance.
• The files can be uploaded to a S3 bucket and then downloaded to EC2 instance using AWS cli
• I unzipped the application folder and updated the configuration by inserting the database instance name.
• I logged back into the MySql instance using the previous commands

mysql -h <hostname> -P 3306 -u admin -p

• I run the below command to update the tables from dump

USE database_name;
SOURCE mysql_dump.sql;

• Exit the mysql client
• Start the application running the entry point.

Once that is done the application is running and is available to be accessed on the internet.

Home page

Existing articles as loaded into database.

Next steps in the migration are testing the workflows and cutting over, by closing down the on premise application and directing traffic to cloud application. Since this was just a simulation this concludes the excercise as I have demonstrated how an application is migrated from on premise to cloud infrastructure.

I experimented with creating KNN classifier and will contrast it with Neural Network using Tensorflow in my next article.

Problem Statement

Given data set of misspelt , jumbled or missing characters decipher the normalized name to curate the data quality.

Data Set

To simulate this exercise I chose retailer data set, by down loading a list of top 100 retailers in US. I created a bunch of methods to impute random characters and numbers. To keep it really vague I also randomized the number of imputations to a singe string, just to ensure the data does not get impossible to comprehend I limited number of imputation between 0 to 50% of length of String.

import string
import random 
import numpy as np 
import pandas as pd

# Using seen hash table to generate all unique values
# Main goal here is to ensure there is no cross category contamination
seen = {}

def gen_impurities(n,word):
    for i in range(n):
        index = random.randint(0,len(word)-1)

        if index%2 == 0:
            symbol = random.choice(string.punctuation)
            word = word[:index] + symbol + word[index:]

        if index%7 == 0:
            num = random.choice(string.digits)
            #print(f"Number = {num}")
            word = word[:index] + str(num) + word[index:]

        if index%3 == 0:
            word.replace(word[index],"")
    return word

def obfuscate(word):
    n = int(len(word)*0.5)
    iteration = random.randint(1,n)
    label = word
    for i in range(5):
        imputed_word = gen_impurities(iteration,label)
        if imputed_word not in seen:
            seen[imputed_word] = 1
            return imputed_word
        seen[imputed_word] = seen[imputed_word]+1
    return word

def gen_gibberish(min_l,max_l):
    l = random.randint(min_l,max_l)
    res = ''.join(random.choices(string.ascii_uppercase +
                                 string.punctuation +
                                 string.digits, k=l))
    while res not in seen:    
        seen[res] = 1
        return res
    seen[res] = seen[res]+1

def get_obfuscated_targets(targets,n):
    obfuscate_target = targets * n
    obfuscate_targets.sort()
    df_obfuscated = pd.DataFrame({"targets": obfuscate_targets})
    df_obfuscated['bad_names'] = df_obfuscated["targets"].apply(lambda x: obfuscate(x) )
    return df_obfuscated
    
def get_catch_gibberish(instance_count,min_l,max_l):
    others = ['Other'] * instance_count
    df_other = pd.DataFrame({'targets':others})
    df_other['bad_names'] = df_other['targets'].apply(lambda x : gen_gibberish(min_l,max_l)) 
    return df_other

def get_good_names(targets,n):
    good_captures = targets*  n
    df_good_captures = pd.DataFrame({'targets':good_captures})
    df_good_captures['bad_names'] = df_good_captures["targets"]
    return df_good_captures

def get_data(impute_n,instance,min_l,max_l): 
    n = impute_n
    instance_count = instance
    min_l = min_l
    max_l = max_l

    df_obfuscated = get_obfuscated_targets(targets,n)
    df_other = get_catch_gibberish(instance_count,min_l,max_l)
    df_good_captures = repeat_good_names(targets,n,instance_count)
    df = pd.concat([df_obfuscated,df_good_captures,df_other])
    return df

K-Nearest Neighbor

The K-Nearest Neighbors (KNN) algorithm is a popular and intuitive classification and regression algorithm in machine learning. It is a non-parametric algorithm that works based on the principle of similarity. The KNN algorithm classifies or predicts a new data point by comparing it to the labeled data points in the training dataset. The algorithm measures the distance between the new data point and its K nearest neighbors in the feature space. The K nearest neighbors are determined based on a distance metric, commonly the Euclidean distance. Once the neighbors are identified, the algorithm assigns the majority class label for classification or calculates the average value for regression. KNN is a simple yet powerful algorithm that can be used for various tasks, such as recommendation systems, image classification, and anomaly detection. However, it’s important to choose an appropriate value for K and consider the impact of feature scaling to achieve optimal results with the KNN algorithm.

Classes

In this particular use case there are 100 classes + 1 class of gibberish strings or garbage class to ensure some random string does not classified as a valid class. I followed the standard practice of splitting the data set in test and train using 80:20 split

def read_data():
    df = pd.read_csv("Targets.csv")
    del df['empty']
    df = df.set_index('no')
    df.head()
    return df


def get_train_test_data(split_ratio,impute_ratio,instance,min_l,max_l):
    test_size = (instance*split_ratio)
    train_size = (instance*(1-split_ratio))
    print(f"test_size = {test_size} & train_size = {train_size} & split = {split_ratio}")
    test_impute_n = int(test_size*impute_ratio)
    test_good_n = int(test_size - test_impute_n)
    print(f"test impute = {test_impute_n} & instances = {test_good_n}")
    df = read_data()
    targets = list(df["target"])
    # get imputed
    df_obfuscated = get_obfuscated_targets(targets,test_impute_n)
    # get good names
    df_good_names = get_good_names(targets, test_good_n)
    # get others 
    df_other = get_catch_gibberish(test_impute_n+test_good_n,min_l,max_l)
    df_test = pd.concat([df_obfuscated,df_good_names,df_other])
    
    del df_obfuscated,df_good_names,df_other
    train_impute_n = int(train_size*impute_ratio)
    train_good_n = int(train_size - train_impute_n)
    # get imputed
    df_obfuscated = get_obfuscated_targets(targets,train_impute_n)
    # get good names
    df_good_names = get_good_names(targets, train_good_n)
    # get others 
    df_other = get_catch_gibberish(train_impute_n+train_good_n,min_l,max_l)
    
    df_train = pd.concat([df_obfuscated,df_good_names,df_other])
    
    del df_obfuscated,df_good_names,df_other
    return df_train, df_test

def get_train_test_split(df_train,df_test,one_hot_encode_labels=False):
    X_train = df_train["bad_names"].values
    y_train = df_train["targets"].values
    X_test  = df_test["bad_names"].values
    y_test  = df_test["targets"].values
    
    if one_hot_encode_labels:
        df_labels = pd.concat([df_train['targets'],df_test['targets']])
        print(f"Labels = {df_labels.shape})")
        lables = pd.get_dummies(df_labels)
        lookup = list(lables.columns)
        print(len(lookup))
        del df_labels
        y_test_labels = pd.get_dummies(y_test)
        y_test_encoded = y_test_labels.astype('float32').values
        y_train_labels = pd.get_dummies(y_train)
        y_train_encoded = y_train_labels.astype('float32').values
        return X_train,y_train_encoded,X_test,y_test_encoded, lookup
    else:
        return X_train, y_train, X_test, y_test, None

split_ratio = 0.2
impute_ratio = 0.5
instances = 50
min_l = 5
max_l = 20

df_train, df_test = get_train_test_data(split_ratio,
                                        impute_ratio,
                                        instances,
                                        min_l,
                                        max_l)

X_train, y_train,X_test, y_test, lable_lookup = get_train_test_split(
                                                df_train,
                                                df_test,
                                                one_hot_encode_labels=False)

Text Vectorization:

Here I chose to use Tfidf vectorizer form scikit learn by assuming combination of two characters might gives a better relationship to predicting the targets. The TfidfVectorizer is a text feature extraction method that converts a collection of text documents into a numerical representation. TF-IDF stands for Term Frequency-Inverse Document Frequency, which is a commonly used statistical measure in information retrieval and text mining.

I fit the vectorizer to my corpus of bad names which generates n-grams for length 2 (2-grams). I then transformt each observation by applying the vectorized to get transformed array.

from sklearn.feature_extraction.text import TfidfVectorizer
Text = "".join(X_train)
vectorizer = TfidfVectorizer(analyzer='char',strip_accents ="ascii" , ngram_range=(2,2)).fit(Text)

X_train_vector = vectorizer.transform(X_train).toarray()
X_test_vector = vectorizer.transform(X_test).toarray()

K-NN Classifier

It’s important to note that the KNN algorithm does not involve model training as it is an instance-based learning algorithm.Below is the snippet used to create the classifier.

from sklearn.neighbors import KNeighborsClassifier
from scipy.spatial import distance      

classifier = KNeighborsClassifier(
                  n_neighbors=20,
                  n_jobs=10, 
                  metric = distance.correlation)

classifier.fit(X_train_vector, y_train)

Here’s a breakdown of how the classifier would work with the given parameters:

1. n_neighbors=20: This parameter determines the number of neighbors considered for classification. In this case, the classifier will consider the 20 nearest neighbors to the query point.
2. n_jobs=10: This parameter specifies the number of parallel jobs to run during the neighbor search. It allows for parallel processing to speed up the computation. In this case, the classifier will utilize 10 parallel jobs for the neighbor search.
3. metric=distance.correlation: This parameter defines the distance metric used to calculate the distance between data points. The distance.correlation metric represents the correlation distance, which measures the dissimilarity between two samples based on their correlation coefficient. It is a valid metric for KNN algorithms.

Given a new data point, the classifier would perform the following steps:

1. Calculate distances: The classifier computes the correlation distance between the new data point and all other data points in the training set.
2. Select neighbors: It selects the 20 nearest neighbors based on the calculated correlation distances.
3. Classify the data point: For classification, the majority class label among the selected neighbors is assigned as the predicted label for the new data point.

The classification decision is made based on the labeled data points in the training set. The specified parameters in the KNeighborsClassifier instance influence the number of neighbors considered, parallel processing, and the distance metric used.

Getting Predictions

As mentioned above there is no training involved and most of the heavy lifting is done on the prediction side. The classifier essentially needs all the training instances to make a decision

def my_classifier(w:list,vectorizer,classifier):
    w_vector = vectorizer.transform(w).toarray()
    pred = classifier.predict(w_vector)
    return pred

The data I used was of top retailers the predictor functions is able to predict as below:

test_stores = ["Targe1", 
                "Larget", 
                "!a7g3T",
                "'7argay'",
                "7- 11",
                "Ac3 Hw"]
my_classifier(test_stores
              ,vectorizer,classifier)

OP : array(['Target', 'Target', 'Other', 'Gap', '7-Eleven', 'Ace Hardware'],
      dtype=object)

As can be seen above the model does a good job of assigning most relevant store name.

Evaluating Model

from sklearn.metrics import classification_report
from sklearn.metrics import confusion_matrix
from sklearn.metrics import accuracy_score

conf_mat = pd.DataFrame({'test_names':X_test,'actual':y_test})
conf_mat["predicted"] = conf_mat["test_names"].apply(
                                          lambda x : my_classifier([x],
                                                              vectorizer,
                                                              classifier)[0])



result = accuracy_score(
                  conf_mat['actual'].values,
                  conf_mat['predicted'].values)
print("Accuracy:",result) 

OP: Accuracy: 0.9910891089108911

Learnings

1. KNN classifier will consume a lot of memory as size of training data grows.
2. The number of class and size of data will also affect the performance of the predictor.
3. Can be accurate with smaller training sets.
4. It needs trial and error with some informed guesstimates to select the correct distance function and number of neighbors.
5. It is best to optimize classifier performance with least number of classes and size of training data.
6. KNN Classifier does not need to one hot encode the labels and can predict the class labels.
7. Evaluation will take time proportional to number of records in test set
8. Can be performant to get single predictions.

Checkout the notebook on Github

https://github.com/mihirsaurkar/ML-apps/blob/c3b2e8a3391fcb11dee5143aab7887d5ad754f9a/Store_Classification/Store%20Classifier%20KNN.ipynb

Introduction

In this project based on a real-world scenario, I acted as Cloud Specialist with the mission to migrate users from existing on premise system to cloud infrastructure in an automated way and manage AWS IAM (Identity and Access Management) resources.

IAM (Identity and Access Management) is a crucial component of cloud security as it enables organizations to manage and secure access to their resources in a centralized manner. It allows administrators to create and manage policies and roles, granting or denying access to users and groups within an organization. IAM also provides audit logs and monitoring capabilities, allowing organizations to track and analyze user activity and identify potential security threats. Overall, IAM is a critical tool in maintaining a secure cloud environment and should be properly implemented and managed.

In this excercuse there were 100 users that needed to be migrated and have MFA (Multi-factor authentication) enabled on their accounts, as this is a security best practice.

To avoid repetitive and manual tasks in the AWS console, I needed to think about automating the processes.

Before we start adding the users we need to look at their roles and anticipate the permissions they might need to perform their duties without any hindrances on the cloud environment seamlessly. To determine the appropriate IAM policies and roles for your AWS resources, it is important to follow the principle of least privilege. This means that you should grant users and services only the minimum permissions required to perform their tasks. To achieve this, you can start by identifying the specific actions and resources that each user or service needs to access. Once you have identified these, you can create custom policies or use AWS managed policies to grant only the necessary permissions. Additionally, it is important to regularly review and update the policies and roles to ensure that they remain relevant and that any unnecessary access is removed. By following this approach, you can help to ensure the security of your AWS resources and minimize the risk of unauthorized access or data breaches.

In this assignment I simulated 5 roles:

• Cloud Administrator: This role is an administrator similar to system admin who would need almost unrestricted access to cloud resources.
• Linux Administrator: This role is Linux Administrator who can provision and manage virtual instances and manage their compute and memory capability moreover allow others to access them.
• Database Administrator (DBA): This is the traditional database administrator who would manage all the ins and outs of the cloud database instances.
• Network Administrator: This the network administrator responsible for managing VPC and setting access policies to it.
• Trainees: This is a special role assigned to anyone who needs access to view resources and services without worrying about modifying them

Implementation

I started by mapping the roles to a logical user groups, and creating them on AWS console using the IAM service.

I created user groups as follows:

• CloudAdministrator: This group has AWS managed policy attached to it which allows the user to have AWS administrator access to it.
• Linux Administrator: This group has the EC2 full access managed permission attached to it.
• Database Administrator (DBA): This group has the RDS full access managed permission attached to it.
• Network Administrator: This group has the VPC full access managed permission attached to it.
• Trainees: This group has the read only managed permission.

In addition to above role specific policies we also add the policies to force the user to change their password on first login and force the users to set up MFA using an authenticator app supported by AWS.

In this case I received a list of email ids along with the user roles. I performed a quick mapping of the provided roles to the user groups I previously created on AWS IAM console. I reduced the email ids to loose the domain part and created the IAM user names.

Once I had the prepped file containing user name, role and assigned group I was ready to create the users on AWS.

There are several ways to do this in this approach I am using a shell script with an utility called dos2unix to read the contents of our user list and iteratively run the AWS cli to perform the following actions:

• Create the IAM user
• Add properties to the created use namely reset password required, and a default password.
• Add the new created user to the appropriate user group

Note: Before we add users to a user group the group should be defined in IAM console.

To run the script I used the AWS cloud shell. Once the cloud shell start ups I uploaded my script along with the user list. Modified the permissions of the shell script to add execute permission and installed the dos2unix utility. Finally I executed the script providing the user list as input.

After a while the script was done executind and all the user are available on the IAM console. I can now email them the link to console and notify them of their default first time password.

Once they login they will be prompted to choose a strong password as defined in the password policy. Additionally since we added a custom MFA policy the users will have restricted access till they complete the MFA set up.

Alternative Execution

I could have authenticated my local console to aws and run the script locally by setting up the environment and downloading AWS cli. Using cloud shell freed up the overhead of local authentication and execution.