Singdata Lakehouse Table Design Best Practices Guide

Content Overview

Document Introduction

This guide is a comprehensive reference manual for table design on the Singdata Lakehouse platform, covering everything from basic data type selection to complex enterprise-level architecture patterns.

How to Use This Guide

Depending on your role and needs, we recommend the following reading paths:

Data Architects: Focus on Design Philosophy (Chapter 1), Partition Architecture (Chapter 5), and Enterprise Design Patterns (Chapter 11)
Data Engineers: Dive into Data Type Design (Chapter 3), Index Architecture (Chapter 6), and Performance Optimization (Chapter 9)
Backend Developers: Concentrate on Table Structure Design (Chapter 4), Complex Data Types (Section 3.3), and Troubleshooting (Chapter 10)
Quick Start: Refer directly to the Design Review Checklist (Chapter 9) as a project guidance framework

Core Chapter Overview

Design Philosophy and Principles - Foundational design philosophy and decision framework
Data Type Design Strategy - Detailed type selection guide and use cases
Table Structure Design Patterns - Effective use of constraints, defaults, and generated columns
Partition Architecture Design - Partition type selection and optimization strategies
Bucketing and Sorting Optimization - Best practices for physical data organization
Index Architecture Design - Vector, inverted, and bloom filter indexes in detail
Performance Optimization Strategies - Query performance and storage cost optimization techniques
Common Design Pitfalls and Solutions - Avoiding common mistakes and optimization recommendations
Design Review Checklist - Comprehensive design validation process
Enterprise Design Patterns in Practice - Four advanced application architectures in detail
Lab Environment Cleanup Guide - Resource management best practices
Summary - Content summary

On first reading, we recommend going through the design philosophy section to understand core principles, then diving into relevant chapters based on your specific needs. Every code example can be copied and used directly to help you quickly apply them in practice.

Design Philosophy and Principles

Core Design Thinking

On the Singdata Lakehouse, excellent table design should balance performance, maintainability, and business requirements. This guide follows these validated core principles:

Business-Driven Design - Table structure should reflect business models and query patterns
Performance-First Consideration - Proper partitioning, bucketing, and indexing strategies are critical
Future-Oriented Scalability - Design with data growth and business evolution in mind
Operations-Friendly - Simplify daily maintenance and troubleshooting complexity

Design Decision Framework

Each design decision should consider the following dimensions:

Query Patterns: Primary data access methods and frequency
Data Characteristics: Data volume, growth rate, distribution characteristics
Business Requirements: Real-time requirements, consistency needs, scalability demands
Resource Constraints: Storage cost, compute resources, operational complexity

Data Type Design Strategy

Numeric Type Selection Guide

Auto-Increment Primary Key Design

Key Limitation: IDENTITY columns only support the BIGINT type

-- Correct IDENTITY usage (only supported syntax) CREATE TABLE business_events ( event_id BIGINT IDENTITY, -- Only BIGINT type is supported event_data JSON, created_at TIMESTAMP DEFAULT current_timestamp() ); -- IDENTITY with seed value CREATE TABLE user_accounts ( user_id BIGINT IDENTITY(1000), -- Auto-increment starting from 1000 username VARCHAR(50) NOT NULL );

Unsupported IDENTITY Syntax (confirmed to fail in testing):

-- These will all result in error: invalid identity column type int, currently only BIGINT is supported CREATE TABLE wrong_examples ( id INT IDENTITY, -- Fails small_id SMALLINT IDENTITY, -- Fails str_id VARCHAR(50) IDENTITY -- Fails );

Business Numeric Field Selection

Data Type	Storage	Value Range	Recommended Scenario	Practical Example
`TINYINT`	1 byte	-128 to 127	Status codes, levels	`status TINYINT DEFAULT 1`
`SMALLINT`	2 bytes	-32,768 to 32,767	Years, counters	`birth_year SMALLINT`
`INT`	4 bytes	+/-2.1 billion	Business IDs, large counts	`user_id INT NOT NULL`
`BIGINT`	8 bytes	+/-9.22 quintillion	Auto-increment PK, large values	`id BIGINT IDENTITY`
`DECIMAL(p,s)`	Variable	Up to 38-digit precision	Financial calculations	`amount DECIMAL(15,2)`
`FLOAT`	4 bytes	Single-precision float	Scientific computing, coordinates	`temperature FLOAT`
`DOUBLE`	8 bytes	Double-precision float	High-precision calculations	`coordinate DOUBLE`

String Type Strategy

Length Planning Principles (Based on Actual Business Requirements)

Business Scenario	Recommended Type	Length Setting	Coverage Rate	Design Considerations
Email address	`VARCHAR(320)`	RFC5321 standard	99.9%	International standard length
Username	`VARCHAR(50)`	Research-based	99.5%	Balance storage and usability
Phone number	`VARCHAR(20)`	International format	100%	Supports +86-138****
URL address	`VARCHAR(2048)`	Measured	98%	Includes complex query params
Article title	`VARCHAR(200)`	SEO optimized	95%	Search engine friendly
Product description	`VARCHAR(2000)`	E-commerce needs	90%	Detail page display
Long-form text	`STRING`	Unlimited length	100%	Blog posts, comments, etc.

-- String type best practices CREATE TABLE user_profiles ( user_id BIGINT IDENTITY, -- Fixed format uses CHAR country_code CHAR(2), -- CN, US, JP currency_code CHAR(3), -- USD, CNY, EUR -- Business fields use reasonable VARCHAR lengths username VARCHAR(50) NOT NULL, email VARCHAR(320) NOT NULL, mobile_phone VARCHAR(20), -- Descriptive content nickname VARCHAR(100), bio VARCHAR(500), -- Personal bio full_description STRING, -- Detailed description, variable length -- Structured data preferences JSON DEFAULT '{}' );

Vector Type Use Cases

Vector Type Syntax and Applications

Standard Syntax: VECTOR(scalar_type, dimension) or VECTOR(dimension)

Scalar Type	Storage Overhead	Use Case	Recommended Dimensions	Application Example
`FLOAT`	4 bytes/dim	Semantic vectors, general AI	128-2048	`VECTOR(FLOAT, 768)`
`INT`	4 bytes/dim	Discrete features, count vectors	64-1024	`VECTOR(INT, 256)`
`TINYINT`	1 byte/dim	Compressed vectors, mobile	64-512	`VECTOR(TINYINT, 128)`

Practical Application Examples:

CREATE TABLE ai_content_vectors ( content_id BIGINT IDENTITY, content_type VARCHAR(50), -- Vector configurations for different business scenarios text_embedding VECTOR(FLOAT, 768), -- BERT/RoBERTa output image_features VECTOR(FLOAT, 512), -- ResNet/CNN features user_preference VECTOR(INT, 256), -- Recommendation system user profile mobile_compact VECTOR(TINYINT, 128), -- Mobile lightweight general_vector VECTOR(512) -- Default FLOAT type ); -- Vector data insert syntax (note: dimensions must match strictly) INSERT INTO ai_content_vectors (content_type, text_embedding) VALUES ( 'document', cast(concat('[', repeat('0.1,', 767), '0.1]') as VECTOR(FLOAT, 768)) );

Complex Data Type Usage Guide

Proper STRUCT Type Usage

Correct STRUCT Data Insert Syntax:

CREATE TABLE user_complex_data ( user_id BIGINT IDENTITY, -- Simple struct basic_info STRUCT<id:INT, name:STRING, age:INT>, -- Complex nested struct detailed_profile STRUCT< personal:STRUCT<name:STRING, email:STRING>, address:STRUCT<city:STRING, country:STRING>, preferences:MAP<STRING, STRING> > ); -- Method 1: Using struct function (positional arguments) INSERT INTO user_complex_data (basic_info) VALUES ( struct(123, 'Alice', 25) ); -- Method 2: Using named_struct function (recommended, explicit field names) INSERT INTO user_complex_data (basic_info) VALUES ( named_struct('id', 123, 'name', 'Alice', 'age', 25) ); -- Inserting complex nested structures INSERT INTO user_complex_data (detailed_profile) VALUES ( named_struct( 'personal', named_struct('name', 'Bob', 'email', 'bob@test.com'), 'address', named_struct('city', 'Shanghai', 'country', 'China'), 'preferences', map('lang', 'zh', 'theme', 'dark') ) );

ARRAY and MAP Type Usage

CREATE TABLE collection_types_demo ( record_id BIGINT IDENTITY, -- Array types tags ARRAY<STRING>, scores ARRAY<INT>, nested_arrays ARRAY<ARRAY<STRING>>, -- Map types config MAP<STRING, STRING>, metrics MAP<STRING, DOUBLE>, complex_map MAP<STRING, ARRAY<INT>> ); -- Correct insert syntax INSERT INTO collection_types_demo ( tags, scores, nested_arrays, config, metrics, complex_map ) VALUES ( array('tech', 'AI', 'database'), -- String array array(85, 92, 78), -- Integer array array(array('group1', 'item1'), array('group2', 'item2')), -- Nested arrays map('env', 'prod', 'version', 'v2.2'), -- String map map('cpu_usage', 0.75, 'memory_usage', 0.60), -- Numeric map map('feature1', array(1, 2, 3), 'feature2', array(4, 5, 6)) -- Complex map );

Table Structure Design Patterns

Constraint Design Strategies

Proper Use of NOT NULL Constraints

NOT NULL constraints not only ensure data integrity but also serve as important hints for the query optimizer:

CREATE TABLE order_management ( order_id BIGINT IDENTITY, -- Core business fields: must be non-null customer_id INT NOT NULL, -- Core business association order_time TIMESTAMP NOT NULL, -- Core time dimension order_status TINYINT NOT NULL DEFAULT 0, -- Business status total_amount DECIMAL(12,2) NOT NULL, -- Core amount field -- Optional business fields: nullable coupon_code VARCHAR(20), -- Coupon (optional) customer_notes VARCHAR(500), -- Customer notes (optional) gift_message VARCHAR(200), -- Gift message (optional) -- System fields: non-null with defaults created_at TIMESTAMP NOT NULL DEFAULT current_timestamp(), updated_at TIMESTAMP, -- Update time (NULL on first creation) -- Partition field (generated column) date_partition STRING GENERATED ALWAYS AS ( date_format(order_time, 'yyyy-MM-dd') ) ) PARTITIONED BY (date_partition);

Using Default Values

Default value design should reflect business logic and system behavior:

CREATE TABLE user_account_enhanced ( user_id BIGINT IDENTITY, username VARCHAR(50) NOT NULL, -- Reasonable defaults for business statuses account_status TINYINT DEFAULT 1, -- 1=normal, 0=disabled, 2=locked email_verified BOOLEAN DEFAULT false, -- Default not verified phone_verified BOOLEAN DEFAULT false, -- Default not verified -- Business defaults for numeric fields credit_balance DECIMAL(10,2) DEFAULT 0.00, -- Default balance 0 loyalty_points INT DEFAULT 0, -- Default points 0 login_attempts TINYINT DEFAULT 0, -- Default login attempts 0 -- System defaults for time fields registration_time TIMESTAMP DEFAULT current_timestamp(), last_login_time TIMESTAMP, -- NULL before first login password_changed_at TIMESTAMP DEFAULT current_timestamp(), -- Defaults for JSON fields user_preferences JSON DEFAULT '{}', -- Default empty object security_settings JSON DEFAULT '{"two_factor": false, "login_notifications": true}' );

Complete Generated Column Function List

Generated columns only support deterministic scalar functions. Below is the complete list of functions verified through testing:

Date/Time Functions

Function Name	Description	Input Type	Return Type	Usage Example	Verified
`year()`	Extract year	DATE/TIMESTAMP	INT	`year(order_date)`	Passed
`month()`	Extract month	DATE/TIMESTAMP	INT	`month(order_date)`	Passed
`day()`	Extract day	DATE/TIMESTAMP	INT	`day(order_date)`	Passed
`hour()`	Extract hour	TIMESTAMP	INT	`hour(event_time)`	Passed
`minute()`	Extract minute	TIMESTAMP	INT	`minute(event_time)`	Passed
`second()`	Extract second	TIMESTAMP	INT	`second(event_time)`	Passed
`dayofweek()`	Day of week (1-7)	DATE/TIMESTAMP	INT	`dayofweek(order_date)`	Passed
`dayofyear()`	Day of year	DATE/TIMESTAMP	INT	`dayofyear(order_date)`	Passed
`quarter()`	Quarter (1-4)	DATE/TIMESTAMP	INT	`quarter(order_date)`	Passed
`date_format()`	Format date	DATE/TIMESTAMP	STRING	`date_format(dt, 'yyyy-MM-dd')`	Passed

