The Finest-Saved Secret in Information Administration: Cloud Amplifier + Snowflake
[ad_1]
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 every single 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 fact
Get oriented
Introducing the important thing components
This information showcases the Cloud Amplifier configuration with Snowflake. To begin, get to know some key phrases:
- Snowflake: The centralized supply of fact for our preliminary information
- Magic ETL: Domo’s device 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 capacity to deal with large-scale, numerous information units with ease and effectivity. For real-time information analytics and reporting, it’s an excellent alternative.
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 assist you to manipulate and combine your information in subtle—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",
#Hook up 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 Clients desk utilizing this code:
import snowflake.connector
from snowflake.connector.pandas_tools import write_pandas
# Hook up with Snowflake
conn = snowflake.connector.join(
consumer=’USER’,
password=’PASSWORD’,
account=’ACCOUNT’,
)
create_customers_table = “””
CREATE OR REPLACE TABLE clients (
customer_id NUMBER(10, 0)
customer_region STRING,
customer_segment STRING,
PRIMARY KEY (customer_id)
)
“””
clients.columns = [col.upper() for col in customers.columns]
cur = conn.cursor()
cur.execute(create_customers_table)
success, nchunks, nrows, _ = write_pandas(conn, clients, ‘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 may 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 repeatedly. Right here’s how we’ve executed 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. Observe that the Domo SDKs are useful for programmatically interacting with Domo to load information and construct purposes. 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.alternative(unique_customer_ids,
measurement=unique_customer_id_count, change=False),
‘account_status’: np.random.alternative([‘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.alternative(date_range,
measurement=unique_customer_id_count),
‘payment_frequency’: np.random.alternative([‘Monthly’, ‘Quarterly’, ‘Yearly’],
measurement=unique_customer_id_count),
})
domo.ds_create(erp_data, ‘CCFD ERP Information 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 may work together with Snowflake at a macro-level but traverse to a micro-level in just some clicks. On this demonstration, we provisioned 5 main tables, all throughout the identical database. When configuring these tables in Domo, you’ll be able to see under how simple it’s to go from warehouse to a multi-select on these tables in three clicks (the blue numbers).
Create a unified supply of fact
with Cloud Amplifier
This one’s easy—by writing the enriched information again to Snowflake, we created a single, unified supply of fact. 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 routinely create this unified supply.
Discover our sources and replica our code
Our objective 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 a whole lot 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 Sources. Or see for your self through the use of our code above.
[ad_2]