sqlx-patterns

# SQL/SQLX Patterns Reference

This skill provides coding conventions and patterns for BigQuery SQL and Dataform SQLX development. Reference these patterns when writing data transformations.

---

## SQLX File Structure

### View Configuration

```sql
config {
  type: "view",
  schema: "expanly_query_builder_data",
  tags: ["client_name", "layer2", "scoring"]
}
```

### Incremental Table Configuration

```sql
config {
  type: "incremental",
  schema: "expanly_query_builder_data_snapshots",
  uniqueKey: ["snapshot_date", "join_id"],
  protected: true,
  bigquery: {
    partitionBy: "snapshot_date",
    clusterBy: ["item_group_id"]
  },
  tags: ["snapshot", "scoring"]
}
```

### Table Configuration

```sql
config {
  type: "table",
  schema: "expanly_query_builder_data",
  tags: ["client_name"]
}
```

---

## Dependency Patterns

### Internal References

```sql
FROM ${ref("table_name")}

FROM ${ref("schema_name", "table_name")}
```

### Variable References

```sql
FROM ${ref(dataform.projectConfig.vars.raw__feed)}
```

### External References (No Tracking)

```sql
FROM `project.dataset.table`
```

### Fully Qualified Reference

```sql
FROM ${ref({database: "project-id", schema: "dataset", name: "table"})}
```

---

## SQL Patterns

### Safe Operations (MANDATORY)

```sql
SAFE_DIVIDE(revenue, cost) AS roas

COALESCE(field, 0) AS field_with_default

SAFE_CAST(string_field AS FLOAT64) AS numeric_field

GREATEST(0, LEAST(100, calculated_value)) AS capped_value
```

### ID Normalization (MANDATORY)

```sql
UPPER(TRIM(id_column)) AS join_id
```

**Critical**: GA4 uses mixed case, Google Ads uses lowercase. Always normalize.

### Deduplication

```sql
SELECT * EXCEPT(row_num)
FROM (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY id
      ORDER BY priority_field DESC
    ) AS row_num
  FROM source_table
)
WHERE row_num = 1
```

### Conditional Ranking

Rank only items with data, avoid ranking nulls:

```sql
CASE
  WHEN gads_cost_short > 0
    THEN PERCENT_RANK() OVER