Math Functions

Function Name	Description	Usage Example	Verified
`abs()`	Absolute value	`abs(profit_loss)`	Passed
`round()`	Round	`round(amount, 2)`	Passed
`ceil()`	Ceiling	`ceil(price)`	Passed
`floor()`	Floor	`floor(score)`	Passed
`power()`	Power	`power(base, 2)`	Passed
`sqrt()`	Square root	`sqrt(area)`	Passed
`mod()`	Modulo	`mod(id, 10)`	Passed

String Functions

Function Name	Description	Usage Example	Return Type	Verified
`concat()`	String concatenation	`concat(first_name, ' ', last_name)`	STRING	Passed
`length()`	String length	`length(username)`	INT	Passed
`upper()`	To uppercase	`upper(code)`	STRING	Passed
`lower()`	To lowercase	`lower(email)`	STRING	Passed
`trim()`	Remove leading/trailing spaces	`trim(input_text)`	STRING	Passed
`substr()`	Extract substring	`substr(phone, 1, 3)`	STRING	Passed
`replace()`	String replacement	`replace(text, 'old', 'new')`	STRING	Passed

Type Conversion and Conditional Functions

Function Name	Description	Usage Example	Verified
`cast()`	Type conversion	`cast(amount AS STRING)`	Passed
`string()`	To string	`string(user_id)`	Passed
`int()`	To integer	`int(price_str)`	Passed
`if()`	Simple conditional	`if(amount > 0, 'positive', 'negative')`	Passed
`coalesce()`	Null handling	`coalesce(nickname, username, 'anonymous')`	Passed
`nullif()`	Null conversion	`nullif(status, '')`	Passed

Unsupported Non-Deterministic Functions (Confirmed by Testing)

The following functions are not supported in generated columns and will cause syntax errors:

current_timestamp() - Current timestamp
current_date() - Current date
random() - Random number generation
uuid() - UUID generation
current_user() - Current user

Comprehensive Generated Column Application Example:

CREATE TABLE comprehensive_generated_columns ( order_id BIGINT IDENTITY, customer_name VARCHAR(100), order_time TIMESTAMP NOT NULL, total_amount DECIMAL(12,2), discount_rate DECIMAL(5,4) DEFAULT 0, -- Time dimension generated columns (for partitioning and analysis) order_year INT GENERATED ALWAYS AS (year(order_time)), order_month INT GENERATED ALWAYS AS (month(order_time)), order_date STRING GENERATED ALWAYS AS (date_format(order_time, 'yyyy-MM-dd')), order_hour INT GENERATED ALWAYS AS (hour(order_time)), quarter_label STRING GENERATED ALWAYS AS (concat('Q', string(quarter(order_time)))), weekday INT GENERATED ALWAYS AS (dayofweek(order_time)), -- Business calculation generated columns final_amount DECIMAL(12,2) GENERATED ALWAYS AS (round(total_amount * (1 - discount_rate), 2)), amount_category STRING GENERATED ALWAYS AS ( if(total_amount < 100, 'small', if(total_amount < 1000, 'medium', 'large')) ), -- String processing generated columns customer_initial STRING GENERATED ALWAYS AS (upper(substr(trim(customer_name), 1, 1))), name_length INT GENERATED ALWAYS AS (length(trim(customer_name))), display_name STRING GENERATED ALWAYS AS (concat('[', string(order_id), '] ', customer_name)), normalized_name STRING GENERATED ALWAYS AS (lower(trim(customer_name))) ) PARTITIONED BY (order_date) -- Use generated column as partition key COMMENT 'Order table - demonstrating various real-world use cases of generated columns';

Partition Architecture Design

Partition Strategy Selection Framework

Supported Partition Data Types (Confirmed by Testing)

Type	Supported	Usage Advice	Practical Example	Test Status
`TINYINT`	Yes	Status/level partitioning	`status TINYINT`	Verified
`SMALLINT`	Yes	Year/month partitioning	`year_part SMALLINT`	Verified
`INT`	Yes	Common partition type	`user_id INT`	Verified
`BIGINT`	Yes	Large value partitioning	`account_id BIGINT`	Verified
`STRING`	Yes	Most commonly used partition type	`date_partition STRING`	Verified
`VARCHAR(n)`	Yes	Variable-length string partitioning	`region VARCHAR(50)`	Verified
`CHAR(n)`	Yes	Fixed-length partitioning	`country CHAR(2)`	Verified
`BOOLEAN`	Yes	Binary partitioning	`is_active BOOLEAN`	Verified
`DATE`	Yes	Date partitioning	`order_date DATE`	Verified
`TIMESTAMP`	No	Needs conversion to other type	Use generated column conversion	Confirmed limit
`FLOAT/DOUBLE`	No	Not recommended due to precision	Avoid	Confirmed limit
`DECIMAL`	No	Precision and performance concerns	Avoid	Confirmed limit

Time Series Partition Patterns

Pattern 1: Daily Partitioning (Recommended, Most Common)

CREATE TABLE daily_business_logs ( log_id BIGINT IDENTITY, application VARCHAR(50) NOT NULL, log_level VARCHAR(10) NOT NULL, message STRING, user_id INT, log_timestamp TIMESTAMP NOT NULL, -- Use generated column to create date partition key date_partition STRING GENERATED ALWAYS AS ( date_format(log_timestamp, 'yyyy-MM-dd') ) ) PARTITIONED BY (date_partition) HASH CLUSTERED BY (application) SORTED BY (log_timestamp DESC) INTO 128 BUCKETS COMMENT 'Business log table - partitioned by date for easy log management and querying';

Pattern 2: Hourly Partitioning (High-Frequency Data)

CREATE TABLE realtime_metrics ( metric_id BIGINT IDENTITY, sensor_id VARCHAR(100) NOT NULL, metric_value DOUBLE, collect_time TIMESTAMP NOT NULL, -- Hourly partitioning for real-time monitoring hour_partition STRING GENERATED ALWAYS AS ( date_format(collect_time, 'yyyy-MM-dd-HH') ) ) PARTITIONED BY (hour_partition) HASH CLUSTERED BY (sensor_id) SORTED BY (collect_time DESC) INTO 512 BUCKETS COMMENT 'Real-time metrics table - hourly partitioning for high-frequency data ingestion';

Pattern 3: Monthly Partitioning (Historical Archive)

CREATE TABLE monthly_report_data ( report_id BIGINT IDENTITY, business_data JSON, created_time TIMESTAMP NOT NULL, -- Monthly partitioning to reduce partition count month_partition STRING GENERATED ALWAYS AS ( date_format(created_time, 'yyyy-MM') ) ) PARTITIONED BY (month_partition) COMMENT 'Monthly report data - partitioned by month for optimized long-term storage';

Business Dimension Partitioning Patterns

Multi-Tenant Partitioning Pattern:

CREATE TABLE saas_tenant_data ( record_id BIGINT IDENTITY, tenant_id VARCHAR(50) NOT NULL, entity_type VARCHAR(50) NOT NULL, entity_data JSON, created_time TIMESTAMP DEFAULT current_timestamp(), -- Partition by tenant for data isolation tenant_partition STRING GENERATED ALWAYS AS (tenant_id) ) PARTITIONED BY (tenant_partition) HASH CLUSTERED BY (entity_type) SORTED BY (created_time DESC) INTO 64 BUCKETS COMMENT 'Multi-tenant data table - partitioned by tenant ID for complete data isolation';

Geographic Region Partitioning Pattern:

CREATE TABLE global_order_data ( order_id BIGINT IDENTITY, customer_id INT NOT NULL, region VARCHAR(50) NOT NULL, -- Geographic region country VARCHAR(50) NOT NULL, order_data JSON, order_time TIMESTAMP ) PARTITIONED BY (region) -- Partition by region HASH CLUSTERED BY (customer_id) SORTED BY (order_time DESC) INTO 128 BUCKETS COMMENT 'Global order data - partitioned by geographic region for regionalized queries';

Composite Partition Strategy (Advanced)

Time + Business Dimension Dual Partitioning:

CREATE TABLE advanced_partitioning_example ( event_id BIGINT IDENTITY, user_id INT NOT NULL, business_type VARCHAR(50) NOT NULL, event_time TIMESTAMP NOT NULL, event_data JSON, -- Composite partition keys date_partition STRING GENERATED ALWAYS AS (date_format(event_time, 'yyyy-MM-dd')), business_partition STRING GENERATED ALWAYS AS (business_type) ) PARTITIONED BY (date_partition, business_partition) -- Dual partitioning HASH CLUSTERED BY (user_id) SORTED BY (event_time DESC) INTO 256 BUCKETS COMMENT 'Advanced partitioning example - dual partitioning by time and business dimension';

Partition Management and Optimization

Dynamic Partition Limits

Key Limitation: A single insert task can create a maximum of 2048 dynamic partitions

-- Operations that may exceed the limit INSERT INTO large_partition_table SELECT * FROM source_table_with_many_partitions; -- Fails if source table has >2048 partitions -- Solution 1: Batch insert INSERT INTO large_partition_table SELECT * FROM source_table_with_many_partitions WHERE date_column BETWEEN '2024-01-01' AND '2024-01-10'; -- Limit partition range -- Solution 2: Loop insert (application-level implementation) -- In the application, batch insert by dimensions like date/region, controlling to within 2000 partitions per batch

Data Lifecycle Management

-- Set table-level data lifecycle CREATE TABLE lifecycle_managed_table ( record_id BIGINT IDENTITY, business_data JSON, created_time TIMESTAMP, date_partition STRING GENERATED ALWAYS AS (date_format(created_time, 'yyyy-MM-dd')) ) PARTITIONED BY (date_partition) PROPERTIES ('data_lifecycle' = '90') -- Auto-cleanup after 90 days COMMENT 'Lifecycle-managed table - 90-day data retention policy';

Bucketing and Sorting Optimization

Bucketing Strategy Design

Bucket Count Planning Guide

Bucket configuration recommendations based on practical testing:

Data Size	Recommended Buckets	Target Size Per Bucket	Use Case	Test Result
< 10GB	16-32	~512MB	Small business tables, dimension tables	Passed
10GB-1TB	64-256	~1GB	Main business tables, fact tables	Passed
1TB-10TB	256-1024	~2GB	Large analytical tables, history tables	Recommended
> 10TB	1024+	~4GB	Very large data warehouse tables	Architecture supports

Bucket Column Selection Principles

High Cardinality Principle: Choose columns with evenly distributed, high-cardinality values
Query Affinity: Prioritize key columns used in JOINs and GROUP BY
Write Balance: Avoid data skew and write hot spots

-- Best practice: User behavior analysis table CREATE TABLE user_behavior_optimized ( behavior_id BIGINT IDENTITY, user_id INT NOT NULL, -- High cardinality, evenly distributed session_id VARCHAR(100) NOT NULL, behavior_type VARCHAR(50), -- Browse, click, purchase, etc. behavior_time TIMESTAMP NOT NULL, product_id INT, -- Partition strategy date_partition STRING GENERATED ALWAYS AS (date_format(behavior_time, 'yyyy-MM-dd')) ) PARTITIONED BY (date_partition) HASH CLUSTERED BY (user_id) -- User dimension bucketing for user behavior analysis SORTED BY (behavior_time DESC, behavior_type ASC) -- Time descending + behavior type ascending INTO 256 BUCKETS; -- Suitable for medium-to-large data volumes -- Index optimization CREATE BLOOMFILTER INDEX user_lookup_idx ON TABLE user_behavior_optimized(user_id); CREATE BLOOMFILTER INDEX product_filter_idx ON TABLE user_behavior_optimized(product_id); CREATE INVERTED INDEX behavior_type_idx ON TABLE user_behavior_optimized(behavior_type);

Sorting Strategy Optimization

The choice of sort fields directly impacts query performance, especially for range queries and TOP-N queries:

