Snowflake developer on retainer: FACT_ORDERS MERGE composite key CUSTOMER_ID + ORDER_DATE — ERP ORDER_TIMESTAMP timezone Eastern→UTC shifts ORDER_DATE for 24% of orders — 847,293 duplicate rows accumulate in 60 days

Snowflake Enterprise Edition • AWS us-east-1 • Oracle E-Business Suite • SP_LOAD_FACT_ORDERS stored procedure • MERGE composite key • CONVERT_TIMEZONE • ORDER_DATE drift • duplicate row detection • retainer billing

Cascade Valley Distribution Corp’s October 2026 financial close process flagged a 27% discrepancy between the GMV figure in Snowflake and the GMV in Oracle E-Business Suite. The Snowflake analytics warehouse showed October 1–13 GMV of $48,347,293. Oracle EBS showed the same period at $38,021,847. When the Snowflake developer retainer was engaged, the investigation identified 847,293 duplicate rows in the FACT_ORDERS table that had accumulated silently over 60 days. The root cause was a timezone change in the Oracle EBS data export: the ERP team had changed ORDER_TIMESTAMP exports from Eastern time to UTC on August 15, 2026, as part of a global operations standardization. The nightly Snowflake load procedure derived ORDER_DATE directly from ORDER_TIMESTAMP::DATE without any timezone conversion. For orders placed after 8 PM Eastern (after midnight UTC), the ORDER_DATE in the new export was the following calendar day. The FACT_ORDERS MERGE statement used a composite natural key of CUSTOMER_ID + ORDER_DATE — and for every evening order, the new source date no longer matched the stored date from before the change. Snowflake’s MERGE inserted a new row instead of updating the existing one. No stored procedure error occurred. No Snowflake constraint fired. Every daily load had reported success.

Cascade Valley Distribution Corp ran Snowflake Enterprise Edition in AWS us-east-1. The analytics warehouse was loaded nightly by a Snowflake stored procedure, SP_LOAD_FACT_ORDERS, triggered by a Snowflake Task at 01:00 AM UTC. The procedure performed a MERGE from a Snowflake external stage (S3) where the Oracle EBS team deposited a daily CSV extract. The FACT_ORDERS table used a composite natural key for the MERGE match condition: CUSTOMER_ID (VARCHAR(20)) and ORDER_DATE (DATE). The ORDER_DATE was derived in the external stage query as ORDER_TIMESTAMP::DATE — casting the timestamp directly to a date with no timezone adjustment. Under the pre-August 15 export format, ORDER_TIMESTAMP values were in Eastern time (America/New_York), so ORDER_TIMESTAMP::DATE correctly produced the Eastern calendar date of the order. Under the post-August 15 UTC export format, ORDER_TIMESTAMP values were UTC timestamps, and ORDER_TIMESTAMP::DATE produced the UTC calendar date — which for orders placed after 8 PM Eastern (after midnight UTC) was one day ahead of the Eastern calendar date.

How MERGE composite key mismatch produces silent duplicate rows

A Snowflake MERGE statement’s WHEN NOT MATCHED THEN INSERT path fires whenever the source row’s match condition finds no corresponding row in the target. For FACT_ORDERS, the match condition was t.CUSTOMER_ID = s.CUSTOMER_ID AND t.ORDER_DATE = s.ORDER_DATE. An order placed at 9 PM Eastern on September 14, 2026 had been originally loaded on the September 14 run with ORDER_DATE = 2026-09-14 (the Eastern date). After the UTC export change, the September 15 run loaded the updated version of this order with ORDER_DATE = 2026-09-15 (the UTC date). The MERGE looked for a row with CUSTOMER_ID=C001 and ORDER_DATE=2026-09-15 and found none — so it inserted a new row. The original row with ORDER_DATE=2026-09-14 remained unchanged in FACT_ORDERS. Customer C001’s order now appeared twice: once with the Eastern date, once with the UTC date. Both rows had valid ORDER_AMOUNT values. Both contributed to any SUM(ORDER_AMOUNT) query.

-- SP_LOAD_FACT_ORDERS: MERGE statement (before fix)
-- Snowflake Enterprise, ANALYTICS_PROD.SALES.FACT_ORDERS

