Snowflake Bundle Laravel Package

Getting Started

Minimal Steps

1. Installation Add the bundle via Composer:

   composer require kaigrassnick/snowflake-bundle
   

   Enable the bundle in config/bundles.php:

   return [
       // ...
       Kaigrassnick\SnowflakeBundle\SnowflakeBundle::class => ['all' => true],
   ];
   

2. Configuration Define Snowflake connection settings in config/packages/kaigrassnick_snowflake.yaml:

   snowflake:
       account: 'your_account_identifier'
       username: 'your_username'
       password: 'your_password'
       database: 'your_database'
       schema: 'your_schema'
       warehouse: 'your_warehouse'
       role: 'your_role'
       host: 'your_host'  # Optional (defaults to Snowflake's regional host)
   

3. First Use Case Inject the SnowflakeClient service into a controller or service and execute a query:

   use Kaigrassnick\SnowflakeBundle\Client\SnowflakeClient;
   
   class MyController extends AbstractController
   {
       public function __construct(private SnowflakeClient $snowflake)
       {
       }
   
       public function queryData()
       {
           $result = $this->snowflake->query('SELECT * FROM my_table LIMIT 10');
           return $this->json($result->fetchAll());
       }
   }
   

Implementation Patterns

Usage Patterns

1. Query Execution Use the query() method for direct SQL execution:

   $result = $snowflake->query('SELECT * FROM users WHERE active = true');
   $rows = $result->fetchAll(); // Array of associative arrays
   

2. Parameterized Queries Prevent SQL injection with named parameters:

   $result = $snowflake->query(
       'SELECT * FROM users WHERE id = :id',
       ['id' => $userId]
   );
   

3. Transactions Wrap operations in a transaction for atomicity:

   $snowflake->beginTransaction();
   try {
       $snowflake->query('INSERT INTO logs (message) VALUES (:message)', ['message' => 'Test']);
       $snowflake->commit();
   } catch (\Exception $e) {
       $snowflake->rollBack();
       throw $e;
   }
   

4. Stored Procedures Execute stored procedures with call():

   $result = $snowflake->call('my_procedure', ['param1' => 'value1']);
   

Workflows

• Data Migration Use the bundle to sync data between Symfony and Snowflake during deployments or migrations. Example:

  $snowflake->query('CREATE TABLE IF NOT EXISTS users (...)');
  $snowflake->query('INSERT INTO users (...) SELECT ... FROM staging_users');
  

• Real-Time Analytics Fetch aggregated data for dashboards:

  $result = $snowflake->query('SELECT COUNT(*) as total_users FROM users');
  $totalUsers = $result->fetch()['total_users'];
  

• Event-Driven Workflows Trigger Symfony events based on Snowflake data changes (e.g., using Snowflake Tasks + Symfony cron jobs).

Integration Tips

1. Dependency Injection Prefer constructor injection for SnowflakeClient to ensure testability:

   public function __construct(private SnowflakeClient $snowflake) {}
   

2. Configuration Overrides Override settings per environment (e.g., config/packages/dev/kaigrassnick_snowflake.yaml):

   snowflake:
       password: '%env(SNOWFLAKE_PASSWORD_DEV)%'
   

3. Doctrine Integration Use the bundle alongside Doctrine for hybrid ORM/Query approaches:

   // Fetch IDs from Snowflake, then load entities via Doctrine
   $snowflakeIds = $snowflake->query('SELECT id FROM users')->fetchFirstColumn();
   $users = $entityManager->getRepository(User::class)->findBy(['id' => $snowflakeIds]);
   

4. Caching Results Cache frequent queries using Symfony’s cache system:

   $cacheKey = 'snowflake_users_active';
   $activeUsers = $cache->get($cacheKey, function () use ($snowflake) {
       return $snowflake->query('SELECT * FROM users WHERE active = true')->fetchAll();
   });
   

Gotchas and Tips

Pitfalls

1. Connection Management

   • Issue: Forgetting to close connections or handle connection drops.
   • Fix: Use the bundle’s connection pooling (if available) or implement retry logic for transient failures:

     try {
         $result = $snowflake->query('SELECT 1');
     } catch (\RuntimeException $e) {
         if (str_contains($e->getMessage(), 'connection')) {
             // Reconnect or retry
         }
         throw $e;
     }
     

2. Parameter Binding Quirks

   • Issue: Snowflake-specific parameter syntax (e.g., :param vs $1).
   • Fix: Stick to named parameters (:param) for portability.

3. Large Result Sets

   • Issue: Fetching millions of rows may crash PHP.
   • Fix: Use fetch() in a loop or limit results:

     $result = $snowflake->query('SELECT * FROM large_table');
     while ($row = $result->fetch()) {
         // Process row-by-row
     }
     

4. Schema Changes

   • Issue: Schema changes in Snowflake may break queries.
   • Fix: Use migrations (e.g., Doctrine Migrations or custom scripts) to sync schema changes.

Debugging

1. Enable Logging Configure Monolog to log Snowflake queries:

   # config/packages/monolog.yaml
   handlers:
       snowflake:
           type: stream
           path: '%kernel.logs_dir%/snowflake.log'
           level: debug
           channels: ['snowflake']
   

   Then enable Snowflake logging in the bundle config:

   snowflake:
       logging: true
   

2. Query Profiling Use Symfony’s profiler to measure query execution time:

   $start = microtime(true);
   $result = $snowflake->query('SELECT * FROM users');
   $time = microtime(true) - $start;
   

3. Common Errors

   • Authentication failed: Verify account, username, and password in config.
   • Table not found: Check database and schema settings.
   • Operation not permitted: Ensure the role has sufficient privileges.

Extension Points

1. Custom Query Builders Extend the bundle’s SnowflakeClient to add domain-specific methods:

   class CustomSnowflakeClient extends SnowflakeClient
   {
       public function getUserByEmail(string $email): array
       {
           return $this->query('SELECT * FROM users WHERE email = :email', ['email' => $email])
               ->fetchAssoc();
       }
   }
   

   Register the service in services.yaml:

   services:
       App\Service\CustomSnowflakeClient:
           decorates: 'kaigrassnick.snowflake.client'
           arguments: ['@App\Service\CustomSnowflakeClient.inner']
   

2. Event Listeners Listen for Snowflake events (e.g., query execution) via Symfony’s event dispatcher:

   use Kaigrassnick\SnowflakeBundle\Event\QueryEvent;
   
   class SnowflakeLogger
   {
       public function onQuery(QueryEvent $event)
       {
           $this->logger->info('Snowflake Query', ['query' => $event->getQuery()]);
       }
   }
   

   Bind the listener in services.yaml:

   services:
       App\EventListener\SnowflakeLogger:
           tags:
               - { name: 'kernel.event_listener', event: 'snowflake.query', method: 'onQuery' }
   

3. Custom Connection Factories Override the default connection factory for advanced use cases (e.g., multi-tenancy):

   // src/Snowflake/ConnectionFactory.php
   class CustomConnectionFactory implements ConnectionFactoryInterface
   {
       public function createConnection(array $config): ConnectionInterface
       {
           // Custom logic (e.g., dynamic account selection)
           return new SnowflakeConnection($config);
       }
   }
   

   Register it in services.yaml:

   services:
       kaigrassnick.snowflake.connection_factory:
           class: App\Snowflake\CustomConnectionFactory
   

4. **Async Quer