Standard Operating Procedure (SOP)

Title: Snowflake Table Profiling with 1Password Credentials
Version: 1.0
Date: January 29, 2026
Owner: Data Engineering Team

1. Purpose

This SOP standardizes the process of profiling Snowflake tables and mapping them to dbt marts usage. It enables team members to:

Connect to client Snowflake instances securely using 1Password credentials
Profile tables in specified databases/schemas
Map source tables to dbt marts to understand data lineage
Generate CSV reports for analysis and documentation

This workflow is reusable across all client engagements that use Snowflake and dbt.

2. Scope

Applies to:

Profiling Snowflake tables for client data assessments
Mapping source tables to dbt marts for lineage documentation
Generating table inventory reports
Understanding data dependencies in dbt projects

Does not apply to:

Production data pipeline execution
Direct Snowflake querying for business analysis (use appropriate tools)
Modifying dbt models or Snowflake schemas

3. Definitions

Source Vendor: The data source system (e.g., shopify, stripe, polytomic)
dbt Mart: Final business-ready tables in dbt (typically in marts/ directory)
Source Table: Raw table imported from external systems (typically in raw/ directory)
Full Identifier: Complete table reference in format DATABASE.SCHEMA.TABLE
1Password Item: Secure credential storage item in 1Password vault

4. Prerequisites

Before performing this SOP, ensure you have:

1Password CLI installed and signed in:
```
# Verify installation
op --version
 
# Sign in
op signin
```
Note: 1Password CLI is typically installed via Homebrew: brew install 1password-cli
Python 3.8+ installed

Required Python packages:

pip install snowflake-connector-python pyyaml

1Password item containing Snowflake credentials with fields:
- account - Snowflake account identifier
- username - Snowflake username
- password - Snowflake password
- warehouse - Snowflake warehouse name
- database - Default database
- schema - Default schema
- role - Snowflake role
Access to client repository with dbt project
Network access to Snowflake instance (may require VPN)

5. Responsibilities

Data Engineer/Analyst: Execute profiling script, validate output, review results
Project Lead: Provide 1Password credential path, validate business context
Client: Provide Snowflake access credentials (stored in 1Password)

6. Step-by-Step Procedure

6.1 Prepare Environment

Navigate to client repository root directory
Verify scripts/profile_raw_tables.py exists (or copy from template)
Verify dbt_project/ directory exists with models/ subdirectory
Verify dbt_project/models/raw/sources.yml exists (defines source vendors)

6.2 Verify 1Password Access

Sign in to 1Password CLI:
```
op signin
```
Verify you can access the Snowflake credentials item:
```
op read "op://knowledge/item-name/account"
```
Replace knowledge/item-name with actual 1Password item path

6.3 Install Python Dependencies

Install required packages:

pip install snowflake-connector-python pyyaml

Verify installation:

python -c "import snowflake.connector; import yaml; print('Dependencies OK')"

6.4 Run Profiling Script

Execute the profiling script with appropriate parameters:

python scripts/profile_raw_tables.py \
  --credentials "op://knowledge/item-name" \
  --database RAW \
  --schema SCHEMA_NAME \
  --output table_profile.csv

Parameters:
- --credentials (required): 1Password item path
- --database: Database name (default: RAW)
- --schema: Schema name to profile
- --output: Output CSV filename (default: table_profile.csv)
- --dbt-project: Path to dbt project (default: dbt_project)

Example for shopify schema:

python scripts/profile_raw_tables.py \
  --credentials "op://Brainforge/Client-Snowflake" \
  --database RAW \
  --schema POLYTOMIC_SHOPIFY \
  --output shopify_table_profile.csv

6.5 Review Output

Verify CSV file was created successfully
Open CSV in spreadsheet software (Excel, Google Sheets)
Review columns:
- source_vendor: Should match sources defined in sources.yml
- table_name: Actual table names in Snowflake
- full_identifier: Complete table reference
- table_size_bytes / table_size_formatted: Table size information
- row_count: Number of rows
- used_in_marts: Yes/No indicator
- customers_mart, wholesale_customers_mart, sales_mart: Which marts use each table

6.6 Validate Results

Check source vendor mapping:
- Verify source vendors match sources.yml definitions
- Unknown vendors may indicate missing source definitions
Check mart usage:
- Verify tables marked as “used” actually appear in dbt dependency chain
- Manually spot-check a few tables by tracing source() → ref() → marts
Check table counts:
- Verify number of tables matches expectations
- Check for missing tables that should exist

6.7 Document Findings

Save CSV output to appropriate location:
- Client repo: resources/ or docs/
- Or internal vault: clients/{client}/resources/
Add summary notes if needed:
- Total tables profiled
- Tables used vs unused in marts
- Notable findings or anomalies

7. Quality Checks

Before considering profiling complete, verify:

✅ CSV file opens correctly in spreadsheet software
✅ All expected tables are present in output
✅ Source vendor mapping is accurate (matches sources.yml)
✅ Mart usage flags are correct (spot-check a few tables)
✅ Table sizes and row counts are reasonable (not all zeros)
✅ Full identifiers are correctly formatted (DATABASE.SCHEMA.TABLE)
✅ No errors in script execution output

8. Escalation Path

If connection fails:

Verify 1Password credentials are correct
Check network/VPN access to Snowflake
Verify Snowflake account/warehouse/role permissions
Contact Project Lead for credential access issues

If source vendor mapping is incorrect:

Review sources.yml for missing or incorrect definitions
Check if table names match between Snowflake and dbt sources
Update sources.yml if needed (may require dbt project changes)

If mart usage is incorrect:

Review dbt model dependency chain manually
Check if script correctly parsed source() and ref() calls
Verify dbt project structure matches expected format
Report script issues to Data Engineering team

If script errors occur:

Check Python version (requires 3.8+)
Verify all dependencies are installed
Review error message for specific issue
Check script path and dbt project path are correct

9. Version History

v1.0 (January 29, 2026) — Initial SOP creation
- Standardized Snowflake table profiling workflow
- Integrated 1Password credential management
- Added dbt mart mapping functionality

Appendix A: Script Location

The profiling script template is located at:

Client repos: scripts/profile_raw_tables.py
Template can be copied from standards/ in this repo if needed

Appendix B: Common 1Password Item Paths

Example 1Password item paths (adjust for your vault structure):

op://Brainforge/Client-Snowflake
op://vault-name/item-name
op://Private/Client-Name-Snowflake-Credentials

Appendix C: Troubleshooting

“snowflake-connector-python not installed”

pip install snowflake-connector-python

“Error retrieving credentials from 1Password”

op signin  # Sign in to 1Password CLI
op read "op://knowledge/item/field"  # Test credential access

“Error connecting to Snowflake”

Verify account identifier format (may need region: account.region)
Check warehouse/database/schema names are correct
Verify role has necessary permissions
Test connection using Snowflake web UI first

“No tables found”

Verify database and schema names are correct (case-sensitive)
Check if schema exists: SHOW SCHEMAS IN DATABASE RAW;
Verify role has access to the schema

Brainforge Knowledge

Explorer

snowflake-table-profiling