MERGE INTO ANALYTICS_PROD.SALES.FACT_ORDERS t
USING (
    SELECT
        ORDER_ID,
        CUSTOMER_ID,
        ORDER_TIMESTAMP::DATE   AS ORDER_DATE,  -- ← no timezone conversion
        ORDER_AMOUNT,
        ORDER_STATUS,
        PRODUCT_CATEGORY,
        WAREHOUSE_ID
    FROM @ANALYTICS_STAGE/orders/orders_{{ run_date }}.csv
    (FILE_FORMAT => 'CSV_ORDERS_FORMAT')
) s
ON  t.CUSTOMER_ID = s.CUSTOMER_ID
AND t.ORDER_DATE  = s.ORDER_DATE   -- ← composite natural key: both columns must match
WHEN MATCHED THEN UPDATE SET
    t.ORDER_AMOUNT   = s.ORDER_AMOUNT,
    t.ORDER_STATUS   = s.ORDER_STATUS,
    t.UPDATED_AT     = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN INSERT (
    ORDER_ID, CUSTOMER_ID, ORDER_DATE, ORDER_AMOUNT,
    ORDER_STATUS, PRODUCT_CATEGORY, WAREHOUSE_ID, CREATED_AT
) VALUES (
    s.ORDER_ID, s.CUSTOMER_ID, s.ORDER_DATE, s.ORDER_AMOUNT,
    s.ORDER_STATUS, s.PRODUCT_CATEGORY, s.WAREHOUSE_ID, CURRENT_TIMESTAMP()
);

-- Pre-August 15 2026: ORDER_TIMESTAMP in Eastern time
-- 9 PM Eastern Sep 14 = ORDER_TIMESTAMP '2026-09-14 21:00:00' (Eastern)
-- ORDER_TIMESTAMP::DATE = 2026-09-14  ← correct Eastern date

-- Post-August 15 2026: ORDER_TIMESTAMP in UTC
-- 9 PM Eastern Sep 14 = ORDER_TIMESTAMP '2026-09-15 01:00:00' (UTC)
-- ORDER_TIMESTAMP::DATE = 2026-09-15  ← UTC date (next day in Eastern)

-- MERGE behavior for the Sep 15 run after the UTC change:
-- Source row: CUSTOMER_ID='C001', ORDER_DATE='2026-09-15' (UTC), ORDER_AMOUNT=$147.50
-- Target lookup: WHERE CUSTOMER_ID='C001' AND ORDER_DATE='2026-09-15' → 0 rows found
-- Result: WHEN NOT MATCHED THEN INSERT → new row created
-- Original row (ORDER_DATE='2026-09-14', ORDER_AMOUNT=$147.50) remains unchanged
-- FACT_ORDERS now has 2 rows for C001's Sep 14 order: one with each ORDER_DATE

The affected orders were those placed after 8 PM Eastern (8 PM Eastern = midnight UTC; Eastern Daylight Time in summer is UTC-4, so 8 PM EDT = midnight UTC). Approximately 24% of daily orders were placed between 8 PM and 11:59 PM Eastern. Over the 60-day window from August 15 through October 14, approximately 3,530,388 total orders were processed. 24% of these — approximately 847,293 orders — had ORDER_DATE values that shifted by one day in the UTC export. Each of these orders generated a duplicate INSERT rather than an UPDATE, producing 847,293 extra rows in FACT_ORDERS. Each duplicate row carried the full ORDER_AMOUNT of the original order, meaning the total duplicated revenue was approximately $10,325,446. Together with the original rows (which remained unupdated), this inflated total GMV by approximately 27%.

Diagnosis: FACT_ORDERS duplicate row detection and ORDER_DATE distribution by hour-of-day

-- Diagnosis Step 1: Check for duplicate CUSTOMER_ID + ORDER_DATE pairs
-- Snowflake, ANALYTICS_PROD.SALES.FACT_ORDERS, executed October 14 2026

SELECT COUNT(*) AS total_rows FROM ANALYTICS_PROD.SALES.FACT_ORDERS;
-- 16,694,681 rows (expected ~15,847,388 based on Oracle EBS ORDER count)

SELECT CUSTOMER_ID, ORDER_DATE, COUNT(*) AS row_count
FROM ANALYTICS_PROD.SALES.FACT_ORDERS
GROUP BY CUSTOMER_ID, ORDER_DATE
HAVING COUNT(*) > 1
ORDER BY row_count DESC
LIMIT 5;
-- CUSTOMER_ID  ORDER_DATE   row_count
-- C84729       2026-09-15   3          (order placed Sep 14 at 9:47 PM Eastern)
-- C12847       2026-10-02   2
-- C29384       2026-08-16   2
-- ... (847,293 distinct CUSTOMER_ID+ORDER_DATE pairs with count > 1)