-- Sort optimization for financial transaction tables CREATE TABLE financial_transactions_optimized ( transaction_id BIGINT IDENTITY, account_id INT NOT NULL, transaction_time TIMESTAMP NOT NULL, amount DECIMAL(15,2) NOT NULL, transaction_type VARCHAR(20) NOT NULL, risk_score DECIMAL(5,3), date_partition STRING GENERATED ALWAYS AS (date_format(transaction_time, 'yyyy-MM-dd')) ) PARTITIONED BY (date_partition) HASH CLUSTERED BY (account_id) -- Bucket by account SORTED BY ( transaction_time DESC, -- Time descending: latest transactions first amount DESC, -- Amount descending: large transactions first risk_score DESC -- Risk score descending: high risk first ) INTO 512 BUCKETS COMMENT 'Financial transaction table - optimized for time, amount, and risk dimension query performance';

Index Architecture Design

Vector Index Detailed Configuration

Complete Distance Function Support List (All Verified)

Full Test Verification: All of the following distance functions have been thoroughly tested and confirmed to be fully available in the current version of the Singdata Lakehouse

Distance Function	Use Case	Mathematical Property	Performance	Verification Status
`cosine_distance`	Text semantic similarity, recommendation systems	Angular distance, normalization-independent	Medium performance	Fully Verified
`l2_distance`	Image feature matching, Euclidean space	Euclidean distance	Higher performance	Fully Verified
`dot_product`	Dot product similarity, normalized vectors	Dot product (optimized for min/max)	High Performance	Fully Verified
`jaccard_distance`	Set similarity, sparse vectors	Intersection/union ratio	Medium performance	Fully Verified
`hamming_distance`	Binary features, hash codes	Bit difference count	High performance	Fully Verified

Vector Index Scalar Type Configuration

Scalar Type	Storage Precision	Supported Vector Column Types	Performance Impact	Use Case
`f32`	32-bit float	INT, FLOAT	Standard performance, balanced precision	General recommendation, production-grade
`f16`	16-bit float	INT, FLOAT	Higher performance, slight precision loss	Mobile, fast retrieval
`i8`	8-bit integer	TINYINT, INT, FLOAT	High performance, quantized precision	Extreme performance requirements
`b1`	1-bit binary	TINYINT, INT, FLOAT	Highest performance, smallest storage	Binary vectors, bloom filter

HNSW Algorithm Parameter Details

Parameter	Default	Recommended Range	Description	Performance Impact
`m`	16	8-64	Maximum connections per node	Higher -> Better precision, higher memory
`ef.construction`	128	64-1000	Candidate set size during construction	Higher -> Better quality, longer build time
`max.elements`	auto	Based on data size	Estimated max vector count	Proper setting avoids rebuild

Complete Vector Index Configuration Examples

-- Create table with multiple vector types CREATE TABLE comprehensive_vector_demo ( doc_id INT, title VARCHAR(200), -- Vector configurations for different scenarios semantic_vector VECTOR(FLOAT, 768), -- Semantic search vector image_vector VECTOR(FLOAT, 512), -- Image feature vector user_vector VECTOR(INT, 256), -- User profile vector binary_vector VECTOR(TINYINT, 128) -- Binary feature vector ); -- High-quality semantic search index CREATE VECTOR INDEX semantic_search_idx ON TABLE comprehensive_vector_demo(semantic_vector) PROPERTIES ( "distance.function" = "cosine_distance", -- Preferred for semantic similarity "scalar.type" = "f32", -- Standard precision "m" = "32", -- Higher connections for better precision "ef.construction" = "400", -- High-quality construction "reuse.vector.column" = "false", -- Independent storage for best performance "compress.codec" = "uncompressed" -- No compression for guaranteed performance ); -- Fast image retrieval index CREATE VECTOR INDEX image_search_idx ON TABLE comprehensive_vector_demo(image_vector) PROPERTIES ( "distance.function" = "l2_distance", -- L2 distance suitable for image features "scalar.type" = "f16", -- Half-precision for speed "m" = "16", -- Standard connections "ef.construction" = "128", -- Balance quality and speed "reuse.vector.column" = "true", -- Reuse data to save space "compress.codec" = "lz4" -- Light compression ); -- Extreme performance binary index CREATE VECTOR INDEX binary_search_idx ON TABLE comprehensive_vector_demo(binary_vector) PROPERTIES ( "distance.function" = "hamming_distance", -- Dedicated for binary vectors "scalar.type" = "b1", -- 1-bit storage for minimal size "m" = "16", "ef.construction" = "128", "conversion.rule" = "as_bits", -- Process per bit "compress.codec" = "zstd", -- High compression ratio "compress.level" = "best" -- Maximum compression ); -- Recommendation system user profile index CREATE VECTOR INDEX user_profile_idx ON TABLE comprehensive_vector_demo(user_vector) PROPERTIES ( "distance.function" = "dot_product", -- Dot product distance function "scalar.type" = "i8", -- 8-bit integer suitable for discrete features "m" = "24", -- Moderate connections "ef.construction" = "200" -- Balanced construction quality );

Full-Text Search Index Configuration (Inverted Index)

Tokenizer Selection Guide

Tokenizer	Language Support	Tokenization Rule	Case Handling	Use Case	Performance
`keyword`	Universal	No tokenization, exact match	Preserve case	Status codes, tags, IDs	Highest Performance
`english`	English	ASCII alphanumeric boundaries	Lowercase	English documents, product descriptions	Higher Performance
`chinese`	Chinese-English mixed	Chinese segmentation + English word	English lowercase	Chinese content, mixed text	Medium Performance
`unicode`	Multilingual	Unicode text boundaries	Lowercase	International content, multilingual	Lower Performance

Inverted Index Support by Data Type

Data Type	Index Support	Tokenizer Requirement	Use Case	Notes
`STRING`	Supported	Recommended	Full-text search on long text	Recommend specifying analyzer for string types
`VARCHAR(n)`	Supported	Recommended	Title, description field search	Same requirements as STRING
`CHAR(n)`	Supported	Recommended	Fixed-length text	Less common use case
`INT/BIGINT`	Supported	Not needed	Numeric range query optimization	Auto-handled, efficient
`DECIMAL`	Supported	Not needed	Precise numeric queries	Common in financial scenarios
`DATE/TIMESTAMP`	Supported	Not needed	Time range query optimization	Essential for time-series data
`BOOLEAN`	Supported	Not needed	Boolean fast filtering	Status filtering optimization
`ARRAY<T>`	Partially supported	analyzer NOT supported	Tag lists, etc.	ARRAY type columns do not support the analyzer parameter

Complete Inverted Index Application Examples

-- Table design for comprehensive search scenarios CREATE TABLE comprehensive_search_demo ( record_id BIGINT IDENTITY, -- Text search fields title VARCHAR(200) NOT NULL, content STRING, tags ARRAY<STRING>, author VARCHAR(100), category VARCHAR(50), -- Numeric and time fields price DECIMAL(10,2), view_count INT, rating TINYINT, created_date DATE, updated_time TIMESTAMP, is_featured BOOLEAN DEFAULT false ); -- Chinese title search index CREATE INVERTED INDEX title_chinese_idx ON TABLE comprehensive_search_demo(title) PROPERTIES ('analyzer' = 'chinese'); -- Full-text content search index (multilingual) CREATE INVERTED INDEX content_unicode_idx ON TABLE comprehensive_search_demo(content) PROPERTIES ('analyzer' = 'unicode'); -- Tag array index (cannot specify analyzer) CREATE INVERTED INDEX tags_idx ON TABLE comprehensive_search_demo(tags); -- Author name search index CREATE INVERTED INDEX author_keyword_idx ON TABLE comprehensive_search_demo(author) PROPERTIES ('analyzer' = 'keyword'); -- Numeric field range query optimization CREATE INVERTED INDEX price_range_idx ON TABLE comprehensive_search_demo(price); CREATE INVERTED INDEX view_count_idx ON TABLE comprehensive_search_demo(view_count); CREATE INVERTED INDEX rating_idx ON TABLE comprehensive_search_demo(rating); -- Time field query optimization CREATE INVERTED INDEX created_date_idx ON TABLE comprehensive_search_demo(created_date); CREATE INVERTED INDEX updated_time_idx ON TABLE comprehensive_search_demo(updated_time); -- Boolean field fast filtering CREATE INVERTED INDEX featured_filter_idx ON TABLE comprehensive_search_demo(is_featured);

Bloom Filter Index Application (High-Cardinality Column Optimization)

Use Case Analysis

Use Case	Cardinality Feature	Query Pattern	Optimization Effect	Practical Application
User ID lookup	Extremely high (millions+)	= Exact match	Significant improvement	User behavior analysis
Email verification	High cardinality, strong uniqueness	= Existence check	Fast filtering	Registration dedup verification
Product SKU search	High cardinality, business unique	= Inventory query	Fast location	E-commerce inventory system
Order number query	Extremely high, unique	= Order lookup	Millisecond response	Order management system
Device ID monitoring	High cardinality, device unique	= Device status	Efficient filtering	IoT monitoring platform

Bloom Filter Best Practices

-- High-cardinality user management table CREATE TABLE user_management_optimized ( user_id BIGINT IDENTITY, username VARCHAR(50) NOT NULL, email VARCHAR(320) NOT NULL, mobile_phone VARCHAR(20), id_card_hash VARCHAR(64), -- ID card hash device_fingerprint VARCHAR(200), -- Device fingerprint -- Core business fields registration_date DATE, last_login_time TIMESTAMP, account_status TINYINT DEFAULT 1, -- 1=normal, 0=disabled, 2=locked verification_level TINYINT DEFAULT 0 -- 0=unverified, 1=email, 2=phone, 3=real-name ); -- Bloom filter indexes for high-cardinality fields CREATE BLOOMFILTER INDEX username_bloom_idx ON TABLE user_management_optimized(username); CREATE BLOOMFILTER INDEX email_bloom_idx ON TABLE user_management_optimized(email); CREATE BLOOMFILTER INDEX phone_bloom_idx ON TABLE user_management_optimized(mobile_phone); CREATE BLOOMFILTER INDEX idcard_bloom_idx ON TABLE user_management_optimized(id_card_hash); CREATE BLOOMFILTER INDEX device_bloom_idx ON TABLE user_management_optimized(device_fingerprint); -- Practical query application examples -- 1. Fast duplicate check during user registration SELECT COUNT(*) FROM user_management_optimized WHERE email = 'newuser@example.com'; -- Bloom filter fast filtering -- 2. Fast location during user login SELECT user_id, account_status, verification_level FROM user_management_optimized WHERE username = 'target_username'; -- Bloom filter accelerates lookup -- 3. Device risk control check SELECT user_id, COUNT(*) as device_usage_count FROM user_management_optimized WHERE device_fingerprint = 'specific_device_fp' -- Bloom filter fast matching GROUP BY user_id;

Index Naming and Management Standards

Index Naming Best Practices

Note: The current version of Singdata Lakehouse strictly enforces schema-level uniqueness for index naming.

Recommended Index Naming Convention

Naming Format: {table_name}_{index_type}_{column_name}_idx

Index Type Abbreviations:

vec - Vector Index (VECTOR INDEX)
inv - Inverted Index (INVERTED INDEX)
bloom - Bloom Filter Index (BLOOMFILTER INDEX)

-- Correct index naming practice CREATE TABLE product_catalog ( product_id INT, product_name VARCHAR(200), description STRING, category VARCHAR(100), price DECIMAL(10,2), features_vector VECTOR(FLOAT, 512) ); -- Unique and descriptive index names CREATE VECTOR INDEX products_vec_features_idx ON TABLE product_catalog(features_vector) PROPERTIES ("distance.function" = "cosine_distance"); CREATE INVERTED INDEX products_inv_name_idx ON TABLE product_catalog(product_name) PROPERTIES ('analyzer' = 'chinese'); CREATE INVERTED INDEX products_inv_desc_idx ON TABLE product_catalog(description) PROPERTIES ('analyzer' = 'unicode'); CREATE BLOOMFILTER INDEX products_bloom_category_idx ON TABLE product_catalog(category); -- Another table uses different index name prefixes CREATE TABLE user_content ( content_id BIGINT IDENTITY, content_text STRING, content_vector VECTOR(FLOAT, 768) ); CREATE VECTOR INDEX users_vec_content_idx -- Different table name prefix ON TABLE user_content(content_vector) PROPERTIES ("distance.function" = "cosine_distance"); CREATE INVERTED INDEX users_inv_text_idx -- Different table name prefix ON TABLE user_content(content_text) PROPERTIES ('analyzer' = 'chinese');

