Ingestion Rules

Ingestion Rules allow you to automatically validate, transform, skip, or reject data during the file upload process. They give you fine-grained control over data quality without requiring manual intervention on every upload.

When Are Ingestion Rules Applied?

Ingestion rules are applied after the spreadsheet has been cleaned and mapped to your Table schema, but before the data is written to the database. This means:

1. ✅ The spreadsheet structure has been detected (headers, body rows)
2. ✅ Column mapping has been completed (your approvals applied)
3. ✅ Data has been transformed to match your Table columns
4. → Ingestion rules are applied here
5. ❌ Data has NOT yet been written to BigQuery

This timing is important: rules operate on the cleaned, mapped data using your Table's column names (like Policy_Number or month.Gross_Premium), not the original spreadsheet column names.

---

Two Types of Rules

Decode supports two types of ingestion rules that address different use cases:

Row-Level Rules

Row-level rules evaluate conditions across one or more columns and then perform an action that typically affects the entire row.

Use cases:

• Skip rows where multiple columns are empty
• Set a default value when a condition is met across columns
• Validate that related fields are consistent

Cell-Level Rules

Cell-level rules target a specific column and evaluate each cell in that column individually.

Use cases:

• Clean up formatting in a specific column (remove spaces, trim whitespace)
• Transform values that meet certain criteria (prepend IDs, pad with zeros)
• Validate individual cell values

---

Creating Ingestion Rules

Accessing the Rules Builder

1. Navigate to Data → Select your Table → Settings
2. Expand the Ingestion Rules accordion
3. Click Add Row Rule or Add Cell Rule

Rule Components

Every rule has these common components:

Component	Description
Name	A descriptive name for the rule (e.g., "Skip empty premium rows")
Enabled	Toggle to activate/deactivate the rule without deleting it
Level	Row or Cell
Conditions	The criteria that must be met for the rule to trigger
Action	What happens when conditions are met

Rule Ordering

Rules are executed in the order they appear in the list. You can drag and drop rules to reorder them. This is important because:

• Earlier rules may modify data that later rules depend on
• A "skip row" rule that runs first will prevent later rules from seeing that row

---

Row-Level Rules

Conditions

Row-level rules use conditions that evaluate column values. Multiple conditions can be combined with AND or OR logic.

Available Operators

Category	Operator	Description	Value Required
Existence	Is Empty	Cell is null or blank	No
	Is Not Empty	Cell has a value	No
Comparison	Equals	Exact match	Yes
	Not Equals	Does not match	Yes
Text	Contains	Text includes substring	Yes
	Not Contains	Text excludes substring	Yes
Numeric	Greater Than	Numeric comparison	Yes
	Less Than	Numeric comparison	Yes
	Greater or Equal	Numeric comparison	Yes
	Less or Equal	Numeric comparison	Yes
	Between	Value in range	Two values
Data Type	Is Valid Date	Can be parsed as date	No
	Is Number	Can be parsed as number	No
	Is Text	Has any value	No
	Is Boolean	True/false/yes/no/1/0	No
String	Length Equals	Character count matches	Yes
	Length Greater Than	Character count exceeds	Yes
	Length Less Than	Character count below	Yes
	Starts With	Text begins with	Yes
	Ends With	Text ends with	Yes
	Matches Pattern	Regex match	Pattern

Negation

Any condition can be negated using the NOT checkbox. This inverts the condition's result.

Example: Combining Conditions

Condition 1: month.Gross_Premium IS EMPTY
Condition 2: month IS EMPTY
Logic: AND

Result: Rule triggers only when BOTH Gross_Premium and month are empty

Row-Level Actions

Action	Description	Options
Skip Row	Remove the row from the data being ingested	None
Fail Row	Stop processing and show an error	Custom error message
Set Value	Set a column to a specific value	Static value or copy from another column
Concatenate	Combine multiple columns into one	Source columns, separator, optional transform
Transform	Modify a column's values	Uppercase, lowercase, trim, replace, format date, round number

---

Cell-Level Rules

Cell-level rules operate on a target column and can use either simple cell conditions or advanced conditions.

Target Column

Select which column this rule applies to. The rule will evaluate and potentially modify cells in this column.

Cell Conditions

Cell conditions evaluate individual cell values:

Condition	Description	Value Required
Always	Always triggers	No
Is Empty	Cell is null or blank	No
Content Length	Character count comparison	Operator + number
Content Type	Value type check	date/number/text
Contains	Text includes substring	Text to find
Is Contained In	Value is in allowed list	List of values
Numeric Comparison	Numeric evaluation	Operator + number