SELECT COUNT(*) AS duplicate_pairs
FROM (
    SELECT CUSTOMER_ID, ORDER_DATE
    FROM ANALYTICS_PROD.SALES.FACT_ORDERS
    GROUP BY CUSTOMER_ID, ORDER_DATE
    HAVING COUNT(*) > 1
);
-- 847,293 duplicate CUSTOMER_ID+ORDER_DATE pairs

-- Diagnosis Step 2: Identify start date and pattern of duplicates
SELECT MIN(ORDER_DATE) AS first_dup_date, MAX(ORDER_DATE) AS last_dup_date
FROM ANALYTICS_PROD.SALES.FACT_ORDERS
WHERE (CUSTOMER_ID, ORDER_DATE) IN (
    SELECT CUSTOMER_ID, ORDER_DATE FROM ANALYTICS_PROD.SALES.FACT_ORDERS
    GROUP BY CUSTOMER_ID, ORDER_DATE HAVING COUNT(*) > 1
);
-- first_dup_date: 2026-08-16  (day after ERP timezone change)
-- last_dup_date:  2026-10-13

-- Diagnosis Step 3: Check Oracle EBS source for ORDER_TIMESTAMP timezone
-- Query Oracle EBS export for a known duplicate order:
SELECT ORDER_ID, CUSTOMER_ID, ORDER_TIMESTAMP, ORDER_TIMESTAMP::DATE AS DERIVED_DATE
FROM @ANALYTICS_STAGE/orders/orders_2026-10-14.csv
(FILE_FORMAT => 'CSV_ORDERS_FORMAT')
WHERE CUSTOMER_ID = 'C84729'
LIMIT 1;
-- ORDER_TIMESTAMP: 2026-10-14 01:47:23  (UTC)
-- DERIVED_DATE:    2026-10-14
-- ORDER_TIMESTAMP in UTC: 01:47 UTC Oct 14 = 9:47 PM Eastern Oct 13

-- For this customer, ORDER_DATE in target = 2026-10-13 (from Eastern-era load)
-- ORDER_DATE in source = 2026-10-14 (from UTC-era load today)
-- MERGE finds no match → INSERT duplicate row

-- Diagnosis Step 4: Confirm ERP timezone change date
-- Oracle EBS export metadata (from ERP team): timezone change applied Aug 15 2026
-- Pre-Aug 15: ORDER_TIMESTAMP in Eastern time (America/New_York, EST/EDT)
-- Post-Aug 15: ORDER_TIMESTAMP in UTC
-- Orders placed 8 PM–11:59 PM Eastern → ORDER_DATE shifts to next calendar day

-- Hour-of-day distribution of duplicate CUSTOMER_ID+ORDER_DATE pairs:
SELECT
    HOUR(TO_TIMESTAMP(ORDER_TIMESTAMP)) AS hour_utc,
    COUNT(*) AS duplicate_count
FROM @ANALYTICS_STAGE/orders/orders_2026-10-14.csv (FILE_FORMAT => 'CSV_ORDERS_FORMAT')
WHERE (CUSTOMER_ID, ORDER_TIMESTAMP::DATE) IN (
    -- orders that will generate a duplicate INSERT today
    SELECT CUSTOMER_ID, ORDER_DATE FROM ANALYTICS_PROD.SALES.FACT_ORDERS
    WHERE ORDER_DATE = DATEADD('day', -1, '2026-10-14')
    -- target has yesterday's Eastern date; source will send today's UTC date
)
GROUP BY hour_utc ORDER BY hour_utc;
-- Hours 0-3 UTC (8 PM - 11 PM Eastern EDT): concentrated duplicate pattern  ✓
-- Confirms: duplicates are orders placed between 8 PM and midnight Eastern

Diagnosis was complete in 1.5 hours. The root cause was unambiguous: the ORDER_TIMESTAMP::DATE cast in the MERGE source query did not account for the August 15, 2026 change from Eastern to UTC in the Oracle EBS export. Orders placed after 8 PM Eastern produced a UTC date one day ahead of the Eastern date, causing the MERGE composite key to fail to match the existing target row and insert a duplicate.

The fix: CONVERT_TIMEZONE in ORDER_DATE derivation and DELETE + reload of 60-day window

