The combined power of Domo Cloud Amplifier and Snowflake is the best-kept secret in data management right now—and we’re reaching new heights every day. In this how-to guide, we’ll take you behind the APIs and show Snowflake engineers how Cloud Amplifier and Snowflake can work together to help accomplish five practical—but powerful—tasks: 

• Prepare and enrich data 
• Create a new table in Snowflake and insert data 
• Integrate and enrich data 
• Configure and utilize multiple warehouses 
• Create a unified source of truth 

 

Get oriented

Introducing the key elements 

This guide showcases the Cloud Amplifier configuration with Snowflake. To start, get to know some key terms: 

• Snowflake: The centralized source of truth for our initial data 
• Magic ETL: Domo’s tool for combining and preparing data tables 
• ERP: A supplemental data source from Salesforce 
• Geographic: A supplemental data source (i.e., a crosswalk table) within Domo, including demographics by zip code data set 
• Social Media: Shorthand for data from social media platforms (e.g., Instagram)

Why Snowflake? We’re focusing on Snowflake’s cloud data platform because of its ability to handle large-scale, diverse data sets with ease and efficiency. For real-time data analytics and reporting, it’s an ideal choice. 

 

Prepare and enrich data 

with Cloud Amplifier and Magic ETL

Cloud Amplifier with Magic ETL will help ensure your data is ready for further analysis. Here, we’ve used Magic ETL to prepare and enrich our Snowflake data. Then we wrote the enriched data back to Snowflake. That data now becomes available to use in Domo and other products that Snowflake supports.    

 

Create a new table in Snowflake and insert data 

with Cloud Amplifier

Snowflake APIs in Python allow you to manipulate and integrate your data in sophisticated—and useful—ways. Here’s how we created the transactions table in Snowflake in our Jupyter Notebook: 

 

import snowflake.connector
    "from snowflake.connector.pandas_tools import write_pandas\n",
#Connect to Snowflake
conn = snowflake.connector.connect(
	user=’USER’,
	password=’PASSWORD’,
	account=’ACCOUNT’,
)

# Create the ‘transactions’ table
create_transactions_table = “””
CREATE OR REPLACE TABLE transactions (
	tx_id STRING
	tx_datetime STRING,
	tx_date DATE,
	customer_id NUMBER(10, 0),
	terminal_id NUMBER (10, 0),
	tx_amount FLOAT,
	tx_fraud NUMBER(3, 0),
	tx_fraud_scenario NUMBER(3, 0),
	…
)
“””
cur = conn.cursor()
cur.execute(create_transactions_table)

# Assuming ‘transactions’ is a Pandas DataFrame with data ready for upload
success, nchunks, nrows, _ = write_pandas(conn, transactions, ‘TRANSACTIONS’)

 

Next, we generated the Customers table using this code: 

 

import snowflake.connector
from snowflake.connector.pandas_tools import write_pandas

# Connect to Snowflake
conn = snowflake.connector.connect(
	user=’USER’,
	password=’PASSWORD’,
	account=’ACCOUNT’,
)

create_customers_table = “””
CREATE OR REPLACE TABLE customers (
	customer_id NUMBER(10, 0)
	customer_region STRING,
	customer_segment STRING,
	PRIMARY KEY (customer_id)
)
“””
customers.columns = [col.upper() for col in customers.columns]
cur = conn.cursor()
cur.execute(create_customers_table)
success, nchunks, nrows, _ = write_pandas(conn, customers, ‘CUSTOMERS’)

   

These snippets illustrate creating a new table in Snowflake and then inserting data from a Pandas DataFrame. Very slick, if we may say so. You can visit Snowflake’s API Documentation for more detailed examples and further support. 

 

Integrate and enrich data for enhanced analytics 

with Cloud Amplifier

Enriching Snowflake with Salesforce, social media, and financial data is another common request that we hear again and again. Here’s how we’ve done it: We used Python to extract data from those sources into dataframe. Then we used the Domo APIs to load those dataframes into data sets in Domo. Note that the Domo SDKs are helpful for programmatically interacting with Domo to load data and build applications. Here’s the code we used:

 

from pydomo import Domo
domo = Domo(‘CLIENT_ID’, ‘SECRET_KEY’,api_host=’API_HOST’)

# Extract unique customer_ids from the primary dataset
unique_customer_ids = df[‘customer_id’].unique()
unique_customer_id_count = df[‘customer_id’].nunique()
# Define a data range for the synthetic data

# Generate synthetic ERP data 
erp_data = pd.DataFrame({
	‘customer_id’: np.random.choice(unique_customer_ids,
size=unique_customer_id_count, replace=False),
	‘account_status’: np.random.choice([‘Active’, ‘Closed’, ‘Delinquent’],
size=unique_customer_id_count),
	‘credit_limit’: np.random.uniform(1000, 10000, size=unique_customer_id_count),
	‘current_balance’: np.random.uniform(o, 10000, size=unique_customer_id_count),
	‘last_payment_date’: np.random.choice(date_range,
size=unique_customer_id_count),
	‘payment_frequency’: np.random.choice([‘Monthly’, ‘Quarterly’, ‘Yearly’],
size=unique_customer_id_count),
})

domo.ds_create(erp_data, ‘CCFD ERP Data Salesforce’)

   

Configure and utilize multiple warehouses 

with Cloud Amplifier

Another thing we’re thrilled about with Cloud Amplifier: You can interact with Snowflake at a macro-level yet traverse to a micro-level in just a few clicks. In this demonstration, we provisioned five primary tables, all within the same database. When configuring these tables in Domo, you can see below how easy it is to go from warehouse to a multi-select on these tables in three clicks (the blue numbers).  

 

Create a unified source of truth 

with Cloud Amplifier

This one’s simple—by writing the enriched data back to Snowflake, we created a single, unified source of truth. Doing this ensures your organization’s data is consistent and reliable. There’s no true action step here; by preparing your data in Domo, you automatically create this unified source.  

 

Explore our resources and copy our code 

Our goal is to help data engineers see firsthand the practical ways that both Snowflake and Domo Cloud Amplifier via API/SDK can amplify their businesses. We’re helping hundreds of businesses build their own comprehensive analytics solutions that combine the cloud database capabilities of Snowflake with the ETL and data preparation capabilities of Domo. 

As a next step, we encourage you to explore Cloud Amplifier for yourself. Visit Snowflake API Documentation and Domo’s Cloud Amplifier Resources. Or see for yourself by using our code above.