Index Feature Limitations

IF NOT EXISTS Syntax Current Status

Based on the latest test verification, the index creation syntax currently does not support the IF NOT EXISTS option:

-- Unsupported IF NOT EXISTS index syntax (causes syntax errors) CREATE VECTOR INDEX IF NOT EXISTS vec_idx ON TABLE example_table(embedding) PROPERTIES ("distance.function" = "cosine_distance"); CREATE INVERTED INDEX IF NOT EXISTS text_idx ON TABLE example_table(content) PROPERTIES ('analyzer'='chinese'); CREATE BLOOMFILTER INDEX IF NOT EXISTS bloom_idx ON TABLE example_table(user_id);

Before creating an index, it is recommended to first check whether the index exists to avoid errors:

-- Recommended approach: check if index exists first -- Then create CREATE VECTOR INDEX vec_idx ON TABLE example_table(embedding) PROPERTIES ("distance.function" = "cosine_distance");

Index Limitations on ARRAY Type Columns

Through testing, the following limitations exist when creating inverted indexes on ARRAY type columns:

-- ARRAY type columns do not support specifying the analyzer parameter CREATE TABLE array_column_table ( id INT, tags ARRAY<STRING> ); -- Error: Specifying analyzer on ARRAY type column CREATE INVERTED INDEX tags_analyzer_idx ON TABLE array_column_table(tags) PROPERTIES ('analyzer' = 'keyword'); -- Fails! -- Correct: Do not specify analyzer on ARRAY type column CREATE INVERTED INDEX tags_idx ON TABLE array_column_table(tags); -- Succeeds -- Alternative: Use STRING type to store tags CREATE TABLE string_tags_table ( id INT, tags_str STRING -- Comma-separated tag string ); CREATE INVERTED INDEX tags_str_idx ON TABLE string_tags_table(tags_str) PROPERTIES ('analyzer' = 'keyword'); -- Succeeds

Performance Optimization Strategies

Query Performance Optimization Techniques

Partition Pruning Optimization

Ensure query conditions effectively leverage partition pruning:

-- Excellent query pattern: Full partition pruning utilization SELECT user_id, COUNT(*) as activity_count, AVG(session_duration) as avg_duration FROM user_activity_logs WHERE date_partition BETWEEN '2024-01-01' AND '2024-01-31' -- Partition pruning AND user_id IN (12345, 67890, 54321) -- Bucket targeting AND activity_type = 'purchase' -- Index filtering GROUP BY user_id ORDER BY activity_count DESC; -- Query pattern to avoid: Cannot utilize partition pruning SELECT user_id, COUNT(*) as activity_count FROM user_activity_logs WHERE activity_time >= '2024-01-01 00:00:00' -- Using raw time column, no partition pruning AND activity_time <= '2024-01-31 23:59:59' GROUP BY user_id;

Multi-Dimensional Index Collaborative Optimization

-- Table structure designed for complex business queries CREATE TABLE business_analytics_optimized ( record_id BIGINT IDENTITY, user_id INT NOT NULL, product_category VARCHAR(50) NOT NULL, event_type VARCHAR(50) NOT NULL, channel VARCHAR(30) NOT NULL, event_data JSON, revenue_amount DECIMAL(12,2), event_timestamp TIMESTAMP NOT NULL, -- Partition key date_partition STRING GENERATED ALWAYS AS (date_format(event_timestamp, 'yyyy-MM-dd')) ) PARTITIONED BY (date_partition) -- Time dimension partition pruning HASH CLUSTERED BY (user_id) -- User dimension bucket targeting SORTED BY (event_timestamp DESC, revenue_amount DESC) -- Dual sort by time and revenue INTO 512 BUCKETS; -- Multi-dimensional index strategy CREATE BLOOMFILTER INDEX analytics_user_idx ON TABLE business_analytics_optimized(user_id); CREATE BLOOMFILTER INDEX analytics_category_idx ON TABLE business_analytics_optimized(product_category); CREATE BLOOMFILTER INDEX analytics_event_idx ON TABLE business_analytics_optimized(event_type); CREATE BLOOMFILTER INDEX analytics_channel_idx ON TABLE business_analytics_optimized(channel); CREATE INVERTED INDEX analytics_revenue_idx ON TABLE business_analytics_optimized(revenue_amount); CREATE INVERTED INDEX analytics_data_search_idx ON TABLE business_analytics_optimized(event_data) PROPERTIES ('analyzer' = 'unicode'); -- Efficient multi-dimensional business query SELECT product_category, event_type, COUNT(*) as event_count, SUM(revenue_amount) as total_revenue, AVG(revenue_amount) as avg_revenue FROM business_analytics_optimized WHERE date_partition = '2024-01-15' -- Partition pruning AND user_id IN (SELECT user_id FROM vip_users) -- Bucket targeting + bloom filter AND product_category = 'electronics' -- Bloom filter AND event_type = 'purchase' -- Bloom filter AND channel = 'mobile_app' -- Bloom filter AND revenue_amount > 100 -- Inverted index range query GROUP BY product_category, event_type ORDER BY total_revenue DESC;

Vector Similarity Query Optimization

-- Vector search performance optimization example CREATE TABLE vector_search_performance ( doc_id INT, doc_title VARCHAR(200), doc_category VARCHAR(50), content_embedding VECTOR(FLOAT, 768), summary_embedding VECTOR(FLOAT, 256), -- Lower dimension for fast pre-filtering created_date DATE, date_partition STRING GENERATED ALWAYS AS (date_format(created_date, 'yyyy-MM-dd')) ) PARTITIONED BY (date_partition); -- High-performance vector index CREATE VECTOR INDEX content_semantic_idx ON TABLE vector_search_performance(content_embedding) PROPERTIES ( "distance.function" = "cosine_distance", "scalar.type" = "f32", "m" = "32", -- Higher connections for better recall "ef.construction" = "400", -- High-quality construction "reuse.vector.column" = "false" -- Independent storage for optimal performance ); -- Fast pre-filter vector index CREATE VECTOR INDEX summary_fast_idx ON TABLE vector_search_performance(summary_embedding) PROPERTIES ( "distance.function" = "dot_product", -- Dot product distance function "scalar.type" = "f16", -- Half-precision for speed "m" = "16", "ef.construction" = "128" ); -- Traditional index-assisted filtering CREATE BLOOMFILTER INDEX doc_category_idx ON TABLE vector_search_performance(doc_category); -- Multi-level vector search strategy example -- 1. Coarse filtering: Use small vectors for fast pre-filtering -- 2. Fine ranking: Use large vectors for precise calculation -- 3. Filtering: Combine with traditional indexes for further filtering

Storage Cost Optimization Strategies

Precise Data Type Selection (Storage Optimization)

-- Table design example for storage cost optimization CREATE TABLE storage_cost_optimized ( -- Primary key field: necessary storage overhead record_id BIGINT IDENTITY, -- 8 bytes, required auto-increment PK -- Business ID fields: Choose types based on actual needs user_id INT NOT NULL, -- 4 bytes, supports 4.2 billion users product_id INT NOT NULL, -- 4 bytes, supports 4.2 billion products order_id BIGINT NOT NULL, -- 8 bytes, supports very large order volumes -- Status enum fields: Use smallest type order_status TINYINT DEFAULT 1, -- 1 byte vs VARCHAR(20) 20 bytes, saves 95% priority_level TINYINT DEFAULT 0, -- 1 byte, 0-255 levels sufficient user_level TINYINT DEFAULT 1, -- 1 byte, VIP level enumeration -- Boolean fields: Clear semantics is_paid BOOLEAN DEFAULT false, -- 1 byte vs VARCHAR(10) 10 bytes, saves 90% is_shipped BOOLEAN DEFAULT false, -- 1 byte, clear boolean semantics is_gift BOOLEAN DEFAULT false, -- 1 byte, gift flag -- Time fields: Choose based on precision requirements order_date DATE, -- 4 bytes, scenarios not needing time of day created_timestamp TIMESTAMP, -- 8 bytes, scenarios needing precise time shipped_date DATE, -- 4 bytes, ship date is sufficient -- Amount fields: Precise calculation item_price DECIMAL(10,2), -- Precise amount vs DOUBLE precision risk total_amount DECIMAL(12,2), -- Supports larger amounts discount_amount DECIMAL(8,2), -- Discount amount range is smaller -- String fields: Precise length settings customer_name VARCHAR(100), -- 100 chars covers 99.5% of real-world cases email VARCHAR(320), -- RFC5321 standard length phone VARCHAR(20), -- Supports international format +86-13812345678 address VARCHAR(500), -- Reasonable length for address info -- Complex data: Use appropriately order_metadata JSON, -- Extended properties vs many sparse columns -- Category IDs: Use integers instead of strings category_id SMALLINT, -- 2 bytes ID vs VARCHAR(50) 50 bytes, saves 96% subcategory_id SMALLINT, -- 2 bytes, supports 65K categories brand_id SMALLINT -- 2 bytes, brand ID ) COMMENT 'Storage cost optimization design - achieving optimal balance between functional requirements and storage costs'; -- Storage savings analysis: -- Status fields: VARCHAR(20) -> TINYINT, saving 19 bytes per row -- Boolean fields: VARCHAR(10) -> BOOLEAN, saving 9 bytes per row -- Category fields: VARCHAR(50) -> SMALLINT, saving 48 bytes per row -- Total savings: ~76 bytes per row, ~760MB saved for tens of millions of records

Bucket Count Optimization Strategy

-- Bucket optimization examples based on data scale -- Small table optimization (< 10GB): Avoid excessive bucketing CREATE TABLE small_table_optimized ( id BIGINT IDENTITY, name VARCHAR(100), category VARCHAR(50), data JSON ) HASH CLUSTERED BY (category) -- Bucket by business dimension SORTED BY (id ASC) -- Simple sorting INTO 16 BUCKETS -- Moderate bucket count, avoids small file issues COMMENT 'Small table optimization - 16 buckets balance performance and management complexity'; -- Medium table optimization (10GB-1TB): Standard configuration CREATE TABLE medium_table_optimized ( record_id BIGINT IDENTITY, user_id INT NOT NULL, business_data JSON, created_time TIMESTAMP, date_partition STRING GENERATED ALWAYS AS (date_format(created_time, 'yyyy-MM-dd')) ) PARTITIONED BY (date_partition) HASH CLUSTERED BY (user_id) -- High-cardinality column bucketing SORTED BY (created_time DESC) -- Time sorting INTO 128 BUCKETS -- Standard bucket count, balances concurrency and file size COMMENT 'Medium table optimization - 128 buckets suitable for mainstream business scenarios'; -- Large table optimization (> 1TB): High concurrency configuration CREATE TABLE large_table_optimized ( event_id BIGINT IDENTITY, user_id INT NOT NULL, session_id VARCHAR(100), event_data JSON, event_time TIMESTAMP, date_partition STRING GENERATED ALWAYS AS (date_format(event_time, 'yyyy-MM-dd')) ) PARTITIONED BY (date_partition) HASH CLUSTERED BY (user_id, session_id) -- Composite bucketing for better distribution uniformity SORTED BY (event_time DESC) INTO 512 BUCKETS -- High bucket count for high-concurrency writes and queries COMMENT 'Large table optimization - 512 buckets support large-scale concurrent processing';

Common Design Pitfalls and Solutions

Data Type Design Pitfalls

Pitfall 1: Wrong IDENTITY Column Type

Error Scenario:

-- All of the following IDENTITY declarations will fail CREATE TABLE identity_type_errors ( id INT IDENTITY, -- Fails: INT type not supported small_id SMALLINT IDENTITY, -- Fails: SMALLINT type not supported char_id CHAR(10) IDENTITY, -- Fails: character type not supported decimal_id DECIMAL(10,0) IDENTITY -- Fails: DECIMAL type not supported ); -- Error message: invalid identity column type int, currently only BIGINT is supported

Correct Solution:

