The mixed energy of Domo Cloud Amplifier and Snowflake is the best-kept secret in information administration proper now—and we’re reaching new heights day-after-day. On this how-to information, we’ll take you behind the APIs and present Snowflake engineers how Cloud Amplifier and Snowflake can work collectively to assist accomplish 5 sensible—however highly effective—duties:
- Put together and enrich information
- Create a brand new desk in Snowflake and insert information
- Combine and enrich information
- Configure and make the most of a number of warehouses
- Create a unified supply of reality
Get oriented
Introducing the important thing components
This information showcases the Cloud Amplifier configuration with Snowflake. To start out, get to know some key phrases:
- Snowflake: The centralized supply of reality for our preliminary information
- Magic ETL: Domo’s software for combining and getting ready information tables
- ERP: A supplemental information supply from Salesforce
- Geographic: A supplemental information supply (i.e., a crosswalk desk) inside Domo, together with demographics by zip code information set
- Social Media: Shorthand for information from social media platforms (e.g., Instagram)
Why Snowflake? We’re specializing in Snowflake’s cloud information platform due to its means to deal with large-scale, various information units with ease and effectivity. For real-time information analytics and reporting, it’s a perfect selection.
Put together and enrich information
with Cloud Amplifier and Magic ETL
Cloud Amplifier with Magic ETL will assist guarantee your information is prepared for additional evaluation. Right here, we’ve used Magic ETL to arrange and enrich our Snowflake information. Then we wrote the enriched information again to Snowflake. That information now turns into obtainable to make use of in Domo and different merchandise that Snowflake helps.
Create a brand new desk in Snowflake and insert information
with Cloud Amplifier
Snowflake APIs in Python help you manipulate and combine your information in refined—and helpful—methods. Right here’s how we created the transactions desk in Snowflake in our Jupyter Pocket book:
import snowflake.connector
"from snowflake.connector.pandas_tools import write_pandasn",
#Connect with Snowflake
conn = snowflake.connector.join(
consumer=’USER’,
password=’PASSWORD’,
account=’ACCOUNT’,
)
# Create the ‘transactions’ desk
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 information prepared for add
success, nchunks, nrows, _ = write_pandas(conn, transactions, ‘TRANSACTIONS’)
Subsequent, we generated the Prospects desk utilizing this code:
import snowflake.connector
from snowflake.connector.pandas_tools import write_pandas
# Connect with Snowflake
conn = snowflake.connector.join(
consumer=’USER’,
password=’PASSWORD’,
account=’ACCOUNT’,
)
create_customers_table = “””
CREATE OR REPLACE TABLE prospects (
customer_id NUMBER(10, 0)
customer_region STRING,
customer_segment STRING,
PRIMARY KEY (customer_id)
)
“””
prospects.columns = [col.upper() for col in customers.columns]
cur = conn.cursor()
cur.execute(create_customers_table)
success, nchunks, nrows, _ = write_pandas(conn, prospects, ‘CUSTOMERS’)
These snippets illustrate creating a brand new desk in Snowflake after which inserting information from a Pandas DataFrame. Very slick, if we might say so. You possibly can go to Snowflake’s API Documentation for extra detailed examples and additional assist.
Combine and enrich information for enhanced analytics
with Cloud Amplifier
Enriching Snowflake with Salesforce, social media, and monetary information is one other widespread request that we hear many times. Right here’s how we’ve finished it: We used Python to extract information from these sources into dataframe. Then we used the Domo APIs to load these dataframes into information units in Domo. Notice that the Domo SDKs are useful for programmatically interacting with Domo to load information and construct functions. Right here’s the code we used:
from pydomo import Domo
domo = Domo(‘CLIENT_ID’, ‘SECRET_KEY’,api_host=’API_HOST’)
# Extract distinctive customer_ids from the first dataset
unique_customer_ids = df[‘customer_id’].distinctive()
unique_customer_id_count = df[‘customer_id’].nunique()
# Outline a knowledge vary for the artificial information
# Generate artificial ERP information
erp_data = pd.DataFrame({
‘customer_id’: np.random.selection(unique_customer_ids,
measurement=unique_customer_id_count, substitute=False),
‘account_status’: np.random.selection([‘Active’, ‘Closed’, ‘Delinquent’],
measurement=unique_customer_id_count),
‘credit_limit’: np.random.uniform(1000, 10000, measurement=unique_customer_id_count),
‘current_balance’: np.random.uniform(o, 10000, measurement=unique_customer_id_count),
‘last_payment_date’: np.random.selection(date_range,
measurement=unique_customer_id_count),
‘payment_frequency’: np.random.selection([‘Monthly’, ‘Quarterly’, ‘Yearly’],
measurement=unique_customer_id_count),
})
domo.ds_create(erp_data, ‘CCFD ERP Knowledge Salesforce’)
Configure and make the most of a number of warehouses
with Cloud Amplifier
One other factor we’re thrilled about with Cloud Amplifier: You possibly can work together with Snowflake at a macro-level but traverse to a micro-level in only a few clicks. On this demonstration, we provisioned 5 major tables, all inside the identical database. When configuring these tables in Domo, you’ll be able to see beneath how straightforward it’s to go from warehouse to a multi-select on these tables in three clicks (the blue numbers).
Create a unified supply of reality
with Cloud Amplifier
This one’s easy—by writing the enriched information again to Snowflake, we created a single, unified supply of reality. Doing this ensures your group’s information is constant and dependable. There’s no true motion step right here; by getting ready your information in Domo, you mechanically create this unified supply.
Discover our sources and replica our code
Our aim is to assist information engineers see firsthand the sensible ways in which each Snowflake and Domo Cloud Amplifier through API/SDK can amplify their companies. We’re serving to lots of of companies construct their very own complete analytics options that mix the cloud database capabilities of Snowflake with the ETL and information preparation capabilities of Domo.
As a subsequent step, we encourage you to discover Cloud Amplifier for your self. Go to Snowflake API Documentation and Domo’s Cloud Amplifier Assets. Or see for your self by utilizing our code above.