Part 1: Update SP_LOAD_FACT_ORDERS to convert ORDER_TIMESTAMP to Eastern before deriving ORDER_DATE. The stored procedure’s MERGE source query was updated to use CONVERT_TIMEZONE('UTC', 'America/New_York', ORDER_TIMESTAMP)::DATE as the ORDER_DATE derivation, correctly producing the Eastern calendar date regardless of the source timestamp’s timezone. The procedure was tested against a sample of known duplicate cases before being deployed to production.

-- SP_LOAD_FACT_ORDERS: MERGE source query (AFTER fix)

MERGE INTO ANALYTICS_PROD.SALES.FACT_ORDERS t
USING (
    SELECT
        ORDER_ID,
        CUSTOMER_ID,
        -- CONVERT_TIMEZONE converts UTC timestamp to Eastern before ::DATE cast
        -- Handles both EST (UTC-5) and EDT (UTC-4) via America/New_York IANA zone
        CONVERT_TIMEZONE('UTC', 'America/New_York', ORDER_TIMESTAMP)::DATE AS ORDER_DATE,
        ORDER_AMOUNT,
        ORDER_STATUS,
        PRODUCT_CATEGORY,
        WAREHOUSE_ID
    FROM @ANALYTICS_STAGE/orders/orders_{{ run_date }}.csv
    (FILE_FORMAT => 'CSV_ORDERS_FORMAT')
) s
ON  t.CUSTOMER_ID = s.CUSTOMER_ID
AND t.ORDER_DATE  = s.ORDER_DATE
WHEN MATCHED THEN UPDATE SET
    t.ORDER_AMOUNT   = s.ORDER_AMOUNT,
    t.ORDER_STATUS   = s.ORDER_STATUS,
    t.UPDATED_AT     = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN INSERT (
    ORDER_ID, CUSTOMER_ID, ORDER_DATE, ORDER_AMOUNT,
    ORDER_STATUS, PRODUCT_CATEGORY, WAREHOUSE_ID, CREATED_AT
) VALUES (
    s.ORDER_ID, s.CUSTOMER_ID, s.ORDER_DATE, s.ORDER_AMOUNT,
    s.ORDER_STATUS, s.PRODUCT_CATEGORY, s.WAREHOUSE_ID, CURRENT_TIMESTAMP()
);

-- Verification: same order, post-fix
-- ORDER_TIMESTAMP '2026-10-14 01:47:23' UTC
-- CONVERT_TIMEZONE('UTC', 'America/New_York', '2026-10-14 01:47:23')
--   = '2026-10-13 21:47:23' (Eastern Daylight Time, UTC-4 in October)
-- ::DATE = 2026-10-13  ← correct Eastern date
-- MERGE: CUSTOMER_ID='C84729', ORDER_DATE='2026-10-13' → finds existing row → UPDATE ✓

Part 2: Delete the 847,293 duplicate rows and reload the 60-day window. The duplicate rows (those inserted after August 15 for orders placed after 8 PM Eastern) were identified and deleted. The deletion criteria: rows in FACT_ORDERS where the CUSTOMER_ID + ORDER_DATE pair appeared more than once, selecting the row with the UTC-derived ORDER_DATE (the incorrect, shifted date) for deletion while retaining the row with the Eastern-derived ORDER_DATE.

-- Step 1: Create a staging table of rows to delete
-- Duplicate rows: those with the UTC-derived ORDER_DATE (shifted forward 1 day)
-- For each duplicate pair: the LATER ORDER_DATE is the incorrect UTC-derived one
-- (Eastern date = original; UTC date = next day = incorrect duplicate)

CREATE TEMP TABLE dup_rows_to_delete AS
WITH ranked AS (
    SELECT
        CUSTOMER_ID,
        ORDER_DATE,
        ORDER_ID,
        CREATED_AT,
        ROW_NUMBER() OVER (
            PARTITION BY CUSTOMER_ID, ORDER_DATE
            ORDER BY CREATED_AT DESC  -- later CREATED_AT = UTC-era duplicate insert
        ) AS rn
    FROM ANALYTICS_PROD.SALES.FACT_ORDERS
    WHERE ORDER_DATE >= '2026-08-16'  -- day after ERP timezone change
    AND (CUSTOMER_ID, ORDER_DATE) IN (
        SELECT CUSTOMER_ID, ORDER_DATE FROM ANALYTICS_PROD.SALES.FACT_ORDERS
        GROUP BY CUSTOMER_ID, ORDER_DATE HAVING COUNT(*) > 1
    )
)
SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE
FROM ranked WHERE rn = 1;  -- later row = UTC-era duplicate

