Skills › Knowledge Work Plugins › data-context-extractor › sql-dialects.md

sql-dialects.md

121 lines · data/skills/data-context-extractor/references/sql-dialects.md at 2574e29

Open on GitHub

1# SQL Dialect Reference

3Include the appropriate section in generated skills based on the user's data warehouse.

5---

SQL Dialect Reference

Include the appropriate section in generated skills based on the user's data warehouse.

Raw lines 1 to 6

7## BigQuery

9```markdown

10## SQL Dialect: BigQuery

12- **Table references**: Use backticks: \`project.dataset.table\`

13- **Safe division**: `SAFE_DIVIDE(a, b)` returns NULL instead of error

14- **Date functions**:

15 - `DATE_TRUNC(date_col, MONTH)`

16 - `DATE_SUB(date_col, INTERVAL 1 DAY)`

17 - `DATE_DIFF(end_date, start_date, DAY)`

18- **Column exclusion**: `SELECT * EXCEPT(column_to_exclude)`

19- **Arrays**: `UNNEST(array_column)` to flatten

20- **Structs**: Access with dot notation `struct_col.field_name`

21- **Timestamps**: `TIMESTAMP_TRUNC()`, times in UTC by default

22- **String matching**: `LIKE`, `REGEXP_CONTAINS(col, r'pattern')`

23- **NULLs in aggregations**: Most functions ignore NULLs; use `IFNULL()` or `COALESCE()`

24```

26---

BigQuery

## SQL Dialect: BigQuery

- **Table references**: Use backticks: \`project.dataset.table\`
- **Safe division**: `SAFE_DIVIDE(a, b)` returns NULL instead of error
- **Date functions**:
  - `DATE_TRUNC(date_col, MONTH)`
  - `DATE_SUB(date_col, INTERVAL 1 DAY)`
  - `DATE_DIFF(end_date, start_date, DAY)`
- **Column exclusion**: `SELECT * EXCEPT(column_to_exclude)`
- **Arrays**: `UNNEST(array_column)` to flatten
- **Structs**: Access with dot notation `struct_col.field_name`
- **Timestamps**: `TIMESTAMP_TRUNC()`, times in UTC by default
- **String matching**: `LIKE`, `REGEXP_CONTAINS(col, r'pattern')`
- **NULLs in aggregations**: Most functions ignore NULLs; use `IFNULL()` or `COALESCE()`

Raw lines 7 to 27

28## Snowflake

30```markdown

31## SQL Dialect: Snowflake

33- **Table references**: `DATABASE.SCHEMA.TABLE` or with quotes for case-sensitive: `"Column_Name"`

34- **Safe division**: `DIV0(a, b)` returns 0, `DIV0NULL(a, b)` returns NULL

35- **Date functions**:

36 - `DATE_TRUNC('MONTH', date_col)`

37 - `DATEADD(DAY, -1, date_col)`

38 - `DATEDIFF(DAY, start_date, end_date)`

39- **Column exclusion**: `SELECT * EXCLUDE (column_to_exclude)`

40- **Arrays**: `FLATTEN(array_column)` to flatten, access with `value`

41- **Variants/JSON**: Access with colon notation `variant_col:field_name`

42- **Timestamps**: `TIMESTAMP_NTZ` (no timezone), `TIMESTAMP_TZ` (with timezone)

43- **String matching**: `LIKE`, `REGEXP_LIKE(col, 'pattern')`

44- **Case sensitivity**: Identifiers are uppercase by default unless quoted

