fix(promote): index traces numbers on the querier's number read

A ::Int64/::Float64 cast fails the whole insert on a non-numeric value.

Assisted-by: Claude Opus 5.5

docs/api/openapi.yml

@@ -7754,6 +7754,31 @@ components:
       - metric
       - value
       type: object
+    PromotetypesIndexMaterializedPathsParams:
+      properties:
+        dryRun:
+          type: boolean
+        signal:
+          type: string
+      required:
+      - signal
+      type: object
+    PromotetypesIndexMaterializedPathsResult:
+      properties:
+        indexed:
+          items:
+            $ref: '#/components/schemas/PromotetypesPromotePath'
+          nullable: true
+          type: array
+        skipped:
+          items:
+            $ref: '#/components/schemas/PromotetypesPromotePath'
+          nullable: true
+          type: array
+      required:
+      - indexed
+      - skipped
+      type: object
     PromotetypesPromotePath:
       properties:
         context:
@@ -13761,6 +13786,77 @@ paths:
       tags:
       - promote
       x-signoz-stability: alpha
+  /api/v1/promoted_paths/materialized:
+    post:
+      deprecated: false
+      description: This endpoint indexes the JSON sub-columns of materialized paths
+        whose columns are indexed and returns the indexed and skipped paths.
+      operationId: IndexMaterializedPaths
+      parameters:
+      - in: query
+        name: signal
+        required: true
+        schema:
+          type: string
+      - in: query
+        name: dryRun
+        schema:
+          type: boolean
+      requestBody:
+        content:
+          application/json:
+            schema:
+              $ref: '#/components/schemas/PromotetypesIndexMaterializedPathsParams'
+      responses:
+        "200":
+          content:
+            application/json:
+              schema:
+                properties:
+                  data:
+                    $ref: '#/components/schemas/PromotetypesIndexMaterializedPathsResult'
+                  status:
+                    type: string
+                required:
+                - status
+                - data
+                type: object
+          description: OK
+        "400":
+          content:
+            application/json:
+              schema:
+                $ref: '#/components/schemas/RenderErrorResponse'
+          description: Bad Request
+        "401":
+          content:
+            application/json:
+              schema:
+                $ref: '#/components/schemas/RenderErrorResponse'
+          description: Unauthorized
+        "403":
+          content:
+            application/json:
+              schema:
+                $ref: '#/components/schemas/RenderErrorResponse'
+          description: Forbidden
+        "500":
+          content:
+            application/json:
+              schema:
+                $ref: '#/components/schemas/RenderErrorResponse'
+          description: Internal Server Error
+      security:
+      - api_key:
+        - logs-field:update
+        - traces-field:update
+      - tokenizer:
+        - logs-field:update
+        - traces-field:update
+      summary: Index materialized paths
+      tags:
+      - promote
+      x-signoz-stability: alpha
   /api/v1/roles:
     get:
       deprecated: false

frontend/src/api/generated/services/promote/index.ts

@@ -18,8 +18,11 @@ import type {
 } from 'react-query';
 
 import type {
+	IndexMaterializedPaths200,
+	IndexMaterializedPathsParams,
 	ListPromotedPaths200,
 	ListPromotedPathsParams,
+	PromotetypesIndexMaterializedPathsParamsDTO,
 	PromotetypesPromotePathDTO,
 	RenderErrorResponseDTO,
 } from '../sigNoz.schemas';
@@ -230,3 +233,104 @@ export const usePromotePaths = <
 > => {
 	return useMutation(getPromotePathsMutationOptions(options));
 };
+/**
+ * This endpoint indexes the JSON sub-columns of materialized paths whose columns are indexed and returns the indexed and skipped paths.
+ * @summary Index materialized paths
+ */
+export const indexMaterializedPaths = (
+	params: IndexMaterializedPathsParams,
+	promotetypesIndexMaterializedPathsParamsDTO?: BodyType<PromotetypesIndexMaterializedPathsParamsDTO>,
+	signal?: AbortSignal,
+) => {
+	return GeneratedAPIInstance<IndexMaterializedPaths200>({
+		url: `/api/v1/promoted_paths/materialized`,
+		method: 'POST',
+		headers: { 'Content-Type': 'application/json' },
+		data: promotetypesIndexMaterializedPathsParamsDTO,
+		params,
+		signal,
+	});
+};
+
+export const getIndexMaterializedPathsMutationOptions = <
+	TError = ErrorType<RenderErrorResponseDTO>,
+	TContext = unknown,
+>(options?: {
+	mutation?: UseMutationOptions<
+		Awaited<ReturnType<typeof indexMaterializedPaths>>,
+		TError,
+		{
+			params: IndexMaterializedPathsParams;
+			data?: BodyType<PromotetypesIndexMaterializedPathsParamsDTO>;
+		},
+		TContext
+	>;
+}): UseMutationOptions<
+	Awaited<ReturnType<typeof indexMaterializedPaths>>,
+	TError,
+	{
+		params: IndexMaterializedPathsParams;
+		data?: BodyType<PromotetypesIndexMaterializedPathsParamsDTO>;
+	},
+	TContext
+> => {
+	const mutationKey = ['indexMaterializedPaths'];
+	const { mutation: mutationOptions } = options
+		? options.mutation &&
+			'mutationKey' in options.mutation &&
+			options.mutation.mutationKey
+			? options
+			: { ...options, mutation: { ...options.mutation, mutationKey } }
+		: { mutation: { mutationKey } };
+
+	const mutationFn: MutationFunction<
+		Awaited<ReturnType<typeof indexMaterializedPaths>>,
+		{
+			params: IndexMaterializedPathsParams;
+			data?: BodyType<PromotetypesIndexMaterializedPathsParamsDTO>;
+		}
+	> = (props) => {
+		const { params, data } = props ?? {};
+
+		return indexMaterializedPaths(params, data);
+	};
+
+	return { mutationFn, ...mutationOptions };
+};
+
+export type IndexMaterializedPathsMutationResult = NonNullable<
+	Awaited<ReturnType<typeof indexMaterializedPaths>>
+>;
+export type IndexMaterializedPathsMutationBody =
+	| BodyType<PromotetypesIndexMaterializedPathsParamsDTO>
+	| undefined;
+export type IndexMaterializedPathsMutationError =
+	ErrorType<RenderErrorResponseDTO>;
+
+/**
+ * @summary Index materialized paths
+ */
+export const useIndexMaterializedPaths = <
+	TError = ErrorType<RenderErrorResponseDTO>,
+	TContext = unknown,
+>(options?: {
+	mutation?: UseMutationOptions<
+		Awaited<ReturnType<typeof indexMaterializedPaths>>,
+		TError,
+		{
+			params: IndexMaterializedPathsParams;
+			data?: BodyType<PromotetypesIndexMaterializedPathsParamsDTO>;
+		},
+		TContext
+	>;
+}): UseMutationResult<
+	Awaited<ReturnType<typeof indexMaterializedPaths>>,
+	TError,
+	{
+		params: IndexMaterializedPathsParams;
+		data?: BodyType<PromotetypesIndexMaterializedPathsParamsDTO>;
+	},
+	TContext
+> => {
+	return useMutation(getIndexMaterializedPathsMutationOptions(options));
+};

frontend/src/api/generated/services/sigNoz.schemas.ts

@@ -9506,6 +9506,17 @@ export interface PrometheusSuccessResponseSchemaDTO {
 	warnings?: string[];
 }
 