SELECT COUNT(*) FROM dup_rows_to_delete;
-- 847,293  ✓

-- Step 2: Delete the duplicate rows
DELETE FROM ANALYTICS_PROD.SALES.FACT_ORDERS
WHERE ORDER_ID IN (SELECT ORDER_ID FROM dup_rows_to_delete);
-- 847,293 rows deleted

-- Step 3: Reload the 60-day window to update the retained rows with correct data
-- Run SP_LOAD_FACT_ORDERS for each date Aug 15 through Oct 14 (61 runs)
-- The updated procedure uses CONVERT_TIMEZONE; MERGE now correctly UPDATE existing rows
-- (the WHEN MATCHED UPDATE path fires for all evening orders that were previously duplicated)

-- Step 4: Post-fix validation
SELECT COUNT(*) AS total_rows FROM ANALYTICS_PROD.SALES.FACT_ORDERS;
-- 15,847,388  ✓ (matches Oracle EBS ORDER count)

SELECT COUNT(*) AS duplicate_pairs
FROM (
    SELECT CUSTOMER_ID, ORDER_DATE FROM ANALYTICS_PROD.SALES.FACT_ORDERS
    GROUP BY CUSTOMER_ID, ORDER_DATE HAVING COUNT(*) > 1
);
-- 0  ✓ (no duplicates)

-- GMV validation for October 2026 (Oct 1-13):
SELECT SUM(ORDER_AMOUNT) AS oct_gmv
FROM ANALYTICS_PROD.SALES.FACT_ORDERS
WHERE ORDER_DATE BETWEEN '2026-10-01' AND '2026-10-13';
-- Before fix: $48,347,293
-- After fix:  $38,021,847  ✓ (matches Oracle EBS GMV for same period)

Total engagement time: 5.0 hours (1.5h diagnosis: FACT_ORDERS duplicate CUSTOMER_ID+ORDER_DATE pair count query, first and last duplicate date range confirmation, Oracle EBS export sample query to compare ORDER_TIMESTAMP timezone before and after August 15, hour-of-day distribution of duplicate orders confirming 8 PM–midnight Eastern pattern, ERP team confirmation of August 15 timezone change, estimated duplicate row count and revenue inflation calculation; 2.0h fix execution: SP_LOAD_FACT_ORDERS procedure update with CONVERT_TIMEZONE, CREATE TEMP TABLE of duplicate row IDs, DELETE 847,293 duplicate rows, 61-day reload of SP_LOAD_FACT_ORDERS for August 15–October 14, monitoring stored procedure execution and row count convergence; 0.5h post-fix validation: total row count vs. Oracle EBS, duplicate pair count = 0, GMV cross-check for October 1–13 vs Oracle EBS; 1.0h additional hardening and documentation: adding a daily Snowflake Task to run a duplicate-detection query and write results to a monitoring table, post-incident write-up, retainer SOP updated to add MERGE composite key audit requirement after any source system timezone or identifier convention change announcement).

Why MERGE composite key timezone drift is a recurring Snowflake retainer problem

The Cascade Valley Distribution incident illustrates the structural vulnerability of any Snowflake MERGE statement that uses a date derived from a timestamp as part of a composite natural key. A timestamp-to-date cast (::DATE) is a timezone-dependent operation: the same absolute moment in time produces different calendar dates depending on the timezone in which the cast is evaluated. Snowflake’s default session timezone is UTC, so ::DATE always returns the UTC date. For organizations that track business activity in Eastern, Pacific, or other local timezones, this means every ORDER_TIMESTAMP::DATE, TRANSACTION_TIMESTAMP::DATE, or EVENT_TIMESTAMP::DATE expression is correct only when the source timestamp is already in the expected timezone — or when an explicit CONVERT_TIMEZONE call aligns the timestamp to the business timezone before the date cast.

The failure is invisible in Snowflake’s monitoring surfaces because the MERGE executes correctly relative to what it was asked to do: find a matching row on the composite key and insert if not found. Snowflake does not enforce uniqueness on CUSTOMER_ID + ORDER_DATE because primary key and unique constraints in Snowflake are informational (not enforced). The MERGE’s WHEN NOT MATCHED INSERT is a valid, intended code path — it fires for genuinely new orders and, after the timezone change, for duplicated evening orders that appear to be new orders because their derived date has changed. The daily load stored procedure reports success with the expected row count (source rows = rows matched + rows inserted).