-- Correct: Uniformly use BIGINT IDENTITY CREATE TABLE identity_correct_usage ( id BIGINT IDENTITY, -- Only supported IDENTITY type user_id INT NOT NULL, -- Business IDs use other appropriate types order_code VARCHAR(50) NOT NULL, -- Business codes use strings sequence_num INT DEFAULT 1 -- Sequence numbers use plain INT ) COMMENT 'IDENTITY column correct usage example';

Pitfall 2: Improper VARCHAR Length Settings

Problem Analysis:

-- Common length setting errors CREATE TABLE varchar_length_problems ( name VARCHAR(10000), -- Overallocation: wastes storage space email VARCHAR(50), -- Insufficient length: email standard is 320 characters phone VARCHAR(255), -- Overallocation: 20 characters sufficient for phone title VARCHAR(100), -- Insufficient length: article titles typically need 200 chars description VARCHAR(500000) -- Massive allocation: should use STRING type );

Optimized Solution:

-- Reasonable length settings based on actual business requirements CREATE TABLE varchar_length_optimized ( name VARCHAR(100), -- Name: covers 99.5% of real-world cases email VARCHAR(320), -- Email: RFC5321 international standard length phone VARCHAR(20), -- Phone: supports international format +86-13812345678 title VARCHAR(200), -- Title: balances SEO needs and storage efficiency summary VARCHAR(500), -- Summary: reasonable summary length description STRING -- Long description: use STRING for variable length ) COMMENT 'VARCHAR length optimization - reasonable settings based on actual business research';

Pitfall 3: Using Float Types for Financial Calculations

Risk Demonstration:

-- Precision problems with float types in financial calculations CREATE TABLE financial_precision_risks ( account_id INT, balance DOUBLE, -- Risk: floating point precision issues interest_rate FLOAT, -- Risk: cumulative compound calculation errors transaction_amount DOUBLE -- Risk: transaction amount calculation errors ); -- Precision problem demonstration INSERT INTO financial_precision_risks VALUES (1, 0.1 + 0.2, 0.001, 1.0); -- Expected: balance = 0.3 -- Actual: balance = 0.30000000000000004 (precision error) -- Compound calculation error demonstration SELECT balance * interest_rate as calculated_interest, -- May produce precision errors (balance * interest_rate * 12) as annual_interest -- Errors are amplified FROM financial_precision_risks;

Correct Solution:

-- Use precise DECIMAL type for financial calculations CREATE TABLE financial_precision_correct ( account_id INT, balance DECIMAL(15,2), -- Precise: supports tens of millions, 2 decimal places interest_rate DECIMAL(8,6), -- Precise: supports interest rate, 6 decimal precision transaction_amount DECIMAL(15,2), -- Precise: no precision loss in transaction amounts -- DECIMAL configurations for different business scenarios daily_limit DECIMAL(10,2), -- Daily limit: ten-thousand-level amounts annual_fee DECIMAL(8,2), -- Annual fee: thousand-level amounts exchange_rate DECIMAL(10,8) -- Exchange rate: high-precision decimal ) COMMENT 'Financial data precise calculation - using DECIMAL to ensure calculation accuracy'; -- Precise calculation verification INSERT INTO financial_precision_correct VALUES (1, 0.30, 0.001000, 1.00, 5000.00, 200.00, 6.78901234); -- Precise compound calculations SELECT balance * interest_rate as precise_interest, -- Precise calculation balance * interest_rate * 12 as precise_annual, -- Precise annualized calculation transaction_amount * exchange_rate as precise_conversion -- Precise exchange rate conversion FROM financial_precision_correct;

Partition Design Pitfalls

Pitfall 4: Unsupported Partition Column Types

Error Scenario:

-- Unsupported partition column types (confirmed to fail in testing) CREATE TABLE partition_type_errors ( id INT, amount DECIMAL(10,2), -- DECIMAL not supported for direct partitioning price DOUBLE, -- DOUBLE not supported for partitioning created_time TIMESTAMP, -- TIMESTAMP cannot be used directly for partitioning location_point STRUCT<lat:DOUBLE,lng:DOUBLE> -- Complex types not supported for partitioning ) PARTITIONED BY (created_time); -- Fails! -- Error message example: -- Unsupported data type for partition transform: timestamp_ltz

Correct Solution:

-- Use generated columns to convert to supported partition types CREATE TABLE partition_type_solutions ( id INT, amount DECIMAL(10,2), price DOUBLE, created_time TIMESTAMP, location_point STRUCT<lat:DOUBLE,lng:DOUBLE>, -- Use generated column to convert TIMESTAMP to STRING (supports partitioning) date_partition STRING GENERATED ALWAYS AS ( date_format(created_time, 'yyyy-MM-dd') ), -- Use generated column to convert DECIMAL to category (supports partitioning) amount_range STRING GENERATED ALWAYS AS ( if(amount < 100, 'small', if(amount < 1000, 'medium', 'large')) ), -- Use generated column to extract field from complex type (supports partitioning) location_region STRING GENERATED ALWAYS AS ( if(location_point.lat > 35, 'north', 'south') ) ) PARTITIONED BY (date_partition) -- Success: STRING type supports partitioning COMMENT 'Partition type solutions - using generated columns to convert unsupported types';

Pitfall 5: Dynamic Partition Count Exceeded

Problem Scenario:

-- Operations that may exceed dynamic partition limit INSERT INTO large_partition_table SELECT * FROM source_table_with_many_dates; -- Fails if source table has >2048 distinct dates -- Error message: -- The count of dynamic partitions exceeds the maximum number 2048

Solution Strategies:

-- Strategy 1: Batch insert by time range INSERT INTO large_partition_table SELECT * FROM source_table_with_many_dates WHERE event_date BETWEEN '2024-01-01' AND '2024-01-10'; -- Limit partition range INSERT INTO large_partition_table SELECT * FROM source_table_with_many_dates WHERE event_date BETWEEN '2024-02-01' AND '2024-02-29'; -- Second batch: 29 partitions -- Strategy 2: Batch insert by partition value INSERT INTO large_partition_table SELECT * FROM source_table_with_many_dates WHERE region IN ('north', 'south', 'east', 'west'); -- Limit to 4 partitions -- Strategy 3: Pre-filter data WITH filtered_source AS ( SELECT *, date_format(event_timestamp, 'yyyy-MM-dd') as date_part FROM source_table_with_many_dates WHERE event_timestamp >= '2024-01-01' -- Pre-filter to reduce partition count AND event_timestamp < '2024-02-01' ) INSERT INTO large_partition_table SELECT * FROM filtered_source; -- Strategy 4: Application-level loop control (pseudocode) -- for month in ['2024-01', '2024-02', ...]: -- INSERT INTO table SELECT * FROM source WHERE month_partition = month

Index Design Pitfalls

Pitfall 6: Index Naming Management

Important Update: Through testing, the current version of the Singdata Lakehouse strictly enforces schema-level uniqueness for index names.

-- Use table name prefix for unique index naming CREATE TABLE orders ( order_id INT, customer_id INT, order_content STRING ); CREATE INVERTED INDEX orders_inv_customer_idx ON TABLE orders(customer_id); CREATE INVERTED INDEX orders_inv_content_idx ON TABLE orders(order_content) PROPERTIES('analyzer'='keyword'); CREATE TABLE products ( product_id INT, customer_id INT, product_description STRING ); CREATE INVERTED INDEX products_inv_customer_idx ON TABLE products(customer_id); CREATE INVERTED INDEX products_inv_desc_idx ON TABLE products(product_description) PROPERTIES('analyzer'='chinese'); -- Recommended index naming convention -- Format: {table_name}_{index_type}_{column_name}_idx -- Examples: users_bloom_email_idx, orders_vec_features_idx

Pitfall 7: PRIMARY KEY Constraint Conflict with HASH CLUSTERED BY

Problem Scenario:

-- PRIMARY KEY constraint conflicts with HASH CLUSTERED BY CREATE TABLE table_with_conflict ( tenant_id VARCHAR(50) PRIMARY KEY, tenant_name VARCHAR(200) NOT NULL, tenant_status TINYINT DEFAULT 1 ) HASH CLUSTERED BY (tenant_id) -- Conflicts with PRIMARY KEY INTO 32 BUCKETS; -- Error message: CLUSTERED BY definition conflicts with enforced PRIMARY KEY -- or UNIQUE constraints defined at :[31,2], must HASH CLUSTERED BY ... SORTED BY ... ASC -- with all PRIMARY KEY or UNIQUE columns

Solutions:

-- Solution 1: Remove PRIMARY KEY constraint, use plain non-null column CREATE TABLE solution_remove_pk ( tenant_id VARCHAR(50) NOT NULL, -- Remove PRIMARY KEY tenant_name VARCHAR(200) NOT NULL, tenant_status TINYINT DEFAULT 1 ) HASH CLUSTERED BY (tenant_id) INTO 32 BUCKETS; -- Solution 2: Adjust HASH CLUSTERED BY and SORTED BY to meet requirements CREATE TABLE solution_adjust_cluster ( tenant_id VARCHAR(50) PRIMARY KEY, tenant_name VARCHAR(200) NOT NULL, tenant_status TINYINT DEFAULT 1 ) HASH CLUSTERED BY (tenant_id) -- Keep consistent with PRIMARY KEY SORTED BY (tenant_id ASC) -- Add sorting with ASC INTO 32 BUCKETS;

PRIMARY KEY and Bucketing Strategy Best Practices

Based on test-verified results, we recommend the following design guidance:

Avoid using both simultaneously: In most scenarios, avoid using both PRIMARY KEY constraints and HASH CLUSTERED BY. Choose one:
- For uniqueness constraint scenarios, use PRIMARY KEY
- For performance optimization on large tables, use HASH CLUSTERED BY with bloom filter indexes
Rules when both must be used: If business needs require using both, the following conditions must ALL be met:
- The HASH CLUSTERED BY column(s) must include ALL PRIMARY KEY columns
- A SORTED BY clause must be added
- The SORTED BY clause must include ALL PRIMARY KEY columns
- All PRIMARY KEY columns in SORTED BY must use ASC sort direction
Reference examples:

-- Best practice 1: Only use PRIMARY KEY (recommended for small tables) CREATE TABLE customer_profiles ( customer_id INT PRIMARY KEY, customer_name VARCHAR(100) NOT NULL, customer_email VARCHAR(200) ); -- Best practice 2: Only use HASH CLUSTERED BY (recommended for large tables) CREATE TABLE customer_events ( event_id BIGINT IDENTITY, customer_id INT NOT NULL, event_type VARCHAR(50), event_time TIMESTAMP ) HASH CLUSTERED BY (customer_id) SORTED BY (event_time DESC) INTO 128 BUCKETS; -- Create bloom filter index for efficient lookup CREATE BLOOMFILTER INDEX customer_lookup_idx ON TABLE customer_events(customer_id); -- Best practice 3: Correct configuration when both must be used CREATE TABLE order_items ( order_id INT, item_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id, item_id) ) HASH CLUSTERED BY (order_id, item_id) -- Includes all PRIMARY KEY columns SORTED BY (order_id ASC, item_id ASC) -- Includes all PRIMARY KEY columns, all ASC INTO 64 BUCKETS;

Pitfall 8: Incorrect analyzer Usage on ARRAY Type Columns

Error Scenario:

-- Using analyzer on ARRAY type columns causes errors CREATE TABLE array_column_table ( id INT, tags ARRAY<STRING> ); CREATE INVERTED INDEX tags_analyzer_idx ON TABLE array_column_table(tags) PROPERTIES ('analyzer' = 'keyword'); -- Fails! ARRAY type does not support analyzer parameter -- Error message example: -- invalid.inverted.index.analyzer.type, array<string>

Correct Solution:

-- Correct: Create inverted index on ARRAY column without specifying analyzer CREATE INVERTED INDEX tags_idx ON TABLE array_column_table(tags); -- Succeeds: no analyzer specified -- Or use STRING type with delimiter CREATE TABLE string_tags_table ( id INT, tags_str STRING -- Comma-separated tag string ); CREATE INVERTED INDEX tags_str_idx ON TABLE string_tags_table(tags_str) PROPERTIES ('analyzer' = 'keyword'); -- Succeeds: STRING type supports analyzer

Generated Column Design Pitfalls

