Ketan Keshri - The Data Engineer

 In SQL, the order  you write the clauses  in a query is  not  the same as the order in which the  database engine executes  them. Here’s the  logical order of execution  of SQL clauses: Step Clause Description 1️⃣ FROM Identifies source tables and joins data. 2️⃣ WHERE Filters rows before grouping. 3️⃣ GROUP BY Groups rows based on specified columns. 4️⃣ HAVING Filters groups after aggregation. 5️⃣ SELECT Selects columns or expressions to return. 6️⃣ DISTINCT Removes duplicate rows from the result set. 7️⃣ ORDER BY Sorts the result set. 8️⃣ LIMIT / OFFSET Returns a subset of the result (pagination). EXAMPLE: SELECT department, COUNT(*) AS employee_count FROM employees WHERE status = 'active' GROUP BY department HAVING COUNT(*) > 10 ORDER BY employee_count DESC LIMIT 5; Execution order: FROM employees WHERE status = 'active' GROUP BY department HAVING COUNT(*) > 10 SELECT department, COUNT(*) AS employee_count ORDER BY employee_count DE...

  🧭  Overview: What is AWS Lake Formation? AWS Lake Formation is a service that simplifies building a secure data lake by: Ingesting data from various sources Organizing it in Amazon S3 Setting up data catalogs (via AWS Glue) Defining security and access policies Querying data with services like Athena, Redshift, and EMR 🛠️  Prerequisites Before starting, ensure you have: An  AWS account IAM permissions for Lake Formation, Glue, S3, and IAM An existing  S3 bucket  (or create a new one) 🧱  Step 1: Set Up a Data Lake Location Go to the  Lake Formation Console . In the left pane, choose  "Data lake locations" . Click  "Register location" . Choose your  S3 bucket  or a folder (e.g.,  s3://your-bucket/data/ ). Choose an  IAM role  that has permission to access this location. 📋  Step 2: Add a Data Catalog Table From the Lake Formation Console, go to  "Databases" . Click  "Create database"  (this...

USE AWS SSO login to access Athena using DBT outputs : dev : type : athena s3_staging_dir : s3://test-bucket/athena/ region_name : ap-south-1 database : awsdatacatalog schema : <db_name>      work_group : < Athena-workgroup > aws_profile_name : <profile-name-on-local-machine>

Name of all the tables in a DB: $ aws glue get-tables --database-name <your-db-name> --output json --query 'TableList[*].Name' --profile <aws-profile-name> Count of tables in the DB $ aws glue get-tables --database-name <your-db-name> --output json --query 'TableList[*].Name' --profile <aws-profile-name>  | jq length

How to connect MySQL through Jump servers: When you don't have direct access to mysql-server, you use jump-server. From your machine, you connect(ssh) to jump-server and from there you connect to your mysql-server. This can be avoided by using ssh- tunneling. Suppose your        jump server is `jump-ip`        mysql server is `mysql-ip`        your machine is `machine-ip` Just open ssh client(Putty in windows or terminal in linux/ios). Type:     ssh -L [local-port]:[mysql-ip]:[mysql-port] [jump-server-user]@[jump-ip] After this, you can use your localhost and local-port to access mysql-server on the remote machine directly. Eg. Your Jdbc url to access mysql database, in that case, will be jdbc:mysql://localhost:[local-port]/[database-name]

Benefits of Parquet Format Columnar Storage Efficient for analytics and read-heavy workloads . Only required columns are read into memory. Highly Compressed Supports efficient compression algorithms (Snappy, GZIP, Brotli). Smaller file size compared to row-based formats like CSV/JSON. Splittable & Scalable Files can be split and read in parallel , improving speed in distributed systems like Hadoop/Spark. Schema Evolution Supports adding new columns without breaking existing data pipelines. Efficient for Queries Works well with SQL engines like Hive, Presto, Trino, Athena, BigQuery. Better IO Performance Reduces disk and network IO by avoiding unnecessary data reads. Interoperable Supported across multiple languages and platforms (Python, Java, Spark, Hive, AWS, GCP, etc.). Self-describing Format Stores schema as metadata within the file itself — no need for external schema definitions. Great with Partitioning When used wi...

unzip a zip file stored on S3 without downloading it   AWS S3 is   an object storage , not a file system. Unzipping is not supported directly. You need some computation for unzipping, which can be achieved by copying that file to some  EC2 machine  or use some  Lambda . Let’s connect.. Github: https://github.com/ketankkeshri LinkedIn: https://in.linkedin.com/in/ketankeshri YouTube: https://www.youtube.com/@KetanKKeshri Instagram: https://www.instagram.com/ketankkeshri/ medium: https://medium.com/@ketankkeshri