A Snowflake developer retainer who maintains a daily duplicate-detection monitoring query — running SELECT COUNT(*) FROM target GROUP BY composite_key HAVING COUNT(*) > 1 on all MERGE targets and alerting when the count exceeds zero — catches this class of issue within one daily load cycle rather than 60 days. The Cascade Valley incident would have been detected on August 16, the morning after the first UTC-timezone load, rather than at the October 14 financial close.

How HourTab helps Snowflake developer retainers

The Cascade Valley Distribution engagement totaled 5.0 hours: diagnosis (1.5h — 0.25h checking FACT_ORDERS total row count vs. Oracle EBS ORDER count confirming 847,293 excess rows; 0.25h running duplicate CUSTOMER_ID+ORDER_DATE pair detection query confirming 847,293 duplicate composite key pairs; 0.25h querying the first and last duplicate ORDER_DATE to confirm the August 16 start date, linking it to the August 15 ERP timezone change; 0.25h hour-of-day distribution analysis of duplicate rows confirming the 8 PM–midnight Eastern pattern, consistent with UTC date shift; 0.5h review of Oracle EBS export metadata with the ERP team confirming the August 15 2026 ORDER_TIMESTAMP timezone change from Eastern to UTC, and confirming no other schema or formatting changes were made to the export), fix (2.0h — 0.25h updating SP_LOAD_FACT_ORDERS ORDER_DATE derivation from ORDER_TIMESTAMP::DATE to CONVERT_TIMEZONE(’UTC’, ‘America/New_York’, ORDER_TIMESTAMP)::DATE; 0.25h testing the updated procedure against a sample of known duplicate orders, confirming WHEN MATCHED UPDATE fires for all eight-PM-and-later orders; 0.25h creating the dup_rows_to_delete temp table using ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID, ORDER_DATE ORDER BY CREATED_AT DESC) to identify the later-created duplicate rows; 0.25h executing DELETE 847,293 rows from FACT_ORDERS; 1.0h running SP_LOAD_FACT_ORDERS for each of the 61 days from August 15 to October 14 using a Snowflake scripting loop, monitoring row count convergence in FACT_ORDERS), validation (0.5h — 0.25h confirming total row count 15,847,388 matches Oracle EBS ORDER count; 0.25h confirming duplicate pair count = 0 and October 1–13 GMV $38,021,847 matches Oracle EBS), and hardening (1.0h — 0.5h creating a Snowflake Task scheduled daily at 03:00 UTC that runs the duplicate-detection query and writes results to ANALYTICS_PROD.MONITORING.MERGE_DUPLICATE_AUDIT, with a Snowflake alert configured to notify via email if any MERGE target table row count > 0; 0.5h post-incident write-up and retainer SOP update to add MERGE composite key audit as a required step whenever any upstream ERP, CRM, or billing system announces a timezone, locale, or identifier convention change).

In a HourTab retainer log, the incident session reads: “FACT_ORDERS duplicate rows: 847,293 duplicate CUSTOMER_ID+ORDER_DATE pairs; Oracle EBS ORDER_TIMESTAMP timezone change Eastern→UTC August 15 2026; SP_LOAD_FACT_ORDERS ORDER_DATE=ORDER_TIMESTAMP::DATE (no timezone conversion); 8 PM–midnight Eastern orders: UTC date = next calendar day (Eastern date + 1); MERGE composite key CUSTOMER_ID+ORDER_DATE: source has UTC date (+1), target has Eastern date; MERGE WHEN NOT MATCHED INSERT fires; original row unupdated; 24% of daily orders affected; 847,293 duplicates over 60 days; GMV $48.3M instead of actual $38.0M ($10.3M inflated); discovery October 14 financial close; fix: SP_LOAD_FACT_ORDERS ORDER_DATE = CONVERT_TIMEZONE(‘UTC’, ‘America/New_York’, ORDER_TIMESTAMP)::DATE; DELETE 847,293 duplicate rows; 61-day reload; FACT_ORDERS 15,847,388 rows, 0 duplicates; GMV $38.0M confirmed vs Oracle EBS; daily duplicate-detection monitoring task added; 5.0h.”

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: Snowflake developer retainers

See also: Azure Data Factory developer retainer · dbt developer retainer · Power BI developer retainer