# External Materialization

Custom dbt materialization that creates Snowflake external tables from dbt models. Automatically converts SELECT statements into `CREATE OR REPLACE EXTERNAL TABLE` statements with proper column type inference and partitioning support.

## Usage

To use the `external` materialization, add the following configuration to your model:

```sql
{{
    config(
        materialized='external',
        location='@your_stage/path',
        file_format='your_file_format',
        pattern='optional_pattern',
        stage='optional_stage'
    )
}}

SELECT
    $1:field1::VARCHAR AS column1,
    $1:field2::VARIANT AS column2,
    $1:field3::FLOAT AS column3,
    TRY_TO_DATE($1:date_field::VARCHAR, 'YYYY-MM-DD') AS date_column
FROM @your_stage/path
```

## Configuration

### Required Parameters

- **`location`**: The Snowflake stage location where the external data files are stored
  - Example: `@SUNO_PROD.PROD.SUNO_DYNAMODB_EVENTS/orpheus`

- **`file_format`**: The Snowflake file format to use for parsing the external data files
  - Example: `SUNO_PROD.PROD.BASIC_JSON_LOADER`

### Optional Parameters

- **`pattern`**: File pattern for filtering files in the stage location (supports regex)
  - Example: `orpheus/pdate=.*/phour=.*/data/.*.json.gz`

- **`stage`**: Alternative to location - specifies the stage name directly
  - If provided, overrides the location parameter

## Column Type Inference

The materialization automatically infers column types based on SQL expressions in the SELECT statement:

| Expression | Snowflake Type | Description |
|------------|----------------|-------------|
| `::VARCHAR` | `VARCHAR` | String data type for text fields |
| `::VARIANT` | `VARIANT` | JSON/object data type for complex structures |
| `::FLOAT` or `::NUMBER` | `FLOAT` | Floating point numeric data type |
| `::INT` or `::INTEGER` | `INTEGER` | Integer numeric data type |
| `TRY_TO_DATE()` | `DATE` | Date data type for date parsing functions |
| `TRY_TO_NUMBER()` | `NUMBER` | Numeric data type for number parsing functions |
| Default | `VARIANT` | Fallback type for unrecognized expressions |

## Special Features

### Automatic Column Type Inference
Analyzes SELECT statement expressions to automatically determine appropriate Snowflake column types. Supports common casting patterns and function-based type detection.

### Partitioning Support
Automatically detects columns using `METADATA$FILENAME` and creates partition columns. Enables efficient querying of partitioned external data.

### Metadata Column Injection
Automatically adds `FILENAME` column using `METADATA$FILENAME` for file tracking. Useful for debugging and data lineage.

### Pre/Post Hook Support
Supports dbt pre-hooks and post-hooks for custom logic execution. Hooks run both inside and outside transactions as appropriate.

## Examples

### Basic External Table
```sql
{{
    config(
        materialized='external',
        location='@SUNO_PROD.PROD.SUNO_DYNAMODB_EVENTS/orpheus',
        file_format='SUNO_PROD.PROD.BASIC_JSON_LOADER'
    )
}}

SELECT
    $1:Keys:item_id:S::VARCHAR AS id,
    $1:Keys:type:S::VARCHAR AS type,
    $1:NewImage:message_id:S::VARCHAR AS message_id,
    $1:NewImage:content:S::VARCHAR AS content,
    $1:NewImage:sessions::VARIANT AS sessions
FROM @SUNO_PROD.PROD.SUNO_DYNAMODB_EVENTS/orpheus
```

### External Table with Partitioning
```sql
{{
    config(
        materialized='external',
        location='@SUNO_PROD.PROD.SUNO_DYNAMODB_EVENTS/orpheus',
        file_format='SUNO_PROD.PROD.BASIC_JSON_LOADER',
        pattern='orpheus/pdate=.*/phour=.*/data/.*.json.gz'
    )
}}

SELECT
    $1:Keys:item_id:S::VARCHAR AS id,
    $1:NewImage:content:S::VARCHAR AS content,
    -- Parse partition columns from file path
    TRY_TO_DATE(SPLIT_PART(SPLIT_PART(METADATA$FILENAME, 'pdate=', 2), '/', 1), 'YYYY-MM-DD') AS p_date,
    TRY_TO_NUMBER(SPLIT_PART(SPLIT_PART(METADATA$FILENAME, 'phour=', 2), '/', 1)) AS p_hour
FROM @SUNO_PROD.PROD.SUNO_DYNAMODB_EVENTS/orpheus
```

## Generated SQL

For the partitioning example above, this would generate:

```sql
CREATE OR REPLACE EXTERNAL TABLE your_schema.your_table
(
    id VARCHAR AS ($1:Keys:item_id:S::VARCHAR),
    content VARCHAR AS ($1:NewImage:content:S::VARCHAR),
    p_date DATE AS (TRY_TO_DATE(SPLIT_PART(SPLIT_PART(METADATA$FILENAME, 'pdate=', 2), '/', 1), 'YYYY-MM-DD')),
    p_hour NUMBER AS (TRY_TO_NUMBER(SPLIT_PART(SPLIT_PART(METADATA$FILENAME, 'phour=', 2), '/', 1))),
    FILENAME STRING AS (METADATA$FILENAME)
)
PARTITION BY (p_date, p_hour)
LOCATION = @SUNO_PROD.PROD.SUNO_DYNAMODB_EVENTS/orpheus
FILE_FORMAT = SUNO_PROD.PROD.BASIC_JSON_LOADER
PATTERN = 'orpheus/pdate=.*/phour=.*/data/.*.json.gz'
AUTO_REFRESH = FALSE
REFRESH_ON_CREATE = TRUE
```

## Best Practices

### SQL Formatting
- Maintain consistent SQL formatting with proper line breaks and indentation
- The materialization parses the SELECT statement line by line, so formatting matters

### Column Naming
- Use clear, descriptive column names with AS aliases
- Avoid special characters that might cause issues in Snowflake

### Type Casting
- Explicitly cast columns using `::TYPE` syntax for predictable type inference
- Use `TRY_TO_*` functions for safe type conversion with error handling

### Partitioning Strategy
- Use `METADATA$FILENAME` to extract partition information from file paths
- Design file naming conventions that support efficient partitioning

### File Pattern Optimization
- Use specific file patterns to limit the scope of external table queries
- This improves query performance and reduces unnecessary file scanning

## Limitations

### SQL Parsing
- The materialization uses simple line-by-line parsing of SELECT statements
- Complex SQL with nested subqueries or complex expressions may not be parsed correctly

### Type Inference
- Type inference is based on string matching of common patterns
- Custom functions or complex expressions may default to VARIANT type

### Snowflake Only
- This materialization is specifically designed for Snowflake and will not work with other database adapters

### External Table Constraints
- External tables have limitations compared to regular tables, including no support for indexes, constraints, or certain SQL operations

## How It Works

The materialization:

1. Parses your SELECT statement to extract column definitions
2. Automatically determines column types based on the expressions (see type mapping table above)
3. Detects partition columns using `METADATA$FILENAME` references
4. Generates a `CREATE OR REPLACE EXTERNAL TABLE` statement
5. Sets `AUTO_REFRESH = FALSE` and `REFRESH_ON_CREATE = TRUE`
6. Automatically adds `FILENAME` column for file tracking

---

**Version**: 1.0.0  
**Last Updated**: 2024-12-19  
**Maintainer**: Data Engineering Team