Pitfall 9: Using Non-Deterministic Functions in Generated Columns

Error Scenario:

-- Using non-deterministic functions in generated columns (confirmed to fail in testing) CREATE TABLE generated_column_errors ( id INT, event_data VARCHAR(1000), -- All of the following generated columns will cause creation failure auto_timestamp TIMESTAMP GENERATED ALWAYS AS (current_timestamp()), -- Fails random_id DOUBLE GENERATED ALWAYS AS (random()), -- Fails current_user_name STRING GENERATED ALWAYS AS (current_user()), -- Fails uuid_value STRING GENERATED ALWAYS AS (uuid()) -- Fails ); -- Error message: Generated column auto_timestamp only contains built-in/scalar/deterministic function

Correct Solution:

-- Distinguish between generated columns and default values CREATE TABLE generated_column_solutions ( id INT, event_time TIMESTAMP, event_data VARCHAR(1000), amount DECIMAL(10,2), -- Use DEFAULT values instead of generated columns (for non-deterministic functions) created_timestamp TIMESTAMP DEFAULT current_timestamp(), random_seed DOUBLE DEFAULT random(), creator_name STRING DEFAULT current_user(), -- Generated columns use deterministic functions (computed from other columns) event_year INT GENERATED ALWAYS AS (year(event_time)), event_date STRING GENERATED ALWAYS AS (date_format(event_time, 'yyyy-MM-dd')), data_length INT GENERATED ALWAYS AS (length(event_data)), amount_category STRING GENERATED ALWAYS AS ( if(amount < 100, 'small', if(amount < 1000, 'medium', 'large')) ), display_info STRING GENERATED ALWAYS AS ( concat('[', string(id), '] ', substr(event_data, 1, 50)) ) ) COMMENT 'Generated column correct usage - distinguishing deterministic computation from default value settings';

Troubleshooting Guide

Common Error Diagnosis and Solutions

Error 1: IDENTITY Column Type Error

Error Message:

invalid identity column type int, currently only BIGINT is supported

Root Cause: Attempting to use IDENTITY constraint on a non-BIGINT column

Diagnosis Steps:

Check the IDENTITY column definition in the CREATE TABLE statement
Confirm whether the IDENTITY column data type is BIGINT
Check if INT, SMALLINT, or other numeric types were mistakenly used

Solution:

-- Incorrect usage CREATE TABLE wrong_table (id INT IDENTITY, name VARCHAR(50)); -- Correct usage CREATE TABLE correct_table (id BIGINT IDENTITY, name VARCHAR(50));

Error 2: Index Naming Management

The current version of the Singdata Lakehouse may not strictly enforce schema-level uniqueness for index naming. Although indexes with the same name can be created successfully, we still recommend using unique index names for code maintainability and future version compatibility.

Best Practice:

-- Recommended unique index naming CREATE INVERTED INDEX table1_inv_content_idx ON TABLE table1(content); CREATE INVERTED INDEX table2_inv_content_idx ON TABLE table2(content); -- Naming convention: {table_name}_{index_type}_{column_name}_idx

Error 3: Generated Column Function Not Supported

Error Message:

Generated column auto_timestamp only contains built-in/scalar/deterministic function

Root Cause: Non-deterministic function used in a generated column

Diagnosis Steps:

Check the functions used in generated column expressions
Cross-reference with the deterministic function support list
Distinguish between default values and generated column use cases

Solution:

-- Incorrect: Using non-deterministic function in generated column created_at TIMESTAMP GENERATED ALWAYS AS (current_timestamp()) -- Correct: Use default value created_at TIMESTAMP DEFAULT current_timestamp() -- Correct: Generated column using deterministic function date_part STRING GENERATED ALWAYS AS (date_format(some_timestamp, 'yyyy-MM-dd'))

Error 4: Unsupported Partition Type

Error Message:

Unsupported data type for partition transform: timestamp_ltz

Root Cause: Using a data type not supported for partitioning

Diagnosis Steps:

Check the data type of the partition column
Cross-reference with the supported partition data type list
Evaluate if a generated column conversion can be used

Solution:

-- Incorrect: Using TIMESTAMP directly for partitioning PARTITIONED BY (created_time) -- Correct: Use generated column conversion CREATE TABLE correct_partition ( created_time TIMESTAMP, date_part STRING GENERATED ALWAYS AS (date_format(created_time, 'yyyy-MM-dd')) ) PARTITIONED BY (date_part);

Error 5: Dynamic Partition Count Exceeded

Error Message:

The count of dynamic partitions exceeds the maximum number 2048

Root Cause: A single insert operation involves more than 2048 dynamic partitions

Diagnosis Steps:

Analyze the partition key distribution in the source data
Count the number of distinct partition values
Evaluate the data insertion strategy

Solution:

-- Query partition distribution in source data SELECT partition_column, COUNT(*) FROM source_table GROUP BY partition_column ORDER BY COUNT(*) DESC; -- Batch insert data INSERT INTO target_table SELECT * FROM source_table WHERE date_column BETWEEN '2024-01-01' AND '2024-01-31';

Error 6: Specifying analyzer on ARRAY Type Column Index

Error Message:

invalid.inverted.index.analyzer.type, array<string>

Root Cause: Specifying the analyzer parameter when creating an inverted index on an ARRAY type column

Diagnosis Steps:

Check the CREATE INVERTED INDEX statement
Confirm whether the index column is of ARRAY type
Check if the analyzer parameter is included

Solution:

-- Incorrect: Specifying analyzer on ARRAY type CREATE INVERTED INDEX tags_analyzer_idx ON TABLE array_column_table(tags) PROPERTIES ('analyzer' = 'keyword'); -- Correct: Do not specify analyzer CREATE INVERTED INDEX tags_idx ON TABLE array_column_table(tags);

Performance Issue Diagnosis

Slow Query Performance

Possible Causes and Solutions:

Partition pruning not taking effect
-- Check if query uses partition column EXPLAIN SELECT * FROM table WHERE partition_column = 'value'; -- Ensure WHERE condition includes partition column WHERE date_partition = '2024-01-15' -- Instead of WHERE original_date = '2024-01-15'
Missing appropriate indexes
-- Create indexes for high-frequency query columns CREATE BLOOMFILTER INDEX table_column_idx ON TABLE table_name(column_name);
Improper bucketing strategy
-- Check cardinality distribution of bucket column SELECT bucket_column, COUNT(*) FROM table_name GROUP BY bucket_column ORDER BY COUNT(*) DESC; -- Choose high-cardinality, evenly distributed columns as bucket keys

Poor Write Performance

Possible Causes and Solutions:

Improper bucket count setting
-- Small table with too many buckets -> reduce bucket count -- Large table with too few buckets -> increase bucket count
Data skew issues
-- Choose a more evenly distributed bucket key HASH CLUSTERED BY (more_uniform_column)
Excessive index maintenance overhead
-- Drop unnecessary indexes DROP INDEX unnecessary_index_name;

Error Prevention Checklist

Pre-Table Creation Checks

IDENTITY column uses BIGINT type
Partition column types are in the supported list
Generated columns use only deterministic functions
VARCHAR lengths are set reasonably
Financial fields use DECIMAL type

Pre-Index Creation Checks

Index names are unique and descriptive
Inverted indexes specify appropriate tokenizer
ARRAY type columns do not specify analyzer
Vector index parameters are correctly configured
PRIMARY KEY and HASH CLUSTERED BY configurations are compatible

Pre-Data Insertion Checks

Estimated dynamic partition count does not exceed the limit
Complex type data insert syntax is verified
Data type matching is confirmed
Constraint conditions are satisfied

Design Review Checklist

Table Structure Design Checks

Data Type Design

IDENTITY Column Type: Uniformly use BIGINT IDENTITY (product limitation)
Financial Data Types: Use DECIMAL instead of FLOAT/DOUBLE (precision guarantee)
String Lengths: Set reasonable lengths based on actual business requirements (storage optimization)
Vector Type Syntax: Use correct VECTOR(scalar_type, dimension) format
Complex Type Insertion: Use struct() or named_struct() functions for STRUCT (correct syntax)

Constraints and Default Values

NOT NULL Constraints: Add NOT NULL constraints on core business fields
Default Value Settings: Set reasonable defaults for system fields
Generated Column Functions: Use only deterministic scalar functions (verified support list)
Primary Key Design: Avoid using primary keys (unless specially required)

Partition Strategy

Partition Column Types: Use data types supported for partitioning (confirmed support list)
Partition Granularity: Choose appropriate partition granularity to avoid too many small partitions
Generated Column Partitions: Use generated columns to convert unsupported types like TIMESTAMP
Dynamic Partition Limits: Control within 2048 partitions per single operation

Performance Optimization Checks

Bucketing Design

Bucket Column Selection: Choose high-cardinality, evenly distributed columns (test-verified)
Bucket Count: Set reasonable bucket count based on data scale (test-verified recommendations provided)
Sorting Strategy: Choose sort columns that support main query scenarios
Composite Bucketing: Consider multi-column composite bucketing for large tables

Index Strategy

Index Naming: Follow unique naming convention (recommended to still follow)
Vector Index: Distance function and parameters optimized for business scenarios (distance function support confirmed)
Inverted Index: Specify appropriate tokenizer for string types (verified)
ARRAY Index: Do not specify analyzer for ARRAY types (confirmed limitation)
Bloom Filter: Used for fast filtering on high-cardinality columns (verified)
PRIMARY KEY and Bucketing: Ensure configuration compatibility (confirmed conflict)

Query Optimization

Partition Pruning: Main queries can leverage partition pruning
Bucket Targeting: JOIN keys align with bucket columns
Index Utilization: Common filter conditions have corresponding index support
Multi-Dimensional Queries: Design multi-level index strategy for complex queries

Operations and Scalability Checks

Maintainability

Naming Convention: Table, field, and index names follow consistent conventions
Complete Comments: Tables and key fields have clear business comments
Lifecycle: Set reasonable data retention policies
Version Management: Important design decisions are documented

Scalability

Data Growth: Design considers future data volume growth
Business Expansion: Reserve expansion field space (e.g., JSON columns)
Index Expansion: Index strategy supports new query patterns
Bucket Headroom: Bucket count reserves expansion capacity

Fault Handling

Error Prevention: Follow common pitfall avoidance strategies
Monitoring Setup: Establish performance and capacity monitoring
Backup Strategy: Develop data backup and recovery plans
Emergency Plans: Prepare handling plans for common issues

Cost Optimization Checks

Storage Costs

Type Optimization: Use the smallest appropriate type for storage
Length Control: VARCHAR lengths set based on actual requirements
Compression Strategy: Use vector index compression parameters appropriately
Lifecycle: Set automatic data cleanup policies

Compute Costs

Index Count: Avoid creating too many unnecessary indexes
Query Optimization: Ensure queries can execute efficiently
Partition Strategy: Avoid too many small partitions increasing metadata overhead
Resource Configuration: Bucket count matches cluster resources

Enterprise Design Patterns in Practice

Pattern 1: Event Sourcing Architecture (Complete Implementation)

Use Case: Financial transactions, audit compliance, user behavior analysis, and other scenarios requiring complete historical records

