Skip to main content
Automation

LDAP User Export for Snowflake

PowerShell 5.1 export pipeline that discovers domain controllers from DNS, extracts LDAP identity metadata and photos, then performs a staged Snowflake merge with delta-aware updates.

Overview

Collects Active Directory user email and sign-in metadata from all discovered DCs, fetches thumbnail photos from the PDC Emulator, and upserts the consolidated user record set into a Snowflake target table.

Business Purpose

Maintain a reliable identity and contact dataset in Snowflake for reporting and downstream systems while minimizing redundant data movement and preserving photo payload integrity.

Technology Stack

PowerShell 5.1 (64-bit)System.DirectoryServicesLDAPSnowflake ODBC DSNPS-SnowflakeSecretStorePSLogger

Execution Model

Five-phase execution sequence: Snowflake pre-fetch, cross-DC LDAP sweep, deduplication, photo processing and CSV build, then staging-based Snowflake MERGE.

Pipeline Phases

  1. Read current Snowflake table state for delta decisions and photo hash comparison.
  2. Resolve DC endpoints via DNS and execute paged LDAP metadata pulls against each DC.
  3. Deduplicate cross-DC rows to one best record per SAM account.
  4. Query PDC Emulator for thumbnailPhoto values, hash payloads, and build final CSV.
  5. Create temporary Snowflake staging objects and execute PUT, COPY, and MERGE upsert steps.

Key Runtime Behaviors

  • Domain controllers are discovered at runtime from the required DcDnsName value.
  • LDAP extraction uses PageSize 2000 for large directories and better memory behavior.
  • Metadata collection supports crash-resume using checkpoint.txt and DC-level commit behavior.
  • Best logon is selected using the newer of lastlogon and lastlogontimestamp per row.
  • Default mode exports delta changes only, skipping unchanged user rows.
  • PhotoBase64 is emitted only when SHA-256 hash changes, reducing upload volume.
  • DryRun still validates Snowflake connectivity and extraction flow but skips Phase E upsert.

Inputs, Outputs, and Required Parameters

Required Parameters
  • ConnectionName
  • SnowDatabase
  • SnowSchema
  • DcDnsName
Main Output Artifacts
  • Final CSV: ldap_users_sam_email.csv
  • Temp raw metadata: raw_dc.csv
  • Crash-resume checkpoint: checkpoint.txt
  • Temp folder default: .\ldap_v6_tmp

Snowflake Merge Semantics

  • Temporary table, file format, and stage are created per execution for isolated loading.
  • CSV is uploaded with PUT and copied into staging before merge execution.
  • Merge key is SAM_ACCOUNT_NAME.
  • Updates occur only when EMAIL_ADDRESS, DOMAINCONTROLLER, LAST_LOGIN, or PHOTO_HASH changed.
  • COALESCE logic preserves existing PHOTO_BASE64 when export row has null photo payload.

Execution Examples

.\Export-LdapUserEmails_V6.ps1 `
  -ConnectionName ExampleConnection `
  -SnowDatabase ExampleDatabase `
  -SnowSchema ExampleSchema `
  -DcDnsName directory.example.invalid

.\Export-LdapUserEmails_V6.ps1 `
  -ConnectionName ExampleConnection `
  -SnowDatabase ExampleDatabase `
  -SnowSchema ExampleSchema `
  -DcDnsName directory.example.invalid `
  -DryRun