Content Length Example

Condition: Content Length < 5
Target: Policy_Number

Result: Triggers for policy numbers shorter than 5 characters

Advanced Conditions

Toggle Use advanced conditions to access the same powerful condition builder used in row-level rules. This allows you to trigger cell actions based on conditions in other columns.

Cell-Level Actions

Action	Description	Options
Skip Row	Remove the entire row	None
Fail Row	Stop with error	Custom message
Trim	Remove leading/trailing whitespace	None
Remove Spaces	Remove all spaces	None
Set Value	Replace the cell value	Static, prepend, append, arithmetic, conditional
Transform	Apply transformations	See transformation options

Transformation Options

Transform	Description	Parameters
Uppercase	Convert to uppercase	None
Lowercase	Convert to lowercase	None
Trim Whitespace	Remove leading/trailing spaces	None
Remove Spaces	Remove all spaces	None
Strip Leading Zeros	Remove leading 0s (keeps at least one)	None
Keep Digits Only	Remove all non-numeric characters	None
Remove Characters	Remove specific characters	Characters to remove
Left (first N)	Keep first N characters	Count
Right (last N)	Keep last N characters	Count
Substring	Extract portion of text	Start position, length
Find & Replace	Replace text	Find text, replace with
Pad Left	Add characters to start	Target length, pad character
Pad Right	Add characters to end	Target length, pad character
Drop Prefix	Remove text from start	Text to drop
Drop Suffix	Remove text from end	Text to drop
Cast to Number String	Convert number to clean text	None

Set Value Formulas

Cell-level "Set Value" actions support several formula types:

Prepend from Column

Adds another column's value before the current value.

Source Column: Contract_ID
Separator: -
Result: "ABC123-POL001" (Contract_ID + separator + Policy_Number)

Append from Column

Adds another column's value after the current value.

Arithmetic Operation

Perform math on numeric values:

• Multiply by
• Divide by
• Add
• Subtract

---

Real-World Examples

Here are some common ingestion rule patterns:

Example 1: Remove Whitespace from Policy Numbers

Type: Cell-level
Target Column: Policy_Number
Condition: Contains (value: " " - a space)
Action: Remove Spaces

This cleans up policy numbers that accidentally contain spaces.

Example 2: Prepend Contract ID to Short Policy Numbers

Type: Cell-level
Target Column: Policy_Number
Condition: Content Length < 5
Action: Set Value → Formula → Prepend
Formula Config: Source Column = Contract_ID, Separator = "-"

When policy numbers are too short to be unique, this prepends the contract ID to create a unique identifier.

Example 3: Skip Rows with Empty Key Fields

Type: Row-level
Conditions:

• month.Gross_Premium IS EMPTY
• month IS EMPTY
  Logic: AND
  Action: Skip Row

This removes rows that have no premium data and no reporting month (likely blank rows in the source spreadsheet).

Example 4: Set Default Value When Field is Empty

Type: Row-level
Conditions:

• Coverholder_Commission_Percentage IS EMPTY
• OR Coverholder_Commission_Percentage EQUALS "0"
• OR Coverholder_Commission_Percentage MATCHES REGEX "^\s0(.0+)?\s%?\s*$"
  Logic: OR
  Action: Set Value
  Target Column: month.Net_Premium_Less_Brokerage
  Value Type: From Column
  Source Column: month.Net_Premium

When commission is zero or empty, copy Net Premium to Net Premium Less Brokerage (since they're equivalent).

---

Tips and Best Practices

Order Matters

• Put "skip row" rules early to avoid processing rows you'll discard
• Put data cleaning rules (trim, remove spaces) before validation rules
• Put transformation rules in logical sequence (clean → validate → transform)

Use Descriptive Names

Name your rules clearly so you can understand their purpose at a glance:

• ✅ "Skip rows with empty gross premium and month"
• ❌ "Rule 1"

Test with Preview

After creating rules, upload a test file and review the preview carefully to ensure rules are working as expected before processing production data.

Disable vs Delete

If a rule isn't working correctly, disable it rather than deleting it. This lets you troubleshoot without losing your configuration.

Regular Expressions

For complex pattern matching, use the Matches Pattern operator with regex. Common patterns:

• ^[A-Z]{2}\d{4}$ - Two letters followed by four digits
• ^\s*$ - Empty or whitespace only
• ^\d+\.?\d*$ - Numeric values (with optional decimal)

---