-- Event store main table CREATE TABLE event_store_transactions ( event_id BIGINT IDENTITY, -- Event identification information aggregate_id VARCHAR(100) NOT NULL, -- Aggregate root ID (user ID, order ID, etc.) aggregate_type VARCHAR(50) NOT NULL, -- Aggregate type (User, Order, Payment, etc.) event_type VARCHAR(50) NOT NULL, -- Event type (Created, Updated, Deleted, etc.) event_version INT NOT NULL DEFAULT 1, -- Event version, supports schema evolution -- Event time information event_timestamp TIMESTAMP NOT NULL, -- Business event occurrence time ingestion_timestamp TIMESTAMP DEFAULT current_timestamp(), -- System ingestion time -- Event data and metadata event_data JSON NOT NULL, -- Event detailed data event_metadata JSON DEFAULT '{}', -- Event metadata (IP, device, etc.) -- Tracing information causation_id VARCHAR(100), -- Causation ID correlation_id VARCHAR(100), -- Correlation ID for business process tracing session_id VARCHAR(100), -- Session ID -- Business context tenant_id VARCHAR(50), -- Tenant ID for multi-tenant scenarios user_id VARCHAR(100), -- Operating user ID source_system VARCHAR(50), -- Source system identifier -- Partition and performance optimization date_partition STRING GENERATED ALWAYS AS (date_format(event_timestamp, 'yyyy-MM-dd')), hour_partition INT GENERATED ALWAYS AS (hour(event_timestamp)) ) PARTITIONED BY (date_partition) HASH CLUSTERED BY (aggregate_id) -- Bucket by aggregate root for entity reconstruction SORTED BY (event_timestamp ASC, event_version ASC) -- Guarantee event order INTO 512 BUCKETS COMMENT 'Event sourcing storage table - records all business events, supporting full audit trails'; -- Event query optimization indexes CREATE BLOOMFILTER INDEX events_aggregate_idx ON TABLE event_store_transactions(aggregate_id); CREATE BLOOMFILTER INDEX events_type_idx ON TABLE event_store_transactions(event_type); CREATE BLOOMFILTER INDEX events_tenant_idx ON TABLE event_store_transactions(tenant_id); CREATE INVERTED INDEX events_data_search_idx ON TABLE event_store_transactions(event_data) PROPERTIES ('analyzer' = 'unicode'); -- Snapshot table (performance optimization) CREATE TABLE aggregate_snapshots ( snapshot_id BIGINT IDENTITY, aggregate_id VARCHAR(100) NOT NULL, aggregate_type VARCHAR(50) NOT NULL, snapshot_version INT NOT NULL, -- Snapshot data snapshot_data JSON NOT NULL, -- Complete state snapshot of the aggregate root -- Snapshot metadata snapshot_timestamp TIMESTAMP NOT NULL, last_event_id BIGINT NOT NULL, -- Last event ID included in the snapshot last_event_version INT NOT NULL, -- Last event version included in the snapshot -- Performance optimization created_at TIMESTAMP DEFAULT current_timestamp(), date_partition STRING GENERATED ALWAYS AS (date_format(snapshot_timestamp, 'yyyy-MM-dd')) ) PARTITIONED BY (date_partition) HASH CLUSTERED BY (aggregate_id) SORTED BY (snapshot_timestamp DESC) INTO 128 BUCKETS COMMENT 'Aggregate snapshot table - periodically saves aggregate state, optimizing reconstruction performance'; -- Set data lifecycle ALTER TABLE event_store_transactions SET TBLPROPERTIES ('data_lifecycle' = '2555'); -- 7-year retention ALTER TABLE aggregate_snapshots SET TBLPROPERTIES ('data_lifecycle' = '365'); -- 1-year retention

Pattern 2: Real-Time Data Lake Architecture (Lambda Enhanced)

Use Case: Real-time analytics, big data processing, machine learning feature engineering

-- Real-time data stream layer (Speed Layer) CREATE TABLE realtime_data_stream ( stream_id BIGINT IDENTITY, -- Data source identification source_system VARCHAR(50) NOT NULL, data_type VARCHAR(50) NOT NULL, -- metrics, events, logs, etc. -- Business identification user_id INT, session_id VARCHAR(100), entity_id VARCHAR(100), -- Real-time data raw_data JSON NOT NULL, -- Raw data processed_data JSON, -- Preprocessed data -- Time information event_timestamp TIMESTAMP NOT NULL, -- Business time ingestion_timestamp TIMESTAMP DEFAULT current_timestamp(), -- Ingestion time processing_timestamp TIMESTAMP, -- Processing time -- Data quality data_quality_score DECIMAL(3,2), -- Data quality score validation_errors ARRAY<STRING>, -- Validation error list -- Real-time partitioning (by hour) hour_partition STRING GENERATED ALWAYS AS ( date_format(event_timestamp, 'yyyy-MM-dd-HH') ) ) PARTITIONED BY (hour_partition) HASH CLUSTERED BY (user_id) SORTED BY (event_timestamp DESC) INTO 1024 BUCKETS COMMENT 'Real-time data stream table - Lambda architecture speed layer, processing streaming data'; -- Real-time query optimization CREATE BLOOMFILTER INDEX realtime_user_idx ON TABLE realtime_data_stream(user_id); CREATE BLOOMFILTER INDEX realtime_source_idx ON TABLE realtime_data_stream(source_system); CREATE INVERTED INDEX realtime_data_search_idx ON TABLE realtime_data_stream(raw_data) PROPERTIES ('analyzer' = 'unicode'); -- Batch aggregation layer (Batch Layer) CREATE TABLE batch_aggregated_analytics ( agg_id BIGINT IDENTITY, -- Aggregation dimensions user_id INT NOT NULL, data_type VARCHAR(50) NOT NULL, source_system VARCHAR(50) NOT NULL, -- Time windows window_start TIMESTAMP NOT NULL, window_end TIMESTAMP NOT NULL, window_type VARCHAR(20) NOT NULL, -- HOUR, DAY, WEEK, MONTH -- Aggregation metrics event_count INT, unique_sessions INT, total_duration BIGINT, -- Milliseconds avg_quality_score DECIMAL(5,3), -- Statistical indicators min_value DOUBLE, max_value DOUBLE, avg_value DOUBLE, std_deviation DOUBLE, percentile_50 DOUBLE, percentile_95 DOUBLE, percentile_99 DOUBLE, -- Business metrics conversion_rate DECIMAL(5,4), error_rate DECIMAL(5,4), -- Batch processing metadata batch_id VARCHAR(100), batch_timestamp TIMESTAMP DEFAULT current_timestamp(), processing_version VARCHAR(20) DEFAULT '2.2', date_partition STRING GENERATED ALWAYS AS (date_format(window_start, 'yyyy-MM-dd')) ) PARTITIONED BY (date_partition) HASH CLUSTERED BY (user_id, data_type) SORTED BY (window_start DESC) INTO 256 BUCKETS COMMENT 'Batch aggregation table - Lambda architecture batch layer, providing accurate historical analysis'; -- Serving Layer unified view CREATE TABLE serving_layer_unified_view ( view_id BIGINT IDENTITY, -- Identification information user_id INT NOT NULL, metric_name VARCHAR(100) NOT NULL, -- Real-time data (last 1 hour) realtime_value DOUBLE, realtime_timestamp TIMESTAMP, realtime_confidence DECIMAL(3,2), -- Batch data (historical aggregation) batch_value DOUBLE, batch_timestamp TIMESTAMP, batch_window_type VARCHAR(20), -- Unified result (intelligent merge) unified_value DOUBLE, data_source VARCHAR(20), -- realtime, batch, hybrid confidence_level DECIMAL(3,2), -- Update information last_updated TIMESTAMP DEFAULT current_timestamp(), date_partition STRING GENERATED ALWAYS AS (date_format(last_updated, 'yyyy-MM-dd')) ) PARTITIONED BY (date_partition) HASH CLUSTERED BY (user_id) SORTED BY (last_updated DESC) INTO 128 BUCKETS COMMENT 'Serving layer unified view - merging real-time and batch results, providing unified query interface'; -- Set data lifecycle for different layers ALTER TABLE realtime_data_stream SET TBLPROPERTIES ('data_lifecycle' = '7'); -- Real-time data 7 days ALTER TABLE batch_aggregated_analytics SET TBLPROPERTIES ('data_lifecycle' = '365'); -- Batch data 1 year ALTER TABLE serving_layer_unified_view SET TBLPROPERTIES ('data_lifecycle' = '90'); -- Serving layer 3 months

Pattern 3: Multi-Tenant SaaS Data Architecture (Enterprise)

Use Case: Enterprise SaaS platforms, multi-tenant applications, scenarios requiring strict data isolation

-- Tenant master data table CREATE TABLE saas_tenant_registry ( tenant_id VARCHAR(50) NOT NULL, -- Removed PRIMARY KEY for HASH CLUSTERED BY compatibility tenant_name VARCHAR(200) NOT NULL, -- Tenant basic information subscription_plan VARCHAR(50) NOT NULL, -- free, basic, premium, enterprise tenant_status TINYINT DEFAULT 1, -- 1=active, 0=suspended, 2=trial -- Configuration information data_region VARCHAR(20) DEFAULT 'default', -- Data storage region schema_version VARCHAR(10) DEFAULT '2.2', -- Tenant schema version feature_flags JSON DEFAULT '{}', -- Feature flag configuration quota_settings JSON DEFAULT '{}', -- Quota limit settings -- Tenant metadata created_at TIMESTAMP DEFAULT current_timestamp(), updated_at TIMESTAMP, -- Contact information admin_email VARCHAR(320), billing_contact JSON ) HASH CLUSTERED BY (tenant_id) INTO 32 BUCKETS COMMENT 'Tenant registry table - manages basic information and configuration for all tenants'; -- Multi-tenant business data table (core table) CREATE TABLE saas_multi_tenant_data ( record_id BIGINT IDENTITY, tenant_id VARCHAR(50) NOT NULL, -- Business entity information entity_type VARCHAR(50) NOT NULL, -- user, order, product, invoice, etc. entity_id VARCHAR(100) NOT NULL, -- Entity ID within the tenant entity_status TINYINT DEFAULT 1, -- Entity status -- Business data core_data JSON NOT NULL, -- Core business data extended_data JSON DEFAULT '{}', -- Extended data custom_fields JSON DEFAULT '{}', -- Tenant custom fields -- Data classification and tags data_category VARCHAR(50), -- Data classification tags ARRAY<STRING>, -- Business tags priority_level TINYINT DEFAULT 1, -- Priority: 1=normal, 2=high, 3=critical -- Audit information created_by VARCHAR(100), updated_by VARCHAR(100), created_at TIMESTAMP DEFAULT current_timestamp(), updated_at TIMESTAMP, version_number INT DEFAULT 1, -- Data governance data_classification VARCHAR(20) DEFAULT 'internal', -- public, internal, confidential, restricted retention_policy VARCHAR(50), -- Data retention policy -- Performance optimization tenant_partition STRING GENERATED ALWAYS AS (tenant_id) ) PARTITIONED BY (tenant_partition) -- Tenant-level data isolation HASH CLUSTERED BY (entity_id) -- Entity dimension bucketing SORTED BY (updated_at DESC, priority_level DESC) -- Latest and high-priority data first INTO 256 BUCKETS COMMENT 'Multi-tenant business data table - realizing tenant-level data isolation and efficient querying'; -- Multi-tenant query optimization indexes CREATE BLOOMFILTER INDEX saas_entity_type_idx ON TABLE saas_multi_tenant_data(entity_type); CREATE BLOOMFILTER INDEX saas_entity_id_idx ON TABLE saas_multi_tenant_data(entity_id); CREATE INVERTED INDEX saas_tags_idx ON TABLE saas_multi_tenant_data(tags); CREATE INVERTED INDEX saas_core_data_idx ON TABLE saas_multi_tenant_data(core_data) PROPERTIES ('analyzer' = 'unicode'); -- Tenant usage statistics table (billing and monitoring) CREATE TABLE saas_tenant_usage_stats ( usage_id BIGINT IDENTITY, tenant_id VARCHAR(50) NOT NULL, -- Statistics time window stat_date DATE NOT NULL, stat_hour TINYINT, -- 0-23, NULL indicates daily statistics -- Usage statistics api_calls_count INT DEFAULT 0, storage_bytes_used BIGINT DEFAULT 0, data_transfer_bytes BIGINT DEFAULT 0, compute_seconds_used INT DEFAULT 0, -- Feature usage statistics active_users_count INT DEFAULT 0, unique_sessions_count INT DEFAULT 0, feature_usage_stats JSON DEFAULT '{}', -- Performance indicators avg_response_time_ms INT, error_rate DECIMAL(5,4), availability_percentage DECIMAL(5,2), -- Cost allocation estimated_cost_usd DECIMAL(10,4), -- Update information last_updated TIMESTAMP DEFAULT current_timestamp(), date_partition STRING GENERATED ALWAYS AS (string(stat_date)) ) PARTITIONED BY (date_partition) HASH CLUSTERED BY (tenant_id) SORTED BY (stat_date DESC, stat_hour DESC) INTO 64 BUCKETS COMMENT 'Tenant usage statistics table - supporting billing, monitoring, and resource management'; -- Set data lifecycle policies ALTER TABLE saas_multi_tenant_data SET TBLPROPERTIES ('data_lifecycle' = '1095'); -- 3 years business data ALTER TABLE saas_tenant_usage_stats SET TBLPROPERTIES ('data_lifecycle' = '730'); -- 2 years statistics data