+export interface PromotetypesIndexMaterializedPathsParamsDTO {
+	/**
+	 * @type boolean
+	 */
+	dryRun?: boolean;
+	/**
+	 * @type string
+	 */
+	signal: string;
+}
+
 export interface PromotetypesWrappedIndexDTO {
 	fieldDataType?: TelemetrytypesFieldDataTypeDTO;
 	/**
@@ -9541,6 +9552,17 @@ export interface PromotetypesPromotePathDTO {
 	signal: string;
 }
 
+export interface PromotetypesIndexMaterializedPathsResultDTO {
+	/**
+	 * @type array,null
+	 */
+	indexed: PromotetypesPromotePathDTO[] | null;
+	/**
+	 * @type array,null
+	 */
+	skipped: PromotetypesPromotePathDTO[] | null;
+}
+
 export interface Querybuildertypesv5AggregationMetaDTO {
 	/**
 	 * @type array
@@ -12901,6 +12923,27 @@ export type ListPromotedPaths200 = {
 	status: string;
 };
 
+export type IndexMaterializedPathsParams = {
+	/**
+	 * @type string
+	 * @description undefined
+	 */
+	signal: string;
+	/**
+	 * @type boolean
+	 * @description undefined
+	 */
+	dryRun?: boolean;
+};
+
+export type IndexMaterializedPaths200 = {
+	data: PromotetypesIndexMaterializedPathsResultDTO;
+	/**
+	 * @type string
+	 */
+	status: string;
+};
+
 export type ListRoles200 = {
 	/**
 	 * @type array

pkg/apiserver/signozapiserver/promote.go

@@ -84,5 +84,27 @@ func (provider *provider) addPromoteRoutes(router *mux.Router) error {
 		return err
 	}
 
+	if err := router.Handle("/api/v1/promoted_paths/materialized", handler.New(provider.authzMiddleware.CheckResources(provider.promoteHandler.IndexMaterializedPaths, authtypes.SigNozAdminRoleName, authtypes.SigNozEditorRoleName), handler.OpenAPIDef{
+		ID:                  "IndexMaterializedPaths",
+		Tags:                []string{"promote"},
+		Summary:             "Index materialized paths",
+		Description:         "This endpoint indexes the JSON sub-columns of materialized paths whose columns are indexed and returns the indexed and skipped paths.",
+		Request:             nil,
+		RequestQuery:        new(promotetypes.IndexMaterializedPathsParams),
+		RequestContentType:  "",
+		Response:            new(promotetypes.IndexMaterializedPathsResult),
+		ResponseContentType: "application/json",
+		SuccessStatusCode:   http.StatusOK,
+		ErrorStatusCodes:    []int{http.StatusBadRequest},
+		SecuritySchemes:     newScopedSecuritySchemes([]string{coretypes.ResourceMetaResourceLogsField.Scope(coretypes.VerbUpdate), coretypes.ResourceMetaResourceTracesField.Scope(coretypes.VerbUpdate)}),
+	}, handler.WithResourceDefs(handler.TelemetryResourceDef{
+		Verb:      coretypes.VerbUpdate,
+		Category:  coretypes.ActionCategoryConfigurationChange,
+		Selector:  coretypes.WildcardSelector,
+		Resources: promotetypes.IndexMaterializedPathsResources,
+	}))).Methods(http.MethodPost).GetError(); err != nil {
+		return err
+	}
+
 	return nil
 }

pkg/modules/promote/implpromote/handler.go

@@ -72,3 +72,23 @@ func (h *handler) ListPromotedPaths(w http.ResponseWriter, r *http.Request) {
 
 	render.Success(w, http.StatusOK, paths)
 }
+
+func (h *handler) IndexMaterializedPaths(w http.ResponseWriter, r *http.Request) {
+	var params promotetypes.IndexMaterializedPathsParams
+	if err := binding.Query.BindQuery(r.URL.Query(), &params); err != nil {
+		render.Error(w, err)
+		return
+	}
+	if err := params.Validate(); err != nil {
+		render.Error(w, err)
+		return
+	}
+
+	paths, err := h.module.IndexMaterializedPaths(r.Context(), params)
+	if err != nil {
+		render.Error(w, err)
+		return
+	}
+
+	render.Success(w, http.StatusOK, paths)
+}

pkg/modules/promote/implpromote/module.go

@@ -2,10 +2,12 @@ package implpromote
 
 import (
 	"context"
+	"log/slog"
 	"strings"
 
 	schemamigrator "github.com/SigNoz/signoz-otel-collector/cmd/signozschemamigrator/schema_migrator"
 	"github.com/SigNoz/signoz/pkg/errors"
+	"github.com/SigNoz/signoz/pkg/factory"
 	"github.com/SigNoz/signoz/pkg/modules/promote"
 	"github.com/SigNoz/signoz/pkg/telemetrystore"
 	"github.com/SigNoz/signoz/pkg/types/ctxtypes"
@@ -22,10 +24,15 @@ var (
 type module struct {
 	metadataStore  telemetrytypes.MetadataStore
 	telemetryStore telemetrystore.TelemetryStore
+	settings       factory.ScopedProviderSettings
 }
 
-func NewModule(metadataStore telemetrytypes.MetadataStore, telemetrystore telemetrystore.TelemetryStore) promote.Module {
-	return &module{metadataStore: metadataStore, telemetryStore: telemetrystore}
+func NewModule(metadataStore telemetrytypes.MetadataStore, telemetrystore telemetrystore.TelemetryStore, providerSettings factory.ProviderSettings) promote.Module {
+	return &module{
+		metadataStore:  metadataStore,
+		telemetryStore: telemetrystore,
+		settings:       factory.NewScopedProviderSettings(providerSettings, "github.com/SigNoz/signoz/pkg/modules/promote/implpromote"),
+	}
 }
 
 func (m *module) ListPromotedPaths(ctx context.Context, filters promotetypes.ListPromotedPathsFilters) ([]promotetypes.PromotePath, error) {
@@ -101,6 +108,70 @@ func (m *module) listPromotedPaths(ctx context.Context, target promotetypes.Targ
 	return response, nil
 }
 
+func (m *module) IndexMaterializedPaths(ctx context.Context, params promotetypes.IndexMaterializedPathsParams) (*promotetypes.IndexMaterializedPathsResult, error) {
+	target, err := params.Target()
+	if err != nil {
+		return nil, err
+	}
+
+	keys, err := m.metadataStore.GetMaterializedKeys(ctx, target.Entry.Signal)
+	if err != nil {
+		return nil, err
+	}
+
+	paths := []*promotetypes.PromotePath{}
+	names := []string{}
+	for _, key := range keys {
+		if key.FieldContext != target.Entry.FieldContext || len(key.Indexes) == 0 {
+			continue
+		}
+		path, err := promotetypes.NewMaterializedPromotePath(target, key)
+		if err != nil {
+			return nil, err
+		}
+		paths = append(paths, path)
+		names = append(names, path.Path)
+	}
+
+	result := &promotetypes.IndexMaterializedPathsResult{Indexed: []*promotetypes.PromotePath{}, Skipped: []*promotetypes.PromotePath{}}
+	if len(paths) > 0 {
+		existing, err := m.metadataStore.ListJSONIndexes(ctx, target.JSONIndexLookup(), names...)
+		if err != nil {
+			return nil, err
+		}
+		existingByPath := map[string][]promotetypes.WrappedIndex{}
+		for _, index := range existing {
+			existingByPath[index.Name] = append(existingByPath[index.Name], promotetypes.WrappedIndex{FieldDataType: index.FieldDataType, Type: index.IndexType, Granularity: index.Granularity})
+		}
+		for _, path := range paths {
+			if indexes, ok := existingByPath[path.Path]; ok {
+				path.Indexes = indexes
+				result.Skipped = append(result.Skipped, path)
+				continue
+			}
+			result.Indexed = append(result.Indexed, path)
+		}
+	}
+
+	if !params.DryRun && len(result.Indexed) > 0 {
+		if err := m.promotePaths(ctx, target, result.Indexed...); err != nil {
+			return nil, err
+		}
+	}
+
+	indexed := make([]string, 0, len(result.Indexed))
+	for _, path := range result.Indexed {
+		indexed = append(indexed, path.Path)
+	}
+	skipped := make([]string, 0, len(result.Skipped))
+	for _, path := range result.Skipped {
+		skipped = append(skipped, path.Path)
+	}
+	m.settings.Logger().InfoContext(ctx, "indexed materialized paths", slog.String("signal", params.Signal), slog.Bool("dry_run", params.DryRun), slog.Int("indexed_count", len(indexed)), slog.Any("indexed", indexed), slog.Int("skipped_count", len(skipped)), slog.Any("skipped", skipped))
+
+	return result, nil
+}
+
 func (m *module) PromotePaths(ctx context.Context, paths ...*promotetypes.PromotePath) error {
 	byTarget := map[promotetypes.Target][]*promotetypes.PromotePath{}
 	targets := []promotetypes.Target{}

pkg/modules/promote/implpromote/module_test.go

@@ -9,6 +9,7 @@ import (
 	"github.com/stretchr/testify/assert"
 	"github.com/stretchr/testify/require"
 
+	"github.com/SigNoz/signoz/pkg/factory/factorytest"
 	"github.com/SigNoz/signoz/pkg/telemetrystore"
 	"github.com/SigNoz/signoz/pkg/telemetrystore/telemetrystoretest"
 	"github.com/SigNoz/signoz/pkg/types/promotetypes"
@@ -71,7 +72,7 @@ func TestPromotePaths(t *testing.T) {
 	for _, testCase := range testCases {
 		t.Run(testCase.name, func(t *testing.T) {
 			store := telemetrytypestest.NewMockMetadataStore()
-			m := NewModule(store, nil)
+			m := NewModule(store, nil, factorytest.NewSettings())
 
 			err := m.PromotePaths(ctx, testCase.paths...)
 			if testCase.wantErr {
@@ -194,7 +195,7 @@ func TestPromotePathsCreatesIndexes(t *testing.T) {
 			ts := telemetrystoretest.New(telemetrystore.Config{}, sqlmock.QueryMatcherRegexp)
 			store := telemetrytypestest.NewMockMetadataStore()
 			store.PromotedPathsMap[telemetrytypes.SignalLogs] = testCase.promoted
-			m := NewModule(store, ts)
+			m := NewModule(store, ts, factorytest.NewSettings())
 
 			ts.Mock().ExpectExec("ADD INDEX (.+)" + regexp.QuoteMeta(testCase.wantDDLColumn)).WillReturnError(nil)
 			require.NoError(t, m.PromotePaths(ctx, testCase.path))
@@ -390,7 +391,7 @@ func TestListPromotedPaths(t *testing.T) {
 			store := telemetrytypestest.NewMockMetadataStore()
 			store.PromotedPathsMap = testCase.promoted
 			store.LogsJSONIndexes = testCase.indexes
-			m := NewModule(store, nil)
+			m := NewModule(store, nil, factorytest.NewSettings())
 
 			paths, err := m.ListPromotedPaths(ctx, testCase.filters)
 			require.NoError(t, err)
@@ -408,3 +409,100 @@ func TestListPromotedPaths(t *testing.T) {
 		})
 	}
 }
+
+func TestIndexMaterializedPaths(t *testing.T) {
+	ctx := context.Background()
+	columnIndex := []telemetrytypes.TelemetryFieldKeySkipIndex{{IndexType: "bloom_filter(0.01)", Granularity: 64}}
+	materializedKeys := []*telemetrytypes.TelemetryFieldKey{
+		{Name: "user.id", Signal: telemetrytypes.SignalTraces, FieldContext: telemetrytypes.FieldContextAttribute, FieldDataType: telemetrytypes.FieldDataTypeString, Indexes: columnIndex},
+		{Name: "tenant.id", Signal: telemetrytypes.SignalTraces, FieldContext: telemetrytypes.FieldContextAttribute, FieldDataType: telemetrytypes.FieldDataTypeString},
+		{Name: "retry.count", Signal: telemetrytypes.SignalTraces, FieldContext: telemetrytypes.FieldContextAttribute, FieldDataType: telemetrytypes.FieldDataTypeNumber, Indexes: columnIndex},
+		{Name: "k8s.pod.name", Signal: telemetrytypes.SignalTraces, FieldContext: telemetrytypes.FieldContextResource, FieldDataType: telemetrytypes.FieldDataTypeString, Indexes: columnIndex},
+	}
+	userIDDDL := "`attributes.user.id_String_bloom_filter` attributes.`user.id`::String TYPE bloom_filter(0.01) GRANULARITY 64"
+	retryCountDDL := "`attributes.retry.count_Float64_bloom_filter` if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`retry.count`)), accurateCastOrNull(attributes.`retry.count`, 'Float64'), NULL) TYPE bloom_filter(0.01) GRANULARITY 64"
+
+	testCases := []struct {
+		name        string
+		params      promotetypes.IndexMaterializedPathsParams
+		keys        []*telemetrytypes.TelemetryFieldKey
+		existing    []telemetrytypes.TelemetryFieldKeySkipIndex
+		wantIndexed []string
+		wantSkipped []string
+		wantDDL     []string
+		wantErr     bool
+	}{
+		{
+			name:        "DryRun_NoDDL",
+			params:      promotetypes.IndexMaterializedPathsParams{Signal: "traces", DryRun: true},
+			keys:        materializedKeys,
+			wantIndexed: []string{"user.id", "retry.count"},
+			wantSkipped: []string{},
+		},
+		{
+			name:        "Run_IndexesBaseColumn",
+			params:      promotetypes.IndexMaterializedPathsParams{Signal: "traces"},
+			keys:        materializedKeys,
+			wantIndexed: []string{"user.id", "retry.count"},
+			wantSkipped: []string{},
+			wantDDL:     []string{userIDDDL, retryCountDDL},
+		},
+		{
+			name:        "AnyIndexBuilt_Skipped",
+			params:      promotetypes.IndexMaterializedPathsParams{Signal: "traces"},
+			keys:        materializedKeys,
+			existing:    []telemetrytypes.TelemetryFieldKeySkipIndex{{Name: "user.id", FieldContext: telemetrytypes.FieldContextAttribute, IndexType: "ngrambf_v1(4, 1024, 2, 0)"}},
+			wantIndexed: []string{"retry.count"},
+			wantSkipped: []string{"user.id"},
+			wantDDL:     []string{retryCountDDL},
+		},
+		{
+			name:    "CardinalPath_Rejected",
+			params:  promotetypes.IndexMaterializedPathsParams{Signal: "traces"},
+			keys:    append([]*telemetrytypes.TelemetryFieldKey{{Name: "session.550e8400-e29b-41d4-a716-446655440000", Signal: telemetrytypes.SignalTraces, FieldContext: telemetrytypes.FieldContextAttribute, FieldDataType: telemetrytypes.FieldDataTypeString, Indexes: columnIndex}}, materializedKeys...),
+			wantErr: true,
+		},
+		{
+			name:    "LogsSignal_NoAttributeTarget_Rejected",
+			params:  promotetypes.IndexMaterializedPathsParams{Signal: "logs"},
+			wantErr: true,
+		},
+		{
+			name:    "MissingSignal_Rejected",
+			wantErr: true,
+		},
+	}
+
+	for _, testCase := range testCases {
+		t.Run(testCase.name, func(t *testing.T) {
+			ts := telemetrystoretest.New(telemetrystore.Config{}, sqlmock.QueryMatcherRegexp)
+			store := telemetrytypestest.NewMockMetadataStore()
+			store.MaterializedKeys = testCase.keys
+			store.LogsJSONIndexes = testCase.existing
+			m := NewModule(store, ts, factorytest.NewSettings())
+			for _, ddl := range testCase.wantDDL {
+				ts.Mock().ExpectExec("ADD INDEX (.+)" + regexp.QuoteMeta(ddl)).WillReturnError(nil)
+			}
+
+			result, err := m.IndexMaterializedPaths(ctx, testCase.params)
+			if testCase.wantErr {
+				assert.Error(t, err)
+				return
+			}
+			require.NoError(t, err)
+
+			indexed := []string{}
+			for _, path := range result.Indexed {
+				assert.False(t, path.Promote)
+				indexed = append(indexed, path.Path)
+			}
+			skipped := []string{}
+			for _, path := range result.Skipped {
+				skipped = append(skipped, path.Path)
+			}
+			assert.Equal(t, testCase.wantIndexed, indexed)
+			assert.Equal(t, testCase.wantSkipped, skipped)
+			assert.NoError(t, ts.Mock().ExpectationsWereMet())
+		})
+	}
+}

pkg/modules/promote/promote.go

@@ -10,9 +10,12 @@ import (
 type Module interface {
 	ListPromotedPaths(ctx context.Context, filters promotetypes.ListPromotedPathsFilters) ([]promotetypes.PromotePath, error)
 	PromotePaths(ctx context.Context, paths ...*promotetypes.PromotePath) error
+
+	IndexMaterializedPaths(ctx context.Context, params promotetypes.IndexMaterializedPathsParams) (*promotetypes.IndexMaterializedPathsResult, error)
 }
 
 type Handler interface {
 	PromotePaths(w http.ResponseWriter, r *http.Request)
 	ListPromotedPaths(w http.ResponseWriter, r *http.Request)
+	IndexMaterializedPaths(w http.ResponseWriter, r *http.Request)
 }

pkg/querybuilder/family_condition.go

@@ -41,7 +41,7 @@ func SharedConditionForRead(
 	value any,
 	sb *sqlbuilder.SelectBuilder,
 ) (qbtypes.Compiled, error) {
-	condition, err := sharedOperator(ctx, q, storage, field, read.SQL, operator, value, sb)
+	condition, err := sharedOperator(ctx, q, storage, field, read, operator, value, sb)
 	if err != nil || condition == "" {
 		return qbtypes.Compiled{}, err
 	}
@@ -58,7 +58,7 @@ func sharedOperator(
 	q qbtypes.QueryInfo,
 	storage qbtypes.Storage,
 	field *telemetrytypes.LogicalField,
-	sql string,
+	read qbtypes.Read,
 	operator qbtypes.FilterOperator,
 	value any,
 	sb *sqlbuilder.SelectBuilder,
@@ -75,7 +75,7 @@ func sharedOperator(
 		}
 		conditions := make([]string, 0, len(values))
 		for _, item := range values {
-			condition, err := sharedOperator(ctx, q, storage, field, sql, itemOperator, item, sb)
+			condition, err := sharedOperator(ctx, q, storage, field, read, itemOperator, item, sb)
 			if err != nil {
 				return "", err
 			}
@@ -93,8 +93,11 @@ func sharedOperator(
 	}
 	// Coercion switches only on the data type, which every member shares, so
 	// the first member stands in for the field.
-	read, value := DataTypeCollisionHandledFieldName(field.Single(), value, sql, operator)
-	return OperatorCondition(ctx, q, storage, field, read, operator, value, sb)
+	sql, value := DataTypeCollisionHandledFieldName(field.Single(), value, read.SQL, operator)
+	if read.Float64 && sql == castFloatHack(read.SQL) {
+		sql = read.SQL
+	}
+	return OperatorCondition(ctx, q, storage, field, sql, operator, value, sb)
 }
 
 // OperatorCondition renders one operator over an already cast read. It is

pkg/signoz/module.go

@@ -155,7 +155,7 @@ func NewModules(
 		MetricsExplorer:      implmetricsexplorer.NewModule(telemetryStore, telemetryMetadataStore, cache, ruleStore, dashboard, fl, providerSettings, config.MetricsExplorer),
 		MetricReductionRule:  metricReductionRule,
 		InfraMonitoring:      implinframonitoring.NewModule(telemetryStore, telemetryMetadataStore, querier, fl, providerSettings, config.InfraMonitoring),
-		Promote:              implpromote.NewModule(telemetryMetadataStore, telemetryStore),
+		Promote:              implpromote.NewModule(telemetryMetadataStore, telemetryStore, providerSettings),
 		ServiceAccount:       serviceAccount,
 		ServiceAccountGetter: serviceAccountGetter,
 		LogsPipeline:         impllogspipeline.NewModule(sqlstore),

pkg/telemetrymetadata/body_json_metadata.go

@@ -14,6 +14,7 @@ import (
 	"github.com/SigNoz/signoz/pkg/errors"
 	"github.com/SigNoz/signoz/pkg/querybuilder"
 	"github.com/SigNoz/signoz/pkg/telemetryschema/logstelemetryschema"
+	"github.com/SigNoz/signoz/pkg/telemetryschema/tracestelemetryschema"
 	"github.com/SigNoz/signoz/pkg/types/ctxtypes"
 	"github.com/SigNoz/signoz/pkg/types/instrumentationtypes"
 	"github.com/SigNoz/signoz/pkg/types/promotetypes"
@@ -41,12 +42,18 @@ var logsBodyIndexLookup = promotetypes.NewLogsBodyTarget().JSONIndexLookup()
 // ClickHouse stores a `col.path::Type` skip index expression as CAST(col.path, 'Type').
 var simpleJSONSubColumnIndexExprRe = regexp.MustCompile(`^CAST\((?P<expr>[^()]+), '(?P<type>.+)'\)$`)
 
-// unfoldJSONSubColumnIndexExpr accepts both the folded (lower/assumeNotNull)
-// and the bare type-cast expression forms.
+// ClickHouse stores a traces number index expression as written.
+var jsonNumberSubColumnIndexExprRe = regexp.MustCompile("^" + strings.ReplaceAll(regexp.QuoteMeta(tracestelemetryschema.AttributeJSONNumberExpr("\x00")), "\x00", `([^()]+)`) + "$")
+
+// unfoldJSONSubColumnIndexExpr accepts the folded (lower/assumeNotNull), the
+// traces number and the bare type-cast expression forms.
 func unfoldJSONSubColumnIndexExpr(expr string) (string, string, error) {
 	if columnExpr, columnType, err := schemamigrator.UnfoldJSONSubColumnIndexExpr(expr); err == nil {
 		return columnExpr, columnType, nil
 	}
+	if matches := jsonNumberSubColumnIndexExprRe.FindStringSubmatch(expr); matches != nil && matches[1] == matches[2] {
+		return matches[1], telemetrytypes.Float64.StringValue(), nil
+	}
 	matches := simpleJSONSubColumnIndexExprRe.FindStringSubmatch(expr)
 	if matches == nil {
 		return "", "", errors.NewInvalidInputf(errors.CodeInvalidInput, "invalid expression: %s", expr)

pkg/telemetrymetadata/body_json_metadata_test.go

@@ -92,6 +92,17 @@ func TestUnfoldJSONSubColumnIndexExpr(t *testing.T) {
 			wantColumn: "attributes_promoted.status",
 			wantType:   "Int64",
 		},
+		{
+			name:       "TracesNumber",
+			expr:       "if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL)",
+			wantColumn: "attributes.`http.status_code`",
+			wantType:   "Float64",
+		},
+		{
+			name:    "TracesNumber_MismatchedPaths",
+			expr:    "if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.a)), accurateCastOrNull(attributes.b, 'Float64'), NULL)",
+			wantErr: true,
+		},
 		{
 			name:    "UnrecognizedForm",
 			expr:    "toString(attributes.status)",
@@ -137,9 +148,25 @@ func TestUnfoldJSONSubColumnIndexExprTracesRoundTrip(t *testing.T) {
 			name:         "PlainKey_UnquotedInStorage",
 			column:       "attributes_promoted",
 			path:         "status",
-			jsonDataType: "Int64",
-			wantExpr:     "attributes_promoted.`status`::Int64",
-			storedExpr:   "CAST(attributes_promoted.status, 'Int64')",
+			jsonDataType: "String",
+			wantExpr:     "attributes_promoted.`status`::String",
+			storedExpr:   "CAST(attributes_promoted.status, 'String')",
+		},
+		{
+			name:         "DottedNumber_BackticksPreservedInStorage",
+			column:       "attributes",
+			path:         "http.status_code",
+			jsonDataType: "Float64",
+			wantExpr:     "if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL)",
+			storedExpr:   "if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL)",
+		},
+		{
+			name:         "PlainNumber_UnquotedInStorage",
+			column:       "attributes_promoted",
+			path:         "latency",
+			jsonDataType: "Float64",
+			wantExpr:     "if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes_promoted.`latency`)), accurateCastOrNull(attributes_promoted.`latency`, 'Float64'), NULL)",
+			storedExpr:   "if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes_promoted.latency)), accurateCastOrNull(attributes_promoted.latency, 'Float64'), NULL)",
 		},
 	}
 

pkg/telemetrymetadata/metadata.go

@@ -118,6 +118,77 @@ func NewTelemetryMetaStore(
 	return t
 }
 
+// GetMaterializedKeys sets each key's Indexes to the skip indexes built on its column alone.
+func (t *telemetryMetaStore) GetMaterializedKeys(ctx context.Context, signal telemetrytypes.Signal) ([]*telemetrytypes.TelemetryFieldKey, error) {
+	var keys []*telemetrytypes.TelemetryFieldKey
+	var dbName, localTableName string
+	var err error
+	switch signal {
+	case telemetrytypes.SignalTraces:
+		keys, err = t.tracesTblStatementToFieldKeys(ctx)
+		dbName, localTableName = t.tracesDBName, tracestelemetryschema.SpanIndexV3LocalTableName
+	case telemetrytypes.SignalLogs:
+		keys, err = t.logsTblStatementToFieldKeys(ctx)
+		dbName, localTableName = t.logsDBName, logstelemetryschema.LogsV2LocalTableName
+	default:
+		return nil, errors.NewInvalidInputf(errors.CodeInvalidInput, "materialized keys are not supported for signal %s", signal.StringValue())
+	}
+	if err != nil {
+		return nil, err
+	}
+
+	indexes, err := t.listColumnSkipIndexes(ctx, signal, dbName, localTableName)
+	if err != nil {
+		return nil, err
+	}
+	for _, key := range keys {
+		for _, index := range indexes[strings.Trim(telemetrytypes.FieldKeyToMaterializedColumnName(key), "`")] {
+			index.Name = key.Name
+			index.FieldContext = key.FieldContext
+			index.FieldDataType = key.FieldDataType
+			key.Indexes = append(key.Indexes, index)
+		}
+	}
+
+	return keys, nil
+}
+
+// listColumnSkipIndexes leaves out indexes on expressions or multiple columns.
+func (t *telemetryMetaStore) listColumnSkipIndexes(ctx context.Context, signal telemetrytypes.Signal, dbName, localTableName string) (map[string][]telemetrytypes.TelemetryFieldKeySkipIndex, error) {
+	ctx = withTelemetryContext(ctx, signal, "listColumnSkipIndexes")
+	sb := sqlbuilder.Select("name", "type_full", "expr", "granularity").From(SkipIndexTableName)
+	sb.Where(sb.Equal("database", dbName), sb.Equal("table", localTableName))
+	query, args := sb.BuildWithFlavor(sqlbuilder.ClickHouse)
+
+	rows, err := t.telemetrystore.ClickhouseDB().Query(ctx, query, args...)
+	if err != nil {
+		return nil, errors.WrapInternalf(err, errors.CodeInternal, "failed to load skip indexes of %s.%s", dbName, localTableName)
+	}
+	defer rows.Close()
+
+	indexes := map[string][]telemetrytypes.TelemetryFieldKeySkipIndex{}
+	for rows.Next() {
+		var name, typeFull, expr string
+		var granularity uint64
+		if err := rows.Scan(&name, &typeFull, &expr, &granularity); err != nil {
+			return nil, errors.WrapInternalf(err, errors.CodeInternal, "failed to scan skip index")
+		}
+		column := strings.Trim(expr, "`")
+		if strings.ContainsAny(column, "`,( ") {
+			continue
+		}
+		indexes[column] = append(indexes[column], telemetrytypes.TelemetryFieldKeySkipIndex{
+			BaseColumn:      column,
+			IndexName:       name,
+			IndexType:       typeFull,
+			IndexExpression: expr,
+			Granularity:     int(granularity),
+		})
+	}
+
+	return indexes, rows.Err()
+}
+
 // tracesTblStatementToFieldKeys returns materialised attribute/resource/scope keys from the traces table.
 func (t *telemetryMetaStore) tracesTblStatementToFieldKeys(ctx context.Context) ([]*telemetrytypes.TelemetryFieldKey, error) {
 	ctx = ctxtypes.NewContextWithCommentVals(ctx, map[string]string{

pkg/telemetryschema/tracestelemetryschema/storage.go

@@ -330,6 +330,15 @@ func attributeColumnEvolutionRegistered(key *telemetrytypes.TelemetryFieldKey, c
 	return false
 }
 
+// AttributeJSONNumberExpr reads every numeric type of path as Float64, like
+// the attributes_number map, and anything else as NULL. Skip indexes on
+// numeric paths are built on this exact expression so filters match them;
+// the type set is a has() array because an index expression with IN is
+// never matched.
+func AttributeJSONNumberExpr(path string) string {
+	return fmt.Sprintf("if(has(['Int64', 'UInt64', 'Float64'], dynamicType(%s)), accurateCastOrNull(%s, 'Float64'), NULL)", path, path)
+}
+
 // attributeJSONValueExpr renders the value expression for a span attribute read from the JSON
 // column along with its per-type existence guard.
 // Numeric and bool gate a crash-safe accurateCastOrNull by dynamicType: the cast alone coerces
@@ -343,7 +352,7 @@ func attributeJSONValueExpr(path string, dataType telemetrytypes.FieldDataType)
 	case telemetrytypes.FieldDataTypeInt64,
 		telemetrytypes.FieldDataTypeFloat64,
 		telemetrytypes.FieldDataTypeNumber:
-		expr := fmt.Sprintf("if(dynamicType(%s) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(%s, 'Float64'), NULL)", path, path) // all numeric types to float64 like attributes_number map.
+		expr := AttributeJSONNumberExpr(path)
 		return expr, expr + " IS NOT NULL"
 	case telemetrytypes.FieldDataTypeBool:
 		expr := fmt.Sprintf("if(dynamicType(%s) = 'Bool', accurateCastOrNull(%s, 'Bool'), NULL)", path, path)
@@ -449,6 +458,24 @@ func absentReads(q qbtypes.QueryInfo, key *telemetrytypes.TelemetryFieldKey, col
 	return qbtypes.AlwaysPresent, nil
 }
 
+// readsJSONNumber reports whether key reads through AttributeJSONNumberExpr
+// alone, after evolution selection.
+func readsJSONNumber(q qbtypes.QueryInfo, key *telemetrytypes.TelemetryFieldKey, columns []*schema.Column) (bool, error) {
+	if key.FieldContext != telemetrytypes.FieldContextAttribute {
+		return false, nil
+	}
+	switch key.FieldDataType {
+	case telemetrytypes.FieldDataTypeInt64, telemetrytypes.FieldDataTypeFloat64, telemetrytypes.FieldDataTypeNumber:
+	default:
+		return false, nil
+	}
+	newColumns, _, err := qbtypes.SelectEvolutionsForColumns(columns, key.Evolutions, q.StartNs, q.EndNs)
+	if err != nil {
+		return false, err
+	}
+	return len(newColumns) == 1 && newColumns[0].Type.GetType() == schema.ColumnTypeEnumJSON, nil
+}
+
 // Read composes the bare read of one key with its membership test and what
 // an absent row reads. A span scope key is virtual. It is always present,
 // and its read is its name. A time column keeps its native type through the
@@ -473,7 +500,11 @@ func (m *storage) Read(ctx context.Context, q qbtypes.QueryInfo, key *telemetryt
 	if err != nil {
 		return qbtypes.Read{}, err
 	}
-	read := qbtypes.Read{SQL: sql, WhenAbsent: whenAbsent, KeepType: temporal}
+	jsonNumber, err := readsJSONNumber(q, key, columns)
+	if err != nil {
+		return qbtypes.Read{}, err
+	}
+	read := qbtypes.Read{SQL: sql, WhenAbsent: whenAbsent, KeepType: temporal, Float64: jsonNumber}
 	switch {
 	case key.FieldContext == telemetrytypes.FieldContextScope:
 		read.Presence = scopeJSONExistsExpression(key, sql, true)

pkg/telemetryschema/tracestelemetryschema/storage_attributes_json_test.go

@@ -57,9 +57,9 @@ func TestFieldForAttributeJSONEvolution(t *testing.T) {
 		{"string after -> json", telemetrytypes.FieldDataTypeString, attrWindowAfter, "attributes.`user.id`::String"},
 		{"string straddle -> dual", telemetrytypes.FieldDataTypeString, attrWindowStraddle, "multiIf(attributes.`user.id` IS NOT NULL, attributes.`user.id`::String, mapContains(attributes_string, 'user.id'), attributes_string['user.id'], NULL)"},
 		{"number before -> map", telemetrytypes.FieldDataTypeNumber, attrWindowBefore, "attributes_number['user.id']"},
-		{"number after -> json", telemetrytypes.FieldDataTypeNumber, attrWindowAfter, "if(dynamicType(attributes.`user.id`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`user.id`, 'Float64'), NULL)"},
-		{"number straddle -> dual", telemetrytypes.FieldDataTypeNumber, attrWindowStraddle, "multiIf(if(dynamicType(attributes.`user.id`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`user.id`, 'Float64'), NULL) IS NOT NULL, if(dynamicType(attributes.`user.id`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`user.id`, 'Float64'), NULL), mapContains(attributes_number, 'user.id'), attributes_number['user.id'], NULL)"},
-		{"int64 after -> json", telemetrytypes.FieldDataTypeInt64, attrWindowAfter, "if(dynamicType(attributes.`user.id`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`user.id`, 'Float64'), NULL)"},
+		{"number after -> json", telemetrytypes.FieldDataTypeNumber, attrWindowAfter, "if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`user.id`)), accurateCastOrNull(attributes.`user.id`, 'Float64'), NULL)"},
+		{"number straddle -> dual", telemetrytypes.FieldDataTypeNumber, attrWindowStraddle, "multiIf(if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`user.id`)), accurateCastOrNull(attributes.`user.id`, 'Float64'), NULL) IS NOT NULL, if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`user.id`)), accurateCastOrNull(attributes.`user.id`, 'Float64'), NULL), mapContains(attributes_number, 'user.id'), attributes_number['user.id'], NULL)"},
+		{"int64 after -> json", telemetrytypes.FieldDataTypeInt64, attrWindowAfter, "if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`user.id`)), accurateCastOrNull(attributes.`user.id`, 'Float64'), NULL)"},
 		{"bool before -> map", telemetrytypes.FieldDataTypeBool, attrWindowBefore, "attributes_bool['user.id']"},
 		{"bool after -> json", telemetrytypes.FieldDataTypeBool, attrWindowAfter, "if(dynamicType(attributes.`user.id`) = 'Bool', accurateCastOrNull(attributes.`user.id`, 'Bool'), NULL)"},
 		{"bool straddle -> dual", telemetrytypes.FieldDataTypeBool, attrWindowStraddle, "multiIf(if(dynamicType(attributes.`user.id`) = 'Bool', accurateCastOrNull(attributes.`user.id`, 'Bool'), NULL) IS NOT NULL, if(dynamicType(attributes.`user.id`) = 'Bool', accurateCastOrNull(attributes.`user.id`, 'Bool'), NULL), mapContains(attributes_bool, 'user.id'), attributes_bool['user.id'], NULL)"},
@@ -180,7 +180,7 @@ func TestConditionForAttributeJSON(t *testing.T) {
 			name:     "greater than number",
 			key:      attrKey("http.status_code", telemetrytypes.FieldDataTypeInt64, evo),
 			operator: qbtypes.FilterOperatorGreaterThan, value: float64(200),
-			expected: "toFloat64(if(dynamicType(attributes.`http.status_code`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL)) > ?",
+			expected: "if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL) > ?",
 		},
 		{
 			name:     "ilike string",
@@ -216,7 +216,31 @@ func TestConditionForAttributeJSON(t *testing.T) {
 			name:     "between number",
 			key:      attrKey("latency", telemetrytypes.FieldDataTypeNumber, evo),
 			operator: qbtypes.FilterOperatorBetween, value: []any{float64(1), float64(9)},
-			expected: "toFloat64(if(dynamicType(attributes.`latency`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`latency`, 'Float64'), NULL)) BETWEEN ? AND ?",
+			expected: "if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`latency`)), accurateCastOrNull(attributes.`latency`, 'Float64'), NULL) BETWEEN ? AND ?",
+		},
+		{
+			name:     "in number",
+			key:      attrKey("http.status_code", telemetrytypes.FieldDataTypeNumber, evo),
+			operator: qbtypes.FilterOperatorIn, value: []any{float64(200), float64(404)},
+			expected: "(if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL) = ? OR if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL) = ?)",
+		},
+		{
+			name:     "greater than number with numeric string",
+			key:      attrKey("http.status_code", telemetrytypes.FieldDataTypeNumber, evo),
+			operator: qbtypes.FilterOperatorGreaterThan, value: "200",
+			expected: "if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL) > ?",
+		},
+		{
+			name:     "equal number with non-numeric string keeps string cast",
+			key:      attrKey("http.status_code", telemetrytypes.FieldDataTypeNumber, evo),
+			operator: qbtypes.FilterOperatorEqual, value: "ok",
+			expected: "toString(if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL)) = ?",
+		},
+		{
+			name:     "equal number with bool keeps string cast",
+			key:      attrKey("http.status_code", telemetrytypes.FieldDataTypeNumber, evo),
+			operator: qbtypes.FilterOperatorEqual, value: true,
+			expected: "toString(if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL)) = ?",
 		},
 	}
 
@@ -271,7 +295,7 @@ func TestColumnExpressionForAttributeJSON(t *testing.T) {
 		key := attrKey("latency", telemetrytypes.FieldDataTypeNumber, evo)
 		got, err := querybuilder.ResolveColumn(ctx, qbtypes.QueryInfo{StartNs: attrWindowAfter[0], EndNs: attrWindowAfter[1], TraceAttrsJSONOn: true}, storage, &key, telemetrytypes.FieldDataTypeFloat64, nil)
 		require.NoError(t, err)
-		assert.Equal(t, "multiIf(if(dynamicType(attributes.`latency`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`latency`, 'Float64'), NULL) IS NOT NULL, toFloat64(if(dynamicType(attributes.`latency`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`latency`, 'Float64'), NULL)), mapContains(attributes_number, 'attribute.latency'), toFloat64(attributes_number['attribute.latency']), NULL)", got)
+		assert.Equal(t, "multiIf(if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`latency`)), accurateCastOrNull(attributes.`latency`, 'Float64'), NULL) IS NOT NULL, toFloat64(if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`latency`)), accurateCastOrNull(attributes.`latency`, 'Float64'), NULL)), mapContains(attributes_number, 'attribute.latency'), toFloat64(attributes_number['attribute.latency']), NULL)", got)
 	})
 }
 
@@ -314,7 +338,7 @@ func TestConditionForAttributeJSONTypeCollision(t *testing.T) {
 	sb.Where(sb.Or(conds...))
 	sql, _ := sb.BuildWithFlavor(sqlbuilder.ClickHouse)
 	assert.Contains(t, sql, "toFloat64OrNull(attributes.`http.status_code`::String) = ?")
-	assert.Contains(t, sql, "toFloat64(if(dynamicType(attributes.`http.status_code`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL)) = ?")
+	assert.Contains(t, sql, "if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL) = ?")
 	assert.Contains(t, sql, "attributes.`http.status_code` IS NOT NULL")
 	assert.NotEmpty(t, warnings, "a colliding name must surface the ambiguity warning")
 }
@@ -339,7 +363,7 @@ func TestColumnExpressionForAttributeJSONTypeCollision(t *testing.T) {
 	got, err := querybuilder.ResolveColumn(ctx, qbtypes.QueryInfo{StartNs: attrWindowAfter[0], EndNs: attrWindowAfter[1], TraceAttrsJSONOn: true}, storage, &ref, telemetrytypes.FieldDataTypeString, fieldKeys)
 	require.NoError(t, err)
 	assert.Equal(t,
-		"multiIf(attributes.`http.status_code` IS NOT NULL, attributes.`http.status_code`::String, if(dynamicType(attributes.`http.status_code`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL) IS NOT NULL, toString(if(dynamicType(attributes.`http.status_code`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL)), NULL)",
+		"multiIf(attributes.`http.status_code` IS NOT NULL, attributes.`http.status_code`::String, if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL) IS NOT NULL, toString(if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL)), NULL)",
 		got)
 }
 
@@ -363,7 +387,7 @@ func TestColumnExpressionForAttributeJSONTypeCollisionNumericAgg(t *testing.T) {
 	got, err := querybuilder.ResolveColumn(ctx, qbtypes.QueryInfo{StartNs: attrWindowAfter[0], EndNs: attrWindowAfter[1], TraceAttrsJSONOn: true}, storage, &ref, telemetrytypes.FieldDataTypeFloat64, fieldKeys)
 	require.NoError(t, err)
 	assert.Equal(t,
-		"multiIf(if(dynamicType(attributes.`http.status_code`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL) IS NOT NULL, toFloat64(if(dynamicType(attributes.`http.status_code`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL)), attributes.`http.status_code` IS NOT NULL, toFloat64OrNull(attributes.`http.status_code`::String), NULL)",
+		"multiIf(if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL) IS NOT NULL, toFloat64(if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL)), attributes.`http.status_code` IS NOT NULL, toFloat64OrNull(attributes.`http.status_code`::String), NULL)",
 		got)
 }
 
@@ -432,7 +456,7 @@ func TestConditionForAttributeJSONNegativeOperatorParity(t *testing.T) {
 	t.Run("not equal number after -> NULL folded to 0", func(t *testing.T) {
 		key := attrKey("http.status_code", telemetrytypes.FieldDataTypeInt64, evo)
 		sql := build(t, key, attrWindowAfter, qbtypes.FilterOperatorNotEqual, float64(200))
-		assert.Contains(t, sql, "toFloat64(ifNull(if(dynamicType(attributes.`http.status_code`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL), 0)) <> ?")
+		assert.Contains(t, sql, "ifNull(if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL), 0) <> ?")
 	})
 
 	t.Run("not equal bool after -> NULL folded to false", func(t *testing.T) {
@@ -447,27 +471,27 @@ func TestConditionForAttributeJSONNegativeOperatorParity(t *testing.T) {
 	t.Run("not equal number after, string value -> string cast over folded read, no type mismatch", func(t *testing.T) {
 		key := attrKey("http.status_code", telemetrytypes.FieldDataTypeInt64, evo)
 		sql := build(t, key, attrWindowAfter, qbtypes.FilterOperatorNotEqual, "teapot")
-		assert.Contains(t, sql, "toString(ifNull(if(dynamicType(attributes.`http.status_code`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL), 0)) <> ?")
+		assert.Contains(t, sql, "toString(ifNull(if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL), 0)) <> ?")
 		assert.NotContains(t, sql, "ifNull(toString(")
 	})
 
 	t.Run("equal number after -> not folded, exists guard excludes absent", func(t *testing.T) {
 		key := attrKey("http.status_code", telemetrytypes.FieldDataTypeInt64, evo)
 		sql := build(t, key, attrWindowAfter, qbtypes.FilterOperatorEqual, float64(0))
-		assert.Contains(t, sql, "toFloat64(if(dynamicType(attributes.`http.status_code`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL)) = ?")
+		assert.Contains(t, sql, "if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL) = ?")
 		assert.NotContains(t, sql, "ifNull")
 	})
 
 	t.Run("not in number after -> each operand folded to 0", func(t *testing.T) {
 		key := attrKey("http.status_code", telemetrytypes.FieldDataTypeInt64, evo)
 		sql := build(t, key, attrWindowAfter, qbtypes.FilterOperatorNotIn, []any{float64(200), float64(404)})
-		assert.Contains(t, sql, "(toFloat64(ifNull(if(dynamicType(attributes.`http.status_code`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL), 0)) <> ? AND toFloat64(ifNull(if(dynamicType(attributes.`http.status_code`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL), 0)) <> ?)")
+		assert.Contains(t, sql, "(ifNull(if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL), 0) <> ? AND ifNull(if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL), 0) <> ?)")
 	})
 
 	t.Run("not equal number straddle -> whole multiIf folded", func(t *testing.T) {
 		key := attrKey("http.status_code", telemetrytypes.FieldDataTypeInt64, evo)
 		sql := build(t, key, attrWindowStraddle, qbtypes.FilterOperatorNotEqual, float64(200))
-		assert.Contains(t, sql, "toFloat64(ifNull(multiIf(if(dynamicType(attributes.`http.status_code`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL) IS NOT NULL, if(dynamicType(attributes.`http.status_code`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL), mapContains(attributes_number, 'http.status_code'), attributes_number['http.status_code'], NULL), 0)) <> ?")
+		assert.Contains(t, sql, "toFloat64(ifNull(multiIf(if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL) IS NOT NULL, if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL), mapContains(attributes_number, 'http.status_code'), attributes_number['http.status_code'], NULL), 0)) <> ?")
 	})
 
 	t.Run("not equal number before -> harmless fold over the map read", func(t *testing.T) {
@@ -509,7 +533,7 @@ func TestConditionForAttributeJSONStraddleAbsentKeyExclusion(t *testing.T) {
 		return sql
 	}
 
-	guard := "multiIf(if(dynamicType(attributes.`http.status_code`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL) IS NOT NULL, if(dynamicType(attributes.`http.status_code`) IN ('Int64', 'UInt64', 'Float64'), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL), mapContains(attributes_number, 'http.status_code'), attributes_number['http.status_code'], NULL) IS NOT NULL"
+	guard := "multiIf(if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL) IS NOT NULL, if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL), mapContains(attributes_number, 'http.status_code'), attributes_number['http.status_code'], NULL) IS NOT NULL"
 
 	t.Run("equal zero takes no guard: the absent read is NULL", func(t *testing.T) {
 		key := attrKey("http.status_code", telemetrytypes.FieldDataTypeInt64, evo)

pkg/types/promotetypes/materialized.go

@@ -0,0 +1,47 @@
+package promotetypes
+
+import (
+	"github.com/SigNoz/signoz/pkg/errors"
+	"github.com/SigNoz/signoz/pkg/types/telemetrytypes"
+)
+
+const (
+	DefaultMaterializedIndexType        = "bloom_filter(0.01)"
+	DefaultMaterializedIndexGranularity = 64
+)
+
+type IndexMaterializedPathsResult struct {
+	Indexed []*PromotePath `json:"indexed" required:"true"`
+	Skipped []*PromotePath `json:"skipped" required:"true"`
+}
+
+type IndexMaterializedPathsParams struct {
+	Signal string `query:"signal" json:"signal" required:"true"`
+	DryRun bool   `query:"dryRun" json:"dryRun"`
+}
+
+func (p *IndexMaterializedPathsParams) Validate() error {
+	if p.Signal == "" {
+		return errors.Newf(errors.TypeInvalidInput, errors.CodeInvalidInput, "signal is required")
+	}
+	_, err := p.Target()
+	return err
+}
+
+func (p *IndexMaterializedPathsParams) Target() (Target, error) {
+	return NewTargetFromText(p.Signal, telemetrytypes.FieldContextAttribute.StringValue())
+}
+
+func NewMaterializedPromotePath(target Target, key *telemetrytypes.TelemetryFieldKey) (*PromotePath, error) {
+	path := &PromotePath{
+		Signal:  target.Entry.Signal.StringValue(),
+		Context: target.Entry.FieldContext.StringValue(),
+		Path:    key.Name,
+		Promote: false,
+		Indexes: []WrappedIndex{{FieldDataType: key.FieldDataType, Type: DefaultMaterializedIndexType, Granularity: DefaultMaterializedIndexGranularity}},
+	}
+	if err := path.ValidateAndSetDefaults(target); err != nil {
+		return nil, err
+	}
+	return path, nil
+}

pkg/types/promotetypes/materialized_test.go

@@ -0,0 +1,82 @@
+package promotetypes
+
+import (
+	"testing"
+
+	"github.com/stretchr/testify/assert"
+	"github.com/stretchr/testify/require"
+
+	"github.com/SigNoz/signoz/pkg/types/telemetrytypes"
+)
+
+func TestNewMaterializedPromotePath(t *testing.T) {
+	testCases := []struct {
+		name      string
+		key       *telemetrytypes.TelemetryFieldKey
+		wantIndex WrappedIndex
+		wantErr   bool
+	}{
+		{
+			name:      "StringPath_StringIndex",
+			key:       &telemetrytypes.TelemetryFieldKey{Name: "user.id", FieldDataType: telemetrytypes.FieldDataTypeString},
+			wantIndex: WrappedIndex{FieldDataType: telemetrytypes.FieldDataTypeString, Type: "bloom_filter(0.01)", Granularity: 64, JSONDataType: telemetrytypes.String},
+		},
+		{
+			name:      "NumberPath_Float64Index",
+			key:       &telemetrytypes.TelemetryFieldKey{Name: "retry.count", FieldDataType: telemetrytypes.FieldDataTypeNumber},
+			wantIndex: WrappedIndex{FieldDataType: telemetrytypes.FieldDataTypeNumber, Type: "bloom_filter(0.01)", Granularity: 64, JSONDataType: telemetrytypes.Float64},
+		},
+		{
+			name:    "BoolPath_Rejected",
+			key:     &telemetrytypes.TelemetryFieldKey{Name: "user.active", FieldDataType: telemetrytypes.FieldDataTypeBool},
+			wantErr: true,
+		},
+		{
+			name:    "CardinalPath_Rejected",
+			key:     &telemetrytypes.TelemetryFieldKey{Name: "session.550e8400-e29b-41d4-a716-446655440000", FieldDataType: telemetrytypes.FieldDataTypeString},
+			wantErr: true,
+		},
+	}
+
+	for _, testCase := range testCases {
+		t.Run(testCase.name, func(t *testing.T) {
+			path, err := NewMaterializedPromotePath(NewTracesAttributesTarget(), testCase.key)
+			if testCase.wantErr {
+				assert.Error(t, err)
+				return
+			}
+			require.NoError(t, err)
+			assert.Equal(t, &PromotePath{
+				Signal:  "traces",
+				Context: "attribute",
+				Path:    testCase.key.Name,
+				Promote: false,
+				Indexes: []WrappedIndex{testCase.wantIndex},
+			}, path)
+		})
+	}
+}
+
+func TestIndexMaterializedPathsParamsValidate(t *testing.T) {
+	testCases := []struct {
+		name    string
+		params  IndexMaterializedPathsParams
+		wantErr bool
+	}{
+		{name: "TracesSignal_Valid", params: IndexMaterializedPathsParams{Signal: "traces", DryRun: true}},
+		{name: "MissingSignal_Rejected", params: IndexMaterializedPathsParams{}, wantErr: true},
+		{name: "UnknownSignal_Rejected", params: IndexMaterializedPathsParams{Signal: "events"}, wantErr: true},
+		{name: "LogsSignal_NoAttributeTarget_Rejected", params: IndexMaterializedPathsParams{Signal: "logs"}, wantErr: true},
+	}
+
+	for _, testCase := range testCases {
+		t.Run(testCase.name, func(t *testing.T) {
+			err := testCase.params.Validate()
+			if testCase.wantErr {
+				assert.Error(t, err)
+				return
+			}
+			assert.NoError(t, err)
+		})
+	}
+}

pkg/types/promotetypes/resource.go

@@ -46,6 +46,18 @@ func ListPromotedPathsResources(ec coretypes.ExtractorContext) ([]coretypes.Reso
 	return fieldResources(signals)
 }
 
+func IndexMaterializedPathsResources(ec coretypes.ExtractorContext) ([]coretypes.ResourceWithID, error) {
+	signalText := ""
+	if ec.Request != nil {
+		signalText = ec.Request.URL.Query().Get("signal")
+	}
+	signal, ok := telemetrytypes.SignalFromText(signalText)
+	if !ok {
+		return nil, errors.NewInvalidInputf(errors.CodeInvalidInput, "invalid signal: %s", signalText)
+	}
+	return fieldResources([]telemetrytypes.Signal{signal})
+}
+
 func fieldResources(signals []telemetrytypes.Signal) ([]coretypes.ResourceWithID, error) {
 	resources := make([]coretypes.ResourceWithID, 0, len(signals))
 	seen := make(map[string]struct{})

pkg/types/promotetypes/resource_test.go

@@ -112,3 +112,46 @@ func TestListPromotedPathsResources(t *testing.T) {
 		})
 	}
 }
+
+func TestIndexMaterializedPathsResources(t *testing.T) {
+	testCases := []struct {
+		name    string
+		query   string
+		want    []string
+		wantErr bool
+	}{
+		{
+			name:  "TracesSignal_TracesField",
+			query: "?signal=traces",
+			want:  []string{"traces-field"},
+		},
+		{
+			name:  "LogsSignal_LogsField",
+			query: "?signal=logs",
+			want:  []string{"logs-field"},
+		},
+		{
+			name:    "MissingSignal_Rejected",
+			query:   "",
+			wantErr: true,
+		},
+		{
+			name:    "UnsupportedSignal_Rejected",
+			query:   "?signal=metrics",
+			wantErr: true,
+		},
+	}
+
+	for _, testCase := range testCases {
+		t.Run(testCase.name, func(t *testing.T) {
+			request := httptest.NewRequest("POST", "/api/v1/promoted_paths/materialized"+testCase.query, nil)
+			resources, err := IndexMaterializedPathsResources(coretypes.ExtractorContext{Request: request})
+			if testCase.wantErr {
+				assert.Error(t, err)
+				return
+			}
+			require.NoError(t, err)
+			assert.Equal(t, testCase.want, resourceKinds(resources))
+		})
+	}
+}

pkg/types/promotetypes/target.go

@@ -27,16 +27,28 @@ func (t Target) BaseColumnPrefix() string { return t.BaseColumn + "." }
 func (t Target) PromotedColumnPrefix() string { return t.PromotedColumn() + "." }
 
 // IndexExpression folds logs strings to lower case over assumeNotNull for
-// case-insensitive LIKE searches; traces indexes are a bare type cast.
+// case-insensitive LIKE searches; traces numbers use the querier's Float64
+// read, other traces indexes are a bare type cast.
 func (t Target) IndexExpression(column, path, jsonDataType string) string {
-	switch t.Entry.Signal {
-	case telemetrytypes.SignalLogs:
+	switch {
+	case t.Entry.Signal == telemetrytypes.SignalLogs:
 		return schemamigrator.JSONSubColumnIndexExpr(column, path, jsonDataType)
+	case jsonDataType == telemetrytypes.Int64.StringValue(), jsonDataType == telemetrytypes.Float64.StringValue():
+		return tracestelemetryschema.AttributeJSONNumberExpr(jsonSubColumn(column, path))
 	default:
 		return simpleJSONSubColumnIndexExpr(column, path, jsonDataType)
 	}
 }
 
+// IndexDataType folds traces Int64 into Float64, the one type the querier
+// reads every traces number as.
+func (t Target) IndexDataType(jsonDataType telemetrytypes.JSONDataType) telemetrytypes.JSONDataType {
+	if t.Entry.Signal != telemetrytypes.SignalLogs && jsonDataType == telemetrytypes.Int64 {
+		return telemetrytypes.Float64
+	}
+	return jsonDataType
+}
+
 func (t Target) JSONIndexLookup() telemetrytypes.JSONIndexLookup {
 	return telemetrytypes.JSONIndexLookup{
 		Signal:               t.Entry.Signal,
@@ -126,7 +138,11 @@ func TargetFor(signal telemetrytypes.Signal, context telemetrytypes.FieldContext
 // the cast unwraps the Nullable the sub-column access returns, which bloom
 // filter indexes reject.
 func simpleJSONSubColumnIndexExpr(column, path, jsonDataType string) string {
-	return fmt.Sprintf("%s.%s::%s", column, clickhousesql.Identifier(path), jsonDataType)
+	return fmt.Sprintf("%s::%s", jsonSubColumn(column, path), jsonDataType)
+}
+
+func jsonSubColumn(column, path string) string {
+	return fmt.Sprintf("%s.%s", column, clickhousesql.Identifier(path))
 }
 
 // reservedPathPrefix returns the target prefix path carries, if any: the base

pkg/types/promotetypes/target_test.go

@@ -3,6 +3,7 @@ package promotetypes
 import (
 	"testing"
 
+	"github.com/SigNoz/signoz/pkg/types/telemetrytypes"
 	"github.com/stretchr/testify/assert"
 )
 
@@ -47,6 +48,22 @@ func TestIndexExpression(t *testing.T) {
 			jsonDataType: "String",
 			want:         "attributes.`user-name`::String",
 		},
+		{
+			name:         "TracesFloat64_QuerierNumberRead",
+			target:       NewTracesAttributesTarget(),
+			column:       "attributes",
+			path:         "http.status_code",
+			jsonDataType: "Float64",
+			want:         "if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`http.status_code`)), accurateCastOrNull(attributes.`http.status_code`, 'Float64'), NULL)",
+		},
+		{
+			name:         "TracesInt64_QuerierNumberRead",
+			target:       NewTracesAttributesTarget(),
+			column:       "attributes_promoted",
+			path:         "retry.count",
+			jsonDataType: "Int64",
+			want:         "if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes_promoted.`retry.count`)), accurateCastOrNull(attributes_promoted.`retry.count`, 'Float64'), NULL)",
+		},
 	}
 
 	for _, testCase := range testCases {
@@ -55,3 +72,22 @@ func TestIndexExpression(t *testing.T) {
 		})
 	}
 }
+
+func TestIndexDataType(t *testing.T) {
+	testCases := []struct {
+		name         string
+		target       Target
+		jsonDataType telemetrytypes.JSONDataType
+		want         telemetrytypes.JSONDataType
+	}{
+		{name: "TracesInt64_Float64", target: NewTracesAttributesTarget(), jsonDataType: telemetrytypes.Int64, want: telemetrytypes.Float64},
+		{name: "TracesString_Unchanged", target: NewTracesAttributesTarget(), jsonDataType: telemetrytypes.String, want: telemetrytypes.String},
+		{name: "LogsInt64_Unchanged", target: NewLogsBodyTarget(), jsonDataType: telemetrytypes.Int64, want: telemetrytypes.Int64},
+	}
+
+	for _, testCase := range testCases {
+		t.Run(testCase.name, func(t *testing.T) {
+			assert.Equal(t, testCase.want, testCase.target.IndexDataType(testCase.jsonDataType))
+		})
+	}
+}

pkg/types/promotetypes/types.go

@@ -133,7 +133,7 @@ func (i *PromotePath) ValidateAndSetDefaults(target Target) error {
 			return errors.Newf(errors.TypeInvalidInput, errors.CodeInvalidInput, "index is not supported for column type: %s", index.FieldDataType)
 		}
 
-		i.Indexes[idx].JSONDataType = jsonDataType
+		i.Indexes[idx].JSONDataType = target.IndexDataType(jsonDataType)
 	}
 
 	return nil

pkg/types/querybuildertypes/querybuildertypesv5/qb.go

@@ -98,6 +98,10 @@ type Read struct {
 	// native type alone. A time column would collapse to seconds. A metrics
 	// label already reads as String.
 	KeepType bool
+	// Float64: SQL already reads as Float64, so a filter compares it with a
+	// number bare instead of through toFloat64, which hides it from a skip
+	// index on SQL.
+	Float64 bool
 	// FilterOnly: the key filters but cannot be selected or grouped. Today
 	// that is a body JSON path when the JSON body flag is off: the legacy
 	// string body has no column read for a path. A select field, group by,

pkg/types/telemetrytypes/store.go

@@ -45,6 +45,9 @@ type MetadataStore interface {
 	// rows templated by entry; FieldName and ReleaseTime are set per path.
 	PromotePaths(ctx context.Context, entry EvolutionEntry, paths ...string) error
 
+	// GetMaterializedKeys lists the keys materialized as dedicated columns.
+	GetMaterializedKeys(ctx context.Context, signal Signal) ([]*TelemetryFieldKey, error)
+
 	// GetFirstSeenFromMetricMetadata gets the first seen timestamp for a metric metadata lookup key.
 	GetFirstSeenFromMetricMetadata(ctx context.Context, lookupKeys []MetricMetadataLookupKey) (map[MetricMetadataLookupKey]int64, error)
 

pkg/types/telemetrytypes/telemetrytypestest/metadata_store.go

@@ -20,6 +20,7 @@ type MockMetadataStore struct {
 	ReducedMap                 map[string]bool
 	PromotedPathsMap           map[telemetrytypes.Signal]map[string]bool
 	LogsJSONIndexes            []telemetrytypes.TelemetryFieldKeySkipIndex
+	MaterializedKeys           []*telemetrytypes.TelemetryFieldKey
 	ColumnEvolutionMetadataMap map[string][]*telemetrytypes.EvolutionEntry
 	LookupKeysMap              map[telemetrytypes.MetricMetadataLookupKey]int64
 	// StaticFields holds signal-specific intrinsic field definitions (e.g. logstelemetryschema.IntrinsicFields).
@@ -376,6 +377,16 @@ func (m *MockMetadataStore) GetPromotedPaths(_ context.Context, entry telemetryt
 	return m.PromotedPathsMap[entry.Signal], nil
 }
 
+func (m *MockMetadataStore) GetMaterializedKeys(_ context.Context, signal telemetrytypes.Signal) ([]*telemetrytypes.TelemetryFieldKey, error) {
+	keys := []*telemetrytypes.TelemetryFieldKey{}
+	for _, key := range m.MaterializedKeys {
+		if key.Signal == signal {
+			keys = append(keys, key)
+		}
+	}
+	return keys, nil
+}
+
 // ListJSONIndexes narrows the stored indexes to the lookup's field context like the real query.
 func (m *MockMetadataStore) ListJSONIndexes(ctx context.Context, lookup telemetrytypes.JSONIndexLookup, filters ...string) ([]telemetrytypes.TelemetryFieldKeySkipIndex, error) {
 	indexes := []telemetrytypes.TelemetryFieldKeySkipIndex{}

tests/conftest.py

@@ -32,6 +32,7 @@ pytest_plugins = [
     "fixtures.alerts",
     "fixtures.cloudintegrations",
     "fixtures.jsontypes",
+    "fixtures.materialized",
     "fixtures.seeder",
     "fixtures.serviceaccount",
     "fixtures.role",

tests/fixtures/materialized.py

@@ -0,0 +1,44 @@
+from collections.abc import Callable, Generator
+
+import pytest
+
+from fixtures import types
+
+# signal -> (database, local table, distributed table)
+MATERIALIZED_TABLES = {
+    "traces": ("signoz_traces", "signoz_index_v3", "distributed_signoz_index_v3"),
+}
+
+# data type -> (column type, attribute map)
+MATERIALIZED_COLUMN_TYPES = {
+    "string": ("LowCardinality(String)", "attributes_string"),
+    "number": ("Float64", "attributes_number"),
+}
+
+
+@pytest.fixture(name="materialize_attribute", scope="function")
+def materialize_attribute(signoz: types.SigNoz) -> Generator[Callable[..., str]]:
+    """Teardown drops the columns and every index whose expression mentions a materialized key."""
+    cluster = signoz.telemetrystore.env["SIGNOZ_TELEMETRYSTORE_CLICKHOUSE_CLUSTER"]
+    created: list[tuple[str, str, str]] = []
+
+    def materialize(signal: str, key: str, indexed: bool, data_type: str = "string") -> str:
+        database, local_table, distributed_table = MATERIALIZED_TABLES[signal]
+        column_type, attribute_map = MATERIALIZED_COLUMN_TYPES[data_type]
+        column = f"attribute_{data_type}_{key.replace('.', '$$')}"
+        for table in (local_table, distributed_table):
+            signoz.telemetrystore.conn.query(f"ALTER TABLE {database}.{table} ON CLUSTER '{cluster}' ADD COLUMN IF NOT EXISTS `{column}` {column_type} DEFAULT {attribute_map}['{key}']")
+        if indexed:
+            signoz.telemetrystore.conn.query(f"ALTER TABLE {database}.{local_table} ON CLUSTER '{cluster}' ADD INDEX IF NOT EXISTS `{column}_idx` `{column}` TYPE bloom_filter(0.01) GRANULARITY 64")
+        created.append((signal, key, column))
+        return column
+
+    yield materialize
+
+    for signal, key, column in created:
+        database, local_table, distributed_table = MATERIALIZED_TABLES[signal]
+        indexes = signoz.telemetrystore.conn.query(f"SELECT name FROM system.data_skipping_indices WHERE database = '{database}' AND table = '{local_table}' AND (expr LIKE '%{key}%' OR expr LIKE '%{column}%')").result_rows
+        for (index_name,) in indexes:
+            signoz.telemetrystore.conn.query(f"ALTER TABLE {database}.{local_table} ON CLUSTER '{cluster}' DROP INDEX IF EXISTS `{index_name}`")
+        for table in (local_table, distributed_table):
+            signoz.telemetrystore.conn.query(f"ALTER TABLE {database}.{table} ON CLUSTER '{cluster}' DROP COLUMN IF EXISTS `{column}`")

tests/integration/tests/promote/02_materialized.py

@@ -0,0 +1,101 @@
+from collections.abc import Callable
+from http import HTTPStatus
+
+import requests
+
+from fixtures import types
+from fixtures.auth import USER_ADMIN_EMAIL, USER_ADMIN_PASSWORD
+from fixtures.traces import Traces
+
+PROMOTED_PATHS = "/api/v1/promoted_paths"
+MATERIALIZED_PATH = "/api/v1/promoted_paths/materialized"
+PROBE_INDEX_EXPRS_QUERY = "SELECT expr FROM system.data_skipping_indices WHERE database = 'signoz_traces' AND table = 'signoz_index_v3' AND expr LIKE 'CAST(%materialized.%probe%'"
+
+
+def test_index_materialized_paths(
+    signoz: types.SigNoz,
+    create_user_admin: types.Operation,  # pylint: disable=unused-argument
+    get_token: Callable[[str, str], str],
+    materialize_attribute: Callable[..., str],
+) -> None:
+    materialize_attribute("traces", "materialized.probe", True)
+    materialize_attribute("traces", "materialized.unindexed_probe", False)
+    token = get_token(USER_ADMIN_EMAIL, USER_ADMIN_PASSWORD)
+
+    resp = requests.post(
+        signoz.self.host_configs["8080"].get(MATERIALIZED_PATH),
+        params={"signal": "traces", "dryRun": "true"},
+        headers={"Authorization": f"Bearer {token}"},
+        timeout=30,
+    )
+    assert resp.status_code == HTTPStatus.OK, resp.text
+    indexed = {path["path"]: path["indexes"] for path in resp.json()["data"]["indexed"]}
+    assert indexed["materialized.probe"] == [{"fieldDataType": "string", "type": "bloom_filter(0.01)", "granularity": 64}]
+    assert "materialized.unindexed_probe" not in indexed
+    assert not signoz.telemetrystore.conn.query(PROBE_INDEX_EXPRS_QUERY).result_rows
+
+    resp = requests.post(
+        signoz.self.host_configs["8080"].get(MATERIALIZED_PATH),
+        params={"signal": "logs"},
+        headers={"Authorization": f"Bearer {token}"},
+        timeout=30,
+    )
+    assert resp.status_code == HTTPStatus.BAD_REQUEST, resp.text
+    assert not signoz.telemetrystore.conn.query(PROBE_INDEX_EXPRS_QUERY).result_rows
+
+    resp = requests.post(
+        signoz.self.host_configs["8080"].get(MATERIALIZED_PATH),
+        params={"signal": "traces"},
+        headers={"Authorization": f"Bearer {token}"},
+        timeout=30,
+    )
+    assert resp.status_code == HTTPStatus.OK, resp.text
+    assert "materialized.probe" in {path["path"] for path in resp.json()["data"]["indexed"]}
+    assert [row[0] for row in signoz.telemetrystore.conn.query(PROBE_INDEX_EXPRS_QUERY).result_rows] == ["CAST(attributes.`materialized.probe`, 'String')"]
+
+    resp = requests.post(
+        signoz.self.host_configs["8080"].get(MATERIALIZED_PATH),
+        params={"signal": "traces"},
+        headers={"Authorization": f"Bearer {token}"},
+        timeout=30,
+    )
+    assert resp.status_code == HTTPStatus.OK, resp.text
+    assert "materialized.probe" not in {path["path"] for path in resp.json()["data"]["indexed"]}
+    assert "materialized.probe" in {path["path"] for path in resp.json()["data"]["skipped"]}
+
+
+def test_index_materialized_number_path(
+    signoz: types.SigNoz,
+    create_user_admin: types.Operation,  # pylint: disable=unused-argument
+    get_token: Callable[[str, str], str],
+    materialize_attribute: Callable[..., str],
+    insert_traces: Callable[[list[Traces]], None],
+) -> None:
+    materialize_attribute("traces", "materialized.number_probe", True, "number")
+    token = get_token(USER_ADMIN_EMAIL, USER_ADMIN_PASSWORD)
+
+    resp = requests.post(
+        signoz.self.host_configs["8080"].get(MATERIALIZED_PATH),
+        params={"signal": "traces"},
+        headers={"Authorization": f"Bearer {token}"},
+        timeout=30,
+    )
+    assert resp.status_code == HTTPStatus.OK, resp.text
+    indexed = {path["path"]: path["indexes"] for path in resp.json()["data"]["indexed"]}
+    assert indexed["materialized.number_probe"] == [{"fieldDataType": "number", "type": "bloom_filter(0.01)", "granularity": 64}]
+    exprs = signoz.telemetrystore.conn.query("SELECT expr FROM system.data_skipping_indices WHERE database = 'signoz_traces' AND table = 'signoz_index_v3' AND expr LIKE '%materialized.number_probe%'").result_rows
+    assert [row[0] for row in exprs] == ["if(has(['Int64', 'UInt64', 'Float64'], dynamicType(attributes.`materialized.number_probe`)), accurateCastOrNull(attributes.`materialized.number_probe`, 'Float64'), NULL)"]
+
+    resp = requests.get(
+        signoz.self.host_configs["8080"].get(PROMOTED_PATHS),
+        params={"signal": "traces", "context": "attribute"},
+        headers={"Authorization": f"Bearer {token}"},
+        timeout=30,
+    )
+    assert resp.status_code == HTTPStatus.OK, resp.text
+    listed = {path["path"]: path.get("indexes") for path in resp.json()["data"]}
+    assert listed["materialized.number_probe"] == [{"fieldDataType": "float64", "type": "bloom_filter(0.01)", "granularity": 64}]
+
+    # a non-numeric value must not fail the insert on the numeric index
+    insert_traces([Traces(attributes={"materialized.number_probe": 200}), Traces(attributes={"materialized.number_probe": "not-a-number"})])
+    assert signoz.telemetrystore.conn.query("SELECT count() FROM signoz_traces.distributed_signoz_index_v3 WHERE attributes.`materialized.number_probe` IS NOT NULL").result_rows == [(2,)]