45```

47---

Snowflake

## SQL Dialect: Snowflake

- **Table references**: `DATABASE.SCHEMA.TABLE` or with quotes for case-sensitive: `"Column_Name"`
- **Safe division**: `DIV0(a, b)` returns 0, `DIV0NULL(a, b)` returns NULL
- **Date functions**:
  - `DATE_TRUNC('MONTH', date_col)`
  - `DATEADD(DAY, -1, date_col)`
  - `DATEDIFF(DAY, start_date, end_date)`
- **Column exclusion**: `SELECT * EXCLUDE (column_to_exclude)`
- **Arrays**: `FLATTEN(array_column)` to flatten, access with `value`
- **Variants/JSON**: Access with colon notation `variant_col:field_name`
- **Timestamps**: `TIMESTAMP_NTZ` (no timezone), `TIMESTAMP_TZ` (with timezone)
- **String matching**: `LIKE`, `REGEXP_LIKE(col, 'pattern')`
- **Case sensitivity**: Identifiers are uppercase by default unless quoted

Raw lines 28 to 48

49## PostgreSQL / Redshift

51```markdown

52## SQL Dialect: PostgreSQL/Redshift

54- **Table references**: `schema.table` (lowercase convention)

55- **Safe division**: `NULLIF(b, 0)` pattern: `a / NULLIF(b, 0)`

56- **Date functions**:

57 - `DATE_TRUNC('month', date_col)`

58 - `date_col - INTERVAL '1 day'`

59 - `DATE_PART('day', end_date - start_date)`

60- **Column selection**: No EXCEPT; must list columns explicitly

61- **Arrays**: `UNNEST(array_column)` (PostgreSQL), limited in Redshift

62- **JSON**: `json_col->>'field_name'` for text, `json_col->'field_name'` for JSON

63- **Timestamps**: `AT TIME ZONE 'UTC'` for timezone conversion

64- **String matching**: `LIKE`, `col ~ 'pattern'` for regex

65- **Boolean**: Native BOOLEAN type; use `TRUE`/`FALSE`

66```

68---

PostgreSQL / Redshift

## SQL Dialect: PostgreSQL/Redshift

- **Table references**: `schema.table` (lowercase convention)
- **Safe division**: `NULLIF(b, 0)` pattern: `a / NULLIF(b, 0)`
- **Date functions**:
  - `DATE_TRUNC('month', date_col)`
  - `date_col - INTERVAL '1 day'`
  - `DATE_PART('day', end_date - start_date)`
- **Column selection**: No EXCEPT; must list columns explicitly
- **Arrays**: `UNNEST(array_column)` (PostgreSQL), limited in Redshift
- **JSON**: `json_col->>'field_name'` for text, `json_col->'field_name'` for JSON
- **Timestamps**: `AT TIME ZONE 'UTC'` for timezone conversion
- **String matching**: `LIKE`, `col ~ 'pattern'` for regex
- **Boolean**: Native BOOLEAN type; use `TRUE`/`FALSE`

Raw lines 49 to 69

70## Databricks / Spark SQL

72```markdown

73## SQL Dialect: Databricks/Spark SQL

75- **Table references**: `catalog.schema.table` (Unity Catalog) or `schema.table`

76- **Safe division**: Use `NULLIF`: `a / NULLIF(b, 0)` or `TRY_DIVIDE(a, b)`

77- **Date functions**:

78 - `DATE_TRUNC('MONTH', date_col)`

79 - `DATE_SUB(date_col, 1)`

80 - `DATEDIFF(end_date, start_date)`

81- **Column exclusion**: `SELECT * EXCEPT (column_to_exclude)` (Databricks SQL)

82- **Arrays**: `EXPLODE(array_column)` to flatten

83- **Structs**: Access with dot notation `struct_col.field_name`

84- **JSON**: `json_col:field_name` or `GET_JSON_OBJECT()`

85- **String matching**: `LIKE`, `RLIKE` for regex

86- **Delta features**: `DESCRIBE HISTORY`, time travel with `VERSION AS OF`