Pattern 4: IoT Time-Series Data Architecture (Industrial Grade)

Use Case: Industrial IoT, smart manufacturing, device monitoring, sensor data processing

-- Device master data table CREATE TABLE iot_device_registry ( device_id VARCHAR(100) NOT NULL, -- Device basic information device_name VARCHAR(200), device_type VARCHAR(50) NOT NULL, -- sensor, actuator, gateway, edge device_model VARCHAR(100), manufacturer VARCHAR(100), firmware_version VARCHAR(50), -- Deployment information installation_location VARCHAR(200), geo_location JSON, -- {"lat": 39.9042, "lng": 116.4074} facility_id VARCHAR(50), production_line VARCHAR(50), -- Device configuration measurement_interval_seconds INT DEFAULT 60, data_retention_days INT DEFAULT 90, alert_thresholds JSON DEFAULT '{}', calibration_params JSON DEFAULT '{}', -- Device status device_status TINYINT DEFAULT 1, -- 1=online, 0=offline, 2=maintenance last_heartbeat TIMESTAMP, health_score DECIMAL(3,2), -- 0.00-1.00 -- Management information created_at TIMESTAMP DEFAULT current_timestamp(), updated_at TIMESTAMP ) HASH CLUSTERED BY (device_type) INTO 32 BUCKETS COMMENT 'IoT device registry table - manages metadata for all IoT devices'; -- High-frequency time-series data table CREATE TABLE iot_timeseries_measurements ( measurement_id BIGINT IDENTITY, -- Device and measurement identification device_id VARCHAR(100) NOT NULL, sensor_id VARCHAR(100), -- Sensor ID in composite devices measurement_type VARCHAR(50) NOT NULL, -- temperature, pressure, vibration, current, etc. -- Measurement data measurement_value DOUBLE, -- Primary value measurement_unit VARCHAR(20), -- Unit: C, Pa, Hz, A, etc. secondary_values JSON, -- Auxiliary measurement values (multi-dimensional sensors) -- Time information (high precision) measurement_timestamp TIMESTAMP NOT NULL, -- Device timestamp collection_timestamp TIMESTAMP DEFAULT current_timestamp(), -- Collection timestamp -- Data quality and status data_quality_code TINYINT DEFAULT 1, -- 1=good, 2=uncertain, 3=bad measurement_status TINYINT DEFAULT 0, -- 0=normal, 1=warning, 2=alarm, 3=fault confidence_level DECIMAL(3,2), -- Measurement confidence -- Anomaly detection results is_anomaly BOOLEAN DEFAULT false, anomaly_score DECIMAL(5,3), -- Anomaly score anomaly_type VARCHAR(50), -- Anomaly type -- Context information environment_context JSON, -- Environmental parameters (temperature, humidity, pressure, etc.) operational_context JSON, -- Operational parameters (load, RPM, etc.) -- High-frequency data partitioned by hour hour_partition STRING GENERATED ALWAYS AS ( date_format(measurement_timestamp, 'yyyy-MM-dd-HH') ) ) PARTITIONED BY (hour_partition) -- Hourly partitioning for time range queries HASH CLUSTERED BY (device_id) -- Bucket by device SORTED BY (measurement_timestamp DESC) -- Time descending, latest data first INTO 2048 BUCKETS -- Large number of devices requires more buckets COMMENT 'IoT time-series measurement data table - storing high-frequency sensor data and anomaly detection results'; -- Time-series data query optimization indexes CREATE BLOOMFILTER INDEX iot_device_lookup_idx ON TABLE iot_timeseries_measurements(device_id); CREATE BLOOMFILTER INDEX iot_measurement_type_idx ON TABLE iot_timeseries_measurements(measurement_type); CREATE INVERTED INDEX iot_anomaly_filter_idx ON TABLE iot_timeseries_measurements(is_anomaly); CREATE INVERTED INDEX iot_status_filter_idx ON TABLE iot_timeseries_measurements(measurement_status); -- Device status aggregation table (real-time computed results) CREATE TABLE iot_device_status_aggregated ( agg_id BIGINT IDENTITY, device_id VARCHAR(100) NOT NULL, -- Aggregation time window window_start TIMESTAMP NOT NULL, window_end TIMESTAMP NOT NULL, window_type VARCHAR(20) NOT NULL, -- MINUTE, HOUR, DAY measurement_type VARCHAR(50) NOT NULL, -- Statistical indicators measurement_count INT, valid_measurement_count INT, -- Count of good quality measurements -- Numeric statistics min_value DOUBLE, max_value DOUBLE, avg_value DOUBLE, median_value DOUBLE, std_deviation DOUBLE, -- Anomaly statistics anomaly_count INT DEFAULT 0, alarm_count INT DEFAULT 0, fault_count INT DEFAULT 0, -- Device health indicators uptime_percentage DECIMAL(5,2), data_quality_avg DECIMAL(3,2), health_trend TINYINT, -- 1=improving, 0=stable, -1=degrading -- Predictive maintenance indicators maintenance_score DECIMAL(5,3), -- Maintenance requirement score estimated_rul_hours INT, -- Estimated remaining useful life (hours) next_maintenance_date DATE, -- Computation metadata computed_timestamp TIMESTAMP DEFAULT current_timestamp(), computation_version VARCHAR(20) DEFAULT '2.2', model_version VARCHAR(20), -- Predictive model version date_partition STRING GENERATED ALWAYS AS (date_format(window_start, 'yyyy-MM-dd')) ) PARTITIONED BY (date_partition) HASH CLUSTERED BY (device_id) SORTED BY (window_start DESC) INTO 512 BUCKETS COMMENT 'Device status aggregation table - real-time computed device health status and predictive maintenance indicators'; -- Device alert event table CREATE TABLE iot_device_alerts ( alert_id BIGINT IDENTITY, -- Alert identification device_id VARCHAR(100) NOT NULL, alert_type VARCHAR(50) NOT NULL, -- threshold, anomaly, fault, offline alert_level TINYINT NOT NULL, -- 1=info, 2=warning, 3=error, 4=critical -- Alert content alert_title VARCHAR(200), alert_description STRING, alert_data JSON, -- Alert-related data -- Alert status alert_status TINYINT DEFAULT 1, -- 1=active, 2=acknowledged, 3=resolved acknowledged_by VARCHAR(100), resolved_by VARCHAR(100), -- Time information alert_timestamp TIMESTAMP NOT NULL, acknowledged_at TIMESTAMP, resolved_at TIMESTAMP, -- Business impact business_impact VARCHAR(100), -- Business impact description estimated_downtime_minutes INT, -- Estimated downtime date_partition STRING GENERATED ALWAYS AS (date_format(alert_timestamp, 'yyyy-MM-dd')) ) PARTITIONED BY (date_partition) HASH CLUSTERED BY (device_id) SORTED BY (alert_timestamp DESC, alert_level DESC) INTO 128 BUCKETS COMMENT 'Device alert event table - records and manages all device alert information'; -- Set tiered data lifecycle ALTER TABLE iot_timeseries_measurements SET TBLPROPERTIES ('data_lifecycle' = '90'); -- Raw data 3 months ALTER TABLE iot_device_status_aggregated SET TBLPROPERTIES ('data_lifecycle' = '730'); -- Aggregated data 2 years ALTER TABLE iot_device_alerts SET TBLPROPERTIES ('data_lifecycle' = '1095'); -- Alert records 3 years

Lab Environment Cleanup Guide

To ensure proper resource usage and avoid unnecessary storage overhead, the following cleanup operations should be performed after completing table design experiments:

Table Resource Cleanup

-- 1. Clean up test tables DROP TABLE IF EXISTS test_identity_table; DROP TABLE IF EXISTS test_identity_seed_table; DROP TABLE IF EXISTS test_string_types; DROP TABLE IF EXISTS test_vector_table; DROP TABLE IF EXISTS test_complex_types; DROP TABLE IF EXISTS test_constraints; DROP TABLE IF EXISTS test_generated_columns; -- 2. Clean up partition test tables DROP TABLE IF EXISTS test_partition_daily; DROP TABLE IF EXISTS test_partition_hourly; DROP TABLE IF EXISTS test_partition_tenant; DROP TABLE IF EXISTS test_partition_multi; DROP TABLE IF EXISTS partition_type_solutions; -- 3. Clean up index test tables DROP TABLE IF EXISTS test_vector_index_table; DROP TABLE IF EXISTS test_inverted_index_table; DROP TABLE IF EXISTS test_bloom_index_table; DROP TABLE IF EXISTS comprehensive_vector_demo; DROP TABLE IF EXISTS comprehensive_search_demo; DROP TABLE IF EXISTS user_management_optimized; DROP TABLE IF EXISTS product_catalog; DROP TABLE IF EXISTS user_content; -- 4. Clean up optimization test tables DROP TABLE IF EXISTS user_behavior_optimized; DROP TABLE IF EXISTS financial_transactions_optimized; DROP TABLE IF EXISTS business_analytics_optimized; DROP TABLE IF EXISTS vector_search_performance; DROP TABLE IF EXISTS storage_cost_optimized; DROP TABLE IF EXISTS small_table_optimized; DROP TABLE IF EXISTS medium_table_optimized; DROP TABLE IF EXISTS large_table_optimized; -- 5. Clean up enterprise architecture pattern tables -- Event sourcing architecture DROP TABLE IF EXISTS event_store_transactions; DROP TABLE IF EXISTS aggregate_snapshots; -- Real-time data lake architecture DROP TABLE IF EXISTS realtime_data_stream; DROP TABLE IF EXISTS batch_aggregated_analytics; DROP TABLE IF EXISTS serving_layer_unified_view; -- Multi-tenant SaaS architecture DROP TABLE IF EXISTS saas_tenant_registry; DROP TABLE IF EXISTS saas_multi_tenant_data; DROP TABLE IF EXISTS saas_tenant_usage_stats; -- IoT time-series data architecture DROP TABLE IF EXISTS iot_device_registry; DROP TABLE IF EXISTS iot_timeseries_measurements; DROP TABLE IF EXISTS iot_device_status_aggregated; DROP TABLE IF EXISTS iot_device_alerts;

Summary

Verification Results

This guide has been fully verified in the Singdata Lakehouse environment, with all key functional points confirmed to be available:

Verified Features

Data Types: IDENTITY (BIGINT only), vector types, complex types (STRUCT/ARRAY/MAP)
Constraints and Generated Columns: Deterministic function list, default value syntax
Partition Strategies: Supported partition types, generated column partition conversion
Bucketing and Sorting: Bucket count configuration, sorting strategy optimization
Index Architecture: 5 vector index distance functions, inverted index tokenizers, bloom filters
Performance Optimization: Query pruning, multi-dimensional index collaboration
Enterprise Architecture: Complete implementation of four design patterns

Key Findings and Corrections

Index Naming: Current version enforces schema-level uniqueness; follow unique naming
Vector Dimensions: Must strictly match defined dimensions on insert
ARRAY Index: analyzer parameter not supported
PRIMARY KEY Conflict: Strict conditions must be met when used simultaneously with HASH CLUSTERED BY

Core Value

The core value of this guide lies in:

Practicality: All examples are practically verified and can be directly applied in production
Completeness: Covers full-stack design guidance from basic types to enterprise architecture
Forward-Looking: Based on the latest product features, adapting to technology trends
Maintainability: Provides a complete troubleshooting and design review system

Usage Recommendations

New Projects: Build a design framework following the design philosophy chapter, reference enterprise patterns to choose the right architecture
Existing Systems: Use the design review checklist for system optimization and issue diagnosis
Team Training: Combine with actual business scenarios, study and practice chapter by chapter
Continuous Optimization: Regularly evaluate and adjust design strategies based on business development and data growth

Best Practice Recommendation: Strictly following the design principles and verified SQL syntax in this guide will significantly improve system performance, reduce operational complexity, and provide a reliable data infrastructure foundation for business growth.

References

Create Table Syntax

Note: This guide is based on testing results from the Singdata Lakehouse version as of May 2025. Subsequent versions may vary. Please check the official documentation regularly for the latest information.