Skip to main content

Scenario 3: Incremental Transactions

Business Context: Your Indian payment gateway processes millions of UPI transactions a day. The raw data lands in Snowflake every hour. Building the fct_upi_transactions table from scratch takes 45 minutes and costs hundreds of dollars in compute credits. Furthermore, due to network issues in rural areas, some transactions arrive several hours late.

Your Objective: You must convert the fct_upi_transactions model into an Incremental Model. You must also implement a 24-hour lookback window to handle late-arriving data.


1. Setup the Database Environment​

(Execute this in your Snowflake worksheet to prepare the environment. Find your username by running SELECT CURRENT_USER();)

-- 1. Create a dedicated database for this scenario
CREATE DATABASE IF NOT EXISTS BEEMS_SCENARIO_3_INCREMENTAL;
USE DATABASE BEEMS_SCENARIO_3_INCREMENTAL;
CREATE SCHEMA IF NOT EXISTS RAW;
CREATE SCHEMA IF NOT EXISTS MARTS;

-- 2. Create the raw transaction table (Source)
CREATE OR REPLACE TABLE RAW.upi_transactions (
txn_id VARCHAR,
payer_vpa VARCHAR,
payee_vpa VARCHAR,
amount NUMBER(10,2),
status VARCHAR,
created_at TIMESTAMP_NTZ
);

-- 3. Insert Day 1 Data (Millions of rows... but we'll pretend it's 2)
INSERT INTO RAW.upi_transactions VALUES
('TXN1001', 'arjun@okicici', 'merchant@sbi', 500.00, 'SUCCESS', '2023-10-01 10:00:00'),
('TXN1002', 'priya@okhdfc', 'zomato@paytm', 850.00, 'SUCCESS', '2023-10-01 11:30:00');

2. Building the Incremental Model​

Create a new file in your dbt project: models/marts/fct_upi_transactions.sql

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

WITH raw_txns AS (
-- Let's pretend we have a source defined for this
SELECT
txn_id,
payer_vpa,
payee_vpa,
amount,
status,
created_at
FROM BEEMS_SCENARIO_3_INCREMENTAL.RAW.upi_transactions
)

SELECT * FROM raw_txns

{% if is_incremental() %}

-- The Lookback Window (Handle late-arriving data up to 24 hours old)
WHERE created_at >= (
SELECT DATEADD('hour', -24, MAX(created_at))
FROM {{ this }}
)

{% endif %}

3. Testing the Incremental Run​

Step 1: The Initial Full Refresh​

Run the model for the first time. Because the target table doesn't exist yet, dbt ignores the is_incremental() block and builds it from scratch.

dbt run --select fct_upi_transactions

Verify in Snowflake:

SELECT * FROM BEEMS_SCENARIO_3_INCREMENTAL.MARTS.fct_upi_transactions;
-- You should see the 2 rows from Day 1.

Step 2: Simulating New & Late Data​

Let's add some data to our raw table. Notice that TXN1004 has a timestamp from the previous day (late-arriving data).

(Run this in Snowflake)

INSERT INTO RAW.upi_transactions VALUES
('TXN1003', 'kavya@okaxis', 'swiggy@icici', 300.00, 'SUCCESS', '2023-10-02 09:00:00'), -- New data
('TXN1004', 'rohan@okicici', 'shop@sbi', 150.00, 'SUCCESS', '2023-10-01 15:00:00'); -- Late arriving!

Step 3: The Incremental Run​

Run the dbt command exactly as before.

dbt run --select fct_upi_transactions

What happens?

  1. dbt looks at the target table. MAX(created_at) is 2023-10-01 11:30:00.
  2. It subtracts 24 hours. The filter threshold becomes 2023-09-30 11:30:00.
  3. It fetches all raw data greater than that threshold (which catches the late-arriving TXN1004).
  4. It performs a MERGE. Existing rows are updated (or ignored if unchanged), and new rows are inserted.

Verify in Snowflake:

SELECT * FROM BEEMS_SCENARIO_3_INCREMENTAL.MARTS.fct_upi_transactions ORDER BY created_at;
-- You should now see all 4 rows, including the late-arriving transaction!

Summary​

You have successfully implemented an Incremental Model with a Lookback Window. This saves massive compute costs by only processing recent data, while safely ensuring that late-arriving transactions from poor network zones are never lost.