Intra-day Job orchestration & SQL: A full tutorial with example [ETL Scenario -2]

Опубликовано: 31 Октябрь 2024
на канале: ETL-SQL

392

In this video I have shared how you can easily change daily job into intra-day job. Queries used in the video:

/*
Problem Statement:
Change the frequency of ETL job from 5 am once a day to 5 am/ 1 pm /9 pm : 3 times a day
*/

DROP TABLE IF EXISTS oltp_sales;
CREATE TABLE oltp_sales (
sales_id INT PRIMARY KEY,
store_id INT,
product_name VARCHAR(50),
quantity_sold INT,
sales_amount DECIMAL(10, 2),
sales_time TIMESTAMP
);

-- Inserting sample data into the sales table
INSERT INTO oltp_sales VALUES
(1, 101, 'iPhone 12', 10, 10000.00, '2024-01-20 09:15:00'),
(2, 102, 'MacBook Air', 5, 5000.00, '2024-01-20 10:30:00'),
(3, 103, 'iPad Pro', 8, 8000.00, '2024-01-20 11:45:00'),
(4, 201, 'iWatch Series 6', 12, 6000.00, '2024-01-20 13:00:00'),
(5, 202, 'iPod Touch', 15, 1500.00, '2024-01-20 14:15:00'),
(6, 101, 'iPhone SE', 7, 7000.00, '2024-01-20 15:30:00'),
(7, 102, 'MacBook Pro', 3, 3000.00, '2024-01-20 16:45:00'),
(8, 103, 'iPad Mini', 6, 6000.00, '2024-01-20 18:00:00'),
(9, 201, 'AirPods Pro', 20, 2000.00, '2024-01-20 19:15:00'),
(10, 202, 'iMac', 4, 4000.00, '2024-01-20 20:30:00'),
(11, 101, 'iPhone 13', 9, 9000.00, '2024-01-21 09:15:00'),
(12, 102, 'Mac Mini', 2, 2000.00, '2024-01-21 10:30:00'),
(13, 103, 'iPad Air', 10, 10000.00, '2024-01-21 11:45:00'),
(14, 201, 'Apple Watch SE', 15, 1500.00, '2024-01-21 13:00:00'),
(15, 202, 'iPod Nano', 8, 800.00, '2024-01-21 14:15:00'),
(16, 101, 'iPhone XR', 5, 5000.00, '2024-01-21 15:30:00'),
(17, 102, 'Mac Pro', 1, 10000.00, '2024-01-21 16:45:00'),
(18, 103, 'iPad 9th Gen', 12, 12000.00, '2024-01-21 18:00:00'),
(19, 201, 'AirPods Max', 3, 3000.00, '2024-01-21 19:15:00'),
(20, 202, 'Mac Studio', 6, 6000.00, '2024-01-21 20:30:00'),
(21, 101, 'iPhone SE 2nd Gen', 8, 8000.00, '2024-01-22 09:15:00'),
(22, 102, 'MacBook 12-inch', 4, 4000.00, '2024-01-22 10:30:00'),
(23, 103, 'iPad 8th Gen', 15, 15000.00, '2024-01-22 11:45:00'),
(24, 201, 'Apple Watch Series 3', 7, 700.00, '2024-01-22 13:00:00'),
(25, 202, 'iPod Shuffle', 20, 200.00, '2024-01-22 14:15:00'),
(26, 101, 'iPhone 11', 10, 10000.00, '2024-01-22 15:30:00'),
(27, 102, 'MacBook Pro 16-inch', 5, 5000.00, '2024-01-22 16:45:00'),
(28, 103, 'iPad Pro 11-inch', 8, 8000.00, '2024-01-22 18:00:00'),
(29, 201, 'AirPods 3rd Gen', 12, 1200.00, '2024-01-22 19:15:00'),
(30, 202, 'iMac Pro', 2, 20000.00, '2024-01-22 20:30:00'),
(31, 101, 'iPhone XS', 5, 5000.00, '2024-01-23 09:15:00'),
(32, 102, 'MacBook Air', 3, 3000.00, '2024-01-23 10:30:00'),
(33, 103, 'iPad Pro', 10, 10000.00, '2024-01-23 11:45:00'),
(34, 201, 'iWatch Series 6', 8, 8000.00, '2024-01-23 13:00:00'),
(35, 202, 'iPod Touch', 15, 1500.00, '2024-01-23 14:15:00'),
(36, 101, 'iPhone SE', 6, 6000.00, '2024-01-23 15:30:00'),
(37, 102, 'MacBook Pro', 4, 4000.00, '2024-01-23 16:45:00'),
(38, 103, 'iPad Mini', 12, 12000.00, '2024-01-23 18:00:00'),
(39, 201, 'AirPods Pro', 20, 2000.00, '2024-01-23 19:15:00'),
(40, 202, 'iMac', 7, 7000.00, '2024-01-23 20:30:00'),
(41, 101, 'iPhone 13', 9, 9000.00, '2024-01-23 09:15:00'),
(42, 102, 'Mac Mini', 2, 2000.00, '2024-01-24 10:30:00'),
(43, 103, 'iPad Air', 10, 10000.00, '2024-01-24 11:45:00'),
(44, 201, 'Apple Watch SE', 15, 1500.00, '2024-01-24 13:00:00'),
(45, 202, 'iPod Nano', 8, 800.00, '2024-01-24 14:15:00'),
(46, 101, 'iPhone XR', 5, 5000.00, '2024-01-24 15:30:00'),
(47, 102, 'Mac Pro', 1, 10000.00, '2024-01-24 16:45:00'),
(48, 103, 'iPad 9th Gen', 12, 12000.00, '2024-01-24 18:00:00'),
(49, 201, 'AirPods Max', 3, 3000.00, '2024-01-24 19:15:00'),
(50, 202, 'Mac Studio', 6, 6000.00, '2024-01-24 20:30:00');

SELECT * FROM oltp_sales ;

DROP TABLE IF EXISTS stg_sales;
CREATE TABLE stg_sales (
sales_id INT PRIMARY KEY,
store_id INT,
product_name VARCHAR(50),
quantity_sold INT,
sales_amount DECIMAL(10, 2),
sales_time TIMESTAMP
);
--21st jan
INSERT INTO stg_sales
SELECT * FROM oltp_sales WHERE cast(sales_time AS date) = CAST('2024-01-21' AS date) - INTERVAL '1 day';

-- changes :
-- airflow / orchestration parameter will change from 2024-01-21 , 2024-01-21 05:00:00|2024-01-21 13:00:00| 2024-01-21 21:00:00
-- sql query
--current_date;
--SELECT * FROM oltp_sales WHERE cast(sales_time AS date) = current_date - INTERVAL '1 day';

SELECT * FROM oltp_sales WHERE sales_time BETWEEN CAST ('2024-01-21 13:00:00' AS timestamp) - INTERVAL '8 hours' AND CAST ('2024-01-21 13:00:00' AS timestamp) - INTERVAL '1 sec' ;