87```

89---

Databricks / Spark SQL

## SQL Dialect: Databricks/Spark SQL

- **Table references**: `catalog.schema.table` (Unity Catalog) or `schema.table`
- **Safe division**: Use `NULLIF`: `a / NULLIF(b, 0)` or `TRY_DIVIDE(a, b)`
- **Date functions**:
  - `DATE_TRUNC('MONTH', date_col)`
  - `DATE_SUB(date_col, 1)`
  - `DATEDIFF(end_date, start_date)`
- **Column exclusion**: `SELECT * EXCEPT (column_to_exclude)` (Databricks SQL)
- **Arrays**: `EXPLODE(array_column)` to flatten
- **Structs**: Access with dot notation `struct_col.field_name`
- **JSON**: `json_col:field_name` or `GET_JSON_OBJECT()`
- **String matching**: `LIKE`, `RLIKE` for regex
- **Delta features**: `DESCRIBE HISTORY`, time travel with `VERSION AS OF`

Raw lines 70 to 90

91## MySQL

93```markdown

94## SQL Dialect: MySQL

96- **Table references**: \`database\`.\`table\` with backticks

97- **Safe division**: Manual: `IF(b = 0, NULL, a / b)` or `a / NULLIF(b, 0)`

98- **Date functions**:

99 - `DATE_FORMAT(date_col, '%Y-%m-01')` for truncation

100 - `DATE_SUB(date_col, INTERVAL 1 DAY)`

101 - `DATEDIFF(end_date, start_date)`

102- **Column selection**: No EXCEPT; must list columns explicitly

103- **Arrays**: Limited native support; often stored as JSON

104- **JSON**: `JSON_EXTRACT(col, '$.field')` or `col->>'$.field'`

105- **Timestamps**: `CONVERT_TZ()` for timezone conversion

106- **String matching**: `LIKE`, `REGEXP` for regex

107- **Case sensitivity**: Table names case-sensitive on Linux, not on Windows

108```

109

110---

111

MySQL

## SQL Dialect: MySQL

- **Table references**: \`database\`.\`table\` with backticks
- **Safe division**: Manual: `IF(b = 0, NULL, a / b)` or `a / NULLIF(b, 0)`
- **Date functions**:
  - `DATE_FORMAT(date_col, '%Y-%m-01')` for truncation
  - `DATE_SUB(date_col, INTERVAL 1 DAY)`
  - `DATEDIFF(end_date, start_date)`
- **Column selection**: No EXCEPT; must list columns explicitly
- **Arrays**: Limited native support; often stored as JSON
- **JSON**: `JSON_EXTRACT(col, '$.field')` or `col->>'$.field'`
- **Timestamps**: `CONVERT_TZ()` for timezone conversion
- **String matching**: `LIKE`, `REGEXP` for regex
- **Case sensitivity**: Table names case-sensitive on Linux, not on Windows

Raw lines 91 to 111

112## Common Patterns Across Dialects

113

115|-----------|----------|-----------|------------|------------|

118| String concat | `CONCAT()` or `\|\|` | `CONCAT()` or `\|\|` | `CONCAT()` or `\|\|` | `CONCAT()` or `\|\|` |

Common Patterns Across Dialects

Operation	BigQuery	Snowflake	PostgreSQL	Databricks
Current date	`CURRENT_DATE()`	`CURRENT_DATE()`	`CURRENT_DATE`	`CURRENT_DATE()`
Current timestamp	`CURRENT_TIMESTAMP()`	`CURRENT_TIMESTAMP()`	`NOW()`	`CURRENT_TIMESTAMP()`
String concat	`CONCAT()` or `\	\	`	`CONCAT()` or `\	\	`	`CONCAT()` or `\	\	`	`CONCAT()` or `\	\	`
Coalesce	`COALESCE()`	`COALESCE()`	`COALESCE()`	`COALESCE()`
Case when	`CASE WHEN`	`CASE WHEN`	`CASE WHEN`	`CASE WHEN`
Count distinct	`COUNT(DISTINCT x)`	`COUNT(DISTINCT x)`	`COUNT(DISTINCT x)`	`COUNT(DISTINCT x)`

Raw lines 112 to 121