Media Content Copyright Monitoring and Royalty Settlement Data Warehouse Best Practices
Integrating a content asset library, licensing contract table, and multi-platform play logs into a multi-layer data warehouse enables automatic royalty attribution calculation, contract expiration risk tracking, and locking historical play snapshots at month-end to prevent data retroactivity disputes. This article uses a dataset of 15 content assets, 20 licensing contracts, and 60 play records to demonstrate an end-to-end ODS → DWD → DWS → ADS four-layer architecture, covering five core capabilities: OSS PIPE, CDC contract sync, Dynamic Table royalty attribution, Table Stream + MERGE INTO contract SCD, and Time Travel month-end snapshot locking.
Overview
The core challenge of a media copyright data warehouse is: play data comes from multiple platforms, revenue-sharing methods vary across contracts (three types: by play count, by revenue percentage, flat licensing fee), and retroactive modifications are not permitted after month-end settlement.
Singdata Lakehouse solves these problems with the following combination:
Problem
Solution
Multiple platforms push daily play CSV files
OSS PIPE auto-ingestion, triggered by file changes, no manual scheduling scripts needed
Contract management system status changes in real time
System automatically identifies upstream changes and incrementally refreshes
REFRESH DYNAMIC TABLE
Manually trigger a single refresh
Used during initial build or debugging
MERGE INTO
Merge Stream changes into contract history table
SCD scenario: new rows inserted, status changes updated
DESC HISTORY
View table historical version records
Confirm available versions before Time Travel
TIMESTAMP AS OF
Query data snapshot at a historical point in time
Month-end snapshot locking to prevent retroactive modifications
DATEDIFF
Calculate remaining contract days
ADS layer contract expiration alerts
Prerequisites
All examples in this article run under the best_practice_copyright_royalty schema.
CREATE SCHEMA IF NOT EXISTS best_practice_copyright_royalty;
ODS Layer: Raw Data Ingestion
Content Asset Table
CREATE TABLE IF NOT EXISTS best_practice_copyright_royalty.doc_content_assets (
content_id STRING,
title STRING,
content_type STRING, -- movie / tv_series / music / documentary / podcast / short_video
rights_holder STRING,
release_year INT,
region STRING -- CN / GLOBAL
);
Insert 15 content asset records:
Import from local CSV (recommended):
-- Step 1: Upload local CSV file to User Volume via SQL PUT
PUT '/path/to/doc_content_assets.csv' TO USER VOLUME FILE 'doc_content_assets.csv';
-- Step 2: COPY INTO table from User Volume
COPY INTO best_practice_copyright_royalty.doc_content_assets
FROM USER VOLUME
USING csv
OPTIONS('header'='true', 'sep'=',', 'nullValue'='')
FILES ('doc_content_assets.csv');
You can also insert a small batch of test data inline (no CSV file required):
rate_type supports three revenue-sharing methods: revenue_share (by revenue percentage), per_play (by play count), and flat_fee (fixed licensing fee).
CREATE TABLE IF NOT EXISTS best_practice_copyright_royalty.doc_license_contracts (
contract_id STRING,
content_id STRING,
platform_id STRING,
start_date DATE,
end_date DATE,
rate_type STRING, -- revenue_share / per_play / flat_fee
rate_value DOUBLE, -- percentage (revenue_share), unit price (per_play), annual fee (flat_fee)
min_guarantee DOUBLE, -- minimum guarantee (annual, in yuan)
status STRING -- active / expired / renewed
);
Insert 20 contracts covering all three revenue-sharing methods:
Import from local CSV (recommended):
-- Step 1: Upload local CSV file to User Volume via SQL PUT
PUT '/path/to/doc_license_contracts.csv' TO USER VOLUME FILE 'doc_license_contracts.csv';
-- Step 2: COPY INTO table from User Volume
COPY INTO best_practice_copyright_royalty.doc_license_contracts
FROM USER VOLUME
USING csv
OPTIONS('header'='true', 'sep'=',', 'nullValue'='')
FILES ('doc_license_contracts.csv');
You can also insert a small batch of test data inline (no CSV file required):
INSERT INTO best_practice_copyright_royalty.doc_license_contracts VALUES
('CTR001','C001','PLT_A',CAST('2025-01-01' AS DATE),CAST('2025-12-31' AS DATE),'revenue_share',0.30,50000,'active'),
('CTR002','C001','PLT_B',CAST('2025-03-01' AS DATE),CAST('2025-08-31' AS DATE),'revenue_share',0.25,30000,'active'),
('CTR003','C002','PLT_A',CAST('2025-01-01' AS DATE),CAST('2025-06-30' AS DATE),'per_play',0.005,10000,'expired'),
('CTR004','C002','PLT_C',CAST('2025-04-01' AS DATE),CAST('2025-12-31' AS DATE),'revenue_share',0.20,20000,'active'),
('CTR005','C003','PLT_A',CAST('2025-01-01' AS DATE),CAST('2025-12-31' AS DATE),'per_play',0.003,5000,'active'),
('CTR006','C003','PLT_B',CAST('2025-02-01' AS DATE),CAST('2026-01-31' AS DATE),'flat_fee',8000,0,'active'),
('CTR007','C004','PLT_C',CAST('2024-12-01' AS DATE),CAST('2025-11-30' AS DATE),'revenue_share',0.15,15000,'active'),
('CTR008','C005','PLT_A',CAST('2025-01-01' AS DATE),CAST('2025-12-31' AS DATE),'per_play',0.002,3000,'active'),
('CTR009','C006','PLT_B',CAST('2025-01-01' AS DATE),CAST('2025-06-30' AS DATE),'revenue_share',0.18,12000,'expired'),
('CTR010','C007','PLT_A',CAST('2025-03-01' AS DATE),CAST('2025-12-31' AS DATE),'per_play',0.004,6000,'active'),
('CTR011','C008','PLT_C',CAST('2025-01-01' AS DATE),CAST('2025-12-31' AS DATE),'revenue_share',0.22,25000,'active'),
('CTR012','C009','PLT_A',CAST('2025-05-01' AS DATE),CAST('2026-04-30' AS DATE),'revenue_share',0.28,40000,'active'),
('CTR013','C010','PLT_B',CAST('2025-01-01' AS DATE),CAST('2025-12-31' AS DATE),'per_play',0.001,2000,'active'),
('CTR014','C011','PLT_C',CAST('2025-02-01' AS DATE),CAST('2025-07-31' AS DATE),'flat_fee',5000,0,'expired'),
('CTR015','C012','PLT_A',CAST('2025-01-01' AS DATE),CAST('2025-12-31' AS DATE),'revenue_share',0.20,18000,'active'),
('CTR016','C013','PLT_B',CAST('2025-04-01' AS DATE),CAST('2026-03-31' AS DATE),'per_play',0.003,4000,'active'),
('CTR017','C014','PLT_A',CAST('2024-11-01' AS DATE),CAST('2025-10-31' AS DATE),'revenue_share',0.25,35000,'active'),
('CTR018','C014','PLT_C',CAST('2025-01-01' AS DATE),CAST('2025-12-31' AS DATE),'per_play',0.006,8000,'active'),
('CTR019','C015','PLT_B',CAST('2025-03-01' AS DATE),CAST('2025-08-31' AS DATE),'revenue_share',0.18,14000,'active'),
('CTR020','C015','PLT_C',CAST('2025-06-01' AS DATE),CAST('2026-05-31' AS DATE),'revenue_share',0.15,10000,'active');
Contract revenue-sharing method distribution:
SELECT rate_type, COUNT(*) AS cnt, ROUND(AVG(rate_value), 4) AS avg_rate
FROM best_practice_copyright_royalty.doc_license_contracts
GROUP BY rate_type;
In an OSS PIPE scenario, video platforms push daily CSV/Parquet play reports via FTP to an OSS Volume, and PIPE auto-ingests them into this table.
CREATE TABLE IF NOT EXISTS best_practice_copyright_royalty.doc_platform_plays (
play_id STRING,
content_id STRING,
platform_id STRING,
play_date DATE,
play_count BIGINT,
revenue DOUBLE
);
💡 Tip: In a production environment, doc_platform_plays is automatically written by OSS PIPE. Without an OSS environment, use the following methods to write data and verify the downstream Dynamic Table logic.
Import from local CSV (recommended):
-- Step 1: Upload local CSV file to User Volume via SQL PUT
PUT '/path/to/doc_platform_plays.csv' TO USER VOLUME FILE 'doc_platform_plays.csv';
-- Step 2: COPY INTO table from User Volume
COPY INTO best_practice_copyright_royalty.doc_platform_plays
FROM USER VOLUME
USING csv
OPTIONS('header'='true', 'sep'=',', 'nullValue'='')
FILES ('doc_platform_plays.csv');
You can also insert a small batch of test data inline (no CSV file required):
Insert 60 play records covering three platforms PLT_A / PLT_B / PLT_C for multiple months from January to July 2025:
INSERT INTO best_practice_copyright_royalty.doc_platform_plays VALUES
('P001','C001','PLT_A',CAST('2025-01-15' AS DATE),12500,18750.00),
('P002','C001','PLT_A',CAST('2025-02-15' AS DATE),14200,21300.00),
('P003','C001','PLT_A',CAST('2025-03-15' AS DATE),16800,25200.00),
('P004','C001','PLT_A',CAST('2025-04-15' AS DATE),15000,22500.00),
('P005','C009','PLT_A',CAST('2025-05-15' AS DATE),25000,37500.00),
('P006','C009','PLT_A',CAST('2025-06-15' AS DATE),28000,42000.00),
('P007','C009','PLT_A',CAST('2025-07-15' AS DATE),31000,46500.00),
('P008','C014','PLT_A',CAST('2025-02-15' AS DATE),21000,31500.00),
('P009','C014','PLT_A',CAST('2025-03-15' AS DATE),19500,29250.00),
('P010','C014','PLT_A',CAST('2025-05-15' AS DATE),20000,30000.00),
('P011','C003','PLT_A',CAST('2025-01-15' AS DATE),300000,900.00),
('P012','C003','PLT_A',CAST('2025-04-15' AS DATE),400000,1200.00),
('P013','C005','PLT_A',CAST('2025-06-15' AS DATE),350000,700.00),
('P014','C007','PLT_A',CAST('2025-03-15' AS DATE),250000,1000.00),
('P015','C012','PLT_A',CAST('2025-07-15' AS DATE),300000,6000.00),
('P016','C015','PLT_A',CAST('2025-06-15' AS DATE),100000,18000.00),
('P017','C005','PLT_A',CAST('2025-05-15' AS DATE),61000,24803.00),
('P018','C001','PLT_B',CAST('2025-03-15' AS DATE),10000,8000.00),
('P019','C001','PLT_B',CAST('2025-04-15' AS DATE),12000,9000.00),
('P020','C001','PLT_B',CAST('2025-05-15' AS DATE),15000,9500.00),
('P021','C003','PLT_B',CAST('2025-02-15' AS DATE),80000,6000.00),
('P022','C003','PLT_B',CAST('2025-05-15' AS DATE),100000,4000.00),
('P023','C006','PLT_B',CAST('2025-01-15' AS DATE),120000,7000.00),
('P024','C006','PLT_B',CAST('2025-06-15' AS DATE),160000,6500.00),
('P025','C010','PLT_B',CAST('2025-01-15' AS DATE),200000,2200.00),
('P026','C010','PLT_B',CAST('2025-05-15' AS DATE),120000,1200.00),
('P027','C013','PLT_B',CAST('2025-03-15' AS DATE),210000,2100.00),
('P028','C013','PLT_B',CAST('2025-04-15' AS DATE),190000,1900.00),
('P029','C001','PLT_B',CAST('2025-06-15' AS DATE),8000,6000.00),
('P030','C003','PLT_B',CAST('2025-07-15' AS DATE),50000,5000.00),
('P031','C006','PLT_B',CAST('2025-02-15' AS DATE),40000,4800.00),
('P032','C010','PLT_B',CAST('2025-07-15' AS DATE),30000,3000.00),
('P033','C013','PLT_B',CAST('2025-06-15' AS DATE),20000,2000.00),
('P034','C001','PLT_B',CAST('2025-07-15' AS DATE),7000,5000.00),
('P035','C003','PLT_B',CAST('2025-01-15' AS DATE),25000,1500.00),
('P036','C006','PLT_B',CAST('2025-03-15' AS DATE),15000,2000.00),
('P037','C010','PLT_B',CAST('2025-02-15' AS DATE),12000,1000.00),
('P038','C013','PLT_B',CAST('2025-07-15' AS DATE),9800,424.00),
('P039','C002','PLT_C',CAST('2025-04-15' AS DATE),30000,6000.00),
('P040','C002','PLT_C',CAST('2025-05-15' AS DATE),40000,7000.00),
('P041','C004','PLT_C',CAST('2025-01-15' AS DATE),45000,9000.00),
('P042','C004','PLT_C',CAST('2025-05-15' AS DATE),50000,8000.00),
('P043','C008','PLT_C',CAST('2025-02-15' AS DATE),60000,12000.00),
('P044','C008','PLT_C',CAST('2025-05-15' AS DATE),70000,3000.00),
('P045','C011','PLT_C',CAST('2025-07-15' AS DATE),80000,10000.00),
('P046','C014','PLT_C',CAST('2025-03-15' AS DATE),90000,15000.00),
('P047','C014','PLT_C',CAST('2025-05-15' AS DATE),100000,1000.00),
('P048','C015','PLT_C',CAST('2025-06-15' AS DATE),70000,9000.00),
('P049','C002','PLT_C',CAST('2025-06-15' AS DATE),5000,7000.00),
('P050','C004','PLT_C',CAST('2025-02-15' AS DATE),4000,6000.00),
('P051','C008','PLT_C',CAST('2025-03-15' AS DATE),3000,5000.00),
('P052','C011','PLT_C',CAST('2025-04-15' AS DATE),4000,5000.00),
('P053','C014','PLT_C',CAST('2025-06-15' AS DATE),3000,4000.00),
('P054','C015','PLT_C',CAST('2025-07-15' AS DATE),3000,3000.00),
('P055','C011','PLT_C',CAST('2025-05-15' AS DATE),46300,471.00),
('P056','C002','PLT_C',CAST('2025-07-15' AS DATE),1000,1000.00),
('P057','C004','PLT_C',CAST('2025-03-15' AS DATE),1000,1000.00),
('P058','C008','PLT_C',CAST('2025-04-15' AS DATE),1000,1000.00),
('P059','C011','PLT_C',CAST('2025-06-15' AS DATE),700,792.00),
('P060','C001','PLT_A',CAST('2025-05-15' AS DATE),17200,25800.00);
Verify overall data volume by platform:
SELECT
platform_id,
SUM(play_count) AS total_plays,
ROUND(SUM(revenue), 2) AS total_revenue,
COUNT(DISTINCT content_id) AS unique_contents
FROM best_practice_copyright_royalty.doc_platform_plays
GROUP BY platform_id
ORDER BY total_revenue DESC;
PLT_A has the highest revenue, mainly driven by high-unit-price revenue_share contracts for "The Lost City" (C009) and "Thriller Night" (C014).
Configure OSS PIPE (Production Environment)
In a production environment, first create an OSS Storage Connection and Volume, then create a PIPE to continuously scan the platform push directory:
-- Create storage connection (using Alibaba Cloud OSS as an example)
CREATE STORAGE CONNECTION IF NOT EXISTS oss_media_conn
TYPE = 'OSS'
ACCESS_ID = '<your-access-id>'
ACCESS_KEY = '<your-access-key>'
ENDPOINT = '<oss-cn-hangzhou.aliyuncs.com>';
-- Create Volume pointing to the platform's daily report directory
CREATE EXTERNAL VOLUME IF NOT EXISTS vol_platform_plays
TYPE = 'OSS'
BUCKET = '<your-bucket>'
PATH = '/media/platform-plays/'
CONNECTION = oss_media_conn;
-- Create PIPE to auto-ingest new Parquet files
CREATE PIPE IF NOT EXISTS best_practice_copyright_royalty.pipe_platform_plays
VIRTUAL_CLUSTER = 'DEFAULT'
AUTO_PURGE = FALSE
AS
COPY INTO best_practice_copyright_royalty.doc_platform_plays
FROM VOLUME vol_platform_plays
USING parquet;
⚠️ Note: PIPE only processes files added to the Volume after it was created. It does not process pre-existing historical files. Historical data must be manually backfilled using COPY INTO.
ODS Layer: Contract Change Table Stream
After the contract management system syncs MySQL data to doc_license_contracts via CDC, a Table Stream is created on this table to capture row-level changes and drive SCD processing for the contract history table.
CREATE TABLE STREAM IF NOT EXISTS stream_contract_changes
ON TABLE doc_license_contracts
WITH PROPERTIES ('TABLE_STREAM_MODE' = 'STANDARD');
⚠️ Note: The CREATE TABLE STREAM syntax requires omitting the schema prefix; execute within the schema context (via the -s best_practice_copyright_royalty parameter or by running USE SCHEMA first). TABLE_STREAM_MODE is a required parameter with value STANDARD (tracks all DML changes) or APPEND_ONLY (tracks INSERT only).
Create the contract history table to store before/after values for each status change:
Use MERGE INTO to consume the Stream and write contract changes to the history table:
MERGE INTO best_practice_copyright_royalty.doc_contract_history AS t
USING (
SELECT
CONCAT(contract_id, '_', CAST(CURRENT_TIMESTAMP() AS STRING)) AS history_id,
contract_id,
content_id,
platform_id,
CASE WHEN __change_type = 'UPDATE_AFTER' THEN NULL
WHEN __change_type = 'UPDATE_BEFORE' THEN status
WHEN __change_type = 'DELETE' THEN status
ELSE NULL END AS old_status,
CASE WHEN __change_type = 'UPDATE_AFTER' THEN status
WHEN __change_type = 'INSERT' THEN status
ELSE NULL END AS new_status,
__change_type AS change_reason
FROM best_practice_copyright_royalty.stream_contract_changes
WHERE __change_type IN ('UPDATE_AFTER', 'INSERT', 'DELETE')
) AS s
ON t.contract_id = s.contract_id AND t.new_status = s.new_status
WHEN NOT MATCHED THEN
INSERT (history_id, contract_id, content_id, platform_id, old_status, new_status, change_reason)
VALUES (s.history_id, s.contract_id, s.content_id, s.platform_id, s.old_status, s.new_status, s.change_reason);
💡 Tip: After consuming the Stream, the Stream's consumption offset advances automatically. The next query to stream_contract_changes will only return new changes that occurred after this consumption, without returning already-consumed data.
DWD Layer: Play Fact Wide Table
The DWD layer JOINs the play log with the content asset table to supplement content metadata, and adds a monthly partition marker to facilitate downstream DWS monthly aggregation.
CREATE DYNAMIC TABLE IF NOT EXISTS best_practice_copyright_royalty.dwd_play_facts
AS
SELECT
p.play_id,
p.content_id,
p.platform_id,
p.play_date,
p.play_count,
p.revenue,
c.title,
c.content_type,
c.rights_holder,
c.release_year,
c.region,
DATE_FORMAT(p.play_date, 'yyyy-MM') AS stat_month
FROM best_practice_copyright_royalty.doc_platform_plays p
LEFT JOIN best_practice_copyright_royalty.doc_content_assets c
ON p.content_id = c.content_id;
Manually trigger the first refresh:
REFRESH DYNAMIC TABLE best_practice_copyright_royalty.dwd_play_facts;
SELECT COUNT(*) AS row_count FROM best_practice_copyright_royalty.dwd_play_facts;
row_count
---------
60
DWS Layer: Royalty Attribution Calculation
The DWS layer is the core layer of the royalty data warehouse. It aggregates play data by the three dimensions of content_id + platform_id + stat_month, joins contract terms, applies different calculation formulas based on rate_type, and compares against the monthly amortized minimum guarantee.
MAX(current month play count × unit price, annual guarantee / 12)
flat_fee
annual license fee / 12 (fixed, does not vary with play count)
CREATE DYNAMIC TABLE IF NOT EXISTS best_practice_copyright_royalty.dws_royalty_calc
AS
SELECT
f.content_id,
f.platform_id,
f.stat_month,
f.title,
f.content_type,
f.rights_holder,
SUM(f.play_count) AS total_plays,
SUM(f.revenue) AS total_revenue,
c.contract_id,
c.rate_type,
c.rate_value,
c.min_guarantee,
c.end_date,
CASE
WHEN c.rate_type = 'revenue_share'
THEN GREATEST(SUM(f.revenue) * c.rate_value, c.min_guarantee / 12)
WHEN c.rate_type = 'per_play'
THEN GREATEST(SUM(f.play_count) * c.rate_value, c.min_guarantee / 12)
WHEN c.rate_type = 'flat_fee'
THEN c.rate_value / 12
ELSE 0
END AS estimated_royalty,
DATEDIFF(c.end_date, CURRENT_DATE()) AS days_to_expiry
FROM best_practice_copyright_royalty.dwd_play_facts f
LEFT JOIN best_practice_copyright_royalty.doc_license_contracts c
ON f.content_id = c.content_id AND f.platform_id = c.platform_id
GROUP BY
f.content_id, f.platform_id, f.stat_month, f.title, f.content_type, f.rights_holder,
c.contract_id, c.rate_type, c.rate_value, c.min_guarantee, c.end_date;
View the 8 royalty records with the highest revenue:
SELECT
content_id, platform_id, stat_month, title,
total_plays, ROUND(total_revenue, 2) AS total_revenue
FROM best_practice_copyright_royalty.dws_royalty_calc
ORDER BY total_revenue DESC
LIMIT 8;
content_id | platform_id | stat_month | title | total_plays | total_revenue
-----------+-------------+------------+--------------------+-------------+--------------
C009 | PLT_A | 2025-07 | The Lost City | 31000 | 46500
C009 | PLT_A | 2025-06 | The Lost City | 28000 | 42000
C009 | PLT_A | 2025-05 | The Lost City | 25000 | 37500
C014 | PLT_A | 2025-02 | Thriller Night | 21000 | 31500
C014 | PLT_A | 2025-05 | Thriller Night | 20000 | 30000
C014 | PLT_A | 2025-03 | Thriller Night | 19500 | 29250
C001 | PLT_A | 2025-05 | The Dragon Legacy | 17200 | 25800
C001 | PLT_A | 2025-03 | The Dragon Legacy | 16800 | 25200
"The Lost City" (C009) on PLT_A uses a 28% revenue_share split. In July, monthly revenue was 46,500 yuan, with royalties of 13,020 yuan — the highest single-month royalty record.
ADS Layer: Settlement Report and Contract Expiration Alerts
The ADS layer adds contract status tags on top of DWS, outputting settlement reports that can be used directly for reconciliation and expiration alerts.
CREATE DYNAMIC TABLE IF NOT EXISTS best_practice_copyright_royalty.ads_settlement_report
AS
SELECT
content_id,
platform_id,
title,
content_type,
rights_holder,
stat_month,
contract_id,
rate_type,
total_plays,
ROUND(total_revenue, 2) AS total_revenue,
ROUND(estimated_royalty, 2) AS estimated_royalty,
days_to_expiry,
CASE
WHEN days_to_expiry < 0 THEN 'expired'
WHEN days_to_expiry <= 30 THEN 'expiring_soon'
WHEN days_to_expiry <= 90 THEN 'expiring_in_quarter'
ELSE 'active'
END AS contract_status
FROM best_practice_copyright_royalty.dws_royalty_calc
WHERE estimated_royalty > 0;
SELECT
rights_holder,
stat_month,
ROUND(SUM(estimated_royalty), 2) AS total_royalty,
COUNT(DISTINCT content_id) AS content_cnt
FROM best_practice_copyright_royalty.ads_settlement_report
GROUP BY rights_holder, stat_month
ORDER BY total_royalty DESC, rights_holder, stat_month DESC
LIMIT 10;
Result interpretation: StarFilms Ltd has led in total royalties for consecutive months, mainly contributed by revenue_share contracts for multiple high-revenue films including "The Dragon Legacy," "The Lost City," and "Thriller Night." May 2025 is the annual peak (28,906.67 yuan), coinciding with multiple films reaching peak play counts in the same month.
View contract expiration alerts (contracts past expiration have a negative days_to_expiry):
SELECT
c.content_id, a.title, a.rights_holder,
c.contract_id, c.platform_id, c.end_date,
DATEDIFF(c.end_date, CURRENT_DATE()) AS days_remaining
FROM best_practice_copyright_royalty.doc_license_contracts c
LEFT JOIN best_practice_copyright_royalty.doc_content_assets a
ON c.content_id = a.content_id
ORDER BY days_remaining ASC
LIMIT 8;
💡 Tip: days_remaining is dynamically calculated based on CURRENT_DATE() at query execution time. The values above reflect the output as of June 9, 2026 when validated; subsequent executions will show different values as the date changes.
⚠️ Note: Contracts with contract_status = 'expired' may still have play records. Plays during a copyright gap period constitute an infringement risk. The ADS layer should separately count records where contract_status = 'expired' AND play_date > end_date, and promptly notify the copyright compliance team.
Dynamic Table Scheduling Configuration
The Dynamic Table DDL does not include REFRESH INTERVAL. Instead, scheduled refresh tasks are created in Studio, making it easy to attach data quality checks and alert rules to the same task.
Create three refresh tasks under the best_practices/copyright_royalty/ path in Studio:
Task Name
Target Dynamic Table
Recommended Schedule
Notes
refresh_dwd_play_facts
dwd_play_facts
Daily at 02:00
Refresh after platform T+1 push completes
refresh_dws_royalty_calc
dws_royalty_calc
Daily at 03:00
Calculate royalties downstream after DWD refresh
refresh_ads_settlement_report
ads_settlement_report
Daily at 04:00
Generate the latest daily settlement snapshot
💡 Tip: Attach data quality rules to Studio tasks (e.g., "total royalties this month must not be less than 50% of last month"). After the Dynamic Table refresh completes, the check is automatically triggered and an alert is sent on anomaly. This is easier to maintain operationally than writing REFRESH INTERVAL in DDL.
Time Travel: Month-End Snapshot Locking
After month-end settlement, platforms may retroactively report or correct historical play data, causing the confirmed settlement amount to change and triggering royalty disputes. Use Time Travel to freeze the data version at the time of settlement as a reconciliation voucher.
First, view the table's historical versions:
DESC HISTORY best_practice_copyright_royalty.doc_platform_plays;
Query the play snapshot using the timestamp at the time of month-end settlement (here using the May settlement cutoff as an example):
SELECT
SUM(play_count) AS total_plays,
ROUND(SUM(revenue), 2) AS total_revenue
FROM best_practice_copyright_royalty.doc_platform_plays
TIMESTAMP AS OF '2026-06-06 23:53:00'
WHERE play_date >= CAST('2025-05-01' AS DATE)
AND play_date <= CAST('2025-05-31' AS DATE);
⚠️ Note: TIMESTAMP AS OF only accepts literal timestamps and does not support dynamic expressions like CURRENT_TIMESTAMP() - INTERVAL. The month-end snapshot timestamp should be recorded in the settlement order table at the start of the settlement process; use the timestamp from the table when querying for reconciliation.
Export historical snapshot data to a settlement archive table (append-only, immutable):
For complex business rules like tiered rates and minimum guarantees with excess bonuses, use a ZettaPark Python Task:
from clickzetta_zettapark.session import Session
def calc_complex_royalty(session):
"""
Tiered rate example:
- Monthly play count < 100,000: 0.003 yuan/play
- Monthly play count 100,000–500,000: 0.003 + excess at 0.005 yuan/play
- Monthly play count > 500,000: 0.003 + excess at 0.008 yuan/play
Minimum monthly guarantee: annual contract guarantee / 12
"""
df = session.sql("""
SELECT content_id, platform_id, stat_month, total_plays, min_guarantee
FROM best_practice_copyright_royalty.dws_royalty_calc
WHERE rate_type = 'per_play'
""").to_pandas()
def tiered_rate(plays, min_guarantee_monthly):
if plays <= 100000:
royalty = plays * 0.003
elif plays <= 500000:
royalty = 100000 * 0.003 + (plays - 100000) * 0.005
else:
royalty = 100000 * 0.003 + 400000 * 0.005 + (plays - 500000) * 0.008
return max(royalty, min_guarantee_monthly)
df['tiered_royalty'] = df.apply(
lambda r: tiered_rate(r['total_plays'], r['min_guarantee'] / 12), axis=1
)
# Write back to settlement table
session.write_pandas(df[['content_id','platform_id','stat_month','tiered_royalty']],
'best_practice_copyright_royalty.doc_tiered_royalty_result',
overwrite=True)
return df.shape[0]
Create a Python Task calc_tiered_royalty under the best_practices/copyright_royalty/ path in Studio, mount the above script, and trigger execution after the refresh_ads_settlement_report task completes.
Contract table updates trigger Dynamic Table incremental refresh: dws_royalty_calc has doc_license_contracts as one of its upstream sources. When a contract rate or expiration date is modified, the affected contract-month combinations are recalculated on the next refresh. After month-end confirmation, it is recommended to export the current month's data to doc_settlement_archive to prevent subsequent contract changes from affecting already-settled amounts.
Table Stream consumption offset cannot be rolled back: Once MERGE INTO consumes the Stream, the consumption offset advances. Already-processed changes will not reappear. If MERGE INTO execution fails, use SHOW TABLE STREAMS to confirm the offset, then re-consume.
OSS PIPE only processes new files: After the PIPE is created, it only scans and ingests files newly added to the Volume. It does not process pre-existing files. Historical data (files already in the Volume before the PIPE was created) must be manually backfilled using COPY INTO.
Time Travel timestamps only accept literals: TIMESTAMP AS OF does not support dynamic expressions like CURRENT_TIMESTAMP() - INTERVAL. It is recommended to persist the settlement cutoff timestamp to the settlement order table, and read it from the table when querying for reconciliation.
days_to_expiry is based on query execution time: The ADS layer's days_to_expiry is calculated using DATEDIFF(end_date, CURRENT_DATE()), and results vary with each query. For a stable expiration alert report, the CURRENT_DATE() at refresh time should be fixed as a parameter field, or a snapshot should be generated at a fixed time in conjunction with the scheduling task.
