Plan a Znode 9 product attribute migration without confusing it with a Store attribute

Znode 9
Written By

Inspect the target PIM schema before planning a product attribute's definition, localized labels, family mappings, options, and published value.

Znode 9GuideIntermediateGCG engineering guide
In this guide
  1. PIM attribute definition
  2. localized label
  3. group and family mapping
  4. product value
  5. published product projection
A product attribute has product-data ownership and a publication path. It is separate from Store global-attribute metadata.

Choose the value owner before writing SQL

A product attribute describes a catalog item, such as a fictional MaterialGrade. A Store attribute describes a portal or buying context. Those owners travel through different metadata and value relationships, so substituting table names from a global-attribute script is unsafe.

Write down the target entity, attribute type, locale, product family, Admin surface, publication path, and whether the value is free text or a controlled option. A migration can create metadata successfully while leaving the field unavailable to editors or absent from a published product.

Map the target patch read-only

Run the query in a disposable copy of the exact target Znode 9 database. It lists PIM tables and columns whose names refer to attributes, families, groups, or locales. Use the result to locate the product metadata paths before designing inserts.

Then inspect an existing product attribute of the same type. Compare its definition, localized label, group and family assignments, and value records. The supplemental foreign-key query helps identify declared relationships, but application code may enforce additional relationships that SQL constraints do not describe.

inspect-pim-attribute-shape.sql sql

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE 'ZnodePim%'
  AND (COLUMN_NAME LIKE '%Attribute%' OR COLUMN_NAME LIKE '%Family%'
       OR COLUMN_NAME LIKE '%Group%' OR COLUMN_NAME LIKE '%Locale%')
ORDER BY TABLE_NAME, ORDINAL_POSITION;

Finding a column named PimAttributeId tells you where to investigate; it does not establish all required insert columns or the order of writes. Verify types, defaults, required values, and the corresponding Admin save path for the selected patch before preparing a mutation.

Plan the definition, form mappings, and value path

Plan each part of the product field separately: its definition, the label editors see, the group and family where it appears, and the product value it stores. A dropdown also needs stable option codes and localized option labels. The Store global-attribute script uses different tables and cannot be converted by changing a few names.

Resolve stable codes to IDs and reject missing or ambiguous prerequisites. Check whether an existing definition has the intended type before adding relationships. Include a rerun case where the definition exists but a required mapping is missing, and preserve administrator-owned product values.

  • Create a new numbered migration rather than editing a journaled file.
  • Use a transaction only when the project runner and deployment policy support it.
  • Verify a synthetic product in Admin, then publish or refresh through the declared version's supported path.

Verification is broader than rows

Use a repeatable verification record: expected database rows, the field location in Admin, behavior for an empty product, one saved value, and the published product projection. Repeat the same record after a rerun and a deliberately conflicting attribute definition. That sequence establishes the migration's creation, rerun, and conflict behavior for the selected patch.

Inspect declared foreign keys around the PIM attribute table

Run this query in the selected Znode 9 database with metadata visibility. ReferencingTable and ReferencingColumn identify the child side of each declared foreign key; ReferencedTable and ReferencedColumn identify its target. Composite keys produce one row per column. Check is_disabled and is_not_trusted before treating a constraint as enforced evidence, and inspect the application save path for relationships not declared as database constraints.

inspect-pim-attribute-foreign-keys.sql sql

DECLARE @TableId int = OBJECT_ID(N'dbo.ZnodePimAttribute', N'U');
IF @TableId IS NULL
    THROW 51020, 'PIM attribute table is absent or not visible to this login.', 1;

SELECT fk.name AS ForeignKeyName,
       OBJECT_SCHEMA_NAME(fk.parent_object_id) AS ReferencingSchema,
       OBJECT_NAME(fk.parent_object_id) AS ReferencingTable,
       pc.name AS ReferencingColumn,
       OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS ReferencedSchema,
       OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,
       rc.name AS ReferencedColumn,
       fkc.constraint_column_id, fk.is_disabled, fk.is_not_trusted
FROM sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc ON fkc.constraint_object_id = fk.object_id
JOIN sys.columns AS pc
  ON pc.object_id = fkc.parent_object_id AND pc.column_id = fkc.parent_column_id
JOIN sys.columns AS rc
  ON rc.object_id = fkc.referenced_object_id AND rc.column_id = fkc.referenced_column_id
WHERE fk.parent_object_id = @TableId OR fk.referenced_object_id = @TableId
ORDER BY fk.name, fkc.constraint_column_id;

References and further reading

Bring your next engineering question.

Need help planning a safe catalog-data migration?

Explore how GCG can help

Independent guidance from GCG. Znode is a trademark of its owner. Examples use fictional data and are not official platform documentation. Suggest a correction.

Previous

Create a Store global attribute with a SQL migration in Znode 9
Next

Build a Znode 9 upgrade inventory before touching packages