-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy path02_shared_library.sql
More file actions
82 lines (77 loc) · 3.24 KB
/
02_shared_library.sql
File metadata and controls
82 lines (77 loc) · 3.24 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
-- =============================================================================
-- FILE: 02_shared_library.sql
-- PURPOSE: Centralized exception definitions with AI Resolution Hints.
-- =============================================================================
CREATE OR REPLACE PROCEDURE C360.SP_GET_EXCEPTION_DEFINITIONS()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
RETURN OBJECT_CONSTRUCT(
-- Functional Exceptions
'invalid_parameter', OBJECT_CONSTRUCT(
'code', -20003,
'message', 'Invalid parameter value provided',
'resolution_hint', 'Check the procedure call signature. Ensure inputs match expected data types and allowed ranges.',
'ai_tag', 'INPUT_VALIDATION'
),
'source_data_issue', OBJECT_CONSTRUCT(
'code', -20011,
'message', 'Source data quality issue detected',
'resolution_hint', 'Investigate source table for NULLs, truncated columns, or schema drift (new/missing columns).',
'ai_tag', 'DATA_QUALITY'
),
'no_data_found', OBJECT_CONSTRUCT(
'code', -20014,
'message', 'No data found for the specified criteria',
'resolution_hint', 'Verify WHERE clauses in extraction logic. Ensure upstream dependencies have completed successfully.',
'ai_tag', 'DATA_AVAILABILITY'
),
-- Technical Exceptions
'timeout', OBJECT_CONSTRUCT(
'code', -20018,
'message', 'Operation timeout exceeded',
'resolution_hint', 'Check Warehouse sizing or query clustering. Consider breaking operation into smaller batches using LIMIT/OFFSET.',
'ai_tag', 'PERFORMANCE'
),
'rbac_permission', OBJECT_CONSTRUCT(
'code', -20014,
'message', 'Insufficient RBAC permissions',
'resolution_hint', 'Verify the executing role has USAGE on Schema and SELECT/INSERT/UPDATE on target tables.',
'ai_tag', 'SECURITY'
),
-- Data Integrity
'duplicate_data', OBJECT_CONSTRUCT(
'code', -20001,
'message', 'Duplicate data found - violates unique constraint',
'resolution_hint', 'Check join logic for cartesian products or verify source data uniqueness keys against target constraints.',
'ai_tag', 'LOGIC_ERROR'
)
);
END;
$$;
-- Helper to declare exceptions in procedures
CREATE OR REPLACE PROCEDURE C360.SP_DECLARE_EXCEPTIONS()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS '
DECLARE
v_exception_defs OBJECT;
v_declaration_sql STRING DEFAULT '''';
v_exception_name STRING;
v_exception_info OBJECT;
BEGIN
CALL C360.SP_GET_EXCEPTION_DEFINITIONS() INTO :v_exception_defs;
v_declaration_sql := ''-- ===== SHARED EXCEPTION DECLARATIONS ====='' || ''\n'';
FOR v_exception_name IN (SELECT key FROM TABLE(FLATTEN(INPUT => v_exception_defs))) DO
v_exception_info := v_exception_defs[v_exception_name];
v_declaration_sql := v_declaration_sql ||
''v_exception_'' || v_exception_name || '' EXCEPTION ('' ||
v_exception_info[''code''] || '', '''''' || v_exception_info[''message''] || '''''');'' || ''\n'';
END FOR;
RETURN v_declaration_sql;
END;
';